投稿
  1. 极客之音首页
  2. 技术随笔

零基础学JavaWeb开发（四）之 多表关系

Java光头强 • • 技术随笔 • 阅读 217

导读：本篇文章讲解 零基础学JavaWeb开发（四）之 多表关系，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

六、多表关系

实际的项目开发中，一个项目通常需要很多张表才能够完成，例如在学生系统中 有学生班级表(stu_class)、学生信息表(stu_info)等多张表。这些表存在一定的关系。

会员信息–会员信息表

课程信息—课程详细内容

例如

1.一对一（使用的比较少）

2.一对多

3.多对多

一对一

1.一个学生只有一张身份证，一个身份证只能够对应一个学生

2.一般一对一关系使用的比较少

一对多

例如 学生班级表(stu_class)、学生信息表(stu_info)等多张表

1.一个学生只要一个班级，学生信息表与班级表一一对应；

2.在表中添加一个外键，指向另一方主键，确保一对一关系;

零基础学JavaWeb开发（四）之 多表关系

部门与员工

例如：一个部门有多个员工，一个员工只能够对应一个部门

零基础学JavaWeb开发（四）之 多表关系

多对多

学生和课程

1.一个学生可以选择多门课程学习 ，一个课程也可以被很多学生选择

原则：多对多关系实现需要借助第三章中间表实现，中间表至少需要包含两个字段，

将多对多的关系拆分一对多的关系。

零基础学JavaWeb开发（四）之 多表关系

外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

零基础学JavaWeb开发（四）之 多表关系

注意事项：主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

1.主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

2.必须在主表定义主键。

3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中；

4.外键中列的数据类型必须和主表主键中对应列的数据类型相同。

创建主外键

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

例如：

CREATE TABLE `mayikt_class` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id) -- 外键约束
); 
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

[CONSTRAINT <mayikt_class_id>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id)

删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
ALTER TABLE mayikt_student DROP FOREIGN KEY mayikt_class_id;

验证：外键约束

1.先向主表新增数据，在向从表新增数据

2.外键列的值必须要在主表存在 或者是为空

3.主表的数据不能够随便删除，从表数据可以随便删除 或者先

删除从表所有数据没有任何关联主表的字段 在可以删除主表的对应的数据

联表查询

多表查询是指多张表联合一起查询，例如学生信息与学生班级表、部门与员工表。

1.交叉连接查询 (笛卡尔积)

2.内连接查询

3.外连接查询

4.子查询

5.表自关联

数据的准备：

CREATE TABLE `mayikt_class` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id) -- 外键约束
); 


INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('1', '第一期');
INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('2', '第二期');

INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('1', 'mayikt', '18', '武汉市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('2', 'meite', '23', '上海市', '2');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('3', '李思', '12', '孝感市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('4', '刘流', '27', '武汉市', '1');

交叉连接查询 (笛卡尔积)

语法：select  * from mayikt_class,mayikt_student

得到的查询结果是两张表的笛卡尔积，也就是用A表中的每条数据都去匹配B表中的所有数据，获得的结果往往不是我们需要的，一般很少使用交叉连接,缺点数据比较冗余。

内连接

显示内连接:

SELECT * FROM A INNER JOIN B ON 条件;

隐示内连接:

SELECT * FROM A,B WHERE 条件;

案例

-- 1.查询每个班级下所有学生信息
-- 显示内连接
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id;
-- 隐士内连接
SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id=
mayikt_class.id


-- 2.需求查询第一期所有学生

SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id=
mayikt_class.id  and mayikt_class.id='1'

select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and mayikt_class.id='1'

-- 3.查询第一期和第二期所有的学生
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and (mayikt_class.id='1'
or  mayikt_class.id='2')
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and mayikt_student.class_id  in 
(1,2)

-- 4.查询每个班级下的学生总数 并且学生总数升序排列
select mayikt_class.`name` , count(*) from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id  GROUP BY  mayikt_class.id 
order by count(*) asc  
-- 5.查询班级总人数>2的班级，并且人数降序排列
select mayikt_class.`name` , count(*) from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id  GROUP BY  mayikt_class.id  HAVING
count(*)>2
order by count(*) asc

2.需求查询第一期所有学生

3.查询第一期和第二期所有的学生

4.查询每个班级下的学生总数 并且学生总数升序排列

5.查询班级总人数>2的班级，并且人数降序排列

外连接

外连接:左外连接、右外连接、全外连接（union）。

1.左外链接（left outer join，outer可以省略）

语法：SELECT * FROM A LEFT OUTER JOIN B ON 条件;

左外连接获得的查询结果是左边的表A的全部信息和A，B两张表的交集，左边A表的全部包含A表中在B表中没有对应关系的信息

2.右外连接（right outer join，outer可以省略）

语法：SELECT * FROM A RIGHT OUTER JOIN B ON 条件;

总结：右外连接获得的查询结果是右边的表B的全部信息和A，B两张表的交集，右边B表的全部包含B表中在A表中没有对应关系的信息

3.全外连接

select * from mayikt_class left join mayikt_student
on mayikt_class.id=mayikt_student.class_id
union
select * from mayikt_class right join mayikt_student
on mayikt_class.id=mayikt_student.class_id;

左连接 以左边为准 左变有该数据 就会返回 右变没有匹配上则直接返回为null

右连接 以右边为准 右变有该数据 就会返回 左变没有匹配上则直接返回为null

内连接左边与右边都是必须匹配才会返回

需求:

1.查询哪些班级是有学生 哪些班级是没有学生
select * from mayikt_class left join mayikt_student
on mayikt_class.id=mayikt_student.class_id;
2.查询哪些学生是有班级，哪些学生是没有班级
select * from mayikt_class right join mayikt_student
on mayikt_class.id=mayikt_student.class_id;
3.使用union关键字实现左连接和右连接的并集 让去重复数据

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

子查询

基本子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

通俗易懂也就是SELECT 嵌套的查询

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

需求：

1.查询年龄最大的学生信息，显示信息包含 学生的id、学生的名称、学生的年龄

select * from mayikt_student order by age desc limit 1

子查询方式：


select * from mayikt_student 
where age in (select max(age) from mayikt_student)

2.查询第一期和第三期学生信息

方式1 关联查询：

select * from mayikt_student join mayikt_class
on mayikt_student.class_id=mayikt_class.id where class_id='1' 
or class_id='3'

方式2 子查询：

select * from mayikt_student where class_id in (select id from mayikt_class where id='1' or id='3'
)

3.查询第一期学生年龄大于18岁 学生信息

方式1 关联查询1：

select * from mayikt_student  join mayikt_class
on mayikt_student.class_id=mayikt_class.id
and mayikt_student.class_id='1'
and mayikt_student.age>18;

方式2 子查询：

select * from mayikt_student where age >18 and class_id in (
select id from mayikt_class where id='1')

方式3 关联查询2：

select * from (select * from mayikt_class where id='1') a1 join 
(select * from mayikt_student where age >18) a2
on a1.id=a2.class_id;

子查询关键字之all

all关键字用在比较操作操符的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足

比较操作符：= 、>、!=、>=、<=等

select ... from mayikt_user（表的名称） where age（字段）  > all(查询语句)
相当于：
select ... from mayikt_user（表的名称） where age> result1 and age >result2

需求1：查询年龄大于第一期所有年龄的学生信息

select * from mayikt_student where age> all(
select age from mayikt_student where class_id='1'
)

需求2：查询没有班级的学生信息

select * from mayikt_student
where class_id!= all(
select id from mayikt_class 
)

子查询关键字之any

select ... from mayikt_user（表的名称） where age（字段）  > any(查询语句)
相当于：
select ... from mayikt_user（表的名称） where age> result1 or age >result2

需求：查询学生年龄大于第一期任意一个学生年龄的信息

select * from mayikt_student where age> any(
select age from mayikt_student where class_id='1'

)
and class_id!=1;

all 底层：多个 and 比较

any 底层：多个 or 或者比较

子查询关键字之not in和in

语法格式：

select * from mayikt_student where 字段  in（查询语句）
select * from mayikt_student where 字段 =result1  or   字段 =result2

特点：

in关键字 用于判断某个记录的值 是否在指定的集合中

使用 not in 可以实现取反

需求：

查询第一期和第三期所有学生信息

select * from mayikt_student where class_id
 in (select id from mayikt_class where name='第一期' 
 or name='第二期' 
)

in 子查询语句中 等于 or 连接比较

SELECT * from mayikt_student

where class_id=1 or class_id =3

all 子查询语句中 and 比较符

any 子查询语句中 or 比较符

子查询关键字之exists

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS比in关键字运算效率高，实际开发中 如果是数据量大的情况下 推荐使用EXISTS关键字。

语法：

select * from mayikt_student where EXISTS
(查询语句---查询到结果 就返回true  没有查询到结果 就返回 false)

需求1:

1.查询学生年龄大于18岁 学生信息

select * from mayikt_student s1
where EXISTS(select * from mayikt_student s2
             where  s1.age >18
 )

2.查询班级下 有学生的班级

select * from mayikt_class a
 where  EXISTS(
select * from mayikt_student  b where b.class_id=a.id
)

3.查询有班级的学生信息

select * from mayikt_student
s1 where EXISTS(
select * from mayikt_class b where s1.class_id =b.id
)

子查询之自关联查询

对mysql数据自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。

注意：关联查询时必须给表取别名

select 字段列表 from 表名称 a ,表 名称b where 条件;
或者
select 字段列表 from 表名称 a left join 表名称 b on 条件;

以京东电商为例子：京东(JD.COM)-正品低价、品质保障、配送及时、轻松购物！

零基础学JavaWeb开发（四）之 多表关系

表结构

CREATE TABLE `commodity_type` (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品类型名称',
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


select * from   commodity_type as a ,commodity_type
as b where a.id=b.parent_id;

多表综合练习1

1.创建表结构

CREATE TABLE `mayikt_dept` (
  `dept_no` int NOT NULL COMMENT '部门id',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `dept_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `mayikt_emp` (
  `emp_number` int DEFAULT NULL COMMENT '员工编号',
  `emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
  `emp_post` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工职务',
  `emp_leader_number` int DEFAULT NULL COMMENT '员工领导编号',
  `emp_hiredate` datetime DEFAULT NULL COMMENT '员工入职时间',
  `emp_salary` double(10,0) DEFAULT NULL COMMENT '员工薪水',
  `emp_bonus` int DEFAULT NULL COMMENT '员工奖金',
  `emp_deptno` int DEFAULT NULL COMMENT '员工对外部门表外键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('1', '技术部门', '湖北武汉市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('2', '财务部门', '中国上海市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('3', '设计部门', '湖北孝感市');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1001', '张三', 'CEO', NULL, '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1002', '小薇', 'CFO', '1001', '2021-10-01 11:32:46', '5000', '10000', '2');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1004', '张三', 'CTO', NULL, '2021-11-01 11:32:46', '80000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1005', '李四', '技术总监', '1004', '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1006', '王麻子', '客服', NULL, '2022-03-02 11:49:45', '3500', NULL, NULL);

1.返回员工拥有部门的 员工信息含员工部门
select * from mayikt_emp as a join mayikt_dept as b
on  a.emp_deptno=b.dept_no;
2.查询员工薪资大于小薇的 员工信息
select * from mayikt_emp where emp_salary>(
select emp_salary from mayikt_emp where emp_number='1002'
)
3.返回员工所属领导信息
select * from mayikt_emp as a, mayikt_emp as b
where a.emp_number=b.emp_leader_number

4.返回入职时间早于领导 入职时间
select * from mayikt_emp as a, mayikt_emp as b
where a.emp_number=b.emp_leader_number
and a.emp_hiredate>b.emp_hiredate
5.返回从事财务工作的员工信息
select * from mayikt_emp as a join mayikt_dept as b
on  a.emp_deptno=b.dept_no
and b.dept_name='财务部门'
6.求每个部门 最低员工薪资
select  emp_deptno,min(emp_salary)
 from mayikt_emp GROUP BY emp_deptno 
7.返回员工薪资大于 平均薪资员工
select  *
 from mayikt_emp where emp_salary >(
select  avg(emp_salary)
 from mayikt_emp
)

多表综合练习2

单独创建员工等级表

CREATE TABLE `mayikt_salgrade` (
  `grade` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工等级',
  `losal` double(255,0) DEFAULT NULL COMMENT '最低工资',
  `hisal` double DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
1.查询工资大于财务部所有员工的 信息
select * from  mayikt_emp where emp_salary > all(
select emp_salary
 from  mayikt_emp where emp_salary and  emp_deptno='2'
)

2.求从事相同职务工作最低工资和最高工资
select emp_post,min(emp_salary) as 最低工资,max(emp_salary)  as 最高工资
 from  mayikt_emp  GROUP BY emp_post
3.计算每位员工的年薪，并且以年薪降序排列
select ((emp_salary *12)+emp_bonus) as 年薪
 from  mayikt_emp  ORDER BY 年薪 desc 
 4.返回工资处于P4级别员工的信息
 select * from  mayikt_emp where emp_salary BETWEEN (
  select losal
  from  mayikt_salgrade where grade='P4'
  ) and (
  select hisal
  from  mayikt_salgrade where grade='P4'
)
 5.返回工资处于P1员工信息含部门名称
 select * from  mayikt_emp as e join 
mayikt_dept as d on e.emp_deptno=d.dept_no
join mayikt_salgrade s 
on s.grade='p1'  and e.emp_salary>=s.losal and e.emp_salary
<=s.hisal

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/111840.html

赞 (0)
Java光头强的头像Java光头强
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！