[SQL实战] SQL课表查询

刚在项目实训中学习了一点点SQL的基本知识，记下来和大家分享。

建表与生成E-R图

表可以提前在数据库软件中建好。在MySQL Workbench中，可选择新建模型，选择database->Reverse Engineer,在提示中关联已建好的schemas，进而生成E-R图，即实体-关系图。

可以看到，我们为模型classroom建立了三张表，分别是教师表teacher、教室表room、课程表course，课程表通过外键roomid、teacherid与room和teacher关联。值得一提的是，我们并不是直接在数据库中按时间建完整课表，而是将数据分在三张表中，这样可以降低耦合度，减少不必要的关联，使各表的修改相对独立。
下面展示三张表的基本数据：

• 教师表：
  
• 教室表
  
• 课程表
  

基本操作——增、删、改、查

• 插入：INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,值3);
  INSERT INTO classroom.teacher(teacher) VALUES('Ada');
• 修改：UPDATE 表名 SET 列名1=值1,… WHERE 条件;
  UPDATE classroom.teacher SET teacher='Claire' WHERE id=4;
• 删除: DELETE FROM 表名 WHERE 条件;
  DELETE FROM classroom.teacher WHERE id = 4;
• 查询: SELECT 列名1,列名2,… FROM 表名 WHERE 条件 ORDER BY 列名列表;
  SELECT * FROM classroom.course ORDER BY roomid ASC;
  

高级查询

别名的使用

可用AS为表或列设置别名，进而可以实现将一张表“复制”多份

IN, NOT IN

前面在WHERE条件中用=描述相等条件，而列名 IN 列表可描述成员关系，而NOT IN可描述非成员关系

JOIN ON

在制作课表时，我们必须将课程表等表格匹配拼接，而JOIN 新表 ON 条件能实现匹配连接这一功能，不使用JOIN，而在FROM中写多个表，将默认采用笛卡尔积的形式生成n*m的表格。

• JOIN: 匹配连接，只保留完全匹配的行
• LEFT JOIN:匹配连接，保留左边全部的行，右边若未匹配则用NULL填充
• RIGHT JOIN: 与上类似，保留右边全部的行
• FULL JOIN:匹配连接，只要有一侧匹配，则保留行，未匹配处NULL填充

值得一提的是，在多表条件查询时，往往JOIN ON的效率比WHERE高，因为JOIN是直接匹配建新表，而WHERE是先做笛卡尔积再筛选。

查询实战

按时间升序查询Jill教的课程

SELECT C.id,course,room,teacher,time 
FROM  classroom.course AS C,classroom.room AS R,classroom.teacher AS T 
WHERE teacher="Jill" AND roomid=R.id AND teacherid=T.id 
ORDER BY time ASC;

查询结果如下

按教室id降序查询时段2的空教室

SELECT * 
FROM classroom.room
WHERE id NOT IN
(SELECT roomid 
FROM classroom.course
WHERE time=2)
ORDER BY id DESC;

按教室id升序查询课表

SELECT R.room,C1.course AS L1,C2.course AS L2,C3.course AS L3,C4.course AS L4,C5.course AS L5
FROM classroom.room AS R
LEFT JOIN classroom.course AS C1
ON C1.roomid=R.id AND C1.time=1
LEFT JOIN classroom.course AS C2
ON C2.roomid=R.id AND C2.time=2
LEFT JOIN classroom.course AS C3
ON C3.roomid=R.id AND C3.time=3
LEFT JOIN classroom.course AS C4
ON C4.roomid=R.id AND C4.time=4
LEFT JOIN classroom.course AS C5
ON C5.roomid=R.id AND C5.time=5
ORDER BY R.id ASC;

其他

由于有外键关联，当删除教室或教师时，对应的课程也将删除。加入课程时，若roomid或teacherid再对应表中找不到，则无法成功加入课程。
至于教室时间冲突，可以交给前端页面，先查询课表，若某项为NULL才提供占用教室的按钮，这样可通过前端保证无冲突。
至于并发安全等问题，由于个人尚未系统学习，欢迎大佬指点，谢谢！