博客

Java数据库编程之多表查询

  • Post author:
  • Post category:java




多表之间的关系



1.为什么要有多表和外键

需求：开发一个OA(办公自动化)系统，需要存储员工和部门信息。 

create table oa_emp(
	id int primary key auto_increment,
	name varchar(128),
	dept_name varchar(64),
	dept_location varchar(128)
);
-- 插入数据
insert into oa_emp values(null,'tony','研发部','上海市'),(null,'tony1','研发部','上海市'),(null,'tony2','市场部','南京市'),(null,'tony3','市场部','南京市'),(null,'tony4','行政部','深圳市');

将员工和部门存储在一张表中是有问题的：若研发部从上海迁移至南京，则员工表中的研发部门所在地的值都要修改，假设公司有10w人，则需要修改10w次。

即：这种做法会使得表中存在大量的冗余数据。

怎么解决上述问题？可以拆表。

即将oa_emp拆分为oa_emp和oa_dept两个表，分别存储员工信息和部门信息。这样只需改oa_dept里的信息就行。 

create table oa_emp(
	id int primary key auto_increment,
	name varchar(128),
	dept_id int not null
);
-- 部门信息表
create table oa_dept(
	id int primary key auto_increment,
	name varchar(128),
	location varchar(128)
);

insert into oa_dept values(null,'研发部','上海市'),(null,'市场部','南京市'),(null,'行政部','深圳市');
-- 这里1就表示oa_dept中的id为1的数据
insert into oa_emp values(null,'tony',1),(null,'tony1',1),(null,'tony2',2),(null,'tony3',2),(null,'tony4',3);

此时又带来一个问题，在添加员工的时候员工的部门id(dept_id)如果在部门表不存在也是可以插入的。

业务需求上员工属于一个不存在的部门显然是不合理的。此时我们可以使用

外键约束

来解决该问题。

dept_id=6对应的值是不存在的



2.外键的使用



外键的介绍

外键约束是为了维护多表之间的关系，保证引用数据的

完整性

。

外键就是一张从表中的某个字段引用主表中的主键。

主表即用来约束别人的，例如上面的oa_dept；从表就是使用别人的数据，被主表约束，例如上面的oa_emp。

外键的列的类型和主表中的主键的类型必须一致。

外键的语法： 

constraint 外键名 foreign key(从表字段名) references 主表(主键字段名) [on update cascade] [on delete cascade];



添加和删除外键



1.为已经有的表添加外键

即修改表，此时如果表中有非法数据会导致添加外键失败，必须删除非法数据后才能成功添加外键约束。 

alter table 表名 add constraint 外键名 foreign key(从表字段名) references 主表(主键字段名) [on update cascade] [on delete cascade];

其中：[on update cascade] 和[on delete cascade]两个可选项代表

级联操作

。

  • on update cascade 级联更新

    此时若主键发生更新，则外键关联的数据也会更新

  • on delete cascade 级联删除

    此时若主键发生删除，则外键关联的数据也会删除

如何判断是否添加了外键约束：Navicat中右键数据库进入“逆向数据库到模型”选项：菱形即表示外键约束

逆向数据库到模型：菱形即表示外键约束

此时再添加非法数据就会报错。

向oa_emp添加dept_id对应于主表中不存在的值就会报错

注意：如果没有级联操作，添加了外键约束之后删除数据表时需

先删除从表(oa_emp)，再删除主表(oa_dept)

。



2.创建表时添加外键

注意：

需先创建主表再创建从表

。 

-- 创建表时添加外键约束
-- 先创建主表
-- 部门信息表
create table oa_dept(
	id int primary key auto_increment,
	name varchar(128),
	location varchar(128)
);
-- 再创建从表
-- 员工信息表
create table oa_emp(
    id int primary key auto_increment,
	name varchar(128),
	dept_id int not null,
	constraint fk_dept_id foreign key(dept_id) references oa_dept(id) on update cascade on delete cascade -- fk_dept_id就是外键
);

