数据库管理

数据库 数据表 记录 表结构是指 数据字段 值 以及约束条件 关系型 有表结构 mysql oracle db2 sqlserver access 非关系型 key-value存储的没有表结构 mongodb redis memcache

sql语句

mysql服务端软件即mysqld帮我们管理好库和表，我们使用mysql软件规定的语法格式去提交自己的命令，实现对库表的管理该语法即sql（结构化查询语句） sql语言主要有存取 查询 更新数据 和 管理关系数据库系统 sql语言由ibm开发 sql语言有3种类型： DOL 语句 数据库定义语言 数据库 表 试图 索引 存储 过程 如 create drop alter DML语句 数据库操作语言 插入数据insert 删除数据delete 更新数据update 查询数据select DCL语句 数据库控制语言 如控制用户的访问权限 grant revoke

库

增
     create database db01 charset utf8;
    #创建数据据库名为db01 字符编码为 utf8
删
    drop database db01;
    #删除数据库 db01
改
    alter database db1 charset gbk;
    #修改数据库 db1 的 字符编码为 gbk
查
    show databases;
    #查看所有数据库
    show create database db01;
    # 查看新建的数据库 db01
    select database();
    #查看当前数据库

information_schema :虚拟库 不占用磁盘空间 存储的是数据库启动后的一些参数信息，用户信息，列表信息，权限信息，字符信息
performance_schema mysql5.5开始新增的一个数据库 主要用于收集数据库服务器的性能参数 记录处理查询请求发生的各种时间，锁等现象
mysql 授权库 主要存储系统用户的权限信息
tets mysql 数据库自动创建的测试数据库
==============================================
数据库命名规则
可以由字母数字下划线@#$
区分大小写
不能使用关键字
不能单独使用数字
最长128位

表

表相当与文件，表中的一条记录相当与文件的一行内容，不同的是表中的一条记录有对应的标题，称为表的字段

什么是存储引擎
表的类型就是存储引擎，表的类型不同，会对应mysql不同的存取机制
存储引擎就是如何存取数据，如何为存储的数据建立索引和如何更新，查询数据等技术的实现方法，因为在
关系型数据库中数据的存储形式是以表的形式存储的，所以存储引擎也可以叫表类型（存储和操作表的类型）
oracle 和sql server等数据库中只有一种存储引擎，所有的数据存储管管理机制都是一样的，而MySql数据库提供了多种存储引擎可以灵活选择
show engines # 查看支持的存储引擎
'''
MEMORY     只有frm  数据存在内存中     
MRG_MYISAM               
CSV                      
FEDERATED             
PERFORMANCE_SCHEMA      
MyISAM    对应3个文件 frm为表结构 MYD为数据 MYI为索引文件              
InnoDB     会对应两个文件 frm为表结构 idb为数据 数据放在磁盘 为默认的存储引擎  
BLACKHOLE  只有frm 黑洞存储引擎 放入里面的数据都会没   
ARCHIVE
'''
命名注意：
同一个表中的字段名不能相同
宽度和约束条件可选
字段名和类型是必选的

增
    create table t1(id int,name char);
    # 新建表 名为 t1 字段id 为int类型  name字段为char类型
    create table t1(id int)engine=innodb;
    # 新建表 名为 t1 字段id 为int类型 存储引擎为innodb
    create table t1(id int(5) unisgned zerofill);
     # 新建表 名为 t1 字段id 为int类型 显示宽度为5 无符号 显示长度不够的用0填充，填充到左边
删
    drop table t01;
改
    alter table 表名 rename 新表名;
    # 修改表名
    alter table t1 modify name char(6);
    #修改表 t1 的 name 字段 为 char类型
    alter table t1 change name NAME char(9);
    # 修改 表 t1 中的 name 字段为 NAME char类型
查
    show create table t1;
    #查看新创建的表 t1
    show tables;
    #查看当前库下的所有表
    desc t01;
    #查看t01的表结构
    show engines;
    #查看存储引擎 InnoDB默认的类型
    show create table t4\G  
    ##查看新创建的表 t4，以行的形式显示
'''
create table t3(
id int primary key, --主键不允许为空重复
name char not null, --不允许为空
email varchar(16) null , --允许为空
age int default 3 ,--插入数据时，如果不给age列设置值，默认为3
) default charset=utf8;
'''

修改表结构
1.修改表名
    alter table 表名 rename 新表名；
2.增加字段
    alter table 表名 add 字段名 数据类型 【完整性约束条件】；
    alter table 表名 add 字段名 数据类型 【完整性约束条件】first ；first放在第一个
    alter table 表名 add 字段名 数据类型 【完整性约束条件】 after 字段名；after新字段放在那个字段之后
