数据库_SQL 语言_总结

参考1：数据库系统概论 王珊 第三章、第四章 参考2：https://blog.csdn.net/weixin_43914604/article/details/105217410

3.3 数据定义

3.3.1 模式的定义与删除

定义模式

定义模式实际上定义了一个命名空间。

• 在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

• 在 CREATE SCHEMA 中可以接受 CREATE TABLE，CREATE VIEW 和 GRANT 子句。

  -- CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义>|<视图定义>|<授权定义>];
  CREATE SCHEMA `my_schema` AUTHORIZATION `toulzx`;

• 如果没有指定 <模式名>，那么 < 模式名 > 隐含为 < 用户名 >

  删除模式

  -- DROP SCHEMA <模式名> <CASCADE|RESTRICT>
  DROP SCHEMA `my_schema` RESTRICT
  

• CASCADE(级联)：删除模式的同时把该模式中所有的数据库对象全部删除

• RESTRICT(限制)：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。

  3.3.2 基本表的定义、删除与修改

  定义基本表

  ```plsql — CREATE TABLE <表名> ( — <列名> <数据类型> [<列级完整性约束条件>] — [, …] — [<表级完整性约束条件>] — [, …] — );

CREATE TABLE my_friends_table ( id INT, name CHAR(20) UNIQUE, — name 取唯一值 sex CHAR(1) NOT NULL, alive CHAR(1) DEFAULT ‘Y’, age SMALLINT, tele CHAR(11), PRIMARY KEY (id), FOREIGN KEY (tele) REFERENCES my_tele_table (tele), CHECK (sex LIKE ‘[MF]’) );

- SQL 中 `域` 的概念，通过 `数据类型` 来实现；
- `UNIQUE` = `PRIMARY` + `NOT NULL`
<a name="bIh9F"></a>
#### 模式与表
显式给出模式名 `CREATE TABLE "my_scheme".`my_friends_table`;`<br />创建模式时一起创建表（见 3.3.1）<br />若不指定，默认先找与`用户名`相同的模式名，再找搜索路径列表中定义为 `PUBLIC` 的模式...
<a name="cJaMl"></a>
#### 修改基本表
```plsql
-- ALTER TABLE <表名>
-- -- [ ADD <新列名> <数据类型> [完整性约束] ]
-- -- [ ADD 表级完整性约束 ]
-- -- [ DROP <列名> [RESTRICT | CASCADE ] ]
-- -- [ DROP CONSTRAINT <完整性约束名> [ RESTRICT | CASCADE ] ]
-- -- [ ALTER COLUMN <原列名> <新数据类型> ]
;

ALTER TABLE Student ADD S_entrance DATE;
ALTER TABLE Student ALTER COLUMN Sage INT;

删除基本表

-- DROP TABLE <表名> [ RESTRICT | CASCADE ];

DROP TABLE Student CASCADE;    -- 默认 RESTRICT：有级联（如外码）不删

3.3.3 索引的建立与删除

索引是关系数据库的内部实现技术，属于内模式的范畴。

建立索引

-- CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
-- ON <表名> (
--     <列名> [<次序>] 
--     [, ...] 
-- );

CREATE UNIQUE CLUSTERED INDEX `iStusName` ON `Student`(`Sname`);    
-- UNIQUE 每个索引值对应唯一数据记录
-- CLUSTERED 聚簇索引 7.5.2

CREATE UNIQUE INDEX `SCno` ON `SC` (
    `Sno` ASC,  -- ASC 默认值，升序
    `Cno` DESC
);

• UNIQUE 唯一索引
• CLUSTER 聚簇索引
• 非唯一索引

次序

