SQL集DDL、DML、DCL于一体。
核心动词
- 查询:SELECT
- 定义:CREATE,DROP,ALTER
- 操纵:INSERT,UPDATE,DELETE
- 控制:GRANT,REVOKE
视图:一个或几个基本表导出的表。只存放视图 的定义,不存放视图的数据,视图是虚表。
1. 数据定义
创建:CREATE;删除:DROP
- 模式定义:SCHEMA
- 表定义:TABLE
- 视图定义:VIEW
- 索引定义:INDEX
1.1 模式
1.1.1 模式定义
CREATE SCHEMA TEST
AUTHORIZATION TEST
CREATE TAB1(
COL1 SMALLINT,
COL2 INT,
);
1.1.2 模式删除
DROP SCHEMA <模式名> <CASCADE | RESTRICT>
CASCADE
:级联。递归删除所有对象RESTRICT
:限制。若定义了下属对象,拒绝执行。
DROP SCHEMA ZHANG CASCADE;
1.2 基本表
1.2.1 基本表的定义
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
SSex CHAR(2),
Sage SMALLINT,
SDept CHAR(20)
);
定义Cpno(先修课)为Course的外码,指向Cno
CREATE TABLE Course(
Cno CHAR(9) PRIMARY KEY,
Cname CHAR(20),
Cpno CHAR(4),
CCredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);
主码由两个属性,定义Sno和Cno为外码
CREATE TABLE SC(
Sno CHAR(9),
Cno CHAR(9),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES S(Sno),
FOREIGN KEY (Cno) REFERENCES C(Cno),
)
1.3 模式和表
一模式包含多个表。定义基本表的所属模式
- 法一:
CREATE TABLE "模式名".Stu(...);
显式定义 - 法二:创建模式时创建表
- 法三:设置所属模式
1.4 修改基本表
ALTER TABEL Student ADD COLUMN S_time DATE;
ALTER TABLE X ALTER COLUMN Sage INT;
ALTER TABLE Course ADD UNIQUE(Cname);
1.5 删除基本表
DROP TABLE <表明> [RE | CA]
1.6 建立索引
CREATE UNIQUE INDEX Stusno ON S(Sno);
CREATE UNIQUE INDEX Coucno ON C(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
-
修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>
-
删除索引
DROP INDEX <索引名>
2. 数据查询
SELECT [ALL | DISTINCT] <目标列>[, <目标列>...]
FROM <表名或视图名>[, <表名或视图名>... ]
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
1. 单表查询
目标列不仅可以是属性列,也剋也是表达式
SELECT Sname, DATE(YEAR)-Sage
FROM S;
SELECT Sname NAME, DATE(YEAR)-Sage BIRTH, LOWER(Sdept) DEPARTMENT
FROM S;
DISTINCT
去掉表中重复行
常见查询条件
SELECT DISTINCT Sno
FROM SC
WHERE GRADE < 60;
SELECT Sname, Sdept, Sage
FROM S
WHERE Sage NOT BETWEEN 20 AND 23;
字符匹配 ESCAPE
[NOT] LIKE '<字符串>' [ESCAPR '<换码字符串>']
- %:任意长度字符串
- _:一个字符
SELECT Sname, Sno
FROM S
WHERE Sname LIKE "_阳%";
SELECT *
FROM Course
WHERE Cname LIKE "DB\_%i_ _" ESCAPE "\";
AND 优先级 高于 OR
ORDER BY
SELECT *
FROM S
ORDER BY Sdept ASC, Sage DESC;
聚集函数
COUNT,SUM,AVG,MAX,MIN
SELECT COUNT(DISTINCT Sno)
FROM SC;
SELECT AVG(GRADE)
FROM SC
WHERE Cno ='1';
SELECT SUM(C.Credit)
FROM SC, C
WHERE SC.Sno='2...' AND SC.Cno=C.Cno;
GROUP BY
细化聚集函数作用对象。对查询分组后,聚集函数将分别用于每个组。
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(GRADE) >= 90;
2. 连接查询
等值连接
SELECT S.*, SC.*
FROM S, SC
WHERE S.sno = SC.sno;
SELECT S.sno, S.sname
FROM S, SC
WHERE S.sno = SC.sno AND
SC.cno=2 AND
SC.grade > 90;
自身连接
SELECT SC1 cno, SC2.cpno
FROM SC SC1, SC SC2
WHERE SC1.Cpno=SC2.Cno;
外连接
指定表为连接主体,不满足连接条件的一并输出
SELECT S.*, SC.*
FROM S LEFT OUT JOIN SC
ON S.sno=SC.sno;
3. 嵌套查询
子查询不能使用ORDER_BY
- 不相关子查询:子查询不依赖父查询;由内向外,子查询结果用于建立父查询查询条件
- 相关子查询:依赖。取外层查询表中的第一个元组,根据它于内层查询相关的属性值处理内层查询。若WHERE子句返回值为真,则元组放入结果表。
1. 代有IN谓词
此为 不相干子查询
SELECT *
FROM S S1
WHERE S1.Sdept IN ( // 不能用=,因为返回的是个表
SELECT S2.Sdept
FROM S S2
WHERE S2.Sname='李晨'
);
SELECT S.Sno, S.Sname
FROM S
WHERE S.Sno IN(
SELECT SC.SNO
FROM SC
WHERE SC.Cno IN(
SELECT C.Cno
FROM C
WHERE C.Cname='信息系统'
)
);
2. 代有比较运算符
只有确切知道内层返回单值时,才能使用比较运算符
(>, <, =, >=, <=, !=或<>)
SELECT SC1.sno, SC2.cno
FROM SC SC1
WHERE SC1.grade >= (
SELECT AVG(grade)
FROM SC SC2
WHERE SC1.sno = SC2.sno
);
3. 代有ANY或ALL谓词的子查询
- 必须与比较运算同时使用
SELECT S.SNO, S.Sage
FROM S
WHERE S.dept!='CS' AND
S.Age < ANY(
SELECT S2.age
FROM S S2
WHERE S2.dept='CS'
);
代有EXIST的查询
带有EXIST的子查询不返回任何数据,只产生T和F。
内层查询结果为空,返回False;
内层查询结果非空,返回True;
由EXIST引出的子查询,目标列通常使用 ∗ * ∗,因为列明无意义。NOT EXIST相反
SELECT S.Sname
FROM S
WHERE EXISTS (
SELECT *
FROM SC
WHERE S.Sno=SC.sno AND
SC.cno = '1'
);
SELECT S.sname
FROM S
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.cno='1' AND
SC.sno = S.sno
);
SELECT *
FROM S S1
WHERE EXISTS (
SELECT *
FROM S S2
WHERE S1.sdept = S2.sdept AND
S2.sname = '刘晨'
);
实现全称量词
SELECT sname
FROM S
WHERE NOT EXISTS(
SELECT *
FROM C
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE S.sno=SC.sno AND
C.cno=SC.cno
)
)
实现逻辑蕴含
- p:学生201215122选修了课程y
- q:学生x 选修了课程y
SELECT SC1.sno
FROM SC SC1
WHERE NOT EXISTS(
SELECT *
FROM SC SC2
WHERE SC1.sno=SC2.sno AND NOT EXISTS(
SELECT *
FROM SC SC3
WHERE SC3.sno=SC1.sno AND
SC3.cno=SC2.cno
)
)
4. 集合查询
UNION并、INTERSECT交、EXCEPT差
要求列数必须相同,对应数据类型也相同
SELECT *
FROM S
WHERE S.dept='CS'
UNION
SELECT *
FROM S
WHERE Sage<=19;
SELECT *
FROM SC
WHERE SC.cno='1'
INTERSECT
SELECT *
FROM SC
WHERE SC.cno='2';
不同属性的与可以用and代替,相同属性不可以。或、差 同理
5. 基于派生表的查询
子查询出现在FROM语句中
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_cs.sno AND
SC.grade >= Avg_sc.avg_grade;
5. 数据更新
5.1 插入数据
1. 插入元组
INSERT
INTO <表名> [(<属性列1> [, (属性列2)])] // 小括号
VALUES (<常量1> [, <常量2>]...)
属性列可以不指定,但要顺序一致。
- VALUES子句提供值的个数,值的类型
INSERT
INTO Stu (Sno, Sname, Ssex, Sdept, Sage)
VALUES (201215128, 陈东, 男, IS, 18)
INSERT INTO Student
VALUES (...)
2. 插入子查询结果
INSERT
INTO <表> [(属性列)]
子查询
- 子查询SELECT列 和 INTO匹配
1. 建表
CREATE TABLE RESULT(
Sdpet CHAR(20),
avg_age SMALLINT
);
2. 查询插入
INSERT INTO RESULT(Sdept, avg_age)
SELECT sdept, AVG(Sage)
FROM S
GROUP BY Sdept;
5.2 修改数据
UPDATE <表名>
SET <列名>=<表达式> [, <列名>=<表达式> ]
[WHERE <条件>]
- SET子句给出表达式的值用于取代相应属性列
UPDATE SC
SET grade=0
WHERE sno in (
SELECT sno FROM Stu
WHERE Sdept='CS'
);
5.3 删除数据
DELETE
FROM <表名>
[WHERE ...];
- 删除WHERE查询的元组。缺省为删除全部元组,但是保留表定义
6. 空值的处理
SELECT * FROM Stu
WHERE Sname IS NULL OR Sage IS NULL OR ...
1. 空值运算
算术运算为空值,比较运算为UNKNOWN(可能T可能F)。
7. 视图
- 虚表,是导出的表
- 仅存放虚表的定义
- 视图数据随基表变化
1. 定义视图
视图建立
CREATE VIEW <视图名> [(列名)]
AS <子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION
:更新视图时要满足视图定义的条件表达式- 视图的属性列名:全部省略 或 全部指定(含聚集函数或同名)
CREATE VIEW IS_VIEW
AS SELECT * FROM Stu
WHERE Sdept='IS';
CREATE VIEW IS_VIEW
AS SELECT * FROM Stu
WHERE Sdept='IS'
WITH CHECK OPTION;
CREATE VIEW IS_S1(Sno, Sname, Grade)
AS
SELECT S.sno, S.sname, SC.grade
FROM S, SC
WHERE S.sno=SC.sno AND
S.dept='IS' AND
SC.cno='1';
CREATE VIEW IS_S2
AS
SELECT *
FROM IS_S1
WHERE grade>=90;
CREATE VIEW BIRTH(Sno, BirthYear)
AS
SELECT Sno, DATE(YEAR)-Sage
FROM Stu;
CREATE VIEW Stu_Avg(Sno, avg_grade)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
CREATE VIEW Grils
AS
SELECT *
FROM Stu
WHERE Ssex='女';
删除视图
DROP VIEW <视图名> [CASCADE]
- 删除基表时,其导出的所有VIEW都必须显式的删除
2. 查询视图
- 视图消解转换:将视图查询转换为等价的基本表查询。缺陷:直接转换不能生成正确的查询
3. 更新视图
UPDATE IS_Student
SET Sname='刘晨'
WHERE Sno='20';
INSERT INTO IS_S
VALUES (...)
DELETE FROM IS_Student
WHERE Sno='20';
- 有些视图不能更新(含聚集函数),如平均成绩视图。因为对视图的更新不能转换成对基本表的更新
不能更新的情况
- 两个基本表
- 字段来自常数
- 字段来自聚集函数
- 视图定义由GROUP , DISTINCT
- 嵌套查询
4. 视图的作用
- 简化用户操作
- 多种角度看待数据
- 逻辑独立性
- 安全保护
- 更清晰的表达查询