数据库的多表设计及复杂查询

╰半夏微凉° 2022-07-13 00:52 288阅读 0赞

数据表与数据表之间关系三种：实体之间关系 多对多、一对多、一对一，接下来我们将从原理和对应的实例来学习以下：  
**（一）多对多M:N**  
例子:项目与程序员  
一个项目可以由多个程序员参与；一个程序员可以参与多个项目的开发

![这里写图片描述][SouthEast]

**建表原则**

*  联系的属性及两个实体的**主标识**形成关系表
 *  关系表的主键为两个实体主标识的组合

![这里写图片描述][SouthEast 1]

雇员：`（E1,E2）;`  
项目：`(P1,P2,P3);`  
参加表：`(E1,P1,percent);`

**（二）一对多1：N**  
例子：教师与课程  
一个老师可以教授多门课程，但是一门课程只能由一个老师教授

![这里写图片描述][SouthEast 2]

**建表原则**：

*  将关系的属性及非多方的主标识加入到多方表
 *  多方表的外键是非多方实体的主标识

![这里写图片描述][SouthEast 3]

教师：`(Tid,Tname,Tsex);`  
课程：`(Cid,Cname,Tid,allowance);`

**（三）一对一（比较少见）**

例子：

![这里写图片描述][SouthEast 4]

**建表原则**：

*  一对一关系，可以在任何一方添加另一方主键作为外键

方案一：

Presidents：`(Eid,Ename);`  
Studios:`(Sid,Sname,Eid,year);`

方案二：

Presidents：`(Eid,Ename,Sid,year);`  
Studios:`(Sid,Sname);`

**建表练习**  
设计学生成绩管理系统数据表  
1、每个教师可以教多门课程  
2、每门课程可以由多个学生选修  
3、每个学生可以选修多门课程  
4、学生选修课程要有成绩

![这里写图片描述][SouthEast 5]

**注意：成绩这个属性既不属于学生也不属于课程，是一种关系属性（对于关系属性也可以参见上面的几个例子）**

建表时先考虑基本属性，之后再考虑关系  
将上述的多表通过MySQL实现以下如下：

CREATE TABLE teacher (
      id int(11) NOT NULL primary key auto_increment,
      name varchar(20) not null unique
     );
    CREATE TABLE student (
      id int(11) NOT NULL primary key auto_increment,
      name varchar(20) NOT NULL unique,
      city varchar(40) NOT NULL,
      age int 
    ) ;
    CREATE TABLE cource(
      id int(11) NOT NULL primary key auto_increment,
      name varchar(20) NOT NULL unique,
      teacher_id int(11) NOT NULL,
      FOREIGN KEY (teacher_id) REFERENCES teacher (id)
    );
    
    CREATE TABLE studentcource (
       student_id int NOT NULL,
       cource_id int NOT NULL,
       score double NOT NULL,
       FOREIGN KEY (student_id) REFERENCES student (id),
       FOREIGN KEY (cource_id) REFERENCES cource (id)
    );
    
    insert into teacher values(null,'关羽');
    insert into teacher values(null,'张飞');
    insert into teacher values(null,'赵云');
    
    insert into student values(null,'小王','北京',20);
    insert into student values(null,'小李','上海',18);
    insert into student values(null,'小周','北京',22);
    insert into student values(null,'小刘','北京',21);
    insert into student values(null,'小张','上海',22);
    insert into student values(null,'小赵','北京',17);
    insert into student values(null,'小蒋','上海',23);
    insert into student values(null,'小韩','北京',25);
    insert into student values(null,'小魏','上海',18);
    insert into student values(null,'小明','北京',20);
    
    insert into cource values(null,'语文',1);
    insert into cource values(null,'数学',1);
    insert into cource values(null,'生物',2);
    insert into cource values(null,'化学',2);
    insert into cource values(null,'物理',2);
    insert into cource values(null,'英语',3);
    
    insert into studentcource values(1,1,80);
    insert into studentcource values(1,2,90);
    insert into studentcource values(1,3,85);
    insert into studentcource values(1,4,78);
    insert into studentcource values(2,2,53);
    insert into studentcource values(2,3,77);
    insert into studentcource values(2,5,80);
    insert into studentcource values(3,1,71);
    insert into studentcource values(3,2,70);
    insert into studentcource values(3,4,80);
    insert into studentcource values(3,5,65);
    insert into studentcource values(3,6,75);
    insert into studentcource values(4,2,90);
    insert into studentcource values(4,3,80);
    insert into studentcource values(4,4,70);
    insert into studentcource values(4,6,95);
    insert into studentcource values(5,1,60);
    insert into studentcource values(5,2,70);
    insert into studentcource values(5,5,80);
    insert into studentcource values(5,6,69);
    insert into studentcource values(6,1,76);
    insert into studentcource values(6,2,88);
    insert into studentcource values(6,3,87);
    insert into studentcource values(7,4,80);
    insert into studentcource values(8,2,71);
    insert into studentcource values(8,3,58);
    insert into studentcource values(8,5,68);
    insert into studentcource values(9,2,88);
    insert into studentcource values(10,1,77);
    insert into studentcource values(10,2,76);
    insert into studentcource values(10,3,80);
    insert into studentcource values(10,4,85);
    insert into studentcource values(10,5,83);

