MySQL知识点

二十二、主题:字段约束条件

• 什么是字段约束

简而言之，字段约束就是将字段的内容定一个规则，我们要按照规则办事，常见的字段约束有下面几个。

• 字段约束的作用

1、保证数据的完整性

描述：我们有时候填表会发现有些是必填项，这里就是not null的作用，他要求这个表格不能为空，获取我们完整的信息。

2、保证数据的有效性

描述：在这里我们假设一个个场景，要是张三的电话号码是123×××××××45，那么李四的电话号码绝对不会和张三一摸一样，这里就体现出unique的作用了

• 常用的约束条件作用概述

一、无符号、零填充

无正负符号

存储记录的时候取消正负号，这时候就只能存储0~max的数据值。

关键字：unsigned
	id int unsigned

字段数据长度不够用0填充

昨天学习后我们了解到，int字段括号内数字的作用是控制输出的长度，这里用上zerofill之后，如果输入333就会把前面空着的两个位置用0填充。

关键字：zerofill
	id int(5) zerofill

二、非空

关键字：not null

作用描述：当我们默认情况下创建字段的时候，是可以输入null让值为空的，但是加上not null之后就不能空着不输入该字段的值了，但是我们输入''，这样的空信息却是可以在not null条件下输入进去的，并且在查看的时候该字段在该条记录对应的值就是空的。

create table t1(
	id int,
 	name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);

我们发现默认情况下，也就是不设置字段约束的情况下，字段值是可以为空的，结果如下：

ps:所有字段类型不加约束条件的情况下默认都可以为空

create table t2(
	id int,
 	name varchar(16) not null
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);

这里我们发现添加了字段约束后我们添加记录的时候如果出现空的值就会报错。

三、默认值

关键字：default 默认值

作用描述：当我们设置了默认值字段约束的时候，可以在后面设置默认值，在添加记录的时候如果输入了值，就会使用输入的值，否则就会用默认值填充(可以跟python中的关键字参数类比记忆)。

create table t3(
	id int default 666,
	name varchar(16) default '匿名'
);
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');

四、唯一值

关键字：unique

作用描述：分成单列唯一和多列唯一两种使用方式，单列唯一就很好理解，一个字段内的所有数据值不能重复。多列唯一的意思是设置多列唯一的几个字段中的数据组合起来不能相同。

单列唯一

'''单列唯一'''
create table t4(
	id int unique,
	name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');

这里我们就是用两个相同的数据值测试唯一性。结果如下：

多列唯一

'''联合唯一'''
create table t5(
	id int,
 	ip varchar(32),
 	port int,
 	unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);

这里我们在添加第四条记录的时候会报错，原因就是我们的第四条记录跟第一条记录的ip和port的值组合起来之后重复了。

五、主键

关键字：primary key

作用描述：单从约束层面上而言主键相当于not null + unique(非空且唯一)

ps：主键分成单列主键和联合主键，联合主键跟多列唯一用法一样，但是用的较少。

代码验证主键作用

	create table t6(
    	id int primary key,
    	name varchar(32)
    );
	insert into t6(name) values('jason');
 	insert into t6 values(1,'kevin');
 	insert into t6 values(1,'jerry');

结果如下：

InnoDB存储引擎规定一张表必须有且只有一个主键

​ InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)，因此当我们没有添加主键的时候InnoDB存储引擎会自动设置一个字段当作主键，有以下两种设置方式：

方式一：

​ 当表中没有主键也没有其他非空切唯一的字段的情况下。
​ InnoDB会采用一个隐藏的字段作为表的主键，隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢。

方式二：

​ 当表中没有主键但是有其他非空且唯一的字段，那么会从上往下将第一个该字段自动升级为主键。

    	create table t7(
        	id int,
         	age int not null unique,
          	phone bigint not null unique,
          	birth int not null unique,
         	height int not null unique
        );

ps：我们在创建表的时候应该有一个字段用来标识数据的唯一性，并且该字段通常情况下就是'id'(编号)字段。
id nid sid pid gid uid

0create 0table userinfo(
uid int primary key,
);

六、自增

功能简介

关键字：auto_increment

作用描述：在设置了自增的字段下，我们如果不输入对应的数据值，他会自动给值，同时也会在下一次记录数据值的时候自动把上一次的值加一当作结果绑定给下一次的记录，前提是这一次记录数据值的时候也没有给定值。

特殊情况1

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，后续添加记录的时候如果没有给定id 的值，就会从200开始自增。

特殊情况2

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，如果再次添加一条记录id的值也是200的话，就会报错。

特殊情况3

把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，接着再添加几条数据值，让他自增，接着我们删除id为200的记录，我们就可以在添加记录的时候重新添加一条id字段值为200的记录。

ps：该约束条件不能单独出现，并且一张表中只能出现一次，主要就是配合主键一起用。

create table t8(
	id int primary key,
 	name varchar(32)
);

create table t9(
	id int primary key auto_increment,
 	name varchar(32)
);
	insert into t8 values(200,'zzh');
	insert into t8(name) values('jason');

