数据库多表关系

今天整理的什么勾八东西一团浆糊学废了

为什么需要多表数据存储

如果所有关联数据都存在一个表中,数据会产生大量冗余
拆表存储以后,使用外键值替代引用数据,防止数据冗余
拆表以后,产生多表查询,关联的数据表一定存在主外键关系!

表关系理解

关系型数据库数据是按照类别进行存储,类别 - >表
表之间存在关系【主外键】
表关系具体：表关系并不是表容器的关系,表关系的具体体现是数据的关系!
表关系种类
- 一对一
- 一对多
- 多对一
- 多对多
表关系:要双方对比,最后总结结果

表关系种类【主外键关系】

一对一
一对多
多对多

表关系实现(表设计者的角度)

一对一实现

CREATE DATABASE day04;
USE day04;

# 学生基本信息表
CREATE TABLE base_info(
   bid INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生基本信息主键',
   NAME VARCHAR(20) NOT NULL COMMENT '学生名'
)
# 方案1： 外键添加唯一约束即可 1：1
CREATE TABLE detail_info(
   did INT PRIMARY KEY AUTO_INCREMENT COMMENT '详情表自己的主键',
   address VARCHAR(200) NOT NULL COMMENT '详情地址',
   bid INT UNIQUE COMMENT '学生的基本信息外键',
   CONSTRAINT b_d_fk FOREIGN KEY (bid) REFERENCES base_info(bid)
)

#方案2： 外键+主键
CREATE TABLE detail_info1(
   address VARCHAR(200) NOT NULL COMMENT '详情地址',
   bid INT  PRIMARY KEY COMMENT '学生的基本信息外键,也是主键',
   CONSTRAINT b_de_fk FOREIGN KEY (bid) REFERENCES base_info(bid)
)

一对多实现

CREATE TABLE student(
  sid INT PRIMARY KEY AUTO_INCREMENT COMMENT "学生主键",
  sname VARCHAR (20) NOT NULL
)


CREATE TABLE score(
  cid INT PRIMARY KEY AUTO_INCREMENT  COMMENT "分数主键",
  score INT DEFAULT 0 COMMENT '分数',
  sid INT COMMENT '学生表外键',
  CONSTRAINT s_s_fk FOREIGN KEY (sid) REFERENCES student (sid)
)

多对多实现

SQL DQL语句(多表查询)

多表查询介绍和理解

多表查询：就是将多个表的结果集合并到一起返回就是多表查询!
多表查询方式：垂直结果拼接到一起,水平结果拼接查询
垂直拼接：将两个结果集垂直方向拼接到一起,行和行数据之间没有任何的关联
垂直拼接：两个结果集的查询的列数要一致,对应的类型一致即可!!不要求两个表有主外键关系
水平拼接：将两个结果集水平拼接到一起,行和行数据之间要联动连接
水平拼接：将两个结果集的不同行，拼接成一个新的行，要求两个表必须存在关系【有主外键】

垂直多表查询(合并结果集)

语法

#结果集1 UNION 结果集2;  UNION会去掉重复数据
#结果集1 UNION ALL 结果集2;  UNION ALL 不会去掉重复数据

注意

要合并的结果集列数必须一样
对应的列类型必须一致

表1

表1列名1	表1列名2
INT类型	VARCHAR类型

表2

表2列名1	表2列名2
INT类型	VARCHAR类型

代码实现

# 垂直合并结果集

# 数据准备
-- 创建表a 表b
CREATE TABLE a(
   aid INT,
   aname VARCHAR(10)
);

CREATE TABLE b(
   bid INT,
   bname VARCHAR(10)
);

-- 表中插入数据
INSERT INTO a VALUES(1,'aaaa'),(2,'bbbb'),(3,'cccc');
INSERT INTO b VALUES(4,'aaaa'),(2,'bbbb'),(3,'cccc'); 
#语法1
SELECT * FROM a
  UNION # 会去掉重复数据！ 所有列都重复，就是重复数据！
