多表理解加基操
多表分类和理解
why: 项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系
多表关系分类
一对一关系
案例：用户与用户详细的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率 (为避免一张表记录过长的情况，字段太多了就可以进行单表拆分，此时拆分的两个表就是一对一的关系)
实现：在任意一方加入一个外键，关联另一方的主键，并且设置外键为唯一约束
一对多关系 (一个班级多个学生, 一个学生对应一个班级)
案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，关联另一方的主键
why ? 我们将部门信息放在员工表中，合并为一个表是不可行吗? 为啥要分开来使用，这样产生的一对多的关系也使得查询等等操作更加复杂.
不是合在一起不行，而是要分析分开的好处, 一旦分开之后我们会减少很多重复的存储部门信息， 因为存在多个员工对应同一个部门的情形，虽然是多个员工，但是对应的是同样的部门信息，记录， 我们没有必要 重复存储这些部门信息呀, 所以才分表 (理解为什么分表)
- 多对多关系
案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
why 使用中间表，还是降低存储压力，减去不必要的存储，我们可以仅仅使用两列来关联左右两张表， 这样可以避免重复的数据存储 ( 保证数据唯一一份 )
全部从避免数据冗余，重复存储相同数据的角度去考虑外键, 中间表 分表的原因
# 准备班级表 create table if not exists class ( `id` tinyint, `name` varchar(10) ) engine = innodb default charset = utf8mb4; insert into class values(1, '重点班'); insert into class values(2, '重点班'); insert into class values(3, '平行班'); insert into class values(4, '平行班'); # 准备学生表 create table if not exists student ( student_id tinyint primary key, name varchar(10), age tinyint, class_id tinyint ) engine = innodb default charset = utf8mb4; insert into student values(10, '张三', 19, 3); insert into student values(11, '李四', 20, 2); insert into student values(12, '王五', 22, 1); insert into student values(13, '赵四', 27, 4); insert into student values(14, '麻子', null, 3);
笛卡尔积 (A * B)
笛卡尔集合运算 A {1, 2, 3} B {4, 5, 6} A✖B {(1, 4), (1, 5), (1, 6), (2, 4), (2, 5), (2, 6), (3, 4), (3, 5), (3, 6)};
select * from A, B;
select * from A inner join B;
求取 class 表 和 student表的笛卡尔积喔
select * from class, student; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 1 | 重点班 | 10 | 张三 | 19 | 3 | | 2 | 重点班 | 10 | 张三 | 19 | 3 | | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 4 | 平行班 | 10 | 张三 | 19 | 3 | | 1 | 重点班 | 11 | 李四 | 20 | 2 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 3 | 平行班 | 11 | 李四 | 20 | 2 | | 4 | 平行班 | 11 | 李四 | 20 | 2 | | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 2 | 重点班 | 12 | 王五 | 22 | 1 | | 3 | 平行班 | 12 | 王五 | 22 | 1 | | 4 | 平行班 | 12 | 王五 | 22 | 1 | | 1 | 重点班 | 13 | 赵四 | 27 | 4 | | 2 | 重点班 | 13 | 赵四 | 27 | 4 | | 3 | 平行班 | 13 | 赵四 | 27 | 4 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | 1 | 重点班 | 14 | 麻子 | NULL | 3 | | 2 | 重点班 | 14 | 麻子 | NULL | 3 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | | 4 | 平行班 | 14 | 麻子 | NULL | 3 | +------+-----------+------------+--------+------+----------+ 20 rows in set (0.00 sec)
- 上述存在问题，存在很多错误的记录数据 合并显示 ， 这些就是 id 和 class_id 不相等的部分， class的 id 和 student的 class_id 无法对应起来的记录
- 条件约束，消除无效记录
select * from class c, student s where c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | +------+-----------+------------+--------+------+----------+
- inner join on
只取两张表有对应关系的记录
- select * from A, B where 条件限制(两表关系部分)
select * from class c, student s where c.id = s.class_id;
- select * from A inner join B on 条件限制(两表关系部分)
select * from class c inner join student s on c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | +------+-----------+------------+--------+------+----------+ 5 rows in set (0.00 sec)
- left outer join on
在内连接的基础上保留左表没有对应关系的记录
- select * from A left outer join B on 条件限制(两表关系部分)
- 为了方便测试: 我在左表中增添没有对应关系的两条记录：
- 记录1： 5 平行班
- 记录2: 6 平民班
insert into class values(5, '平行班'); insert into class values(6, '平民班');
select * from class c left outer join student s on c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | | 5 | 平行班 | NULL | NULL | NULL | NULL | | 6 | 平民班 | NULL | NULL | NULL | NULL | +------+-----------+------------+--------+------+----------+ 7 rows in set (0.00 sec) select * from class c inner join student s on c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | +------+-----------+------------+--------+------+----------+ 5 rows in set (0.00 sec) # 对比一下发现, 左外连接就是整个左表记录完全显示 # 就算没有关联记录也显示
- right outer join on
在内连接的基础上保留右表没有对应关系的记录
select * from A right outer join B on 条件限制(两表关系部分)
为了方便测试: 我在左表中增添没有对应关系的两条记录：
记录1：15 王顺 25 null; 没有对应class_id
记录2: 16 虹猫 30 null; 没有对应class_id
insert into student values(15, '赵顺', 25, null); insert into student values(16, '虹猫', 30, null);
select * from class c right outer join student s on c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | NULL | NULL | 15 | 赵顺 | 25 | NULL | | NULL | NULL | 16 | 虹猫 | 30 | NULL | +------+-----------+------------+--------+------+----------+ 7 rows in set (0.00 sec) select * from class c inner join student s on c.id = s.class_id; +------+-----------+------------+--------+------+----------+ | id | name | student_id | name | age | class_id | +------+-----------+------------+--------+------+----------+ | 3 | 平行班 | 10 | 张三 | 19 | 3 | | 2 | 重点班 | 11 | 李四 | 20 | 2 | | 1 | 重点班 | 12 | 王五 | 22 | 1 | | 4 | 平行班 | 13 | 赵四 | 27 | 4 | | 3 | 平行班 | 14 | 麻子 | NULL | 3 | +------+-----------+------------+--------+------+----------+ 5 rows in set (0.00 sec) # 对比之下right outer join 就是相关记录 # + 右表多余不相关记录全部显示右表
- 子查询 (嵌套查询,合并查询)
⼀条select语句结果作为另⼀条select语法⼀部分（查询条件，查询结果，表等）。
select ....查询字段 ... from ... 表.. where ... 查询条件
eg : 查询 class表中 张三所在班级的总人数.
分步1： 查询张三所在班级
分布2: 根据第一步获得的班级查询这个班级的总人数
# 第一步获取张三所在班级 select class_id from student where name = '张三'; +----------+ | class_id | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) # 第二步获取张三班级的总人数 select c.id '班级', count(*) '学生人数' from class c, student s where c.id = s.class_id && c.id = 3 group by c.id; +--------+--------------+ | 班级 | 学生人数 | +--------+--------------+ | 3 | 2 | +--------+--------------+ 1 row in set (0.00 sec)
- 合并上述查询方式:
- 合并方式1： 将 select 查询班级的结果作为条件
- select ....查询字段 ... from ... 表.. where 条件1 && c.id = (select ...) group by...
select c.id '班级', count(*) '学生人数' from class c, student s where c.id = s.class_id && c.id = (select class_id from student s where s.name = '张三') # select 查询结果当作条件进行子查询, 嵌套查询 group by c.id;
- 合并方式2：将select 查询班级的结果作为一个新的表进行内连接组合查询
- select 查询字段 from 表1 (select ...)表2 where ...
- 从一次select 之后的结果当作表进行内联查询
select c.id '班级', count(*) '学生人数' from class c, (select * from student stu where stu.name = '张三') s # 对于select name = 张三的查询结果别名s 当作新表联合查询 where c.id = s.class_id group by c.id; # 上述是一种子查询的方式，但是并不能获取张三班级人数 # 仅作为一种查询方式而已
总之，将来我们进入公司需要操作的表结构绝对是多表操作，多表查询的时候，多表需要联合查询
内连接 inner join left outer join right outer join 都是不同的连接多表的方式
子查询， 我们需要 思考的是 一条select查询的结果可以做另外一条select 查询的条件或者是 子表 (select ...) 作为一个where 条件 或者是一个新的 table 在做一次查询
算是嵌套select 查询
总结
小杰从最简单的SQL语法和表操作入手，从 DDL(表定义，表结构操作) DQL (select表查询) DML (记录的增删改查)着手，总结了近期所学，也算是sql入门的一个精简资料了...熟练的掌握理解了上述这些东西，就大致可以算是会操作数据表了
多表查询 将两个表以不同的方式各种形式连接起来联合查询，我们需要不断地积累，根据不同地实际业务场景采取不同地多表查询连接方式
灵活地嵌套使用 select 语句， 一条select 查询结果可以做条件 还可以做 表进行内连接查询.
应老规矩：简要地总结上述关键字操作
DDL回忆
use 数据库; 指定数据库操作
show databases; 显示所有数据库 show tables; 显示所有数据表
create table 表名; create database 库名; 新建表 + 库 不安全 加上 if not exists 修饰可避免报错
drop table 表名; drop database 库名; 删除表 + 库 不安全 加上 if exists 修饰可避免报错
alter table 表名 add/modify/change 列调整; 可以修改表结构
alter table 表名 drop 列名; 删除列
DML回忆
insert into 表名(指定字段) values(指定值); 增加记录
delete from 表名 where 指定删除记录; 删除记录
update 表名 set 字段 = 值 where 指定跟新记录; 跟新记录
DQL回忆
select 指定列 from 表; (指定列查询)
select distinct 列 from 表; 去重查询列
select * from 表 order by 指定排序列 排序规则
select 指定分组字段 聚合函数 from 表 where ... group by 分组字段
聚合函数是为了在分组去重之后压缩多条记录为一条.
select * from 表 limit 起始记录数, 每页记录数; 分页查询
inner join 内连接 left outer join right outer join 外连接, 关系部分 + 其中一表独有部分
select 查询结果可做条件 + 可以新表再 select 查询 (子查询)
