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

出世&入世

已于 2023-01-15 17:20:30 修改

阅读量375

点赞数

分类专栏： JavaWeb Java 文章标签：数据库 mysql sql 学习 java

于 2023-01-14 18:45:00 首次发布

本文链接：https://blog.csdn.net/weixin_54828627/article/details/128684732

版权

Java 同时被 2 个专栏收录

100 篇文章 6 订阅

订阅专栏

JavaWeb

29 篇文章 2 订阅

订阅专栏

六、多表关系

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

会员信息--会员信息表

课程信息---课程详细内容

例如

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

2.一对多

3.多对多

一对一

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

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

一对多

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

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

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

部门与员工

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

多对多

学生和课程

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

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

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

外键约束

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

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

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

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

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)-正品低价、品质保障、配送及时、轻松购物！

表结构

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

出世&入世

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
零基础学JavaWeb开发（四）之多表关系

对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。得到的查询结果是两张表的笛卡尔积，也就是用A表中的每条数据都去匹配B表中的所有数据，获得的结果往往不是我们需要的，一般很少使用交叉连接,缺点数据比较冗余。总结：右外连接获得的查询结果是右边的表B的全部信息和A，B两张表的交集，右边B表的全部包含B表中在A表中没有对应关系的信息。左外连接获得的查询结果是左边的表A的全部信息和A，B两张表的交集，左边A表的全部包含A表中在B表中没有对应关系的信息。
复制链接

扫一扫