Javaweb之MySQL中的多表查询语句

一、什么是多表查询

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

select * from A

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

select * from A,B

这是初学者比较容易理解的多表查询，就是直接查询两张表的字段，当然，星号可以改为对应的字段名。但是这样直接查询两张表，展示的结果会出现笛卡尔积。

笛卡尔积：有A，B两个集合，取A，B所有的组合情况。比如A表有6条数据，B表有4条数据，那么查询出来就是24条数据。

二、多表查询的分类

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

2.1 连接查询

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

2.1.1 内连接

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

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

2.1.2 外连接

左外连接：相当于查询A表所有数据和交集部分数据。

右外连接：相当于查询B表所有数据和交集部分数据。

我们常用的就是左外连接，实际上他俩没什么差别，掌握一种就可以。

— 左外连接
select 字段列表 from A表 left outer join B表 on 条件 — outer可以省略不写
— 右外连接
select 字段列表 from A表 right outer join B表 on 条件 — outer可以省略不写

2.2 子查询

查询中嵌套查询，称嵌套查询为子查询。子查询根据查询结果不同，作用不同，总体分为三类：单行单列，多行单列，多行多列。

看到这里可能有点懵逼，子查询的概念说的好模糊呀，后面给出的三类分别是啥意思呀？别着急，慢慢看，并不难，很容易理解的。

2.2.0 示例

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

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

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

2.2.1 单行单列

判断依据：作为条件值，使用> , < , = , !=等进行条件判断。

上例就是单行单列的查询。因为嵌套查询出来的结果是一个单行单列的表，所以称为单行单列查询。嵌套查询结果为：

3000

2.2.2 多行单列

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

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

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

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

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

name财务部市场部

2.2.3 多行多列

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

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

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

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

idnameagedept0001

张三

25财务部

0002李四31销售部0003王五26财务部

2.3 小结

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

三、案例

本例给出四张表，解决相关问题，巩固子查询的知识。四张表分别是：

部门表：部门id，部门名称，部门所在地；

职务表：职务名称，职务描述；

员工表：员工id，员工姓名，职务id，上级领导，入职日期，工资，奖金，所在部门编号；

工资等级表：级别，最低工资，最高工资。

3.1 数据

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
SELECT * FROM emp
SELECT * FROM dept
SELECT * FROM job
SELECT * FROM salarygrade
— 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, — 部门id
dname VARCHAR(50), — 部门名称
loc VARCHAR(50) — 部门所在地
);
— 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
— 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, — 员工id
ename VARCHAR(50), — 员工姓名
job_id INT, — 职务id
mgr INT , — 上级领导
joindate DATE, — 入职日期
salary DECIMAL(7,2), — 工资
bonus DECIMAL(7,2), — 奖金
dept_id INT, — 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
— 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, — 级别
losalary INT, — 最低工资
hisalary INT — 最高工资
);
#解决编码问题
ALTER TABLE dept DEFAULT CHARACTER SET UTF8;
ALTER TABLE dept CHANGE dname dname VARCHAR(20) CHARACTER SET UTF8;
ALTER TABLE dept CHANGE loc loc VARCHAR(50) CHARACTER SET UTF8;
— 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,\’教研部\’,\’北京\’),
(20,\’学工部\’,\’上海\’),
(30,\’销售部\’,\’广州\’),
(40,\’财务部\’,\’深圳\’);
#解决编码问题
ALTER TABLE job DEFAULT CHARACTER SET UTF8;
ALTER TABLE job CHANGE jname jname VARCHAR(50) CHARACTER SET UTF8;
ALTER TABLE job CHANGE description description VARCHAR(50) CHARACTER SET UTF8;
— 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, \’董事长\’, \’管理整个公司，接单\’),
(2, \’经理\’, \’管理部门员工\’),
(3, \’销售员\’, \’向客人推销产品\’),
(4, \’文员\’, \’使用办公软件\’);
#解决编码问题
ALTER TABLE emp DEFAULT CHARACTER SET UTF8;
ALTER TABLE emp CHANGE ename ename VARCHAR(50) CHARACTER SET UTF8;
— 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,\’孙悟空\’,4,1004,\’2000-12-17\’,\’8000.00\’,NULL,20),
(1002,\’卢俊义\’,3,1006,\’2001-02-20\’,\’16000.00\’,\’3000.00\’,30),
(1003,\’林冲\’,3,1006,\’2001-02-22\’,\’12500.00\’,\’5000.00\’,30),
(1004,\’唐僧\’,2,1009,\’2001-04-02\’,\’29750.00\’,NULL,20),
(1005,\’李逵\’,4,1006,\’2001-09-28\’,\’12500.00\’,\’14000.00\’,30),
(1006,\’宋江\’,2,1009,\’2001-05-01\’,\’28500.00\’,NULL,30),
(1007,\’刘备\’,2,1009,\’2001-09-01\’,\’24500.00\’,NULL,10),
(1008,\’猪八戒\’,4,1004,\’2007-04-19\’,\’30000.00\’,NULL,20),
(1009,\’罗贯中\’,1,NULL,\’2001-11-17\’,\’50000.00\’,NULL,10),
(1010,\’吴用\’,3,1006,\’2001-09-08\’,\’15000.00\’,\’0.00\’,30),
(1011,\’沙僧\’,4,1004,\’2007-05-23\’,\’11000.00\’,NULL,20),
(1012,\’李逵\’,4,1006,\’2001-12-03\’,\’9500.00\’,NULL,30),
(1013,\’小白龙\’,4,1004,\’2001-12-03\’,\’30000.00\’,NULL,20),
(1014,\’关羽\’,4,1007,\’2002-01-23\’,\’13000.00\’,NULL,10);
— 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);