Javaweb之MySQL中的多表查询语句

树仔

一、什么是多表查询

        在了解多表查询之前，我们先看一下什么是单表查询。

1. select * from A

复制代码

       这是单表查询，那什么又是多表查询呢？

1. select * from A,B

复制代码

       这是初学者比较容易理解的多表查询，就是直接查询两张表的字段，当然，星号可以改为对应的字段名。但是这样直接查询两张表，展示的结果会出现笛卡尔积。
        笛卡尔积：有A，B两个集合，取A，B所有的组合情况。比如A表有6条数据，B表有4条数据，那么查询出来就是24条数据。

二、多表查询的分类

        多表查询总体分为两类：连接查询和子查询。

2.1 连接查询

        连接查询又分类两种：内连接和外连接。

2.1.1 内连接

        相当于查询A，B交集数据。下面给出它的两种写法，掌握一种就够用了。

1. -- 隐式内连接
   
2. select 字段列表 from A表,B表 where 条件                                -- 比如某两列相同
   
3. -- 显示内连接
   
4. select 字段列表 from A表 inner join B表 on 条件                -- inner可以省略不写

复制代码

2.1.2 外连接

        左外连接：相当于查询A表所有数据和交集部分数据。
        右外连接：相当于查询B表所有数据和交集部分数据。
        我们常用的就是左外连接，实际上他俩没什么差别，掌握一种就可以。

1. -- 左外连接
   
2. select 字段列表 from A表 left outer join B表 on 条件                    -- outer可以省略不写
   
3. -- 右外连接
   
4. select 字段列表 from A表 right outer join B表 on 条件                -- outer可以省略不写

复制代码

2.2 子查询

        查询中嵌套查询，称嵌套查询为子查询。子查询根据查询结果不同，作用不同，总体分为三类：单行单列，多行单列，多行多列。
        看到这里可能有点懵逼，子查询的概念说的好模糊呀，后面给出的三类分别是啥意思呀？别着急，慢慢看，并不难，很容易理解的。

2.2.0 示例

        首先来看下什么是子查询：称嵌套查询为子查询

1. -- 比如要在一张工资表中查询比张三工资高的人的信息
   
2. -- 首先查询张三的工资
   
3. select salary from A where name='张三'
   
4. -- 然后根据得到的查询结果，假设是3000，再查询salary比3000大的人的信息
   
5. select * from A where salary>3000
   
6. -- 这两句查询语句可以合并在一起，我们称之为子查询
   
7. select * from A where salary>(select salary from A where name='张三')

复制代码

       上例看完之后，你应该对子查询有一定的概念了，理解之后再往下看。

2.2.1 单行单列

        判断依据：作为条件值，使用 > , < , = , != 等进行条件判断。
        上例就是单行单列的查询。因为嵌套查询出来的结果是一个单行单列的表，所以称为单行单列查询。嵌套查询结果为：

1. 3000

复制代码

2.2.2 多行单列

        判断依据：作为条件值，使用 in 等关键字进行条件判断。

1. select 字段列表 from 表 where 字段名 in (子查询)

复制代码

        举个例子：现有一张员工表(emp)和一张部门表(dept)，员工表中除了姓名、性别等基本信息外，还有对应部门的 id 号；部门表中不同的部门对应唯一的 id 号。要求查询“财务部”和“市场部”所有的员工信息。

1. -- 一般情况
   
2. -- 先查询到财务部和市场部对应的id号,假设对应为3,4
   
3. select id from dept where name in ("财务部","市场部")                        -- 此时查询结果是一个多行单列的表
   
4. -- 然后查询员工表中对应部门id的员工
   
5. select * from emp where emp.dept_id in (3,4)
   
6. -- 子查询
   
7. select * from emp where emp.dept_id in (select id from dept where name in ("财务部","市场部"))

复制代码

       多行单列指的就是子查询（括号里面的查询）的结果是多行单列的。嵌套查询结果为：

name

财务部

市场部

2.2.3 多行多列

1. select 字段列表 from (子查询) where 条件

复制代码

       举个例子：还是有一张员工表(emp)和一张部门表(dept)，要求查询出年龄在20岁以上的员工的员工信息和部门信息。

1. -- 一般情况
   
2. -- 先查询20岁以上员工的员工信息
   
3. select * from emp where age>20                        -- 此时查询结果是一个多行多列的表
   
4. -- 再查询对应的部门信息
   
5. select * from emp,dept where emp.dept_id=dept.id
   
6. -- 子查询
   
7. -- 可以将第一次查询出的表作为一张虚拟表，在这张虚拟表与部门表中做筛选，直接将emp改为子查询即可，要给虚拟表赋一个名字
   
8. select * from (select * from emp where age>20) as t,dept where t.dept_id=dept.id

复制代码

       多行多列指的是嵌套查询是多行多列的一张表，我们把这张表称作“虚拟表”。在本例中，这张虚拟表的内容是20岁以上员工的信息，假设是下面这样：

id	name	age	dept
0001	张三	25	财务部
0002	李四	31	销售部
0003	王五	26	财务部

2.3 小结

        这一小部分的内容看完之后，应该是能基本清楚“子查询是什么”，“什么是虚拟表”，“子查询的三种不同类型区别是什么”等问题（讲解的内容在代码注释里面，没在正文）。如果不清楚，那是我的问题，没讲清楚，欢迎留言批评指正。