3.删除字段
    alter table 表名 drop 字段名；
4.修改字段
    alter table 表名 modify 字段名 数据类型 【完整性约束条件】；改字段的数据类型
    alter table 表名 change 旧字段名，新字段名，旧数据类型【完整性约束条件】；
    alter table 表名 change 旧字段名，新字段名，新数据类型【完整性约束条件】；
5.复制表
将查询的数据直接复制到t1表
create table t1 select host,user from mysql.user; 既有表结构也有数据
将表结构直接复制到t1表
create table t1 select host,user from mysql.user where 1>5; 只有表结构，条件不成立无法查询出数据
将表结构直接复制到t1表
create table t3 like mysql.user;
6.清空表
delete from t20; 不会清空表的设置，会删除掉数据 dlete 一般用来和where结合删除某些数据
truncate t20; 会清空数据并且设置也会初始化无法恢复
7.修改列 默认值
alter table 表 alter 列 set default 100；
8.删除列 默认值
alter table 表 alter 列 drop default;
9.添加主键
alter table 表 add primary key(列名)；
10.删除主键
alter table 表 drop primay key;

记录

增
insert into t1(id,name) values(1,'kil'),(2,'liu');
# 插入到 t1 表 字段 为 id ，name  值为 1,kill 2,liu
删
delete from t01;
delete from t1 where id = 2 and name='liu';
改
update db01.t01 set name = 'sb'
update db01.t01 set name = 'kill' where id =2;
update users set name=concat(name,'123') where id=2 ;
# concat是一个函数可以拼接字符串
查  select 相当于打印
select 列名，列名 as 别名，列名 from 表名；
select id,name from t1;
#查看 t1表的 id，name 字段
select id,name from db01.t1;
#查看 db01下t1表的 id，name 字段
select * from t01;
插入查询的结果
insert into 表（字段，字段）select (字段，字段) from 表2 whrer ...;

数据类型

整形类型 INT

整形类型限制的宽度不是存储宽度而是显示宽度,其他类型都是存储宽度

int  表示有符号 取值范围 -2147483648---2147483647
int unsigned 表示无符号 取值范围 0---4294967295
int(5)zerofill  仅用于显示不满足5位时左边用0填充

浮点型 FLOAT DOUBLE DECIMAL

float [(M,D)] [UNSIGNED] [ZEROFILL]
定义 单精度浮点型（不是准确小数值） ，m是数字总个数最大255，d是小数点后个数最大30
精确度 随着小数位曾多，小数位变的不准确
double [(M,D)] [UNSIGNED] [ZEROFILL]
定义 双精度浮点型（不是准确小数值） ，m是数字总个数最大255，d是小数点后个数最大30
精确度 随着小数位的增多 精度比float高，但是也会变得不准确
decimal [m[,d]] [UNSIGNED] [ZEROFILL]
定义 准确的小数值 ，m是数字总个数（负号不算）最大65，d是小数点后个数最大30
精确度 随着小数位的增多，精确度始终准确

日期类型 year data time datatime

create table student(id int,name char(6),born_year year,birth_date date,class_time time,reg_time datetime)
insert into student values(1,'kil',now(),now(),now(),now()); now()获取当前时间
===============================================================================
datetime 与timestamp的区别
1.datetime 的日期范围是1001-9999年 timestamp 的是1970-2038
2.datetime的存储时间与时区无关，timestamp的存储时间和时区有关，显示的值也依赖与时区。mysql服务器，操作一些以及客户端的连接都有时区的设置
3.datetime使用8字节的存储空间 timestamp 存储空间为4字节 timestamp比datetime的空间利用率高
4.datetime的默认值为null timestamp的字段默认值不为空 默认为当前时间，如果不做特殊处理，并且update语句中没有指定改列的更新值则默认更新为当前时间

字符类型 char 定长 varchar 变长

字符宽度指的是字符的个数
char 在宽度不满足5的时候往右填充空格，取值长度的时候会自动去除空格
create table t1（name char(5)）
varchar 不填充，取值长度的时候取真实长度
create table t12（name varchar(5)）
insert into t1 values('理解   ')；
insert into t12 values('理解   ')；
查看t1表中name字段的字符数
slelect char_leng(name) from t1;
set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'
select name form t1 where name ='理解'；  可以查询出结果
select name form t1 where name like '理解'； 查询不出结果，末尾去空格不使用于like
char存储     长度（4bytes） + 数据 （为空则填充4个空格）
varchar存储   长度(实际长度) + 数据
char: 
严格模式和非严格模式  默认时严格模式
严格超出字符的个数无法插入数据报错
非严格 插入的数据超出限制的个数会截断，将超出的部分丢弃
修改严为非严格模式
在配置文件中加入： sql-mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'保存重启
* 默认底层存储数据时固定的长度，不够的用空格补齐，但是查询数据的时候会自动去除空格，如果要保存空格，在sql-mode中加入 PAD_CHAT_TO_FULL_LENGTH 
查看模式sql-mode 的命令： show variables like 'sql_mode';
text ：
text数据类型用于保存变长的大字符串，可以最多到65535个字符，
一般存储文章等

