MySQL笔记（二）

MySQL第二部分：索引、视图、触发器、事务、存储过程。

存储引擎用innodb的话，会生产一个后缀为.frm的文件。
存储引擎用myisam的话，会生产三个文件。
其中后缀为.frm的文件存放的是表的结构；后缀为.MYD的存放的是表中的数据；后缀为.MYI的文件存放的是表的索引。

索引（index）：索引就是某列上的目录。
假设有600W条数据，如果没有索引的话，要在其中找一条数据，平均要比对300次。才能找到。

mysql中内部索引实现
索引的优缺点：
优点：大大的加快了查询的速度。
缺点：会降低增删改的速度。
缺点：索引也会占用一定的空间。有些时候，索引文件占用的空间会比数据占用的空间还要大。
索引的使用原则：
1、不过度索引
2、索引条件列(where后面最频繁的条件比较适宜索引)
3、索引散列值,过于集中的值不要索引。例如不要给性别列加索引。
索引的类型：
普通索引（index）：
唯一索引（unique）：行上的值不能重复
主键索引（primary key）：主键是唯一的。行上的值不能重复。
在一张表中，主键索引只能有一个。唯一索引可以有多个。
全文索引（fulltext）：

查看索引：show index from 表名
例如：查看stu表中的索引
show index from stu;

添加索引：
创建表并且添加好数据，然后再添加索引。
alter table 表名 add index/unique/fulltext [索引名] (列名);
alter table 表名 add primary key (列名).

例子：
create table info
(id int,
email varchar(30),
tel varchar(20),
intro text
)engine myisam charset utf8;

在创建表的时候就添加索引
create table info
(id int,
email varchar(30),
tel varchar(20),
intro text,
primary key(id),
unique(email)
)engine myisam charset utf8;

给id列添加主键索引
alter table info add primary key (id);

给email列添加唯一索引
alter table info add unique e_index (email);
alter table info add unique (email);

给tel列添加普通索引
alter table info add index (tel);

给intro列添加全文索引
alter table info add fulltext (intro);

删除索引：
alter table 表名 drop index 索引名
例如：删除tel索引
alter table info drop index tel;
注意：当主键索引是自动增长的时候，主键索引是删除不掉的。
对于大数据的转移，建议先删除所有的索引。然后转移数据，当数据转移完后，在同一添加索引。

面试题：联合索引
在一个表中，经常出现几列同时作为查询条件，例如stu表中，条件中经常出现name、age、sex。
例子：select * from stu where name=’’ and sex=’’ and age=…;
这个时候，不要每个列都加索引，像sex列，单独加索引是没有多大意义的，产生的负面影响可能还要大于正面影响。
解决办法：联合索引；
alter table stu add index nsa(name,age,sex);

补充：如果单独用name列作为条件，仍然会用到联合索引（nsa）。如果单独用age列或者sex列作为查询条件，不会用到联合索引（nsa）

PRI主键约束；
UNI唯一约束；
MUL可以重复。

转自http://hi.baidu.com/gungod/item/db34199557a646bb83d2954b

MySQL中SHOW INDEX FROM table 会有以下结果列
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment
多种评注。

我更想知道的是Cardinality列的深入含义。
查看官方文档的解释：
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
所以这个Cardinality会有如下的含义：

1. 列值代表的是此列中存储的唯一值的个数（如果此列为primary key 则值为记录的行数）
2. 列值只是个估计值，并不准确。
3. 列值不会自动更新，需要通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库。
4. 列值的大小影响Join时是否选用这个Index的判断。
   因此经常analyze table 保持Cardinality最新，对索引的选择是很有用的，会提高SQL语句的执行效率。
   MyISAM与InnoDB在NULL上对于Cardinality的计算方式不同。Innodb中是把所有的null被看成相等的一个值，也就是说，它们形成一个数值组，这样如果NULL数值组大小远大于平均非NULL数值组大小，该方法向上倾斜平均数数值组大小。这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是，会使优化器进行ref访问时本应使用索引而没有使用，影响索引的选择。因此在XtraDB存储引擎中增加了一个参数innodb_stats_method，该参数值类似于myisam中的myisam_stats_method，搜集索引值分发的统计信息时服务器如何处理NULL值，该参数有3个可选值，nulls_equal=所有的null被看成相等的一个值，nulls_unequal=每个null被看成单独的一个值，nulls_igored=null被忽略。缺省值为nulls_equal。

全文索引和停止词：在所有的词中，给不常用的词添加一个索引，这个索引就是全文索引。常用的词不会加索引（如my is you…）
一般情况下，全文索引针对中文意义不大。因为中文无法分词，目前国内能做中文分词的公司只有寥寥几家。
hello,nice to meet you! my name is lll.

