【12】表关系（表的对应关系）

文章目录

* 1. 一对一 <https://blog.csdn.net/spectre_win/article/details/88538507#1__1>
* 1.1 主键一对一 <https://blog.csdn.net/spectre_win/article/details/88538507#11__2>
* 1.2 外键一对一
<https://blog.csdn.net/spectre_win/article/details/88538507#12__41>
* 2.一对多（多对一） <https://blog.csdn.net/spectre_win/article/details/88538507#2_81>
* 3. 多对多 <https://blog.csdn.net/spectre_win/article/details/88538507#3__131>
* 3.1 学生、课程、成绩类的问题
<https://blog.csdn.net/spectre_win/article/details/88538507#31__132>
* 3.2 用户、角色、权限类的问题
<https://blog.csdn.net/spectre_win/article/details/88538507#32__211>


<>1. 一对一

<>1.1 主键一对一

* 场合：基础信息、附加信息，需要有相同的用户编号。
*
举例：Person表和Address表具有相同的PersonId，Person表做主表，Address表做子表；Address表的主键是Person表的外键。
* 创建表： CREATE TABLE Person ( person_Id INT NOT NULL, -- PK person_Name
VARCHAR(50), PRIMARY KEY(person_Id) ); CREATE TABLE Address ( person_Id INT NOT
NULL, -- PK , FK address VARCHAR(200), PRIMARY KEY(person_Id), FOREIGN
KEY(person_Id) REFERENCES Person(person_Id) );
* 添加数据 INSERT INTO Person(person_Id,person_Name) VALUES (1,'张三'); INSERT
INTO Person(person_Id,person_Name) VALUES (2,'李四'); SELECT * FROM Person;
INSERT INTO Address(person_Id,address) VALUES (1,'解放路55号'); INSERT INTO
Address(person_Id,address) VALUES (2,'文化东路55号'); SELECT * FROM Address;
* 关联查询：
连接条件：从表的外键 等于 主表的主键 SELECT * FROM Person , Address WHERE Address.`person_Id`
= Person.`person_Id`; SELECT * FROM Person p, Address a WHERE a.`person_Id` =
p.`person_Id`;
<>1.2 外键一对一

* 场合：每个用户有唯一的学历信息。一个学历的编号只能被使用一次。
* 举例：Address表做主表，Person表做子表；Person表中的address_Id是Address表的外键，并且是唯一的。
* 创建表： CREATE TABLE Address2 ( address_Id INT NOT NULL, -- PK
address_content VARCHAR(200), PRIMARY KEY(address_Id) ); CREATE TABLE Person2 (
person_Id INT NOT NULL, -- PK person_Name VARCHAR(50), address_Id INT NOT NULL
UNIQUE, -- FK , UNIQUE 表示唯一，每一个address_Id只能用一次 PRIMARY KEY(person_Id), FOREIGN
KEY(address_Id) REFERENCES Address2(address_Id) );
* 添加数据 INSERT INTO Address2(address_Id,address_content) VALUES (1,'青岛路33号');
INSERT INTO Address2(address_Id,address_content) VALUES (2,'和平路158号'); INSERT
INTO Address2(address_Id,address_content) VALUES (3,'香山大街7号'); SELECT * FROM
Address2; INSERT INTO Person2(person_Id,person_Name,address_Id) VALUES
(1,'张三',1); INSERT INTO Person2(person_Id,person_Name,address_Id) VALUES
(2,'李四',2); INSERT INTO Person2(person_Id,person_Name,address_Id) VALUES
(3,'李四',3); SELECT * FROM Person2;
* 关联查询：
连接条件：从表的外键 等于 主表的主键 SELECT * FROM Person2 p, Address2 a WHERE p.`address_Id`
= a.`address_Id`;
<>2.一对多（多对一）

* 场合：一张表是主表，另一张表是子表，主表与子表的对应关系不受唯一性控制；
*
举例：Grade表和Student表，Grade表做主表，Student表做子表；Student表的Grade_Id是Student表的外键，引用的是Grade表中的主键Grade_Id。
* 创建表： CREATE TABLE IF NOT EXISTS Grade ( Grade_Id INT NOT NULL, -- 主键
Grade_Name VARCHAR(50) NOT NULL , PRIMARY KEY(Grade_Id) ) CREATE TABLE Student(
Stu_No INT NOT NULL , Stu_Name VARCHAR(50) NOT NULL , Stu_Age INT DEFAULT 15 ,
Stu_Sex CHAR(2) DEFAULT '男' , Stu_Birthday DATE , Grade_Id INT , -- 外键 PRIMARY
KEY (Stu_No), FOREIGN KEY (Grade_Id) REFERENCES Grade(Grade_Id) )
* 添加数据 INSERT INTO Grade(Grade_Name) VALUES ('一年级'); INSERT INTO
Grade(Grade_Name) VALUES ('二年级'); INSERT INTO Grade(Grade_Name) VALUES ('三年级');
SELECT * FROM Grade; INSERT INTO
Student(Stu_Name,Stu_Age,Stu_Birthday,Grade_Id) VALUES ('张三',21,'1997-1-1',1);
INSERT INTO Student(Stu_Name,Stu_Age,Stu_Birthday,Grade_Id) VALUES
('李四',22,'1996-4-1',2); INSERT INTO
Student(Stu_Name,Stu_Age,Stu_Birthday,Grade_Id) VALUES ('王五',19,'1999-11-11',2);
* 关联查询：
连接条件：从表的外键 等于 主表的主键 SELECT * FROM Grade INNER JOIN Student ON
Grade.`Grade_Id` = Student.`Grade_Id`; SELECT
s.`Stu_No`,s.`Stu_Name`,g.`Grade_Name` FROM Student s INNER JOIN Grade g ON
s.`Grade_Id` = g.`Grade_Id` WHERE s.`Stu_Sex` = '女';
<>3. 多对多

