SQL 语法教学

SQL 是非过程化语言：只说明"要什么数据"，不说明"怎么取"。 一条查询语句由若干子句组成，书写顺序与执行顺序不同——这是初学时最容易混淆的地方。 数据库先根据 FROM 确定数据来源，用 WHERE 过滤行，再按 GROUP BY 分组、HAVING 过滤组， 最后才计算 SELECT 中的表达式，并用 ORDER BY 排序。

SELECT [ALL|DISTINCT] <目标列表达式>[, ...]
FROM <表名或视图名>[, ...]
[WHERE <行条件>]
[GROUP BY <列名> [HAVING <组条件>]]
[ORDER BY <列名> [ASC|DESC]];

-- 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
-- 因此：WHERE 中不能出现聚集函数，SELECT 中的列别名也不能在 WHERE 中使用

建表时要同时想清楚三件事：有哪些列、每列的数据类型、以及必须满足的完整性约束。 约束可以直接写在列后面（列级），也可以在所有列定义完之后单独写（表级）。 凡涉及多个列的约束（如多属性主码、外码）只能写成表级。

-- 模式（数据库下的命名空间）
CREATE SCHEMA "S-T" AUTHORIZATION WANG;
DROP SCHEMA "S-T" CASCADE;          -- CASCADE 连带删除 / RESTRICT 有依赖则拒绝

-- 基本表：列级约束
CREATE TABLE Student (
  Sno   CHAR(9)  PRIMARY KEY,                  -- 主码：非空且不重复
  Sname CHAR(20) UNIQUE,
  Ssex  CHAR(2)  CHECK (Ssex IN ('男','女')),
  Sage  SMALLINT NOT NULL,
  Sdept CHAR(20)
);

-- 基本表：表级约束（多属性主码 + 两个外码）
CREATE TABLE SC (
  Sno   CHAR(9),
  Cno   CHAR(4),
  Grade SMALLINT CHECK (Grade BETWEEN 0 AND 100),
  PRIMARY KEY (Sno, Cno),
  FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE,
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

ALTER TABLE Student ADD Sentrance DATE;        -- 增加列
ALTER TABLE Student ALTER COLUMN Sage INT;     -- 修改类型
DROP TABLE Student CASCADE;

-- 索引（系统自动使用，用户不能显式指定）
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE CLUSTERED INDEX Stusname ON Student(Sname);
DROP INDEX Stusno;

单表查询是所有查询的基础。分成四步理解： ①投影（选哪些列，可含计算列与别名）→ ②选择（WHERE 保留哪些行）→ ③排序（ORDER BY）→ ④统计（聚集函数与 GROUP BY）。 空值用 IS NULL 判断，不能用等号。

-- ①投影与计算列
SELECT Sname, 2026 - Sage AS birth_year FROM Student;
SELECT DISTINCT Sdept FROM Student;               -- 去掉重复行

-- ②选择：条件表达式
SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20;
SELECT Sname FROM Student WHERE Sage BETWEEN 20 AND 23;
SELECT Sname FROM Student WHERE Sdept IN ('CS','MA');
SELECT Sname FROM Student WHERE Sname LIKE '刘%';  -- % 任意长度，_ 单个字符
SELECT Sno  FROM SC WHERE Grade IS NULL;           -- 空值判断不能用 = NULL

-- ③排序
SELECT * FROM Student ORDER BY Sdept ASC, Sage DESC;

-- ④统计：聚集函数忽略 NULL
SELECT COUNT(*), COUNT(DISTINCT Sno), AVG(Grade), MAX(Grade) FROM SC;
SELECT Cno, COUNT(Sno) AS cnt, AVG(Grade) AS avg_g
FROM SC GROUP BY Cno HAVING AVG(Grade) >= 80;

当所需信息分散在多个表中时，需要连接。内连接只保留能匹配上的行； 外连接保留一侧的全部行（未匹配的列填 NULL）； 同一张表与自己连接叫自身连接，必须起别名才能区分两个实例。

-- 等值连接：两种写法等价
SELECT Student.Sno, Sname, Cno, Grade
FROM Student, SC WHERE Student.Sno = SC.Sno;

SELECT Student.Sno, Sname, Cname, Grade
FROM Student
JOIN SC     ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno;

-- 自身连接：查询每门课的间接先修课
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

-- 左外连接：保留未选课的学生
SELECT Student.Sno, Sname, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno;

当一个查询的条件依赖另一个查询的结果时，就把后者写成子查询。 不相关子查询可以独立执行（先算内层，再算外层）；相关子查询依赖外层当前行（如 EXISTS）。 表达"全部 / 所有"这类全称量词时，标准写法是双层 NOT EXISTS。

-- IN：先算内层集合，再判断外层是否属于它
SELECT Sname FROM Student
WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = '2');

