sql巩固学习2

https://blog.csdn.net/jdqm2014/article/details/67636547

连接查询是关系型数据库中的主要查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

1.连接查询

1)等值与非等值连接查询

-- 查询每个学生及其选修课程的情况
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;

-- 自然连接
select student.Sno, Sname,Ssex, Sage, Sdept, Cno, Grade
from student, sc
where student.Sno=sc.Sno;

2)自身连接

-- 查询课程的先修课的先修课
select c1.Cno, c2.Cpno
from course c1, course c2
where c1.Cpno=c2.Cno;

3)外连接

-- 查询每个学生及其选修课程的情况
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;

-- 左外连接查询
select student.Sno, Sname, Sage, Ssex, Sdept, Cno, Grade
from student
left join sc on (student.Sno=sc.Sno);

4)复合条件连接 

-- 查询选修了2号可并且成绩在90分以上的学生信息
select student.Sno, Sname
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;

-- 查询选修了2号可并且成绩在90分以上的学生信息
select student.Sno, Sname, Grade
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;

-- 查询每个学生的学号、选修的课程名、成绩
select student.Sno, Cname, Grade
from student,course,sc
where student.Sno=sc.Sno and sc.cno=course.cno;

2.嵌套查询

将一个查询块嵌套在另一个查询块的where子句或者having短语的条件中的查询称为嵌套查询。 

1、在in谓词的子查询

-- 使用嵌套查询查出选修了2号课程的学生的学号
select Sno
from student
where Sno in(
    select Sno
    from sc
    where Cno='2'
);

2、带比较运算符的子查询

-- 查询每个学生成绩超过他平均成绩的课程号
select Sno, Cno
from sc x
where Grade >(
        select avg(Grade)
        from sc y
        where y.Sno=x.Sno);

3、带有any(some)或all谓词的子查询

子查询返回单值时可以用比较运算符，返回多值要用any(有的系统用some)或者all谓词来修饰，而使用any或all谓词修饰是必须同时使用比较运算符，其语义为：

比较运算	语义
> ANY	大于子查询中的某个值
> ALL	大于子查询中的所有值
< ANY	小于子查询中的某个值
< ALL	小于子查询中的所有值
>= ANY	大于等于子查询中的某个值
>= ALL	大于等于子查询中的所有值
<= ANY	小于等于子查询中的某个值
<= ALL	小于等于子查询中的所有值
= ANY	等于子查询中的某个值
= ALL	等于子查询中的所有值(通常没有意义)
!=(或<>) ANY	不等于子查询中的某个值
!=(或<>) ALL	不等于子查询中的所有值

-- 查询其他系中比计算机系某一个学生年龄小的学生姓名和年龄
select Sname, Sage
from student
where Sage < any(
        select Sage
        from student
        where Sdept='CS'
        )
and Sdept !='CS';

-- 用聚集函数查询其他系中比计算机系某一个学生年龄小的学生姓名和年龄
select Sname, Sage
from student
where Sage <(
        select max(Sage)
        from student
        where Sdept='CS'
        )
and Sdept !='CS';

4、带有exists，not exists谓词的子查询

-- 查询选修了1号课的学生姓名
select Sname
from student
where exists(
        select * from sc
        where student.Sno=sc.Sno and Cno='1'
);

-- 查询没有选修了1号课的学生姓名
select Sname
from student
where not exists(
        select * from sc
        where student.Sno=sc.Sno and Cno='1'
);

3.集合查询

集合操作包括并操作（union），交操作（intersect）和差操作(except)。

-- 查询计算机系及年龄不大于19岁的学生 ，不保留重复的元组
select * from student
where Sdept='CS'
union
select * from student
where Sage<=19;

-- 查询计算机系及年龄不大于19岁的学生，保留重复的元组
select * from student
where Sdept='CS'
union all
select * from student
where Sage<=19;

总结：

1.如果两个表通过某些字段有关联，则用直接连接(可加条件)或者外连接。

2.如果某个表的字段之间有父子关系。则用自身连接。

3.嵌套查询用in,not in,比较运算符，exists，not exists。

4.集合查询常用union