时间类型 datetime timestamp date time

datetime:
    YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)
     datetime不做任何改变，原样输入输出      
timestamp
    YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00/2037年)
       对于timestamp他把客户端插入的时间从当前时间转换为utc时间进行存储，查询时再转换为客户端当前时区进行返回
data:
    YYYY-MM-DD
time：
    HH:MM:SS
查询数据库的时区： show variables like '%time_zone%'

枚举类型和集合类型

字段的值只能在给定的范围中选择 比如 单选框 多选框

enum 单选 只能在给定的范围内选择一个值

set 多选 在给定的范围内可以选择一个或一个以上的值

vreate table consumer(
id int,
name char(6),
sex enum('male','female'),#规定输入的值必须是设置好的值，如果传如的值不是的化显示为空
level enum('vip1','vip2'),
hobbies set('paly','music','read')
)
insert into sonsume values(1,'kill','male','vip2','music,read')

约束条件

not null default

create table t16 (
id int,
name char(6),
sex enum('male','female') not null default 'male' 
); # sex的值不能为空默认值male
mysql> desc t16;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id    | int                  | YES  |     | NULL    |       |
| name  | char(6)              | YES  |     | NULL    |       |
| sex   | enum('male','femal') | NO   |     | male    |       |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

unique key 限制唯一

create table t17 (
id int,
name char(6) unique  #插入的值不能重复
);
方式2：
create table t17 (
id int,
name char(6)，
unique(id), # 单列唯一
unique(name),  # 单列唯一  
);
方式3：联合唯一
create table services(
id int,
ip char(15),
port int,
unique(id), # id字段值唯一，单个唯一 
unique(ip,port),#ip和port唯一 联合唯一
);
mysql> desc t17;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | YES  |     | NULL    |       |
| name  | char(6) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t17 (id,name) values (1,'kil'),(2,'kil');
ERROR 1062 (23000): Duplicate entry 'kil' for key 't17.name'
mysql>
mysql> insert into t17 (id,name) values (1,'kil'),(2,'liu');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

primary key 主键

not null unique 不为空且唯一

innodb存储引擎一张表必须有一个主键，不指定主键的时候，会自动找一个不为空且唯一的字段为主键

单列主键

create table t17(
id int primary key,  id设置为主键 不指定id字段的值时默认为0 
name char(16)
);

复合主键

create table t18(
ip char(15),
port int,
primary key(ip,port)  ip port 为主键
);

auto_increment 自增长 必须设置为键

自增长必须设置为key
create table t20(
id int primary key auto_increment，
name char(16)
);
插入数据时id不传值的话会自动传值，以上一条数据为准增加，步长为1 从1开始
show variables like 'auto_inc%'; 查看环境变量 auto_inc的设置
set session auto_increment_increment =5 ;设置步长为5 当前会话生效
set global auto_increment_increment =5 ; 设置全局步长为5 需要会话重新登录才生效
set global auto_increment_offset = 5; 设置起始值为5，起始偏移量要小于等于 步长

foreign key 外键 ：用于建立表之间的关系

create table emp(
id int primary key,
name char(10),
sex enum('male','fem')
dep_id int,
constraint fk_info foreign key (dep_id) references dep(id) #表中的depid字段关联 dep表的id字段，需要先有被关联的表并且被关联的字段唯一// constraint(约束) fk_info (外键名)
);
'''
1表关联2表
需要先完善被关联表(2)的字段以及值，并且被关联的字段唯一，再操做关联表(1)
向关联表(1)插入数据的时候需要保证外键字段已在被关联表(2)存在，否则报错
删除被关联表(2)中的数据时需要先删除关联表(1)中的数据
'''
create table emp(
id int primary key,
name char(10),
sex enum('male','fem')
dep_id int,
foreign key (dep_id) references dep(id) on delete cascade on update cascade
);
'''
设置 on delete cascade时可以先删除被关联表中的数据 ，此时关联表中的数据也会被删除
设置 on update cascade时可以先修改被关联表中的数据，此时关联表中的数据也会被更新
'''
# 表结构已经创建好了，然后增加外键
alter table info add constraint fk_info foreign key info (depart_id) references depart(id);
# 删除外键
alter table info drop foreign key fk_info