-- ANY / ALL：与某个值 / 与全部值比较
SELECT Sname, Sage FROM Student
WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept = 'CS');   -- 比其中某个小
SELECT Sname FROM Student
WHERE Sage > ALL (SELECT Sage FROM Student WHERE Sdept = 'CS');   -- 比全部都大

-- EXISTS：相关子查询，只关心"有没有返回行"
SELECT Sname FROM Student
WHERE EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = '1');

-- 全称量词：查询选修了全部课程的学生
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 = 'CS'
UNION SELECT * FROM Student WHERE Sage <= 19;      -- 并，UNION ALL 保留重复
SELECT Sno FROM SC WHERE Cno = '1'
INTERSECT SELECT Sno FROM SC WHERE Cno = '2';      -- 交
SELECT Sno FROM Student EXCEPT SELECT Sno FROM SC; -- 差

插入、修改、删除都会触发完整性检查：违反主码、外码或 CHECK 时语句会被拒绝。 更新操作可以带子查询，用来描述"对满足某条件的一批行进行操作"。

-- 插入：单条与批量（子查询结果）
INSERT INTO Student (Sno, Sname, Ssex, Sdept, Sage) VALUES ('201215128','陈冬','男','IS',18);
INSERT INTO Deptage(Sdept, Avgage)
  SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept;

-- 修改：全部、按条件、带子查询
UPDATE Student SET Sage = Sage + 1;
UPDATE Student SET Sage = 22 WHERE Sno = '201215121';
UPDATE SC SET Grade = 0
WHERE 'CS' = (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);

-- 删除
DELETE FROM SC WHERE Sno = '201215128';
DELETE FROM SC
WHERE 'CS' = (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);
DELETE FROM SC;                       -- 清空数据，保留表结构

-- 空值
SELECT Sno, Cno FROM SC WHERE Grade IS NULL;
SELECT Sno FROM SC WHERE Grade IS NOT NULL AND Grade < 60;

视图是从一张或多张基本表导出的虚表，数据库中只保存它的定义。 查询视图时，DBMS 会做视图消解：把对视图的查询转换成对基本表的等价查询。 更新视图时，DBMS 需要把更新唯一地映射回基本表——因此由聚集、分组、DISTINCT 导出的视图通常不可更新。

CREATE VIEW CS_Student AS
  SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 'CS'
  WITH CHECK OPTION;                  -- 通过视图更新时仍受视图条件约束

CREATE VIEW S_G(Sno, Gavg) AS         -- 分组视图：通常不可更新
  SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;

SELECT * FROM CS_Student WHERE Sage < 20;          -- 视图消解为对 Student 的查询
UPDATE CS_Student SET Sname = '刘辰' WHERE Sno = '201215121';
DROP VIEW CS_Student;

授权解决"谁能做什么"：自主存取控制用 GRANT 授予、REVOKE 收回，可按列授权，也可用角色批量管理。 事务解决"一组操作要么全做要么全不做"：以 COMMIT 提交或 ROLLBACK 回滚结束，具备 ACID 特性。

-- 授权与收回
GRANT SELECT ON TABLE Student TO U1 WITH GRANT OPTION;   -- 允许 U1 再授权
GRANT UPDATE(Sage), SELECT ON TABLE Student TO U2, U3;   -- 列级权限
REVOKE UPDATE(Sage) ON TABLE Student FROM U2;

-- 角色：先授权给角色，再把角色授予用户
CREATE ROLE R1;
GRANT SELECT, UPDATE ON TABLE Student TO R1;
GRANT R1 TO 王平, 张明;

-- 命名约束，便于后续修改
CREATE TABLE Student (
  Sno NUMERIC(6) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
  CONSTRAINT StudentKey PRIMARY KEY (Sno)
);
ALTER TABLE Student DROP CONSTRAINT C1;

-- 事务
BEGIN TRANSACTION;
  UPDATE Account SET balance = balance - 100 WHERE id = 'A';
  UPDATE Account SET balance = balance + 100 WHERE id = 'B';
COMMIT;                 -- 任一步出错则执行 ROLLBACK;