Mysql——CRUD进阶

一、表的设计

1. 数据库的约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 唯一约束，保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• NOT NULL - 和 UNIQUE 的结合。确保某列（或两个列多个列的结 合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 外键约束，保证一个表中的数据匹配另一个表中的值的参照完整性。
  例子：一个学生对应一个班级，一个班级对应多个学生。classes_id为外键，关联班级表的主键id FOREIGN KEY (classes_id) REFERENCES classes(id)
• PRIMARY KEY -主键约束，对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。
• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

2. 设计原则（数据库三大范式）

• 第一范式 —— 列不可再分
• 第二范式 —— 属性完全依赖于主键
• 第三范式 —— 属性不依赖于其他非主属性,直接依赖于主键

3.表的关系

• 一对一
  

• 一对多
  

• 多对多
  1 - m : n - 1
  
  下图为中间表的建表语句
  

在一对一的关系中，外键设计在两张表任意一张中都可以；
在一对多关系中，主表到从表通常是1对多，从表到主表1对1，外键设计在从表 ；
多对多关系中，需要设计一张中间表，设计两个外键分别关联两张主表的主键。

二、新增与复杂查询（难点）

可以使用 insert + select 完成先查询再插入的操作
例：
-- 将学生表中的所有数据复制到用户表 insert into test_user(name, email) select name, qq_mail from student;

1.聚合查询

1） 聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有如下，这些函数都是对结果集进行操作，而不是对表进行查询。是先查到expr的得到结果集，再对这个返回的结果集进行聚合函数，在得到结果。

注意点：
① count可以使用count(*),count(0),count(1),count(字段)。
② sum,avg,max,min都是传入字段或表达式使用。
使用如下：

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

2） GROUP BY

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是 “分组依据字段”，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

例如：查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

3） HAVING——group by后替换where

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

例如：显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)<1500;

2.联合查询——多表操作（重点）

多表查询是对多张表的数据取笛卡尔积

select * 可以查询所有列，多张表的列都会显示，结果集行数是每张表的行数相乘。

1）内连查询

连接的两张表都有共同的一列，如果一张有，一张没有，那么就不会显示。

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件1 and 连接条件2 where 其他条件1 and 其他条件2; 

例如：
查询所有同学的总成绩，个人信息，学生表对成绩表是一对多的。

-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的 
SELECT 
	 stu.sn, 
	 stu.NAME, 
	 stu.qq_mail, 
	 sum( sco.score )
FROM 
 	student stu 
 	JOIN score sco ON stu.id = sco.student_id 
GROUP BY 
 	sco.student_id;

2）外连查询

外连接分为左外连接和右外连接。左表/右表的数据不会依据连接过滤条件过滤而完全显示。 以左连接为例，左外连左表不依赖连接条件，连接后的结果集根据where条件再次过滤。

-- 左外连接，表1完全显示 
select 字段名 from 表名1 left join 表名2 on 连接条件; 
-- 右外连接，表2完全显示 
select 字段 from 表名1 right join 表名2 on 连接条件;

Tips： 外连与内连的区别
与内连不同的点在于结果集的显示上。可以理解为，在于显示上，内连只会显示两个表相同的列（不同的不会显示），外连是强制遵循一张表显示（如果是左连，就会强制显示左连的表的列，右表如果没有也会显示为null）。

例子：查询所有同学的成绩，及同学的个人信息，其中“老外学中文”该同学没有成绩，也需要显示

-- “老外学中文”同学没有考试成绩，也显示出来了
 select * 
 from student stu 
 left join 
 score sco 
 on 
 stu.id=sco.student_id;
 
-- 对应的右外连接为：
 select * 
 from score sco 
 right join 
 student stu 
 on 
 stu.id=sco.student_id;

3）自连查询

用于同一张数据，多行进行操作。具体的，自连接是指在同一张表连接自身进行查询，可以理解为，是多表查询，但是另一张表是自身。

例如：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
s1.*
FROM
score s1,
score s2
WHERE
s1.student_id = s2.student_id
AND s1.score < s2.score
AND s1.course_id = 1
AND s2.course_id = 3;

4）子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

① 单行子查询： 返回一行记录的子查询
例：

select * 
from student
where classes_id=(
	select classes_id 
	from student 
	where name='不想毕业');

② 多行子查询：返回多行记录的子查询，使用IN (NOT) 和EXSIST( NOT ) 关键字。

Tips：IN 和 EXSIST的区别
Ⅰ. IN 和 EXSIST的区别在于，IN先查询内层表，然后把外表和内表做hash连接；
Ⅱ. Exists首先执行外层查询对结果做loop循环，再执行内存查询，用外层的找内层的找到返回真循环直到外层。
Ⅲ. 在外表大的时用in效率更快，内表大用exists更快。

例：

-- 使用IN
select * 
from score
where course_id 
IN
 	(select id from course where
	name='语文' or name='英文');

也可以用于多列包含，例：

SELECT
*
FROM
score
WHERE
( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) > 1 );

5）合并查询

在实际应用中，为了合并多个select的执行结果，可以使用 集合操作符 union，union all 。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

union——用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
union all——用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

例子：

select * from course where id<3
union
select * from course where name='英文';

等同于

select * from course where id<3 or name='英文';