MySQL数据库——多表查询
查询语法:
select
列名列表
from
表名列表
where …
准备sql:
#创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) VALUES('研发部'),('市场部'),('财务部');
#创建员工表
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)-- 外键,关联部门表(部门表的主键)
);
insert into emp(name ,gender,salary,join_date,dept_id) values ('张三','男','8200','2013-02-24',1);
insert into emp(name ,gender,salary,join_date,dept_id) values ('李四','男','4600','2010-12-02',2);
insert into emp(name ,gender,salary,join_date,dept_id) values ('王五','男','7000','2008-08-08',2);
insert into emp(name ,gender,salary,join_date,dept_id) values ('小红','女','4000','2015-10-07',3);
insert into emp(name ,gender,salary,join_date,dept_id) values ('小兰','女','4800','2011-03-14',1);
笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况。
要完成多表查询,需要消除无用的数据
多表查询的分类:
1、内连接查询
1、隐式内链接:使用where条件消除无用数据
select * from emp,dept where emp.dept_id =dept.id; #筛选出员工表外键和部门表id一致的所有记录
select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = depr.id; #筛选出员工表外键和部门表id一致的所有记录中员工名、员工性别、部门名
2、显式内连接:
语法:select 字段列表 from 表名1 [innner] join 表名2 on 条件
例如:
select * from emp inner join dept on emp.dept_id = dept.id;
select * from emp join dept on emp.dept_id =dept.id;
2、外连接查询
(1)、左外连接
语法:select 字段名称 from 表1 left [outer] join 表2 on 条件
查询的是左表所有数据以及其交集部分
(2)、右外连接
语法:select 字段名称 from 表1 right [outer] join 表2 on 条件
查询的是右表所有数据以及其交集部分
3、子查询
概念:查询中嵌套查询,称嵌套查询或子查询
例:查询工资最低的员工信息
普通的查询要分两步走:
(1)查询最低工资是多少?select min(salary) from emp;
(2)查询员工信息,工资等于4000。select * from emp where salary =4000;
子查询一条语句就可以完成操作:
select * from emp where salary =(select min(salary) from emp);
子查询的不同情况:
1、子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断,>,>=,<,<=,=
#查询员工工资小于平均工资的人
select * from emp where salary <(select AVG(salary) from emp);
2、子查询的结果是多行单列的:
子查询可以作为条件,使用运算符in来判断
#查询财务部和市场部所有员工的信息
select * from emp where dept_id in(select id from dept where name ='财务部' or name '市场部')
3、子查询的结果是多行多列的:
子查询可以作为一张虚拟表参与查询
#查询入职日期在2011-11-11之前的员工信息
select * from dept,(select * from emp where emp.join_date >2011-11-11) where dept.id=emp.dept.id;