结果如下：

特性描述

自增特性
自增不会因为数据的删除而回退 永远自增往前
如果自己设置了更大的数 则之后按照更大的往前自增

如果想重置某张表的主键值 可以使用

truncate t9; 

清空表数据并重置主键。

七、外键前戏

1、外键的定义

外键是某个表中的一列，它包含在另一个表的主键中。

外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。

一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

2、外键的作用

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：

阻止执行

从表插入新行，其外键值不是主表的主键值便阻止插入。
从表修改外键值，新值不是主表的主键值便阻止修改。
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

这里需要提前设置

on update cascade
on delete cascade

关联主表和从表

主表删除行，连带从表的相关行一起删除。
主表修改主键值，连带从表相关行的外键值一起修改。

3、外键创建限制

父表必须已经存在于数据库中，或者是当前正在创建的表。

如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

外键中列的数目必须和父表的主键中列的数目相同。

两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键。

外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和tinyint 可以，而 int 和 char 则不可以；

4、例子引入

我们需要一张员工表
id name age dep_name（部门名称） dep_desc（部门功能）

出现的问题：

1.表语义不明确(到底是员工表还是部门表)

2.存取数据过于冗余(浪费存储空间)，如部门名称和功能重复出现。

3.数据的扩展性极差（修改的时候会遇到很的问题，比较繁琐）

解决方案

将上述表一分为二
员工表：id name age
部门表：id dep_name dep_desc

上述的三个问题全部解决，但是员工跟部门之间没有了关系，接着我们引出外键来解决这个问题：

外键字段:用于标识数据与数据之间关系的字段。

八 、表关系的判断

表关系、数据关系其实意思是一样的，只是知识说法上有区分。

关系总共有四种：

• 一对多
• 多对多
• 一对一
• 没有关系

关系的判断可以采用'换位思考'原则

九、一对多关系

以员工表和部门表为例
1.先站在员工表的角度
问:一名员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多名员工
答:可以
结论:一个可以一个不可以 那么关系就是'一对多'
针对'一对多'关系 外键字段建在'多'的一方

十、外键字段的建立

因为外键字段需要引入其他表中的字段，所以我们应该先创建没有外键的表，再创建含有外键的表。

小技巧:先定义出含有普通字段的表 之后再考虑外键字段的添加

create table emp(
	id int primary key auto_increment,
 	name varchar(32),
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除

针对3有简化措施>>>:级联更新级联删除

create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

这里我们可以看到外键的key值是MUL，意为复合约束条件

• 外键其实是强耦合，不符合解耦合的特性，会导致维护难度和成本变高。
• 所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系

十一、多对多关系

以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以

结论:两个都可以，关系就是'多对多'

针对'多对多'关系不能在表中直接创建，需要新建第三张关系表。

create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);
create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(
	id int primary key auto_increment,
   author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);

十二、一对一关系

以用户表与用户详情表为例

1.先站在用户表的角度

​ 问:一个用户能否对应多个用户详情
​ 答:不可以

2.再站在用户详情表的角度

​ 问:一个用户详情能否对应多个用户
​ 答:不可以

结论:两个都可以 关系就是'一对一'或者没有关系

针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

二十三、SQL语句查询关键字

关键词：select和from

作用描述：from的作用是指定需要查询信息的表，select的作用是指定需要查询的字段信息，根据字段信息获取内容

select
	指定需要查询的字段信息
  	select *					查询所有字段的信息
    select name 				查询name字段的信息
    select char_length(name) = 4
    支持对字段做处理，比如这里是查找name字段中长度为4的记录

from
	指定需要查询的表信息
  	from mysql.user
    from t1

SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
	eg:
    select id,name from userinfo;

• 比如这里就是先执行from查找到表中的信息，然后再根据select跟后面的字段名称查找对应的id和name信息。

• 对应关键字的编写顺序和执行顺序我们没必要过多的在意，熟练之后会非常自然的编写，我们只需要把注意力放在每个关键字的功能上即可。

二十四、前期数据准备

• 为后面的查询准备的数据值，cv复制执行即可

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

二十五、编写SQL语句的小技巧

• 针对select后面的字段名可以先用*占位往后写 最后再回来修改

select * from 表名称

• 在实际应用中select后面很少直接写* 因为*表示所有，当表中字段和数据都特别多的情况下非常浪费数据库资源（也就是说记录特别多的时候就会显示不下，造成数据显示混乱，但是可以用\G来分行查看）。
• SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要反反复复的修修补补。

二十六、查询关键字之where筛选

关键词：where

使用描述：where后面跟上条件，根据条件查找信息

1.查询id大于等于3小于等于6的数据

select * from emp where id >= 3 and id <= 6;  支持逻辑运算符（如<>表示不等于等）
select * from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据

select * from emp where salary=20000 or salary=18000 or salary=17000;

select * from emp where salary in (20000,18000,17000); 支持成员运算

3.查询id小于3大于6的数据

select * from emp where id<3 or id>6;

select * from emp where id not between 3 and 6;