**关系表表名，通常用两个实体表表名组合而成！例如上述例子中的studentcource**

**（四）复杂查询**  
**笛卡尔积**  
当两个数据表进行关联查询时，用第一张数据表每一条记录去匹配第二张数据表每一条记录。

第一张表10条数据  
第二张表20条数据  
使用笛卡尔积 结果 10\*20 = 200 条记录  
**并且**：  
第一张表10条数据  
第二张表20条数据  
内连接 结果 <= 10条

在实际开发中，获得笛卡尔积中有意义的记录 ？ —- 连接查询  
内连接  
外连接

**1、内连接** ： 将两张表相同意义字段连接起来  
select \* from a,b where a.id= b.id; 条件 A表中A\_ID与 ，B表中 A\_ID 相等匹配  
\* 返回结果一定是两个表都存在信息 ， 最有意义的信息，如果第一张表记录在第二张表找不到匹配信息，不显示，第二张表记录在第一张表无匹配信息，不显示

![这里写图片描述][SouthEast 6]

**内连接的两种写法都是可以的**

**外连接**：左外连接、右外连接、全外连接  
**2、左外连接**：用第一张表每条记录去匹配第二张表对应记录，无论是否找到匹配信息，都显示第一张表匹配结果（没有找到的用null）

第一张表10条数据  
第二张表20条数据  
左外连接 — 10条

![这里写图片描述][SouthEast 7]

**3、右外连接**：从第二张表找第一张表匹配记录，无论是否找到，第二张表所有记录都显示

![这里写图片描述][SouthEast 8]

**4、全外连接**：左外连接与右外连接 结果和 —- 排除重复数据

![这里写图片描述][SouthEast 9]

**在mysql中使用全外连接需要使用union关键字来实现**

**5、关联子查询**：将第一个查询结果 ，作为第二个查询条件

查询student表中年龄最大学员的信息：

select * from student where age = (select max(age) from student);

等价于：

select max(age) from student; ----- 25
    select * from student where age = 25; ----- 学生信息

由于sql语言是非过程性的，因此不分为两步进行查询。

**IN当前查询记录在子查询结果中存在**  
查询studentcource表成绩小于60 所有记录(两个表进行关联查询)：  
如果按照分步走，我们需要先从成绩表查询出id，再根据id从学生表中找到姓名：

select student_id from studentcource where score < 60; --- 小于60分学生学号 2,8
    select * from student where id in(2,8);

将上面两句通过in进行组合：

select * from studentcource where id in(select * from studentcource where score<60);

**EXISTS 当前查询记录在子查询结果中存在**

select name from student where exists (select * from studentcource where score < 60 and student.id = studentcource.student_id);

![这里写图片描述][SouthEast 10]

在实际开发中 exists比 in效率要高，因为in后面的sql语句是独立执行的，而exists语句是进行了关联的，因此进行sql优化时，多用exists语句而非in。

**ANY、SOME、ALL 用法**  
SOME和ANY作用相同的 —– 一些 >any(1,2,3) 大于任何一个都可以 等价于 >min  
ALL —- 所有 >all(1,2,3) 必须同时大于三个值 等价于 >max

查询获得最高分的学生学号：

select max(score) from studentcource;

select student_id from studentcource where score >=all(select score from studentcource);

查询编号2课程比编号1课程成绩高所有学号：

select score from studentcource where cource_id = 2 and score > any(select score from studentcource where cource_id = 1);

[SouthEast]: /images/20220713/4368c543da7d497aa0c8c526e1292b45.png
[SouthEast 1]: /images/20220713/20308a0a1bfa460d83bbe221573f9cea.png
[SouthEast 2]: /images/20220713/9444bfb9b5b7485e92a54e1ec0466b9e.png
[SouthEast 3]: /images/20220713/fd65497ebb934e9cbd1722954980f8bf.png
[SouthEast 4]: /images/20220713/f4c60d490a8f4504ad88fe86e1396f6e.png
[SouthEast 5]: /images/20220713/269fd8905ea045d09d61bdc50c56e849.png
[SouthEast 6]: /images/20220713/edce1076c858460fab3418f8e5e6b748.png
[SouthEast 7]: /images/20220713/d12349ec784c40599e2dec2df2a031a9.png
[SouthEast 8]: /images/20220713/1d46602ab937442bb33b112081c537b0.png
[SouthEast 9]: /images/20220713/9d7450b745104c979fb7ea835b2d67a3.png
[SouthEast 10]: /images/20220713/022ff0fe0825482ab527840db60d8de9.png