数据库基本语法及规范

- 事务
mysql高级篇

基本语法及规范

一、用户及权限

创建用户：create user 用户名@'ip地址' identified by '密码'；
修改用户：alter user 用户名@'ip地址' identified by '密码'；
赋予权限：grant 权限，权限2 on 作用对象 to 用户名@'ip地址'；
查看权限：show grants for 用户名@'ip地址'；
收回权限：revoke 权限 on 作用对象 from 用户名@'ip地址'；

作用对象：库名.表名，如果所有的库中的所有的表使用 . 表示

二、数据库

数据库规范
- 数据库名必须是小写字母
- 数据库名不能是内置的字符
- 数据库名不能以数字开头
- 数据库创建的时候必须设置charset和collate
显示所有数据库
show databases;
显示单个数据库
show create database student;
创建数据库
CREATE DATABASE student;
切换数据库
use test;
修改数据库设置(charset和collate)
alter database student charset utf8mb4 collate utf8mb4_bin

三、表

表规范
- 表名不能以数字开头
- 表名必须是小写
- 表名不能是内置字符
- 建表的时候必须在括号后添加engine InnoDB charset
  utf8mb4，也可以添加校验规则collate utf8mb4_bin
- unsigned表示无符号，通常和数字类型搭配使用，表示不能为负数
- not null和default搭配使用，表示不能为空，如果不填写则使用默认值
- enum()括号中必须是字符，不能为数字
- 每一个字段后必须添加注释comment
- unique key表示唯一健约束，不允许重复
- primary
  key表示主健，一般都用在和主要内容没有关联的数字子段，通常设置在id上，并设置为auto-increment
显示数据库中的所有表
show tables;
创建数据表

