MySQL表的增删改查(进阶)

1. 数据库的约束

1.1 NULL 约束
创建表时, 可以指定某列不为空

例如:
1.2 UNIQUE 唯一约束

指定某列是唯一的, 不重复的

例如:

1.3 DEFAULT 默认值约束

例如: 指定插入数据时, name 列为空, 默认值为 ‘unknown’

1.4 PRIMARY KEY 主键约束

相当于 NOT NULL 和 UNIQUE 的结合

对于整数类型的主键, 常搭配自增长 auto_increment 使用(插入数据对应字段没有给值的时候, 使用最大值 + 1)

例如:

1.5 外键约束 FOREIGN KEY

外键用于关联其他表的主键
语法实现:

foreign key (字段名) references 主表(列)

例如:
学生表中的 classes_id 与 班级表中的班级 id 关联; 一个学生对应一个班级, 一个班级对应多个学生.


2. 表的设计

具有三大范式:
2.1 一对一
人 <=========> 身份证
在一个表中即可实现

2.2 一对多
班级 <===============> 学生
解决方法:创建两个表, 例如:

2.3 多对多
学生 <================>课程
在处理多对多关系时, 就需要创建中间表, 把多对多问题转换成一对多问题

在这三个表中就实现了多对多的关系: 张三选修了语文 + 数学, 李四选修了英语 + 数学, 王五选修语文 + 数学 + 英语;

3. 新增

插入操作可以把查询结果的数据新增到表中

注意: select 查找出的列数和类型必须和要插入的列的数目和类型相同.

语法实现:

insert into 表名 [(列名)] select ....;

例如:



4. 查询
4.1 聚合查询
4.1.1
count: 返回查询到的数据的数量
sum: 返回查询到的数据的总和, 不是数字没有意义
avg: 返回查询到的数据的平均值,不是数字没有意义
max: 返回查询到的数据的最大值,不是数字没有意义
min: 返回查询到的数据的最小值,不是数字没有意义

例如:


4.1.2
group by
对指定列进行分组查询, 查询时把结果中值相同的记录作为一组

group by 要想搭配一些额外的诗选条件, 不能使用 where , 应该使用 having

语法实现:

select 列名, sum/max...(列名) from 表名 group by 列名;

4.2 联合查询/ 多表查询

实质上, 多表查询就是对多张表的数据取笛卡尔积

笛卡尔积:

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

4.2.1 内连接
语法实现:

select 表名.列名... from 表1 join 表2 on 连接条件 and 其他条件;
select 表名.列名... from 表1, 表2.. where 连接条件 and 其他条件;

下面用一个案例来实现多表查询的相关操作




(1) 查找名为’许仙’的成绩
要查找成绩, 就得去查看 score 表中的 score 列, 但是 score 表中没有学生姓名, 只有学生 id, 所以根据学生 id 把两张表连接起来, 再根据名叫’许仙’限定查找范围


(2) 查找所有同学的总成绩以及每个同学的个人信息

(3) 查询所有同学的成绩以及同学的个人信息(三个表的查询)

4.2.2 外连接
外连接分为左外连接和右外连接

语法实现:

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

案例:
查询所有同学的成绩. 如果该同学没有成绩, 也要显示出来

左外连接
右外连接
4.2.3 自连接

自连接是指在同一张表连接自身进行查询

4.2.4 子查询

子查询是嵌套在其他 sql 语句中的 select 语句, 也叫嵌套查询

(a) 单行子查询
案例:
查询与"不想毕业"同学的同班同学
(这个过程需要先知道"不想毕业"同学对应的 class_id, 然后再根据这个 class_id 去查询同学)

(b) 多行子查询
案例:
查询语文或者英文的成绩信息
先在 course 表中查找到语文和英文对应得课程 id, 再在分数表中拿着 id 对应 course_id 查找对应的 score

方法一:
这种方法是先执行子查询, 然后把子查询得到的结果放在内存中, 在执行主查询, 拿着内存中的数据筛选主表中的记录(适用于子查询结果比较小, 主查询结果比较大的情况)

方法二:
先执行主查询, 得到的结果再和子查询结果进行 join 操作(多表联合查询)(适合子查询数目较多的情况, 尤其是内存中不方便缓存子查询结果时)

4.2.5 合并查询 (UNION, UNION ALL)

合并查询可以合并两个 select 的结果, 在使用时必须注意在查询的结果中, 前后字段必须一致.

(a) UNION

使用 UNION 时候会自动去掉结果中的重复行

案例: 查询 id 小于 3, 或者名字为 ‘英文’ 的课程

也可使用 or 来实现

(b) UNION ALL

使用 UNION ALL 的时候不会去掉结果中的重复数据

案例: 查询 id 小于 3, 或者名字为 “java” 的课程

会发现结果中出现了重复数据 Java, 这也说明了使用 UNION ALL 的时候不会去掉结果中的重复数据