数据库设计与多表操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/Cream336/article/details/131175800

多表操作

表：班级表和学生表；电商项目：订单表–>商品表

现实生活中，（班级）实体与（学生）实体之间肯定是有关系的，那么我们在设计表的时候，就应该体现出（班级）表与（学生）表之间的这种关系！

简称：关系型数据库（Relation DBMS）

常见的表关系

一对一
社保基金（用户、基金账户）

公民和身份证号、公司和注册地、老公与老婆的关系（合法夫妻）

一个公民只能有一个身份证号，一个身份证号对应一个公民
一对多（多对一）

班级和学生、部门和员工父亲–儿子角色->权限

一个班级下面有多名同学，多名同学属于某一个班级

多对多
角色->权限医疗项目（医生、患者）客户< ->基金

老师和学生、学生和课程

一名老师可以教导多名学生，一名学生可以被多个老师教导

一对一

一对一关系在实际开发中用的并不多，因为可以把关联字段设计在同一张表…

约束：行约束、列约束、表间约束（外键约束）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aUKfOiWS-1686570491994)(images/image-20210104152707808.png)]

一对多(多对一)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S3dAbH6b-1686570491995)(images/image-20210104153614386.png)]

多对多

代码：作业；

A B 是 m VS n

思路：借助于第三张表；中间表；

学生_课程表

（ sid int,

cid int,

//设置联合主键；他们两个联合起来的时候，称为一个主键01，主键的特征：唯一且不可为null。

Primary key(sid,cid),

foreign key(sid) references 学生表（sid),

foreign key(cid)references 课程表(cid)

)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E54d7exm-1686570491995)(images/image-20210104154611466.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZASJCUnt-1686570491995)(文档3-mysql笔记.assets/image-20230523093153255.png)]

外键约束

限定二张表有关系的数据，保证数据的正确性、有效性和完整性

语法

create table 表名(
  	列名 数据类型 约束名,
    列名 数据类型 约束名,
  	[constraint] [约束名] foreign key(外键列) references 主表(主键)
);

已有表添加外键

alter table 表名 add [constraint] [约束名] foreign key(外键列) references 主表(主键);

删除外键约束

alter table 表名 drop foreign key 约束名;

案例演示

-- 创建部门表
-- 一方，主表
CREATE TABLE dept(
	d_id INT PRIMARY KEY AUTO_INCREMENT,
	d_name VARCHAR(20),
	d_location VARCHAR(20)
);
-- 创建员工表
-- 多方，从表
CREATE TABLE emp(
	e_id INT PRIMARY KEY AUTO_INCREMENT,
	e_name VARCHAR(20),
	e_age INT,
	d_id INT, -- 外键对应主表的主键
	-- CONSTRAINT emp_ibfk_1  可以省略不写
	-- CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `dept` (`d_id`)
    FOREIGN KEY (`d_id`) REFERENCES `dept` (`d_id`)
);


-- 添加 2 个部门
INSERT INTO dept VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM dept;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (e_name, e_age, d_id) VALUES ('张三', 20, 1);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('李四', 21, 1);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('王五', 20, 1);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('老王', 20, 2);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('大王', 22, 2);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('小王', 18, 2);
SELECT * FROM emp;

笛卡尔积

a表中的每一条记录，都和b表中的每一条进行匹配连接。会造在数据量级的增长

A{a,b} B{c,d,e} ,两个集合进行乘积，则A集合元素与B集合的每个元素进行匹配，最终结果A*B 6

a c / a d /a e

b c /b d /b e

SELECT * FROM emp ,dept;

如果要得到正确的数据,需要添加过滤条件
select * from a,b where a.a_id = b.a_id;

-- 部门中有哪些员工，要求结果显示在一块
select * from emp , dept ;
-- 笛卡尔积现像，每一个员工都会与每一个部门进行匹配
-- 笛卡尔积现像会带来什么问题呢？ 
   数据的冗余 100w员工  100w部门 亿万级别的数据
-- 为什么会出现这种问题呢？ 因为条件不足

