29 mysql 表查询–加强
29.1 介绍
在前面我们学习了mysql表的基本查询,但都是对一张表进行的查询,这在我们实际开发中,是远远不够的。下面将使用前面创建的三张表(emp,dept,salgrade)来学习如何进行多表查询。
-- 查询加强
-- ■ 使用where子句
-- ?如何查找1992.1.1后入职的员工
-- 说明: 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- ■ 如何使用like操作符(模糊)
-- %: 表示0到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- ■ 查询表结构
DESC emp
-- 使用order by子句
-- ?如何按照工资的从低到高的顺序[升序],显示雇员的信息
SELECT * FROM emp
ORDER BY sal
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
29.2 分页查询
-- 分页查询
-- 按雇员的id号升序取出, 每页显示3条记录,请分别显示 第1页,第2页,第3页
-- 第1页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
-- 第2页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
-- 第3页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
-- 推导一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数,以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
29.3 使用分组函数和分组子句
-- 增强group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;
-- (2) 显示雇员总数,以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列),如果该列的值为null, 是
-- 不会统计 , SQL 非常灵活,需要我们动脑筋.
SELECT COUNT(*), COUNT(comm)
FROM emp
-- 扩展要求:统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- (4) 显示雇员工资的最大差额。
-- 思路: max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
FROM emp;
SELECT * FROM emp;
select * from dept;
29.4 数据分组的总结
-- 应用案例:请统计各个部门group by 的平均工资 avg,
-- 并且是大于1000的 having,并且按照平均工资从高到低排序, order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
30 mysql 多表查询
30.1 问题的引出(重点,难点)
30.2 说明
多表查询是指基于两个和两个以上的表查询。在实际应用中,查询单个表可能不能满足你的需求,如下面的练习,需要使用到(dept表和emp表)
30.3 多表查询练习
- 如何显示雇员名,雇员工资及所在部门的名字【笛卡尔集】
- 小技巧:多表查询的条件不能少于表的个数 -1,否则会出现笛卡尔集
- 如何显示部门号为10的部门名、员工名和工资 ?
- 如何显示各个员工的姓名,工资,及其工资的级别 ?
-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
分析:
1. 雇员名,雇员工资 来自 emp表
2. 部门的名字 来自 dept表
3. 需求对 emp 和 dept查询 ename,sal,dname,deptno
4. 当我们需要指定显示某个表的列是,需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno
select * from emp;
select * from dept;
select * from salgrade;
-- 小技巧:多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno and emp.deptno = 10
-- ?显示各个员工的姓名,工资,及其工资的级别
-- 思路 姓名,工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写sql , 先写一个简单,然后加入过滤条件...
select ename, sal, grade
from emp , salgrade
where sal between losal and hisal;
30.4 自连接
自连接是指在同一张表的连接查询【同一张表看作两张表】
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 分析: 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
-- 小结:
-- 自连接的特点:
-- 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确,可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;
31 mysql 表子查询
31.1 什么是子查询
子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询。
31.2 单行子查询
单行子查询是指只返回一行数据的子查询语句。
31.3 请思考:如何显示与 SMITH 同一部门的所有员工?
-- 子查询的演示
-- 请思考:如何显示与SMITH同一部门的所有员工?
/*
1. 先查询到 SMITH的部门号得到
2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
-- 下面的答案.
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)
31.4 多行子查询
多行子查询指返回多行数据的子查询 使用关键字 in
-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员.
/*
1. 查询到10号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
select distinct job
from emp
where deptno = 10;
-- 下面语句完整
select ename, job, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno <> 10
31.5 子查询当做临时表使用
查询ecshop表中各个类别中,价格最高的商品
-- 查询ecshop中各个类别中,价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中,价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询
select cat_id , max(shop_price)
from ecs_goods
group by cat_id
-- 这个最后答案
select goods_id, ecs_goods.cat_id, goods_name, shop_price
from (
SELECT cat_id , MAX(shop_price) as max_price
FROM ecs_goods
GROUP BY cat_id
) temp , ecs_goods
where temp.cat_id = ecs_goods.cat_id
and temp.max_price = ecs_goods.shop_price
31.6 在多行子查询中使用 all 操作符
-- all 的使用
-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
-- 可以这样写
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)
31.7 在多行子查询中使用 any 操作符
-- any 的使用
-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > any(
SELECT sal
FROM emp
WHERE deptno = 30
)
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT min(sal)
FROM emp
WHERE deptno = 30
)
31.8 多列子查询
-- 多列子查询
-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1, 字段2 ...) = (select 字段 1,字段2 from ......)
-- 分析: 1. 得到smith的部门和岗位
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用,并且使用多列子查询的语法进行匹配
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'
-- 请查询 和宋江数学,英语,语文
-- 成绩 完全相同的学生
SELECT *
FROM student
WHERE (math, english, chinese) = (
SELECT math, english, chinese
FROM student
WHERE `name` = '宋江'
)
SELECT * FROM student;
--
31.9 在 from 子句中使用子查询
- 查找每个部门工资高于本部门平均工资的人的资料,这里要用到数据库查询的小技巧,把一个子查询当作一个临时表使用。
-- 子查询练习
-- 请思考:查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧,把一个子查询当作一个临时表使用
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp, (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp
where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
- 查找每个部门工资最高的人的详细资料
-- 查找每个部门工资最高的人的详细资料
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, max(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
31.10 在 from 子句中使用子查询
查询每个部门的信息(包括:部门名,编号,地址)和人员数量。
思路分析:
- 先将人员信息和部门信息关联显示
- 然后统计
-- 查询每个部门的信息(包括:部门名,编号,地址)和人员数量
-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -> 构建一个临时表
select count(*), deptno
from emp
group by deptno;
select dname, dept.deptno, loc , tmp.per_num as '人数'
from dept, (
SELECT COUNT(*) as per_num, deptno
FROM emp
GROUP BY deptno
) tmp
where tmp.deptno = dept.deptno
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中,当多个表的列不重复时,才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
32 表复制
32.1 自我复制数据(蠕虫复制)
有时,为了对某个sql语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据。
-- 表的复制
-- 为了对某个sql语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据
CREATE TABLE my_tab01
( id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
32.2 如何删除掉一张表重复记录
-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列),复制到my_tab02
desc my_tab02;
insert into my_tab02
select * from emp;
select * from my_tab02;
-- 3. 考虑去重 my_tab02的记录
/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
create table my_tmp like my_tab02
-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
select * from my_tmp;
-- (5) drop 掉 临时表my_tmp
drop table my_tmp;
select * from my_tab02;
33 合并查询
33.1 介绍
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查询结果合并,不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查询结果合并,会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
34 mysql 表外连接
34.1 提出一个问题
- 前面我们学习的查询,是利用 where 子句对两张表或者多张表,形成的笛卡尔集进行筛选,根据关联条件,显示所有匹配的记录,匹配不上的,不显示。
- 比如:列出部门名称和这些部门的员工名称和工作,同时要求显示出那些没有员工的部门。
- 使用我们学过的多表查询的SQL,看看效果如何 ?
34.2 外连接
-- 外连接
-- 比如:列出部门名称和这些部门的员工名称和工作,
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的SQL, 看看效果如何?
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp;
-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu (
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu;
-- 创建 exam
/*
id grade
1 56
2 76
11 8
*/
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;
-- 使用左连接
-- (显示所有人的成绩,如果没有成绩,也要显示该人的姓名和id号,成绩显示为空)
SELECT `name`, stu.id, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外连接(显示所有成绩,如果没有名字匹配,显示空)
-- 即:右边的表(exam) 和左表没有匹配的记录,也会把右表的记录显示出来
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
34.3 练习
-- 列出部门名称和这些部门的员工信息(名字和工作),
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
--