子查询:
不相关子查询:内层查询是外层查询的条件,内层查询执行一次。
相关子查询:
exists 子查询:首先外层查询取出一条记录,带入内层查询,看内层查询是否有结果,如果有就返回
true,相当与外层查询的条件为true,取出的记录就放到结果集中。
然后,取出下一条记录,重复上面的过程。
查找没有一门课他不选的学生的姓名。
查找学号为081101没有选修的课程。
select课程名 from kc
where not exists
(select * from xs_kc
where 课程号=kc.课程号 and 学号='081101');
查找选修了全部课程的学生姓名
select sname from student
where sno in(
select sno from sc group by sno
having count(*)=(select count(*) fromcourse)
)
作为特例,可以将一个表与它自身进行连接,称为自连接。若要在一个表中查找具有相同列值的行,则可以使用自连接。使用自连接时需为表指定两个别名,且对所有列的引用均要用别名限定。
【例4.25】查找XSCJ数据库中课程不同、成绩相同的学生的学号、课程号和成绩。
SELECT a.学号,a.课程号,b.课程号,a.成绩
FROM XS_KC AS a JOIN XS_KC AS b
ON a.成绩=b.成绩AND a.学号=b.学号AND a.课程号!=b.课程号;
执行结果为:
如果要连接的表中有列名相同,并且连接的条件就是列名相等,那么ON条件也可以换成USING子句。USING(column_list)子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。其中column_list为两表中相同的列名。
【例4.26】查找KC表中所有学生选过的课程名。
SELECT课程名
FROM KC INNER JOIN XS_KC
USING (课程号);
说明:查询的结果为XS_KC表中所有出现的课程号对应的课程名。
(2)外连接
指定了OUTER关键字的连接为外连接。
外连接包括:
● 左外连接(LEFTOUTER JOIN):结果表中除了匹配行外,还包括左表有的但右表中不匹配的行,对于这样的行,从右表被选择的列设置为NULL。
● 右外连接(RIGHTOUTER JOIN):结果表中除了匹配行外,还包括右表有的但左表中不匹配的行,对于这样的行,从左表被选择的列设置为NULL。
● 自然连接(NATURALJOIN):自然连接还有自然左外连接(NATURAL LEFT OUTER JOIN)和自然右外连接(NATURAL RIGHTOUTER JOIN)。NATURAL JOIN的语义定义与使用了ON条件的INNER JOIN相同。
其中的OUTER关键字均可省略。
【例4.27】查找所有学生情况及他们选修的课程号,若学生未选修任何课,也要包括其情况。
SELECT XS.* , 课程号
FROM XS LEFT OUTER JOIN XS_KC ON XS.学号= XS_KC.学号;
说明:若本例不使用LEFT OUTER JOIN,则结果中不会包含未选任何课程的学生信息。使用了左外连接后,本例结果中返回的行中有未选任何课程的学生信息,相应行的课程号字段值为NULL。
【例4.28】查找被选修了的课程的选修情况和所有开设的课程名。
SELECT XS_KC.* , 课程名
FROM XS_KC RIGHT JOIN KC ON XS_KC.课程号= KC.课程号;
说明:本例执行时,若某课程未被选修,则结果表中相应行的学号、课程号和成绩字段值均为NULL。
【例4.29】使用自然连接实现例4.22中相同的结果。
SELECT DISTINCT 课程名, XS_KC.课程号
FROMKC NATURAL JOIN XS_KC;
说明:SELECT语句中只选取一个用来连接表的列时,可以使用自然连接代替内连接。用这种方法,可以用自然左外连接来替换左外连接,自然右外连接替换右外连接。
注意:外连接只能对两个表进行。
(3)交叉连接
指定了CROSS JOIN关键字的连接是交叉连接。
在不包含连接条件,交叉连接实际上是将两个表进行笛卡儿积运算,结果表是由第一个表的每行与第二个表的每一行拼接后形成的表,因此结果表的行数等于两个表行数之积。
在MySQL中,CROSS JOIN从语法上来说与INNER JOIN等同,两者可以互换。
【例4.30】列出学生所有可能的选课情况。
SELECT 学号, 姓名, 课程号, 课程名
FROM XS CROSS JOIN KC;
另外,STRAIGHT_JOIN连接用法和INNER JOIN连接基本相同。不同的是,STRAIGHT_JOIN后不可以使用USING子句替代ON条件。
【例4.31】使用STRAIGHT_JOIN连接实现例4.22中相同的结果。
SELECTDISTINCT 课程名, XS_KC.课程号
FROMKC STRAIGHT_JOIN XS_KC
ON (KC.课程号=XS_KC.课程号);
LIMIT子句是SELECT语句的最后一个子句,主要用于限制被SELECT语句返回的行数。
语法格式:
LIMIT {[offset,] row_count | row_countOFFSET offset}
说明:
语法格式中的offset和row_count都必须是非负的整数常数,offset指定返回的第一行的偏移量,row_count是返回的行数。例如,“LIMIT 5”表示返回SELECT语句的结果集中最前面5行,而“LIMIT 3,5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。
【例4.66】查找XS表中学号最靠前的5位学生的信息。
SELECT 学号, 姓名, 专业名, 性别, 出生日期, 总学分
FROMXS
ORDERBY 学号
LIMIT5;
查询结果: