第八节：DCL

Insert

语法格式：insert into tb_name[字段1,...,字段n] values(值1,...,值n);
注意点：
1. tb_name后面的字段可以省略，则values后面的值必须是表中所有字段的值（insert into Teacher values(1,'Tom',17)）
2. tb_name后面添加了字段，那么values后面的值必须要与字段名称一一对应（insert into Teacher(id,age,name) values(2,18,'Weiking')）
3. insert语句既可以向表中插入一行数据，也可以插入多行数据（insert into Teacher values(3,'jack',19),(4,'James',20)）
4. insert...select 语句可以向表中插入其他表的数据（insert into man (id,name,age) select id,name,age from Teacher;）
5. 使用单条insert语句处理多个插入要比多条insert语句处理单个插入要快

Update

语法格式：update tb_name set 字段1=值1,...,字段2=值n [where语句][order by语句] [limit语句]
语法说明：
1. where语句：用于指定需要修改的行，若不指定表示修改所有的行
2. order语句：用于指定排序之后的数据进行修改
3. limit：限制修改的行数
注意：update和delete语句在实际应用中要加上where语句避免修改删除所有的数据造成错误

实例:

update Teacher set age = 26; #修改表中所有的行
update Teacher set age =16 where id =1; #根据条件修改表中的数据

Delete

语法格式：delete from tb_name [where 语句] [order by 语句] [limit 语句]

实例：

delete from Teacher; 删除所有的数据
delete from Teacher where id =1;根据条件删除表的数据

Select

语法格式：select 字段名称 from 数据源1,...,数据源n where 表达式 group by 分组字段 having 过滤条件 order by 排序字段 asc/desc limit(位置偏移量，输出条数)
语法说明：
1. select 字段名称：表示要查询的字段（*通配符表示所有的字段）
2. from 数据源1,...,数据源n：表示数据查询的来源，可以是单个数据源也可以是多个数据源（一次查询多个数据源会产生笛卡尔积）
3. where 表达式：限定查询行必须满足的条件
4. group by 分组字段：对where过滤后的数据集按照指定的字段进行分组
5. Having 过滤条件：对group by后的数据组进行过滤
6. order by 排序字段：对查询的字段按照规定的顺序进行排序（asc:升序。desc：降序）
7. limit(位置偏移量，行数):限制查询的结果集的条数

Where子句

注意点：聚合函数不能用于Where语句之后，select 后面的字段的别名也不能再where语句中使用。

实例

select * from president where birth=min(birth)  #错误，因为MySQL不允许在Where子句里面使用统计函数！
select * from president where birht=(select min(birth) from presidnet);

作用:指定查询条件，对数据源中的数据进行行的过滤
查询条件类型：
1. 字符匹配作为查询条件：可以使用Like关键字实现模糊匹配
2. 日期字段作为查询条件：可以使用比较运算符，也可以使用 BETWEEN AND 运算符：select * from Teacher where graDate between '2019-2-27' and '2020-2-27';

Group By子句

作用：将中间结果集中的数据根据选择列的值进行逻辑分组
语法格式：group by { <列名> | <位置> }
语法说明:
1. 列名：指定用于分组的列(可以指定多个列)
2. 位置：指定select语句结果集中的第几列为分组的列（如：group by 2）
注意点：
1. group by：分组的列可以是多个列
2. select 语句中的每个列都必须在group by子句中出现或者使用聚合函数进行聚合
3. 若分组的列中含有null，则将null作为一个单独的分组处理
实例: select age,count(1) as count from Teacher group by age;

Having子句

作用：用来过滤group by后的分组，在结果集中规定了包含哪些分组和排除哪些分组
where和Having的区别
1. where子句用于过滤数据行，Having子句用于过滤分组
2. HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。
实例：select age,count(age) as count from Teacher group by age having count > 1;

Order By子句

作用：对结果集中的数据按照一定的顺序进行排序
语法格式：order by 列名|位置 [asc|desc]
语法说明：
1. 列名：指定用于排序的列。可以指定多个列
2. 位置：指定select语句结果集中的第几列为排序的列（如：order by 2）
3. ASC|DESC：关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值
注意点：
1. 当排序的值中存在空值时，ORDER BY 子句会将该空值作为最小值来对待
2. 当在 ORDER BY 子句中指定多个列进行排序时，会先对左边的列进行排序，如果左边的列相等才会对右边的列排序，如果左边的列都不相等则不会对右边的列排序
3. 如果要对多列进行排序，则必须在每一列后面加上asc或者desc,如果某列没有加则会默认是升序
实例：select * from Teacher order by name desc,age desc;