4.查询员工姓名中包含字母o的员工姓名与薪资

因为查找条件不够精确的查询，我们称之为——模糊查询。

模糊查询的关键字：

• like

模糊查询的常用符号 ：

• %:匹配任意个数的任意字符
• _:匹配单个个数的任意字符

eg: 

    %o%			o jason owen loo wwoww
    %o			o	asdasdo asdo
    
    _o_			aox	wob iok 
    o_			oi ok ol
    select * from emp where name like '%o%';

5.查询员工姓名是由四个字符组成的员工姓名与其薪资

select * from emp where name like '____';
select name,salary from emp where name like '____';

select * from emp where char_length(name) = 4;
select name,salary from emp where char_length(name)=4;

6.查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is

select * from emp where post_comment=NULL;  		不可以
select * from emp where post_comment is NULL;		可以

在MySQL中也有很多内置方法，我们可以通过查看帮助手册学习。同时我们可以使用如下方法，查看不同方法名的简介：

help 方法名

二十七、查询关键字之group by分组

group by介绍

关键词：group by

使用描述：group by 后面接上条件，根据条件进行筛选，同时除了5.6版本，别的版本需要把select后面的* 号改成固定的字段。

分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
将班级学生按照性别分组
将全国人民按照民族分组
将全世界的人按照肤色分组

分组的目的是为了更好的统计相关数据
eg:
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资

聚合函数

​ 专门用于分组之后的数据统计

例子

1.将员工数据按照部门分组

select * from emp group by post;

• 小知识点

​ 输入上面的代码的时候，MySQL5.6默认不会报错，MySQL5.7及8.0默认都会直接报错。
​ 原因是分组之后，select后面默认只能直接填写分组的依据，不能再写其他字段。

5.6版本如果需要修改成跟其他版本一样的话需要修改配置：

set global sql_mode='strict_trans_tables,only_full_group_by'

接着使用下方代码即可正确查询

select post from emp group by post;

select age from emp group by age;

ps：分组之后默认的最小单位就应该是组，而不应该再是组内的单个数据单个字段。

2.获取每个部门的最高工资

要不要分组我们完全可以从题目的需求中分析出来，尤其是出现关键字：每个、平均等词的时候。

select post,max(salary) from emp group by post;

针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;

3.一次获取部门薪资相关统计

select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;

4.统计每个部门的人数

select post,count(id) from emp group by post;

5.统计每个部门的部门名称以及部门下的员工姓名

分组以外的字段无法直接填写，因此我们需要借助于方法。

group_concat作用：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

select post,name from emp group by post;
这里只能看到每个部门中的一个名字

select post,group_concat(name) from emp group by post;
使用了group_concat方法后就可以获得每个部门的所有名字

group_concat方法还可以获取多个字段的值。

select post,group_concat(name,age) from emp group by post;

group_concat方法获取的字段值可以拼接。

select post,group_concat(name,'|',age) from emp group by post;

select post,group_concat(name,'_NB') from emp group by post;

select post,group_concat('DSB_',name,'_NB') from emp group by post;

二十八、查询关键字之having过滤

关键词：having

作用描述：

• having与where本质是一样的 都是用来对数据做筛选
• 只不过where用在分组之前(首次筛选)，having用在分组之后(二次筛选)

题目：统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据