SELECT * FROM b
  UNION
SELECT * FROM b;  
#语法2
SELECT * FROM a
  UNION ALL # 不会去掉重复数据！ 所有列都重复，就是重复数据！
SELECT * FROM b
  UNION ALL
SELECT * FROM b;

水平多表查询（水平连接查询）【重点】

水平查询明确 【连接查询为重点，99大法为辅助理解，自然连接是鸡肋，自连接是特殊方式】

将多个结果集水平拼接，行和行要融合
两个表之间必须存在关系【主外键】

99查询大法【方言】

实现思路

就是模仿99乘法表
将两个表的数据全部水平拼接一遍,一定包含所有正常的组合
也会包含错误的组合
还需要自己使用WHERE进行条件筛选【有主外键】
其实就是内连接的另外写法

实现语法

select * from 表1，表2，表3...表n where 主键 = 外键 and 主键 = 外键...
如果有n张表，就有n-1对主外键条件！

实现代码

准备数据

# 准备数据
 -- 学生表
CREATE TABLE student(
	stuid VARCHAR(10)PRIMARY KEY,
	stuname VARCHAR(50)
);
-- 添加数据
SELECT * FROM student;
INSERT INTO student VALUES('1001','张三峰');
INSERT INTO student VALUES('1002','张无忌');
INSERT INTO student VALUES('1003','王尼玛');
INSERT INTO student VALUES('1004','王老五');


-- 分数表  多表查询的数据
CREATE TABLE score(
	stuid VARCHAR(10),
	score INT,
	courseid INT,
	CONSTRAINT fk_stu_sco  FOREIGN KEY(stuid) REFERENCES student(stuid)
);
-- 添加数据
SELECT * FROM score;
INSERT INTO score VALUES('1001',98,1);
INSERT INTO score VALUES('1002',95,1);
INSERT INTO score VALUES('1002',67,2);
INSERT INTO score VALUES('1003',83,2);
INSERT INTO score VALUES('1003',57,3);


# 课程表
CREATE TABLE course(
   cid INT PRIMARY KEY,
   cname VARCHAR(20) COMMENT '课程名称',
   tid  INT COMMENT '授课老师编号'
);
-- 添加数据
INSERT INTO course(cid,cname,tid) 
VALUES (1,'java',1),(2,'php',2),(3,'h5',3);

# 教师表
CREATE TABLE teacher(
   tid INT PRIMARY KEY COMMENT '讲师编号',
   tname VARCHAR(20) COMMENT '讲师名称'
);
-- 添加数据
INSERT INTO teacher(tid,tname) 
VALUES (1,'迪迦'),(2,'戴拿'),(3,'盖亚');

查询

# 查询学生姓名和分数以及对应的学科 
# 全部数据查询
SELECT * FROM student AS s,score c ,course u;
 
# 数据赛选
	 
SELECT s.stuname,c.score,u.cname 
FROM student AS s,score c ,course u
WHERE s.stuid = c.stuid 
AND c.courseid = u.cid ;
        
 # 查询学生姓名和分数以及课程已经对应的讲师姓名
 # 四张表！ 水平拼接
 # 1.全部查询
 SELECT * FROM student s,score c,course u,teacher t;
 # 2.数据赛选
 SELECT s.stuname,c.score,u.cname,t.tname 
 FROM student s,score c,course u,teacher t
 WHERE s.stuid = c.stuid 
 AND c.courseid = u.cid 
 AND u.tid = t.tid

连接查询【普通话】

内连接

语法

#a. select  列  from 表1 [ inner] join 表2  on 主= 外  [inner] join 表3  on  主 = 外  where  group by ...
#b. 规律：连接查询，就是两张表连接一次，on一次主外键，再连接第三张表，以此类推即可！！！

代码

# 2.1 内连接 
# select * from 表1 别名 [inner] join 表2 别名 on 主 = 外 where group by  having order by limit 

# 使用内连接查询下你学生和学生分数
# 将两个表连接到一起，也有笛卡尔积  20条数据！
# from s,c
SELECT * FROM student s  INNER JOIN score c