此时再更改或者删除主表中的id则从表中的dept_id也会发生对应的变化。



3.删除外键

互联网项目中一般不会使用外键约束，其维持数据表中的完整性不会依赖数据库的外键，而是在Java程序中实现。

删除外键的语法： 

alter table 表名 drop foreign key 外键名;



3.多表之间的关系

使用多表是为了数据管理方便，这是因为数据全部放在一张表中不好管理，还会造成数据冗余。

在开发系统时可以根据业务设计不同的表。

例如

电商系统

会有如下常见的表：

  • 用户信息表
  • 商品信息表
  • 品类表
  • 支付信息表
  • 订单信息表
  • 订单详情表
  • 收货信息表
  • 等等

当系统存在多个表时，多表之间就会有如下关系：


  • 一对一：一对一可以创建成一张表

  • 一对多：在从表（多的一方）创建一个字段作为外键指向主表（少的一方）的主键

    • 一个品类下有多个商品，品类和商品是一对多的关系
    • 一个商品只会对应一个品类

  • 多对多：需要使用一张中间表，在中间表中至少要有两个字段作为外键，这两个字段分别作为外键指向各自的主键

    • 一个老师可以有多个学生
    • 一个学生可以有多个老师

案例：实现一个学校的

选课系统的数据库设计

，在选课系统中包含班级、学生和课程这三个实体，每个实体就是对应一张表。因此需要考虑班级、学生和课程之间的关系。

  • 一个班级可以有多个学生，一个学生只能属于一个班级——班级和学生的关系是一对多。
  • 一门课程可以有多个学生，一个学生可以学习多个课程——课程和学生的关系是多对多。

1.设计表结构 

-- 创建班级表
create table sms_class(
	id int primary key auto_increment,
	name varchar(64)
);

-- 一对多
-- 创建学生信息表
create table sms_student(
	id int primary key auto_increment,
	name varchar(128),
	age int,
	class_id int,
	constraint fk_class_id foreign key(class_id) references sms_class(id) 
	on update cascade on delete cascade -- fk_class_id为学生信息表中指向班级id的外键
);

-- 多对多
-- 创建课程信息表
create table sms_course(
	id int primary key auto_increment,
	name varchar(64)
);
-- 创建中间表
create table sms_student_course(
	student_course_id int primary key auto_increment,
	student_id int,
	course_id int,
	constraint fk_student_id foreign key(student_id) references sms_student(id) on update cascade on delete cascade, -- fk_student_id为学生信息表中指向课程id的外键
	constraint fk_course_id foreign key(course_id) references sms_course(id) on update cascade on delete cascade -- fk_course_id为课程信息表中指向学生id的外键
);

这样数据库的模型为：

设计好的数据库的模型



多表查询之连接查询

多表查询的常用查询有连接查询和子查询。其中连接查询又分为交叉查询、内连接查询和外连接查询三种。

数据准备： 

-- 员工信息表
create table oa_emp(
	id int primary key auto_increment,
	name varchar(128),
	gender char(10),
	salary decimal(10,2),
	hire_date date,
	dept_id int
);
-- 部门信息表
create table oa_dept(
	id int primary key auto_increment,
	name varchar(128),
	location varchar(128)
);

-- 增加部门信息
insert into oa_dept values(null,'研发部','南京'),(null,'市场部','南京'),(null,'行政部','南京');

-- 增加员工信息
insert into oa_emp values(null,'tony0','男',25000.00,'2015-10-12',1),(null,'tony1','女',35000.00,'2013-12-12',1),(null,'tony2','男',28000.00,'2015-12-10',3),(null,'tony3','男',15000.00,'2018-02-12',2),(null,'tony4','女',25000.00,'2017-02-12',1);

两张表的结果如下：

部门信息表

员工信息表



1.交叉查询