REATE TABLE `stu01` (
    id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
    sname varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
    age tinyint(4) NOT NULL DEFAULT 0 COMMENT '年龄',
    gender enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
    intime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

创建一个表结构一模一样的表
create table stu02 like student.stu01;
查看数据表结构
describe student;或者desc student;
查询表
SELECT * from student;
为已创建的表添加新的子段及属性
- 直接在最后一个字段后添加：
  alter table student.stu01 add QQ varchar(20) not null comment 'QQ号';

注意：当执行DDL这种操作的时候，如果是紧急必须修改，那么需要先根据当前表创建一个结构完全相同的临时表，对临时表进行修改操作，
修改完后再把原来的表改成其他的名字，新建临时表为正式使用的表。这样做的原因是如果进行DDL这种操作，mysql会进行锁表的操作，如果
正好遇见业务量繁忙的时候，就会出现用户无法获取信息的情况。当然也可以使用pt-osc工具来解决，mysql8.0以后也解决了这方面的问题。

- 在某一个字段的后添加(after)：
alter table student.stu01 add wechat varchar(64) not null comment '微信号'
after QQ;
- 第一个字段之前添加字段(first):
alter table student.stu01 phone varchar(12) not null comment '手机号'
first;

删除表单中已经创建的字段
alter table student.stu01 drop phone;

alter table student.stu01 drop phone;                                                  
Query OK, 0 rows affected

修改表单中的具体字段属性(modify)
alter table student.stu01 modify sname varchar(100) not null comment
'姓名';

注意：修改具体的属性的时候，需要用alter和modify，修改某一个属性需要把字段所有的属性都写上才可以
修改表单中的字段名(change)
alter table student.stu01 change sname name varchar(100) not null comment
'姓名';

注意：修改名字需要用到alter和change，change后边跟的第一个是原字段名，第二个是新的字段名，后边是这个字段的所有属性。
在已存在的表中添加新的字段
- 插入字段：alter table school.student01 add 字段的属性设置
- 需要注意的是，当插入的字段不设置unique属性的时候，可以正常的插入，但是如果插入的字段设置了unique属性，并且表中也存在数据，那么mysql会报错：1062, "Duplicate entry 'xx' for key 'xx'"

修改表名
alter table 表名 rename 新表名;

  ```
  alter table test rename usertb;
  ```

插入数据的方式
- 最偷懒的方式
  insert student.stu01 values(1,'李振',20,'f',now(),133794793,23793490,12423797223);
- 最规范的方式
  insert into student.stu01(name,age,gender,intime,QQ,wechat,sphone)
  values('刘洋',23,'f',now(),123455323,2343296,12387549321);
注意：最偷懒的方式使用的时候会出现两个问题：1.必须把自增长的字段数据也录入，2.这种偷懒的方式会在中间件等的使用过程中出现问题
最规范的方式是必须在into后把每一个需要的字段按顺序列出，values后按顺序输入数据，另外这种方式不需要写自增长字段信息。
- 一次添加多行的信息
  insert into student.stu01(name,age,gender,intime,QQ,wechat,sphone)
  values('李晓明',28,'m',now(),12342354,3497823940690,21342678321),('柳柳',23,'f',now(),123432,21432324,12372637483);
每一行数据之间用逗号隔开
修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
删除数据
DELETE FROM pet where name = 'squirrel';
删除表
DROP TABLE myorder;

四、查询select/show(DQL):

select单独使用的情况
- 查询数据库的目录
  select @@basedir;
- 查询数据库的端口号
  select @@port
- 查看当前所在的数据库
  select database();
- 查看当前时间
  select now();
- 查询具体的用法或者变量
  show variables like '%变量名%'
通配符%可以省略，也可以加一个或者两个都用

select的通用语法(单表)

select：列
from：表
where：过滤条件
- 精准查询：=、>=、>、<=、<
```
select Name,District,Population from city where countrycode='CHN' limit 34;
select * from city where Population >= 2000000 and CountryCode='CHN' limit 30;
```
- 配合where使用的逻辑连接符：and和or，or和and共同使用的时候可以使用()来实现查询条件的优先级
```
select * from city where (`Population` >= 2000000 or `Population`<1000000) and `CountryCode`='CHN' limit 30;
select * from city where `Population`>=5000000 and `Population`<=6000000;
select * from city where `Population` between 5000000 and 6000000;
```
- 模糊查询：like和%或者下划线，两个必须同时使用
  - %：匹配的是任意的字符，个数不限制
  - _ ：匹配的是任意一个字符，限定只能匹配一个字符
这里需要注意的是：在模糊查询的时候查询信息的前边不要出现%，因为这种情况不走索引。
```
查询表中城市名以C开头的所有城市信息
select * from city where Name like 'C%';
```
- 配合in来使用，in相当于or的作用，需要配合()来使用，()内是所有可能的情况，使用in的查询字段必须是同一个，与or相比有一定的局限性。
```
select Name,Population from city where `CountryCode` in ('CHN','USA') and `Population` > 5000000;
```
group by：配合聚合函数进行使用，使用group by 字段的作用是以该字段来作为条件进行分组，例如以国家来分组必须使用group by CountryCode。
- 常用的聚合函数
  - 最大值：max()
  - 最小值：min()
  - 平均值：avg()
  - 数量：count()
  在求总的数量的时候可能会出现重复的情况，为了避免重复对结果造成的影响，必须在count()中的字段之前加上distinct
```
求所有国家省份的数量
select `CountryCode`,count(distinct `District`) from city group by CountryCode;
求中国每个省的城市个数
select District,count(Name) from city where CountryCode='CHN' group by District;
```
  - 总和：sum()
```
查询所有的国家各自的人口数量
select CountryCode,sum(Population) from city group by CountryCode;
这里用的是sum()函数，group by分组
查询各自的国家的人口数量大于5000000的城市总数
select * from city where Population >= 2000000 group by CountryCode;
求中国的每个省份的人口数量
select `District`,sum(`Population`) from city where CountryCode='CHN' group by `district`;
```
  注意：在使用聚合函数之前必须先使用group by进行分组，分组之后再进行聚合函数的计算，另外就是group by的使用必须放在where查询条件之后才可以使用。
  - 归类：group_concat()，这个函数主要是用于归类，例如求同一个省份中的所有城市的列表，可以通过group_concat()来实现
```
查询中国的所有省份的城市列表
select District, group_concat(Name) from city where CountryCode='CHN' group by District;
```
having：having的用法和where基本一样，不同的是where用在group by之前，having必须用在group by之后，having是在前边过滤之后还需要再过滤的情况下才使用

having在过滤的时候是不走索引的，也就是全表查询，如果查询的数据量很大，对性能会有比较大的影响

查询所有人口数量超过1亿的国家
select `CountryCode`,':',sum(`Population`) from city group by `CountryCode` having sum(`Population`)>100000000;                                 
+-------------+---+-------------------+
| CountryCode | : | sum(`Population`) |
+-------------+---+-------------------+
| CHN         | : | 175953614         |
| IND         | : | 123298526         |
+-------------+---+-------------------+

order by：默认是以升序的方式排列，加上DESC则是以降序的方式来排列

以降序的方式查询出所有总人口数超过50000000的国家的信息
select CountryCode,sum(Population) from city group by CountryCode having 50000000 order by sum(Population) DESC;

limit：限制查询的数据条数

只显示查询结果的前几条数据：limit num

查询所有人口数量大于50000000的国家，降序排列，并只显示人口最多的前5个国家
select `CountryCode`,':',sum(`Population`) from city group by `CountryCode` having sum(`Population`)>50000000 order by sum(`Population`) DESC limit 5;

显示查询结果中的不是开头的具体几条数据

limit num1,num2：num1表示从开头偏移num1行，num2表示偏移num1行后从num1+1行处开始显示num2行

查询所有人口数超过50000000的国家，降序排列，并且显示4-7行的数据信息
select `CountryCode`,':',sum(`Population`) from city group by `CountryCode` having sum(`Population`)>50000000 order by sum(`Population`) DESC limit 3,4;

limit num1 offset num2：num1表示显示数据的条数，offset num2表示从开头开始偏移num2行，从num2+1处开始显示num1行信息

查询所有人口数超过50000000的国家，降序排列，显示第3-6行数据信息
select `CountryCode`,':',sum(`Population`) from city group by `CountryCode` having sum(`Population`)>50000000 order by sum(`Population`) DESC limit 4 offset 2;

综合练习
- 以省区为分组，查询各个省的总人口数大于5000000的省份信息，降序排列，只显示前三条信息
```
select `District`,sum(`Population`) from city where CountryCode='CHN' group by `District` having sum(`Population`)> 5000000 order by sum(`Population`) DESC limit 3;
```
- 查询并显示中国或者美国的所有城市信息
```
select `CountryCode`,group_concat(`Name`) from city where `CountryCode` in ('CHN','USA') group by `CountryCode`\G;
```
查询语句的书写和mysql的执行顺序：select、from 、where、group by、having、order by和limit，必须严格按照这个顺序来书写，否则会报错，where和having的用法是一样的，只是顺序不一样，having是做二次的筛选
union和union all的区别
- 作用：union和union all主要用于把两个查询集合并为一个进行显示
- 区别：
  - union会在两个查询集合并的时候执行去重，所有的结果都不重复
  - union all使用的时候不对两个查询的集的结果进行去重，结果中可能会存在重复的问题
- 具体用法：查询语句1
  union/union all
  查询语句2
```
查询中国或美国所有城市的个数
select `CountryCode`,count(`Name`) from city where CountryCode='CHN'  
union all
select `CountryCode`,count(`Name`) from city where CountryCode='USA';     
这个查询是可以通过CountryCode='CHN' or CountryCode='USA'来查询实现
```
- union和union all查询代替or和and用法，但是在执行的时候优先级是要高于and和or的，执行的速度更快，效率高。

五、多表连接查询与子查询

(一)、内连接(使用最多)

基本的用法
select 表名.子段名,表名.子段名,......from 主表
join 从表1
on 主表.关联列=从表1.关联列
join 从表2
on 从表1.关联列=从表2.关联列
......
where
group by
having
order by
limit
mysql中的select的执行顺序
select 列
from 表
where 列条件
group by 列条件
having 列条件
order by 列条件 DESC
limit 过滤条件

注意：执行的顺序也是和mysql语句的书写顺序是完全一致的，即group by前的语句是初步查询出结果，having在初步的结果上做进一步的筛选，在筛选的基础上再做排序order by和限制显示的功能limit。

多表连接查询的基本要求
- 最核心的是找到多张表之间的关联条件列
- 列书写时必须是：表名.列名
- 所有涉及到的查询列都必须放在select语句后
- 所有的过滤、分组、排序等条件列都必须放在on的后边
- 关联列条件必须写在on后边，相当于把多个表拼接成为单独一个表，在一个表中去查询需要的结果
- 在多表连接中，驱动表(from后的第一个表)应该尽量选择数据行较少的表，后续所有表的关联列尽量是主健或者是唯一健(表设计的时候)，至少建立一个索引。

实例

  ```
  1. 查询学生李振学习了几门课程
  use school;
  select student.sname,count(score.cno)
  from student
  join score
  on student.sno=score.sno
  where student.sname='李振';

  2.查询学生李振学习的课程名称
  use school;
  select student.sname,group_concat(course.cname)
  from student
  join score
  on student.sno=score.sno
  join course
  on score.cno=course.cno
  where student.sname='李振'
  group by student.sno;

  3.查询李老师教课的学生名和个数
  use school;
  select teacher.tname,group_concat(distinct student.sname),count(distinct student.sno)
  from teacher
  join course
  on teacher.tno=course.tno
  join score
  on course.cno=score.cno
  join student
  on score.sno=student.sno
  where teacher.tname='李老师'
  group by teacher.tno;

  4.查询李老师所教课程的平均分
  use school;
  select teacher.tname,course.cname,avg(score.score)
  from teacher
  join course
  on teacher.tno=course.tno
  join score
  on course.cno=score.cno
  where teacher.tname='李老师'
  group by course.cno;

  5.查询每一位老师所教课程的平均分，并按照平均分由高到低排序
  use school;
  select teacher.tname,course.cname,avg(score.score)
  from teacher
  join course
  on teacher.tno=course.tno
  join score
  on course.cno=score.cno
  group by teacher.tname,course.cno
      注意：在查询中为了确定唯一性，必须同时以老师的名字和所教的课程的名字为分组条件
  order by avg(score.score) DESC;

  6.查询张老师所教课程中的不及格学生的名字、课程和成绩
  use school;
  select student.sname,course.cname,score.score
  from teacher
  join course
  on teacher.tno=course.tno
  join score
  on course.cno=score.cno
  join student
  on score.sno=student.sno
  where teacher.tname='马老师' and score.score<60;

  7.查询所有老师所教课程中的不及格学生的信息
  use school;
  select teacher.tname,group_concat(student.sname,":",course.cname,"  ",score.score)
  from teacher
  join course
  on teacher.tno=course.tno
  join score
  on course.cno=score.cno
  join student
  on score.sno=student.sno
  where score.score<60
  group by teacher.tno;
  ```

(二)、外连接

左连接

在join的左边添加left，即left join

left join优先显示左边表中的内容，显示完后再显示右边表的内容，左边的列右边没有相关的，内容为NULL

// 示例：
select * from course  
left join score  
on course.cno=score.cno  
left join class1  
on score.sno=class1.sno  
left join class_manager_group  
on class1.sno=class_manager_group.class_id ;                                                                       
+-------+----------+------+-------+-------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------+
| cno   | cname    | id   | sno   | cno   | score   | sno   | sname   | sage   | sgender   | stime               | cno    | cname   | post   | class_id   |
|-------+----------+------+-------+-------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------|
| 1     | 古汉语   | 11   | 5     | 1     | 91      | 5     | 刘非    | 23     | f         | 2020-04-04 18:31:48 | 3      | 刘非    | 班长   | 5          |
| 2     | 大明历史 | 7    | 3     | 2     | 78      | 3     | 王辉    | 22     | m         | 2020-04-04 18:03:17 | <null> | <null>  | <null> | <null>     |
| 3     | 幸福学   | 8    | 2     | 3     | 78      | 2     | 刘洋    | 24     | f         | 2020-04-04 18:03:17 | <null> | <null>  | <null> | <null>     |
+-------+----------+------+-------+-------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------+

右连接

在join前添加right，即right join

right join优先显示右边的内容，显示完右边的表的内容后，右边的列左边没有相关的，内容为NULL

select * from course  
right join score  
on course.cno=score.cno  
right join class1  
on score.sno=class1.sno  
right join class_manager_group  
on class1.sno=class_manager_group.class_id;                                                                       
+--------+---------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+-------+---------+--------+------------+
| cno    | cname   | id     | sno    | cno    | score   | sno   | sname   | sage   | sgender   | stime               | cno   | cname   | post   | class_id   |
|--------+---------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+-------+---------+--------+------------|
| 1      | 古汉语  | 11     | 5      | 1      | 91      | 5     | 刘非    | 23     | f         | 2020-04-04 18:31:48 | 3     | 刘非    | 班长   | 5          |
| <null> | <null>  | <null> | <null> | <null> | <null>  | 8     | 万名常  | 25     | n         | 2020-04-04 18:31:48 | 4     | 万名长  | 班副   | 8          |
+--------+---------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+-------+---------+--------+------------+

全外连接
mysql中没有明确的全外连接的用法，不过还是可以实现全外连接的。格式：左连接语句 union 右连接语句；

  ```
  select * from course  
  left join score  
  on course.cno=score.cno  
  left join class1  
  on score.sno=class1.sno  
  left join class_manager_group  
  on class1.sno=class_manager_group.class_id
  union
  select * from course   
  right join score   
  on course.cno=score.cno   
  right join class1   
  on score.sno=class1.sno   
  right join class_manager_group   
  on class1.sno=class_manager_group.class_id;                                                                        
  +--------+----------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------+
  | cno    | cname    | id     | sno    | cno    | score   | sno   | sname   | sage   | sgender   | stime               | cno    | cname   | post   | class_id   |
  |--------+----------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------|
  | 1      | 古汉语   | 11     | 5      | 1      | 91      | 5     | 刘非    | 23     | f         | 2020-04-04 18:31:48 | 3      | 刘非    | 班
  长   | 5          |
  | 2      | 大明历史 | 7      | 3      | 2      | 78      | 3     | 王辉    | 22     | m         | 2020-04-04 18:03:17 | <null> | <null>  | <null> | <null>     |
  | 3      | 幸福学   | 8      | 2      | 3      | 78      | 2     | 刘洋    | 24     | f         | 2020-04-04 18:03:17 | <null> | <null>  | <null> | <null>     |
  | <null> | <null>   | <null> | <null> | <null> | <null>  | 8     | 万名常  | 25     | n         | 2020-04-04 18:31:48 | 4      | 万名长  | 班
  副   | 8          |
  +--------+----------+--------+--------+--------+---------+-------+---------+--------+-----------+---------------------+--------+---------+--------+------------+

  ```

(三)、子查询

定义：
- 子查询是把一个查询嵌套在另外一个查询语句中
- 内置查询语句的结果可以为外层查询语句的查询提供条件
- 子查询可以包含：NOT、NOT、IN、ANY、ALL、EXISTS、NOT EXISTS等关键字
- 还可以包含运算符：=、!=、>、<等

示例

查询平均年龄大于27岁的部门的名字

select dname from department
where dno in(
select depart_id
from employee
group by depart_id
having avg(eage) > 27);
//结果是：
+---------+
| dname   |
|---------|
| 技术部  |
| 研发部  |
| 后勤部  |
+---------+

查询技术部门的员工名字


select ename  
from employee  
where depart_id=(
select dno  
from department
where dname="技术部");
//结果是：
+---------+
| ename   |
|---------|
| 马超    |
| 庞统    |
| 诸葛    |
+---------+

查询部门员工小于3的部门名


select dname  
from department  
where dno  
in(
select depart_id  
from employee
group by depart_id
having count(depart_id) < 3);

//结果是：
+---------+
| dname   |
|---------|
| 研发部  |
+---------+

查询所有大于平均年龄的员工的姓名和年龄

select ename,eage
from employee
where eage > (
select avg(eage)
from employee);

+---------+--------+
| ename   | eage   |
|---------+--------|
| 张飞    | 29     |
| 黄忠    | 29     |
| 孙权    | 35     |
| 庞统    | 34     |
| 何大胖  | 28     |
| 许褚    | 30     |
| 许攸    | 35     |
+---------+--------+

查询所有大于部门平均年龄的员工名和年龄

select ename,eage from employee as t1
inner join (select depart_id,avg(eage) avg_age
from employee  
group by depart_id) as t2
on t1.depart_id =t2.depart_id
where eage > avg_age;   

//结果是：
+---------+--------+
| ename   | eage   |
|---------+--------|
| 张飞    | 29     |
| 孙权    | 35     |
| 庞统    | 34     |
| 刘备    | 26     |
| 关羽    | 26     |
| 许褚    | 30     |
| 许攸    | 35     |
+---------+--------+

注意：最后一个其实同时用到了内连查询和子查询，inner join把内连的结果表和外表合成一张表，再对比。这里内连的avg(eage)必须起别名，否则会报错。

查询最后入职的员工的信息

select * from employee
order by etime DESC
limit 1;  

+-------+---------+--------+-------------+---------------------+
| eno   | ename   | eage   | depart_id   | etime               |
|-------+---------+--------+-------------+---------------------|
| 31    | 吕布    | 29     | 103         | 2020-04-05 17:54:56 |
+-------+---------+--------+-------------+---------------------+

查看公司各个部门的最后入职员工

select  * from employee as t1 inner join
(select depart_id,max(etime) as max_time from  
employee group by depart_id) as t2
on t1.depart_id=t2.depart_id
where etime=max_time;
//结果是：
+-------+---------+--------+-------------+---------------------+-------------+---------------------+
| eno   | ename   | eage   | depart_id   | etime               | depart_id   | max_time            |
|-------+---------+--------+-------------+---------------------+-------------+---------------------|
| 16    | 刘禅    | 25     | 101         | 2020-04-05 17:53:02 | 101         | 2020-04-05 17:53:02 |
| 17    | 张飞    | 29     | 101         | 2020-04-05 17:53:02 | 101         | 2020-04-05 17:53:02 |
| 18    | 赵云    | 23     | 101         | 2020-04-05 17:53:02 | 101         | 2020-04-05 17:53:02 |
| 31    | 吕布    | 29     | 103         | 2020-04-05 17:54:56 | 103         | 2020-04-05 17:54:56 |
| 32    | 貂蝉    | 20     | 104         | 2020-04-05 18:21:06 | 104         | 2020-04-05 18:21:06 |
| 33    | 曹丕    | 20     | 105         | 2020-04-05 18:21:28 | 105         | 2020-04-05 18:21:28 |
| 34    | 司马懿  | 20     | 102         | 2020-04-05 18:22:18 | 102         | 2020-04-05 18:22:18 |
+-------+---------+--------+-------------+---------------------+-------------+---------------------+

注意：在数据库优化中有很重要的一项：当查询的时候如果子查询和多表连接查询同样可以得到结果，那么就使用多表连接查询，因为多表连接查询的执行效率更高。

六、别名的使用

基本用法：表/列 as 表/列的别名
表别名是全局调用，列别名只能被having和order by 调用
创建别名的时候as也可以省略，但是最正规的用法还是加上as

实例

  ```
  use school;
  select st.sname as 学生,co.cname as 课程,sc.score as 成绩
  from student as st
  join score as sc
  on st.sno=sc.sno
  join course as co
  on sc.cno=co.cno
  where st.sname="李振"
  order by 成绩 DESC;
  ```

注意：列别名主要的作用是当查询出结果之后，把对应列的列头名显示为别名

七、sql_mode和concat拼接问题

关于group_by中的sql_mode问题
当使用group_by的时候如果语法没有写正确，mysql会报错：this is incompatible with sql_mode=only_full_group_by
- 这个问题在mysql5.7中存在，5.6中没有，8.0版本后又取消了
- 通过select @@sql_mode; 查看sql_mode所具有的属性
```
 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```
- 在带有group_by子句的sql语句中，select后的条件列(非主健列)要么是group_by后的列(group_by那一个条件)，要么需要在函数后包裹。即group by的条件列必须在select后出现
注意：group_b具备两个功能：分组、去重
- 解决办法
  - 在my.cnf配置文件中的服务器端设置sql_mode属性，去掉ONLY_FULL_GROUP_BY这一项
```
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```
  - 保存退出
- 去掉ONLY_FULL_GROUP_BY属性后会出现一个问题就是查询的结果可能不能很好的显示，最好还是严格按照ONLY_FULL_GROUP_BY来写sql语句。

concat拼接的用法

基本的用法：concat(字段1，"需要拼接符号或者空格"，字段2......)
group_concat的作用是把不同的数据行拼接为同一行，concat的作用是把一个数据行的不同数据信息根据需求来进行相应的拼接操作

简单的实例

select sname,concat(sname," ",sage," ",sgender) from student;                                       
+-------+------------------------------------+
| sname | concat(sname," ",sage," ",sgender) |
+-------+------------------------------------+
| 李振  | 李振 23 m                          |
| 李名  | 李名 24 m                          |
| 刘洋  | 刘洋 22 f                          |
| 马红  | 马红 23 f                          |
| 周秉  | 周秉 23 m                          |
+-------+------------------------------------+

八、元数据的使用

元数据：除了数据行和文件名之外的所有属性信息即是元数据，被封装起来保存在数据库中的基表中
基表：
- 在mysql中无法直接对基表做增删改操作
- 必须通过专用的DDL语句或者DCL语句的修改操作
- 通过专用的视图进行元数据的查询操作
- information_schema数据库中保存了大量的元数据查询的视图
- show命令是封装好功能，提供元数据查询的基础的功能
视图：把常用的和复用性很高的sql语句创建为视图，直接调用视图即可获取查询的结果
- 创建视图：create view 视图名 as mysql查询语句
- 调用视图：select * from 视图名
```
创建视图
create view bb as
select sname,sage,sgender
from school.student;
调用视图获取查询结果
select * from bb;
```
- 删除已经创建视图
```
drop view aa;                                                                                        
Query OK, 0 rows affected
```
  视图就相当于封装查询指令，使得查询会更加的便捷，视图的用法和表的用法基本上是一样的。

tables视图的使用

TABLE_SCHEMA：存储的是表所在的库名信息
TABLE_NAME：存储的是mysql中的表名信息
ENGINE：存储的是存储引擎信息
TABLE_ROWS：存储总共有多少行数据
AVG_ROW_LENGTH：存储的是平均行长度
INDEX_LENGTH：存储的是索引的长度
实例

1.查询information_schema库中的元数据信息
use information_schema;
show tables;
desc tables;

2.查询mysql数据库中所有的库名和表名
use information_schema;
select table_schema as 库,group_concat(table_name) as 表
from tables
group by table_schema;

3.统计所有使用InnoDB引擎的表
use information_schema;
select engine,group_concat(table_schema,":",table_name)
from tables
group by engine;

4.mysql中所有库的占用空间情况
    注意：占用空间大小=（平均行长度 * 行数） +  索引长度
use information_schema;
select table_schema,sum(table_rows * avg_row_length + index_length)/1024
from tables
group by table_schema;

5.查询数据库school中的数据表student占用的空间大小
use information_schema;
select table_schema,table_name,
sum(table_rows * avg_row_length + index_length)/1024
from tables
where table_schema="school" and table_name="student";

6.查询数据库school中的数据表各自占用的空间大小
use information_schema;
select table_schema,table_name,
sum(table_rows * avg_row_length + index_length)/1024
from tables
where table_schema="school"
group by table_name;

7.统计每个数据库占用空间大小，并按照降序排列
use information_schema;
select table_schema,
sum(table_rows * avg_row_length + index_length)/1024 as total_KB
from tables
group by table_schema
order by total_KB DESC;

8.配合concat()函数拼接语句和命令，
    8.1模仿以下语句进行数据库的分库分表备份
    > 分库分表：mysqldump -u username -p password 数据库.表单 > /bak/数据库_表单.sql
    use information_schema;
    select
    concat("mysqldump -u root -p 930215", table_schema,".",table_name,
        ">/bak/",table_schema,"_",table_name,".sql")
    from tables;
        注意：concat的拼接中使用双引号来括住不变的信息，使用逗号把各个部分连接，可变的直接写上字段信息即可

    8.2模仿以下语句，进行批量生成对world库下所有的表进行操作
    > 操作：alter table world.city discard tablespace;
    use information_schema;
    select
    concat("alter table",table_schema,".",table_name,"discard tablespace")
    from tables;
    ```

九、show语句的使用

show databases：查看mysql中所有的数据库
show tables：查看当前数据库中所有的表单
show create databases xx：查看建库语句
shoe crate table xx：查看建表语句
show processlist：查看所有的连接情况
show charset：查看所有支持的字符集
show collation：查看所有支持的校对规则
show grants for 用户：查看用户所有的权限信息
show variables like "%xx%"：查看对应的参数信息
show engines：查看所有支持的存储引擎信息
show index from 表单：查看表单中的索引信息
show engine inndb status \G：查看innodb引擎所有的状态信息
show binary logs：查看二进制日志的列表信息
show binlog event in "xx"：查看二进制日志中的事件信息
show master status：查看当前使用的二进制信息
show slave status\G：查看从库状态信息
show relaylog events in "xx"：查看中继日志的时间信息
show status like "xx"：查看数据库的整体状态信息

十、索引及执行计划

一、索引

索引的功能
类似于书中的目录，可以起到优化查询的作用，使得查询的语句更加快速和便捷
索引的分类(按照算法)
- BTree索引：默认使用的索引类型，95%以上做维护等都是这个索引
- RTree索引
- HASH索引
- FullText：全文索引，把很大的字符串拆分成一个个的小的单词或者单词组合，使得查询更加的方便和灵活
- GIS索引：地理位置索引，借鉴与mongoDB
BTree索引算法演变(了解)
在最初的索引中会采用二分法来快速的查询数据，但是这种方法存在一个严重的问题：每一次查询所用到的时间和执行的效率是不一样的，这样会造成不同的查询消耗的时间是不同的，会给用户带来不好的观感。BTree索引很好的解决了二分法中存在的问题
- 普通的BTree索引：
  BTree索引
  - 根节点：保存每一个枝节点中的最小值，并且指针指向对应的枝节点
  - 枝节点：保存每一个叶子节点中的最小值，并且指针指向对应的叶子节点
  - 叶子节点：数据库中的数据会被均匀的保存在每一个叶子节点中
  Btree索引会去mysql中申请数据页，每一个数据页都是16KB固定大小。在BTree索引下查找数据都是三步：根结点、枝节点和叶子节点
- B+Tree索引
  和普通的BTree的区别在与叶子节点上，对范围查询进行了进一步的IO方面的优化：在每一个枝节点上进行了双向指针设计，一个数据从根结点往下找到叶子节点而没有找到对应的数据时，该数据在其他的叶子节点上，如果是普通的BTree索引时会从根结点开始再一次进行三步索引，这样会造成IO压力。B+Tree索引在相邻的叶子节点上设置双向指针，那么当在一个叶子节点上找不到数据的时候，可以直接通过叶子节点的指针跳转到下一个叶子节点查找数据，从而减少了两步操作。
- B* Tree索引
  在B+Tree索引的基础上对枝节点进行进一步的优化，在枝节点上添加了双向指针。原理可叶子节点的双向指针一致，现在的mysql使用的就是B* Tree索引。
BTree索引在功能上的分类
- 辅助索引
  - 管理员选择一个列创建辅助索引
  - MySQL会自动将此列的值取出来
  - 将此列的值做自动排序
  - 将排好序的数据均匀的存储到索引树中的叶子节点
  - 根据叶子节点生成枝节点
  - 根据枝节点生成根结点，至此索引树生成完毕
  - 叶子节点上的每一个号码对应的时原表中对应的数据，BTree索引就相当于课本中的目录
  - 根据列值来查询需要的数据
  辅助索引辅助的是聚集索引
- 聚集索引
  - MySQL会自动选择主健作为聚集索引列，如果没有主健，则会选择唯一健。如果两者都没有，在MySQL5.5版本之后会自动生成隐藏主健，选择隐藏主健作为索引列
  - 强制根据数据表中的主健列(非空、唯一和自增)的顺序有序的把数据表中的数据写入到磁盘空间
  - 生成BTree，直接将整行的数据按照顺序存储在叶子节点，辅助索引时把列中的数据存储在叶子节点中
  - 枝节点和根结点还是存储列中的最小值，不是行，这样就减少了BTree的大小
- 聚集索引和辅助索引配合使用
  - 聚集索引根据主健列生成BTree树结构，主健列一般都是ID
  - 辅助索引根据非主健列生成BTree树索引，但是与普通的辅助索引不同的是：这里的辅助索引的叶子节点存储的是ID值，而非数据页
  - 当查询一个数据的时候，如果用户是根据非主健列查询，那么可以通过辅助索引查找到所要查找数据对应的主健值，再把主健值放到聚集索引中去查询得到对应的数据；如果查询使用的是主健，那么直接走聚集查询。
  - 由于聚集索引中的叶子节点存储的是整行的数据，所以严格意义上并不算叶子节点，一定程度上可以把聚集索引看作是只有枝节点和根结点
虽然聚集索引和辅助索引可以解决所有的查询问题，但是由于查询中用到了两个索引树，查询了两次，这个也被称为回表，增加了IO操作。如果解决前边的问题，可以在辅助索引中提取所有的信息，这样就可以只在辅助索引中完成所有的索引，这个被称为覆盖索引，但是很难实现，太过完美。只有InnoDB才具有聚集索引，MyISAM没有聚集索引。
- 聚集索引和辅助索引的区别
  - 任何一个都可以创建辅助索引，在有需要的时候就可以创建，可以在一张表中创建多个，只要名字不同即可
  - 在一个表中只能有一个聚集索引，一般都是主健
  - 辅助索引的叶子节点存储的是索引列的有序值+聚集索引列值，聚集索引的叶子节点存储的是表中的有序的整行数据
  - mysql的表存储是聚集索引组织表
- 辅助索引细分
  - 单列辅助索引
  - 联合索引(覆盖索引)
  很重要，实现难度大
  - 唯一索引：使用的列里的值都是唯一的
索引树高度
- 索引树高度应当是越低越好，一般是维持在3层最好
- 影响索引树高度的因素及优化办法
  - 数据行数较多的时候
    - 分表：parttion，把一个超大型的表分成多个小表，现在用的比较少了
    - 分片(分库分表)：分布式架构
  - 字段长度
    - 在业务情况允许的情况下，尽量使用字符长度短的列作为索引列
    - 如果必须使用字符长度很长的列作为索引列，那么采用前缀索引的方法，只取字符串开头固定长度的字符作为索引列
  - 数据类型
    - char和varchar
    - enum

索引的命令操作

查询索引
- 命令：
  - 方式一：desc 表名
  - 方式二：show index from 表名
- 索引类型：
  - PRI：主健索引
  - MUL：辅助索引
  - UNI：唯一索引
  - 前缀索引类型

创建索引

单列辅助索引的创建
- alter table 表名 add index idx_列名(列名);
- idx：是一个自定义的前缀，表明是一个索引

//创建单列索引
alter table city add index index_name(Name);
//查询索引
show index from city\G;                                                                              

//第一行
Table         | city
Non_unique    | 0
Key_name      | PRIMARY
Seq_in_index  | 1
Column_name   | ID
Collation     | A
Cardinality   | 4046
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |

//第二行
Table         | city
Non_unique    | 1
Key_name      | CountryCode
Seq_in_index  | 1
Column_name   | CountryCode
Collation     | A
Cardinality   | 232
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |

//第三行
Table         | city
Non_unique    | 1
Key_name      | index_name
Seq_in_index  | 1
Column_name   | Name
Collation     | A
Cardinality   | 3998
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |

主健索引的创建
- alter table 表名 add primary key(主健列)
- 主健索引最好是在创建表的时候就设定好，如果没有设定，则通过上述的语法来实现，需要注意的是主健索引不需要名字，并且前边必须是primary key，而不是promary index。
- 联合主健设定的时候，在括号被添加多个主健列就可以了
```
//联合主健
alter table usertb1 add primary key(id,uname);
```
联合索引的创建
- alter table 表名 add index idx_列1_列2(列1，列2)
- 括号中的是联合索引所有到的列
注意：联合索引创建中应该关注列的排序，因为在查询中使用联合索引，那么索引是按照联合索引创建的顺序从左往右进行的，不可能跳过前边的从后边开始
- 最左前缀原则：必须带着最左边的列做条件，从出现范围开始整条索引实效，即联合索引中的最左边的列不能使用范围。

// 创建联合索引
alter table city add index idx_c_p(`CountryCode`,`Population`);
//show index from city\G;

//结果
mysql root@localhost:world> show index from city\G;                                                                              
// 第一行
Table         | city
Non_unique    | 0
Key_name      | PRIMARY
Seq_in_index  | 1
Column_name   | ID
Collation     | A
Cardinality   | 4046
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
//第二行
Table         | city
Non_unique    | 1
Key_name      | CountryCode
Seq_in_index  | 1
Column_name   | CountryCode
Collation     | A
Cardinality   | 232
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
//第三行
Table         | city
Non_unique    | 1
Key_name      | index_name
Seq_in_index  | 1
Column_name   | Name
Collation     | A
Cardinality   | 3998
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
//第四行
Table         | city
Non_unique    | 1
Key_name      | idx_c_p
Seq_in_index  | 1
Column_name   | CountryCode
Collation     | A
Cardinality   | 232
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
//第五行
Table         | city
Non_unique    | 1
Key_name      | idx_c_p
Seq_in_index  | 2
Column_name   | Population
Collation     | A
Cardinality   | 4046
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |

唯一索引创建

创建唯一健索引：alter table 表名 add unique index uidx_唯一健(唯一健)

查询所要创建索引的列是否是唯一健

方法一：直接创建唯一健索引，如果不是唯一健，则会报如下错误：

(1062, "Duplicate entry 'Zuid-Holland' for key 'uidx_dis'")

方法二：查询列中数据的总数，然后去重后再次查看列中数据的总数，如果两次数量不同则不是唯一健，如果相同则是唯一健

// 去重之前
select count(district) from city;                                                                    
+-----------------+
| count(district) |
+-----------------+
| 4079            |
+-----------------+

// 去重之后
mysql root@localhost:world> select count(distinct district) from city;                                                           
+--------------------------+
| count(distinct district) |
+--------------------------+
| 1367                     |
+--------------------------+

创建前缀索引
- 创建：alter table 表名 add index idx_列名(列名(num))
- num：表示的是前缀索引取的是列中的前几个字符做索引
```
alter table city add index idx_dis(district(5));
```

注意：创建索引中只有唯一健索引是使用unique index，其他类型的索引都是使用的是index

删除索引
alter table 表名 drop index 索引名

注意：删除索引的时候不需要区分索引的类型，全部是index
```
use world;
alter table city drop index idx_c_p;
alter table city drop index index_name;
alter table city drop index idx_dis;
```

正确使用索引：
- 对哪一个字段创建了索引，就用哪个字段做条件查询
- 在创建索引的时候应该对区分度比较大的列进行创建，1/10以下的重复率比较适合创建索引，重复率比较大的列不适合创建
- 范围：
  - 范围越大越慢
  - 范围越小越快
- 条件列参与计算，索引会比较慢，所以条件列不能参与计算
- and和or
  - 多个条件的组合中如果其中一列存在索引，都可以加快索引
  在and中，开始查找的时候mysql会从范围比较小的那一列开始查找，缩小范围后在查找其他的列。
  - 如果使用or连接，必须所有的列都包含索引才能加快查找速度，否则不会加快查找速度
- 索引列不要使用函数
- 条件查询中注意数据类型必须正确
- order by后的条件列必须和select后的列一致
- 联合索引查找的时候必须遵循最左前缀原则，联合索引中的最左边的索引列不能使用范围查找
- 避免使用select *
- 可以使用count( * )
- 组合索引代替单列索引
- 尽量使用短索引
- 使用join连接来代替子查询
- 连表时注意条件类型一致
- 表的字段固定长度的顺序优先

二、执行计划分析

作用
把优化器选择后的执行计划截取出来，用于管理判断语句的执行效率

分析执行计划

table：表名

type：查询的类型

全表扫描(一般比较低效)：ALL

索引扫描

index：全索引扫描。当查询的字段需要遍历整个索引树的情况就是全索引扫描，type为index

// 查看查询的类型
desc select id from city
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-------------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key         | key_len   | ref    | rows   | filtered   | Extra       |
|------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-------------|
| 1    | SIMPLE        | city    | <null>       | index  | <null>          | CountryCode | 12        | <null> | 4046   | 100.0      | Using index |
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-------------+

range：索引范围扫描(<、<=、>、>=、and、or、between、in、like)

// or的用法
desc select * from city where `CountryCode`="CHN" or `CountryCode`="USA";                             
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key         | key_len   | ref    | rows   | filtered   | Extra                 |
|------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------|
| 1    | SIMPLE        | city    | <null>       | range  | CountryCode     | CountryCode | 12        | <null> | 637    | 100.0      | Using index condition |
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------+

// like的用法
desc select * from city where `CountryCode` like "CH%";                                               
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key         | key_len   | ref    | rows   | filtered   | Extra                 |
|------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------|
| 1    | SIMPLE        | city    | <null>       | range  | CountryCode     | CountryCode | 12        | <null> | 397    | 100.0      | Using index condition |
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+--------+--------+------------+-----------------------+

需要注意的是：在获取同样查询结果的情况下，or和in的效率要低于其他的范围索引，因为在B+和B* 树上叶子和枝节点之间存在双向的指针，当相邻近的几个条件查询的时候可以快速的从当前叶子节点跳转到下一个叶子节点，而不相邻的两个叶子节点的跳转效率就低了很多，or和in都是不相邻的，例如：CountryCode="CHN" or CountryCode="USA"，需要从c跳到u；而其他的范围查找都是相邻近的，所以效率更高
基于上述的原因，or和in的效率低需要进行优化，改写成union all语句，改写后类型为ref，不再是range
ref：辅助索引等值查询(=)

desc select *  from city where CountryCode ="USA";                                                    
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+-------+--------+------------+---------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key         | key_len   | ref   | rows   | filtered   | Extra   |
|------+---------------+---------+--------------+--------+-----------------+-------------+-----------+-------+--------+------------+---------|
| 1    | SIMPLE        | city    | <null>       | ref    | CountryCode     | CountryCode | 12        | const | 274    | 100.0      | <null>  |
+------+---------------+---------+--------------+--------+-----------------+-------------+-----------+-------+--------+------------+---------+

eq_ref：多表连接时，子表使用主键列或者唯一健作为连接条件，from后的表是驱动表，join后的表是子表，驱动表走的是全表索引，所以尽量选择量级较小的表，子表的连接列最好是主键或者唯一建，最差也得是辅助索引，这个需要在程序的开发时就设计好的。

desc select b.name,a.population from city as a join country as b on a.`CountryCode`=b.`Code` where a.`Population`<100;                                                                                      
+------+---------------+---------+--------------+--------+-----------------+---------+-----------+---------------------+--------+------------+-------------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key     | key_len   | ref                 | rows   | filtered   | Extra       |
|------+---------------+---------+--------------+--------+-----------------+---------+-----------+---------------------+--------+------------+-------------|
| 1    | SIMPLE        | a       | <null>       | ALL    | CountryCode     | <null>  | <null>    | <null>              | 4046   |  33.33     | Using where |
| 1    | SIMPLE        | b       | <null>       | eq_ref | PRIMARY         | PRIMARY | 12        | world.a.CountryCode | 1      | 100.0      | <null>      |
+------+---------------+---------+--------------+--------+-----------------+---------+-----------+---------------------+--------+------------+-------------+

const(system)：主键或者唯一健的等值查询，主键的等值查询效率比唯一健的等值查询效率略高

// 主键查询：ID是主键查询
desc select * from city where `ID`=100;                                                               
+------+---------------+---------+--------------+--------+-----------------+---------+-----------+-------+--------+------------+---------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key     | key_len   | ref   | rows   | filtered   | Extra   |
|------+---------------+---------+--------------+--------+-----------------+---------+-----------+-------+--------+------------+---------|
| 1    | SIMPLE        | city    | <null>       | const  | PRIMARY         | PRIMARY | 4         | const | 1      | 100.0      | <null>  |
+------+---------------+---------+--------------+--------+-----------------+---------+-----------+-------+--------+------------+---------+

// 唯一健查询:sphone是唯一健
desc select * from student01 where sphone='13765438879';
+------+---------------+-----------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|------+---------------+-----------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------|
| 1 | SIMPLE | student01 | | const | sphone | sphone | 82 | const | 1 | 100.0 | |
+------+---------------+-----------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------+

null：表中没有数据，没有数据的时候最高，这个不用关注

从上到下效率依次增加，依次为：全表查询-->全索引查询-->辅助索引范围查询-->辅助索引等值查询(包括多表联查)-->主键或者唯一健等值查询-->没有数据，基本的思路还是辅助索引没有聚集索引的效率高

not in 和 != 的类型划分

索引的条件不是主键列的时候，not in和!=不走索引，走的是全表查询，不走索引

desc select * from student where `sname`!="李振";                                                    
+------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+-------------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key    | key_len   | ref    | rows   | filtered   | Extra       |
|------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+-------------|
| 1    | SIMPLE        | student | <null>       | ALL    | <null>          | <null> | <null>    | <null> | 5      | 80.0       | Using where |
+------+---------------+---------+--------------+--------+-----------------+--------+-----------+--------+--------+------------+-------------+

索引的条件是主键列的时候，not in和!=走的是辅助索引范围查询(range)

possible_key：可能会用到的索引

desc select * from usertb01 where id != 3000;                                                        
+------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------+
| id   | select_type   | table    | partitions   | type   | possible_keys   | key     | key_len   | ref    | rows    | filtered   | Extra       |
|------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------|
| 1    | SIMPLE        | usertb01 | <null>       | range  | PRIMARY         | PRIMARY | 8         | <null> | 3753658 | 100.0      | Using where |
+------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------+

key：执行sql查询的时候最终选择的索引，possible_key是可能用到但不一定用的索引

desc select * from usertb01 where id != 3000;                                                        
+------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------+
| id   | select_type   | table    | partitions   | type   | possible_keys   | key     | key_len   | ref    | rows    | filtered   | Extra       |
|------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------|
| 1    | SIMPLE        | usertb01 | <null>       | range  | PRIMARY         | PRIMARY | 8         | <null> | 3753658 | 100.0      | Using where |
+------+---------------+----------+--------------+--------+-----------------+---------+-----------+--------+---------+------------+-------------+

key_len:索引覆盖长度，在utf8mb4编码中，一个中文占4个字节：

字段没有设置not null：索引的覆盖长度=创建字段设定的长度 * 4 + 1
字段设置not null：索引的覆盖长度=创建字段设定的长度 * 4


desc select * from usertb01 where uname="用户0";                                                     
+------+---------------+----------+--------------+--------+-----------------+-----------+-----------+-------+---------+------------+---------+
| id   | select_type   | table    | partitions   | type   | possible_keys   | key       | key_len   | ref   | rows    | filtered   | Extra   |
|------+---------------+----------+--------------+--------+-----------------+-----------+-----------+-------+---------+------------+---------|
| 1    | SIMPLE        | usertb01 | <null>       | ref    | idx_uname       | idx_uname | 63        | const | 1647776 | 100.0      | <null>  |
+------+---------------+----------+--------------+--------+-----------------+-----------+-----------+-------+---------+------------+---------+

由于在utf8mb4中一个中文字符占4个字节长度，中文的长度比英文和数字都要长，一个数字或者英文占1个字节，所以最大字节预留长度以中文为标准，当字段没有设置非空属性的时候，需要预留一个字节给null，所以非空字段最大覆盖索引长度需要 +1。

如果是varchar类型，则在遵循null和not null的前提下，还要在开头和结束位置各占一个字节，例如：一个varchar(4) null 的字段，最大的覆盖索引长度为：4 * 4 + 1 + 2 = 19

//新建表test

desc test;                                                                                                       
+---------+------------+--------+-------+-----------+---------+
| Field   | Type       | Null   | Key   | Default   | Extra   |
|---------+------------+--------+-------+-----------+---------|
| id      | int(11)    | YES    | MUL   | <null>    |         |
| k1      | char(2)    | YES    | MUL   | <null>    |         |
| k2      | varchar(2) | YES    | MUL   | <null>    |         |
| k3      | char(4)    | YES    | MUL   | <null>    |         |
| k4      | varchar(4) | YES    | MUL   | <null>    |         |
+---------+------------+--------+-------+-----------+---------+

//查看varchar类型的最大覆盖索引长度
desc select * from test where k4="这是第二";                                                                     
+------+---------------+---------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------+
| id   | select_type   | table   | partitions   | type   | possible_keys   | key    | key_len   | ref   | rows   | filtered   | Extra   |
|------+---------------+---------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------|
| 1    | SIMPLE        | test    | <null>       | ref    | idx_k4          | idx_k4 | 19        | const | 1      | 100.0      | <null>  |
+------+---------------+---------+--------------+--------+-----------------+--------+-----------+-------+--------+------------+---------+

最大索引覆盖长度主要用于联合索引中

联合索引

联合索引中的最大的索引覆盖长度为每一个索引的最大覆盖长度之和，创建联合查询的时候应该把唯一值多的放在最前边

  ```
  //创建联合索引
  alter table test add index idx(k1,k2,k3,k4);  

  //查看联合索引最大的覆盖长度
  desc select * from test where k1="莉莉" and k2="as" and k3="sdwq" and k4="这是中文";                             
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------+
  | id   | select_type   | table   | partitions   | type   | possible_keys   | key   | key_len   | ref                     | rows   | filtered   | Extra   |
  |------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------|
  | 1    | SIMPLE        | test    | <null>       | ref    | idx             | idx   | 56        | const,const,const,const | 1      | 100.0      | <null>  |
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------+

  ```
  - 全部索引列
  只要我们将来的查询，<font color="red">所有的索引列</font>都是</font color="red">等值</font>查询条件下，无关索引列写的顺序，原因是：优化器会自动做查询条件的排列

  ```
  //正序和乱序
  desc select * from test where k1="莉莉" and k2="as" and k3="sdwq" and k4="这是中文";
  desc select * from test where k2="as" and k1="莉莉" and k3="sdwq" and k4="这是中文";  

  //结果
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------+
  | id   | select_type   | table   | partitions   | type   | possible_keys   | key   | key_len   | ref                     | rows   | filtered   | Extra   |
  |------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------|
  | 1    | SIMPLE        | test    | <null>       | ref    | idx             | idx   | 56        | const,const,const,const | 1      | 100.0      | <null>  |
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------------------+--------+------------+---------+

  ```
  - 部分且不连续索引列
  将来的查询中，联合索引中只写了部分不连续的索引列等值的查询条件，那么最终只能走连续的索引列。例如在下边的例子中，联合索引的顺序是k1,k2,k3,k4，但是查询的时候索引列顺序为：k2、k1和k4，这个不是全部索引列，k1和k2是连续索引列，k2和k4不是，那么索引只能走连续索引k1和k2，所以最大的索引覆盖长度为k1+k2=20

  ```
  desc select * from test where k2="as" and k1="莉莉" and k4="这是中文";                                           
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------+--------+------------+-----------------------+
  | id   | select_type   | table   | partitions   | type   | possible_keys   | key   | key_len   | ref         | rows   | filtered   | Extra                 |
  |------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------+--------+------------+-----------------------|
  | 1    | SIMPLE        | test    | <null>       | ref    | idx             | idx   | 20        | const,const | 1      | 50.0       | Using index condition |
  +------+---------------+---------+--------------+--------+-----------------+-------+-----------+-------------+--------+------------+-----------------------+
  ```

主键约束

主键约束
使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
联合主键
联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
自增约束
自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
添加主键约束
如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
删除主键
ALTER TABLE user drop PRIMARY KEY;

唯一主键

- 建表时创建唯一键
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

- 添加唯一键
- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

- 删除唯一主键
ALTER TABLE user DROP INDEX name;

非空约束

- 建表时添加非空约束
- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

- 建表时添加默认约束
- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

- 移除非空约束
ALTER TABLE user MODIFY age INT;

外键约束

创建基本的格式：foreign key(本表中设置外健的字段) references 关联表(关联字段)
```

  //班级
  CREATE TABLE classes (
      id INT PRIMARY KEY,
      name VARCHAR(20)
  );

  //学生表
  CREATE TABLE students (
      id INT PRIMARY KEY,
      name VARCHAR(20),
      class_id INT,
      FOREIGN KEY(id) REFERENCES classes(id)
  );
  ```

给外健设置名字
格式：alter table 表名 add constraint 外健名 foreign key(字段) references 关联表(字段);
```
alter table students add constraint 名字 foreign key(id) references classes(id);
```
删除外健
- 删除外健之前必须先为外健创建名字
- 查看外健的名称：show create table 表名;
- 删除外健格式：alter table 表名 drop foreign key 外健名;
主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
主表中的记录被副表引用时，主表不可以被删除。
设置外健级联删除级联更新：on update cascade on delete cascade，注意：这个一般不要使用，容易造成误操作。
```
foreign key(class_id) references class1(sno) on update cascade on delete cascade
```
学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
- 这里的 class_id 要和 classes 中的 id 字段相关联
  class_id INT,
  FOREIGN KEY(id) REFERENCES classes(id)
  );


    - 基本的格式：<font color="red">foreign key(本表中设置外健的字段) references 关联表(关联字段)</font>
    - 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
    - 主表中的记录被副表引用时，主表不可以被删除。
    - 设置外健级联删除级联更新：on update cascade on delete cascade，注意：<font color='red'>这个一般不要使用，容易造成误操作。</font>


    ```
    foreign key(class_id) references class1(sno) on update cascade on delete cascade
    ```


## 数据库的三大设计范式

### 1NF

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

### 2NF

在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：

订单表
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);


实际上，在这张订单表中，`product_name` 只依赖于 `product_id` ，`customer_name` 只依赖于 `customer_id` 。也就是说，`product_name` 和 `customer_id` 是没用关系的，`customer_name` 和 `product_id` 也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);

CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);

CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);


拆分之后，`myorder` 表中的 `product_id` 和 `customer_id` 完全依赖于 `order_id` 主键，而 `product` 和 `customer` 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

### 3NF

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);


表中的 `customer_phone` 有可能依赖于 `order_id` 、 `customer_id` 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);

CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);


修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

## 查询练习

### 准备数据

创建数据库
CREATE DATABASE select_test;
切换数据库
USE select_test;
创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, - 生日
class VARCHAR(20) - 所在班级
);
创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, - 职称
department VARCHAR(20) NOT NULL - 部门
);
创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, - 教师编号
- 表示该 tno 来自于 teacher 表中的 no 字段值
  FOREIGN KEY(t_no) REFERENCES teacher(no)
  );
成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, - 学生编号
c_no VARCHAR(20) NOT NULL, - 课程号
degree DECIMAL, - 成绩
- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
  FOREIGN KEY(s_no) REFERENCES student(no),
  FOREIGN KEY(c_no) REFERENCES course(no),
- 设置 s_no, c_no 为联合主键
  PRIMARY KEY(s_no, c_no)
  );
查看所有表
SHOW TABLES;
添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');
查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;


### 1 到 10

查询 student 表的所有行
SELECT * FROM student;
查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
查询 teacher 表中不重复的 department 列
department: 去重查询
SELECT DISTINCT department FROM teacher; (distinct表示的是不重复的，截然不同的，distinct用在需要被排重的字段之前)
查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;
⚠️在查询区间的写法上，运算符方式比between....and...方式更加的精确，两边存在明确的包含与不包含
查询 score 表中成绩为 85, 86 或 88 的行
IN: 查询规定中的多个值，表示表中一个字段不同的值之间或的关系。
SELECT * FROM score WHERE degree IN (85, 86, 88);
查询 student 表中 '95031' 班或性别为 '女' 的所有行
or: 表示表中两个不同字段之间以及同一个字段之间不同值的或关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
select * from student where name = "曾华" or name = "李军";
以 class 降序的方式查询 student 表的所有行
DESC: 降序，从高到低
ASC（默认）: 升序，从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;
以no降序，class升序同时进行的排列，中间用逗号隔开，两个排序条件在执行的时候存在优先级，优先执行第一个排序条件，在此基础上再执行第二个排序条件。
mysql> select * from student order by no desc,class asc;
+----+-----------+-----+------------+-------+
| no | name | sex | birthday | class |
+----+-----------+-----+------------+-------+
| 109 | 赵铁柱 | 男 | 1974-06-03 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
| 101 | 曾华 | 男 | 1977-09-01 | 95033 |
+----+-----------+-----+------------+-------+
查询 "95031" 班的学生人数
COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';
查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
(SELECT MAX(degree) FROM score): 嵌套子查询，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);
排序查询
LIMIT r, n: 表示从第r行开始，查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;


### 分组条件与模糊查询

**查询 `score` 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。**

SELECT * FROM score;

c_no 课程编号
+-----+-------+--------+
| s_no | c_no | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+


分析表发现，至少有 2 名学生选修的课程是 `3-105` 、`3-245` 、`6-166` ，以 3 开头的课程是 `3-105` 、`3-245` 。也就是说，我们要查询所有 `3-105` 和 `3-245` 的 `degree` 平均分。

首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+------+-------------+
| c_no | AVG(degree) |
+------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+------+-------------+
再查询出至少有 2 名学生选修的课程
HAVING: 表示持有
HAVING COUNT(c_no) >= 2
并且是以 3 开头的课程
LIKE 表示模糊查询，"%" 是一个通配符，匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';
把前面的SQL语句拼接起来，
后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。

SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+------+-------------+----------+

-查询c_no>=3并且s_no以3结尾的成绩最大值：

select s_no,max(degree) from score group by s_no having count(c_no)>=3 and s_no like "%3";
+-----+-------------+
| s_no | max(degree) |
+-----+-------------+
| 103  |          92 |
+-----+-------------+
1 row in set (0.00 sec)

子查询加分组求平均分

查询 95031 班学生每门课程的平均成绩。

在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：

- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+-----+-------+--------+

这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+------+-------------+
| c_no  | AVG(degree) |
+------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+------+-------------+

子查询 - 1

查询在 3-105 课程中，所有成绩高于 109 号同学的记录。

首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。

SELECT * FROM score
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

子查询 - 2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

- 不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR 函数与带 IN 关键字查询

查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。

- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));

多层嵌套子查询

查询 '张旭' 教师任课的学生成绩表。

首先找到教师编号：

SELECT NO FROM teacher WHERE NAME = '张旭'

通过 sourse 表找到该教师课程号：

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '张旭' );

通过筛选出的课程号查询成绩表：

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = (
        SELECT no FROM teacher WHERE NAME = '张旭'
    )
);

子查询 - 3

查询 “计算机系” 课程的成绩表。

思路是，先找出 course 表中所有 计算机系 课程的编号，然后根据这个编号查询 score 表。

- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no, name, department FROM teacher WHERE department = '计算机系'
+----+--------+--------------+
| no  | name   | department   |
+----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+----+--------+--------------+

- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+------+
| no    |
+------+
| 3-245 |
| 3-105 |
+------+

- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'
    )
);
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+-----+-------+--------+

ANY 表示至少一个 - DESC ( 降序 )

查询课程 3-105 且成绩至少高于 3-245 的 score 表。

SELECT * FROM score WHERE c_no = '3-105';
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+-----+-------+--------+

SELECT * FROM score WHERE c_no = '3-245';
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+-----+-------+--------+

- ANY: 符合SQL语句中的任意条件。
- 也就是说，在 3-105 成绩中，只要有一个大于从 3-245 筛选出来的任意行就符合条件，
- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+-----+-------+--------+

表示所有的 ALL

查询课程 3-105 且成绩高于 3-245 的 score 表。

- 只需对上一道题稍作修改。
- ALL: 符合SQL语句中的所有条件。
- 也就是说，在 3-105 每一行成绩中，都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+-----+-------+--------+

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的 score 表。

- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+------+-------------+
| c_no  | AVG(degree) |
+------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+------+-------------+

- 查询 score 表
SELECT degree FROM score;
+-------+
| degree |
+-------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+-------+

- 将表 b 作用于表 a 中查询数据
- score a (b): 将表声明为 a (b)，
- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+-----+-------+--------+
| s_no | c_no  | degree |
+-----+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+-----+-------+--------+

条件加组筛选

查询 student 表中至少有 2 名男生的 class 。

NOTLIKE 模糊查询取反

查询 student 表中不姓 "王" 的同学记录。

YEAR 与 NOW 函数

查询 student 表中每个学生的姓名和年龄。

- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+----------+------+
| name      | age  |
+----------+------+
| 曾华      |   42 |
| 匡明      |   44 |
| 王丽      |   43 |
| 李军      |   43 |
| 王芳      |   44 |
| 陆军      |   45 |
| 王尼玛    |   43 |
| 张全蛋    |   44 |
| 赵铁柱    |   45 |
| 张飞      |   45 |
+----------+------+

多段排序

以 class 和 birthday 从大到小的顺序查询 student 表。

SELECT * FROM student ORDER BY class DESC, birthday;
+----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+----+-----------+-----+------------+-------+
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
+----+-----------+-----+------------+-------+

事务

在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中，事务的自动提交状态默认是开启的。

- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+-------------+
| @@AUTOCOMMIT |
+-------------+
|            1 |
+-------------+

自动提交的作用：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能回滚。

什么是回滚？举个例子：

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
+---+------+-------+

可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚：

- 回滚到最后一次提交
ROLLBACK;

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
+---+------+-------+

由于所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？

- 关闭自动提交
SET AUTOCOMMIT = 0;

- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+-------------+
| @@AUTOCOMMIT |
+-------------+
|            0 |
+-------------+

将自动提交关闭后，测试数据回滚：

INSERT INTO user VALUES (2, 'b', 1000);

- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，
- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+---+------+-------+

- 数据表中的真实数据其实还是：
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
+---+------+-------+

- 由于数据还没有真正提交，可以使用回滚
ROLLBACK;

- 再次查询
SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
+---+------+-------+

那如何将虚拟的数据真正提交到数据库中？使用 COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
- 手动提交数据（持久性），
- 将数据真正提交到数据库中，执行后不能再回滚提交过的数据。
COMMIT;

- 提交后测试回滚
ROLLBACK;

- 再次查询（回滚无效了）
SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+---+------+-------+

总结

自动提交

查看自动提交状态：SELECT @@AUTOCOMMIT ；

设置自动提交状态：SET AUTOCOMMIT = 0 。

手动提交

@@AUTOCOMMIT = 0 时，使用 COMMIT 命令提交事务。

事务回滚

@@AUTOCOMMIT = 0 时，使用 ROLLBACK 命令回滚事务。

事务的实际应用，让我们再回到银行转账项目：

- 转账
UPDATE user set money = money - 100 WHERE name = 'a';

- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+---+------+-------+

这时假设在转账时发生了意外，就可以使用 ROLLBACK 回滚到最后一次提交的状态：

- 假设转账发生了意外，需要回滚。
ROLLBACK;

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+---+------+-------+

这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：COMMIT 。

手动开启事务 - BEGIN / START TRANSACTION

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

- 由于手动开启的事务没有开启自动提交，
- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+---+------+-------+

- 测试回滚
ROLLBACK;

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+---+------+-------+

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+---+------+-------+
| id | name | money |
+---+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+---+------+-------+

- 提交数据
COMMIT;

- 测试回滚（无效，因为表的数据已经被提交）
ROLLBACK;

事务的 ACID 特征与使用

事务的四大特征：

A 原子性：事务是最小的单位，不可以再分割；
C 一致性：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；
I 隔离性：事务1 和事务2 之间是具有隔离性的；
D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

READ UNCOMMITTED ( 读取未提交 )

如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
READ COMMITTED ( 读取已提交 )

只能读取到其他事务已经提交的数据。
REPEATABLE READ ( 可被重复读 )

如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
SERIALIZABLE ( 串行化 )

所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

- MySQL 8.x, GLOBAL 表示系统级别，不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+-------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+-------------------------------+
| REPEATABLE-READ                | - MySQL的默认隔离级别，可以重复读。
+-------------------------------+

- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别：

- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

- 查询系统隔离级别，发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+-------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+-------------------------------+
| READ-UNCOMMITTED               |
+-------------------------------+

脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性：

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+---+-----------+-------+

- 开启一个事务操作数据
- 假设小明在淘宝店买了一双800块钱的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

- 然后淘宝店在另一方查询结果，发现钱已到账。
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+---+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 ROLLBACK 命令，会发生什么？

- 小明所处的事务
ROLLBACK;

- 此时无论对方是谁，如果再去查询结果就会发现：
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+---+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 READ COMMITTED ：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+-------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+-------------------------------+
| READ-COMMITTED                 |
+-------------------------------+

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

- 正在操作数据事务（当前事务）
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

- 虽然隔离级别被设置为了 READ COMMITTED，但在当前事务中，
- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+---+-----------+-------+


- 假设此时在远程开启了一个新事务，连接到数据库。
$ mysql -u root -p12345612

- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+---+-----------+-------+

但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：

- 小张在查询数据的时候发现：
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+---+-----------+-------+

- 在小张求表的 money 平均值之前，小王做了一个操作：
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

- 此时表的真实数据是：
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+---+-----------+-------+

- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
SELECT AVG(money) FROM user;
+-----------+
| AVG(money) |
+-----------+
|  820.0000  |
+-----------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。

幻读

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+-------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+-------------------------------+
| REPEATABLE-READ                |
+-------------------------------+

测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :

- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

- 小王 - 北京
START TRANSACTION;

- 小张 - 成都
COMMIT;

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+---+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

INSERT INTO user VALUES (6, 'd', 1000);
- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了，操作被告知已存在主键为 6 的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。

串行化

顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+-------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+-------------------------------+
| SERIALIZABLE                   |
+-------------------------------+

还是拿小张和小王来举例：

- 小张 - 成都
START TRANSACTION;

- 小王 - 北京
START TRANSACTION;

- 开启事务之前先查询表，准备操作数据。
SELECT * FROM user;
+---+-----------+-------+
| id | name      | money |
+---+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+---+-----------+-------+

- 发现没有 7 号王小花，于是插入一条数据：
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢？由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。

mysql高级篇

触发器

触发器及要素

1.触发器是mysql中的一种特殊的事务，在mysql中触发器*主要针对增删改，监视和触发，对查询无效*  

,触发器英文:trigger  

2.四个要素：**监视地点(table)、监视事件(insert/delete/update)、触发时间(after/befor)、触发事件(insert/delete/update)**

事例：商品需求与订单goods table和ord table分析：监视ord表中的行为，监视动作:insert，触发时间:暂选after，触发事件:update goods表

-创建两个表，先为goods表插入数据

insert into goods values  

(1,"猫",34),  

(2,"狗",65),  

(3,"马",21)  

select * from goods;  

+-----+------+--------+  

| gid  | name | number |  

+-----+------+--------+  

|    1 | 猫   |     34 |  

|    2 | 狗   |     65 |  

|    3 | 马   |     21 |  

+-----+------+--------+  

3 rows in set (0.00 sec)

-用户购买两条小猫咪，ord表中的订单号是123

insert into ord values(123,1,2);  

Query OK, 1 row affected (0.00 sec)

select * from ord;

+-----+------+------+  

| oid  | gid  | much |  

+-----+------+------+  

|  123 |    1 |    2 |  

+-----+------+------+  

1 row in set (0.00 sec)

-在没有触发器的情况下，订单结果更新到商家

update goods set number = number -2 where gid = 1;  

Query OK, 1 row affected (0.01 sec)  

Rows matched: 1  Changed: 1  Warnings: 0

select * from goods;

+-----+------+--------+  

| gid  | name | number |  

+-----+------+--------+  

|    1 | 猫   |     32 |  

|    2 | 狗   |     65 |  

|    3 | 马   |     21 |  

+-----+------+--------+  

3 rows in set (0.00 sec)

-没有触发器的情况下会很繁琐，用触发器来解决这个问题

-创建触发器:

delimiter $   

create trigger t1  

after  

insert  

on ord  

for each row  

begin

update goods set number = number - 2 where gid = 1;

end$  

Query OK, 0 rows affected (0.01 sec)

trigger的具体写法：

第一、由于在触发器中会执行sql语句，语句是以分号来结束，这与创建整个trigger语句的分号冲突，所以，需要用**delimiter把外层系统默认的；结束符号改成其他的符号加以区分**。  

第二、触发器的创建不需要在外层加括号，加了之后会报错。  

第三、after insert on ord for each row表示的是ord引发触发器的一个动作，begin...end$表示的是触发器启动后触发goods表执行的命令

-查看触发器：

show triggers \G;  

*************************** 1. row ***************************  

         Trigger: t1  

           Event: INSERT  

           Table: ord  

       Statement: begin  

update goods set number = number - 2 where gid = 1;  

end  

          Timing: AFTER  

         Created: 2019-10-26 10:55:02.68  

        sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION  

         Definer: root@localhost  

character_set_client: utf8mb4  

collation_connection: utf8mb4_unicode_ci  

  Database Collation: utf8mb4_unicode_ci  

1 row in set (0.00 sec)

-由于改变了结束符号，系统中再使用分号结束就会报错，应该用$结束,查看的结果是乱码的，那么用\G来查看会得到上边的结果。

-上边的trigger只能解决固定的一次购买两个猫咪的订单，无法购买其他的动物或者其他数量的猫咪，因此下边重新生成一个变量的trigger

delimiter $   
create trigger t1	  
after   
insert   
on ord   
for each row   
begin  
update goods set number = number - new.much where gid = new.git; end$  
Query OK, 0 rows affected (0.02 sec)

在goods的表中用new来代表新的行，新行的两个字段来表示每一次订单的具体量和参照值，从而形成动态的商品数据库。

-创建第二个触发器用于解决取消订单退货的情况

delimiter $  

create trigger t3  

after  

delete  

on ord  

for each row  

begin  

update goods set number = number + old.much where gid = old.gid;  

end$  

Query OK, 0 rows affected (0.01 sec)

需要注意的是在通t3中after后跟的是delete删除，number后跟的是+号

-删除oid为123号的订单

delete from ord where oid = 123$  

Query OK, 1 row affected (0.01 sec)

-ord表已取消订单

select * from ord$  

+-----+------+------+  

| oid  | gid  | much |  

+-----+------+------+  

|  121 |    1 |    2 |  

|  122 |    2 |    2 |  

+-----+------+------+  

2 rows in set (0.00 sec)

-订单取消后的goods表单添加了ord表单中取消的订单数。

select * from ord$  

Empty set (0.00 sec)  

select * from goods$  

+-----+------+--------+  

| gid  | name | number |  

+-----+------+--------+  

|    1 | 猫   |     30 |  

|    2 | 狗   |     65 |  

|    3 | 马   |      5 |  

+-----+------+--------+  

3 rows in set (0.00 sec)

-在下了订单之后，在对订单进行修改，重新下单

-先创建一个触发器t4来修改订单，思路是先退还原有订单量，在进行购买

delimiter $  

mycreate trigger t4  

before   

update  

on ord  

for each row  

begin  

update goods set number = number + old.much - new.much where gid = new.gid;  

end$  

Query OK, 0 rows affected (0.01 sec)

-对已下订单进行修改

update ord set much = 15 where oid = 1$  

Query OK, 1 row affected (0.00 sec)  

Rows matched: 1  Changed: 1  Warnings: 0

-修改之后的ord和goods的表单结果，都进行了调整。

select * from goods$  

+-----+--------+--------+  

| gid  | name   | number |  

+-----+--------+--------+  

|    1 | 猫     |     30 |  

|    2 | 狗     |     65 |  

|    3 | 马     |      5 |  

|    4 | 兔子   |      8 |  

|    5 | 羊     |     10 |  

+-----+--------+--------+  

5 rows in set (0.00 sec)

-查看ord表单

select * from ord$  

+-----+------+------+  

| oid  | gid  | much |  

+-----+------+------+  

|    1 |    4 |   15 |  

+-----+------+------+  

1 row in set (0.00 sec)

-before和after的用法区别

select * from ord$  

+-----+------+------+  

| oid  | gid  | much |  

+-----+------+------+  

|    1 |    4 |   15 |  

+-----+------+------+  

1 row in set (0.00 sec)

-增加新的订单

insert into ord values(2,3,7)$  

Query OK, 1 row affected (0.00 sec)  

select * from goods$  

+-----+--------+--------+  

| gid  | name   | number |  

+-----+--------+--------+  

|    1 | 猫     |     30 |  

|    2 | 狗     |     65 |  

|    3 | 马     |     -2 |  

|    4 | 兔子   |      8 |  

|    5 | 羊     |     10 |  

+-----+--------+--------+  

5 rows in set (0.00 sec)

更改已经提交过的订单中的商品数量

update ord set much = 20 where oid = 1$

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0
查看goods表单中的数据，出现了负值的存储情况，触发器设置出了问题

select * from goods$

+-----+--------+--------+

| gid | name | number |

+-----+--------+--------+

| 1 | 猫 | 30 |

| 2 | 狗 | 65 |

| 3 | 马 | -2 |

| 4 | 兔子 | 3 |

| 5 | 羊 | 10 |

+-----+--------+--------+

5 rows in set (0.00 sec)
查看ord表单中的订单详情

select * from ord$

+-----+------+------+

| oid | gid | much |

+-----+------+------+

| 1 | 4 | 20 |

| 2 | 3 | 7 |

+-----+------+------+

2 rows in set (0.00 sec)

-对比before和after用法之间的区别

delimiter $  

create trigger t2  

after  

insert  

on ord  

for each row  

begin  

declare  

rnum int;  

select number into rnum from goods where gid = new.gid;  

if new.much > rnum then  

	set new.much = rnum;  

end if;  

update goods set number = number - new.much where gid = new.gid;  

end$

**注意：按照上边的输入，系统进行了报错，因为在after inter后订单已经提及了没有办法

在进行改动，需要在before insert前提前判断库存是否够用，不够的话立即将即将insert

的new订单量改成所有的库存量，after后是不能再进行修改**

-修改after为before，重新创建触发器

create trigger t2  

before  

insert  

on ord  

for each row  

begin  

declare  

rnum int;  

select number into rnum from goods where gid = new.gid;  

if new.much > rnum then  

	set new.much = rnum;  

end if;  

update goods set number = number - new.much where gid = new.gid;  

end$  

10:22:36 0 row(s) affected	0.014 sec

触发器创建成功,需要注意的两点：

1.在sql中的判断语句用到的是if <条件语句>then <控制语句>；end if；两个冒号不能少

2.变量的声明用declare，声明变量包括三个部分：declare 变量名变量类型

-输入超量的订单测试一下

insert into ord values(3,3,25)$  

Query OK, 1 row affected (0.01 sec)

-查看3号订单和原来提交的订单中商品数量是否一致

select * from ord$  

+-----+------+------+  

| oid  | gid  | much |  

+-----+------+------+  

|    1 |    4 |   20 |  

|    2 |    3 |    7 |  

|    3 |    3 |   20 |  

+-----+------+------+  

3 rows in set (0.00 sec)

结果是当数量超过库存量的时候，订单上的数量自动转换成库存量。

for each row的用法

在orical触发器中，触发器分为语句级触发器和行级触发器

delimiter $   

create trigger t1  

after  

insert  

on ord  

for each row

for each row是一个行级触发器，就是只要前边的触发事件发生，触发器就需要在后边表单中
每一行都要执行begin...end$中的语句指令,如果在触发器中for each row没有写，那么无论
无论程序设置中想要影响多少行，最终触发器只会执行一次，目前msql程序中还不支持语句级触
发器，因此，所有的mysql触发器程序的编写中必须加上for each row行级触发器程序，在oracle
中可以不写for each row ,那么触发器就会变成语句级触发器。

begin  
update goods set number = number - 2 where gid = 1;

end$  

Query OK, 0 rows affected (0.01 sec)

存储过程

在一些语言中，存在过程procedure和函数function,也有一些语言只有函数而没有过程;

过程和函数的区别：

过程：封装了若干条的语句，调用时这些封装体自动执行，没有返回值
函数：是一个有返回值的"过程"

mysql中的过程和存储：

过程：与其他语言一致，mysql语句的过程就是把若干条sql语句封装起来，起个名字
存储过程：就是把此过程存储在数据库中，在musql中不支持短期的匿名过程。

mysql过程的基本写法、查看及调用存储过程：

-基本的格式：

create procedure procedureName()
begin
sql语句指令
end 结束符号
注意sql语句后边要加上结束符号 ,并且过程中的sql语句可以有若干个。

-事例：

delimiter $  
create procedure p1()
begin
select number from goods;
end$
Query OK, 0 rows affected (0.01 sec)

-查看过程：

show procedure status

-调用存储过程：

call procedureName()
存储过程名字后必须加上小括号，即使没有参数

例如：调用上边创建的p1  

call p1() $
+-------+
| number |
+-------+
|     30 |
|     65 |
|     10 |
|      0 |
|     15 |
+-------+
5 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

-总结：

存储过程是可以编程的,意味着可以使用变量、表达式以及控制语句，来完成复杂的功能。

存储过程的变量和控制结构

声明变量

格式：declare 变量名变量类型 [default 默认值]

在写法上变量的声明和表的具体字段创建上是一致的。

-事例：

create procedure p2()
    -> begin
    -> declare age int default 18;
    -> declare height int default 180;
    -> select concat("年龄是",age,"身高是",height);
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p2()$
+-------------------------------------------+
| concat("年龄是",age,"身高是",height)       |
+-------------------------------------------+
| 年龄是18身高是180                          |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<!- concat的作用是将上边声明的两个变量粘在一起 -->

变量的运算

1.虽然在过程中声明了变量，但是这并不算严格意义上的编程。存储过程中变量可以做合法的运算，如+ — * /

2.将运算结果赋值给变量，格式为：set 变量名 : = expression。

-事例：

create procedure p3()
    ->   begin
    ->   declare age int default 18;
    ->   declare height int default 180;
    ->   set age:= age + 20;
    ->   select concat("20年后的年龄是",age,"身高是",height);
    ->   end $
Query OK, 0 rows affected (0.00 sec)

-调用过程p3
call p3()$
+------------------------------------------------------+
| concat("20年后的年龄是",age,"身高是",height)          |
+------------------------------------------------------+
| 20年后的年龄是38身高是180                             |
+------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

条件控制结构

-控制结构：

控制结构的基本语法：

if condition then

满足条件的语句快；

elseif condition then <!-注意：elseif是连着写的，如果分开系统会一直报错,在python中是elif，在js中是分开写的else if，也可以换行写-->

满足条件的语句块

else

不满足上边所有条件的语句块；

end if;

delimiter $
create procedure p4()
    -> begin
    -> declare age int default 18;
    -> if age >= 18 then
    -> select "已成年";
    -> else
    -> select "未成年";
    -> end if;
    -> end$
Query OK, 0 rows affected (0.01 sec)

-调用存储过程
call p4$
+----------+
| 已成年    |
+----------+
| 已成年    |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

存储过程的参数传递：

前边的演示中存储过程都没有参数，现在在括号内给过程传递相应的参数

参数传递的格式：

[in/out/inout] 参数名参数类型

-事例

create procedure p5(age1 int,age2 int)
begin
if age1 > age2 then
	select "第一个更大";
elseif age1 < age2 then
	select "第二个更大";
else
	select "第三个更大";
end if;
end$
Query OK, 0 rows affected (0.00 sec)

-调用存储过程，并在括号内传递参数
mysql> call p5(19,28)$
+----------------+
| 第二个更大      |
+----------------+
| 第二个更大      |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<!-创建存储过程时候括号里的参数是形参，在call语句调用的时候根据形参类型传递实参-->

循环控制结构

在msql中的循环结构是while语句

基本语法为:

while condition do

满足条件的语句块

end while;

-事例1(给定具体的条件区间)：循环计算0-100之间，包括100的所有数值的和

create procedure p6()
    -> begin
    -> declare sum int default 0;
    -> declare  num int default 0;
    -> while num <= 100 do
    -> set sum := sum + num;
    -> set num := num + 1;
    -> end while;
    -> select sum;
    -> end$
Query OK, 0 rows affected (0.00 sec)

-调用存储过程
call p6()$
+-----+
| sum  |
+-----+
| 5050 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-事例2：没有固定的区间，需要通过实参来调用存储过程

[ in/out/inout ]

-the 'in' argu is input type,which can get the value we input .

-the 'out' argu is out type, which can output the resault of program .

in的用法

in的作用是把外部输入的参数传递进存储过程中

delimiter $
create procedure p7(in n int)
    -> begin
    -> declare sum int default 0;
    -> declare num int default 0;
    -> while num <= n do
    -> set sum := sum + num;
    -> set num := num + 1;
    -> end while;
    -> select sum;
    -> end$  
Query OK, 0 rows affected (0.01 sec)

-调用存储过程：
call p7(12)$
+-----+
| sum  |
+-----+
|   78 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<!-set赋值冒号必须加上，如果不加冒号，在后边while循环中会将循环输出的次数增加到变量-->

out的用法

out的作用：

1.对于即将在程序中运行并且最终需要输出的变量，不用在程序中再声明，直接在参数内通过（out 变量名变量类型）的方式来声明

2.在procedure中循环结束的后不需要再写select 变量输出的语句，out本身就有输出的功能

需要注意：在用到out的时候，由于out定义的参数不能赋值，所以需要在程序里给参数初始化，例如事例中的set sum := 0，如果设置初始值，那么最后输出的结果只能为NULL

delimiter $
create procedure p8(in n int,out sum int)
    begin
    declare num int default 0;
    set sum:= 0;
    while num <= n do
    set sum := sum + num;
    set num := num + 1;
    end while;
    end$  
-调用储存过程:
1.sum作为输出型的变量，如果我们也给定一个量，那么就会报错
call p8(40,0)$
Error Code: 1414. OUT or INOUT argument 2 for routine bank.p8 is not a variable or NEW pseudo-variable in BEFORE trigger	0.0018 sec
2.如果不给sum赋值，那么也会报错：缺少一个参数
call p8(40)$
Error Code: 1318. Incorrect number of arguments for PROCEDURE bank.p8; expected 2, got 1 0.00069 sec
3.正确的做法是：在call调用时给out输出型变量提供一个变量名(名字不限),在程序运行结束后结果会赋给变量名
call p8(40,@total)
0 row(s) affected	0.0015 sec
select @total;
select @total LIMIT 0, 1000	1 row(s) returned	0.00066 sec / 0.000022 sec

inout用法：

这种类型实质上是in和out两种类型的糅合，用法上也基本一致。

第一步：由于in 类型需要参数，程序中由没有设置，所以在调用存储过程的时候，需要先设置参数初始值，第二步：同时具有out类型的特征，所以需要需要给输出变量赋一个变量名，用于输出结果。

delimiter $;
create procedure p9(inout age int)
begin
set age := age + 20;
end$

-call调用存储过程:
1.直接传入实参，会报错，因为参数即是in类型又是out类型
call p9(20)	Error Code: 1414. OUT or INOUT argument 1 for routine bank.p9 is not a variable or NEW pseudo-variable in BEFORE trigger	0.0012 sec
2.正确的做法：给参数确定一个输出的变量名，然后给参数设置初始值，再将变量名传入调用语句中输出结果。
set @sum := 18;
Query OK, 0 rows affected (0.00 sec)

call p9(@sum);
Query OK, 0 rows affected (0.00 sec)

select @sum;
+-----+
| @sum |
+-----+
|   38 |
+-----+
1 row in set (0.00 sec)

case用法：

1.case用法格式：

case 变量名

when 值1 then

执行语句块

when 值2 then

执行语句块

when 值3 then

执行语句块

else

执行语句块

2.这种用法和js和python中的用法基本类似，python中是switch....case.....，js中是switch....case....default.....

-创建procedure
p10
delimiter $
create procedure p10()
begin
declare pos int default 0;
set pos := floor(rand(pos) * 4);
case pos
when 1 then
select "西瓜";
when 2 then
select "水果";
when 3 then
select "桃子";
else
select "其他的水果";
end case;
end$
0 row(s) affected	0.0044 sec

-调用存储过程：
call p10();
+----------------+
| 其他的水果      |
+----------------+
| 其他的水果      |
+----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

repeat循环用法：

基本用法格式：

repeat

sql statement1

sql statement2

.....

until condition

end repeat;

-创建procedure p11
delimiter $
create procedure p11()
begin
declare i int default 0;
declare sum int default 0;
repeat
	set i := i + 1;
	set sum := sum + i;
until i> 10
end repeat;
select sum;
end$

-调用存储过程：
call p12();
+-----+
| sum  |
+-----+
|   21 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<!-注意：end repeat后边不能加分号-->

游标

游标的概念

游标(cursor)

一条sql语句对应N条结果集的资源，取出资源的接口/句柄，就是游标

沿着游标就可以一次取出一行，每取完一次游到下一行

游标的意义在于可以通过循环语句取出N条希望得到资源，而不限于select一次一条的情况。

游标的用法：

-声明游标：

声明格式：

declare cursorName cursor for select sentence;

-打开游标：

打开格式：

open cursorName;

-取值：

取值格式：

fetch cursorName into variable1,variable2,variable3......;

<!-注意，在声明cursor中，select语句如果设定一次获取一个表单资源中的多个字段，那么在fetch之前声明分别与字段一一对应的variableName，将variableName放在into后用于接收select出来的字段资源-->

-关闭游标：

关闭格式：

close cursorName;

-事例1：

-创建procedure，添加cursor
delimiter $
create procedure p13()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,number,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
close getgoods;
select row_gid,row_name,row_num;
end$

-调用存储过程：
call p13;
+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       1 | 猫       |      30 |
+--------+----------+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

事例2:

-创建procedure p14:
```shell
delimiter $
create procedure p14()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,number,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_name,row_num;
close getgoods;
end$

-调用存储过程：
1.正确的结果是：
call p14();
+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
| 1 | 猫 | 30 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
| 2 | 狗 | 65 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
| 5 | 羊 | 10 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
| 3 | 马 | 15 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
| 4 | 兔子 | 15 |
+--------+----------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
2.如果在取值过程中fetch的行数超过了表单资源的行数，那么就会报如下错误。
call p14();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed


**分析事例1和事例2： 事例1中只fetch一次，就只能获取到表单的第一行，事例2中回去全部的表单资源，fetch、select语句的数量需要和表单row数量一致，超出就会报错02000**

### 循环游标

-用法：

> 一、基本思路：用循环控制游标的次数，最重要的是获得到一份表单资源具体的行的数量，将其作为循环控制条件，再进行游标的取值和输出。
>
> 二、用法格式：
>
> 1. 声明循环需要用到的变量i，初始化
> 2. 声明用于接收fetch出来的具体字段资源的一一对应的variableName
> 3. 声明一个variableName，通过select count( * ) into variableName from tableName;将接收到的表单总行数信息赋给variableName。
> 4. 正式进行游标取值，在循环条件的编写中一定要注意条件的越界问题。
> 5. 注意：用于最后输出的select语句如果放在了close cursorName；之后，那么select得到的信息只会是表单中的最后一行，所以需要把select语句房子啊循环体内。

-事例：

```shell
-创建procedure p15，设置循环控制游标
delimiter $
create procedure p15()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare i int default 0;
declare cnt int default 0;
declare getgoods cursor for select gid,name,number from goods;
select count(*) into cnt from goods;
open getgoods;
repeat
set i := i + 1;
fetch getgoods into row_gid,row_name,row_num;
select row_gid,row_name,row_num;
until i>=cnt
end repeat;
close getgoods;
end$

-调用存储过程：

call p15();
+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       1 | 猫       |      30 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       2 | 狗       |      65 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       5 | 羊       |      10 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       3 | 马       |      15 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       4 | 兔子     |      15 |
+--------+----------+---------+
1 row in set (0.00 sec)

游标越界问题

-declare条件处理越界问题

在mysql cursor中，可以用declare continue handler来操作一个越界标识

具体格式：

声明一个variableName初始化为任意一个值

在open cursorName前编写：

declare continue handler for NOT FOND set variableName := 另外一个值;

handle：操作者、操控者、句柄

这一句的具体含义是：handle会一致操控游标的运行，直到cursor找不到new row，即NOT FOUND,此时将variableName设置成另外一个值

循环的判断条件是当variableName = 初始值时，游标循环运行，并select，直到 != 初始值的时候，结束循环。

-事例：

-创建procedure p16

delimiter $
create procedure p16()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare i int default 1;
declare getgoods cursor for select gid,name,number from goods;
declare continue handler for not found set i := 0;
open getgoods;
while i = 1 do
	fetch getgoods into row_gid,row_name,row_num;
    select row_gid,row_name,row_num;
end while;
close getgoods;
end$

-调用存储过程：

call p16();
+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       1 | 猫       |      30 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       2 | 狗       |      65 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       5 | 羊       |      10 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       3 | 马       |      15 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       4 | 兔子     |      15 |
+--------+----------+---------+
1 row in set (0.00 sec)

+--------+----------+---------+
| row_gid | row_name | row_num |
+--------+----------+---------+
|       4 | 兔子     |      15 |
+--------+----------+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<!-注意：此时用declare声明条件后重复取了最后一行的值-->

解决游标越界问题

使用continue导致游标越界的原因：continue是继续的意思，当游标读取到超过行数的时候就会激发declare continue语句，重新给viriableName赋值，但赋值后会继续continue的指令，再执行一次循环结束。

针对上边的游标越界的问题，可以将continue改成exit，即重新赋值后立刻结束程序。

continue、exit和undo的区别：

continue:触发declare语句后，再执行一次

exit：一旦触发declare语句后立即结束程序

undo：触发declare语句后，前边的语句撤销，目前mysql还不支持undo

-事例：

-创建procedure p17，将continue改成exit
delimiter $
create procedure p17()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare i int default 1;
declare getgoods cursor for select gid,name,number from goods;
declare exit handler for not found set i := 0;
open getgoods;
while i = 1 do
	fetch getgoods into row_gid,row_name,row_num;
    select row_gid,row_name,row_num;
end while;
close getgoods;
end$
-调用存储过程后不再出现游标越界的问题。

游标循环读取的正确逻辑

-总结

上边的几个procedure cursor都能fetch表单数据，exit也解决了游标越界的问题，但是都是建立在表单内容不为空的基础上的，如果表单为空，那么fetch的只能是NULL和default。

事例：

-创建procedure p18
delimiter $
create procedure p18()
begin
declare i int default 1;

declare row_gid int;
declare row_num int;
declare row_name varchar(20);

declare getgoods cursor for select gid,name,number from goods;
declare continue handler for NOT Found set i := 0;

open getgoods;
fetch getgoods into row_gid,row_name,row_num;

repeat
fetch getgoods into row_gid,row_name,row_num;
select row_gid,row_name,row_num;
until i = 0
end repeat;
close getgoods;
end$

主从复制

主从复制原理

概念

1.主服务器：maste 从服务器：slave

2.主从复制：maste所从事的任何指令，在slave上都施加同样的影响，master运行后会产生binlog，slave需要将master的binlog复制过来，转换成能为自己所用的relaylog，施加在自己身上

3.master需要配置binlog，slave需要配置relaylog

4.由于binlog关系到数据库的安全问题，所以如果从slave要复制binlog，需要得到master的授权。slave用账号连接master。

主从配置过程

-server-id

由于主从服务器配置中容易产生混乱，所以需要给每一个master和slave都起一个server-id来用于识别

-binlog配置的格式：

1.server—id = 1 / ip末尾后三位

1是本机的序列号，表示master；或者用IP的后三位来作为id，这是默认的用法

2.log-bin = mysql-bin

声明二进制的文件为myql-bin.xxx，相当于打开二进制文件用于记录，后续如果数据丢失可以通过二进制文件尝试修复。

3.binlog-format = mixed/row/statement

二进制的格式有：mixed混合的、row行级的、statement语句级的。

2.row二进制记录的是磁盘上总的变化，指令语句影响行数少，占用磁盘少，建议用row

3.statement二进制记录的是具具体到某一条语句的变化，如insert/update......。指令语句影响行数多，占用磁盘多，建议用statement

4.mixe二进制采用row和satement混合的方式记录，如果不确定如何使用，建议用mixed，有系统分析语句来决定

4.删除原有的服务器日志的配置：

格式为：

rm -rf . /mysql-bin*

5.slave在配置的过程中比master多一个relaylog

relay-log = mysql-relay

posted @ 2020-04-09 15:54 大道至诚阅读(441) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

大道至诚

数据库基本语法及规范

基本语法及规范

一、用户及权限

二、数据库

三、表

四、查询select/show(DQL):

五、多表连接查询与子查询

(一)、内连接(使用最多)

(二)、外连接

(三)、子查询

六、别名的使用

七、sql_mode和concat拼接问题

八、元数据的使用

九、show语句的使用

十、索引及执行计划

一、索引

二、执行计划分析

主键约束

唯一主键

非空约束

默认约束

外键约束

子查询加分组求平均分

子查询 - 1

子查询 - 2

YEAR 函数与带 IN 关键字查询

多层嵌套子查询

子查询 - 3

ANY 表示至少一个 - DESC ( 降序 )

表示所有的 ALL

复制表的数据作为条件查询

条件加组筛选

NOTLIKE 模糊查询取反

YEAR 与 NOW 函数

多段排序

事务

如何控制事务 - COMMIT / ROLLBACK

手动开启事务 - BEGIN / START TRANSACTION

事务的 ACID 特征与使用

事务的隔离性

脏读

读取已提交

幻读

串行化

mysql高级篇

触发器

触发器及要素

for each row的用法

存储过程

过程和函数的区别：

mysql中的过程和存储：

mysql过程的基本写法、查看及调用存储过程：

存储过程的变量和控制结构

声明变量

变量的运算

条件控制结构

存储过程的参数传递：

循环控制结构

in的用法

out的用法

inout用法：

case用法：

repeat循环用法：

游标

游标的概念

游标(cursor)

游标的用法：

游标越界问题

解决游标越界问题

游标循环读取的正确逻辑

主从复制

主从复制原理

概念

主从配置过程

公告