mysql常用sql语句的练习笔记

mysql常用sql语句的练习笔记，方便不记得时，翻出来看看

一、mysql操作语句

1.1 操作命令

+ grant all privileges on . to '用户名'@'主机' identified by '密码'；创建用户 + show databases; + 如果数据库太多可以过滤， show databases like "%abd%"; + use dbname; + show tables; + 如果表太多可以过滤， show tables like "%abd%"; + drop database dbanme; + drop table tbname; + drop view view_name; 删除视图 + desc tablename; 查看表的结构 + show create table/view 表名/视图名 + show full tables； 会返回 表名和table_type + show full tables where table_type = 'view';显示所有的视图 + show full tables where table_type = 'base table';显示所有的视图 + show status; 服务器状态 + select version(); mysql的版本 + select database(); 当前数据库名 + select user(); 当前用户 + show variables; 显示服务器变量 + delete/drop/truncate + delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交 + 效率方面:drop > truncate > delete + 当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete. + 参考连接 + MySQL在控制台上以竖行显示表格数据 最后+'\G' + 例如， select * from user limit 10 \G;

1.2 数据库重命名

+ CREATE DATABASE new_db_name; + RENAME TABLE db_name.table1 TO new_db_name.table1, + db_name.table2 TO new_db_name.table2; + DROP DATABASE db_name;

1.3 修改数据默认字符集和字符排序

+ ALTER DATABASE study CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

1.4 创建数据库

+ create database per_test default charset utf8 collate utf8_general_ci; + create database per_test character set utf8 collate utf8_general_ci; + 创建数据库是无法指定数据库的引擎，只能修改mysql的配置文件设置默认引擎，或者在创建表时指定数据引擎

1.5 auto_increment的初始值，增长值

1.5.1 创建表时设置初始值

create table test1( 
id int(3) unsigned not null primary key auto_increment, 
name varchar(20) not null, 
last_update timestamp default current_timestamp on update current_timestamp
) engine=Innodb auto_increment=1001 default charset=utf8;

1.5.2 修改已有表的增长初始值

1. alter table table_name AUTO_INCREMENT=1001

1.5.3 数据库默认自增初始值、增长值

1. 查看数据库的默认值：show variables like 'auto_inc%';

1.5.4 mysql配置文件修改自增步长和初始值

找到 auto_increment_increment 变量设置的地方；VI里面可以用 "/auto_increment_increment" 找到。 找到后设置 auto_increment_increment=1; auto_increment_offset=1001;

1.5.4 mysql客户端修改自增步长和初始值

SET @@auto_increment_increment=10; //设置步长

其他全局变量也可在此配置文件里面永久设置好。 配置文件修改好后，要重启mysql服务才会生效。

二、数据库DML

2.1 创建数据

create table student( -> id int(8) primary key auto_increment, -> name varchar(20) not null, -> grade int(1), -> gender char(10) -> ); create table stu_farmily( id int(3) primary key auto_increment, child_name varchar(20) not null, father_name varchar(20) not null, mather_name varchar(20) not null, phone varchar(20), address varchar(100));

create table teacher( 
id int(3) unsigned primary key auto_increment, 
name varchar(100) not null, 
age int(1) not null default 0, 
worktime varchar(50), 
create_time timestamp not null default current_timestamp,
last_update timestamp not null default current_timestamp on update current_timestamp
)engine=Innodb default charset=utf8;

2.2 插入数据

insert into student (name, grade,gender) values('jack',40,'M'), ('tom',100, 'M'),('lily',90,'F'), ('qinming', 90, 'M'), ('husanniang', 88, 'F'), ('sunerniang', 66, 'F'), ('wusong', 86, 'M'), ('linchong', 92, 'M'), ('yanqing', 90, NULL), ('test3m',70,'F'),('test_jim',30,'M'), ('jim',30,'M');