Limit 子句

作用：限制查询的结果集的行数
语法格式：limit 位置偏移量，行数
语法解释:　　
1. 位置偏移量：是一个可选参数，指示 MySQL 从哪一行开始显示，省略表示从第一行纪录开始显示（第一行纪录的位置偏移量是0）
2. 行数:指示返回的记录条数
3. “LIMIT n”与“LIMIT 0，n”等价

实例：

select * from Teacher order by age limit 3; #显示前三行
select * from Teacher order by age limit 0,3; #同上，显示前三行
select * from Teacher order by age limit 3,5; #从第四行开始显示5行

子查询

学生表：
成绩表：
子查询：出现在其他的SQL语句内部的select语句叫做子查询，又叫做内部查询
主查询：包含子查询的查询语句叫做主查询，又叫做外部查询
子查询的语法：
1. 先执行子查询，然后执行主查询
2. 子查询必须放在小括号中
3. 子查询可以出现在select字句、from字句、where字句、order字句、having字句中
4. 子查询一般放在比较运算符右边，增加代码可持续性
什么时候用子查询
1. 在写查询语句时候，发现最后的结果需要多条查询语句才可以得到结果，并且多条查询语句之间存在某种依赖关系，这个时候就可以使用子查询
2. 实例：求周梅同学的所有学科的成绩（A查询语句的结果需要B两个查询语句才能得到相同的结果）
```
A：select * from score where score_stu = (select stu_num from student where stu_name = '周梅');
B:select stu_num from student where stu_name = '周梅';
select * from score where score_stu = 5;
```
内联视图：用在from子句中的子查询叫做内联视图（子查询相当于一个数据源）
1. 注意点：内联视图必须取别名，否则会报错
2. 实例：得到stu_num小于5的男学生的信息
```
select * from (select * from student where stu_num<5) as stu where stu_sex = '男';
```

常用的运算符和操作符

比较运算符：<、> 、=、!=等等
In：主要是判断主查询中的的某个值是否存在于子查询的结果集中
1. 实例：查询语文成绩大于等于80分的学生信息
```
select * from student where stu_name in (select stu_name from score where score_number >=80 and score_course = '语文');
```
All、Any操作符：不能单独使用，必须和比较运算符一起连用，主要是对主查询中的某个值和子查询返回的结果集进行比较
1. 1. All:主查询中的值要与子查询结果集中的每一个值进行比较，都满足比较关系时，才会返回True,否则返回False（<=all(结果集) ：释义为小于等于所有的，也就是求最小的）
  2. Some和Any:主查询中的值与子查询中的某一个满足比较关系时，就会返回True（>any(结果集)：释义为大于里面任何一个即可，也就是大于最小的）
  3. 实例：求语文成绩最少学生的所有成绩
```
select * from score where score_number <=all (select score_number from score where score_course ='语文');
select * from score where score_stu = (select score_stu from score where score_number = (select min(score_number) from score where score_course = '语文') and score_course ='语文');
```
EXISTS操作符:主要是用来判断子查询的结果集是否为空集，若不是空集则返回True,否则返回false
1. Exists操作符何时用：
  1. 常用语子查询中
  2. 需求中往往有至少的关键字
2. 实例：得到那些至少支付了一次罚款的球员的名字(就是拿着外查询的结果去比较内查询的结果，如果两个结果相符，则保留结果，否则将结果摒弃)
```
select name from players as pl where exists (select * from from penalties where playerno = pl.playerno);
```
NOT EXISTS操作符:主要是用来判断子查询的结果集是否为空集，若是空集则返回True,否则返回false
1. Exists操作符何时用
  1. 常用语子查询中
  2. 需求中往往有”不是”关键字
2. 实例：得到那些不是队长的球员的名字和首字母(teams:队长表)
```
select name from players as pl where not exists (select * from teams where playerno = pl.playerno )
```

子查询的分类

分成两类

不相关子查询：子查询中没有使用到外部查询的表中的任何列。先执行子查询，然后执行外部查询
相关子查询：子查询中使用到了外部查询的表中的列（子查询的where条件中用到了主查询的列）。先执行外部查询，然后执行子查询
相关子查询的执行步骤：
1. 先执行外部查询，得到的行叫做候选行
2. 使用某个候选行来执行子查询
3. 使用子查询的返回值来决定该候选行是出现在最终的结果集中还是被丢弃
4. 重复以上步骤2和3，将所有的候选行处理完毕，得到最终的结果
实例：对于球队的队长参加的每场比赛，得到比赛编号(就是拿着外查询的结果去比较内查询的结果，如果两个结果相符，则保留结果，否则将结果摒弃)
```
select matchno,playerno from matches as mwhere playerno = ( select playerno from teams  where playerno = m.playerno);
```

分成四类

标量子查询：就是子查询返回的数据就只有一行一列
1. 实例：查询赵雷同学的各科成绩
```
select * from score where score_stu = (select stu_num from student where stu_name = '赵雷' );
```

行子查询：就是子查询返回的数据就有一行有多列

实例：查询和钱电同学是性别相同并且住在同一城市的学生信息

select * from student where (stu_sex,stu_province) = (select stu_sex,stu_province from student where stu_name ='钱电') and stu_name != '钱电';

列子查询：就是子查询返回的数据有一列有多行

实例：查询学生语文成绩及格的学生信息

select * from student where stu_num in (select score_stu from score where score_course ='语文' and score_number>=60);

表子查询：就是子查询返回的数据有多行和多列
总结：
1. 单行(单列|多列)子查询：子查询的返回结果就只有一行数据，主查询语句的条件语句中引用子查询结果，可用单行比较符号（＝, >, <, >=, <=，!=）
2. 多行(单列|多列)子查询：子查询的返回结果是多行数据时（即返回的值是一个区间），主查询语句的条件语句中引用子查询结果时，必须用IN，NOT IN，ALL,ANY）

连接查询

连接查询:使用连接操作符实现对多个表进行查询（连接运算符有：cross join,inner join,outer join等等）
分类：
1. 内连接
2. 外连接
3. 交叉连接
笛卡尔积：是指两个关系中所有元素的任意组合（如：一张表中的所有的列和另一张表中所有的列进行连接，得到的结果集中的总行数就是两张表中行数的乘积，叫做笛卡尔积）

交叉连接

交叉连接：使用cross join 连接操作符实现对多个表的查询
语法：select 列名1，...,列名n from 表1 cross join 表2;
原理：将一张表中的每条记录与另外一张表中的每条记录组合成为一个新的记录
步骤：
1. 从第一张表中依次取出一条记录
2. 将第一张表中取出的每一条记录与另一张表中的全部记录进行匹配
3. 没有任何匹配条件，所有的结果都会进行保留
分类：
1. 隐式的交叉连接：没有CROSS JOIN连接符
```
select * from Student,Score;
```
2. 显式的交叉连接：使用CROSS JOIN连接符
```
select * from Student cross join Score;
```
注意点：
1. 交叉连接得到的结果就是笛卡尔积（包含两个表中所有行的组合）
2. 记录数 = 第一张表记录数 * 第二张表记录数
3. 字段数 = 第一张表字段数 + 第二张表字段数

内连接

内连接：使用 [inner] join连接操作符实现对多个表的查询（inner 可以省略）
语法： select 列名1，...,列名n from 表名1 [inner] join 表名2 ON 连接条件
原理：将一张表中的每条记录与另一张表中的每条记录进行匹配。如果两个表的相关字段满足连接条件，就提取两张表中的记录组合成新的记录，如果两个表的相关字段不满足连接条件则放弃该记录。
步骤：
1. 从第一张表中依次取出一条记录
2. 将第一张表中取出的每一条记录与另一张表中的全部记录进行匹配
3. 利用连接条件进行匹配，匹配成功则保留，继续向下匹配；匹配失败则放弃，继续向下匹配
4. 直到全部匹配结束
分类一：
1. 隐式的内连接：没有inner join，形成的中间表为两个表的笛卡尔积
```
select * from Student,Score where Student.id =student_id;
```
2. 显示的内连接：一般称为内连接，有inner join，形成的中间表为两个表经过ON条件过滤后的笛卡尔积
```
select * from Student join Score on Student.id =student_id;
```
分类二：
1. 等值连接：在on后面的连接条件中使用等于号(=)比较被连接列的列值；查询结果中列出被连接表中的所有列，包括其中的重复列
2. 不等连接：在on后面的连接条件中使用除等于运算符以外的其它比较运算符（>、>=、<=、<、!>、!< <>）比较被连接的列的列值
自连接：如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询就称为自连接查询
1. 一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名
2. 自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但在逻辑上分为两张表
3. 实例：
```
select * from Student as s1 join Student as s2 on s1.id =s2.id;
```

外连接

