文章目录
MySQL单表表查询 - 增强
在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。
下面我们讲解的过程中，将使用前面创建三张表
(emp,dept,salgrade)
部门表源码（文件源码）
CREATE TABLE dept (/*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES (10,'ACOUNTING ','NEW YORK'),
(20,'RESEARCh' , 'DALAS '),
(30,'SALRS', 'CHICGO '),
(40,'OPERATION', 'BoTo ');
#创建表EMP雇员
CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL,/*编号*/
ename VARCHAR(20) NOT NULL DEFAULT '' ,/*名字*/
job VARCHAR(9) NOT NULL DEFAULT '' ,/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)NOT NULL,/*薪水*/
comm DECIMAL(7,2),/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/) ;
INSERT INTO emp VALUES(7369,'SMITH ','CLERK', 7902,'1990-12-17',800.00, NULL , 20),
(7499,'ALLEN','SALESMAN ', 7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN' ,7698,'1991-2-22',1250.00,500.00,30),
(7566,'TONES','AANACEN', 7098,'1991-2-22 ',1250.00,500.00,30),
(7654,'MARTIN ','SALESMAN ', 7698,'1991-9-28', 1250.00,1400.00,30),
(7698,'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30 ),
(7900,'JAMES', 'CLERK', 7698,'1991-12-3 ',950.00,NULL,30),
(7902,'FORD ','ANALYST', 7566,'1991-12-3', 3000.00,NULL, 20 ),
(7934,'MILLER', 'CLERK ', 7782,'1992-1-23',1300.00,NULL,10);
-- 工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
losal DECIMAL(17,2) NOT NULL, /*该级别最低工资*/
hisal DECIMAL(17,2) NOT NULL /*该级别最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
delete from emp
delete from dept
delete from salgrade
select * from emp
like（案例）
-- 使用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%'
-- 如何显示姓名第三个字符为大写R的所有员工的姓名和工资
select ename,sal from emp where ename like '__R%'
-- 如何显示没有上级的雇员的情况
select * from emp where mgr is null;
-- 查询表结构
desc emp
select * from emp
order by（案例）
-- 如何按照工资的从低到高的顺序，显示雇员的信息
select * from emp order by sal
-- 按照部门号升序,而雇员的工资降序排列，显示雇员信息
select * from emp order by deptno ASC,sal DESC
分页查询（案例）
-- 按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页,第三页
-- 基本语法:select ... limit start, rows表示从start+1行开始取,取出rows行, start 从0开始计算
-- 第一页
select * from emp order by empno
limit 0,3
-- 第二页
select * from emp order by empno
limit 3,3
-- 第三页
select * from emp order by empno
limit 6,3
-- 推导一个公式
select * from emp order by empno
limit 每页显示记录数 * (第几页 - 1)，每页显示的记录数
group by（案例）
-- 使用分组函数和分组子句group by
-- 显示每种岗位的雇员总数
select count(*) ,job from emp group by job
-- 显示每种岗位的雇员总数、平均工资。
select count(*),job,avg(sal) from emp group by job
-- 显示雇员总数，以及获得补助的雇员数。
select count(*),count(comm) from emp -- 注意这里count(comm)如果不为空则统计
-- 显示雇员总数，以及获得没有获得补助的雇员数
select count(*),count(if(comm is null,1,null)) from emp
select count(*),count(*) - count(comm) from emp
-- 显示管理者的总人数。(去重)
select count(distinct mgr) from emp
-- 显示雇员工资的最大差额。
select MAX(sal) - MIN(sal) from emp
select * from emp
综合使用
如果select语句同时包含有group by ,having , limit,order by那么他们的顺序是group by , having , order by，limit)
案例
-- 请统计各个部门的平均工资,并县是大于1000的,并且按照平均工资从高到纸排序，取出前两行记录.
select deptno,avg(sal) as avg_sal from emp
group by deptno
having avg_sal > 1000
order by avg(sal)
DESC limit 0,2
MySQL多表查询（重点，难点）
概念
多表查询是指基于两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求，（如下面的课堂练习），需要使用到（dept表和emp表）
多表查询练习
-- 多表查询
-- ?显示雇员名，雇员工资及所在部门的名字【笛卡尔集】
/*
分析：
1.雇员名，雇员工资 来自 emp表
2.部门的名字 来自dept表
3.需求对emp 和 dept查询
*/
-- 在默认情况下：当两个表查询时，规则
-- 1.从第一张表中，取出一行 和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
-- 2.一共返回的记录数，第一张表行数 * 第二张表的行数
-- 3. 这样多表查询默认返回处理结果，称为【笛卡尔集】
-- 4.解决这个多表的关键就是要写出正确的过滤条件 where
-- 5.当我们需要指定显示某个表的列是，需要 表.列表
select *
from emp,dept
select ename,sal,dname
from emp,dept
where emp.deptno = dept.deptno
select ename,sal,dname,emp.deptno
from emp,dept
where emp.deptno = dept.deptno
select * from emp
select * from dept
注意
多表查询的条件不能少于表的个数 -1，否则会出现笛卡尔集
（比如两个表，过滤条件必须有一个）
多表查询练习2
-- ?如何显示部门号为10的部门名、员工名和工资
select dname,ename,sal,emp.deptno from emp,dept where emp.deptno = 10 and emp.deptno = dept.deptno;
-- ?显示各个员工的姓名，工资,及其工资的级别
select grade,ename,sal from emp,salgrade -- [笛卡尔集]
select grade,ename,sal from emp,salgrade where sal between losal and hisal
order by sal
desc salgrade
desc emp
自连接
概念
自连接是指在同一张表的连接查询【同一张表看做两张表】
案例题目
思考题：显示公司员工名字和他的上级的名字
自连接特点
- 把同一张表当做两张表使用
- 需要给表取别名 | 表明 表别名
- 列名不明确，可以指定列的别名 列名 as 列的别名
MySQL表子查询
什么是子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
案例练习
-- 子查询演示
-- 请思考：如何显示与smith同一部门的所有员工？
/*
1.先查询到 smith的部门号
select deptno from emp where ename = 'smith'
2.把这条语句当做子查询来使用
3.条件：emp.deptno = smith.deptno
*/
select *
from emp
where emp.deptno = (
select deptno
from emp
where ename = 'smith'
)
多行子查询
多行子查询指返回多行数据的子查询，使用关键字in
-- 查询部门30的工作相同的雇员的名字、岗位、工资、部门号，
-- 但是不含30号部门自己的雇员
/*
1.查询到30号部门有哪些工作
select distinct job from emp where deptno = 30
2.把上面查询的结果当做子查询使用
*/
select distinct job
from emp
where deptno = 30
select ename,job,sal,deptno
from emp
where job in(
select distinct job
from emp
where deptno = 30
) and deptno != 30
select * from emp
select * from dept
all、any（操作符）
-- 如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
-- select sal from emp where deptno = 30
-- all
select ename,sal,deptno
from emp
where sal > all(
select sal from emp where deptno = 30
)
-- max
select ename,sal,deptno
from emp
where sal > (
select max(sal) from emp where deptno = 30
)
-- 如何显示工资比部门30的'其中一个'员工的工资高的员工的姓名、工资和部门号
-- any
select ename,sal,deptno
from emp
where sal > any(
select sal from emp where deptno = 30
)
-- min
select ename,sal,deptno
from emp
where sal >(
select min(sal) from emp where deptno = 30
)
子查询临时表
子查询当做临时表使用
案例练习1
没有表，看一下就行了
-- 查询ecshop中各个类别中，价格最高的商品
--
-- 查询商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_i,当做临时表
-- 把子查询当做一个临时表可以解决很多很多复杂的查询
select cat_id,max(shop_price)
from ecs_goods
group by cat_id
select good_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
案例练习2
-- 问题：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧,把一个子查询当作一个临时表使用
-- 1.得到本部门的平均工资
select deptno,avg(sal) as '平均工资'
from emp
group by deptno
-- 2.把上面的结果当作子查询，和 emp进行多表查询
select ename,emp.sal,emp.deptno,temp.avg_sal
from emp,(
select deptno,avg(sal) as avg_sal
from emp
group by deptno
) temp
where emp.sal > temp.avg_sal and emp.deptno = temp.deptno
案例练习3
-- 问题：得到每个部门工资最高的人的详细资料
-- 1.得到本部门的最高工资
select deptno ,max(sal)
from emp
group by deptno
-- 2.把上面的结果当作子查询，和emp进行多表查询
select *
from emp,(
select deptno ,max(sal) as max_sal
from emp
group by deptno
)temp
where temp.max_sal = emp.sal and temp.deptno = emp.deptno
案例练习4
-- 显示每个部门的信息(包括：部门名，编号，地址)和人员数量
-- 1.各个部门的人员数量，构建一个临时表
select count(*),deptno
from emp
group by deptno;
-- 2.通过临时表建立查询
select dname,dept.deptno,dept.loc,temp.per_num as '人数'
from dept,(
select count(*)as per_num,deptno
from emp
group by deptno
) temp
where temp.deptno = dept.deptno
-- 第二种写法 表.* 表示将该表所有的列都显示出来，可以简化sql语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
select temp.*,dname,loc
from dept,(
select count(*)as per_num,deptno
from emp
group by deptno
) temp
where temp.deptno = dept.deptno
注意
- 子查询别名使用
多列子查询
多列子查询则是指查询返回多个列数据的子查询语句
-- 如何查询与smith的部门和岗位完全相同的所有雇员(并且不包含smith本人)
-- 1.得到smith的部门和岗位
select deptno,job
from emp
where ename = 'SMITH'
-- 2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
select *
from emp
where (deptno, job) = (
select deptno,job
from emp
where ename = 'smith'
) and ename != 'smith'
思考：查询表中与某位同学各科成绩相同的学生（不再示范）
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
