SQL

索引

索引概念

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引负面影响

降低数据增删改的速度：就像字典中多加一个字（或修改、删除），目录也要进行更新，所以这样就降低了增删改的速度。
案例
设有新闻表15列，10列上有索引，共500w行数据，如何快速导入？
1：把空表的索引全部删除
2：导入数据
3：数据导入完毕后，集中建索引

索引创建原则

1：不要过度索引（拖增删改的速度）
2：在where条件最频繁的列上加
3：尽量索引散列值（即不重复），过于集中的值加索引意义不大。如：给性别“男”，“女”加索引，意义不大

索引类型

mysql索引的四种类型：主键索引、唯一索引、普通索引和全文索引。
普通索引： 基本的索引类型，值可以为空，没有唯一性的限制。
唯一索引： 索引列的所有值都只能出现一次，即必须唯一，值可以为空。
全文索引： 全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。
主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。
联合索引：组合索引，即一个索引包含多个列

索引SQL

alter table 表名 add index/unique/fulltext [索引名] ();
Alter table 表名 add primary key (列名);
(注：[]中内容意思为可以不加索引名，默认索引名是列名；主键不加索引名，因为主键只有一个)
唯一索引和主键的区别：
(1) 主键必唯一，唯一索引不一定是主键；
(2) 一张表上，只能有一个主键，但是可以用一个或多个唯一索引
查看索引
show index from 表名;
show keys from 表名;
show index from 表名;   \G格式化查看结果
删除索引
删除非主键索引：Alter table 表名 drop index 索引名;
删除主键：alter table member drop primary key;

索引靠左原则

例如：在表a中，添加联合索引a_id_name_age（a_id, a_name, a_age），相当于创建了(a_id)单列索引，(a_id,a_name)组合索引，(a_id,a_age)组合索引(a_id,a_name,a_age)组合索引。
演示如下：
mysql> explain select count(*) as num,a_id from a where a_age=‘17’ and a_id IN(10)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a_id_name_age
key: a_id_name_age
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index
1 row in set (0.00 sec)

Explain

mysql> explain select b_id,b_grade from b order by b_grade \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: index
possible_keys: NULL  //可能用到的key
key: grade  //最终用到的key
key_len: 10
ref: NULL
rows: 8
Extra: Using index; Using filesort
1 row in set (0.00 sec)

type

是指查询的方式，非常重要，是分析“查数据过程”的重要依据。可能的值如下：
all：从表的第一行往后逐行做全表扫描；
index：比all性能稍微好些，index扫描所有索引节点；
range：查询时，能根据索引做范围的扫描；
ref：通过索引列，直接引用到某些数据行；
eq_ref：通过索引列，直接引用某一行数据，eq是相等的意思，两表连查时容易看到；
const：常数，优化到常量级别，再多的数也会在一个常量的时间内返回；
null：速度极快（当mysql的存储引擎是MyISAM时，会这样。是因为information_schema存储了这些信息）

extra

using index 覆盖索引效果最佳
using where 分行检查，效果较慢
using temporary 建了临时表，需优化索引。如：使用distinct消除取值重复的行
using filesort 使用了文件排序，即对无索引列order by

---

常用SQL语句

使用limit查看第n页的数据
limit （n-1）*N,N    n-第几页，N-一页的个数
（注：这里是先查询，后跳过，速率比较低）

数据升降序排列、limit取数据
mysql> select * from a where a_id =10 order by a_num asc,a_age desc limit 4;
±-----±------±------+
| a_id | a_num | a_age |
±-----±------±------+
| 10 | 5 | 8 |
| 10 | 5 | 7 |
| 10 | 7 | 7 |
±-----±------±------+

删除
delete from a where a_id=10;
（注：在保持数据完整性的情况下，一般大网站的数据都是不物理删除，只做逻辑删除，如：is_deleted=1）

查看表结构
show create table a;  该命令会显示编码方式
desc a;

命令取消
在输错的命令后加’\c’即可取消该条命令

为列指定别名
(1) 采用ANSI规则的方法指定别名
例如：select did ‘编号’,depart ‘部门’,start_time ‘有效开始时间’,end_time ‘有效
结束时间’ from depart_status;
(2) 使用as关键字指定别名（是最常用的一种方法）
例如：select did as ‘编号’,depart as ‘部门’,start_time as ‘有效开始时间’,end_ti
me as ‘有效结束时间’ from depart_status;