全文索引的应用：
查关键词与全文的匹配度，如果匹配度大于0，将取出该行。
select [列名],match(要匹配的列) against (‘关键词’) from 表名;
select id,match(intro) against (‘is’) from info;
全文搜索：
select * from 表名 where match (列名) against (‘匹配的某个单词’);
select * from info where match(intro) against (‘database’);

停止词：常用的词，如my，is，you，this等，叫做停止词

● 视图：

视图就是一张表的映射。是根据某种经常被查询的条件查询得出的结果。

查看视图：
show tables;

创建视图：
create view 视图名 as select ….
例子：把所有商品中进价大于200元的商品找到，存储视图
create view gui as select * from goods where price > 200;

删除视图：
drop view 视图名
删除gui视图
drop view gui;

视图的好处：

1、简化查询

2、可以做权限控制
例如，不想让你看到我的进价
方法：除去进价列，把其他列查询出来，结果作为一张视图。给你视图的权限而不给你表的权限。
例如：create view abc as select id,category_id,name from goods;
给你操作abc的权限，而不给你操作goods表的权限。

3、可以用做大数据分表

假设有1000万条数据，我将他们存到10张表中。10张表分表是user0/user1/……user9

取模法：用数据的id%10=（0,1,2,……9）。结果如果等于0，那么将该条数据存放到user0表中，结果如果等于1，那么将该条数据存放到user1表中….依次类推。

假设现需要从10张表中取数据。
解决方法：把这10张表查询的结果，存储成一张视图
create view all_user as select * from user0 union select * from user1 union select ……

视图与表的关系：

视图是表的映射。

1. 当表中的数据发生变化，视图中的结果也会更新。

2. 当视图中的数据发生变化，表的数据不一定发生变化。
   比如，视图中的列是通过原表的几列计算而来的时候，此时不能修改。
   比如，新增数据的时候，视图必须要包括原表中所有没有默认值的列。

算法：（merge(合并)、temptable(临时表)、undefined(未定义)）
例子：查每个分类下最贵的商品。
不用视图：select * from (select * from goods order by category_id,price desc) as aa group by category_id;

用视图：create view paixu as select * from goods order by category_id,price desc;
select * from paixu group by category_id; /得到一个错误的结果/

①、有些时候，视图创建好了，只不过是一条sql语句。并没有形成临时表。
例如上例中
创建视图的时候，sql语句是：select * from goods order by category_id,price desc;
在该视图中查询的sql是：select * from paixu group by category_id;

把这两条sql语句整合到一起，得到：
select * from goods group by category_id order by category_id,price desc;

②、有的时候，视图创建好了，我们可以把这个视图看成一张临时表，虽然是临时表，但终归是一张实实在在存在的表。
create algorithm=temptable view paixu as select * from goods order by category_id,price desc;
查询：select * from paixu group by category_id;/得到正确结果/

触发器：trigger
监视某种事件，如果监视的事件发生了变化，就会引发其他的变化。

goods表（商品）
id name（商品名） num（库存）
1 方便面 200
2 矿泉水 200

order表（订单）
oid（订单id） gid（商品id） much（个数）
1 2 10
2 1 20

从该例中看出，我监视的是order表的insert事件。如果有insert事件发生，就会触发goods表的update操作。

创建trigger四要素：
监视地点：o表
监视事件：o表的insert事件
触发时间：o表有insert之后/之前
触发事件：update g表

语法：
create trigger 触发器名
after/before insert/update/delete on 表名
for each row
begin
触发的sql(insert/update/delete)
end

#先改下mysql命令结束标识符
delimiter $

#第一个触发器
create trigger t1
after insert on o
for each row
begin
update g set num=50 where id=1;
end$

drop trigger t1$

#第二个触发器
create trigger t2
after insert on o
for each row
begin
update g set num = num - new.much where id = new.gid;
end$

#第三个触发器
create trigger t3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;
end$

#第四个触发器
create trigger t4
after update on o
for each row
begin
update g set num = num + old.much - new.much where id = new.gid;
end$

#第五个触发器
create trigger t5
before insert on o
for each row
begin
declare n int;
select num from g where id = new.gid into n;
if n < new.much then
set new.much = n;
end if;
update g set num = num - new.much where id = new.gid;
end$

● 事务：mysql中必须用innodB的存储引擎
account账户表
id name money
1 张三 200
2 李四 200

张三给李四打100块钱。需要如何操作

1. update account set money = money – 100 where id = 1;
2. update account set money = money + 100 where id = 2;

这两条sql语句要么都成功执行，要么都不执行。à 原子性
事务操作过程中，对外是不可见的。当完成一条sql的时候，并不会看到结果 à 隔离性
打钱前后，双方钱的总和要保持一致 à 一致性
通过事务改变的结果是不可逆的 à 持久性

使用事务的语法：
语法：
#开启事务
start transaction;
#事务中要执行的sql
sql1 sql2 ….
#没有问题，提交 commit / 发生错误，回滚（取消事务） rollback;

例子：张三给李四打100块钱。

