企业官网建设流程全解析

多表查询

之前再DQL中初步整理了用select关键字进行单表查询，多表查询是利用数据表中的同一外键进行连接，从而获取更多的数据进行连接查询。

多表查询

• 多表关系
• 多表查询概述
• 内连接
• 外连接
• 自连接
• 子查询
• 多表查询案例

我们需要事先插入一些相关的表格

• 学生表

createtablestudent(idintauto_incrementprimarykeycomment'主键ID',namevarchar(10)comment'姓名',novarchar(10)comment'学号')comment'学生表';insertintostudentvalues(null,'黛绮丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');

• 课程表

createtablecourse(idintauto_incrementprimarykeycomment'主键ID',namevarchar(10)comment'课程名称')comment'课程表';insertintocoursevalues(null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');

• 学生课程表

createtablestudent_course(idintauto_incrementcomment'主键'primarykey,studentidintnotnullcomment'学生ID',courseidintnotnullcomment'课程ID',constraintfk_courseidforeignkey(courseid)referencescourse(id),constraintfk_studentidforeignkey(studentid)referencesstudent(id))comment'学生课程中间表';insertintostudent_coursevalues(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

• 用户基本信息表

createtabletb_user(idintauto_incrementprimarykeycomment'主键ID',namevarchar(10)comment'姓名',ageintcomment'年龄',genderchar(1)comment'1:男, 2:女',phonechar(11)comment'手机号')comment'用户基本信息表';insertintotb_user(id,name,age,gender,phone)VALUES(null,'黄渤',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');

• 用户教育信息表

createtabletb_user_edu(idintauto_incrementprimarykeycomment'主键ID',degreevarchar(20)comment'学历',majorvarchar(50)comment'专业',primaryschoolvarchar(50)comment'小学',middleschoolvarchar(50)comment'中学',universityvarchar(50)comment'大学',useridintuniquecomment'用户ID',constraintfk_useridforeignkey(userid)referencestb_user(id))comment'用户教育信息表';insertintotb_user_edu(id,degree,major,primaryschool,middleschool,university,userid)VALUES(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

内连接

隐式内连接

select字段列表from表1,表2where连接条件and筛选条件;

显式内连接

select字段列表from表1[inner]join表2on连接条件...;

内连接查询的是两张表交集的部分

-- 查询每一个员工的姓名及关联部门的名称selectemp.name,dept.namefromemp,deptwhereemp.dept_id=dept.id;-- （起别名）selecte.name,de.namefromemp e,dept dewheree.dept_id=de.id;-- 显式查询selecte.name,d.namefromemp ejoindept done.dept_id=d.id;

外连接

实际上用左连接居多，右连接也可以改成左连接。

• 左外连接

select字段列表from表1left[outer]join表2on条件...;

• 相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据
• 右外连接

select字段列表from表1right[outer]join表2on条件...;

-- 查询emp表的所有数据，和对应的部门信息(左外连接)selecte.*,d.namefromemp eleftouterjoindept done.dept_id=d.id;-- 查询dept表的所有数据，和对应的员工信息（右外连接）selectd.*,e.*fromemp erightouterjoindept done.dept_id=d.id;

自连接

当自身表的两个字段需要进行连接时

必须要分别起别名，不然不知道具体是哪张表用的这个字段

-- 查询员工及其所属领导的名字selecta.name,b.namefromemp a,emp bwherea.managerid=b.id;-- 查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来selecta.name'员工',b.name'领导'fromemp aleftjoinemp bona.managerid=b.id;

子查询

用select 进行嵌套，将表筛出来一遍之后再进行查询

• 标量子查询：利用上一个select查出来的结果作为另一个查询的条件并且第一次查询出来的结果只有一个信息
  • 子查询返回的结果是单个值（数字、字符串、日期）等最简单的形式。

-- 总目标：查询"销售部"的所有员工信息-- a.查询"销售部"的所有员工信息 (查出来是4)selectidfromdeptwherename='销售部';-- b.查询销售部部门ID, 查询员工信息select*fromempwheredept_id=4;-- 合并：4就是a查出来的结果，直接替换即可select*fromempwheredept_id=(selectidfromdeptwherename='销售部');-- 总：查询“方东白”之后入职的员工信息-- a.查询“方东白”的入职时间selectentrydatefromempwherename='方东白';-- b.查询所有入职时间晚于此时间的员工信息select*fromempwhereentrydate>'2009-02-12';-- 总：select*fromempwhereentrydate>(selectentrydatefromempwherename='方东白');

• 列子查询：子查询返回的结果是一列（可以是多行）
  • 常用操作符：IN NOT IN ANY SOME ALL

-- 查询"销售部"和"市场部"的所有员工信息select*fromempwheredept_idin(selectidfromdeptwheredept.name='销售部'or'市场部');-- 查询比 财务部 所有人工资都高的员工信息(max 和 any 都可以)-- 先查财务部的id,再查财务部最高的薪水，然后是大于这个薪水的人员信息select*fromempwheresalary>(selectmax(salary)fromempwheredept_id=(selectidfromdeptwherename='财务部'));select*fromempwheresalary>all(selectsalaryfromempwheredept_id=(selectidfromdeptwherename='财务部'));-- 查询比研发部其中任意一人工资高的员工信息select*fromempwheresalary>any(selectsalaryfromempwheredept_id=(selectidfromdeptwherename='研发部'));

• 行子查询：子查询返回的结果是一行（同时包含多个字段）

-- 查询与"张无忌"的薪资及直属领导相同的员工信息-- 1.先查出来张无忌的薪资和领导selectsalary,manageridfromempwherename='张无忌';-- 查出来薪资和领导一样的员工信息select*fromempwhere(salary,managerid)=(12500,1);-- 总和：select*fromempwhere(salary,managerid)=(selectsalary,manageridfromempwherename='张无忌');

• 表子查询： 查询返回的是多行多列（一张表），往往可以放在from后面用于查询。

-- 查询与"鹿杖客","宋远桥"的职位和薪资相同的员工信息-- 1.先查询两个人的职位和薪资selectjob,salaryfromempwherenamein('鹿杖客','宋远桥');

-- 2.查询职位和薪资在表中有的信息select*fromempwhere(job,salary)in(selectjob,salaryfromempwherenamein('鹿杖客','宋远桥'));

-- 查询入职日期是 2006-01-01 之后的员工信息，及其部门信息-- 1.入职日期之后的员工信息select*fromempwhereentrydate>'2006-01-01';-- 2.查询这部分员工对应的部门信息selecta.*,b.*from这部分表 aleftjoindept bona.dept_id=b.id;-- 总：selecta.*,b.*from(select*fromempwhereentrydate>'2006-01-01')aleftjoindept bona.dept_id=b.id;

多表查询案例

1. 查询员工的姓名、年龄、职位、部门信息。

2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。

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

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

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

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

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

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

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

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

11. 查询所有的部门信息，并统计部门的员工人数。

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

主要用到emp，dept表和salgrade表（薪资等级）

将salgrade表插入:

createtablesalgrade(gradeint,losalint,hisalint)comment'薪资等级表';insertintosalgradevalues(1,0,3000);insertintosalgradevalues(2,3001,5000);insertintosalgradevalues(3,5001,8000);insertintosalgradevalues(4,8001,10000);insertintosalgradevalues(5,10001,15000);insertintosalgradevalues(6,15001,20000);insertintosalgradevalues(7,20001,25000);insertintosalgradevalues(8,25001,30000);

12个多表查询案例

-- 1. 查询员工的姓名、年龄、职位、部门信息。selecte.name,age,job,d.namefromemp e,dept dwheree.dept_id=d.id;-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。selecte.name,age,job,d.namefromemp e,dept dwheree.dept_id=d.idandage<30;selecte.name,e.age,e.job,d.name,fromemp ejoindept done.dept_id=d.idwheree.age<30;-- 3. 查询拥有员工的部门ID、部门名称。selectdistinctd.id,d.namefromemp e,dept dwheree.dept_id=d.id;selecte.dept_id,d.namefromemp e,dept dwheree.dept_id=d.idgroupbye.dept_id,d.namehavingcount(e.dept_id)>0;-- 4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。selecte.*,d.namefromemp eleftjoindept done.dept_id=d.idwheree.age>40;-- 5. 查询所有员工的工资等级。selecte.*,s.gradefromemp e,salgrade swheree.salarybetweens.losalands.hisal;selecte.*,s.gradefromemp e,salgrade swheree.salary>=s.losalande.salary<=s.hisal;-- 6. 查询"研发部"所有员工的信息及工资等级。-- 先在dept找研发部id，然后在emp筛研发部信息所有员工信息，然后求工资等级selecte.*,s.gradefromemp e,dept d,salgrade swheree.dept_id=d.idand(e.salarybetweens.losalands.hisal)andd.name='研发部';selecte.*,s.gradefrom(select*fromempwheredept_id=(selectidfromdeptwherename='研发部'))e,salgrade swheree.salary>=s.losalande.salary<=s.hisal;-- 7. 查询"研发部"员工的平均工资。-- 先查出来研发部的id, 然后再算所有id一样的人的工资的平均值selectavg(salary)fromemp e,dept dwheree.dept_id=d.idandd.name='研发部';selectavg(salary)fromempwheredept_id=(selectidfromdeptwherename='研发部');-- 8. 查询工资比"灭绝"高的员工信息。select*fromempwheresalary>(selectsalaryfromempwherename='灭绝');-- 9. 查询比平均薪资高的员工信息select*fromempwheresalary>(selectavg(salary)fromemp);-- 10. 查询低于本部门平均工资的员工信息。-- 外层查询每一行，内层查询计算该员工所在部门的平均工资，然后比较。--select*fromemp ewheree.salary<(selectavg(salary)fromempwheree.dept_id=dept_id);-- 11. 查询所有的部门信息，并统计部门的员工人数。selectd.id,d.name,(selectcount(*)fromemp ewheree.dept_id=d.id)'人数'fromdept d;-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称selects.name'学生名称',s.no'学号',c.name'课程名称'fromstudent s,course c,student_course scwheres.id=sc.studentidandc.id=sc.courseid;