group by和having
（注：在select语句中，from，where，group by和order by必须按照语法中列出的次序依次执行）
(1) 使用group by进行分组；
(2) 一般group by 和having组合使用，having作用类似where，但两者有区别：
Where后面跟的必须是数据库表里有的字段，而having只是根据前面查询出来的来查询。如：
使用having
mysql> select *,count(did),avg(did) as ag from depart_status group by did having
ag >1000;
使用where
mysql> select *,count(did),avg(did) as ag from depart_status where ag>1000 group
by did;
ERROR 1054 (42S22): Unknown column ‘ag’ in ‘where clause’
(3) Group by单独使用时，只显示该组的第一条记录

连接查询（参考：https://www.cnblogs.com/Brambling/p/6691760.html)

(1) 内连接
内连接,或等值连接，获取两个表中字段匹配关系的记录，可以使用关键字INNER JOIN……ON或CROSS JOIN……ON
INNER JOIN……ON
例如：mysql> select b.b_id,b_grade from b inner join (select b_id from b limit 2,3) as tmp on b.b_id=tmp.b_id;
延迟关联技巧，加快查询速度
CROSS JOIN……ON
select per.*,dep.depart,dep.start_time,dep.end_time from persion_info per CROSS JOIN depart_status dep ON per.pid = dep.did\G

(2) 左外连接
可以查询出左表的所有记录，右表只能查询出匹配的记录
例如：mysql> select per.*,dep.depart,dep.start_time,dep.end_time from persion_info per LEFT JOIN depart_status dep ON per.pid = dep.did\G

(3) 右外连接
可以查询出右表的所有记录，左表只能查询出匹配的记录
例如：select per.*,dep.depart,dep.start_time,dep.end_time from persion_info per RIGHT JOIN depart_status dep ON per.pid = dep.did\G

联合查询
UNION用来把两个或两个以上的select语句的查询结果输出连接成一个单独的结果集；会去除相同的记录；查询结果的列标题要在第一个查询语句中定义，排序也一样。
例如：mysql> (select * from persion_info where pid in (‘1001’,‘1002’)) union (select * from persion_info where sex = ‘女’) order by pid desc\G

正则表达式查询数据
(1) ^ 匹配字符串开始；$匹配字符串结束
如：mysql> select * from depart_status where depart regexp ‘^数’ and depart regexp ‘系$’;
还可以使用like
如：mysql> select * from depart_status where depart like ‘数%系’;
(2) [] 匹配字符集合
如：mysql> select * from member where email regexp ‘[b]’;
±—±------±-----±------+
| id | email | tel | intro |
±—±------±-----±------+
| 2 | ab | 121 | 1212 |
| 3 | bg | 111 | 1313 |
±—±------±-----±------+
（注：这里汉字不能精确匹配）
(3) | 匹配字符串中的任意一个字符串
如：mysql> select * from member where email regexp ‘ab|2’;
±—±------±-----±------+
| id | email | tel | intro |
±—±------±-----±------+
| 1 | 12 | 123 | 1234 |
| 2 | ab | 121 | 1212 |
±—±------±-----±------+
(4) . 代替字符串中的任意一个字符
如：字母1.g 两个字母之间必须有一个字符
mysql> select * from member where email regexp ‘1.g’;
±—±------±-----±------+
| id | email | tel | intro |
±—±------±-----±------+
| 1 | 12g | 123 | 1234 |
| 3 | b11g | 111 | 1313 |
±—±------±-----±------+

profile功能
开启：mysql> set profiling=1;
执行：mysql> show profiles;
查看某个查询的详细时间消耗：mysql> show profile for query 2;

删除缓存
mysql> reset query cache;

修改表的存储引擎方式
mysql> alter table d engine=myisam;

外键约束
table必须是InnoDB才支持
CASCADE：跟随父表更新，ON UPDATE时选择使用
RESTRICT：不进行任何操作，ON DELETE时选择

自增约束
a.使用AUTO_INCREMENT修饰符，字段类型需要时INT型
b.必须有唯一约束
查看服务器的自增约束：show variables like ‘%auto_increment%’;