004-SQL查询语言基础

004-SQL查询语言基础

难度：🟢 | 预计时间：90分钟 | 前置：003-关系模型基础

学习目标

• 理解SQL语言的发展历程和标准
• 掌握SQL的基本语法和数据类型
• 熟练使用DDL语句进行数据库结构定义
• 掌握DML语句进行数据操作
• 学会使用基本的SELECT查询语句
• 理解SQL与关系代数的对应关系

SQL概述

SQL的发展历程

SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言。

timeline
    title SQL发展历程
    1970 : Codd提出关系模型
    1974 : IBM开始SEQUEL项目
    1979 : System/R发布第一个SQL版本
    1982 : IBM SQL/DS商业化
    1986 : SQL-86 (SQL1) 成为ANSI标准
    1989 : SQL-89 (SQL1修订版)
    1992 : SQL-92 (SQL2) 重大扩展
    1999 : SQL:1999 (SQL3) 面向对象特性
    2003 : SQL:2003 XML特性
    2006 : SQL:2006 定义数组类型
    2008 : SQL:2008 MERGE语句
    2011 : SQL:2011 时态数据
    2016 : SQL:2016 JSON支持

SQL的特点

特性	说明	优势
综合统一	DDL、DML、DCL集于一体	减少学习成本
高度非过程化	描述"要什么"而非"怎么做"	提高开发效率
面向集合	以关系（表）为操作对象	批量处理能力强
语法简洁	接近自然语言	易学易用
功能强大	查询、更新、控制功能完备	满足各种需求
标准化	国际标准，跨平台	可移植性好

SQL的组成

SQL数据类型

基本数据类型

1. 数值类型

-- 文件路径: examples/numeric_types.sql

-- 整数类型
CREATE TABLE numeric_demo (
    -- 小整数
    small_int SMALLINT,           -- 通常 -32,768 到 32,767
    
    -- 标准整数
    normal_int INTEGER,           -- 通常 -2,147,483,648 到 2,147,483,647
    
    -- 大整数
    big_int BIGINT,              -- 通常 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
    
    -- 精确数值
    decimal_num DECIMAL(10,2),    -- 总位数10，小数位数2
    numeric_num NUMERIC(8,3),     -- 总位数8，小数位数3
    
    -- 近似数值
    real_num REAL,               -- 单精度浮点数
    double_num DOUBLE PRECISION, -- 双精度浮点数
    float_num FLOAT(24)          -- 指定精度的浮点数
);

-- 插入示例数据
INSERT INTO numeric_demo VALUES (
    32767,                       -- small_int
    2147483647,                  -- normal_int
    9223372036854775807,         -- big_int
    12345678.99,                 -- decimal_num
    12345.678,                   -- numeric_num
    3.14159,                     -- real_num
    3.141592653589793,           -- double_num
    2.718281828                  -- float_num
);

2. 字符类型

-- 文件路径: examples/character_types.sql

CREATE TABLE character_demo (
    -- 定长字符串
    fixed_char CHAR(10),         -- 固定长度，不足补空格
    
    -- 变长字符串
    var_char VARCHAR(50),        -- 可变长度，最大50字符
    
    -- 大文本对象
    text_data TEXT,              -- 大文本（MySQL/PostgreSQL）
    clob_data CLOB               -- 字符大对象（Oracle/DB2）
);

-- 字符串操作示例
INSERT INTO character_demo VALUES (
    'HELLO',                     -- 存储为'HELLO     '（补5个空格）
    'Hello World',               -- 存储为'Hello World'
    'This is a very long text that can contain multiple paragraphs...',
    'Character Large Object data...'
);

-- 字符串函数示例
SELECT 
    LENGTH(fixed_char) as fixed_len,     -- 返回10（包含空格）
    LENGTH(var_char) as var_len,         -- 返回11
    UPPER(var_char) as upper_case,       -- 'HELLO WORLD'
    LOWER(var_char) as lower_case,       -- 'hello world'
    SUBSTRING(var_char, 1, 5) as sub_str -- 'Hello'