# on 只写主外键！
SELECT * FROM student s  INNER JOIN score c ON s.stuid = c.stuid

# 内连接！！！！ inner可以省略
SELECT * FROM student s JOIN score c ON s.stuid = c.stuid

# 查询学生姓名和分数以及课程名称！

# 学生表 分数表 课程表 【水平查询，99 还是 内外连接都要求有主外键！】
# 两两连接，
SELECT * FROM student s INNER JOIN score c ON s.stuid = c.stuid
		    INNER JOIN course u  ON c.courseid = u.cid;	
		    
#学生姓名和分数以及课程以及授课老师！
SELECT s.stuname,c.score,u.cname,t.tname FROM student s INNER JOIN score c ON s.stuid = c.stuid	
		    INNER JOIN course u ON c.courseid = u.cid
		    INNER JOIN teacher t ON u.tid = t.tid
		    WHERE c.score > 60;		
		    
# 没有三张以上的表查询！ 我们都是两两处理！主外键！
# 最后如果有where 再添加即可！

外连接

语法

#语法:
SELECT * FROM 表1 [inner] JOIN  表2 ON 主 = 外
#外连接:
select * from 表1 left/right [outer] join  表2 on 主 = 外
#left和right的作用就是用来指定逻辑主表！

代码

#99大法和内连接查询效果是一样！ 
#他们都是一种公平查询法则，要求两个表必须存在主外键的值，结果才能显示！例如：没有王老五， 因为学生表王老五，但是分数表没有外键的值！ 结果没有王老五！

#外连接： 就是了结果显示王老五！外连接不是公平查询法则，他可以通过左和右方向来指定逻辑主表！
#一旦被指定逻辑主表
#逻辑主表的数据再结果中全部会显示！ 如果想要王老五，必须使用外连接！！

# 查询学生和分数

SELECT * FROM score c  RIGHT OUTER JOIN student s ON c.stuid = s.stuid;

#方向就是指定你想要全部数据的表！
-- 两张表
SELECT * FROM student s  LEFT  JOIN  score c ON c.stuid = s.stuid;

-- 如果是三张表呢?
SELECT * FROM student s
LEFT  JOIN  score c ON c.stuid = s.stuid
LEFT JOIN course u ON c.courseid = u.cid
LEFT JOIN teacher t ON u.tid = t.tid;  

# left / right 外连接     join 内连接

内外连接的选择

只要有一个表的数据必须要全部展示，外连接！
规律
- 一旦你选择了外连接，那么后面的所有连接都是外连接！并且连接方向都一致！！

内外连接的区别

外连接有王老五，内连接没有王老五！
内连接和99大法他们是一类语法，都是公平法则，最后结果的数据要求双方都存在引用！查询没有王老五！
外连接是非公平查询法则，可以通过方向指定左或者右为逻辑主表，主表的数据一定再结果中存在！查询有王老五！

自然连接

自连接也是多表查询，不是新的语法，只是新的方式！
如果需要的数据来自于一行，那么就是单表查询！
如果需要的数据来自于多行，那么就是多表查询！
表查询分为两种，一种正常不同多表【学生和分数】另一种自连接【查员工和经理】
自连接：因为在设计表的时候，引用数据和被引用数据都存在一个表！还是不同行！

代码练习

# 4. 自连接 - 自己连接自己  
/*
  他不是一种新的语法，他是一种新的查询方式！
   一个表使用多次！
   学生表 连接 学生表  -》 自连接
*/       

编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名
CREATE TABLE IF NOT EXISTS Employee (Id INT, NAME VARCHAR(255), 
Salary INT, ManagerId INT)

INSERT INTO Employee (Id, NAME, Salary, ManagerId) VALUES ('1', 'Joe', '70000', '3');
INSERT INTO Employee (Id, NAME, Salary, ManagerId) VALUES ('2', 'Henry', '80000', '4');
INSERT INTO Employee (Id, NAME, Salary, ManagerId) VALUES ('3', 'Sam', '60000', 'None');
INSERT INTO Employee (Id, NAME, Salary, ManagerId) VALUES ('4', 'Max', '90000', 'None');

