创建以下四张表，并实行连接，进行成绩查询
举例
create table departments
(
did char(2) primary key not null comment'部门号',
dname varchar(30) not null comment'部门名称'
);
create table classes
(
clid char(70) primary key not null comment'班级号',
did char(2) not null comment'部门号',
constraint fk_Classes_Departments foreign key(DID) references Departments(DID),
clname varchar(20) not null comment'班级名称'
);
*表格建立时注意外键的建立，外键应当在主键的基础之上建立
外键语句：
constraint fk_Classes（当前表名）_Departments（主表名）
foreign key (DID)（设为外键的字段名）
references Departments(DID)（主键所在地）
insert departments(DID,DName)
values("01","学生部"),
("02","教工部"),
("03","员工部");
*利用insert语句插入记录
在插入成绩时空值要输如null
select st.sid,st.sname,st.sclass,sc.score from students as st
join teaching as t
on st.sclass=t.clid
join courses as c
on c.cid=t.cid
left join scores as sc
on sc.sid=st.sid and sc.cid=t.cid;
+-----------+--------+---------+-------+
| sid | sname | sclass | score |
+-----------+--------+---------+-------+
| 174042112 | 王洋飞 | 1740421 | 83 |
| 174042112 | 王洋飞 | 1740421 | 83 |
| 174042112 | 王洋飞 | 1740421 | 78 |
| 174042112 | 王洋飞 | 1740421 | 77 |
| 174042112 | 王洋飞 | 1740421 | 76 |
| 174042112 | 王洋飞 | 1740421 | 45 |
| 174042135 | 白孝石 | 1740421 | 88 |
| 174042135 | 白孝石 | 1740421 | 88 |
| 174042135 | 白孝石 | 1740421 | 66 |
| 174042135 | 白孝石 | 1740421 | 90 |
| 174042135 | 白孝石 | 1740421 | 77 |
| 174042135 | 白孝石 | 1740421 | NULL |
| 174042136 | 许欣怡 | 1740421 | 97 |
| 174042136 | 许欣怡 | 1740421 | 97 |
| 174042136 | 许欣怡 | 1740421 | 87 |
| 174042136 | 许欣怡 | 1740421 | 75 |
| 174042136 | 许欣怡 | 1740421 | 98 |
| 174042136 | 许欣怡 | 1740421 | 88 |
| 184041213 | 盛鑫 | 1840412 | 96 |
| 184041213 | 盛鑫 | 1840412 | 96 |
| 184041213 | 盛鑫 | 1840412 | 88 |
| 184041213 | 盛鑫 | 1840412 | 97 |
| 184041213 | 盛鑫 | 1840412 | 98 |
| 184041213 | 盛鑫 | 1840412 | 35 |
| 184041216 | 李欣 | 1840412 | 55 |
| 184041216 | 李欣 | 1840412 | 55 |
| 184041216 | 李欣 | 1840412 | 87 |
| 184041216 | 李欣 | 1840412 | 86 |
| 184041216 | 李欣 | 1840412 | 76 |
| 184041216 | 李欣 | 1840412 | 78 |
| 184041223 | 王博 | 1840412 | 68 |
| 184041223 | 王博 | 1840412 | 68 |
| 184041223 | 王博 | 1840412 | 80 |
| 184041223 | 王博 | 1840412 | NULL |
| 184041223 | 王博 | 1840412 | 77 |
| 184041223 | 王博 | 1840412 | 65 |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041248 | 李柏 | 1840412 | 78 |
| 184041248 | 李柏 | 1840412 | 78 |
| 184041248 | 李柏 | 1840412 | 86 |
| 184041248 | 李柏 | 1840412 | 77 |
| 184041248 | 李柏 | 1840412 | 55 |
| 184041248 | 李柏 | 1840412 | NULL |
| 194041111 | 张瑞芳 | 1940411 | 89 |
| 194041111 | 张瑞芳 | 1940411 | 89 |
| 194041111 | 张瑞芳 | 1940411 | 45 |
| 194041111 | 张瑞芳 | 1940411 | 85 |
| 194041111 | 张瑞芳 | 1940411 | 68 |
| 194041111 | 张瑞芳 | 1940411 | 54 |
| 194041115 | 邹建 | 1940411 | 54 |
| 194041115 | 邹建 | 1940411 | 54 |
| 194041115 | 邹建 | 1940411 | 79 |
| 194041115 | 邹建 | 1940411 | 12 |
| 194041115 | 邹建 | 1940411 | NULL |
| 194041115 | 邹建 | 1940411 | 45 |
| 194041124 | 雪莉 | 1940411 | 78 |
| 194041124 | 雪莉 | 1940411 | 78 |
| 194041124 | 雪莉 | 1940411 | 77 |
| 194041124 | 雪莉 | 1940411 | 45 |
| 194041124 | 雪莉 | 1940411 | 45 |
| 194041124 | 雪莉 | 1940411 | 87 |
| 194041133 | 张泽瑞 | 1940411 | 88 |
| 194041133 | 张泽瑞 | 1940411 | 88 |
| 194041133 | 张泽瑞 | 1940411 | 76 |
| 194041133 | 张泽瑞 | 1940411 | 68 |
| 194041133 | 张泽瑞 | 1940411 | 89 |
| 194041133 | 张泽瑞 | 1940411 | 77 |
| 194041145 | 王凯 | 1940411 | 76 |
| 194041145 | 王凯 | 1940411 | 76 |
| 194041145 | 王凯 | 1940411 | 67 |
| 194041145 | 王凯 | 1940411 | 75 |
| 194041145 | 王凯 | 1940411 | 45 |
| 194041145 | 王凯 | 1940411 | 65 |
| 194041212 | 詹飞三 | 1940412 | 87 |
| 194041212 | 詹飞三 | 1940412 | 87 |
| 194041212 | 詹飞三 | 1940412 | 67 |
| 194041212 | 詹飞三 | 1940412 | 97 |
| 194041212 | 詹飞三 | 1940412 | 45 |
| 194041212 | 詹飞三 | 1940412 | 45 |
| 194041221 | 汪涛 | 1940412 | 90 |
| 194041221 | 汪涛 | 1940412 | 90 |
| 194041221 | 汪涛 | 1940412 | 84 |
| 194041221 | 汪涛 | 1940412 | 56 |
| 194041221 | 汪涛 | 1940412 | 66 |
| 194041221 | 汪涛 | 1940412 | 78 |
| 194041222 | 高成河 | 1940412 | 88 |
| 194041222 | 高成河 | 1940412 | 88 |
| 194041222 | 高成河 | 1940412 | 87 |
| 194041222 | 高成河 | 1940412 | 77 |
| 194041222 | 高成河 | 1940412 | 76 |
| 194041222 | 高成河 | 1940412 | 89 |
| 194041229 | 徐松涛 | 1940412 | 91 |
| 194041229 | 徐松涛 | 1940412 | 91 |
| 194041229 | 徐松涛 | 1940412 | 86 |
| 194041229 | 徐松涛 | 1940412 | 76 |
| 194041229 | 徐松涛 | 1940412 | 87 |
| 194041229 | 徐松涛 | 1940412 | 78 |
+-----------+--------+---------+-------+
102 rows in set (0.17 sec)
*增加where语句添加条件将成绩为空的搜索出来
select st.sid,st.sname,st.sclass,sc.score from students as st
join teaching as t //
on st.sclass=t.clid
join courses as c //
on c.cid=t.cid
left join scores as sc //左外连接
on sc.sid=st.sid and sc.cid=t.cid
where sc.score is null;
+-----------+--------+---------+-------+
| sid | sname | sclass | score |
+-----------+--------+---------+-------+
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041223 | 王博 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 194041115 | 邹建 | 1940411 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 184041248 | 李柏 | 1840412 | NULL |
| 184041235 | 吴泽楷 | 1840412 | NULL |
| 174042135 | 白孝石 | 1740421 | NULL |
+-----------+--------+---------+-------+
10 rows in set (0.04 sec)
