MySQL数据篇之多表操作-----保姆级教程

2021-11-15 15:19:51 浏览数 (2)

多表操作

  • 外键约束
    • 添加外键
      • 语法
    • 注意
  • 表与表之间的关系
    • 子查询的缺陷
    • 一对多的关系
    • 一对多的建表原则
    • 多对多的关系
    • 多对多的建表原则
    • 一对一的例子
    • 一对一的建表原则
  • 多表案例分析
  • 多表查询
    • 多表查询分类
      • 1.连接查询
        • 交叉连接: cross join
        • 这里交叉连接，就是表1的每一行都会去匹配表2的所有行，如果不加限制条件去匹配，求出来的结果就是笛卡尔积
        • 内连接：inner join（inner）可以省略
        • 外连接---outer join(outer可以省略)
      • 2.子查询
    • 多表查询之数据准备
    • 多表查询之交叉连接
      • 使用 cross join 关键字
      • 不使用cross join关键字
    • 多表查询之内连接--返回两张表都满足条件的部分记录
    • 多表查询之外连接
      • 左外连接
        • 注意
    • 右外连接
      • 注意
    • 小总结
    • 可以在update语句中使用inner join和left join
    • 在delet语句中使用join语句---替代级联删除
    • join语句中使用USING代替on---键值同名
  • union联合查询
    • union all可以查询出所有，不进行去重操作
    • 内连接与外连接的区别

外键约束

作用：保证多表之间的数据完整性

测试环境准备:

代码语言：javascript复制
create table dept(
  did int primary key auto_increment,
  dname varchar(20)
);
insert into dept values(null,'市场部');
insert into dept values(null,'人事部');
insert into dept values(null,'教研部');

create table employee(
       eid int primary key auto_increment,
        ename varchar(20),
       salary double,
         birthday date,
          sex varchar(10),
              dno int
);
insert into employee values(null,'张三',8000,'1980-09-01','男',3);
insert into employee values(null,'李四',9000,'1980-09-01','男',1);
insert into employee values(null,'王五',6000,'1980-09-01','男',2);
insert into employee values(null,'赵六',10000,'1980-09-01','男',3);
insert into employee values(null,'孙七',10000,'1980-09-01','男',1);

向员工表中插入一条记录，没有部门:

代码语言：javascript复制
insert into employee values(null,'王五',6000,'1980-09-01','男',null);

删除一个人事部门:

代码语言：javascript复制
delete from dept where did=2;

向刚才做的这两个操作(插入一个没有部门的员工和删除一个带有员工的部门)，这种情况都是不应该发生的。

这个时候就需要在多表之间添加外键约束

添加外键

语法

代码语言：javascript复制
在新表中添加外键约束语法： constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)

在已有表中添加外键约束：alter table 从表表名 add constraints 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)

删除外键语法： alter table 从表表名 drop foreign key 外键名称;

在员工表上添加外键

代码语言：javascript复制
alter table employee add foreign key(dno) references dept(did);
//让员工表中员工的部门类型不为空，即设置外键为非空
alter table employee modify dno int not null;

注意

添加外键约束后，如果想要删除主键即某个部门，需要先将该部门下关联的员工记录删除，否则报错。

但是我们可以直接删除掉某个部门下的员工，即外键的删除不影响主键。

而主键的删除会影响外键。

表与表之间的关系

子查询的缺陷

  • 麻烦
  • mysql的子查询会单独创建一张临时表存放查询的结果集，等到整体查询完成之后会自动删除这个临时表

一对多的关系

一个部门下可以有多个员工，但是一个员工只能属于一个部门

一对多的建表原则

在多的一方创建外键指向一的一方的主键

多对多的关系

一个学生可以选择多门课程，一个课程可以被多个学生选择、

多对多的建表原则

需要创建中间表，中间表中至少有两个字段，分别作为外键指向多对多双方的主键

一对一的例子

一个公司只能有一个注册地址，一个注册地址也只能对应一个公司

一对一的建表原则

多表案例分析

建表:

多表查询

多表查询分类

1.连接查询

交叉连接: cross join
  • 交叉连接：查询到的是两个表的笛卡尔积
  • 语法:
代码语言：javascript复制
select* from  表1 cross join 表2;

或者

代码语言：javascript复制
select * from 表1，表2;
这里交叉连接，就是表1的每一行都会去匹配表2的所有行，如果不加限制条件去匹配，求出来的结果就是笛卡尔积
内连接：inner join（inner）可以省略
  • 显示的内连接：在sql中显示调用inner join关键字
  • 语法:
代码语言：javascript复制
select *from 表1 inner join 表2 on 关联条件;
  • 隐式内连接 : 在sql中没有调用inner join 关键字
  • 语法:
代码语言：javascript复制
select *from 表1,表2 where 关联条件;
外连接—outer join(outer可以省略)
  • 左外连接:
  • 语法:
代码语言：javascript复制
select * from 表1 left outer join 表2 on 关联条件;
  • 右外连接
  • 语法:
代码语言：javascript复制
select * from 表1 right outer join 表2 on 关联条件;

2.子查询

一个查询语句需要依赖另一个查询语句的结果

多表查询之数据准备

  1. 班级表数据准备
  1. 学生表数据准备
  1. 课程表数据准备
  1. 学生选课表数据准备

多表查询之交叉连接

使用 cross join 关键字

代码语言：javascript复制
SELECT * FROM classes CROSS JOIN stu;

不使用cross join关键字

代码语言：javascript复制
SELECT* FROM classes,stu;

效果一样:

多表查询之内连接–返回两张表都满足条件的部分记录

1.显示内连接

