数据库多表关系

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

为什么需要多表数据存储

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

表关系理解

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

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

一对一
一对多
多对多

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

一对一实现

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;

子查询总结

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

posted @ 2022-03-25 23:31 乱起的名字阅读(168) 评论(0) 收藏举报

刷新页面返回顶部