• ASC 升序，默认值。
• DESC 降序。

  修改、删除索引

  -- ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  -- DROP INDEX <索引名>
  ALTER INDEX SCno RENAME TO SCSno;
  DROP INDEX iStusName;
  

  3.4 数据查询 SELECT

  3.4.1 单表查询

  ```plsql — SELECT [ALL | DISTINCT] — { <目标表达式> [[AS] 列别名] } — FROM — {<表名|视图名> | ()} [[AS] 查询表别名] — [WHERE <条件表达式>] — [GROUP BY <列名1> [HAVING <条件表达式>]] — [ORDER BY {<列名2> [ASC | DESC]}]

SELECT * FROM Student;

— 计算 SELECT DISTINCT — DISTINCT 不显示重复项，默认是 ALL Sname, “Year of Birth”, — 此列的列名和所有值都为 “Year of Birth” (2004 - Sage) AS work_time, — 注意这里的 AS 不是必须的！！！ LOWER(Sdept) — 小写 FROM Student ;

— BETWEEN AND SELECT Sname FROM Student WHERE Sdept <> ‘CS’ — 不等于 AND Sage BETWEEN 20 AND 23 — 下限上线全部包括 ;

— IN SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ( ‘IS’, ‘MA’, ‘CS’ ) — IN 实际上是多个 OR 的组合。 ;

— 字符匹配 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE ‘刘%’ — 匹配多个字符；注意ASCII的汉字占2个，GBK占1个 OR Sname LIKE ‘欧阳‘ — 匹配 1 个字符；注意用到字符本身时需转义
OR Sname LIKE ‘李华’ — 没有通配符时 like 等价 == OR Sname LIKE ‘Q\歪果人’ ESCAPE’ \ ‘ — 使用 \ 作转义字符
;

— IS NULL SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL ;

— ORDER BY SELECT * FROM Student ORDER BY Sdept, Sage DESC ;

— GROUP BY SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 2 ;

```plsql
-- COUNT(*)                                                                    -- 只有它处理空值，其余无视空值
-- COUNT( [DISTINCT | ALL] <列名> )
-- SUM( [DISTINCT | ALL] <列名> )
-- MAX( [DISTINCT | ALL] <列名> )
-- MIN( [DISTINCT | ALL] <列名> )
-- AVG( [DISTINCT | ALL] <列名> )

-- 只能用在 SELECT子句、HAVING子句，不能用在 WHERE子句！！！

-- 求各个课程号及相应选课人数 | 例 3.46
SELECT Cno,COUNT(Sno)                        -- 分组后聚集函数将作用于每一组
FROM SC
GROUP BY Cno
;

-- 查询平均成绩大于等于90的学生学号和平均成绩 | 例 3.48
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90                    -- 只输出满足条件的组
;

3.4.2 连接查询（多表查询）

-- 查询选修课程2且成绩在90分以上的所有学生的学号和姓名 | 例 3.51
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno        -- 连接谓词
            AND SC.Sno ='2'                    -- 其它限定条件
            AND SC.Grade > 90
;

-- 自身连接：取别名
-- 查询每一门课的间接先修课（先修课的先修课）
SELECT c1.Cname, c2.Cpno
FROM Course AS c1, Course AS c2
WHERE c1.Cpno = c2.Cname
;

-- 外连接
-- SELECT *
-- FROM `table_A`
-- < LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL JOIN > `table_B`
-- ON (<条件>)

3.4.3 嵌套查询（子查询）

嵌套进 WHERE、HAVING、FROM 后的查询语句，称为子查询，子查询中不能使用 ORDER BY。

不想关子查询

-- 查询与“刘晨”在同一个系学习的学生 | 例 3.55
SELECT Sname
FROM Student
WHERE Sdept IN(                                                                        -- 这里也可以用`=`
    SELECT Sdept FROM Student WHERE Sname = '刘晨'
)
;

相关子查询

不相关子查询可以分成两个部分，而相关子查询不能简单地把子查询和父查询分成两部分分别执行，它相当于每次取一个外层元组传递给内层查询，x,y 相当于元组变量。

