1. 连接表达式 (Join Expressions)
1.1 连接操作基础
- 连接操作：接受两个关系，返回另一个关系
- 是笛卡尔积的扩展，要求元组在某种条件下匹配
- 通常在FROM子句中作为子查询表达式使用
1.2 自然连接 (Natural Join)
-- 自动匹配相同名称的属性
SELECT * FROM course NATURAL JOIN prereq; 危险情况：不相关的同名属性会被错误匹配
-- 错误版本：可能遗漏跨系选课记录
SELECT name, title
FROM student NATURAL JOIN takes NATURAL JOIN course;
-- 正确版本
SELECT name, title
FROM student NATURAL JOIN takes, course
WHERE takes.course_id = course.course_id; 1.3 使用USING子句
避免错误匹配，明确指定要匹配的列：
SELECT name, title
FROM (student NATURAL JOIN takes) JOIN course
USING (course_id); 1.4 ON条件连接
允许使用通用谓词：
SELECT *
FROM student JOIN takes ON student.ID = takes.ID;
-- 等价于
SELECT *
FROM student, takes
WHERE student.ID = takes.ID; 1.5 外连接 (Outer Join)
避免信息丢失，对不匹配的元组使用NULL值填充：
左外连接：保留左表所有记录
SELECT * FROM course NATURAL LEFT OUTER JOIN prereq; 右外连接：保留右表所有记录
SELECT * FROM course NATURAL RIGHT OUTER JOIN prereq; 全外连接：保留左右表所有记录
SELECT * FROM course NATURAL FULL OUTER JOIN prereq; 2. 视图 (Views)
2.1 视图定义
视图：对用户可见的”虚拟关系”，用于隐藏敏感数据
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor; 2.2 视图使用
-- 查询视图
SELECT name FROM faculty WHERE dept_name = 'Biology';
-- 基于视图创建视图
CREATE VIEW departments_total_salary(dept_name, total_salary) AS
SELECT dept_name, SUM(salary)
FROM instructor
GROUP BY dept_name; 2.3 视图展开
递归替换视图定义直到没有视图关系：
-- 原始视图定义
CREATE VIEW physics_fall_2009_watson AS
SELECT course_id, room_number
FROM physics_fall_2009
WHERE building = 'Watson';
-- 展开后等价于
CREATE VIEW physics_fall_2009_watson AS
(SELECT course_id, room_number
FROM (SELECT course.course_id, building, room_number
FROM course, section
WHERE course.course_id = section.course_id
AND course.dept_name = 'Physics'
AND section.semester = 'Fall'
AND section.year = '2009')
WHERE building = 'Watson'); 2.4 视图更新限制
可更新视图条件：
- FROM子句只有一个数据库关系
- SELECT子句只包含关系属性名（无表达式、聚集、distinct）
- 未列出的属性可设为NULL
- 没有GROUP BY或HAVING子句
-- 可更新视图示例
CREATE VIEW history_instructors AS
SELECT * FROM instructor WHERE dept_name = 'History'; 2.5 物化视图
创建包含视图查询结果的物理表，需要维护更新。
3. 事务 (Transactions)
3.1 事务特性
- 工作单元：原子性执行
- 原子事务：完全执行或完全回滚
- 隔离性：并发事务间的隔离
- 默认每个SQL语句自动提交
- 可关闭自动提交
3.2 事务语法
BEGIN ATOMIC
-- SQL语句
END; 4. 完整性约束 (Integrity Constraints)
4.1 约束类型
- NOT NULL：非空约束
- PRIMARY KEY：主键
- UNIQUE：候选键（允许NULL）
- CHECK(P)：检查谓词
4.2 CHECK约束示例
CREATE TABLE section (
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
PRIMARY KEY (course_id, sec_id, semester, year),
CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer'))
); 4.3 参照完整性
确保一个关系中出现的值在另一个关系中存在：
CREATE TABLE course (
course_id char(5) PRIMARY KEY,
title varchar(20),
dept_name varchar(20) REFERENCES department
);
-- 使用级联操作
CREATE TABLE course (
dept_name varchar(20),
FOREIGN KEY (dept_name) REFERENCES department
ON DELETE CASCADE
ON UPDATE CASCADE
); 4.4 复杂CHECK约束
-- 理论上（实际不支持）
CHECK (time_slot_id IN (SELECT time_slot_id FROM time_slot));
-- 使用断言（实际也不支持）
CREATE ASSERTION <assertion-name> CHECK <predicate>; 5. SQL数据类型和模式
5.1 内置数据类型
- DATE：日期，格式’2005-7-27’
- TIME：时间，格式’09:00:30.75’
- TIMESTAMP：日期+时间
- INTERVAL：时间段
5.2 索引创建
CREATE INDEX studentID_index ON student(ID); 5.3 用户定义类型
CREATE TYPE Dollars AS numeric(12,2) FINAL;
CREATE TABLE department (
dept_name varchar(20),
building varchar(15),
budget Dollars
); 5.4 域 (Domains)
CREATE DOMAIN person_name char(20) NOT NULL;
CREATE DOMAIN degree_level varchar(10)
CONSTRAINT degree_level_test
CHECK (value IN ('Bachelors', 'Masters', 'Doctorate')); 5.5 大对象类型
- BLOB：二进制大对象
- CLOB：字符大对象
- 查询返回指针而非实际对象
6. 授权 (Authorization)
6.1 权限类型
数据权限：
- READ：读数据
- INSERT：插入数据
- UPDATE：更新数据
- DELETE：删除数据
模式权限：
- INDEX：创建删除索引
- RESOURCES：创建新关系
- ALTERATION：添加删除属性
- DROP：删除关系
6.2 授权语法
-- 授予权限
GRANT <privilege_list>
ON <relation_name or view_name>
TO <user_list>;
-- 示例
GRANT SELECT ON instructor TO U1, U2, U3;
GRANT ALL PRIVILEGES ON department TO Amit; 6.3 撤销权限
REVOKE <privilege_list>
ON <relation_name or view_name>
FROM <user_list>;
-- 示例
REVOKE SELECT ON branch FROM U1, U2, U3; 6.4 角色 (Roles)
-- 创建角色并授权
CREATE ROLE instructor;
GRANT instructor TO Amit;
GRANT SELECT ON takes TO instructor;
-- 角色继承
CREATE ROLE teaching_assistant;
GRANT teaching_assistant TO instructor;
-- 角色链
CREATE ROLE dean;
GRANT instructor TO dean;
GRANT dean TO Satoshi; 6.5 视图授权
CREATE VIEW geo_instructor AS
(SELECT * FROM instructor WHERE dept_name = 'Geology');
GRANT SELECT ON geo_instructor TO geo_staff; 6.6 其他授权特性
- REFERENCES权限：创建外键
- 授权传递：WITH GRANT OPTION
- 级联撤销：CASCADE
- 限制撤销：RESTRICT
7. 触发器 (Triggers)
7.1 触发器定义
触发器：数据库修改的副作用自动执行的语句
7.2 触发器语法
CREATE TRIGGER setnull_trigger BEFORE/AFTER UPDATE ON r
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.phone_number = ' '
SET nrow.phone_number = null; 7.3 语句级触发器
-- 对语句而非每行执行
FOR EACH STATEMENT
REFERENCING OLD TABLE OR NEW TABLE 7.4 完整触发器示例
CREATE TRIGGER credits_earned AFTER UPDATE OF takes ON (grade)
REFERENCING NEW ROW AS nrow
REFERENCING OLD ROW AS orow
FOR EACH ROW
WHEN nrow.grade <> 'F' AND nrow.grade IS NOT NULL
AND (orow.grade = 'F' OR orow.grade IS NULL)
BEGIN ATOMIC
UPDATE student
SET tot_cred = tot_cred +
(SELECT credits FROM course
WHERE course.course_id = nrow.course_id)
WHERE student.id = nrow.id;
END; 关键概念总结
- 连接操作：理解各种连接类型的区别和应用场景
- 视图机制：掌握视图的定义、使用和限制
- 事务管理：保证数据一致性的重要手段
- 完整性约束：维护数据质量的关键
- 权限控制：数据库安全的核心机制
- 触发器：实现复杂业务逻辑的有效工具
这些中级SQL特性在实际数据库应用中至关重要，能够帮助开发人员构建更安全、高效和可靠的数据库系统。
如果您喜欢我的文章，可以考虑打赏以支持我继续创作.
