Mysql数据库命令大全(五）

目录

子查询

标量子查询

列子查询

行子查询

表子查询

多表查询练习

隐式内连接

显式内连接

去重distinct

左外连接

between ... and ...

三表查询

函数AVG

标量子查询

命名引用

---

子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个
根据子查询结果不同，分为：
A. 标量子查询（子查询结果为单个值）
B. 列子查询 ( 子查询结果为一列 )
C. 行子查询 ( 子查询结果为一行 )
D. 表子查询 ( 子查询结果为多行多列 )
根据子查询位置，分为：
A. WHERE 之后
B. FROM 之后
C. SELECT 之后
 

标量子查询

常用的操作符： = <> > >= < <=

查询 " 销售部 " 的所有员工信息

select * from emp where dept_id = (select id from dept where name = '销售部'); 

查询在 " 方东白 " 入职之后的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

常用的操作符：

 查询 " 销售部 " 和 " 市场部 " 的所有员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市
场部');

查询比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id = (select id 
from dept where name = '财务部') );

查询比研发部其中任意一人工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

行子查询

常用的操作符： = 、 <> 、 IN 、 NOT IN

查询与 " 张无忌 " 的薪资及直属领导相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

表子查询

常用的操作符： IN

A. 查询与 " 鹿杖客 " , " 宋远桥 " 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

多表查询练习

数据环境准备 :

create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

隐式内连接

1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;

显式内连接

2). 查询年龄小于 30 岁的员工的姓名、年龄、职位、部门信息（显式内连接）

select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

去重distinct

3). 查询拥有员工的部门 ID 、部门名称

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

左外连接

4). 查询所有年龄大于 40 岁的员工 , 及其归属的部门名称 ; 如果员工没有分配部门 , 也需要展示出

来 ( 外连接 )

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;

between ... and ...

5). 查询所有员工的工资等级

-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >=
s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary
between s.losal and s.hisal;

三表查询

6). 查询 " 研发部 " 所有员工的信息及 工资等级

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary 
between s.losal and s.hisal ) and d.name = '研发部';

函数AVG

7). 查询 " 研发部 " 员工的平均工资

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

标量子查询

8). 查询工资比 " 灭绝 " 高的员工信息

select * from emp where salary > ( select salary from emp where name = '灭绝' );

9). 查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

10). 查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id 
= e2.dept_id );

11). 查询所有的部门信息 , 并统计部门的员工人数（select ... from 之间也可以用子查询）

select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;

命名引用

12). 查询所有学生的选课情况 , 展示出学生名称 , 学号 , 课程名称

select s.name , s.no , c.name from student s , student_course sc , course c where s.id = 
sc.studentid and sc.courseid = c.id ;