表关系

多对一

一个表的多条记录可以对应另一个表的一条记录
左表的多条记录能否对应右边的一条记录
哪个是多就在这个表新建一个字段去关联另一个表的一个字段(外键)
需要两张表来存储信息，且两张表存在 一对多或者多对一的关系
如 员工表和部门表,员工表的多条记录也就是员工信息可以对应部门表的一个部门

多对多

'''
双向的多对一叫做多对多   多个作者可以写一本数     一本书可以让多个作者写
多对多关系需要新建一个表来存关系，两张单表+关系表,实现两个电表的多对多
'''
=======================================================
create table author(
id int primary key auto_increment,
name char(16)
);
create table authortobok(
id int not null unique,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint(外键起的名字) fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

一对一

'''
一个学生只能是一个客户，一个客户只能是一个学生
一个客户不一定是一个学生，有可能是一个学生
要在学生表去关联客户表
'''
create table customer(
id int primary key,
name char(15) not null,
qq char(10) not null    
);
create table student(
id int primary key,
course varchar(10) not null,
class char(18),
customer_id int unique,#该字段一定要唯一
foreign key(customer_id) references customer(id) #外键的字段要保证唯一
on delete cascade
on update cascade,   
)；

查询

单表查询

语法顺序：
select distinct（查询的数据去重） 字段，字段 
from 表  
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n；显示的行数
（优先级是从上往下的）order by的执行顺序在select之后 limit也是在select之后
============================================================
select id*10 from t1 where id=1;
select id*10 as id10 from t1 where id=1; # as重名命
select id*10  id10 from t1 where id=1; # 重名命
显示格式
concat（）用于连接字符串
select concat('姓名：',name,'性别：'sex) as info from t1;
'''
====================================|
info     
=================================== |
姓名：kill 性别：male                 
姓名：xxx1 性别：male
姓名：xxx2 性别：male
====================================
'''
select concat_ws('：',name,sex) as info from t1; 
'''
====================================|
concat_ws('：',name,sex)
=================================== |
kill ：male                 
xxx1 ：male
xxx2 ：male
====================================
'''

where 约束条件

where 语句中可以使用：
1.比较运算符 > < >= <= != =
2.between 80 and 100 大于80小于100
3.in（80，90，100）
4.like 'kill%'
pattern 可以是%或_
%表示任意多个字符
_表示一个字符
5.逻辑运算符：可以在多个条件之间使用 not and or

select salary ,name from t1 where salary >= 2000 and salary <= 30000;
select salary ,name from t1 where salary between 20000 and 30000；salary大于2000 小于 30000
select salary ,name from t1 where salary not between 20000 and 30000；
select * from t1 where post is NUll;  判断是否为空的时候必须使用is   is not 不是什么
select * from t1 where name like "jin%";

group by 分组

'''
分组发生在where之后，就是基于where之后得到的记录而进行的
分组指的是 将所有的记录按照某个相同的字段进行归类
可以按照任意字段分类，但是分组完成后比如 group by post 只能查看post字段，如果想查看组内的信息需要借助于聚合函数
不要用unique 的字段作为分组依据，是的话会一条记录为一个组，因为是唯一的
多条记录之间的某个字段值相同，通常用该字段作为分组依据
查询语句不使用group by时默认为一组
聚合函数聚合的是组的内容
'''
select * from t1 group by post; 现在只显示每个组的第一条数据
set global sql mode="ONLY_FULL_GROUP_BY"; 设置模式为严格匹配，设置后只能取分组的字段，也就是只能查分组的字段以及每个组的聚合结果

聚合函数 max min avg sum count

select post,count(id) from t1 group by post;
计算每个部门的人数，count计数

group_concat()函数

select post,group_concat(name) from t1 group by post;# 显示每个组的员工姓名,group_concat()显示每个组内的字段

having 过滤

having 和 where的区别
1.执行优先级不一样，where >group>having
2.whrer 发生在group by之前 因此where中可以有任意字段，但是不能使用聚合函数
3.having 发生在group by之后 因此having只能使用分组的字段，无法直接取到其他字段可以使用聚合函数

查询各岗位内包含的员工个数小于2的岗位名 ，岗位内包含员工名字 ，个数
seletc post,group_concat(name),count(id) from t1 group by post having count(id)<2;

order by 排序

select * from t1 order by age asc;asc 升序  默认就是升序
select * from t1 order by age desc; desc 降序
select * from t1 order by age asc，id desc;先按age升序排如果age一样按id降序排

limit 限制显示行数

seletc * from t1 limit 3; 显示前5行数据
select * from t1 limit 0,5;  从0开始显示5条
select * from info limt 3 offset 2; 从位置2开始 向后获取3条数据

正则查询

select * from t1 where name like 'jin%';
select * from t1 where name regexp '^jin';
select * from t1 where name regexp '^jin.*(g|n)$';找jin开头 g或者n结尾的

映射

筛选id name 新建一个列为v1 如果 depart 等于1 显示为第一部门 否则显示为第二部门
select id,name case depart_id when 1 then '第一部门' else '第二部门' end v1 from info;

Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数
CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END
Case搜索函数 
CASE 
    WHEN sex = '1' THEN '男' 
    WHEN sex = '2' THEN '女' 
ELSE '其他' END

多表查询

在笛卡尔积的基础上进行数据的筛选
笛卡尔积就是左表的一条数据 分别对应右表的所有记录 如左表有 1 2 3 右表有 4 5 6 笛卡尔积后就是 
14 15 16 24 25 26 34 35 36

内连接 inner join

只取两张表共有的记录
on是连接条件
select * from t1 inner join t2 on t1.id = t2.id;

左外连接

在内连接的基础上保留左表的记录,即使左表的记录和右表毫无记录 也保留
left join 等于 left outer join
select * from t1 left join t2 on t1.id = t2.id;

右外连接

在内连接的基础上保留右表的记录,即使右表的记录和左表毫无记录 也保留
select * from t1 right join t2 on t1.id = t2.id;

全外连接

在内连接的基础上保留左右两表没有对应关系的记录
全外连接又叫上下联表 两个表的列数需要相同
（左连接和右连接结合起来去重即全外连接）union去重
select * from t1 left join t2 on t1.id = t2.id union select * from t1 right join t2 on t1.id = t2.id;

select 语句的定义顺序

select distinct 查询字段
from 表
join 表
on
whrwe
group by
having
order by
limit

select 语句的执行顺序

from  两张表 笛卡尔积
on过滤 链表的条件
添加外部行 这一步只有在连接类型为outer join时才发生，如left outer join ,right outer hoin ,full outer join ，大多数时候会省略掉outer ,但是outer表示的就是外部行的概念
执行where过滤
执行 group by
执行 having
执行select
执行 distinst
执行 oreder by
执行 limit

子查询

将一个查询语句嵌套进另一个查询语句

内层查询语句的结果可以作为外层查询语句提供查询条件

子查询包含 in, not in ,any,all,exists,not exists

还可以包含比较运算符 = > < !=

查询平均年龄在25岁以上的部门
select name from department where id in (
seletc dep_id from employee group by dep_id having avg(age)>25
);
查询大于所有人平均年龄的员工姓名与年龄
seletc name ,age from employee where age >(select avg(age) from employee)

权限管理

mysql默认数据库mysql中的user表中存储着所有的账户信息

创建账号

本地账号：
create user 'yqy'@'localhost' inentified by '123';
远程账号：
create user 'yqy'@'192.168.90.100' identified by '123';  
# ip地址为客户端IP,mysql -uyqy -p123 -h 服务端ip
create user 'yqy'@'192.168.90.%' identified by '123'; 192.168.90.0网段
删除用户：
drop user yqy@127.0.0.1
修改用户：
renmae user '用户名'@'IP地址' to '新用户名'@'IP地址'
修改密码：
set password for '用户名'@'IP地址' = Password('新密码')

授权

自带的mysql授权库里面的表和权限有关
user:*.*  所有库下的所有表都有权限    默认创建的账号存在user表 是没有放权的
db：db.*  库下的所有表都有权限           
tables_priv ： db1.t1  表的权限
columns_priv:  t1.id   字段的权限
grant 权限 on 数据库.表 to '用户'@'IP地址';
grant all privileges on *.* to 'yqy@localhost'; 所有权限
grant select  on *.* to 'yqy@localhost';给yqy账号seletc权限 所有库的所有表
revoke select on *.* from 'yqy@localhost';删除权限
grant select(id,name)   on db1.t1 to  'yqy'@'localhost'; 给yqy账号seletc权限 只能查看t1下的id name字段
flush privilehes; 将数据读到内存中，从而立即生效
查看权限：
show grants from '用户'@'IP地址'
取消授权：
revoke 权限 on 数据库.表 from '用户'@'IP地址'

mysql内置功能

视图

视图就是虚拟表(不是真实存在的)，比如查询出来的结果，将查询出来的结果保存下来 ，视图是用来查询数据的，如果修改的话会涉及到连接表的修改
#创建一张新表为coursetoteacher（表名）内容为查询出来的结果,只有表结构，因为是虚拟表，数据来自其他表
create view coursetoteacher as select * from course inner join teacher on course.teacher_id = teacher.tid;
使用视图：
select * from coursetoteacher;
修改视图：
alter view teachertable as select * from course where cid>3;
删除视图：
drop view teachertable;

触发器

使用触发器可以定制用户对表进行增删改操做时的前后行为，不包括查询

创建触发器

插入前：
create trigger 触发器的名字  before insert on 表名 for each row   （each row 针对每一行）
begin  
    .......(代码)
end
插入后：
create trigger 触发器的名字  after insert on 表名 for each row
begin  
end
删除前：
create trigger 触发器的名字 before delete on 表名 for each row
begin  
end
删除后： 对表的每行进行删除时会触发  begin 到 end 之间代码的执行
create trigger 触发器的名字  after delete on 表名 for each row
begin  
end
更新前：
create trigger 触发器的名字 before update on 表名 for each row
begin  
end
更新后：
create trigger 触发器的名字 after update on 表名 for each row
begin  
end

#准备表
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
#创建触发器
delimiter //   #声明sql语句以//结束不再是;结束
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #等值判断只有一个等号 new对象代表新增的记录,old对象代表旧的记录   new.id 代表新增记录的id字段   then固定语法
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
      END IF ; #必须加分号
END//
delimiter ;
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
rows in set (0.00 sec)
插入后触发触发器

使用触发器

触发器无法由用户直接调用，而是由对表的增删改操做被动触发的

删除触发器

drop trigger 触发器的名字；

存储过程

是一个存储在mysql中的sql语句集合，当主动去调用存储过程时，其中内部的sql语句会按照逻辑执行

参数类型

in 仅用于传入参数用
out 仅用于返回值用
inout 即可以传入又可以当作返回值
delimiter$$
create procedure P1(
    in i1 int, # 定义一个参数i1 int类型 用于接收参数
    inout i2 int, # 参数
    out i3 int # 参数
) 
begin
    declare temp1 int; # 定义一个变量temp1
    declare temp2 int default 0;
    set temp1 = 1; # set用于返回 相当与return
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100
end $$
delimiter ;
===============================================================
set @t1 = 4 ; # 声明一个全局变量叫t1 
set @t2 = 0;
call p2(1,2,@t1,@t2); # 调用存储过程
select @t1,@t2;
==================================================================
pymysql 执行存储过程
import pymysql
conn=pymysql.connect(host='127.0.0.1',port='3306',user='root',passwd='root123',charset='utf-8')
cursor = conn.cursor(cursor=pymysql.cursors.Dictcursor)
# 执行存储过程
cursor.callproc('p2',args=(1,2,3,4))
# 获取执行完存储的参数 @_存储过程名字_传入的参数的索引        
cursor.executer('select @_p2_0,@_p2_1,@_p2_2,@_p2_3')#0是存储过程的第一个参数
result = cursor.fetchall()

创建存储过程

创建存储过程：
delimiter$$
create procedure P1() 名称
begin
    select * from d1;
end $$
delimiter ;
执行存储过程
call P1（）；
删除存储过程
drop procedure proc_name；
pymsql执行存储过程：
cursor.callproc('P1')
cursor.fetchall()

调用存储过程

• mysql中调用
  无参：call p1()
  有参： set @X=0 call p1(2,4,@X); select @x;#查看返回值
• python 中调用
  无参：cursor.callproc(‘p1’)
  有参： cursor.callproc(‘p1’,（2，4，0）) # @_p1_0=2,第一个参数等于2 ，@_p1_1=4,第二个参数等于4
  cursor.excute(‘select@_p1_2’) 查看返回值
  cursor.fetchone()

返回值&结果集

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1; # 结果集 
end $$
delimiter ;

set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2); 会得到返回值 以及结果集
SELECT @t1,@t2;

# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 得到执行存储过中的结果集 也就是（select * from d1）
# 获取执行完存储的参数
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)

事务

事务的四大特型

• 原子性（Atomicity）

是指 事务包含的所有操做不可分隔 要么全部成功，要么全部失败回滚

• 一致性（consistency）

执行的前后数据的完整性保持一致

• 隔离性（isolation）

一个事务的执行过程中，不应该受到其他事务的干扰

• 持久性（durability）

事务一旦结束，数据就持久到数据库

mport pymysql
建立连接
connect = pymysql.connect = (host = '192.168.0.1',port = '3306',user = 'root',pwd = '123',db='db9',charset='utf-8')
拿到游标对象
cursor = connect.cursor()
#开启事务
conn.begin()
try:
    cursor.execute('sql 语句')
    int('asdf')
    cursor.execute('sql语句')
except Exception as e:
    # 回滚
    conn.rollback()
else:
    # 提交
    conn.commit()
cursor.close()
conn.close()

mport pymysql
建立连接
connect = pymysql.connect = (host = '192.168.0.1',port = '3306',user = 'root',pwd = '123',db='db9',charset='utf-8')
拿到游标对象
cursor = connect.cursor()
#开启事务
conn.begin()
try:
    cursor.execute('sql 语句')
    int('asdf')
    cursor.execute('sql语句')