-- 解决笛卡尔积现像，给足条件
SELECT
	e.e_name '员工姓名',
	d.d_id '部门编号',
	d.d_name '部门名称'
FROM
	emp e,
	dept d 
WHERE
	e.d_id = d.d_id;

注意事项

1） 主表不能删除从表已引用的数据
2） 从表不能添加主表未拥有的数据
3） 先添加主表数据再添加从表数据
4） 先删除从表数据再删除主表数据
5） 外键约束允许为空但不能是错的

== 主外键关系: 注意事项 ==
-- 前者：建立【实际的主外键关系】 和 后者：建立【逻辑主外键关系】 的区别？
-- 前者有约束，必须按照外键约束来写
-- 后者没有外键约束，但你不能写主键不存在
-- 前者删除受主外键约束，后者删除不再受主外键约束
-- 前者可能会造成级联删除， 后者不会造成级联删除

> 在实际开发中，一般不建立实际的外键关系，建立逻辑外键关系
> 在互联网公司中，不建立实际的外键关系，在实际传统行业中，需要建立实际的外键关系

多表关系案例

多表设计

设计学生成绩管理系统数据表

-- 3课程表
create table course(
	c_id int primary key auto_increment,
	c_name varchar(20),
     t_id int,  
    foreign key(t_id) references teacher (t_id)
);

-- 1学生表
create table student(
	s_id int primary key auto_increment,
	s_name varchar(20),
    s_city varchar(20),
    s_age int
);

-- 2老师表
create table teacher( 
	t_id int primary key auto_increment,
	t_name varchar(20)
);

-- 4学生课程表
create table stu_course(
	id int primary key auto_increment,
	score double,
	s_id int,
	c_id int,
	foreign key(s_id) references student(s_id),
	foreign key(c_id) references course(c_id)
);

添加数据

-- teacher
INSERT INTO `teacher`(`t_id`, `t_name`) VALUES (1, '张老师');
INSERT INTO `teacher`(`t_id`, `t_name`) VALUES (2, '王老师');
INSERT INTO `teacher`(`t_id`, `t_name`) VALUES (3, '李老师');
INSERT INTO `teacher`(`t_id`, `t_name`) VALUES (4, '江老师');

-- student
INSERT INTO `student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (1, 'jack', '上海', 88);
INSERT INTO `student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (2, 'rose', '上海', 99);
INSERT INTO `student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (3, 'tom', '郑州', 66);
INSERT INTO `student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (4, 'yiyan', '北京', 27);

-- course
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (1, '语文', 1);
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (2, '数学', 1);
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (3, '物理', 2);
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (4, '生物', 2);
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (5, '英语', 3);
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (6, '前端', NULL);