insert into student(name) values('test2),('test3'),('test4'); insert into student(name, gender) values('test5','M'),('test6','F'),('test7','M'); insert into stu_farmily(child_name,father_name,mather_name,phone,address) values ('lucy','lucy_f','lucy_m','13811112200',null), ('lily','lily_f','jlily_m','13811112222','street 1 building 115'), ('tom','tom_f','tom_m','13811112233','street 2 building 111'), ('jim','jim_f','jim_m','13811112244','street 2 building 113'), ('test3m','test_f','test_m',null,null);

('jack','jack_f','jack_m','13811112211','street 1 building 109'),

2.3 修改表

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、创建或取消索引、更改原有列类型、重新命名列或表等。 基本语法 修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

常用的语法格式如下： ALTER TABLE <表名> [修改选项] 修改选项的语法格式如下： { ADD COLUMN <列名> <类型> [约束条件] [FIRST|AFTER 已存在的字段名] default <值> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> SET DEFAULT <默认值>/DROP DEFAULT | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> } 例如 + alter table stu_farmily rename to family; 更改表名 + alter table student add column test_id int(3) default 0 after name; 在name后面新增列 + alter table student change column test_id test int(1); 修改列名和类型 + alter table student alter column test set default 5; 设置默认值 + alter table student modify test varchar(10); 修改字段类型 + alter table student drop test; 删除字段

2.4 修改表数据

2.4.1单表的MySQL UPDATE语句：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

例如： update family set father_name = 'test3m_f' where child_name = 'test3m' limit 1; LIMIT子句是一个与行匹配的限定。只要发现可以满足WHERE子句的row_count行，则该语句中止，不论这些行是否被改变。 如果一个UPDATE语句包括一个ORDER BY子句，则按照由子句指定的顺序更新行。

2.4.2多表的UPDATE语句：

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。 例如： update student set family_id =(select id from family where family.child_name = student.name); update student as s,family as f set s.family_id=f.id where s.name = f.child_name; 以上两个语句效果不一样，会导致没有查询结果的family_id 变为null, 在一个被更改的多表 UPDATE中，有些列被引用。您只需要这些列的MySQL UPDATE权限。有些列被读取了，但是没被修改。您只需要这些列的SELECT权限。 注释：您不能把ORDER BY或LIMIT与multiple-table UPDATE同时使用。

2.4.3 MySQL UPDATE语句修饰符：LOW_PRIORITY 和IGNORE

1. 如果您使用LOW_PRIORITY关键词，则UPDATE的执行被延迟了，直到没有其它的客户端从表中读取为止。
2. 如果您使用IGNORE关键词，则即使在更新过程中出现错误，更新语句也不会中断。如果出现了重复关键字冲突，则这些行不会被更新。如果列被更新后，新值会导致数据转化错误，则这些行被更新为最接近的合法的值。

2.4.4 update 固定条件更新

例如，update student set grade = grade +1 where grade is not null and grade < 100;

三、单表sql查询语句

3.1 通用查询格式

SELECT [DISTINCT] *|{字段名1, 字段名2, 字段名3,……} FROM 表名 [WHERE 条件表达式1] [GROUP BY 字段名 [HAVING 条件表达式2]] [ORDER BY 字段名 [ASC|DESC]] [LIMIT [OFFSET] 记录数]

3.2 逐个详解

3.2.1 IN关键字

带In关键字的语法为 select * | 字段名1， 字段名2，... from 表名 where 字段名 [NOT] IN (元素1， 元素2， 元素3, ...) Between AND关键字 带BETWEEN AND关键字的查询—判断某个字段的值是否在指定的范围之内 语法为： SELECT *|{字段名1,字段名2,……} FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2

select name as 姓名, gender 性别, grade  from student where grade not between 80 and 90 order by id limit 10 ;

3.2.2带Distinct关键字

Distinct关键字的语句用于去除查询结果的重复记录。有时候，处于对数据的分析需求，需要过滤掉查询记录中重复的值。语法如下： SELECT DISTINCT 字段名，字段2 FROM 表名; select distinct gender from student;

2.2.5 空值查询

判断某些列是否有空值。 在数据表中，某些列的值可能为空值NULL，空置不同于0，也不同于空字符串。

在MySQL中，使用IS NULL关键字来判断字段的值是否为空值，其语法格式为：

SELECT *|字段名1,字段名2,…… FROM 表名 WHERE 字段名IS [NOT] NULL select *from student where gender is null；

3.2.3 带Like关键字的查询

有时候需要对字符串进行模糊匹配，例如查询student表中name字段值’b’开头的记录， 为了完成类似功能，Mysql提供了Like关键字进行模糊查询。 LIKE关键字可以判断两个字符串是否相匹配。语法如下：

SELECT *|{字段名1,字段名2,……} FROM 表名 WHERE 字段名 [NOT] LIKE '匹配字符串'; 1.通配符% 匹配字符串指定用来匹配的字符串，其值可以是一个普通字符串，也可以是一个包含百分号%或者下划线_的通配字符串。 两个符号在匹配过程中有特殊含义。 select * from student where like 't%'; 匹配以t开头的信息 select * from student where name like '%i%'; 匹配包含i字母的name select * from student where name like '%i%g'; 这种情况是同时包含两种字符的name 2. 通配符_ 下划线_ 下划线通配符_仅仅匹配单个字符，如果要匹配多个字符，需要使用多个下划线通配符。 查询student表中name字段值以字符串‘test’开始，以字符串‘数字’结束 select * from student where name like 'test_'; 3.如要查询包含%和_的，需要在前面加转义字符\

3.2.4 多个条件用and 和 or

注意：OR关键字和AND关键字可以一起使用，需要注意的是，AND优先级高于OR，因此当两者在一起使用时， 应该先运算AND量表的条件表达式，再运算OR两边的条件表达式。

3.3 聚合查询

3.3.1 COUNT()

COUNT函数用来统计记录的条数，语法格式如下： select COUNT(*) from 表名

3.3.2 SUM()

sum是求和函数，用于求出表中某个字段所有值的总和，语法如下： select sum(字段名) from 表名 select sum(grade) as grade_sum from student;

3.3.3 AVG()

AVG可以用于求出某个字段所有值得平均值，语法如下： select avg(字段名) from 表名 select avg(grade) as grade_sum from student;

3.3.4 MAX/min

max/min函数则是用于求最大值/最小值的函数，用于求出某个字段的最大值 语法如下： select max/min(字段名) from 表名 select max(grade) as grade_max from student;

3.4 对查询结果进行排序

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户预期的。为了使查询结果满足用户的要求，可以使用ORDER BY对查询结果进行排序，语法格式如下：

SELECT 字段名1,字段名2,…… FROM 表名 ORDER BY 字段名1 [ASC | DESC]，字段名2 [ASC | DESC]…… 默认是ASC，可以不写，是顺序； desc，是倒序。 select *from student where grade is not null and gender is not null order by grade desc, name; 先按第一个条件排序，如果第一条件的值相同，再按第二个条件排序

3.5 分组查询

在对表中数据进行统计时，也可能需要按照一定的类别进行统计， 比如，分别统计student表中gender字段为“男” “女“， “NULL”的学生成绩之和。 在mysql中，可以使用group by按某个字段或者多个字段中的值进行分组，字段中值相同的为1组，其语法格式如下：

SELECT 字段名1,字段名2,…… FROM 表名 GROUP BY 字段名1，字段名2，……[HAVING 条件表达式];

HAVING关键字指定条件表达式对分组后的内容进行过滤。需要注意的是，Group BY一般和聚合函数一起使用，

3.5.1 单独是有groub by

select gender from student group by gender; 注意：select * from student group by gender; 会报错哦 group by 多个字段 select count(*)as num,gender,grade from student group by gender, grade; group by 但个字段，效果和select distinct 相近，只是distinct无法用聚合函数 select distinct gender from student; select gender from student group by gender; 如果查询的字段出现在Group By后，却没有包含在聚合函数中，该字段显示的是分组后第一条记录的值，这样可能会导致查询的结果与预期不符合。

3.5.2group by 经常和聚合函数一起用， 例如

select sum(grade) as gender_sum, avg(grade) as grade_avg, count(*) as gender_num,gender from student group by gender;

加上having的例子，性别不为空的 select sum(grade) as gender_sum, avg(grade) as grade_avg, count() as gender_num,gender from student group by gender having gender is not null; HAVING的条件一定是前面select后面的字段，如果要平均分大于70的 select sum(grade) as gender_sum, avg(grade) as grade_avg, count() as gender_num,gender from student group by gender having grade_avg>70 ;

3.5.3 Group By和Having关键字一起使用

Having关键字与where字句作用相同，都用于设置条件表达式对查询结果进行过滤，两者的区别在于，Having关键字后可以跟聚合函数，而where子句不可以。通常情况先Having关键字都和Group By一起使用，用于对分组后的结果进行过滤。 将student表中按照gender字段进行分组排序，查询出grade字段值之和小于300的分组 select sum(grade), gender from student group by gender having sum(grade)<300;

3.6 单表子查询

找出成绩最低的学生信息 select *from student where grade = (select min(grade) as grade_min from student);

3.7 union 联合

3.7.1 union 联合 语法

SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

3.7.2 union的左右

找出不同表中，同一个字段，合并到一起 1. select distinct gender from student union select distinct gender from test1; 自动去重 2. select distinct gender from student union all select distinct gender from test1; 两个结果合并到一起，不去重

3.8 join 连接

3.8.1内连接 inner join(等同于join)

语法：select xxx from 表1 inner join 表2 on 条件 如：查询出所有学生 -> select a.*, b.father_name from student as a inner join family as b on a.name=b.child_name where a.grade is not null; 结果：返回都有值得数据，相当于视图

3.8.2 左连接 left join

语法：select xxx from 表1 left join 表2 on 条件 如：查询出所有学生 -> select a.*, b.father_name from student as a left inner join family as b on a.name=b.child_name where a.grade is not null; 结果：以连接左边的表student为基础，补全所有father_name 信息，没有的会自动补 null

3.8.3 右连接 right join

语法：select xxx from 表1 right join 表2 on 条件 如：查询出所有学生 -> select a., b.father_name from student as a right inner join family as b on a.name=b.child_name where a.grade is not null; 结果：以连接右边的表family为基础，补全所有student.的信息，没有的会自动补 null

3.9 explain

3.9.1 概要描述：

1. id:选择标识符
2. select_type:表示查询的类型。
3. table:输出结果集的表
4. partitions:匹配的分区
5. type:表示表的连接类型
6. possible_keys:表示查询时，可能使用的索引 7.key:表示实际使用的索引
7. key_len:索引字段的长度
8. ref:列与索引的比较
9. rows:扫描出的行数(估算的行数)
10. filtered:按表条件过滤的行百分比
11. Extra:执行情况的描述和说明

3.9.2 type类型：

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。 常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树
3. range:只检索给定范围的行，使用一个索引来选择行
4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4 mysql内部函数

4.1 mysql时间函数

1.mysql的时间类型 + date 3字节，1000-01-01/9999-12-31，YYYY-MM-DD，无法设置默认值 + time 3字节，'-838:59:59'/'838:59:59'，HH:MM:SS，无法设置默认值 + year 1字节， 1901/2155， YYYY，无法设置默认值 + datetime 8字节，1000-01-01 00:00:00/9999-12-31 23:59:59,从5.6.5开始，，default now()或current_timestamp + timestamp 4字节，1970-01-01 00:00:00/2038-1-19 11:14:07（北京时间），default now()或current_timestamp + 它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。 + 将时间转换为时间戳unix_timestamp， SELECT UNIX_TIMESTAMP('2019-02-22 13:25:07'); #1550813107 + 将时间戳转换为时间from_unixtime，SELECT FROM_UNIXTIME(1550813107); #2019-02-22 13:25:07 2. mysql的时间函数 + NOW() 获取当前日期和时间的函数。 + curdate() 获取当前的日期 + curtime() 获取当前时间 + date(date) 获取参数里面的，日期部分 + extract(unit FROM date), 获取返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。 + unit 参数可以是下列的值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、 MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、 DAY_MINUTE、DAY_HOUR、YEAR_MONTH + select EXTRACT(YEAR_MONTH FROM now()) ; + date_add(date,interval expr type) 将日期添加指定的时间间隔。 + date 参数是合法的日期表达式。expr 参数是添加的时间间隔,+正数是未来，-负数是过去。 + type 参数可以是下列值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、 MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、 DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH + 实例： select *from users where last_update > date_add(now(),interval -2 day); 查询2天内登陆过的用户信息 + date_sub(date,interval expr type) 将日期减去指定的时间间隔,相当于date_add用负数的效果 + datediff(date1,date2)获取两个日期之间的天数 + date1 和 date2 参数是合法的日期或日期/时间表达式。 + 实例：select id, username, datediff(now(),date(regist_time)) as reg_day from user; 查询用户注册时间 + dateformat(date,format) 格式化日期时间 + date 参数是合法的日期。format 规定日期/时间的输出格式。 + %a：缩写星期名， + %b：缩写月名， + %D：带有英文前缀的月中的天， + %M：月名， + %W：星期名， + %w：周的天 （0=星期日, 6=星期六） + %Y：年，4 位，%y：年，2 位，%c：月，数值，%m：月，数值(00-12)，%d：月的天，数值(00-31)，%e：月的天，数值(0-31), + %H：小时 (00-23)，%h：小时 (01-12)，%I：小时 (01-12)，%i：分钟，数值(00-59)，%S：秒(00-59)，%s：秒(00-59)，%f：微秒 + %r：时间，12-小时（hh:mm:ss AM 或 PM， + %p：AM 或 PM， + %k：小时 (0-23)，%l：小时 (1-12) + %j：年的天 (001-366) + %T：时间, 24-小时 (hh:mm:ss) + %U：周 (00-53) 星期日是一周的第一天 + %u：周 (00-53) 星期一是一周的第一天 + %V：周 (01-53) 星期日是一周的第一天，与 %X 使用 + %v：周 (01-53) 星期一是一周的第一天，与 %x 使用 + %X：年，其中的星期日是周的第一天，4 位，与 %V 使用 + %x：年，其中的星期一是周的第一天，4 位，与 %v 使用 ``` #查询示例 #根据年月日查数据 select id,name, date_format(last_update,"%Y-%m-%d") as last_update from test1 where last_update="2020-06-12"; #根据年月查数据 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y-%m')='2021-01'; #根据年查数据 SELECT *FROM tb_test WHERE DATE_FORMAT(update_time, '%Y')='2022';

今天

select * from 表名 where to_days(时间字段名) = to_days(now()); #近7天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名) #本月 SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) #询本季度数据 select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now()); + to_days() 从1970到现在的天数 3. 查询某一天数据的各种方法 SELECT * FROM test WHERE substring(Convert(char(10),update_time ,112),1,8)='20200601' SELECT * FROM test WHERE update_time between '2020-06-01 00:00:00' and '2020-06-017 23:59:59' SELECT * FROM test WHERE year(update_time ) = 2020 and month(update_time )= 06 and day(update_time ) = 01 SELECT * FROM test WHERE update_time > '2020-06-01' and update_time < '2020-06-02' SELECT * FROM tests WHERE ( datediff ( update_time , '2020-06-01' ) = 0 )

5 mysql的索引

5.1 索引种类

1. 主键索引primary key
2. 普通索引 key
3. 唯一索引 unique
4. 组合索引
   • 比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
   • 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，
   • 但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，
   • 那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
   • 因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
5. 全文索引
   • 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。 fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ， create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
   • 自然语言搜索，就是普通的包含关键词的搜索.
   • BOOLEAN MODE
     • 这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况. 比如 +嘻哈表示必须包含嘻哈, -嘻哈表示必须不包含,默认为误操作符,代表可以出现可以不出现, 但是出现时在查询结果集中的排名较高一些.也就是该结果和搜索词的相关性高一些.
     • 具体包含的所有操作符可以通过MySQL查询来查看: show variables like '%ft_boolean_syntax%'
   • 查询语法 where match(索引字段) agninst("字符串")
   • ngram and MeCab full-text parser plugins 从MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。
   • 使用ngram插件 必要的mysql.cnf参数设置
     • 在使用中文检索分词插件ngram之前，先得在MySQL配置文件里面设置他的分词大小（默认是2），比如，
     • [mysqld]
     • ngram_token_size=2 2、必要的参数设置

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';
ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;
#自然语言模式
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);
等于 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');
#BOOLEAN MODE
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -贯彻' IN BOOLEAN MODE);