'''
	稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
	每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
步骤1:先筛选出所有年龄大于30岁的员工数据
	select * from emp where age > 30;
    
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
	select post,avg(salary) from emp where age > 30 group by post;
    
步骤3:针对分组统计之后的结果做二次筛选
	select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

二十九、查询关键字之distinct去重

关键词：distinct

作用描述：类似python中的集合的去重作用。

ps：去重有一个必须的条件，也是很容易被忽略的条件——数据必须一模一样才可以去重

select distinct id,age from emp; 

当我们的去重关键字后跟上多个字段后，去重的对象就会变成这些字段组合起来之后的结果，如果组合起来的结果出现了重复，才会去重。
关键字后跟上单个字段的时候就会针对单个字段内的值进行去重。

select distinct age from emp; 

select distinct age,post from emp;

三十、查询关键字之order by排序

概念讲解

关键词：order by

作用描述：关键词后面跟上字段名称，会根据字段的值进行排序，默认情况下是升序，字段名称后面跟上asc也是升序，字段名称后面跟上desc就会变成降序。

举例：

1.可以是单个字段排序

select * from emp order by age;  默认升序
select * from emp order by age asc;  默认升序(asc可以省略)
select * from emp order by age desc;  降序

2.也可以是多个字段排序

select * from emp order by age,salary desc; 
先按照年龄升序排 相同的情况下再按照薪资降序排

统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

1.先筛选出所有年龄大于10岁的员工
  	select * from emp where age > 10;
2.再对他们按照部门分组统计平均薪资
  	select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
  	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
 	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);

"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""

三十一、查询关键字之limit分页

关键词：limit

作用描述：当表中数据特别多的情况下 我们很少会一次性获取所有的数据，limit的作用就是控制一次性显示多少跳记录。后面跟的数字就是规定显示几条数据。
ps：很多网站也是做了分页处理 一次性只能看一点点

select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5; 从第5条开始往后读取5条

查询工资最高的人的详细信息
'''千万不要惯性思维 一看到工资最高就想着用分组聚合'''

select * from emp order by salary desc limit 1;

三十二、查询关键字之regexp正则表达式

关键词：regexp

作用描述：关键词后面接上正则表达式进行筛选。

SQL语句的模糊匹配如果用不习惯，也可以自己写正则批量查询。

ps：8.0之前的MySQL无法理解?运算符。

select * from emp where name regexp '^j.*?(n|y)$';

select * from emp where name regexp '^j.*(n|y)$';

三十三、多表查询的思路

表数据准备

create table dep(
  id int primary key auto_increment,
  name varchar(16) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

多表查询

select * from emp,dep;  
会将两张表中所有的数据对应一遍

这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义，应该将有关系的数据对应到一起才合理。

笛卡尔积概念

首先，先简单解释一下笛卡尔积。现在，我们有两个集合A和B。

A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

可以得出A×B和B×A的笛卡尔积，但总体思路为用

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。

从以上的数据分析我们可以得出以下两点结论：

1，两个集合相乘，不满足交换率，既 A×B ≠ B×A;

2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;

基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来

涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定

select * from emp,dep where emp.dep_id=dep.id;

基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

三十四、多表查询的两种方法

方式1:连表操作

inner join(内连接)

select * from emp inner join dep on emp.dep_id=dep.id;

只连接两张表中公有的数据部分

left join(左连接)

select * from emp left join dep on emp.dep_id=dep.id;

以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充

right join（右连接）

select * from emp right join dep on emp.dep_id=dep.id;

以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充

union（全连接）

select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;

以左右表为基准 展示所有的数据 各自没有的全部NULL填充

学会了连表操作之后也就可以连接N多张表

思路:将拼接之后的表起别名当成一张表再去与其他表拼接，再起别名当一张表，再去与其他表拼接，其次往复即可。

方式2:子查询

将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件

题目:求姓名是jason的员工部门名称

ps：子查询类似于我们日常生活中解决问题的方式>>>:分步操作

步骤1:先根据jason获取部门编号

select dep_id from emp where name='jason';

步骤2:再根据部门编号获取部门名称

select name from dep where id=200;

步骤3：整合上面的代码

select name from dep where id=(select dep_id from emp where name='jason');

很多时候多表查询需要结合实际情况判断用哪种，更多时候甚至是相互配合使用。

三十五、小知识点补充说明

1.concat与concat_ws

之前我们学习到group_concat()可以获得分组字段以外的字段数据，并支持拼接。

concat和concat_ws也是用于获取字段数据，并支持拼接。

concat用于分组之前的字段拼接操作

select concat(name,'$',sex) from emp;

concat_ws拼接多个字段并且中间的连接符一致

select concat_ws('|',name,sex,age,dep_id) from emp;

2.exists

sql1 exists (sql2);

select * from dep where exists (select * from emp where emp.id>1);

sql2有结果的情况下才会执行sql1，否则不执行sql1，返回空数据。

3.表相关SQL补充

alter table 表名 rename 新表名;  # 修改表名
alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;# 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
alter table 表名 drop 字段名;  # 删除字段

三十六、可视化软件Navicat

1、Navicat简介

Navicat 是一套快速、可靠并价格适宜的数据库管理工具, 适用于三种平台: Windows、mac OS 及 Linux。 可以用来对本机或远程的 MySQL、SQL Server、SQLite、Oracle 等数据库进行管理及开发。 专门为简化数据库的操作而设计。 它的设计符合数据库管理员、开发人员及中小企业的需求。 Navicat 是一个数据库图形化客户端软件, 让你可以以安全并且简单的方式对数据库进行操作。

能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat

ps：Navicat是一个第三方数据库客户端，提供简单快捷的操作界面，本质还是使用sql语言对数据库进行操作。

2、安装教程

正版教程

官网地址：http://www.navicat.com.cn/

步骤一：

进入官网点击上方的产品

步骤二：

如果想要先使用可以点击免费使用，如果想要直接购买的可以点击购买，试用满十四天后就需要收费了。

步骤三：

参考破解版的安装教程

破解版教程

文件获取和简单教程可以参考这两个网站。

https://www.jb51.net/database/803226.html

https://www.jb51.net/softjc/823874.html

步骤一：

获取文件，下载后解压。然后双击运行navicat161_premium_cs_x64.exe

步骤二：

点击下一步，并同意规定，然后选择安装路径（安装路径后面要用到）

步骤三：

然后一路下一步一路到安装成功。

步骤四：

解压NavicatCracker.rar，然后打开解压得到的文件夹中的NavicatCracker v16.0.7.0 .exe

步骤五：

出现窗口后点击patch边上的两个点，把目录选择到navicat的安装目录

步骤六：

接着点击patch，如果成功了会有一个如下的弹窗，然后点击是

步骤七：

接着断网，然后点击generate获得激活码

步骤八：

然后打开navicat软件，点击注册，在激活码处输入获得的激活码

步骤九：

点击激活后会出现一个弹窗，点击手动激活，这时候会再出现一个弹窗

步骤十：

把新出现的弹窗中的请求码复制，然后粘贴到request code的窗口中

步骤十一：

点击Generate Activation Code按钮，这时候会在Activation Code中生成一串字符，把他复制然后粘贴到请求码的窗口下方

步骤十二：

点击激活就可以永久激活，但是不支持更新。

3、常用操作

1.连接数据库

步骤一：

打开软件后点击左上角的连接然后点击MySQL

步骤二：

输入用户名和密码，然后点击测试连接，试试看能不能连上，如果可以就点击确定创建连接

步骤三：

接下去会出现如下界面，这就表示连接数据库成功了

2.创建库

前置条件：连接数据库

步骤一：

选中连接，右键点击新建数据库，这时候会出现一个弹窗

步骤二：

填写数据库名称和字符集（就是字符编码类型，建议选utf8或utf8mb4），排序规则不要动，让他空着

步骤三：

点击确定就可以在左边看到新创的数据库了

3.创建表

步骤一：

双击一个数据库

步骤二：

点击表，然后右键点击新建表

步骤三：

这时候右边窗口会出现创建表的窗口，我们在内部可以更简单的创建表，创建表的时候需要设置上主键（使用tab可以换到下一行）

步骤四：

设置好了之后点击保存就可以创建出这个表了，我们可以在左边查看到表的信息

4.往表中添加记录

在左边双击表，就能开始添加表的记录

5.设置外键

需要在创建表的时候就进行设置

ps：还有很多实用的功能，可以自行探索，比如编写sql代码会出现提示可以用Tab补全，类似pycharm。

4.SQL语句注释语法

方式一：--

在一行代码的开头加上两个横杠就可以将其注释

注释快捷键就是用这种方式，使用ctrl+？是将代码注释，使用ctrl+shift+？是解开注释

方式二：#

在一行代码的开头加上一个井号就可以将其注释

方式三：\**\

这是sql代码中的多行注释

5.运行、转储SQL文件

在左侧选中一个数据库，然后右键点击运行/转储SQL文件就可以导入或是到处数据库文件

三十七、多表查询练习题

提示：

• 编写复杂的SQL不要想着一口气写完
• 一定要先明确思路 然后一步步写一步步查一步步补

题目：

1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级

-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表  课程表 分数表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- SELECT
-- 	teacher.tname,
-- 	course.cname
-- FROM
-- 	course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
-- SELECT
-- 	student.sname,
-- 	t1.avg_num
-- FROM
-- 	student
-- INNER JOIN (
-- 	SELECT
-- 		student_id,
-- 		avg(num) AS avg_num
-- 	FROM
-- 		score
-- 	GROUP BY
-- 		student_id
-- 	HAVING
-- 		avg_num > 80
-- ) AS t1 ON student.sid = t1.student_id;
-- 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid NOT IN (
-- 		SELECT DISTINCT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					teacher_id = (
-- 						SELECT
-- 							tid
-- 						FROM
-- 							teacher
-- 						WHERE
-- 							tname = '李平老师'
-- 					)
-- 			)
-- 	)
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid IN (
-- 		SELECT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					cname IN ('物理', '体育')
-- 			)
-- 		GROUP BY
-- 			student_id
-- 		HAVING
-- 			count(course_id) = 1
-- 	) 
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);

三十八、python操作MySQL

需要用到一个第三方模块——pymysql，需要导入，通过pymysql模块就可以充当客户端存储数据了。

部分方法简介

代码实操

pymysql模块
	pip3 install pymysql
import pymysql

# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='976863429',
    db='infor-1',
    charset='utf8mb4',
    autocommit=True  # 针对增 删 改 自动确认
)
# 2.产生游标对象
# cursor = conn.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
sql = 'select * from teacher;'  # 查询是可以
# sql = 'insert into teacher(tname) values("Jason老师");'  # 插入不可以
# sql = 'update teacher set tname="tony老师" where tid=7;'  # 修改不可以
# sql = 'delete from teacher where tid=7;'  # 删除不可以
# sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)

# conn.commit()  # 针对 增 删 改 需要二次确认
# 5.获取SQL语句执行之后的结果
res1 = cursor.fetchall()
# res2 = cursor.fetchall()
# res3 = cursor.fetchall()
print(res1)
# print(res2)
# print(res3)
# res1 = cursor.fetchone()
# print(res1)
# res2 = cursor.fetchone()
# print(res2)
# res2 = cursor.fetchmany(2)
# print(res2)

# res1 = cursor.fetchall()
# print(res1)
# cursor.scroll(1,'relative')  # 基于当前位置往后移动
# cursor.scroll(-1, 'relative')  # 基于当前位置往后移动
# cursor.scroll(0,'absolute')
# res1 = cursor.fetchall()
# print(res1)

三十九、pymysql补充说明

1.获取数据
    fetchall()  获取所有的结果
    fetchone()	获取结果集的第一个数据
    fetchmany() 获取指定数量的结果集
    ps:注意三者都有类似于文件光标移动的特性
	cursor.scroll(-1, 'relative') # 基于末尾移动
    cursor.scroll(1,'relative')  # 基于当前位置往后移动
    cursor.scroll(0,'absolute')  # 基于数据的开头往后移动
 
2.增删改查
	 autocommit=True  # 针对增 删 改 自动确认(直接配置)
  	 conn.commit()  # 针对 增 删 改 需要二次确认(代码确认)

四十、SQL注入问题

SQL注入问题引入

我们在pycharm中使用pymysql模块执行mysql代码的时候会发现用占位符传输信息的时候若是按下方的代码输入内容，会出现下面的两个怪象。

sql = " insert into userinfo(name,pwd) values(%s,%s)" % (sign_name,sign_pwd)

sign_name = 'jason' -- kasdjksajd'
sign_pwd = ''

sign_name = 'xyz' or 1=1 -- aksdjasldj'
sign_pwd = ''

怪象1:输对用户名就可以登录成功
怪象2:不需要对的用户名和密码也可以登录成功

这时候pycharm会在报错信息中返回一个sql语句，内容如下：

select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd='' 

讲解：

通过上面的语句，我们可以发现语句的中间出现了两个'--'，而这个--符号的意思是注释的作用，因此就相当于执行了--符号前面的语句，后面的内容成了注释内容，这种情况就叫做SQL注入。

SQL注入概念和解决方案

• 概念

SQL注入:利用特殊符号的组合产生特殊的含义，从而避开正常的业务逻辑

• 解决方案

针对上述的SQL注入问题，核心在于手动拼接了关键数据，交给execute处理即可
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))

补充说明

我们也可以使用executemany来一次性处理多个数据，把多个数据填入下方的小括号的即可。

executemany(sql,[(),(),(),()...])

四十一、视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用，通常用于连表操作得到的结果。语句如下：

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

注意事项

1.视图的表只能用来查询不能做其他增删改操作
2.视图尽量少用，会跟真正的表产生混淆，从而干扰操作者（使用show tables查看表名称或是进行其他操作的时候跟普通的表没有区别）。

四十二、触发器

定义

触发器是由事件来触发某个操作。当数据库执行这些事件时，就会激活触发器来执行相应的操作。这些事件称为触发条件，在MySQL中，有UPDATE，INSERT，和DELETE（即对表进行增、删、改）。

主要有六种情况:增前、增后、删前、删后、改前、改后（BEFORE INSERT、BEFORE DELETE、BEFORE UPDATE、AFTER INSERT、AFTER DELETE、AFTER UPDATE）。

代码

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end

1.触发器命名有一定的规律

tri_before_insert_t1
tri_after_delete_t2
tri_after_update_t2

讲解：

这里我们发现第一部分名称就是tirgger关键字的前三个字母，第二部分名称是出发的状态，这部分名称由两部分组成，最后的一部分名称我们可以自定义。

2.临时修改SQL语句的结束符

• 因为有些操作中需要使用分号，这时候就会出现冲突。
• 这里我们用delimiter关键字修改结束符号，通过这种方式达到目的。

触发器实际应用

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') #no代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象，如果新纪录中success的值为no就会把数据也记录到errlog表中
        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
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;

四十三、事务

MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT

事务的四大特性(ACID)

• A:原子性

​ 指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

特点：

1、事务是一个完整的操作，事务的各元素是不可分的。
2、事务中的所有元素必须作为一个整体提交或回滚。
3、如果事务中的任何元素失败，则整个事务将失败。

举例：

​ 比如转账的时候，如果对方接收失败了，钱应该退回自己的账户中。让双方的数据都回到转账前的状态。

• C:一致性

​ 指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。一致性与原子性是密切相关的。

特点：

1、当事务完成时，数据必须处于一致状态。
2、在事务开始前，数据库中存储的数据处于一致状态。
3、在正在进行的事务中，数据可能处于不一致的状态。
4、当事务成功完成时，数据必须再次回到已知的一致状态。

举例：

​ 对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

• I:隔离性

​ 指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间（多个事务之间彼此不干扰）。

特点：

1、对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
2、修改数据的事务可在另一个使用相同数据的事务开始之前访问
3、这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

• D:持久性

​ 持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被回滚。

特点：

1、指不管系统是否发生故障，事务处理的结果都是永久的。
2、一旦事务被提交，事务的效果会被永久地保留在数据库中。

代码

transaction是事务功能的关键字

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;

事务相关关键字

  事务(transaction)
  回退(rollback)
  提交(commit)
  保留点(savepoint)
	为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint
    	savepoint sp01;
    回退到占位符地址
    	rollback to sp01;

保留点在执行rollback或者commit之后自动释放

事务之间的相互影响

1、脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。

2、不可重复读：一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

3、幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

4、丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别

设置隔离级别语句：

set transaction isolation level 级别

查询全局事务隔离级别

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别

​ MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username

​ create_version

​ delete_version
​ jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
​ username

​ create_version

​ delete_version

​ jason 1

​ 2
​ jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
​ username

​ create_version

​ delete_version
​ jason01 2

​ 3

由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。

四十四、存储过程

​ 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。

​ 可以看成是python中的自定义函数。

# 无参函数
delimiter $$
create procedure p1()
begin
	select * from cmd;
end $$
delimiter ;

# 调用
call p1()


# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from cmd where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

"""
查看存储过程具体信息
	show create procedure pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

四十五、函数

可以看成是python中的内置函数

ps:可以通过help 函数名 查看帮助信息!

1.移除指定字符

Trim、LTrim、RTrim

2.大小写转换

Lower、Upper

3.获取左右起始指定个数字符

Left、Right

4.返回读音相似值(对英文效果)

Soundex

eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的

where Soundex(name)=Soundex('J.Lie')

​

5.日期格式:date_format

'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值

四十六、流程控制

# 分支结构
declare i int default 0;
IF i = 1 THEN
	SELECT 1;
ELSEIF i = 2 THEN
	SELECT 2;
ELSE
	SELECT 7;
END IF;

# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
	SELECT num ;
	SET num = num + 1 ;
END WHILE ;

四十七、索引相关概念

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容
2）让获取的数据更有目的性，从而提高数据库检索数据的性能

MySQL索引主要有两种结构：B+Tree索引和Hash索引。

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构

	* primary key
	* unique key
	* index key

1.上述的三个key都可以加快数据查询
2.primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
3.外键不属于索引键的范围 是用来建立关系的 与加快查询无关

索引加快查询的本质

	id int primary key auto_increment,
 	name varchar(32) unique,
  	province varchar(32)
 	age int
 	phone bigint
 	
	select name from userinfo where phone=18818888888;  # 一页页的翻
	select name from userinfo where id=99999;  # 按照目录确定页数找

索引可以加快数据查询 但是会降低增删的速度
通常情况下我们频繁使用某些字段查询数据
为了提升查询的速度可以将该字段建立索引

聚集索引(primary key)
主键、主键索引
辅助索引(unique,index)
除主键意外的都是辅助索引，辅助索引其实本质上也是用的聚集索引的数据来查询的（用的主键的数据）

覆盖索引

select name from user where name='jason';

所谓覆盖索引就是条件中的字段名和查看的字段名称相同

非覆盖索引

select age from user where name='jason';

四十八、索引数据结构

索引的底层其实是树结构>>>:树是计算机底层的数据结构

树有很多中类型

​ 二叉树、b树、b+树、B*树......

二叉树

二叉树里面还可以细分成很多领域，我们简单的了解即可。

二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

B树（B-Tree）

MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找树，主要的特点是：

1、叶子节点都在同一层，叶子节点没有指针连接
2、B树的节点中存储着多个元素，每个内节点有多个分叉
3、节点中的元素包含键值和数据，节点中的键值从大到小排列

4、所有的节点都可以存放完整的数据

下面模拟下查找key为27的data的过程：

存在的一些问题：

• B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响查询效率。
• 不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

B+\B*树

​ 只有叶子节点才会存放真正的数据 其他节点只存放索引数据

B+树

B+叶子节点增加了指向其他叶子节点的指针

对比B树和B+树，我们发现二者主要存在以下几点不同的地方：

• 数据都存放在叶子节点中
• 非叶子节点只存储键值信息，不再存储数据
• 所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表

等值查询

下面模拟下查找key为9的data的过程：

范围查询

下面模拟下查找key的范围为9到26这个范围的data的过程：

从上面的结果，我们可以知道B+树作为索引结构带来的好处：

• 磁盘IO次数更少
• 数据遍历更为方便
• 查询性能更稳定

由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。

B*树

​ B*叶子节点和枝节点都有指向其他节点的指针

ps：

• 辅助索引在查询数据的时候最会还是需要借助于聚集索引
• 辅助索引叶子节点存放的是数据的主键值
• 有时候就算采用索引字段查询数据 也可能不会走索引!!!

索引失效的几种情况

1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

     'A%'就可以正常使用索引

2.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

3.OR前后存在非索引的列，索引失效

​ 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

​ 要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

4.普通索引的不等于不会走索引；如果是主键，则还是会走索引；如果是主键或索引是整数类型，则还是会走索引

5.组合索引最左前缀

    如果组合索引为：(name,email)

    name and email -- 使用索引

    name -- 使用索引

    email -- 不使用索引

6.is null可以使用索引，is not null无法使用索引

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。

7.计算、函数导致索引失效另外一种情况

#使用到了索引

explain select * from student_info where name like 'li%';

#未使用索引，花费时间更久

explain select * from student_info where LEFT(name,2)='li';

扩展：

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

#不会使用name的索引

explain select * from student_info where name=123;

#使用到索引

explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。这也相当于是函数导致的索引失效。

8.字符集不统一

统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。。

ps：最好能记三个左右的特殊情况

四十九、慢查询优化

我们使用explain方法来查看语句的索引扫描类型。

1、Explain是什么？

• 主键自动建立唯一索引。
• 频繁作为查询条件的字段应该创建索引。
• 查询中与其他表关联的字段，外键关系建立索引。
• 单键/组合索引的选择问题，组合索引性价比更高。
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
• 查询中统计或者分组字段。

2、Explain能干什么？

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引可以被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

3、怎么用

• explain + SQL
• 执行计划包含的信息

+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+

4、常见的索引扫描类型:

1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

索引扫描类型详解

• 生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

• 从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

key_len: 越小越好

• 前缀索引去控制

rows: 越小越好

五十、测试索引

准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
# 由于这里要加三百万调记录要很久
#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

1、联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！

2、全文检索

使用Mysql全文检索fulltext的先决条件
表的类型必须是MyISAM
建立全文检索的字段类型必须是char,varchar,text

MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
	id int primary key auto_increment,
  content text
	fulltext(content)
)engine=MyISAM;

# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''

# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
	jason is handsome and cool,every one want to be cool,tony want to be more handsome;
	二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""

# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);

# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略

3、插入数据

数据库经常被多个用户访问，insert操作可能会很耗时(特别是有很多索引需要更新的时候)而且还可能降低等待处理的select语句性能
如果数据检索是最重要的(一般都是)，则可以通过在insert与into之间添加关键字low_priority指示MySQL降低insert语句优先级
	insert low_priority  into 
  
insert还可以将一条select语句的结果插入表中即数据导入:insert select
eg:想从custnew表中合并数据到customers表中
  insert into customers(contact,email) select contact,email from custnew;

4、更新数据

如果使用update语句更新多列值，并且在更新这些列中的一列或者多列出现一个错误会导致整个update操作被取消，如果想发生错误也能继续执行没有错误的更新操作可以采用
	update ignore custmoers ...
  """
  update ignore  set name='jason1',id='a' where id=1;
  	name字段正常修改
  update set name='jason2',id='h' where id=1;
  	全部更新失败
  """

5、删除数据

• delete语句从表中删除数据，甚至可以是所有数据但是不会删除表本身
• 并且如果想从表中删除所有的行不要使用delete可以使用truncate速度更快并且会重置主键值(实际是删除原来的表并重新创建一个表而不是逐行删除表中的数据)

6、主键

查看当前表主键自增到的值(表当前主键值减一)
	select last_insert_id();

7、外键

MySQL存储引擎可以混用，但是外键不能跨引擎即使用一个引擎的表不能引用具有使用不同引擎表的外键

8、重命名表

rename关键字可以修改一个或者多个表名
	rename table customer1 to customer2;
	rename table back_cust to b_cust,
	back_cust1 to b_cust1,
	back_cust2 to b_cust2;

9、安全管理

1.创建用户
	create user 用户名 identified by '密码';
 	"""修改密码"""
  	set password for 用户名 = Password('新密码');
    set password = Password('新密码');  # 针对当前登录用户
2.重命名
	rename user 新用户名 to 旧用户名; 
3.删除用户
	drop user 用户名;
4.查看用户访问权限
	show grants for 用户名;
5.授予访问权限
	grant select on db1.* to 用户名;
  # 授予用户对db1数据库下所有表使用select权限
6.撤销权限
	revoke select on db1.* from 用户名;
"""
整个服务器
	grant all/revoke all
整个数据库
	on db.*
特定的表
	on db.t1
"""

10、隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别

1.read uncommitted(未提交读)
	事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读)		# MySQL默认隔离级别
	能够解决"脏读"问题，但是无法解决"幻读"
  所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
	强制事务串行执行，很少使用该级别

11、锁

读锁(共享锁)
	多个用户同一时刻可以同时读取同一个资源互不干扰
写锁(排他锁)
	一个写锁会阻塞其他的写锁和读锁
死锁
	1.多个事务试图以不同的顺序锁定资源时就可能会产生死锁
  2.多个事务同时锁定同一个资源时也会产生死锁
	# Innodb通过将持有最少行级排他锁的事务回滚

12、事务日志

事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
  事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

13、MVCC多版本控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
	一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

14、转换表的引擎

主要有三种方式，并各有优缺点!
# 1.alter table
	alter table t1 engine=InnoDB;
  """
  	适用于任何存储引擎 但是需要执行很长时间 MySQL会按行将数据从原表赋值到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表会加读锁
  """
# 2.导入导出
	"""
	使用mysqldump工具将数据导出到文件，然后修改文件中相应的SQL语句
		1.引擎选项
		2.表名
	""" 	
# 3.insert ... select
	"""
	综合了第一种方案的高效和第二种方案的安全
		1.先创建一张新的表
		2.利用insert ... select语法导数据
	数据量不大这样做非常合适 数据量大可以考虑分批处理 针对每一段数据执行事务提交操作避免产生过多的undo
	"""
  ps:上述操作可以使用pt-online-schema-change(基于facebook的在线schema变更技术)工具，简单方便的执行上述过程

五十一、数据库三大范式

1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。

2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。