交叉查询就是把若干张表（至少是2张表）没有条件地连接在一起展示。 

select [*] [列名] from a,b;
select a.*,b.* from a,b; -- 查询a，b两张表的所有信息：a.*即表示a的所有数据
select * from a,b; -- 查询a，b两张表的所有信息

执行交叉查询，查询两张表的结果： 

-- 交叉查询员工信息和部门信息
select * from oa_emp,oa_dept;
-- 统计交叉查询数量 15
select count(*) from oa_emp,oa_dept;

交叉查询结果

可以发现，交叉查询的结果实际上是两张表的直接拼合，其数量为两张表的

笛卡尔乘积

。

即若集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔乘积为{a,0}{a,1}{a,2}{b,0}{b,1}{b,2}

在日常开发中不会使用交叉查询。



2.内连接查询

内连接查询即是在交叉查询的基础上添加过滤条件（即多个表之间的关联关系，也即主外键关系），去掉笛卡尔乘积中的错误数据。

内连接查询的特点是查询公共的部分，即满足连接条件（主外键关系）的部分，使用主外键关系作为连接条件是为了去掉无用的数据。

内连接查询分为隐式内连接查询和显式内连接查询。



隐式内连接查询

select a.* ,b.* from a,b where a.主键字段名 = b.外键字段名 [and 其他条件];

其中：a是主表，b是从表。这里where后面的是关联条件。

例如：下面语句的结果如下： 

-- 隐式内连接查询所有员工信息和部门信息
select * from oa_dept dept,oa_emp emp where dept.id = emp.dept_id; --dept和emp为起的别名
-- 隐式内连接查询id为1的员工信息和部门信息
select * from oa_dept dept,oa_emp emp where dept.id = emp.dept_id and emp.id=1;

隐式内连接的查询结果



显式内连接查询

select a.* ,b.* from a [inner] join b on a.主键字段名 = b.外键字段名 [where 其他条件];

其中：a是主表，b是从表。 

-- 显式内连接查询所有员工信息和部门信息
select * from oa_dept dept inner join oa_emp emp on dept.id = emp.dept_id;

结果和上面的一样。 

-- 查询所有部门下员工信息，如果员工没有部门就不显示员工信息和部门信息
-- 增加部门信息
insert into oa_dept values(null,'销售部','南京');
-- 增加员工信息
insert into oa_emp values(null,'tony5','女',19000.00,'2017-06-12',null);

select * from oa_dept dept inner join oa_emp emp on dept.id = emp.dept_id where emp.dept_id is not null;

结果和上面的一样。



3.外连接查询

内连接查询查询的结果是公共的，如果要

保证某个表的数据全部展示

的情况下进行连接查询，则需要使用外连接查询。外连接查询分为左外连接和右外连接查询两种。



左外连接查询

左外连接是以join左边的表为主表，展示主表的所有数据，根据条件查询连接右边的数据，如果满足条件则展示，不满足条件则显示null。

左外连接就是在内连接的基础之上保证左表的数据全部显示。 

select [*] [字段] from a left [outer] join b on a.主键字段名 = b.外键字段名 [where 其他条件];

-- 左外连接查询，查询所有部门下的员工信息，如果部门没有员工，则不显示员工信息，但是要显示部门信息
select * from oa_dept dept left outer join oa_emp emp on dept.id = emp.dept_id;

左外连接查询结果



右外连接查询

右外连接是以join右边的表为主表，展示主表的所有数据，根据条件查询连接左边表的数据，如果满足条件则展示，不满足条件则显示null。

右外连接就是在内连接的基础之上保证右表的数据全部显示。 

select [*] [字段] from a right [outer] join b on a.主键字段名 = b.外键字段名 [where 其他条件];

-- 右外连接查询，查询所有员工对应的部门信息，如果该员工没有所属部门，则显示员工信息，不显示部门信息
select * from oa_dept dept right outer join oa_emp emp on dept.id = emp.dept_id;