三、案例

        本例给出四张表，解决相关问题，巩固子查询的知识。四张表分别是：
        部门表：部门id，部门名称，部门所在地；
       职务表：职务名称，职务描述；
        员工表：员工id，员工姓名，职务id，上级领导，入职日期，工资，奖金，所在部门编号；
        工资等级表：级别，最低工资，最高工资。

3.1 数据

1. DROP TABLE IF EXISTS emp;
   
2. DROP TABLE IF EXISTS dept;
   
3. DROP TABLE IF EXISTS job;
   
4. DROP TABLE IF EXISTS salarygrade;
   
5. SELECT * FROM emp
   
6. SELECT * FROM dept
   
7. SELECT * FROM job
   
8. SELECT * FROM salarygrade
   
9. -- 部门表
   
10. CREATE TABLE dept (
    
11.   id INT PRIMARY KEY PRIMARY KEY, -- 部门id
    
12.   dname VARCHAR(50), -- 部门名称
    
13.   loc VARCHAR(50) -- 部门所在地
    
14. );
    
15. -- 职务表，职务名称，职务描述
    
16. CREATE TABLE job (
    
17.   id INT PRIMARY KEY,
    
18.   jname VARCHAR(20),
    
19.   description VARCHAR(50)
    
20. );
    
21. -- 员工表
    
22. CREATE TABLE emp (
    
23.   id INT PRIMARY KEY, -- 员工id
    
24.   ename VARCHAR(50), -- 员工姓名
    
25.   job_id INT, -- 职务id
    
26.   mgr INT , -- 上级领导
    
27.   joindate DATE, -- 入职日期
    
28.   salary DECIMAL(7,2), -- 工资
    
29.   bonus DECIMAL(7,2), -- 奖金
    
30.   dept_id INT, -- 所在部门编号
    
31.   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
    
32.   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
    
33. );
    
34. -- 工资等级表
    
35. CREATE TABLE salarygrade (
    
36.   grade INT PRIMARY KEY,   -- 级别
    
37.   losalary INT,  -- 最低工资
    
38.   hisalary INT -- 最高工资
    
39. );
    
40. #解决编码问题
    
41. ALTER TABLE dept DEFAULT CHARACTER SET UTF8;
    
42. ALTER TABLE dept CHANGE dname dname VARCHAR(20) CHARACTER SET UTF8;
    
43. ALTER TABLE dept CHANGE loc loc VARCHAR(50) CHARACTER SET UTF8;                               
    
44. -- 添加4个部门
    
45. INSERT INTO dept(id,dname,loc) VALUES
    
46. (10,'教研部','北京'),
    
47. (20,'学工部','上海'),
    
48. (30,'销售部','广州'),
    
49. (40,'财务部','深圳');
    
50. #解决编码问题
    
51. ALTER TABLE job DEFAULT CHARACTER SET UTF8;
    
52. ALTER TABLE job CHANGE jname jname VARCHAR(50) CHARACTER SET UTF8;
    
53. ALTER TABLE job CHANGE description description VARCHAR(50) CHARACTER SET UTF8;       
    
54. -- 添加4个职务
    
55. INSERT INTO job (id, jname, description) VALUES
    
56. (1, '董事长', '管理整个公司，接单'),
    
57. (2, '经理', '管理部门员工'),
    
58. (3, '销售员', '向客人推销产品'),
    
59. (4, '文员', '使用办公软件');
    
60. #解决编码问题
    
61. ALTER TABLE emp DEFAULT CHARACTER SET UTF8;
    
62. ALTER TABLE emp CHANGE ename ename VARCHAR(50) CHARACTER SET UTF8;
    
63. -- 添加员工
    
64. INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
    
65. (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
    
66. (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
    
67. (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
    
68. (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
    
69. (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
    
70. (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
    
71. (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
    
72. (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
    
73. (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
    
74. (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
    
75. (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
    
76. (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
    
77. (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
    
78. (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
    
79. -- 添加5个工资等级
    
80. INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
    
81. (1,7000,12000),
    
82. (2,12010,14000),
    
83. (3,14010,20000),
    
84. (4,20010,30000),
    
85. (5,30010,99990);

复制代码

3.2 问题及代码

1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述。
         员工编号、员工姓名、工资都在emp表中，职务名称、职务描述都在job表中，所以从这两个表中查询对应字段；并且加上约束条件：让员工的职务id（emp.job_id）和部门id（job.id）相等即可。

1. SELECT e.id,e.ename,e.salary,j.jname,j.description FROM emp e,job j WHERE e.job_id=j.id

复制代码

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置。
        分析同上，部门名称和部门位置在dept表中，只需加上响应的字段和约束条件即可。

1. SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc FROM emp e,job j,dept d WHERE e.job_id=j.id AND e.dept_id=d.id

复制代码

3.查询员工姓名，工资，工资等级。

1. SELECT * FROM emp e,salarygrade s WHERE e.salary>=s.losalary AND e.salary<=s.hisalary

复制代码

4.查询出部门编号、部门名称、部门位置、部门人数。
        部门编号、部门名称、部门位置都来自于dept表；部门人数可以根据emp.dept_id分组，然后使用count(*)计算得出；然后进行子查询，让部门表和分组后的表进行内连接。

1. SELECT * FROM dept d,(SELECT dept_id,COUNT(*) FROM emp GROUP BY emp.dept_id) t WHERE t.dept_id=d.id

复制代码

到此这篇关于Javaweb MySQL中的多表查询的文章就介绍到这了,更多相关mysql多表查询内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/291972uoh.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！