本文详细介绍了SQL中的多表关系、多表查询、连接查询（内连接、外连接、自连接）、联合查询（union,unionall）以及子查询的多种类型和用法，包括笛卡尔积和实际案例，旨在帮助读者理解和掌握数据库查询的基本技巧。
目录
一、多表关系
（1）表结构之间的联系，一般分为三种
- 一对多（多对一）
- 多对多
- 一对一
（2）一对多
（3）多对多
通过中间表来维护两张表之间的关系
案例：可视化界面展示多对多之间的关系
（4）一对一（可以合并成一个表）
二、多表查询概述
（1）概述
多表查询：指从多张表中查询数据。
（2）扩展：笛卡尔积
概念：
练习：
# --笛卡尔积
select * from emp2,dept2;
# 输出的结果一共30条,需要emp2中的每一条数据都和dept2中的数据组合,所以结果有6*5=30种,这种结果称作两表的"笛卡尔积"（3）语法
select * from 表1,表2 where 两表之间的联系;
代码展示：
# --多表查询
select * from emp2,dept2 where emp2.dept_id=dept2.id;
# 加上限制条件,即可从笛卡尔积中挑选出来有用的数据（4）多表查询分类
三、连接查询——内连接
（1）概念和语法
内连接查询：查询两张表之间的交集部分
（2）代码演示
# ---------------内连接查询-------------------
# --1.查询每个员工的姓名,及关联的部门的名称(隐式内连接实现)
# 表结构:emp,dept
# 连接条件:dept_id=dept.id
select emp2.name,dept2.name from emp2,dept2 where emp2.dept_id=dept2.id;
select e.name,d.name from emp2 e,dept2 d where e.dept_id = d.id;
# emp2 e即给emp2表起了个别名e,此时就不能再通过表名来限制字段了
# --2.查询每个员工的姓名,及关联的部门的名称(显示内连接实现) ---- inner join ... on...
# 表结构:emp,dept
# 连接条件:dept_id=dept.id
select e.name,d.name from emp2 e inner join dept2 d on e.dept_id=d.id;
# inner可以省略四、外连接
（1）语法
外连接分为：左外连接和右外连接（左右外连接只有关键字不同，其他的都一样）
- 左外连接（对应的关键字left）
相当于查询表1（左表）的所有数据 包含 表1和表2 交集部分的数据。
- 右外连接（对应的关键字right）
相当于查询表2（左表）的所有数据 包含 表1和表2 交集部分的数据。
（2）代码演示
# ----------------------------外连接---------------------------------
# 左右外连接可以相互转化
# --左外连接:查询emp2表的所有数据和对应的部门信息
select emp2.*,dept2.name from emp2 left outer join dept2 on emp2.dept_id=dept2.id;
# --右外连接:查询dept2表的所有数据和对应员工的信息
select emp2.*,dept2.name from emp2 right outer join dept2 on emp2.dept_id=dept2.id;五、自连接
（1）语法结构
自连接：一张表自己连接自己。
（2）代码展示
# ----------------------------自连接---------------------------------
# --1.查询员工及其所属的领导的名字
select e.name '员工',m.name '领导' from emp2 e join emp2 m on e.managerid=m.id;
# 此时没有领导的则不会输出
# --2.查询所有员工emp及其领导的名字emp,如果员工没有领导,也需要查询出来
select e.name '员工',m.name '领导' from emp2 e left join emp2 m on e.managerid=m.id;
# 右外连接,emp2表中的name全部展示,没有领导的展示为null六、联合查询——nuion ， union all
（1）定义
（2）代码展示
# ----------------------------联合连接---------------------------------
# --union , union all
# 1.将薪资低于10000的员工和年龄大于30岁的员工全部查询出来
select * from emp2 e where e.salary>10000 # 前四个是薪资大于10000的
union all
select * from emp2 m where m.age>30; # 后四个是年龄大于30的注意：对于联合查询的多张表的数列必须保持一致，字段类型也需要保持一致。
七、子查询
（1）概念
注意：子查询外部的语句可以是INSERT/UPDATE/DELEYTE/SELECT的任何一个。
（2）分类
（3）根据子查询结果分类
- 标量子查询
概念：子查询的结果是单个的值（数字、字符串、日期等），最简单的形式。
常用的操作符：= > < >= <=
代码演示：
# ----------------------------子查询--------------------------------- # --标量子查询 # 1.查询“销售部”的所有员工信息 select * from emp2 where emp2.dept_id=(select id from dept where name='销售部'); # select id from dept where name='销售部'------这部分查询叫做子查询 # 先进性子查询得出销售部的id为4，再让emp2表中的员工作书部门ID即dept_id和销售部的相等即可。 # 2.查询在韦一笑入职之后入职的员工信息 select emp2.name,emp2.entrydate from emp2 where emp2.entrydate>(select entrydate from emp2 where emp2.name='韦一笑'); # 子查询先查询并返回韦一笑的入职时间，然后在emp2表中查询入职时间在韦一笑之后的人
- 列子查询
概念：子查询返回结果是一列（可以是多行）
常用操作符：in ，not in，any，some，all
代码演示：
# --列子查询 # 1.查询销售部和市场部的所有员工 select * from emp2 where emp2.dept_id in (select id from dept2 where dept2.name='销售部' or dept2.name='市场部'); # 2.查询比销售部所有人工资都高的员工信息 select * from emp2 where emp2.salary > all (select emp2.salary from emp2 where emp2.dept_id=(select id from dept2 where dept2.name='销售部')); # 3.查询比销售部其中任意一人工资都高的员工信息 select * from emp2 where emp2.salary > any (select emp2.salary from emp2 where emp2.dept_id=(select id from dept2 where dept2.name='销售部')); # any是满足其一即可,all是所有都要满足
- 行子查询
概念：子查询结果是一行（可以是多列）
常用的操作符：=，<，>，in，not in
代码演示：
# --行子查询 # 1.查询与张无忌的薪资及直属领导都相同的员工信息 select emp2.name,emp2.salary,emp2.managerid from emp2 where (emp2.salary,emp2.managerid) in (select emp2.salary,emp2.managerid from emp2 where emp2.name='张无忌');
- 表子查询
概念：子查询结果返回的是多行多列
常用操作符：in
代码演示：
# --表子查询 # 1.查询和张无忌和韦一笑的职位和薪资相同的员工信息 select * from emp2 where (emp2.salary,emp2.job) in (select emp2.salary,emp2.job from emp2 where emp2.name='张无忌' or name='韦一笑'); # 2.查询入职日期是2006-01-01之后的员工信息及部门信息 select e.*,d.name from (select emp2.name,emp2.dept_id from emp2 where emp2.entrydate>'2006-01-01') e left join dept2 d on d.id=e.dept_id;
（4）根据子查询位置分类
- WHERE之后
- FROM之后
- SELECT之后
八、案例
# -----------------------------------子查询练习--------------------------------------
# 数据准备
create table salgrade(
grade int,
losal int,
hisal int
)comment '薪资等级表';
insert into salgrade values (1,0,3000),(2,3001,5000),
(3,5001,8000),(4,8001,10000),
(5,10001,15000),(6,15001,20000),
(7,20001,25000),(8,25001,30000);
# 练习
# 1.查询员工姓名,年龄,职位,部门信息(隐式内连接)
select e.name,e.age,e.job,e.dept_id,d.name from emp2 e,dept2 d where(e.dept_id=d.id);
# 2.查询年龄小于30岁的员工的姓名,年龄,职位,部门信息(显示内连接)
select e.name,e.age,e.job,e.dept_id,d.name from emp2 e join dept2 d on e.dept_id=d.id;
# 3.查询拥有员工的部门ID,部门名称
select distinct d.id,d.name from emp2 e join dept2 d on e.dept_id=d.id ;
# 4.查询所有年龄大于40岁的员工,及其所属的部门名称
select e.name,e.age,e.dept_id,d.name from emp2 e join dept2 d on e.age>40 and e.dept_id=d.id;
# 5.查询所有员工的工资等级
select e.*,s.* from emp2 e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;
# 6.查询研发部的所有员工的信息及工资等级
# 研发部的所有员工（研发部的dept_id=1）
select * from emp2 e where e.dept_id='1';
# 所有员工的工资等级
select e.*,s.* from emp2 e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;
# 两者结合
select e.*,s.* from (select * from emp2 e where e.dept_id='1') e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;
# 7.查询研发部员工的平均工资
select avg(e.salary) '研发部员工平均工资' from emp2 e where e.dept_id='1';
# 8.查询工资比韦一笑高的员工信息
select * from emp2 e where e.salary>(select e.salary from emp2 e where e.name='韦一笑');
# 9.查询比平均工资高的员工信息
select * from emp2 e where e.salary > (select avg(emp2.salary) '所有员工平均工资' from emp2);
# 10.查询低于本部门平均工资的员工信息
select * from emp2 e where e.salary < (select avg(emp2.salary) '所有员工平均工资' from emp2);
# 11.查询所有员工的部门信息，并统计部门的员工人数
select d.id,d.name ,(select count(*) from emp2 e where e.dept_id = d.id) '部门人数' from dept2 d;
# 12.查询所有学生的选课情况，展示出学生的姓名，学号，课程名称
# 表：student,course,student_course
select s.name,s.no,c.name from student s,course c,student_course sc where s.id=sc.studentid and sc.courseid=c.id;
2869
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