except Exception as e:
    # 回滚
    conn.rollback()
else:
    # 提交
    conn.commit()
cursor.close()
conn.close()

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。（将sql语句绑定到一起都执行成功，或者都不成功）

create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
#原子操作
start transaction; #开启事务
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;#开启的事务不commit 可以回滚，执行commit之后就无法回滚
#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;  # 回退
commit; 
mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | wsb  |    1000 |
|  2 | egon |    1000 |
|  3 | ysb  |    1000 |
+----+------+---------+
rows in set (0.00 sec)

#介绍
delimiter //
            create procedure p4(
                out status int
            )
            BEGIN
                1. 声明如果出现异常则执行{
                    set status = 1;
                    rollback;
                }
                开始事务
                    -- 由秦兵账户减去100
                    -- 方少伟账户加90
                    -- 张根账户加10
                    commit;
                结束
                set status = 2;
            END //
            delimiter ;
#实现
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint #定义返回值
)
BEGIN 
    DECLARE exit handler for sqlexception #检测sql的异常
    BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; #回滚
    END; 
    DECLARE exit handler for sqlwarning 
    BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
    END; 
    START TRANSACTION; 
        DELETE from tb1; #执行失败
        insert into blog(name,sub_time) values('yyy',now());
    COMMIT; 
    -- SUCCESS 
    set p_return_code = 0; #0代表执行成功
