数据库基础 DQL (多表基础练习)

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结
构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

内连接

– 隐式连接

select *
from emp,dept
where emp.dept_id=dept.id;

– 显示连接

select * from emp inner join dept d on emp.dept_id = d.id;

– A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

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

– B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)

select emp.name '员工',d.name '部门' from emp inner join dept d on emp.dept_id = d.id;

外连接

– A. 查询emp表的所有数据, 和对应的部门信息

select emp.*,d.name from emp left join dept d on d.id = emp.dept_id;

– B. 查询dept表的所有数据, 和对应的员工信息(右外连接)

select emp.*,dept.name from dept left join emp on dept.id = emp.dept_id;

自连接

select *
from emp;
– A. 查询员工 及其 所属领导的名字

select e1.name,e2.name from emp e1 join emp e2 on e1.managerid=e2.id;

– B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select e1.name,e2.name from emp e1 left join emp e2 on e1.managerid=e2.id;

联合查询

– A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

select *
from emp where salary < 5000 or age > 50;

select *
from emp where salary<5000
union all
select *
from emp where age>50;

子查询

标量子查询

– A. 查询 “销售部” 的所有员工信息

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

– B. 查询在 “方东白” 入职之后的员工信息

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

列子查询（常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL）

– A. 查询 “销售部” 和 “市场部” 的所有员工信息

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

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

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

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

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

行子查询

– A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

select salary,managerid
from emp where name ='张无忌';

select *
from emp where (salary,managerid) in (select salary,managerid
from emp where name ='张无忌');

表子查询

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

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

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

select emp.*,d.name
from emp left join dept d on d.id = emp.dept_id where entrydate > '2006-01-01';
select *
from emp where entrydate > '2006-01-01';
select e.*,dept.* from (select *
from emp where entrydate > '2006-01-01') e
left join dept on e.dept_id =dept.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 emp.name,emp.name,emp.job,dept.name
from emp,dept where emp.dept_id = dept.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;
/*
 3). 查询拥有员工的部门ID、部门名称
 */
select distinct dept.id,dept.name
from dept,emp where emp.dept_id = dept.id;
/*
 4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出
来(外连接)
 */
select e.*,d.name
from emp e left join dept d on d.id = e.dept_id where e.age>40;
/*
 5). 查询所有员工的工资等级
 */
select e.*,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
/*
 6). 查询 "研发部" 所有员工的信息及 工资等级
 */
 select * from (select e.*,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal) p join dept
    on p.dept_id=dept.id where dept.name = '研发部';
/*
 7). 查询 "研发部" 员工的平均工资
 */
 select avg(e.salary) as '研发部平均工资' from emp e join dept d on d.id = e.dept_id where 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 d.id '部门id', avg(emp.salary) '平均薪资' from emp join dept d on emp.dept_id = d.id group by d.id having avg(emp.salary) ;
select * from emp e,(select d.id '部门id', avg(emp.salary) '平均薪资' from emp join dept d on emp.dept_id = d.id group by d.id having avg(emp.salary))
p where salary < p.平均薪资 and e.dept_id = p.部门id;
/*
 11). 查询所有的部门信息, 并统计部门的员工人数
 */
 select dept.*,(select count(*) from emp e where e.dept_id = dept.id) '员工人数' from dept;
/*
 12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
 */
select *
from student s,student_course sc,course c
where s.id=sc.studentid and c.id =sc.courseid;