二、连接查询
学生数据库(join on中的on相当于where)
关于外连接
普通连接只把满足条件的元组做输出,而外连接则把属性值为空也显示出来、
Student left outer join SC on ()
--5、选修了“数据库”和“数学”两门课程的学生的基本信息。(为啥sc也要两张,进行自身连接)
--法一
select student.*
from sc a , sc b ,course c, course d,student
where a.cno=c.cno and a.sno=b.sno and b.cno=d.cno and c.cname='数据库' and d.cname='数学'
and a.sno=student.sno and b.sno=student.sno
--法二
select * from student
where sno in (select sno from sc
where cno=(select cno from course
where cname='数据库'))and
sno in (select sno from student
where sno in (select sno from sc
where cno=(select cno from course
where cname='数学')))
错解()
--没结果
select student.*
from sc,course c, course d,student
where sc.cno=c.cno and sc.cno=d.cno and c.cname='数据库' and d.cname='数学'
and sc.sno=student.sno
--(三个不同表连接)、 列出所有课程被选修的详细情况,包括课程号、课程名、学号、姓名及成绩
select course.cno,Cname,sc.sno,sname,grade
from course ,sc ,student
where course.cno=sc.cno and sc.sno=student.sno
order by course.cno
而左外连接
select course.cno,Cname,sc.sno,sname,grade
from (course left join sc on course.cno=sc.cno) left join student on sc.sno=student.sno
order by course.cno
--(表的自身连接) 选修了“数据库”和“数学”两门课程的学生的基本信息(若有附加条件a,b二者都要加)
select a.sno
from sc a , sc b ,course c, course d
where a.cno=c.cno and a.sno=b.sno and b.cno=d.cno and c.cname='数据库' and d.cname='数学'
--查询至少选修了一门间接先行课为“5”号课程的学生姓名(a.cpno=b.cno)
select sname
from course a,course b ,sc c ,student d
where a.cpno=b.cno and b.cpno='5'and a.cno=c.cno and c.sno =d.sno
--关于左连接(left join)、右连接(right join)、全连接(full join)
bookshop库
--统计每月销售额度(year(order_date),month(order_date)可以用来获得年份和月份)
select year(order_date) 年份,month(order_date) 月份,sum(qty*unit_price) 销售金额
from sale_item a,books b,sales c
where a.book_no=b.book_no and a.order_no=c.order_no
group by year(order_date),month(order_date)
order by year(order_date),month(order_date)
等值连接和自然连接
等值连接(连接条件中的运算符为“=”)
SELECT * FROM Student,SC where (Student.Sno=SC.Sno);
自然连接(在等值连接中去除重复的属性列)
SELECT student.sno,sname,cno,grade
FROM Student,SC where (Student.Sno=SC.Sno);