Employee 表包含所有员工，他们的经理也属于员工。
每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | NAME  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | MAX   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过
他们经理的员工的姓名。
在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

# 1. 查询员工姓名和经理名
   joe     sam 
   henry   MAX 
   
# 2. 行内数据对比   
   
SELECT  e1.*,e2.* 
FROM Employee e1 
INNER JOIN  Employee e2 ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;

子查询/嵌套查询

概念

子查询也叫嵌套查询
子查询就是查询中嵌套其他的查询
子查询产生的原因：没有得到直接条件，得到了间接条件，需要根据间接条件返回数据再次查询！

语法

#查询中嵌套查询
#比如 和小明身高一样的同学
 SELECT * FROM student 
 WHERE height = (select height from student where name = '小明');

子查询位置

单行单列

一般放在WHERE后面,充当一个条件值,可以使用任何比较符号 > < = ! =

代码

#1.工资高于JONES的员工 【单行单列】
# 1.查询JONES的工资 单行单列
SELECT sal FROM emp WHERE ename ='JONES'
# 2.查询比他工资高的员工信息  > < =
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename ='JONES')
#2.SCOTT同一个部门的员工 【单行单列】
#1.查询SCOTT的部门
SELECT deptno FROM emp WHERE ename = 'SCOTT'
#2.查询这个部门的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT')

多行单列

一般放在where后面，充当多个条件值

多列等于比较：例如：查询性别和身高与小明一样的同学！ 可以使用 in关键字 (列名,列名) in (子查询列名，列名) ，注意一一对应

多列等于比较：例如：查询性别一样但是身高高于小明的同学！不用in了，拆分成单行单列再比较即可！

代码

#4.查询工作和工资与MARTIN完全相同的员工信息
# 1。查询martin的工作和工资
SELECT job,sal FROM emp WHERE ename = "MARTIN"
# 2. 多列等于
SELECT * FROM emp 
WHERE (job,sal) 
IN (SELECT job,sal FROM emp WHERE ename = "MARTIN")
  
SELECT * FROM emp 
WHERE job = (SELECT job FROM emp WHERE ename = 'MARTIN')
AND sal = (SELECT sal FROM emp WHERE ename = 'MARTIN')

多行多列

都放在from 充当虚拟表！子查询结果集做表，****必须要起别名 from (子查询) 别名

代码

#5.有2个以上直接下属的员工信息
# 1.分组查询 有几个下属
SELECT 分组字段，聚合函数
# 查询出有两个以上下属的经理id和下属数量
SELECT mgr,COUNT(1) ct FROM emp  GROUP BY mgr HAVING ct >2;
    
#2.虚拟结果充当表在和员工表连接一次
SELECT e1.empno,e1.ename,temp.ct FROM emp e1 JOIN 
(SELECT mgr,COUNT(1) ct FROM emp  GROUP BY mgr HAVING ct >2) temp
ON e1.empno = temp.mgr;

子查询总结

子查询就是间接条件查询
子查询可以出现在单表,也可以出现在多表

数据库多表关系

数据库多表关系

为什么需要多表数据存储

表关系理解

表关系种类【主外键关系】

表关系实现(表设计者的角度)

一对一实现

一对多实现

多对多实现

SQL DQL语句(多表查询)

多表查询介绍和理解

垂直多表查询(合并结果集)

语法

注意

代码实现

水平多表查询（水平连接查询）【重点】

99查询大法【方言】

实现思路

实现语法

实现代码

准备数据

查询

连接查询【普通话】

内连接

语法

代码

外连接

语法

代码

内外连接的选择

内外连接的区别

自然连接

代码练习

子查询/嵌套查询

概念

语法

子查询位置

单行单列

代码

多行单列

代码

多行多列

代码

子查询总结

相关

标签