<>3.1 学生、课程、成绩类的问题

* 一个学生可以选择多门课程，一门课程可以被多个学生选；
* 使用中间表（成绩表）记录学生和课程之间的对应关系。
* 建表 CREATE TABLE Student( Stu_No INT NOT NULL, Stu_Name VARCHAR(50) NOT
NULL , PRIMARY KEY (Stu_No), ) ; CREATE TABLE Course ( Course_Id INT NOT NULL ,
Course_Name VARCHAR(50) NOT NULL , PRIMARY KEY(Course_Id) ); CREATE TABLE
Result ( Id INT NOT NULL , Stu_No INT NOT NULL , Course_Id INT NOT NULL ,
Result DECIMAL(5,2) , PRIMARY KEY(Id) ); ALTER TABLE Result ADD CONSTRAINT
FK_Result_Student FOREIGN KEY(Stu_No) REFERENCES Student(Stu_No); ALTER TABLE
Result ADD CONSTRAINT FK_Result_Course FOREIGN KEY(Course_Id) REFERENCES
Course(Course_Id);
* 插入数据 INSERT INTO Student(Stu_No,Stu_Name) VALUES (1,'张三'); INSERT INTO
Student(Stu_No,Stu_Name) VALUES (2,'李四'); INSERT INTO Student(Stu_No,Stu_Name)
VALUES (3,'王五'); INSERT INTO Student(Stu_No,Stu_Name) VALUES (4,'赵柳'); INSERT
INTO Student(Stu_No,Stu_Name) VALUES (5,'张无忌'); SELECT * FROM Student; INSERT
INTO Course(Course_Id,Course_Name) VALUES (1,'Java'); INSERT INTO
Course(Course_Id,Course_Name) VALUES (2,'SEO'); INSERT INTO
Course(Course_Id,Course_Name) VALUES (3,'PS'); SELECT * FROM Course; INSERT
INTO Result(id,Stu_No,Course_Id,Result) VALUES (1,1,2,90); INSERT INTO
Result(id,Stu_No,Course_Id,Result) VALUES (2,2,1,75.5); INSERT INTO
Result(id,Stu_No,Course_Id,Result) VALUES (3,3,1,39); INSERT INTO
Result(id,Stu_No,Course_Id,Result) VALUES (4,2,2,70); SELECT * FROM Result;
* 具体实现
* 查询学号、姓名、课程编号、课程名称、成绩
* 学号（Result、Student）
* 姓名（Student）
* 课程编号（Result、Course）
* 课程名（Course）
* 成绩（Result） SELECT
s.`Stu_No`,s.`Stu_Name`,c.`Course_Id`,c.`Course_Name`,r.`Result` FROM Result r
RIGHT JOIN Student s ON s.`Stu_No` = r.`Stu_No` LEFT JOIN Course c ON
r.`Course_Id` = c.`Course_Id` WHERE c.Course_Id IS NULL -- 没选课 -- WHERE
c.Course_Id is not null and r.`Result` IS NULL -- 选课了，没考试 Order by s.`Stu_No`
* 查询没有被选的课程信息 SELECT * FROM Course WHERE Course_Id NOT IN (SELECT DISTINCT
Course_id FROM Result);
<>3.2 用户、角色、权限类的问题

* 用户：（User）当前系统中注册的用户信息
* 角色：（Role）把特定的权限，组成一个角色，将角色分配给用户。可以把角色看做是一组权限的组合体。
* 权限：（Permission）在多用户计算机系统中，权限是指某个特定的用户具有特定的系统资源使用权力，权限是对每个用户能够做什么事情，进行的具体约束。
* 用户与角色的关系：多对多，一个用户可以具备多个角色，一个角色可以分配给多个用户。
* 角色与权限的关系：多对多，一个角色包含多个权限，一个权限可以赋给多个角色。
* 建表 # 用户表 CREATE TABLE t_User ( User_Id INT NOT NULL, User_Name VARCHAR(50)
NOT NULL, PRIMARY KEY(User_Id) ); # 角色表 CREATE TABLE t_Role ( Role_Id INT NOT
NULL, Role_Name VARCHAR(50) NOT NULL, PRIMARY KEY(Role_Id) ); # 用户角色表 CREATE
TABLE t_RoleUser ( RU_Id INT NOT NULL, User_Id INT, Role_Id INT, PRIMARY
KEY(RU_Id), FOREIGN KEY(User_Id) REFERENCES t_User(User_Id), FOREIGN
KEY(Role_Id) REFERENCES t_Role(Role_Id) ); # 权限表 CREATE TABLE t_Permission (
Permission_Id INT NOT NULL, Permission_Name VARCHAR(50) NOT NULL, PRIMARY
KEY(Permission_Id) ); # 权限角色表 CREATE TABLE t_PermissionRole ( PR_Id INT NOT
NULL, Permission_Id INT, Role_Id INT, PRIMARY KEY (PR_Id), FOREIGN
KEY(Permission_Id) REFERENCES t_Permission(Permission_Id), FOREIGN KEY(Role_Id)
REFERENCES t_Role(Role_Id) );
*
插入数据
。。。。。。。。。。。。

*
具体实现
查询指定用户的角色列表
查询指定用户的权限列表
查询指定用户的所有角色和所有权限
。。。。。。。。。

• « 上一篇：Esp8266 进阶之路31【外设篇】分享一个乐鑫esp8266 SDK编程使用 IIC总线驱动 0.96寸的OLED显示屏，显示天气预报信息。（附带Demo）
• » 下一篇：OpenCV3基础——几种基本的图像处理