本文共 5752 字，大约阅读时间需要 19 分钟。

创建以下四张表，并实行连接，进行成绩查询

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'班级名称'     );

*表格建立时注意外键的建立，外键应当在主键的基础之上建立

insert departments(DID,DName)values("01","学生部"),      ("02","教工部"),      ("03","员工部");

*利用insert语句插入记录

select  st.sid,st.sname,st.sclass,sc.score from students as stjoin teaching as ton st.sclass=t.clidjoin courses as c on c.cid=t.cidleft join scores  as scon 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 stjoin teaching as t       //on    st.sclass=t.clidjoin courses as c        //on    c.cid=t.cidleft join scores  as sc  //左外连接on    sc.sid=st.sid and sc.cid=t.cidwhere 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)