END //
delimiter ;
#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;
#在python中基于pymysql调用存储过程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p5_0;')
print(cursor.fetchall())

delimiter $$
create PROCEDURE p4(
    out P_return_code tinyint
)
begin 
    DECLARE exit handler for sqlexception # 定义一个异常
    begion
        --ERROR
        set P_RETURN_CODE =1;
        rollback;
    end;
    DECLARE exit handler for sqlwarning # 定义一个警告
    begin
        --warning
        set P_return_code =2;
        rollback; # 回滚 ，如果事务中存在失败，会将成功的步骤给回滚
    end;
    SATRT TRANSACTION;   # 开启事务
    # 以下的代码出现警告就走 上面定义的警告，要是异常就走上面定义的异常
        DELETE from d1;
        insert into tb(name) values('seven');
    COMMIT; # 提交事务
    --success
    set P_return_code = 0;
    END $$
delimiter ;
set @ret = 100；
call p4（@ret）
select @ret;

游标

游标通常和表结合使用

delimiter $$
create procedure p5()
begin 
    declare sid int; # 生命变量
    declare sname varchar(50); 
    declare done int default false;
    # 定义一个游标叫my_cursor 这个游标时专门用来查询表中的 id，name的
    declare my_cursor CURSOR FOR select id,name from d1;
    # 如果游标查询完了数据会自动将 done 值设置为True
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    # 打开游标
    open my_cursor;
        # 以下是循环 名字叫 XX00
        xxoo: LOOP
            # 每一行去查询id name 查询到的值赋值给 sid，sname
            fetch my_cursor into sid,sname;
            # 判断 done 是否为 True
            IF done then 
                # 离开循环
                leave xxoo;
            END IF;
            # 查询到的值插入到t1表
            insert into t1(name) values(sname);
        end loop xxoo;
    # 关闭游标
    close my_cursor;
end $$
delimiter ;

锁

mysql中自带了锁功能，从锁的范围来说：

表级锁 ：即A操作表的时候，其他人对整个表都不能操作，等待A操作完之后才能继续

行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等A操作完之后才可以继续

MYISAM 支持表锁，不支持行锁
Innodb 引擎支持行锁和表锁
在innodb引擎下如果时基于索引产需的数据则时行级锁，否则就是表锁
在innodb引擎中 update insert delete 的行为内部会先申请锁（排它锁）申请到之后执行相关的操作，最后再释放锁
select 默认不加锁 ，想让select 申请锁，需要 事务 + 特殊语法实现

