· 我们知道,查询的时候的同一个条件不能重叠,比如下面这一句就是错的,C#的查询条件出现了两次。
SELECT DISTINCT `S#` FROM `SC` WHERE `C#`="001" AND `C#`="002";
· 但是我们难免会遇到必须要使用复合条件的情况,这样我们就自然而然的想到之前学过的表连接了。
· 在SQL中,表连接的语法如下:
SELECT `列名` [, `列名2`,...] FROM `表名1`, `表名2`, ... WHERE 检索条件;
· 等值连接是比较常用的连接方式,我们用一个例子来理解一下。
· 有以下三张表,需要查询每个学生及其选修课的情况:
SELECT `Student`.*, `SC`.* FROM `Student`, `SC` WHERE `Student`.`S#` = `SC`.`S#`;
· 需要注意,因为Student和SC表里面都有S#这一列,因此要带上表名来表示是哪个表里的S#。
· 其实,第二行就相当于进行笛卡尔积,第三行就是对笛卡尔积的结果进行筛选,因此必须要有连接条件。
· 上面的查询的结果是这样的:
· 可以看到出现了两列S#,如果我只想保留一列,那就在查询结果上改一改即可:
· 如果不写查询条件的话,就会直接出笛卡尔积:
SELECT * FROM `Student`, `SC`;
· 当我们需要自己和自己连接的时候,就涉及到一个改名操作了。
· 我们拿一开始的问题做例子,在下面的表中筛选出即学过001也学过002课程的学生。
SELECT `SC`.`C#` FROM `SC`, `SC` AS `SC1` # 改名,避免歧义 WHERE `SC`.`S#` = `SC1`.`S#` AND `SC`.`C#`="001" AND `SC1`.`C#`="002";
· 等值连接还能写成更容易阅读的形式:
SELECT `SC`.`C#` FROM `SC` INNER JOIN `SC` AS `SC1` # 这种写法能让人一看就懂 ON `SC`.`S#` = `SC1`.`S#` WHERE `SC`.`C#`="001" AND `SC1`.`C#`="002";
· 我们来看一个例子,有一张Employee表,请找出工资比自己的经理多的人:
SELECT `name` as `Employee` # 这个改名是因为输出规定 FROM Employee INNER JOIN Employee as `e` # 这个改名是为了避免歧义 ON Employee.`id` = e.`manaerId` WHERE Employee.`salary` > e.`salary`;
· 除了等值连接之外,还支持左外连接和右外连接,但是不支持全外连接(后面用其他方法实现)。
· 我们来看一个例子,有一张Employee表和Bonus表,请找出奖金小于1000元的人:
SELECT `name`, `bonus` FROM `Employee` LEFT JOIN `Bonus` ON `Employee`.`empID` = `Bonus`.`empID` WHERE `bonus` IS NULL OR `bonus` < 1000; # 没奖金也算!
· 注意我们要使用左连接,因为要保留所有的人!
· 最后看一个例子:
SELECT `Peoject`.`project_id` AVG(`experience_years`) AS `average_years` FROM `Project` INNER JOIN `Employee` ON `Project`.`employee_id` = `Employee`.`employee_id` GROUP BY `Project`.`project_id`;
· 首先,我们把一个查询语句称为一个查询块:
SELECT 列名1 [[, 列名2]...] FROM 表名 [WHERE 检索条件];
· 如果把一个查询块放到WHERE子句或HAVING子句里面,这样的查询就被称为嵌套查询:
SELECT `Sname` # 外层查询,父查询 FROM `Student` WHERE `S#` IN ( SELECT `S#` # 内层查询,子查询 FROM `SC` WHERE `C#` = "002" );
· 注意,子查询要使用括号括起来;两个查询要用IN来连接,子查询的结果要和父查询的筛选条件的目标在同一个域中。
· 举个例子,有一张Student表,现在要找出和李勇在同一个系的同学:
SELECT * FROM `Student` WHERE `Sdept` IN ( SELECT `Sdept` FROM `Student` WHERE `Sname` = "李勇" );
· 再看一个例子,在下面三张表中查询选修了“数据库”的学生的学号姓名:
SELECT `Student`.`S#`, `Student`.`Sname` FROM `Student` WHERE `S#` IN ( SELECT `SC`.`S#` FROM `SC` WHERE `SC`.`C#` IN ( SELECT `Course`.`C#` FROM `Course` WHERE `Cname` = "数据库" ) );
· 注意,当子查询返回的值确定是一个的时候,IN可以更换为等号(=),如刚刚到例子可以改为:
SELECT * FROM `Student` WHERE `Sdept` = ( # 这里的IN改为等号 SELECT `Sdept` FROM `Student` WHERE `Sname` = "李勇" );
· 当然,嵌套查询也可以使用比较运算符,但是要注意子查询必须要返回单值,且对象在同一个域中!
· 也可以使用ANY,ALL谓词来修饰子查询,ANY指的是子查询结果中的任意一个值,ALL指的是子查询结果中的所有值。
· 举个例子,在Student表中查询非IS系中比IS中任意一个学生年龄小的学生姓名和年龄:
SELECT `Sname`, `Sage` FROM `Student` WHERE `Sage` < ANY( SELECT `Sage` FROM `Student` WEHRE `Sdept` = "IS" ) AND `Sdept` != "IS";
· 在看一个例子,找出年龄最小的学生的姓名和年龄:
SELECT `Sname`, `Sage` FROM `Student` WHERE `Sage` <= ALL( # 注意是用小于等于,不然就筛不出任何一个结果了 SELECT `Sage` FROM `Student` ); # 当然还有另一种写法 SELECT `Sname`, `Sage` FROM `Student` WHERE `Sage` <= ( SELECT MIN(`Sage`) FROM `Student` );
· 我们知道了嵌套查询可以在WHERE或HAVING中嵌套,但其实还可以在FROM中嵌套。
· 用户自己创建的表叫做基本表,SELECT的结果叫做派生表。
· 有时候我们可能有这个想法,先把一个表筛选出来作为中间变量,再把它用到一个其他查询中,这个时候派生表就派上用场了。
· 举个例子,在SC表中找出每个学生自己最高分的课程号:
SELECT `S#`, `C#` FROM SC, ( SELECT `S#`, MAX(`Score`) FROM `SC` GROUP BY `S#` ) AS `m`(`m_S#`, `m_score`) # 给派生表取个别名m,里面的两列分别取别名m_S#和m_score WHERE `S#` = `m`.`m_S#` AND `Score` = `m`.`m_score`;
· 我们来分析一下这个查询。
· 首先子查询,查询到了每个学生自己的最高分是多少分;然后给查询出来的表取一个别名;然后再通过等值连接连接原表和派生表,通过分数确定最高分的课程。
· 当然,我们也可以对查询结果进行求交集、并集和做差。
· 我们使用UNION来把两个查询结果进行合并,系统会自动去掉重复元组;使用UNION ALL可以保留重复元组。
· 看个简单的例子,筛选出下表中既选修了001也选修了002课程的同学:
SELECT `S#` FROM `SC` WHERE `C#` = "001" UNION SELECT `S#` FROM `SC` WHERE `C#` = "002";
· 同时,我们也可以使用UNION来实现全外连接:
SELECT * FROM `Student` LEFT JOIN `SC` # 先左外连接 ON `Student`.`S#` = `SC`.`S#` UNION SELECT * FROM `Student` RIGHT JOIN `SC` # 再右外连接 ON `Student`.`S#` = `SC`.`S#`;
· 我们使用INTERSECT将多个查询结果取交集,系统会自动去掉重复元组;使用INTERSECT ALL会保留重复元组。
· 继续上面那张表,这次要选出学了001又学了002课程的学生:
SELECT `S#` FROM `SC` WHERE `C#` = "001" INTERSECT SELECT `S#` FROM `SC` WHERE `C#` = "002";
· 真是太优雅了。
· 我们使用EXCEPT来对查询结果求差,我们通过一个例子来理解。
· 还是刚刚的表,如果我要选出不选002课程的同学,一个很直观的想法是:
SELECT `S#` FROM `SC` WHERE `C#` != "002";
· 但其实这样子会把98040202这个人也筛出来了,因为他选了002和003,即使002被筛出去了,003也被留下来了。
· 这个时候,我们就可以使用求差来避免这个问题:
SELECT DISTINCT `S#` # 先把所有人筛出来,去重 FROM `SC` EXCEPT # 然后去掉 SELECT `S#` # 选过002课程的人 FROM `SC` WHERE `C#` = "002";