数据库 2：SQL

数据库结构的创建 #

创建数据库 #

CREATE DATABASE <NAME>;

创建表 #

CREATE TABLE <NAME> (<FIELD 1> <DATA TYPE>, ...);

数据的插入、更新与删除 #

增加数据 #

INSERT INTO <TABLE NAME>(<FIELD 1>, <FIELD 2>) VALUES (<VALUE 1>, <VALUE 2>);

使用合成的元组插入数据，例如：将学生的各科均分插入一个新的表，可以使用下面的语法：

INSERT INTO St(S#, Sname, avgScore)
SELECT S#, Sname, AVG(Score) FROM Student, Score
WHERE Student.S# = Score.S#
GROUP BY Student.S#

删除数据 #

DELETE FROM <TABLE NAME> [WHERE <CONDITION>]

如果不写 WHERE 子句，就会删除表中所有的元组。

更新数据 #

UPDATE <TABLE NAME> SET <FIELD> = <EXPRESSION> WHERE <CONDITION>

如果不写 WHERE 子句，就会更新所有的元组。

数据的查询 #

基础查询 #

SELECT <FIELD 1>, <FIELD 2> , ...
FROM <TABLE 1>, <TABLE 2> AS <ALIAS 2>, ...
[WHERE <CONDITION>]

对应关系代数的选投连操作。

• 使用 LIKE 实现模糊搜索，如 WHERE name LIKE "Hans%" 匹配所有名字以 Hans 开头的人。

• 使用 IN 实现子查询，即将一个子 SQL 语句的结果作为条件。

• 使用 EXISTS 实现复杂的嵌套。EXISTS 会在其之后的查询有结果时返回 TRUE。下面的代码可以选出「选修了 001 老师所有课的学生」。

  SELECT Sname FROM Student
  WHERE NOT EXISTS (
    SELECT * FROM Course
    WHERE Course.T# = 001 AND NOT EXISTS (
      SELECT * FROM SC WHERE S# = Student.S# AND C# = Course.C#
  ));
  

分组查询 #

使用 GROUP BY 将检索到的元组按某一条件分类，以调用聚集函数。

求每个学生各科目平均分：

SELECT S#, AVG(SCORE) FROM StudentCourse
GROUP BY S#;

使用 HAVING 在分组时过滤条件。求 10 人以上不及格的课程：

SELECT C# FROM StudentCourse
WHERE Score < 60
GROUP BY C#
HAVING COUNT(*) > 10;

内外连接 #

当然可以只使用 WHERE 子句实现多表连接。假设现在有表 Student（包含学号 S# 和姓名 Name）以及表 Grade（包含学号 S# 和成绩 Score），最朴素的连接方式：

SELECT Name, Score
FROM Student S, Grade G
WHERE S.S# = G.S#

也可以使用 INNER JOIN 进行连接。

SELECT Name, Score
FROM Student INNER JOIN Grade
ON Student.S# = Grade.G#

这种连接方式为内连接，它只会返回两表中共有的匹配数据。而外连接则不同，它以一个表为主体，用另外一个表的数据去扩充主表——如果主表中某个数据（例如，学号）在另外这个表中没有，那么对应的字段用 NULL 填充。

LEFT OUTER JOIN 以左侧的表为主表，称左外连接；RIGHT OUTER JOIN 称右外连接。

​

视图 #

视图即将某种查询固化，在数据库中只存储导出视图的「公式」，视图内容在运行时动态产生。

CREATE VIEW <VIEW NAME> 
AS <SUBQUERY>

权限 #

授权 #

GRANT <PRIV>
ON <TARGET>
TO <USER>

其中：

• <PRIV> 是权限类型，用 ALL PRIVILEGES 表示所有权限，或者具体到 SELECT 或 INSERT 等子权限。
• <TARGET> 是允许操作的目标，可以是 TABLE 加上表名，也可以是视图名。
• <USER> 是受权用户，用 PUBLIC 给所有用户授权。

取消授权 #

REVOKE <PRIV> ON <TARGET> FROM <USER>

结构与前者无异。