• 排他锁 for update 加锁之后 其他不可以读写

  begin；
   select * from t1 where name ='XXX' for update; # name 不是索引（表锁）
  commit;

  begin； # 或者 start transaction；
   select * from t1 where id =1 for update; # id 是索引（行锁）
  commit;

  • 场景：
• 共享锁 lock in share mode 加锁之后，其他可读不可写

begin；
    select * from t1 where name ='XXX' lock in share mode ; # name 不是索引（表锁）
commit;

begin； # 或者 start transaction；
    select * from t1 where id =1 for update; # id 是索引（行锁）
commit;

索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

什么是索引？ 加速查查找

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，

索引是基于B+Tree的数据结构实现的，但是再创建数据表时，如果指定不同的引擎，底层使用的B+Tree结构原理存在差异（myicam引擎，非聚簇索引，数据和存储结构分开存储），（innodb引擎，聚簇索引，数据和主键索引结构存储再一起）

优点：数据查找快

缺点：维护额外的数据结构，增删改查的速度变慢，因为每次操做都需要调整整改B+Tree的数据结构关系

无法命中索引的情况：

• 数据类型不一致，在主键索引的情况下数据类型不一致也会命中
  ```python select from big where name = 123; — 未命中 因为类型不一致 name是字符串类型 123是int类型 select from big where email = 123; — 未命中

特殊的主键： select * from big where id = “123”; — 命中 id是整形

-  使用不等于，**在主键索引的情况使用不等于也会命中**  
```python
select * from big where name != "yiiq";     -- 未命中
select * from big where email != "wer";  -- 未命中
特殊的主键：
select * from big where id != 123;    -- 命中 id是主键

• or 当or条件中有未建立索引的列才失效

  select * from big where id = 123 or password="xx";    -- 未命中
  select * from big where name = "qwer" or password="xx";    -- 未命中
  特别的：
  select * from big where id = 10 or password="xx" and name="xx"; -- 命中

• 排序 当根据索引排序的时候，选择的映射（列）如果不是索引则不走索引
  ```python select from big order by name asc; — 未命中 因为显示的所有 select * from big order by name desc; — 未命中

特别的主键： select * from big order by id desc; — 命中

-  like 模糊匹配，通配符在前面无法走索引，在后面可以走索引  
```python
select * from big where name like "%u-12-19999";    -- 未命中
select * from big where name like "_u-12-19999";    -- 未命中
select * from big where name like "wu-%-10";        -- 未命中
特别的：
    select * from big where name like "wu-1111-%";    -- 命中
    select * from big where name like "wuw-%";        -- 命中

• 使用函数
  ```python select * from big where reverse(name) = “wer”; — 未命中

特别的： select * from big where name = reverse(“wer”); — 命中

-  最左前缀，如果是联合索引，要遵循最左前缀原则，（把索引列放在最左边）  
```python
如果联合索引为：(name,password) # 创建name和paswd创建联合错用
    name and password       -- 命中
    name                     -- 命中
    password                -- 未命中
    name or password           -- 未命中

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

B+Tree的查找过程 如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引与辅助索引

在数据库中，B+树的高度一般都在24层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，24次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

• 聚簇索引

#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+tree，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+tree数据结构一样，每个数据页都通过一个双向链表来进行链接。
#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
#由于实际的数据页只能按照一棵B+tree进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+tree索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
-rw-r-----  1 _mysql  _mysql       8684 May 15 22:51 big.frm，表结构。
-rw-r-----  1 _mysql  _mysql  717225984 May 15 22:51 big.ibd，数据和索引结构。
-rw-r-----  1 _mysql  _mysql       8588 May 16 11:38 goods.frm
-rw-r-----  1 _mysql  _mysql      98304 May 16 11:39 goods.ibd
-rw-r-----  1 _mysql  _mysql       8586 May 26 10:57 t2.frm，表结构
-rw-r-----  1 _mysql  _mysql          0 May 26 10:57 t2.MYD，数据
-rw-r-----  1 _mysql  _mysql       1024 May 26 10:57 t2.MYI，索引结构

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

#参照第六小结测试索引的准备阶段来创建出表s1
mysql> desc s1; #最开始没有主键
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> explain select * from s1 order by id desc limit 10; #Using filesort，需要二次排序
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2633472 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.11 sec)
mysql> alter table s1 add primary key(id); #添加主键
Query OK, 0 rows affected (13.37 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from s1 order by id desc limit 10; #基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

mysql> alter table s1 drop primary key;
Query OK, 2699998 rows affected (24.23 sec)
Records: 2699998  Duplicates: 0  Warnings: 0
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)
mysql> explain select * from s1 where id > 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2690100 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (16.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1343355 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.09 sec)