SQL复习

SQL基础

包括：

• 数据查询：SELECT
• 数据定义语言：CREATE、ALTER和DROP
• 数据操纵语言：INSERT、UPDATE、DELETE
• 数据控制语言：GRANT、REVOKE
  

特点：

• 综合统一
• 高度非过程化
• 面向集合
• 既是独立的也是嵌入式语言

三级模式结构

• 外模式：视图、部分基本表
• 模式：基本表
• 内模式：存储文件

基本语句

建表

CREATE TABLE SC(
studentNo char(7) NOT NULL ,
courseNo char(3) NOT NULL ,
score numeric(5,1) DEFAULT 0 NOT NULL
CHECK(score BETWEEN 0.0 AND 100.0),
/*主码由两个属性构成，必须作为表级完整性约束进行定义*/
CONSTRAINT ScorePK PRIMARY KEY (studentNo, courseNo),
/*表级完整性约束条件，studentNo是外码，被参照表是Student*/
CONSTRAINT ScoreFK1 FOREIGN KEY (studentNo)
REFERENCES student(studentNo),
/*表级完整性约束条件，courseNo是外码，被参照表是Course*/
CONSTRAINT ScoreFK2 FOREIGN KEY(courseNo)
REFERENCES course(courseNo)
);

索引

CREATE INDEX ClassBirthIdx
ON Student(classNo, birthday DESC);

通配符

select Cno,Cname,credit from course where
Cname like 'DB\_%设__' escape '\';

排序

常用语句

检索全部学生都选修的课程号和课程名

SELECT CNO,CNAME FROM C WHERE CNO IN(
							SELECT DISTINCT CNO FROM SC GROUP BY CNO 
							HAVING COUNT(SNO) = (SELECT COUNT (*) FROM S) 
							);

检索至少选修Liu老师所教授课程中一门课程的女学生的姓名

SELECT DISTINCT S.SNAME FROM S,C,SC WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO 
AND S.SEX = '女' AND S.SNO IN (
								SELECT DISTINCT SNO FROM C,SC 
								WHERE C.CNO=SC.CNO AND C.TEACHER = 'Liu'
								);

求Liu老师所授课程的每门课程的平均成绩

SELECT CNAME, AVG(GRADE) FROM C,SC WHERE C.CNO=SC.CNO AND TEACHER='shu' GROUP BY CNAME;

统计每个学生选修课程的门数（超过2门的学生才统计）。要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列。

SELECT SNO,COUNT(CNO) FROM SC GROUP BY SNO 
HAVING COUNT(CNO)>2 ORDER BY COUNT(CNO) DESC，SNO ASC;

查询每一门课的间接先修课

select  A.Cno, A.Cname, B.Pre_Cno 
from  Course  A,Course  B
where  A.Pre_Cno =  B.Cno  and  B.Pre_Cno is not null;

查询成绩至少比选修了C02号课程的一个学生成绩低的学生学号

select  Sno  from  SC where grade < any (
select  Grade from SC where Cno = "C02"
)  and  Cno <> "C02";

查询成绩比所有选修了C02号课程的学生成绩低的学生学号

select  Sno  from  SC where grade < all (
select  Grade from SC where Cno = "C02"
)  and  Cno <> "C02"

查询选修了全部课程的学生姓名

SELECT SNAME
FROM Student
WHERE SNO IN (
    SELECT SNO
    FROM SC
    GROUP BY SNO
    HAVING COUNT(DISTINCT CNO) = (SELECT COUNT(*) FROM Course)
);

或

select Sname from Student where not Exists (
select * from course where not Exists (
select * from SC where Sno=Student.Sno and Cno=Course.Cno)
);

查询所在系为自动化的学生以及姓李的学生的基本信息

select * from student where Sdept like '自动化'  
Union  
select * from student where Sname like '李%';

删除平均分在60分到70分之间的同学选课记录

DELETE FROM Score 
WHERE studentNo IN (
                 SELECT studentNo 
                 FROM Score 
                 GROUP BY studentNo
                 HAVING avg(score) BETWEEN 60 AND 70 );