SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
分组后聚集函数将作用于每一个组,即每一组都有一个聚集函数
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3;
连接查询
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;
查询每个学生及其选修课程的情况
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,COURSE SECOND
WHERE FIRST.Cpno=SECOND.Cno;
自身连接是要为表取两个不同的名字
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
左外连接
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
嵌套查询
SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno='2');
ANY | ALL
SELECT Sname,Sage
FROM Student
WHERE Sage
集合查询
[UNION|INTERSECT|EXCEPT]
SELECT * FROM Student WHERE Sdept='CS'
UNION
SELECT * FROM Student WHERE Sage<=19;
基于派生表的查询
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;
数据更新
插入数据
INSERT
INTO <表名> [(<属性列1> [,<属性列2>] ...)]
VALUES (<常量1>[,<常量2>] ...);
修改数据
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>] ...
[WHERE <条件>];
删除数据
DELETE
FROM <表名>
[WHERE <条件>];
空值的处理
IS NULL|IS NOT NULL
UNIQUE
视图
CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student WHERE Sdept='IS'
WITH CHECK OPTION;
以后对该视图进行插入、修改、删除操作时,DBMS会自动加上Sdept='IS'的条件
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND Student.Sno=SC.Sno AND SC.Cno='1';
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
DROP VIEW <视图名> [CASCADE];
UPDATE 有一些视图是不能更新的
SELECT 与表操作相同
授权
GRANT <权限>[,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
SELECT|DELETE|ALL PRIVILEGES ...
REVOKE <权限>[,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
FROM <用户>[,<用户>]...[CASCADE|RESTRICT];
CREATE USER [WITH][DBA|RESOURCE|CONNECT];
CREATE ROLE <角 {MOD}名>
AUDIT ALTER,UPDATE
ON SC;
NOAUDIT ALTER,UPDATE
ON SC;