右外连接查询结果



多表查询之子查询



1.子查询的介绍

多表查询的连接查询解决不了复杂的业务需求，因此需要使用子查询实现。

子查询就是一个查询语句中至少有两个select语句，一个查询语句的查询结果会作为另外一个查询语句的条件。

子查询有查询的嵌套，子查询要用()包含起来。

子查询的查询结果有三种情况：

  1. 返回单行单列的值
  2. 返回单列多行的值
  3. 返回多列多行的值



2.子查询返回单行单列的值

子查询返回单行单列的值时，肯定是在where子句后面作为条件 

select 字段 from 表名 where 字段 运算符 (子查询语句);

案例： 

-- 子查询返回单行单列的值
-- 查询最高工资的员工信息
select max(salary) from oa_emp; -- 查询oa_emp表中salary的最大值
select * from oa_emp where salary = (select max(salary) from oa_emp);

查询结果：

子查询返回单行单列的值



3.子查询返回单列多行的值

子查询返回单列多行的值时，肯定是在where子句后面作为条件，且一般使用in 

select 字段 from 表名 where 字段 in (子查询语句);

案例： 

-- 子查询返回单列多行的值
-- 查询工资大于20000的员工来自哪个部门
select * from oa_emp emp where emp.salary >= 20000;
select dept.name from oa_dept dept where dept.id in (select emp.dept_id from oa_emp emp where emp.salary >= 20000);

-- 查询研发部和市场部的全部员工信息
select dept.id from oa_dept dept where dept.name='研发部' or dept.name='市场部';
select * from oa_emp emp where emp.dept_id in (select dept.id from oa_dept dept where dept.name='研发部' or dept.name='市场部');

查询结果：

查询工资大于20000的员工来自哪个部门

查询研发部和市场部的全部员工信息



4.子查询返回多列多行的值

子查询返回多列多行的值，多列多行的值就是一张虚拟表，这张虚拟表在使用时还需要取别名，否则将无法访问这张表的字段。这肯定是在from子句后面作为表。 

select 列名 from (子查询语句) 别名 where 条件;

子查询返回多列多行的值时一般与内连接查询和外连接查询一起使用。

和内连接查询一起使用： 

select 列名 from 表名 别名 inner join (子查询语句) 别名 on 关联条件 [where 条件];

和左外连接查询一起使用： 

select 列名 from 表名 别名 left join (子查询语句) 别名 on 关联条件 [where 条件];

和右外连接查询一起使用： 

select 列名 from 表名 别名 right join (子查询语句) 别名 on 关联条件 [where 条件];

其中：关联条件就是

主表的主键(dept.id)等于从表的外键(emp.dept_id)

。

案例： 

-- 子查询返回多列多行的值
select * from oa_emp;
-- 查询2017年以后入职的员工信息，包含部门名称
select * from oa_emp emp where emp.hire_date >'2017-01-01'; -- 查询2017年以后入职的员工信息
select dept.name,emp.name,emp.gender,emp.salary,emp.hire_date from oa_dept dept inner join (select * from oa_emp emp where emp.hire_date >'2017-01-01') emp on dept.id = emp.dept_id;

查询结果：

from 表名 别名 right join (子查询语句) 别名 on 关联条件 [where 条件]; 


其中：关联条件就是**主表的主键(dept.id)等于从表的外键(emp.dept_id)**。



案例：

```sql
-- 子查询返回多列多行的值
select * from oa_emp;
-- 查询2017年以后入职的员工信息，包含部门名称
select * from oa_emp emp where emp.hire_date >'2017-01-01'; -- 查询2017年以后入职的员工信息
select dept.name,emp.name,emp.gender,emp.salary,emp.hire_date from oa_dept dept inner join (select * from oa_emp emp where emp.hire_date >'2017-01-01') emp on dept.id = emp.dept_id;

查询结果：

子查询返回多列多行的值


版权声明：本文为arckcck原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: