数据库系统的多表设计
实体的三种关系 ： 多对多、一对多(多对一)、一对一
1、多对多关系
雇员和项目 、顾客和商品 课程 学生
描述：一个雇员可以参与多个项目，一个项目可以由多个雇员共同完成
一个顾客可以购买多件商品，同一种商品可以被多个顾客购买
建表原则：必须引入第三张关系表，在关系表中引入两张实体表的主键，定义为外键约束
2、一对多关系
教师和课程、作者和微博 用户 播客，博客 评论
描述：一个教师可以教授多个课程，一个课程只能由一个教师教授
一个作者可以发表多篇博文，一个博文只能由一个作者发表
建表原则：不用第三张关系表，在多的一方添加 一的一方 的主键作为 外键
3、一对一关系（主 从）
公司和经理 人 身份证 学生 学号
描述：一个公司 只能 有一个经理，一个经理只能负责一个 公司
建表原则：可以在任意一方，添加另一方主键作为外键
------------------------------------------------------------------------------------------------------------------------
多表设计案例
设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每个课程由一个老师负责
3、每门课程可以由多个学生选修
4、每个学生可以选修多门课程
5、学生选修课程要有成绩
teacher 1
course N 1 N ==》 M
student N 1 ==》 N
分析E-R图 编写建表语句
create table teacher(
id int primary key auto_increment,
name varchar(40) not null
);
create table cource(
id int primary key auto_increment,
name varchar(40) not null,
teacher_id int ,
foreign key(teacher_id) references teacher(id)
);
create table student(
id int primary key auto_increment,
name varchar(40) not null
);
教师 和 课程 之间存在 一对多关系 ：在 课程中添加 教师主键 作为外键
学生 和 课程 之间存在 多对多关系 ：必须引入第三张关系表，将两个实体主键引入作为外键
create table studentcource(
student_id int ,
cource_id int,
score double not null,
foreign key(student_id) references student(id),
foreign key(cource_id) references cource(id)
);
1 1
1 2
1 3
2 1
2 3
--------------------------------------------------------------------
笛卡尔积：将两张表关联，从第一张表取出每条记录，与第二张表每条记录连接
select * from a,b; 在from字段后面，可以用，连接多个表，查询结果 就是笛卡尔积
mysql> select * from a,b;
+------+--------+------+---------+
| A_ID | A_NAME | A_ID | B_PRICE |
+------+--------+------+---------+
| 1 | Apple | 1 | 2.3 | 有意义
| 2 | Orange | 1 | 2.3 |
| 3 | Peach | 1 | 2.3 |
| 1 | Apple | 2 | 3.5 |
| 2 | Orange | 2 | 3.5 | 有意义
| 3 | Peach | 2 | 3.5 |
| 1 | Apple | 4 | NULL |
| 2 | Orange | 4 | NULL |
| 3 | Peach | 4 | NULL |
+------+--------+------+---------+
笛卡尔积查询结果无意义的，连接查询，添加条件使得结果有意义
1、内连接查询 找共有的
用A去B中找寻对应的数据，找到了，显示结果，找不到对应不显示
* 内连接查询 显示数据 最有价值 ，该数据一定在A表和B表中同时存在
写法一： select * from a inner join b on a.A_ID = b.A_ID; ***标准的sql语句
* inner join 连接表，用on 关键字添加 连接条件
写法二： select * from a,b where a.A_ID = b.A_ID; ****数据库额外支持的功能
* 不需要写inner join，只需要在多表后面 添加where 加入连接条件
2、外连接查询（左外连接 、右外连接 、全外连接）
左外连接：用左边表 去 右边 表中查询 对应记录，不管是否找到， 都将显示左边表中 全部记录
***左边全显示 右边显示对应记录
select * from a left outer join b on a.A_ID=b.A_ID;
右外连接：用右边表 去 左边表 查询对应记录，不管是否找到，右边表全部记录都将显示
***右边全显示 左边显示对应记录
select * from a right outer join b on a.A_ID = b.A_ID;
全外连接：左外连接和右外连接和，排重
select * from a full outer join b on a.A_ID = b.A_ID; (MySQL 不支持)
* mysql 可以使用关键字 union 实现全外连接效果
select * from a left outer join b on a.A_ID=b.A_ID
union
select * from a right outer join b on a.A_ID = b.A_ID;
SQL 语言非过程语言 ： 一条sql 一个执行结果
关联子查询 ： 将一个子查询的结果作为另一个查询参数、条件、中间值
例如：最大年龄学生姓名
mysql> select max(age) from student;
+----------+
| max(age) |
+----------+
| 25 |
+----------+
将 25 作为第二个查询 条件
select * from student where age = ?
---- 使用子查询 select * from student where age = (select max(age) from student);
in 和 exists 存在
查询所有成绩小于60分的同学名称
in的用法： 在一个列表中取值，先通过查询获得列表，再使用in 取值
select * from student where id in (select student_id from studentcource where score < 60);
exists 用法：存在，核心关键 主查询和子查询 字段发生关系
select * from student where exists (select * from studentcource where score < 60 and student.id = studentcource.student_id);
mysql> select * from student where exists(select * from studentcource where score < 60);
select * from student where exists(select 1 from studentcource );
select * from student where exists(true);
select * from student where exists (select * from studentcource where score <60 and student.id = studentcource.student_id;
* 结论：exists 效率 好于 in
all 和 some 、any 使用
all 表示全部记录
some和any 效果一样 ，代表一部分记录
ANY 部分数据 >any(1,2,3) ====== > min(1,2,3)
ALL 所有数据 >all(1,2,3) ====== > max(1,2,3)
查询获得最高分的学生学号
select max(score) from studentcource;
将最高分作为子查询
select student_id from studentcource where score = (select max(score) from studentcource);
* 使用all ： select student_id from studentcource where score >= all(select score from studentcource);
查询编号2课程比编号1课程成绩高所有学号
编号1 所有成绩 ：select score from studentcource where cource_id = 1;
select student_id from studentcource where cource_id = 2 and score > any (select score from studentcource where cource_id = 1);
* select student_id from studentcource where cource_id = 2 and score > (select min(score) from studentcource where cource_id = 1);
union[all] 使用
union 过滤重复结果
union all 不过滤重复结果
select * from a left outer join b on a.A_ID=b.A_ID
union all
select * from a right outer join b on a.A_ID = b.A_ID;
将不对重复数据记录进行过滤
------------------------------------------------------------------------------------------
teacher 教师表 student 学生表 cource 课程表
+----+------+ +----+------+------+------+ +----+------+------------+
| id | name | | id | name | city | age | | id | name | teacher_id |
+----+------+ +----+------+------+------+ +----+------+------------+
| 3 | 赵云 | | 1 | 小王 | 北京 | 20 | | 1 | 语文 | 1 |
| 2 | 张飞 | | 2 | 小李 | 上海 | 18 | | 2 | 数学 | 1 |
| 1 | 关羽 | | 3 | 小周 | 北京 | 22 | | 3 | 生物 | 2 |
+----+------+ | 4 | 小刘 | 北京 | 21 | | 4 | 化学 | 2 |
| 5 | 小张 | 上海 | 22 | | 5 | 物理 | 2 |
| 6 | 小赵 | 北京 | 17 | | 6 | 英语 | 3 |
| 7 | 小蒋 | 上海 | 23 | +----+------+------------+
| 8 | 小韩 | 北京 | 25 |
| 9 | 小魏 | 上海 | 18 |
| 10 | 小明 | 北京 | 20 |
+----+------+------+------+
studentcource 选课表
+------------+-----------+-------+
| student_id | cource_id | score |
+------------+-----------+-------+
| 1 | 1 | 80 |
| 2 | 3 | 77 |
| 2 | 5 | 80 |
+------------+-----------+-------+
1、查询平均成绩大于70分的同学的学号和平均成绩
A: 每个人平均成绩 ---- 分组
select student_id,avg(score) from studentcource group by student_id;
* 如果显示学生的姓名 ？？
将之前查询结果id 去查询 student表中对应name ，显示姓名时还要显示平均成绩
select student.name,temp.平均成绩 from student,(select student_id,avg(score) ascore from studentcource group by student_id having ascore>70) temp where temp.student_id = student.id;
备注：以下是王恒自己写
mysql>select student_id '学号',student.name '姓名',avg(score) '平均成绩' from studentcource,student where student.id=studentcource.student_id group by student_id having avg(score)>70;
+------------+-------------------+
| student_id | avg(score) |
+------------+-------------------+
| 1 | 83.25 |
| 3 | 72.2 |
| 4 | 83.75 |
| 6 | 83.66666666666667 |
| 7 | 80 |
| 9 | 88 |
| 10 | 80.2 |
+------------+-------------------+
2、查询所有同学的学号、姓名、选课数、总成绩
A: 先查询学生的学号，选课数，总成绩
select student_id, count(cource_id),sum(score) from studentcource group by student_id;
姓名来自 student表
其它信息 来自 查询结果
select temp.student_id 学号,student.name 姓名,temp.选课数,temp.总成绩 from student,(select student_id ,
count(cource_id) 选课数,sum(score) 总成绩 from studentcource group by student_id) temp where temp.student_id = student.id;
备注：以下是王恒自己写的
mysql>select t.id,t.name,count(s.cource_id),sum(s.score) from student t,student
cource s where t.id=s.student_id group by s.student_id;
+------+------+-------------------+
| 学号 | 姓名 | 平均成绩 |
+------+------+-------------------+
| 1 | 小王 | 83.25 |
| 3 | 小周 | 72.2 |
| 4 | 小刘 | 83.75 |
| 6 | 小赵 | 83.66666666666667 |
| 7 | 小蒋 | 80 |
| 9 | 小魏 | 88 |
| 10 | 小明 | 80.2 |
+------+------+-------------------+
3、查询没学过关羽老师课的同学的学号、姓名
A. 先查找关羽老师教过课 编号
select cource.id from cource,teacher where teacher.id = cource.teacher_id and teacher.name='关羽';
学过关羽老师课学生学号 select student_id from studentcource where cource_id in (select cource.id from cource,teacher where teacher.id = cource.teacher_id and teacher.name='关羽');
没学过关羽老师学生学号和姓名
select id,name from student where id not in (select student_id from studentcource where cource_id in (select cource.id from cource,teacher where teacher.id = cource.teacher_id and teacher.name='关羽'));
备注：以下是王恒自己写的
mysql> select id,name from student where id not in (select student_id from stude
ntcource where cource_id in (select id from cource where teacher_id =(select id
from teacher where name='关羽')));
+----+------+
| id | name |
+----+------+
| 7 | 小蒋 |
+----+------+
4、查询学过赵云老师所教的所有课的同学的学号、姓名
A：查找赵云老师教过课程编号列表 select cource.id from cource,teacher where teacher.id = cource.teacher_id and teacher.name='张飞';
* 计算赵云老师 共教过几门
（select cource.id from cource,teacher where teacher.id = cource.teacher_id and teacher.name='赵云'） t1，from studentcource t2 where t1.cid= t2.id
1，2,3 123456
2 45 sid
------学过赵云老师的课 学生
count（） 3
那个学生学过赵云老师教的课
select * from ((select * from studentcource , (select cource.id cid from cource,teacher where teacher.id = cource.teacher_id and teacher.name='赵云') temp where temp.cid =studentcource.cource_id) t group by t.student_id having count(t.cource_id)=(select count(cource.id) from cource,teacher where teacher.id = cource.teacher_id and teacher.name='赵云'))
select * from (select t.student_id sid from (select * from studentcource , (select cource.id cid from cource,teacher where teacher.id = cource.teacher_id and teacher.name='张飞') temp where temp.cid = studentcource.cource_id) t group by t.student_id having count(t.cource_id)=3) t2,student st where t2.sid= st.id;
对学过赵云老师课程结果，按照学号分组，统计课程数--- 每个学生学过赵云老师几门课
备注：以下是王恒自己写的 **************有点难**************
mysql> select id,name from student where id in (select student_id from studentco
urce where cource_id in (select id from cource where teacher_id=(select id from
teacher where name='赵云')) group by student_id having count(student_id)=(select
count(id) from cource where teacher_id=(select id from teacher where name='赵云
')));
+----+------+
| id | name |
+----+------+
| 4 | 小刘 |
| 5 | 小张 |
| 3 | 小周 |
+----+------+
5、查询没有学三门课以上的同学的学号、姓名
A：每个学生学过几门课 select student_id from studentcource group by student_id having count(cource_id)<3;
select id,name from student where id in (select student_id from studentcource group by student_id having count(cource_id)<3);
备注：以下是王恒自己写的
mysql> select id,name from student where id in (select student_id from studentcource group by student_id having (select count(id) from cource)-count(cource_id)>3);
+----+------+
| id | name |
+----+------+
| 9 | 小魏 |
| 7 | 小蒋 |
+----+------+
6、查询各科成绩最高和最低的分
A: 按课程分组 select cource_id, max(score),min(score) from studentcource group by cource_id;
备注：以下是王恒自己写的
mysql> select s.cource_id,c.name,max(s.score),min(s.score) from studentcource s,cource c where c.id=s.cource_id group by cource_id;
+-----------+------+--------------+--------------+
| cource_id | name | max(s.score) | min(s.score) |
+-----------+------+--------------+--------------+
| 1 | 语文 | 80 | 60 |
| 2 | 数学 | 90 | 53 |
| 3 | 生物 | 87 | 58 |
| 4 | 化学 | 85 | 70 |
| 5 | 物理 | 83 | 65 |
| 6 | 英语 | 95 | 69 |
+-----------+------+--------------+--------------+
7、查询学生信息和平均成绩
A：查询学号和平均成绩 select student_id,avg(score) from studentcource group by student_id;
select * from student,(select student_id,avg(score) 平均成绩 from studentcource group by student_id) temp where student.id = temp.student_id;
备注：以下是王恒自己写的
mysql> select stu.*,avg(s.score) from student stu,studentcource s where stu.id=s.student_id group by stu.id;
+----+------+------+------+-------------------+
| id | name | city | age | avg(s.score) |
+----+------+------+------+-------------------+
| 1 | 小王 | 北京 | 20 | 83.25 |
| 2 | 小李 | 上海 | 18 | 70 |
| 3 | 小周 | 北京 | 22 | 72.2 |
| 4 | 小刘 | 北京 | 21 | 83.75 |
| 5 | 小张 | 上海 | 22 | 69.75 |
| 6 | 小赵 | 北京 | 17 | 83.66666666666667 |
| 7 | 小蒋 | 上海 | 23 | 80 |
| 8 | 小韩 | 北京 | 25 | 65.66666666666667 |
| 9 | 小魏 | 上海 | 18 | 88 |
| 10 | 小明 | 北京 | 20 | 80.2 |
+----+------+------+------+-------------------+
teacher 教师表 student 学生表 cource 课程表
+----+------+ +----+------+------+------+ +----+------+------------+
| id | name | | id | name | city | age | | id | name | teacher_id |
+----+------+ +----+------+------+------+ +----+------+------------+
| 3 | 赵云 | | 1 | 小王 | 北京 | 20 | | 1 | 语文 | 1 |
| 2 | 张飞 | | 2 | 小李 | 上海 | 18 | | 2 | 数学 | 1 |
| 1 | 关羽 | | 3 | 小周 | 北京 | 22 | | 3 | 生物 | 2 |
+----+------+ | 4 | 小刘 | 北京 | 21 | | 4 | 化学 | 2 |
| 5 | 小张 | 上海 | 22 | | 5 | 物理 | 2 |
| 6 | 小赵 | 北京 | 17 | | 6 | 英语 | 3 |
| 7 | 小蒋 | 上海 | 23 | +----+------+------------+
| 8 | 小韩 | 北京 | 25 |
| 9 | 小魏 | 上海 | 18 |
| 10 | 小明 | 北京 | 20 |
+----+------+------+------+
studentcource 选课表
+------------+-----------+-------+
| student_id | cource_id | score |
+------------+-----------+-------+
| 1 | 1 | 80 |
| 2 | 3 | 77 |
| 2 | 5 | 80 |
+------------+-----------+-------+
8、查询上海和北京学生数量
A: 按照城市分组 select city,count(*) from student group by city having city='北京' or city='上海';
select city,count(*) from student where city='北京' or city='上海' group by city ;
备注：以下是王恒自己写的
mysql> select city,count(*) from student where city='上海' or city='北京' group by city;
+------+----------+
| city | count(*) |
+------+----------+
| 上海 | 4 |
| 北京 | 6 |
+------+----------+
9、查询不及格的学生信息和课程信息
A：从选课表 知道那个学号、课程号、成绩不及格 select student_id,cource_id,score from studentcource where score < 60;
select student.name,cource.name,studentcource.score from student,cource,studentcource where student.id = studentcource.student_id and cource.id = studentcource.cource_id and studentcource.score < 60;
备注：以下是王恒自己写的
mysql> select stu.name,c.name,s.score from student stu,studentcource s,cource c
where stu.id=s.student_id and c.id=s.cource_id and s.score<60;
+------+------+-------+
| name | name | score |
+------+------+-------+
| 小李 | 数学 | 53 |
| 小韩 | 生物 | 58 |
+------+------+-------+
10、统计每门课程的学生选修人数（超过两人的进行统计）
A: 每个课程选休学生人数 （选课表中安装课程编号分组）
select cource_id, count(student_id) from studentcource group by cource_id;
查过两人 通过having 添加条件
select cource_id, count(student_id) from studentcource group by cource_id having count(student_id) > 2;
备注：以下是王恒自己写的
mysql> select cource_id,c.name,count(student_id) from studentcource,cource c wh
ere studentcource.cource_id=c.id group by cource_id having>2;
+-----------+------+-------------------+
| cource_id | name | count(student_id) |
+-----------+------+-------------------+
| 1 | 语文 | 5 |
| 2 | 数学 | 9 |
| 3 | 生物 | 6 |
| 4 | 化学 | 5 |
| 5 | 物理 | 5 |
| 6 | 英语 | 3 |
+-----------+------+-------------------+
------------------------------------------------------------------------------------------------------------------------十道题全做
1.自连接查询
2.查询课程编号2课程成绩比编号1课程成绩高的学生的编号。
3.学过关羽老师2门课的学生编号和姓名
1.
select s1.* from student s1,student s2 where s1.id=s2.id and s2.age=20;
2:
select t2.student_id from (select student_id,score from studentcource where cource_id=1)as t1,(select student_id,score from studentcource where cource_id=2) as t2 where t1.student_id=t2.student_id and t1.score-t2.score<0;
3:
select id,name from student where id in (select student_id from studentcource where cource_id in (select id from cource where teacher_id=(select id from teacher where name='关羽')) group by student_id having (count(student_id)=2)) order by id asc;