FROM character_demo;

3. 日期时间类型

-- 文件路径: examples/datetime_types.sql

CREATE TABLE datetime_demo (
    -- 日期
    birth_date DATE,             -- 格式：YYYY-MM-DD
    
    -- 时间
    work_time TIME,              -- 格式：HH:MM:SS
    
    -- 日期时间
    created_at TIMESTAMP,        -- 格式：YYYY-MM-DD HH:MM:SS
    
    -- 带时区的时间戳
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 插入示例数据
INSERT INTO datetime_demo VALUES (
    '1990-05-15',                -- birth_date
    '09:30:00',                  -- work_time
    '2024-01-15 14:30:25',       -- created_at
    '2024-01-15 14:30:25+08:00'  -- updated_at
);

-- 日期时间函数示例
SELECT 
    CURRENT_DATE as today,                    -- 当前日期
    CURRENT_TIME as now_time,                 -- 当前时间
    CURRENT_TIMESTAMP as now_timestamp,       -- 当前时间戳
    EXTRACT(YEAR FROM birth_date) as birth_year,  -- 提取年份
    EXTRACT(MONTH FROM birth_date) as birth_month, -- 提取月份
    AGE(CURRENT_DATE, birth_date) as age      -- 计算年龄（PostgreSQL）
FROM datetime_demo;

4. 布尔类型

-- 文件路径: examples/boolean_type.sql

CREATE TABLE boolean_demo (
    id INTEGER PRIMARY KEY,
    is_active BOOLEAN,           -- TRUE/FALSE/NULL
    is_verified BOOLEAN DEFAULT FALSE
);

-- 插入示例数据
INSERT INTO boolean_demo VALUES 
    (1, TRUE, TRUE),
    (2, FALSE, DEFAULT),
    (3, NULL, FALSE);

-- 布尔查询示例
SELECT * FROM boolean_demo WHERE is_active = TRUE;
SELECT * FROM boolean_demo WHERE is_active IS NULL;
SELECT * FROM boolean_demo WHERE NOT is_verified;

空值（NULL）处理

-- 文件路径: examples/null_handling.sql

-- NULL的特性演示
SELECT 
    NULL = NULL as null_equals,      -- 结果：NULL（不是TRUE）
    NULL <> NULL as null_not_equals, -- 结果：NULL（不是FALSE）
    NULL IS NULL as is_null,         -- 结果：TRUE
    NULL IS NOT NULL as is_not_null, -- 结果：FALSE
    
    -- 算术运算中的NULL
    10 + NULL as arithmetic,         -- 结果：NULL
    
    -- 字符串连接中的NULL
    'Hello' || NULL as concat,       -- 结果：NULL
    
    -- 聚合函数中的NULL
    COUNT(NULL) as count_null,       -- 结果：0
    SUM(NULL) as sum_null;           -- 结果：NULL

-- NULL值处理函数
CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);

INSERT INTO employee VALUES 
    (1, 'Alice', 5000.00, 1000.00),
    (2, 'Bob', 6000.00, NULL),
    (3, 'Charlie', NULL, 500.00);

-- 使用COALESCE处理NULL
SELECT 
    name,
    salary,
    bonus,
    COALESCE(salary, 0) as salary_with_default,
    COALESCE(bonus, 0) as bonus_with_default,
    COALESCE(salary, 0) + COALESCE(bonus, 0) as total_income
FROM employee;

-- 使用NULLIF避免除零错误
SELECT 
    name,
    salary,
    bonus,
    salary / NULLIF(bonus, 0) as salary_bonus_ratio
FROM employee;

数据定义语言（DDL）

数据库操作

-- 文件路径: examples/database_operations.sql

-- 创建数据库
CREATE DATABASE university
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE university;

-- 显示数据库
SHOW DATABASES;

