准备数据库
# 创建部门表 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,'2021-02-01',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('啊二','男',3600,'2021-01-02',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('张三','男',9000,'2019-03-01',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('李四','女',5000,'2016-01-01',3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('王五','女',4500,'2011-03-14',1);
dept(部门)表
emp(员工)表
隐式内联查询:条件是左边和右表的交集部分
语法
SELECT 字段名 FROM 左表, 右表 WHERE 条件
示例:
-- 查询所有员工信息和对应的部门信息 SELECT * FROM emp e,dept d WHERE e.`dept_id`=d.`id`;
-- 查询员工表的名称,性别。部门表的名称 SELECT e.`NAME`,e.`gender`,d.`NAME` FROM emp e,dept d WHERE e.`dept_id`=d.`id`;
-- 查询张三的信息,显示员工 id,姓名,性别,工资和所在的部门名 SELECT e.`dept_id`,e.`NAME`,e.`gender`, e.`salary`,d.`NAME`FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND e.`NAME`='张三';
语法
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
演示
-- 查询员工表的名称,性别。部门表的名称 SELECT e.`NAME`,e.`gender`,d.`NAME` FROM emp e JOIN dept d ON e.`dept_id`=d.`id`;
-- 查询张三的信息,显示员工 id,姓名,性别,工资和所在的部门名 SELECT e.`dept_id`,e.`NAME`,e.`gender`, e.`salary`,d.`NAME` FROM emp e JOIN dept d ON e.`NAME`='张三' WHERE e.`dept_id`=d.`id`;
注意:左右外链接查看的是左表的全部信息或右表的全部信息,与两表之间出现再左边或者右边的顺序无关。例如
要emp表再左则,dept表在右侧。 Select emp,dept
要dept表在左侧,emp表再右则,Select dept,emp
左外连接:用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL ,可以理解在内连接的基础上保证左表的数据全部显示
语法(OUTER可以省略)
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
演示
-- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称 SELECT e.*,d.`NAME`FROM emp e LEFT JOIN dept d ON e.`dept_id`=d.`id`;
右外链接: 用右边表的记录去匹配左边表的记录,如果符合条件的则显示;否则,显示 NULL ,可以理解在内连接的基础上保证左表的数据全部显示
语法(OUTER可以省略)
SELECT 字段名 FROM 左表 RIGHT [OUTER ] JOIN 右表 ON 条件
演示
-- 查看所有部门信息和部门中员工的名字 SELECT d.*,e.`NAME` FROM emp e RIGHT JOIN dept d ON e.`dept_id`=d.`id`;
查询中嵌套查询,称嵌套查询为子查询
示例
-- 查询工资最高的员工信息 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
也就是查询结果是一个值得时候,在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、 = 等
语法:
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
示例:
-- 查询员工得最低工资 SELECT MAX(salary) FROM emp # 查询工资最高的员工信息 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
-- 查询平均工资 SELECT AVG(salary) FROM emp; #查询工资小于平均工资的员工有哪些? SELECT * FROM emp WHERE emp.`salary`<( SELECT AVG(salary) FROM emp);
子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符
语法
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
注意:不能使用=。使用=会报错,因为=一般只是一个值
示例
-- 先查询大于6500的员工所在的部门id SELECT dept_id FROM emp WHERE salary > 6500; -- 再查询在这些部门id中部门的名字 Subquery returns more than 1 row SELECT dept.`NAME` FROM dept WHERE id = (SELECT dept_id FROM emp WHERE salary > 6500); SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 6500);
注意:可见,使用=后市报错的,因为使用=只能是一个值,查询员工工资大于6500结果可能有多种,不能使用=
子查询结果只要是多列,肯定在 FROM 后面作为子句
语法
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件
演示
-- 查询出2021年以后入职的员工信息,包括部门名称 SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date >='2021-1-1') e WHERE d.`id`= e.dept_id ;
理解:
还可以直接使用隐式内连接
SELECT * FROM dept d,emp e WHERE d.`id`=e.`dept_id` AND e.`join_date`>'2021-1-1';
也可以直接使用显示内连接
select * from emp join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';