SQL复习

admin2024-05-15  0

SQL基础

包括:

  • 数据查询:SELECT
  • 数据定义语言:CREATE、ALTER和DROP
  • 数据操纵语言:INSERT、UPDATE、DELETE
  • 数据控制语言:GRANT、REVOKE
    SQL复习,在这里插入图片描述,第1张

特点:

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

三级模式结构

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

基本语句

建表

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) 
							);
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'
								);
SELECT CNAME, AVG(GRADE) FROM C,SC WHERE C.CNO=SC.CNO AND TEACHER='shu' GROUP BY CNAME;
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;
select  Sno  from  SC where grade < any (
select  Grade from SC where Cno = "C02"
)  and  Cno <> "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 '李%';
DELETE FROM Score 
WHERE studentNo IN (
                 SELECT studentNo 
                 FROM Score 
                 GROUP BY studentNo
                 HAVING avg(score) BETWEEN 60 AND 70 );
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!