-- 删除数据库（谨慎使用）
-- DROP DATABASE university;

表的创建

-- 文件路径: examples/table_creation.sql

-- 创建学生表
CREATE TABLE Student (
    student_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    major VARCHAR(30),
    phone VARCHAR(15),
    email VARCHAR(100) UNIQUE,
    admission_date DATE DEFAULT CURRENT_DATE,
    gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0)
);

-- 创建课程表
CREATE TABLE Course (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INTEGER CHECK (credits > 0),
    department VARCHAR(50),
    prerequisite VARCHAR(10),
    FOREIGN KEY (prerequisite) REFERENCES Course(course_id)
);

-- 创建选课表
CREATE TABLE Enrollment (
    student_id VARCHAR(10),
    course_id VARCHAR(10),
    semester VARCHAR(10),
    grade DECIMAL(4,2),
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id, semester),
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

-- 创建教师表
CREATE TABLE Teacher (
    teacher_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    title VARCHAR(20),
    department VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2)
);

-- 创建授课表
CREATE TABLE Teaching (
    teacher_id VARCHAR(10),
    course_id VARCHAR(10),
    semester VARCHAR(10),
    classroom VARCHAR(20),
    schedule VARCHAR(50),
    PRIMARY KEY (teacher_id, course_id, semester),
    FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

表的修改

-- 文件路径: examples/table_modification.sql

-- 添加列
ALTER TABLE Student 
ADD COLUMN address TEXT,
ADD COLUMN graduation_date DATE;

-- 修改列定义
ALTER TABLE Student 
MODIFY COLUMN phone VARCHAR(20);

-- 重命名列
ALTER TABLE Student 
CHANGE COLUMN phone phone_number VARCHAR(20);

-- 删除列
ALTER TABLE Student 
DROP COLUMN address;

-- 添加约束
ALTER TABLE Student 
ADD CONSTRAINT chk_gpa CHECK (gpa BETWEEN 0.0 AND 4.0);

-- 删除约束
ALTER TABLE Student 
DROP CONSTRAINT chk_gpa;

-- 添加索引
CREATE INDEX idx_student_major ON Student(major);
CREATE INDEX idx_student_name ON Student(name);

-- 删除索引
DROP INDEX idx_student_major;

表的删除

-- 文件路径: examples/table_deletion.sql

-- 删除表数据，保留结构
TRUNCATE TABLE Enrollment;

-- 删除表结构和数据
DROP TABLE IF EXISTS Teaching;
DROP TABLE IF EXISTS Enrollment;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Teacher;
DROP TABLE IF EXISTS Student;

数据操纵语言（DML）

数据插入（INSERT）

-- 文件路径: examples/insert_operations.sql

-- 插入单行数据
INSERT INTO Student (student_id, name, gender, birth_date, major, email)
VALUES ('2024001', '张三', 'M', '2000-05-15', '计算机科学', 'zhangsan@email.com');

-- 插入多行数据
INSERT INTO Student VALUES 
    ('2024002', '李四', 'F', '2000-08-20', '数学', '13800138002', 'lisi@email.com', '2024-09-01', 3.8),
    ('2024003', '王五', 'M', '1999-12-10', '物理', '13800138003', 'wangwu@email.com', '2024-09-01', 3.6),
    ('2024004', '赵六', 'F', '2001-03-25', '化学', '13800138004', 'zhaoliu@email.com', '2024-09-01', 3.9);

-- 从其他表插入数据
INSERT INTO Student (student_id, name, gender, major)
SELECT emp_id, emp_name, gender, 'MBA' 
FROM Employee 
WHERE department = 'Management';

-- 插入课程数据
INSERT INTO Course VALUES 
    ('CS101', '计算机科学导论', 3, '计算机学院', NULL),
    ('CS102', '程序设计基础', 4, '计算机学院', 'CS101'),
    ('MATH101', '高等数学', 4, '数学学院', NULL),
    ('PHYS101', '大学物理', 3, '物理学院', 'MATH101');

-- 插入选课数据
INSERT INTO Enrollment (student_id, course_id, semester, grade) VALUES 
    ('2024001', 'CS101', '2024春', 85.5),
    ('2024001', 'MATH101', '2024春', 92.0),
    ('2024002', 'CS101', '2024春', 78.5),
    ('2024002', 'CS102', '2024秋', NULL),  -- 尚未考试
    ('2024003', 'PHYS101', '2024春', 88.0);

数据更新（UPDATE）

-- 文件路径: examples/update_operations.sql

-- 更新单个字段
UPDATE Student 
SET gpa = 3.85 
WHERE student_id = '2024001';

-- 更新多个字段
UPDATE Student 
SET phone_number = '13900139001', 
    email = 'zhangsan_new@email.com'
WHERE student_id = '2024001';

-- 基于条件的批量更新
UPDATE Student 
SET gpa = gpa + 0.1 
WHERE major = '计算机科学' AND gpa < 3.5;

-- 使用子查询更新
UPDATE Student 
SET gpa = (
    SELECT AVG(grade) / 25.0  -- 假设满分100，GPA满分4.0
    FROM Enrollment 
    WHERE Enrollment.student_id = Student.student_id
      AND grade IS NOT NULL
)
WHERE student_id IN (
    SELECT DISTINCT student_id 
    FROM Enrollment 
    WHERE grade IS NOT NULL
);

-- 使用JOIN更新
UPDATE Student s
JOIN (
    SELECT student_id, AVG(grade) as avg_grade
    FROM Enrollment
    WHERE grade IS NOT NULL
    GROUP BY student_id
) e ON s.student_id = e.student_id
SET s.gpa = e.avg_grade / 25.0;

数据删除（DELETE）

-- 文件路径: examples/delete_operations.sql

-- 删除特定记录
DELETE FROM Enrollment 
WHERE student_id = '2024001' AND course_id = 'CS101';

-- 基于条件删除
DELETE FROM Student 
WHERE gpa < 2.0;

-- 使用子查询删除
DELETE FROM Student 
WHERE student_id NOT IN (
    SELECT DISTINCT student_id 
    FROM Enrollment
);

-- 删除重复记录（保留一个）
DELETE s1 FROM Student s1
JOIN Student s2 ON s1.name = s2.name 
                AND s1.birth_date = s2.birth_date
                AND s1.student_id > s2.student_id;

-- 清空表（保留结构）
DELETE FROM Enrollment;  -- 或使用 TRUNCATE TABLE Enrollment;

基本查询语句（SELECT）

SELECT语句结构

-- 文件路径: examples/select_structure.sql

-- 完整的SELECT语句结构
SELECT [DISTINCT] 列名列表
FROM 表名列表
[WHERE 条件表达式]
[GROUP BY 列名列表 [HAVING 条件表达式]]
[ORDER BY 列名列表 [ASC|DESC]]
[LIMIT 数量 [OFFSET 偏移量]];

基本查询

-- 文件路径: examples/basic_queries.sql

-- 查询所有列
SELECT * FROM Student;

-- 查询指定列
SELECT student_id, name, major FROM Student;

-- 使用别名
SELECT 
    student_id AS 学号,
    name AS 姓名,
    major AS 专业,
    gpa AS 绩点
FROM Student AS s;

-- 去除重复行
SELECT DISTINCT major FROM Student;

-- 计算列
SELECT 
    name,
    gpa,
    gpa * 25 AS 百分制成绩,
    CASE 
        WHEN gpa >= 3.7 THEN '优秀'
        WHEN gpa >= 3.0 THEN '良好'
        WHEN gpa >= 2.0 THEN '及格'
        ELSE '不及格'
    END AS 等级
FROM Student;

条件查询（WHERE子句）

-- 文件路径: examples/where_clause.sql

-- 比较运算符
SELECT * FROM Student WHERE gpa > 3.5;
SELECT * FROM Student WHERE major = '计算机科学';
SELECT * FROM Student WHERE birth_date < '2000-01-01';

-- 逻辑运算符
SELECT * FROM Student 
WHERE major = '计算机科学' AND gpa > 3.0;

SELECT * FROM Student 
WHERE major = '数学' OR major = '物理';

SELECT * FROM Student 
WHERE NOT (gpa < 2.0);

-- 范围查询
SELECT * FROM Student 
WHERE gpa BETWEEN 3.0 AND 3.8;

SELECT * FROM Student 
WHERE birth_date BETWEEN '1999-01-01' AND '2001-12-31';

-- 集合查询
SELECT * FROM Student 
WHERE major IN ('计算机科学', '数学', '物理');

SELECT * FROM Student 
WHERE student_id NOT IN ('2024001', '2024002');

-- 模糊查询
SELECT * FROM Student WHERE name LIKE '张%';      -- 姓张的学生
SELECT * FROM Student WHERE name LIKE '%三';      -- 名字以"三"结尾
SELECT * FROM Student WHERE name LIKE '%李%';     -- 名字包含"李"
SELECT * FROM Student WHERE student_id LIKE '2024___'; -- 2024年入学的学生

-- 空值查询
SELECT * FROM Enrollment WHERE grade IS NULL;     -- 未评分的选课记录
SELECT * FROM Enrollment WHERE grade IS NOT NULL; -- 已评分的选课记录

排序查询（ORDER BY子句）

-- 文件路径: examples/order_by_clause.sql

-- 单列排序
SELECT * FROM Student ORDER BY gpa DESC;          -- 按GPA降序
SELECT * FROM Student ORDER BY name ASC;          -- 按姓名升序

-- 多列排序
SELECT * FROM Student 
ORDER BY major ASC, gpa DESC;                     -- 先按专业升序，再按GPA降序

-- 使用表达式排序
SELECT student_id, name, birth_date,
       YEAR(CURRENT_DATE) - YEAR(birth_date) AS age
FROM Student 
ORDER BY age DESC;

-- 使用CASE表达式排序
SELECT * FROM Student 
ORDER BY 
    CASE major
        WHEN '计算机科学' THEN 1
        WHEN '数学' THEN 2
        WHEN '物理' THEN 3
        ELSE 4
    END,
    gpa DESC;

限制结果数量（LIMIT子句）

-- 文件路径: examples/limit_clause.sql

-- 限制返回行数
SELECT * FROM Student ORDER BY gpa DESC LIMIT 5;  -- 前5名学生

-- 分页查询
SELECT * FROM Student 
ORDER BY student_id 
LIMIT 10 OFFSET 20;                               -- 跳过前20行，取10行

-- 等价写法（MySQL）
SELECT * FROM Student 
ORDER BY student_id 
LIMIT 20, 10;                                     -- 从第21行开始，取10行

-- 查找中位数
SELECT gpa FROM Student 
ORDER BY gpa 
LIMIT 1 OFFSET (SELECT COUNT(*) FROM Student) / 2;

聚合函数和分组查询

聚合函数

-- 文件路径: examples/aggregate_functions.sql

-- 基本聚合函数
SELECT 
    COUNT(*) AS 总学生数,
    COUNT(gpa) AS 有GPA记录的学生数,
    AVG(gpa) AS 平均GPA,
    MAX(gpa) AS 最高GPA,
    MIN(gpa) AS 最低GPA,
    SUM(gpa) AS GPA总和,
    STDDEV(gpa) AS GPA标准差
FROM Student;

-- 字符串聚合函数
SELECT 
    major,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS 学生名单
FROM Student 
GROUP BY major;

-- 条件聚合
SELECT 
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS 男生数,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS 女生数,
    AVG(CASE WHEN major = '计算机科学' THEN gpa END) AS 计算机专业平均GPA
FROM Student;

分组查询（GROUP BY子句）

-- 文件路径: examples/group_by_clause.sql

-- 按单列分组
SELECT 
    major AS 专业,
    COUNT(*) AS 学生数,
    AVG(gpa) AS 平均GPA
FROM Student 
GROUP BY major;

-- 按多列分组
SELECT 
    major AS 专业,
    gender AS 性别,
    COUNT(*) AS 学生数,
    AVG(gpa) AS 平均GPA
FROM Student 
GROUP BY major, gender
ORDER BY major, gender;

-- 使用HAVING子句过滤分组
SELECT 
    major AS 专业,
    COUNT(*) AS 学生数,
    AVG(gpa) AS 平均GPA
FROM Student 
GROUP BY major
HAVING COUNT(*) >= 2 AND AVG(gpa) > 3.0;

-- 复杂分组查询
SELECT 
    s.major,
    COUNT(DISTINCT s.student_id) AS 学生总数,
    COUNT(DISTINCT e.course_id) AS 选课门数,
    AVG(e.grade) AS 平均成绩
FROM Student s
LEFT JOIN Enrollment e ON s.student_id = e.student_id
GROUP BY s.major
HAVING 平均成绩 > 80
ORDER BY 平均成绩 DESC;

多表查询

连接查询

-- 文件路径: examples/join_queries.sql

-- 内连接（INNER JOIN）
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    e.grade AS 成绩
FROM Student s
INNER JOIN Enrollment e ON s.student_id = e.student_id
INNER JOIN Course c ON e.course_id = c.course_id;

-- 左外连接（LEFT JOIN）- 显示所有学生，包括未选课的
SELECT 
    s.name AS 学生姓名,
    s.major AS 专业,
    c.course_name AS 课程名称,
    e.grade AS 成绩
FROM Student s
LEFT JOIN Enrollment e ON s.student_id = e.student_id
LEFT JOIN Course c ON e.course_id = c.course_id
ORDER BY s.name;

-- 右外连接（RIGHT JOIN）- 显示所有课程，包括无人选修的
SELECT 
    c.course_name AS 课程名称,
    c.credits AS 学分,
    s.name AS 学生姓名,
    e.grade AS 成绩
FROM Student s
RIGHT JOIN Enrollment e ON s.student_id = e.student_id
RIGHT JOIN Course c ON e.course_id = c.course_id
ORDER BY c.course_name;

-- 全外连接（FULL OUTER JOIN）
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    e.grade AS 成绩
FROM Student s
FULL OUTER JOIN Enrollment e ON s.student_id = e.student_id
FULL OUTER JOIN Course c ON e.course_id = c.course_id;

-- 自连接 - 查找先修课程关系
SELECT 
    c1.course_name AS 课程,
    c2.course_name AS 先修课程
FROM Course c1
JOIN Course c2 ON c1.prerequisite = c2.course_id;

子查询

-- 文件路径: examples/subqueries.sql

-- 标量子查询
SELECT name, gpa
FROM Student
WHERE gpa > (SELECT AVG(gpa) FROM Student);

-- 列子查询
SELECT name, major
FROM Student
WHERE student_id IN (
    SELECT student_id 
    FROM Enrollment 
    WHERE grade > 90
);

-- 行子查询
SELECT name, major, gpa
FROM Student
WHERE (major, gpa) IN (
    SELECT major, MAX(gpa)
    FROM Student
    GROUP BY major
);

-- 表子查询
SELECT s.name, avg_grades.avg_grade
FROM Student s
JOIN (
    SELECT student_id, AVG(grade) as avg_grade
    FROM Enrollment
    WHERE grade IS NOT NULL
    GROUP BY student_id
) avg_grades ON s.student_id = avg_grades.student_id
WHERE avg_grades.avg_grade > 85;

-- 相关子查询
SELECT s1.name, s1.major, s1.gpa
FROM Student s1
WHERE s1.gpa > (
    SELECT AVG(s2.gpa)
    FROM Student s2
    WHERE s2.major = s1.major
);

-- EXISTS子查询
SELECT name, major
FROM Student s
WHERE EXISTS (
    SELECT 1
    FROM Enrollment e
    WHERE e.student_id = s.student_id
      AND e.grade > 95
);

-- NOT EXISTS子查询
SELECT name, major
FROM Student s
WHERE NOT EXISTS (
    SELECT 1
    FROM Enrollment e
    WHERE e.student_id = s.student_id
);

SQL与关系代数的对应关系

基本运算对应

-- 文件路径: examples/sql_relational_algebra.sql

-- 1. 选择（Selection）σ
-- 关系代数：σ_major='计算机科学'(Student)
-- SQL实现：
SELECT * FROM Student WHERE major = '计算机科学';

-- 2. 投影（Projection）π
-- 关系代数：π_name,major(Student)
-- SQL实现：
SELECT DISTINCT name, major FROM Student;

-- 3. 并（Union）∪
-- 关系代数：CS_Students ∪ Math_Students
-- SQL实现：
SELECT student_id, name FROM Student WHERE major = '计算机科学'
UNION
SELECT student_id, name FROM Student WHERE major = '数学';

-- 4. 交（Intersection）∩
-- 关系代数：High_GPA_Students ∩ CS_Students
-- SQL实现：
SELECT student_id, name FROM Student WHERE gpa > 3.5
INTERSECT
SELECT student_id, name FROM Student WHERE major = '计算机科学';

-- 5. 差（Difference）-
-- 关系代数：All_Students - Enrolled_Students
-- SQL实现：
SELECT student_id, name FROM Student
EXCEPT
SELECT DISTINCT s.student_id, s.name 
FROM Student s JOIN Enrollment e ON s.student_id = e.student_id;

-- 6. 笛卡尔积（Cartesian Product）×
-- 关系代数：Student × Course
-- SQL实现：
SELECT * FROM Student CROSS JOIN Course;

-- 7. 自然连接（Natural Join）⋈
-- 关系代数：Student ⋈ Enrollment
-- SQL实现：
SELECT s.*, e.course_id, e.grade
FROM Student s NATURAL JOIN Enrollment e;
-- 或者显式连接：
SELECT s.*, e.course_id, e.grade
FROM Student s JOIN Enrollment e ON s.student_id = e.student_id;

-- 8. 除法（Division）÷
-- 关系代数：π_student_id(Enrollment) ÷ π_course_id(Course)
-- 问题：查找选修了所有课程的学生
-- SQL实现（双重否定）：
SELECT DISTINCT s.student_id, s.name
FROM Student s
WHERE NOT EXISTS (
    SELECT c.course_id
    FROM Course c
    WHERE NOT EXISTS (
        SELECT e.course_id
        FROM Enrollment e
        WHERE e.student_id = s.student_id
          AND e.course_id = c.course_id
    )
);

复合运算示例

-- 文件路径: examples/complex_operations.sql

-- 复杂查询1：查找每个专业GPA最高的学生
-- 关系代数表达式：
-- π_name,major,gpa(Student ⋈ (
--     γ_major;MAX(gpa)→max_gpa(Student)
-- ))

-- SQL实现：
SELECT s.name, s.major, s.gpa
FROM Student s
JOIN (
    SELECT major, MAX(gpa) as max_gpa
    FROM Student
    GROUP BY major
) max_by_major ON s.major = max_by_major.major 
                AND s.gpa = max_by_major.max_gpa;

-- 复杂查询2：查找选修了"数据库"课程但没选修"操作系统"课程的学生
-- 关系代数表达式：
-- π_student_id(σ_course_name='数据库'(Enrollment ⋈ Course)) -
-- π_student_id(σ_course_name='操作系统'(Enrollment ⋈ Course))

-- SQL实现：
SELECT DISTINCT e1.student_id
FROM Enrollment e1 
JOIN Course c1 ON e1.course_id = c1.course_id
WHERE c1.course_name = '数据库'
  AND e1.student_id NOT IN (
    SELECT e2.student_id
    FROM Enrollment e2
    JOIN Course c2 ON e2.course_id = c2.course_id
    WHERE c2.course_name = '操作系统'
  );

实践练习

练习1：基础DDL操作

任务：设计一个图书馆管理系统的数据库结构

要求：

1. 创建数据库 library
2. 设计表结构：读者表、图书表、借阅表
3. 定义适当的主键、外键和约束
4. 插入测试数据

验收标准：

• 数据库和表创建成功
• 约束定义合理
• 能够插入符合约束的数据
• 违反约束时能正确报错

练习2：基础DML操作

任务：对图书馆系统进行数据操作

要求：

1. 插入多个读者和图书记录
2. 模拟借书和还书操作
3. 更新读者信息
4. 删除过期的借阅记录

验收标准：

• 插入操作成功
• 更新操作正确
• 删除操作安全
• 数据一致性保持

练习3：复杂查询设计

任务：编写各种类型的查询语句

查询需求：

1. 查找借书最多的读者
2. 查找最受欢迎的图书
3. 查找逾期未还的借阅记录
4. 统计各类图书的借阅情况
5. 查找从未借过书的读者

验收标准：

• 查询语句语法正确
• 查询结果符合预期
• 能够处理空值和边界情况
• 查询性能合理

常见问题

Q1：什么时候使用INNER JOIN，什么时候使用LEFT JOIN？

A1：

• INNER JOIN：只返回两表中都存在匹配的记录，用于查找确实存在关联的数据
• LEFT JOIN：返回左表的所有记录，即使右表没有匹配，用于"显示所有A，以及A对应的B（如果有的话）"
• 选择原则：根据业务需求决定是否需要显示没有关联的记录

Q2：GROUP BY和HAVING的区别是什么？

A2：

• GROUP BY：对数据进行分组
• HAVING：对分组后的结果进行过滤
• WHERE：对原始数据进行过滤（分组前）
• 执行顺序：WHERE → GROUP BY → HAVING → ORDER BY

Q3：子查询和连接查询哪个性能更好？

A3：一般情况下：

• 连接查询性能通常更好，因为数据库优化器可以更好地优化
• 相关子查询性能较差，因为需要为外层的每一行执行一次
• 非相关子查询性能介于两者之间
• 建议：优先使用连接查询，必要时使用子查询

Q4：如何处理SQL中的NULL值？

A4：

• 使用 IS NULL 和 IS NOT NULL 进行判断
• 使用 COALESCE() 或 ISNULL() 提供默认值
• 注意NULL参与的运算结果都是NULL
• 聚合函数会忽略NULL值（除了COUNT(*)）

总结

• SQL是关系数据库的标准语言，具有功能强大、语法简洁的特点
• DDL用于定义数据库结构，DML用于操作数据
• SELECT语句是SQL的核心，支持复杂的查询操作
• 连接查询和子查询是处理多表关系的重要工具
• 聚合函数和分组查询用于数据统计和分析
• SQL与关系代数有直接的对应关系，理解这种对应有助于编写正确的查询

下一步

• 前往：005-数据库规范化理论
• 扩展阅读：SQL高级特性（窗口函数、CTE、存储过程等）
• 实践建议：在实际数据库中练习各种SQL语句，熟悉不同数据库系统的差异

参考与引用

• ISO/IEC 9075 SQL标准
• MySQL官方文档 - Oracle Corporation
• PostgreSQL官方文档 - PostgreSQL Global Development Group
• SQL教程 - W3Schools
• 数据库系统概念 (第7版) - 第3-6章 - Abraham Silberschatz等 (2019)

更新记录

• 更新时间: 2024-01-15 | 更新内容: 创建SQL查询语言基础章节，包含DDL、DML、基本查询等核心内容 | 更新人: lvs