-- stu_course
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (1, 89, 1, 1);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (2, 90, 1, 2);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (3, 78, 2, 1);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (4, 88, 3, 4);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (5, 99, 4, 3);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (6, 67, 3, 3);
INSERT INTO `stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (7, 100, 4, 4);

总结：

一个老师教多门课一对多关系

一个学生可以选多门课，一门课也可以被多个学生选，多对多关系

查询练习

-- 老师教了什么课
SELECT
	t.t_id,
  t.t_name,
	c.c_name
FROM
	course c,
	teacher t
WHERE 
  c.t_id = t.t_id

-- 学生选了什么课，每一课得了多少分
SELECT
	s.s_name,
	c.c_name,
	sc.score
FROM
	student s,
	course c,
	stu_course sc 
WHERE
	sc.s_id = s.s_id 
	AND sc.c_id = c.c_id

-- 学生选了什么课，每一课得了多少分，对应的代课老师是谁
SELECT
	s.s_name,
	c.c_name,
	sc.score,
	t.t_name
FROM
	student s,
	course c,
	stu_course sc ,
	teacher t
WHERE
	sc.s_id = s.s_id 
	AND sc.c_id = c.c_id
	AND c.t_id = t.t_id

多表查询（*）

内链接查询

必须表与表之间的关系一一对应

隐式内连接查询：

select * from a,b where a.a_id = b.a_id;  

没有INNER JOIN，形成的中间表为两个表的笛卡尔积，需要使用条件保留有意义的数据

-- 查询老师对应的课程
select * from teacher , course where teacher.t_id = course.t_id;

显式内连接查询

select * from a inner join b on a.a_id = b.a_id

显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积

-- 查询老师对应的课程
select * from teacher inner join course on teacher.t_id = course.t_id;

外链接

左外连接

写法：select * from a left [outer] join b on a.a_id = b.b_id

把left 关键字之前的表，是定义为左侧。 left关键字之后的表，定义右侧。

当两个表连接的时候，如果左侧表有数据，右测的表没有对应的数据。会把左侧的表中的数据显示出来。

如何去区分左右： left 关键字之前的 ，左侧。 left 关键字后面的表，右侧。

-- 需求：查询老师的授课情况，如果某个老师有授课，把老师和课程信息都显示出来。如果某个老师没有授课。则把老师的信息显示出来。
-- 要显示老师的信息。 把老师作为左侧

select * from teacher left join course on teacher.t_id = course.t_id;

右外连接

写法：select * from a  right [outer] join b on a.a_id = b.b_id

如果右侧有数据，左侧没匹配到，把右侧的数据显示出来。

right之前的是左侧，right之后的是右侧。

-- 需求：查询所有课程信息，以及某个课程的教师信息。 如果没有对应的教师信息，也要把课程的信息显示出来。

select * from teacher right join course on teacher.t_id = course.t_id;

全外连接

全外连接
会把两侧的内容都显示出来

需求：查询教师授课情况。 列出教师的信息，课程的信息。如果教师教授课程，把教师和课程都显示出来。
如果教师没有授课，则把教师的信息显示出来。
如果某一门课程没有任课教师。 也需要把课程的信息显示出来。
写法： select * from a full join b on a.a_id = b.a_id 
mysql 不支持 full 关键字 全外连接。

union 是把两个查询的结果进行合并。 去掉重复的合并

select * from teacher left join course on teacher.t_id = course.t_id 
union
select * from teacher right join course on teacher.t_id = course.t_id;

union all 把两个结果集都显示出来，不会合并去重复

select * from teacher left join course on teacher.t_id = course.t_id 
union all
select * from teacher right join course on teacher.t_id = course.t_id;

关联子查询（*****）

关联子查询：将一个查询结果当作另一个查询的一/条件部分；多个sql语句的嵌套

数据准备

-- 创建部门表（主表）
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');

-- 创建员工表（从表）
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

sql演示

语法

-- 【1】查询结果单值
SELECT MAX(salary) FROM emp;

-- 【2】查询结果单列多行  in（）
SELECT salary FROM emp;

-- 【3】查询结果多行多列
SELECT * FROM emp;

子查询结果为单行单列

-- 【1】查询工资最高的员工是谁？ 
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

-- 【2】查询工资小于平均工资的员工有哪些？
-- 2.1 先求出平均工资
SELECT AVG(salary) FROM emp;
-- 2.2 查询低于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

子查询结果为单列多行

-- 【1】查询工资大于5000的员工，来自于哪些部门的名字  
-- 1.1 查询工资大于5000的员工
SELECT dept_id FROM emp WHERE salary >5000;
-- 1.2 来自于哪些部门的名字  
-- 错误 SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
Select  emp2.id,emp2.name,d.name
from (Select * from emp where salary>5000)emp2 Left JOIN dept d
     On emp2.dept_id=d.id

-- 【2】查询开发部与财务部所有的员工信息
-- 2.1 根据部门名称，查询部门主键
SELECT id FROM dept WHERE `name` IN('开发部','财务部');
-- 2.2 根据部门id查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));

子查询结果为多列多行

-- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';

-- 1.2 根据1步骤的结果，跟部门表关联
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

多表查询综合案例

数据准备

-- 多表案例
create database db3_pro;
-- 切换数据库
use db3_pro;
-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY auto_increment, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20), -- 职务名称
  description VARCHAR(50) -- 职务描述
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id  外键
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资 99999.99
  bonus DECIMAL(7,2), -- 奖金 99999.99
  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)
);

-- 添加员工
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);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,  -- 等级
  losalary INT, -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

表之间的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LMKoyZ8B-1686570491995)(images/image-20210109230301998.png)]

案例一

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

-- 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
-- 有几张表？ emp, job     表与表之间的关系？ emp.job_id = job.id;
SELECT
 e.id '员工编号',
 e.ename '员工姓名',
 e.salary '工资',
 j.jname '职务名称',
 j.description '职务描述'
FROM
 emp e,
 job j 
WHERE
 e.job_id = j.id;

案例二

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置


-- 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 有几张表？ emp, job , dept   
-- 表与表之间的关系？ emp.job_id = job.id  and emp.dept_id = dept.id;
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;

案例三

查询员工姓名，工资，工资等级

/*
    1.员工姓名，工资 emp  工资等级 salarygrade
    2.条件 emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
          emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/

SELECT
	e.ename,
	e.salary,
	s.grade 
FROM
	emp e,
	salarygrade s 
WHERE
    e.salary BETWEEN  s.losalary and s.hisalary;

案例四

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 有几张表？ emp , job , dept , salarygrade
-- 表与表之间的关系？ 
-- emp.job_id = job.id and emp.dept_id = dept.id  and e.salary BETWEEN  s.losalary and s.hisalary
 
SELECT
	e.ename,
	e.salary,
	j.jname,
	j.description,
	d.dname,
	d.loc,
	s.grade 
FROM
	emp e,
	job j,
	dept d,
	salarygrade s 
WHERE
	e.job_id = j.id and 
	e.dept_id = d.id and 
	e.salary BETWEEN  s.losalary and s.hisalary;

案例五

查询出部门编号、部门名称、部门位置、部门人数

-- 查询出部门编号、部门名称、部门位置、各个部门人数
-- 用到的表？ dept emp
-- 子查询  select dept_id, count(dept_id) from emp  group by dept_id;
SELECT
	d1.id,
	d1.dname,
	d1.loc,
	d2.total
FROM
	dept d1,
	( SELECT dept_id, count( dept_id ) as total FROM emp GROUP BY dept_id ) d2 
WHERE
	d1.id = d2.dept_id;

案例六

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NYIGBHzo-1686570491996)(images/image-20210109231707532.png)]

查询每个员工的名称及其上级领导的名称（自关联）

/*
	1.姓名 emp， 直接上级的姓名 emp
	 emp表的id 和 mgr 是自关联
    2.条件 emp.id = emp.mgr
    3.查询左表的所有数据，和 交集数据
         使用左外连接查询
*/
SELECT
	e1.ename,
	e2.mgr,
	e2.ename 
FROM
	emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.id;

总结

我们在企业开发时，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。其实不管是几张表的查询，都是有规律可循的。
y BETWEEN s.losalary and s.hisalary;




## 案例五

> ==查询出部门编号、部门名称、部门位置、部门人数==

```sql
-- 查询出部门编号、部门名称、部门位置、各个部门人数
-- 用到的表？ dept emp
-- 子查询  select dept_id, count(dept_id) from emp  group by dept_id;
SELECT
	d1.id,
	d1.dname,
	d1.loc,
	d2.total
FROM
	dept d1,
	( SELECT dept_id, count( dept_id ) as total FROM emp GROUP BY dept_id ) d2 
WHERE
	d1.id = d2.dept_id;

案例六

[外链图片转存中…(img-NYIGBHzo-1686570491996)]

查询每个员工的名称及其上级领导的名称（自关联）

/*
	1.姓名 emp， 直接上级的姓名 emp
	 emp表的id 和 mgr 是自关联
    2.条件 emp.id = emp.mgr
    3.查询左表的所有数据，和 交集数据
         使用左外连接查询
*/
SELECT
	e1.ename,
	e2.mgr,
	e2.ename 
FROM
	emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.id;