代码语言：javascript复制
SELECT * FROM classes c INNER JOIN stu s ON c.cid=s.cno;
可以省略INNER
SELECT * FROM classes c JOIN stu s ON c.cid=s.cno;

2.隐式内连接

代码语言：javascript复制
SELECT *FROM classes c,stu s WHERE c.cid=s.cno;

多表查询之外连接

左外连接

返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

代码语言：javascript复制
SELECT *FROM classes c LEFT OUTER JOIN classesstu s ON c.cid=s.cno;
可以省略OUTER
SELECT *FROM classes c LEFT JOIN classesstu s ON c.cid=s.cno;
注意
  • 根据on后面的条件，右表匹配左表的数据，有匹配上的，就在左表的对应行后面加上右表的数据，没有匹配上的时候，也要在左表的对应行后面加上右表的数据，但是数据都为null
  • 这样对应左表的数据全部通过on条件匹配完成之后，就会生成一张临时表，这个临时表中包含的字段就是select的那些字段，值来源于左表和右表
  • 如果LEFT JOIN查询有where条件部分，根据where条件对整个的临时表做筛选，得到对应的结果集，如果没有where条件部分，直接把临时表作为查询结果集返回
  • 构成临时表的时候，左表的一行数据以及其对应的右表的一行数据共同共同构成临时表的一行数据，on的条件永远筛选的是临时表的一行数据中的右表部分的数据，左表部分的数据一定是展示的
  • 如果是为了筛选右表的数据，来指定什么样的右表数据与左表匹配，那就使用on条件
  • 如果是为了对整个临时表做筛选，那就使用where条件
  • 如果是为了筛选左表的数据，也要使用where条件，因为左表的数据必定存在于临时表中，所有筛选左表的数据和筛选整个临时表一样

右外连接

恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

代码语言：javascript复制
SELECT *FROM classes c RIGHT OUTER JOIN stu s ON c.cid=s.cno;
可以省略OUTER
SELECT *FROM classes c RIGHT JOIN stu s ON c.cid=s.cno;

注意

  • RIGHT JOIN与LEFT JOIN相反，右表要全部展示，左表展示哪些数据根据on的条件来匹配
  • 能匹配上当前这行的右表数据，就展示这行的左表数据
  • 不能匹配上当前这行的右表数据，这行的左表数据就全是null

RIGHT JOIN和where使用，与LEFT JOIN基本相同

1.根据on的条件构建临时表，只是这时候临时表的一行数据中，右表的数据必须存在，左表的数据按照on的条件与当前行的右表数据匹配，匹配上就展示左表数据，匹配不上左表数据就是null。所有的右表数据匹配完之后，就返回临时表。

2.根据where条件来筛选整个临时表，这个阶段就是和LEFT JOIN的where一样了

另外，与LFET JOIN的where相似的，如果是要筛选右表，那么也要使用where

小总结

LEFT JOIN中

  • 如果是为了筛选整个右表数据，来指定什么样的右表数据与左表匹配，那就使用on条件
  • 如果是为了对整个临时表做筛选，那就使用where条件
  • 如果是为了筛选左表的数据，也要使用where条件(因为左表数据必定存在于临时表中，所有筛选左表和筛选整个临时表一样)

RIGHT JOIN中

  • 如果是为了筛选左表数据，来指定什么样的左表数据与右表匹配，那就使用on条件
  • 如果是为了对整个临时表做筛选，那就使用where条件
  • 如果是为了筛选右表的数据，也要使用where条件(因为右表数据必定存在于临时表中，所以筛选右表和筛选整个临时表一样)

可以在update语句中使用inner join和left join

使用inner join

代码语言：javascript复制
UPDATE course c INNER JOIN coursetype ct ON c.type_id=ct.id SET flag =1 WHERE ct.id=2;

使用left join

代码语言：javascript复制
UPDATE course c LEFT JOIN coursetype ct ON c.type_id=ct.id SET flag =0 WHERE ct.id=2;

在delet语句中使用join语句—替代级联删除

多表删除:

代码语言：javascript复制
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
代码语言：javascript复制
delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

同时会删除两张表中满足条件的数据:

代码语言：javascript复制
DELETE e,d FROM emp e INNER JOIN depart d ON  e.d_id=d.id WHERE e.d_id=2

不论是delete join还是delete left join，都能实现同时删除多个表的数据，这个就可以代替外键关联里面的级联删除

join语句中使用USING代替on—键值同名

代码语言：javascript复制
SELECT * FROM emp e LEFT JOIN depart d ON e.id=d.id ;
等价于
SELECT * FROM emp e LEFT JOIN depart USING(id);

使用USING代替join中的on,只有在两个判断条件键值同名时才可以使用，在查询时不会产生多余的字段

如果join查询中，on的条件是多个and拼接的键值同名的判断

代码语言：javascript复制
SELECT * FROM emp e LEFT JOIN depart d ON e.id=d.id AND e.name=d.name;
等价于
SELECT * FROM emp e LEFT JOIN depart USING(id,`name`);

union联合查询

基本格式:

代码语言：javascript复制
SELECT id FROM emp 
UNION 
SELECT id FROM depart
  • 合并的多个结果集必须保证字段个数一致
  • union关键字默认去重,union对重复记录的认定，是一行数据的重复，如果一行数据有多个字段，那么就得是所有字段都重复
  • 查询的结果集显示的字段名是第一个结果集的字段名

union all可以查询出所有，不进行去重操作

代码语言：javascript复制
SELECT id FROM emp 
UNION  ALL
SELECT id FROM depart

内连接与外连接的区别

sql join null union using

0 人点赞