5.2 索引创建方式

1. 创建表时，创建
   • primary key(字段)
   • key(字段)
   • unique(字段)
   • INDEX index_name (字段)
   • UNIQUE INDEX index_name (字段)
   • index index_name (字段1,字段2，字段3) 组合索引
   • FULLTEXT (字段1,字段2) 可多个字段
2. create index 创建
   • CREATE INDEX index_name ON table_name (column_list)
   • CREATE UNIQUE INDEX index_name ON table_name (column_list)
3. alter table 创建
   • ALTER TABLE table_name ADD INDEX index_name (column_list)
   • ALTER TABLE table_name ADD UNIQUE (column_list)
   • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

5.3 删除索引

1. 语法
   • DROP INDEX index_name ON talbe_name
   • ALTER TABLE table_name DROP INDEX index_name
   • ALTER TABLE table_name DROP PRIMARY KEY

5.4 查看索引

1. 语法
   • show index from tblname;
   • show keys from tblname;

5.6 什么字段建索引

1. 表的主关键字
2. 表的字段唯一约束
3. 直接条件查询的字段
4. 查询中与其它表关联的字段
5. 查询中排序的字段
6. 查询中统计或分组统计的字段

5.7 不用索引的情况

1. 表记录太少
2. 经常插入、删除、修改的表
3. 数据重复且分布平均的表字段
4. 经常和主字段一块查询但主字段索引值比较多的表字段
5. 经常和主字段一块查询但主字段索引值比较多的表字段

5.8 MySql在建立索引优化时需要注意的问题

1. 索引不会包含有NULL值的列
2. 使用短索引。
   • 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内， 多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 排序的索引问题
   • mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
   • 因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4. like语句操作
   • 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5. 不要在列上进行运算
   • select * from users where YEAR(adddate)
6. 不使用NOT IN和操作
   • NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id