start transaction;
update account set money = money + 100 where id = 2;
update account set money = money – 100 where id =1;
#没有问题了，可以提交了
commit;

事务运行原理：

需要注意一点：
有些sql语句的执行会自动提交事务。有：
ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES

《数据库概念》

● 存储过程：

把一些sql语句或者其他存储起来。当用的时候，直接调用。
语法：
#js

create procedure 存储过程名()
begin

end

创建第一个存储过程：
create procedure p1()
begin
select * from stu;
end$

调用该存储过程p1. à call p1()$

第二个存储过程：

create procedure p2(n int)
begin
select * from stu order by id desc limit n;
end$

第三个存储过程：

create procedure p3(n int, t char(1))
begin
if t = ‘h’ then
select * from stu order by id desc limit n;
else
select * from stu order by id asc limit n;
end if;
end$

调用：call p3(4,‘h’)$

第四个存储过程：
写一个存储过程，计算1—给出的数n之间的所有数的和。

function p4(n){
var sum = 0;
var a = 1;
while(a <= n){
sum = sum + a;
a = a+1;
}
document.write(sum);
}

create procedure p4(n int)
begin
declare sum int default 0;
declare a int default 1;
while(a <= n) do
set sum = sum + a;
set a = a + 1;
end while;
select sum;
end$

● 函数：

create function f1([n])
returns type;
begin
return value;
end$

注意事项：
①、在begin之前必须指定一个返回类型
②、在函数体里，必须包含一个return语句，即使这个return语句没有用，也要指定。
③、函数中不能包含一个有结果集的语句，如select/show
#第一个函数
create function f1(n int)
returns int(1)
begin
delete from stu where id = n;
return 1;
end$

调用函数：select f1(1)$

#第二个函数，给stu表添加数据
create function f2(_name varchar(10), _sex char(1), _age tinyint, _class varchar(10))
returns int(1)
begin
insert into stu(name,sex,age,class)values(_name,_sex,_age,_class);
return 1;
end$

mysql编程：
1、声明变量
declare 变量名 数据类型
declare 变量名 数据类型 default 默认值
例如：declare n int default 0;

2、给变量赋值（设置变量的值）
declare n int;
set n = 1; #给变量一个值

3、leave语句，相当于js中的break。可以用来跳出指定的循环

4、loop循环，输出1–10
create procedure _loop()
begin
declare i int default 1;
s:loop
if i>10 then
leave s;
end if;
select i;
set i = i+1;
end loop;
end$

5、repeat循环
create procedure _repeat()
begin
declare i int default 1;
repeat
select i;
set i = i + 1;
until i>10 end repeat;
end$

6、while循环
create procedure _while()
begin
declare i int default 1;
while i<=10 do
select i;
set i = i + 1;
end while;
end$

存储过程和函数的区别：
①、定义时，在结构上有区别（函数有很大的限制）
②、在处理大量数据的时候，函数的效率远比存储过程高

mysql内部函数：
返回X 的绝对值。
mysql> SELECT ABS(2);
-> 2

返回不小于X 的最小整数值。
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEIL(-1.23);
-> -1

返回不大于X的最大整数值 。
mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2

若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：
SELECT FLOOR(7 + (RAND() * 6));

字符串函数
连接字符串
SELECT CONCAT(‘My’, ‘S’, ‘QL’);
-> ‘MySQL’

求字符串的长度
SELECT LENGTH(‘text’);
-> 4

字符串截取
mysql> SELECT SUBSTRING(‘Quadratically’,5);
-> ‘ratically’

mysql> SELECT SUBSTRING(‘foobarbar’ FROM 4);
-> ‘barbar’

mysql> SELECT SUBSTRING(‘Quadratically’,5,6);
-> ‘ratica’

mysql> SELECT SUBSTRING(‘Sakila’, -3);
-> ‘ila’

mysql> SELECT SUBSTRING(‘Sakila’, -5, 3);
-> ‘aki’

mysql> SELECT SUBSTRING(‘Sakila’ FROM -4 FOR 2);
-> ‘ki’

任务：新建一张表，写一个函数，插入到该表200万条数据。

create table demo
(
id int primary key auto_increment,
randstr char(6)
)charset = utf8 engine = myisam;

#写一个函数，生成随机字符串
create function rand_str()
returns varchar(6)
begin
declare all_str varchar(52) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
declare i tinyint default 0;
declare str varchar(6) default ‘’;
while i<6 do
set str = concat(str,substring(all_str,FLOOR((RAND() * 52)),1));
set i = i + 1;
end while;
return str;
end$

#写一个插入语句的函数
create function insert_value(n int)
returns int(10)
begin
declare i int default 0;
repeat
insert into demo(randstr) values (rand_str());
set i = i + 1;
until i>=n end repeat;
return 1;
end$

#要求：硬盘空余容量大于150M；

后面：
sql注入
万能用户名，万能密码
sql优化
explain…用来分析select的效率的