一、多表查询
# 创建部门表
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('孙悟空','男',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. 内连接查询
隐式内连接:使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 查询员工表的名称,性别。部门表的名称 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`; SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`;
显式内连接
显示内连接:使用 INNER JOIN ... ON 语句, 可以省略 INNER SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
select * from emp e inner join dept d on e.`dept_id` = d.`id`;
2.外链接查询
1. 左外连接:
* 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
* 查询的是左表所有数据以及其交集部分。
* 例子:
-- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称 SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
2. 右外连接:
* 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
* 查询的是右表所有数据以及其交集部分。
* 例子:
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
3. 子查询:
* 概念:查询中嵌套查询,称嵌套查询为子查询。
-- 查询工资最高的员工信息 -- 1 查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; -- 2 查询员工信息,并且工资等于9000的 SELECT * FROM emp WHERE emp.`salary` = 9000; -- 一条sql就完成这个操作。子查询 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
查询工资小于平均工资的员工有哪些?
-- 1) 查询平均工资是多少 select avg(salary) from emp; -- 2) 到员工表查询小于平均的员工信息 select * from emp where salary < (select avg(salary) from emp);
查询工资大于5000的员工,来自于哪些部门的名字
-- 先查询大于5000的员工所在的部门i d select dept_id from emp where salary > 5000; -- 再查询在这些部门id中部门的名字 select name from dept where id = (select dept_id from emp where salary > 5000); select name from dept where id in (select dept_id from emp where salary > 5000);
查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id select id from dept where name in('开发部','财务部'); -- 再查询在这些部门id中有哪些员工 select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
查询出2011年以后入职的员工信息,包括部门名称
-- 查询出2011年以后入职的员工信息,包括部门名称 -- 在员工表中查询2011-1-1以后入职的员工 select * from emp where join_date >='2011-1-1'; -- 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
-- 也可以使用表连接: select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';
二、多表查询练习
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部'