外连接：是用left,right,full [outer] join连接操作符实现对多个表的查询(outer 可以省略)
语法：
1. 左连接：select 列名1，...,列名n from 主表 left join 从表 on 连接条件；
2. 右连接：select 列名1，...,列名n from 从表 right join 主表 on 连接条件；
3. 全连接： select 列名1，...,列名n from 表1 full join 表2 on 连接条件；
应用：主表的数据全部需要，又想获取其他表的数据时候可以使用外连接
分类
1. 左外连接（left outer join）：左表是主表，右表时从表
  1. 左外连接：又称为左连接，使用关键字 left outer join 或者 left join来连接两张表；左连接返回左表所有行而不仅仅是匹配到的行，如果左表的某行在右表中没有匹配行，则在结果集中右表的相关字段的值为null
  2. 实例：
```
select * from Score left join Student on student_id = Student.id;
```
2. 右外连接（right outer join）：右表是主表，左表时从表
  1. 外右连接：又称为右连接，使用关键字right outer join或者 right join来连接两张表；右连接返回右表的所有行而不仅仅是匹配到的行，如果右表的某行在左表中没有匹配行，则在结果集中左表的相关字段的值为null
  2. 实例：
```
select * from Score right join Student on student_id = Student.id;
```
3. 全外连接（full outer join）
  1. 全外连接：又称为全连接，中使用关键字full outer join或者 full join来连接两张表；全连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值；如果表之间有匹配行，则整个结果集行包含基表的数据值。
  2. 实例：
```
select * from Score full join Student on student_id = Student.id;
```
特点：外连接中主表数据记录一定会保存：连接之后不会出现记录数少于主表
1. 左连接是限制连接关键字右端的表中的数据必须满足连接条件，而不管左端的表中的数据是否满足连接条件，均输出左端表中的内容
2. 右连接是限制连接关键字左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容
3. 全外连接查询的特点是左、右两端表中的元组都输出，如果没能找到匹配的元组，就使用NULL来代替

总结

每个派生表都必须要有自己的别名
连接查询的时候为避免出现两张表的字段同名而导致的错误，通常使用表名.字段名
如果使用到的表名比较长，可以通过设置表别名来简化
如果内连接没有匹配条件，结果为笛卡尔积，结果和交叉连接相同，两者可以互换
如果最终的结果集中的字段来自于多张表则必须要使用连接查询
如果最终的结果集中的字段来自于一张表，但是查询的中间结果集中用到了多张表，则可以使用子查询也可以使用连接查询（推荐使用子查询）
多表连接的时候注意看看是否能先对各个表分别进行查询，然后对查询之后的结果集进行连接。减少数据的join，优化sql
连接查询选择的依据：
1. 查两表关联列相等的数据用内连接
2. 左表是右表的子集时用右外连接
3. 右表是左表的子集时用左外连接
4. 两表彼此有交集但彼此互不为子集时候用全外
单表查询和两表查询的区别：
1. 单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果
2. 两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果
ON后面的条件（ON条件）和WHERE条件的区别：
1. ON条件：是过滤两个连接表产生的笛卡尔积而生成中间表的约束条件
2. WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束

联合查询

联合查询：是指使用union或者union all将多个查询语句的结果集纵向合并到一起。

基本语法：

select 语句
union /union all
select 语句
...
union /union all
select 语句

union和union all 的区别：（union会去掉重复的行，union all 不会去掉重复的行）

union：合并多个select语句的结果集时会消除表中的重复的行
union all：合并多个select语句的结果集时不会消除表中的重复行

实例：

insert into student1 values(1,'张三'),(2,'李四'),(3,'王五');
insert into student2 values(1,'孙六'),(2,'李四'),(3,'赵七');

select * from student1 union select * from student2; #返回五行，名字为李四只出现一行
select * from student1 union all select * from student2; #返回六行，名字为李四只出现两行

注意事项：
1. 各个SELECT 语句的结果集中字段数必须相同，对应字段的数据类型可以不同（推荐相同）
2. 各个SELECT 语句的的结果集中的字段名称可以不同，合并之后只保留第一个select语句中的字段名称
联合查询中Order by 的使用：
1. 先对 select 语句的结果集单独进行排序然后再进行合并
  1. 对应的select语句必须使用括号括起来
  2. order by 必须配合limit使用，而limit后面必须跟对应的限制数量（通常可以使用一个较大的值：大于对应表的记录数）
  3. order by如果不配合limit使用，会被语法分析器优化分析时去除而不起作用
  4. 实例：
```
(select stu_name,stu_birth from student order by stu_birth limit 10 ) union (select score_course,score_number from score order by score_number limit 100);
```
2. 先对select 语句的结果集进行合并然后再进行排序
  1. 将order by 放到所有的字句之后，即是对最终合并的结果来进行排序
  2. 实例：
```
select stu_name,stu_birth from student  union select score_course,score_number from score  order by stu_birth;
```