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

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

建表原则

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

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

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

建表原则：

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

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

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

例子：

建表原则：

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

方案一：

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

方案二：

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

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

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

建表时先考虑基本属性，之后再考虑关系
将上述的多表通过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 相等匹配
* 返回结果一定是两个表都存在信息 ， 最有意义的信息，如果第一张表记录在第二张表找不到匹配信息，不显示，第二张表记录在第一张表无匹配信息，不显示

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

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

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

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

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

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

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

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

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

等价于：

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

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

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

select student_id from studentcource where score < 60; --- 小于60分学生学号 2,8select * 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);

在实际开发中 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);