-- 找出每个学生超过他自己选修课程平均成绩的课程号 | 例 3.57
SELECT Sno
FROM SC AS x
WHERE Grade >= (
    SELECT AVG(Grade) 
    FROM SC AS y 
    WHERE x.Sno = y.Sno 
)
;

谓词查询（P107）：
比较运算符只能对单值，对于返回多元组的，可用 ANY | ALL：

• ANY() 只要括号内子查询结果中有一项满足即可
• ALL() 满足括号内子查询的所有结果

具体代码略。
判断 ∈``包含于``=``∩ 时可以考虑用 EXISTS | NOT EXISTS：

• 只会返回真假给 WHERE ，是高效的查询的方法。所以子查询的 SELECT 常接 *
• EXISTS 可以替换所有 IN、比较、ANY | ALL，但不能被他们替换。

  -- 查询与“刘晨”在同一个系学习的学生 | 另解 3.55
  SELECT Sname
  FROM Student AS s1
  WHERE EXISTS (
  SELECT * 
  FROM Student AS s2 
  WHERE s2.Sname = '刘晨'
             AND s1.Sdept = s2.Sdept                            -- 理解每次取一个外层元组传递给内层查询
  )
  ;
  -- 查询选修了全部课程的学生姓名。| 例 3.62
  -- 没有一门课程是他不选修的
  SELECT Sname
  FROM Student
  WHERE NOT EXISTS (
  SELECT *
  FROM Course
  WHERE NOT EXISTS (
     SELECT *
     FROM SC
     WHERE Sno = Course.Sno AND Sno = Student.Sno
  )
  )
  ;
  -- 用连接的方法另解
  SELECT Sname
  FROM Student
  WHERE Sno IN (
  SELECT Sno
  FROM SC
  GROUP BY Sno
  HAVING COUNT(*) = (
     SELECT COUNT(*) FROM Course
  )
  )
  ;
  

  3.44 集合查询（略）

  -- < SELECT ... >
  -- < UNION | INTERSECT | EXCEPT > [ALL]
  -- < SELECT ... >
  

• 并操作 UNION
• 交操作 INTERSECT
• 差操作 EXCEPT

含有 ALL 则不删除重复项。
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

SELECT *
FROM Student
WHERE Sdept= 'CS'

UNION 

SELECT *
FROM Student
WHERE Sage<=19
;

3.45 派生表查询

即在 FROM 后面的查询，派生表必须有原名。

3.5数据更新

插入 INSERT

-- INSERT INTO <表名> [ ( {<属性列>} ) ]
-- VALUES (
--         {<常量>} 
--        )

-- INSERT INTO <表名> [ ( {<属性列>} ) ]
-- <SELECT子查询>

INSERT
INTO  Student
VALUES ('200215126', '张成民', '男', 18, 'CS')
;

INSERT INTO SC(Sno,Cno)
VALUES ('200215128','1');
-- 等价
INSERT INTO SC
VALUES ('200215128','1', NULL);        -- 没有指名属性列时，所有列都要赋值

-- 对每一个系，求学生的平均年龄，并把结果存入数据库 | 例3.72
CREATE TABLE Dept_age (
    Sdept        CHAR(15)
    Avg_age    SMALLINT
);
INSERT INTO Dept_age(Sdept, Avg_age)
    SELECT Sdept, AVG(Sage)
    FROM Student
    GROUP BY Sdept
;

修改 UPDATE

-- UPDATE <表名>
-- SET { <列名> = <表达式> }
-- [WHERE [ <条件> | <SELECT 子查询> ] ]

UPDATE  Student
SET Sage = 22, Sname = "李帅"
WHERE  Sno = '201215121'
; 

-- 将CS系全体学生成绩置0 | 例3.75
UPDATE SC
SET Grade = 0
WHERE Sno IN (
    SELECT Sno FROM Student WHERE Sdept='CS'
)
;

删除 DELETE

