-- 内连接-- 内连接演示-- 1、查询每一个员工的姓名,以及关联的部门的名称(隐式内连接实现)-- 表结构:emp,dept-- 连接条件:emp.dept_id = dept.idselect emp.name, dept.name from emp, dept where emp.dept_id = dept.id;select e.name, d.name from emp e, dept d where e.dept_id = d.id;-- 2、查询每一个员工的姓名,以及关联的部门的名称(显式内连接实现)-- 表结构:emp,dept-- 连接条件:emp.dept_id = dept.id-- 第一个表 inner join 第二个表 on 连接条件(inner关键字可以省略)select e.name, d.name from emp e innerjoin dept d on e.dept_id = d.id;-- 外连接-- 外连接演示-- 1、查询emp表的所有数据,和对应部门的信息(左外连接)(outer可省略掉)-- 表结构:emp,dept-- 连接条件:emp.dept_id = dept.idselect e.*, d.name from emp e leftouterjoin dept d on e.dept_id = d.id;-- 2、查询dept的所有数据,和对应的员工信息(右外连接)select d.*, e.*from emp e rightouterjoin dept d on e.dept_id = d.id;select d.*, e.*from dept d leftouterjoin emp e on e.dept_id = d.id;-- 自连接-- 1、查询员工以及所属领导的名字-- 表结构:emp a, emp, bselect a.name, b.name from emp a, emp b where a.managerid = b.id;-- 2、查询所有员工emp及其领导的名字emp,如果员工没有领导,也需要查询出来-- 表结构:emp a, emp, bselect a.name '员工', b.name '领导'from emp a leftjoin emp b on a.managerid = b.id;-- 联合查询-union, union all-- 对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。-- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。-- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。-- 1、将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来。select*from emp where salary <5000unionallselect*from emp where age >50;-- 上述SQL语句执行以后会有重复的条目,执行下述语句可去重select*from emp where salary <5000unionselect*from emp where age >50;
-- 标量子查询-- --------------------------------------------子查询----------------------------------------- 标量子查询-- 1、查询销售部的所有员工信息-- 先查询出销售部的部门IDselect id from dept where name ='销售部';-- 根据销售部的部门ID查找员工信息select*from emp where dept_id =4;select*from emp where dept_id =(select id from dept where name ='销售部');-- 2、查询在东方白入职之后的员工信息-- 查询方东白的入职日期select entrydate from emp where name ='方东白';-- 查询指定入职时期之后入职的员工信息select*from emp where entrydate >'2009-02-12';select*from emp where entrydate >(select entrydate from emp where name ='方东白');-- 列子查询-- 1、查询销售部和市场部的所有员工信息-- 查询销售部和市场部的部门IDselect id from dept where name ='销售部'or name ='市场部';-- 根据部门ID查询员工信息select*from emp where dept_id in(2,4);select*from emp where dept_id in(select id from dept where name ='销售部'or name ='市场部');-- 2、查询比财务部所有人工资都高的员工信息-- 查询财务部所有人的工资select id from dept where name ='财务部';select salary from emp where dept_id =3;select salary from emp where dept_id =(select id from dept where name ='财务部');-- 查询比财务部所有人工资都高的员工信息select*from emp where salary >all(select salary from emp where dept_id =(select id from dept where name ='财务部'));-- 3、查询比研发部其中任意一人工资高的员工信息select id from dept where name ='研发部';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 ='研发部'));select*from emp where salary >some(select salary from emp where dept_id =(select id from dept where name ='研发部'));-- 行子查询-- 1、查询与张无忌的薪资及直属领导相同的员工信息-- 查询张无忌的薪资及其直属领导select salary, managerid from emp where name ='张无忌';-- 查询与张无忌的薪资及直属领导相同的员工信息select*from emp where salary =12500and managerid =1;select*from emp where(salary, managerid)=(12500,1);select*from emp where(salary, managerid)=(select salary, managerid from emp where name ='张无忌');-- 表子查询-- 子查询结果返回的是多行多列,这种查询称为表子查询-- 1、查询与鹿杖客,宋远桥的职位和薪资相同的员工信息-- 查询鹿杖客和宋远桥的职位和薪资select job, salary from emp where name ='鹿杖客'or name ='宋远桥';-- 查询与鹿杖客,宋远桥的职位和薪资相同的员工信息select*from emp where(job, salary)in(select job, salary from emp where name ='鹿杖客'or name ='宋远桥');-- 2、查询入职日期是2006-01-01之后的员工信息,及其部门信息-- 查询入职日期是2006-01-01之后的员工信息select*from emp where entrydate >'2006-01-01';-- 查询这部分员工对应的部门信息select e.*, d.*from(select*from emp where entrydate >'2006-01-01') e leftjoin dept d on e.dept_id = d.id;-- --------------------------------多表查询案例---------------------------------------------createtable salgrade(
grade int,
losal int,
hisal int)comment'薪资等级表';insertinto salgrade values(1,0,3000);insertinto salgrade values(2,3001,5000);insertinto salgrade values(3,5001,8000);insertinto salgrade values(4,8001,10000);insertinto salgrade values(5,10001,15000);insertinto salgrade values(6,15001,20000);insertinto salgrade values(7,20001,25000);insertinto salgrade values(8,25001,30000);-- 1、查询员工的姓名、年龄、职位、部门信息(隐式内连接)-- 表:emp,dept-- 连接条件:emp.dept_id = dept.idselect e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;-- 2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)-- 表:emp,dept-- 连接条件:emp.dept_id = dept.idselect e.name, e.age, e.job, d.name from emp e innerjoin dept d on e.dept_id = d.id where e.age <30;-- 3、查询拥有员工的部门ID、部门名称-- 表:emp,dept-- 连接条件:emp.dept_id = dept.idselectdistinct d.id, d.name from emp e, dept d where e.dept_id = d.id;-- 4、查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来select e.*, d.name from emp e leftjoin dept d on d.id = e.dept_id where e.age >40;-- 5、查询所有员工的工资等级-- 表:emp,salgrade-- 连接条件:emp.salary >= salgrade.losal and emp..salary <= salgrade.hisalselect 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;-- 查询研发部所有员工的信息及工资等级-- 表:emp,salarygrade, dept-- 连接条件:e.salary between s.losal and s.hisal,emp.dept_id = dept.id-- 查询条件: dept.name = '研发部'select e.*, s.grade
from emp e,
dept d,
salgrade s
where e.dept_id = d.id
and(e.salary between s.losal and hisal)and d.name ='研发部';-- 7、查询研发部的平均工资-- 表:emp,dept-- 连接条件:emp.dept_id = dept.idselectavg(e.salary)from emp e, dept d where e.dept_id = d.id and d.name ='研发部';-- 8、查询工资比灭绝高的员工信息-- 查询灭绝的薪资select salary from emp where name ='灭绝';-- 查询工资比灭绝高的的员工信息select*from emp where salary >(select salary from emp where name ='灭绝');-- 9、查询比平均薪资高的员工信息-- 查询员工的平均薪资selectavg(salary)from emp;-- 查询比平均薪资高的员工信息select*from emp where salary >(selectavg(salary)from emp);-- 10、查询低于本部门平均工资的员工信息-- 比如查询指定部门平均薪资1selectavg(salary)from emp where emp.dept_id =1;-- 查询低于本部门平均工资的员工信息select*,(selectavg(salary)from emp e1 where e1.dept_id = e2.dept_id)'平均'from emp e2
where e2.salary <(selectavg(salary)from emp e1 where e1.dept_id = e2.dept_id);-- 11、查询所有的部门信息,并统计部门的员工人数select id,name from dept;select id,name,(selectcount(*)from emp e where e.dept_id = d.id)'人数'from dept d;selectcount(*)from emp where dept_id =1;-- 12、查询所有学生的选课情况,展示出学生名称,学号,课程名称-- 表:student,course,student_course-- 连接条件:student.id = student_course.studentid, course.id = student_course.courseidselect s.name, s.no, c.name
from student s,
student_course sc,
ccourse c
where s.id = sc.studentid
and c.id = sc.courseid;