数据库学习之路----六

自身连接

例题35 查询与刘晨在同一个系学习的学生
SELECT S1.*
FROM Student S1，Student S2
WHERE S1.Sdept = S2.Sdept AND
S2.Sname = '刘晨'

外连接
实验
(1)查询所有学生的学号和姓名

select Sno , Sname
from Student

(2)查询选修了“数据库原理”的学生的学号和姓名

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno =SC.Sno ANd
SC.Cno = Course.Cno AND
Course.Cname ='数据库原理'

用嵌套查询
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(
SELECT Sno
FROM SC
WHERE Cno IN
(
SELECT Cno
FROM Course
WHERE Cname='数据库原理'
)
);

（３）查询全体学生姓名，出生年份，和所在系，并用小谢字幕表示所有系名，并给各列指定列名。Ｌｏｗｅｒ（ｓｄｅｐｔ）
（４）查询有多少学生的数据库原理课程成绩不合格

SELECT Sno, 
COUNT(*) as 修课门数
FROM SC 
WHERE 
(select Grade
from SC
Where Cno =
(Select Cno 
from Course 
Where Cname = '数据库原理'
)
)<= 60 
GROUP BY Sno 
HAVING COUNT(*) >= 1 

select
学生成绩表.学号，学生姓名
from
学生情况表，学生成绩表
where
成绩<60
and
学生情况表.学号=学生成绩表.学号

（５）查找所有姓李的学生的姓名，学号和性别

select Sname,Sno,Ssex
from Student
 where Sname like '李%'-

并操作
查询cs学生并且年龄小于19岁

select *
from Student
where Sdept = 'CS'
UNION
select *
from Student
where Sage <=19;

查询选修了1号课程与2号课程学生的学号
select Sno
from SC
where Cno = '1'
UNION
select Sno
from SC
where Cno ='2';

q求交集
选修1号课程和5号课程学生学号交集
select Sno
from SC
where Cno = '1'
Intersect
select Sno
from SC
where Cno ='5';

嵌套查询
select Sno
from SC
where Cno = '1' AND Sno IN
(
select Sno
from SC
where Cno ='5'
)

select DISTINCT Sname
from Student
where Sname = (select Tname
from Teacher)

查询计算机科学学生与年龄不到鱼19岁学生的差集

select * from Student where Sdept = 'CS'
EXCEPT
Select *from Student where Sage <=10;
＃嵌套查询
SELECT DISTINCT　Sname
FROM Student 
WHERE Sname NOT IN
(SELECT Tname 
FROM Teacher
);

基于派生表的查询
找出每个学生超过他选修课程平均成绩的课程号
select Sno,Cno
from SC,(SELECT Sno,Avg(Grade)
FROM SC
GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade)
where SC.Sno = Avg_sc.avg_sno and SC.Grade >=Avg_sc.avg_grade

查询所有选修了１号课程的学生姓名
SELECT Sname
FROM Student,
(SELECT Sno FROM SC WHERE Cno = '1') AS SC1
WHERE Student.Sno = SC1.Sno;

插入子查询结果
对每一个系，求学生平均年龄，并把结果存入数据库
create table deptage
(
Sdept char (15)  ,
Average SMALLINT  
)

select Sdept,avg(Sage)
from Student
group by Sdept

INSERT
INTO Deptage(Sdept, Average)
	select Sdept,avg(Sage)
	from Student
	group by Sdept;

将计算机系学生成绩置为０
update SC
set grade = 0
where sno in
(
select Sno
from Student
where Sdept = 'CS'
)
解法２
update SC
set grade = 0
where 'CS' =
(
select Sdept
from Student
where Student.Sno = SC.Sno
)

删除操作
DELECT
FORM SC
WHERE Sno IN (
select Sno
from Student
Where Sdept = 'CS'
)


DELECT
FORM SC
WHERE 'CS' = (
select Sdept
from Student
Where Student.Sno = SC.Sno
)

查询选修１号课程且不及格学生的以及缺考学生的学号
SELECT Sno
from SC
where Cno= '1' AND (Grade <99 OR Grade IS NULL)

视图
必须指定列名
１．如果ＡＳ＜子查询＞后面出现聚集函数或是表达式
２．多表连接时，选出几个同名列作为视图的属性
３．如果需要启用新的列名的时候
CREATE VIEW

AS ＜子查询＞
[WITH CHECK OPTION]


CREATE VIEW IS_Student
	AS
	SELECT Sno,Sname,Sage
	from Student
	Where Sdept = 'IS'

CREATE VIEW BS_S(Sno,Sname,Sbirth)
	AS
	SELECT Sno,Sname,2021-Sage
	from Student

CREATE VIEW S_G(Sno,Gavg)
	AS
	SELECT Sno,AVG(Grade)
	from SC
	GROUP BY Sno;

建立ｃｓ系选修了１号课程和成绩的学生的视图
CREATE VIEW IS_S1(Sno,Sname,Grade)
	AS
	SELECT Student.Sno,Sname,Grade
	from Student,SC
	where Sdept = 'CS' and Student.Sno = SC.Sno AND SC.Cno = '1';

建立ｃｓ系选修了１号课程并且成绩在９０分以上的学生的视图
CREATE VIEW IS_S2
	AS
	SELECT Sno,Sname,Grade
	from IS_S1
	where Grade >=90;

删除视图
drop view IS_Student1

在信息系学生视图里找出年龄小于２０岁的学生的学号和年龄
Select Sno ,Sage
FROM  IS_Student
Where Sage<20;

视图消解法
Select Sno ,Sage
FROM  IS_Student
Where Sage<20　and Sdept = 'IS';

查询信息系选修了１号课程的学生的学号和姓名
Select IS_Student.Sno ,Sname
FROM  IS_Student,SC
Where IS_Student.Sno = SC.Sno AND SC.Cno = '1';

聚集函数不能用ｗｈｅｒｅ聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。
Select Sno ,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG (Grade)>=90

向信息系学生视图插入一个新的学生记录
insert  
into IS_Student 
values('95029','赵新',20)
insert  
into student(Sno, Sname,Sage,Sdept)
values('95029','赵新',20)

select Sno,Cno
Frem SC A
where grade =
(
select max(Grade)
from SC B
where B.Sno = A.Sno
)