-- DELETE FROM <表名>
-- [ <条件> | <SELECT 子查询> ] ][WHERE <条件>]

DELETE
FROM Student
WHERE Sno = '200215128'
;

-- 删除计算机科学系所有选课记录 | 例 3.78
DELETE FROM SC
WHERE Sno IN(
    SELECT Sno FROM Student WHERE Sdept='CS'
)
;

3.6 空值

空值的产生：插入不完整、赋值、外连接。

空值的判断 IS NULL | IS NOT NULL

SELECT Sno FROM SC WHERE Grade IS NULL AND Cno='1';

3.7 视图

• 虚表，是从一个或几个基本表（或视图）导出的表
• 只存放视图的定义，不存放视图对应的数据
• 基表中的数据发生变化，从视图中查询出的数据也随之改变

  3.7.1 创建/删除视图

  ```plsql — CREATE VIEW <视图名> [ ( {<列名>} ) ] — AS <子查询> — [WITH CHECK OPTION];

CREATE VIEW IS_Student — 省略列名就把查到的所有列加入 AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = ‘IS’ WITH CHECK OPTION — 插入操作时会检查是否符合视图定义中子查询的条件 ;

CREATE VIEW IS_S1 (Sno, Sname, Gavg) AS SELECT Student.Sno, Sname, AVG(Grade) FROM Student, SC WHERE Sdept = ‘IS’ AND Student.Sno = SC.Sno AND SC.Cno = ‘1’ ;

```plsql
-- DROP VIEW <视图名> [CASCADE] 

DROP VIEW `IS_S1`;    --     -- 默认有级联不删，增加 CASCADE 会删级联

3.7.2 查询视图

和基本表一致。

3.7.3 更新视图

和基本表一致。
本质就是更新基本表。
请注意，如果无法对应基本表的内容，比如视图中的属性列是计算结果（比如 AVG()），则无法更新视图。

4.2 数据库安全性控制

4.2.4 授权：授予 GRANT 与收回 REVOKE

权限对象类型：

-- GRANT {<权限>}
-- ON {<对象类型> <对象名称>}
-- TO {<用户>}
-- [WITH GRANT OPTION]                    -- 允许用户授权该权限

-- 把对 Student 和 Course 表的所有操作权限授予用户 U2 和 U3 | 例 4.2
GRANT　ALL　PRIVILEGES
ON TABLE Student, Course
TO U2, U3

-- 把对 SC 表的修改学号权限授予所有用户 | 例 4.3、4.4 改
GRANT UPDATE(Sno)
ON TABLE SC
TO PUBLIC

-- REVOKE {<权限>}                            -- 管理员或授权者    
-- ON {<对象类型> <对象名称>}    
-- FROM {<用户>}
-- [RESTRICT | CASCADE]                    -- 默认RESTRICT：拒绝级联权限的取回

4.2.5 角色

角色是权限的集合，方便一次性同等权限的授予。

-- 创建角色
-- CREATE ROLE <角色名>
CREATE ROLE R1

-- 给角色授权
GRANT SELECT,UPDATE,INSERT
ON TABLE Student
TO U1, U2

-- 将角色授予其它角色或用户
-- GRANT {<角色>}
-- TO {<其它角色或用户>}
-- [WITH ADMIN OPTION]                            -- 允许角色/用户授权该权限

-- 角色权限的取回（谁能取回：角色创建者/拥有该角色的 ADMIN OPTION）
-- REVOKE {<角色>}
-- FROM {<其它角色或用户>}

4.3 视图机制

-- 建立计算机系学生的视图，王平老师拥有 SELECT 权限，张明主任拥有全部操作权限。
CREATE VIEW CS_Student AS 
    SELECT * 
    FROM Student
    WHERE Sdept = 'CS'
;
GRANT SELECT ON CS_Student TO 王平;
GRANT ALL PRIVILEGES ON CS_Student TO 张明;