文章目录
1 表连接查询
数据准备
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values ('开发部'),('市场部'),('财务部');
select * from dept;
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键)
)
select * from emp;
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);
1.1 什么是多表查询
多表查询的作用:
比如:我们想查询孙悟空的名字和他所在的部门的名字,则需要使用多表查询。
如果一条SQL语句查询多张表,因为查询结果在多张不同的表中。每张表取1列或多列。
1.2 笛卡尔积现象(case 1)
1.2.1 什么是笛卡尔积现象
-- 需求:查询所有的员工和所有的部门
select * from emp,dept;
or
select * from emp cross join dept;
select * from dept cross join emp;
1.2.2 如何清除笛卡尔积现象的影响
我们发现不是所有的数据组合都是有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
-- 设置过滤条件
select * from emp,dept where emp.dept_id = dept.id;
1.3 内连接(case 2)
用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键=主表.主键
1.3.1 隐式内连接
隐式内连接:看不到JOIN关键字,条件使用WHERE指定
SELECT 字段名 FROM 左表, 右表 WHERE 条件
1.3.2 显式内连接
显示内连接:使用INNER JOIN … ON语句, 可以省略INNER
1.确定查询哪些表
select * from emp inner join dept;
select * from dept inner join emp;
2.确定表连接条件,员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.dept_id = d.id;
3.确定查询条件,查询的是唐僧的信息,员工表.name=‘唐僧’
select * from emp2 e inner join dept d on e.dept_id = d.id where e.name='唐僧';
4.确定查询字段,查询唐僧的信息,显示员工id,姓名,性别,工资和所在的部门名称。
可以给表取别名,显示的字段名也使用别名
select e.id 编号,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名字
from emp2 e inner join dept d
on e.dept_id=d.id
where e.name='唐僧';
1.3.3 总结内连接查询步骤
1)确定查询哪些表
2)确定表连接的条件
3)确定查询的条件
4)确定查询的字段
1.4 外连接(case 3)
1.4.1 左外连接
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示NULL
可以理解为:在内连接的基础上保证左表的数据全部显示(左表是部门,右表员工)
-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部');
select * from dept;
-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;
1.4.2 右外连接
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示NULL
可以理解为:在内连接的基础上保证右表的数据全部显示
-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用右外连接查询
select * from dept right join emp on dept.id = emp.dept_id;
2 子查询
2.1 什么是子查询
1)一个查询的结果做为另一个查询的条件
2)有查询的嵌套,内部的查询称为子查询
3)子查询要使用括号
-- 需求:查询开发部中有哪些员工
select * from emp;
select * from dept;
-- 通过两条语句查询
select id from dept where name='开发部' ;
select * from emp where dept_id = 1;
-- 使用子查询
select * from emp where dept_id = (select id from dept where name='开发部');
2.2 子查询的3种情况
2.3 子查询的结果是一个值的时候
子查询结果只要是单行单列,肯定在WHERE后面作为条件,父查询使用:比较运算符,如:> 、<、<>、= 等
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
案例:查询工资最高的员工是谁?
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
2.4 子查询的结果是多行单列的时候
子查询结果是单例多行,结果集类似于一个数组,父查询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
案例:查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门id中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
2.5 子查询的结果是多行多列
2.6 子查询小结
子查询结果只要是单列,则在WHERE后面作为条件
子查询结果只要是多列,则在FROM后面作为表进行二次查询