SQL入门

欢迎来到SQL的奇妙世界！🔍 本教程将带你了解结构化查询语言(SQL)的基础知识，从最简单的查询到更复杂的数据操作，让你能够自如地操作和管理数据库。

什么是SQL？

SQL（Structured Query Language，结构化查询语言）是一种专门用于管理关系型数据库的编程语言。无论你是数据分析师、后端开发者，还是数据科学家，SQL都是你必备的技能之一。

SQL的读法

虽然SQL的全称是Structured Query Language，但它的读法有两种常见的方式：有人读作"S-Q-L"（字母读音），也有人读作"sequel"（像单词一样读）。两种读法都被广泛接受，所以不用担心你读错了！

数据库基础

在深入学习SQL之前，让我们先了解一些基本概念：

• 数据库（Database）：数据的集合，组织存储在一起的相关数据
• 表（Table）：数据库中存储数据的基本单位，由行和列组成
• 列/字段（Column/Field）：表中的垂直部分，代表数据的特定属性
• 行/记录（Row/Record）：表中的水平部分，代表一条完整的数据记录
• 主键（Primary Key）：唯一标识表中每条记录的字段
• 外键（Foreign Key）：建立表之间关系的字段，引用另一个表的主键
• 索引（Index）：提高查询效率的数据结构

常见的关系型数据库系统

SQL语言在不同的数据库系统中有细微的差异，但核心概念和语法是相通的：

• MySQL：开源、流行、性能优秀，被广泛用于Web应用
• PostgreSQL：功能强大、高度可扩展的开源数据库系统
• SQLite：轻量级、嵌入式数据库，常用于移动应用
• SQL Server：微软开发的企业级数据库系统
• Oracle：大型企业级数据库系统，功能全面

SQL语句类型

SQL语句按照功能可以分为四大类：

1. DDL（Data Definition Language，数据定义语言）
2. 用于定义数据库结构，如创建、修改和删除数据库对象

3. 常见命令：CREATE, ALTER, DROP, TRUNCATE

4. DML（Data Manipulation Language，数据操作语言）

5. 用于操作数据库中的数据

6. 常见命令：SELECT, INSERT, UPDATE, DELETE

7. DCL（Data Control Language，数据控制语言）

8. 用于控制对数据库的访问权限

9. 常见命令：GRANT, REVOKE

10. TCL（Transaction Control Language，事务控制语言）

11. 用于管理数据库中的事务
12. 常见命令：COMMIT, ROLLBACK, SAVEPOINT

数据库和表操作

创建和管理数据库

-- 创建新数据库
CREATE DATABASE 数据库名;

-- 使用数据库
USE 数据库名;

-- 删除数据库
DROP DATABASE 数据库名;

创建表

CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
);

例如，创建一个学生表：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender CHAR(1),
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

修改表结构

-- 添加新列
ALTER TABLE 表名 ADD 列名 数据类型 [约束];

-- 修改列
ALTER TABLE 表名 MODIFY 列名 新数据类型 [新约束];

-- 删除列
ALTER TABLE 表名 DROP COLUMN 列名;

示例：

-- 添加地址列
ALTER TABLE students ADD address VARCHAR(200);

-- 修改email列为必填
ALTER TABLE students MODIFY email VARCHAR(100) NOT NULL;

-- 删除age列
ALTER TABLE students DROP COLUMN age;

删除和清空表

-- 删除表结构和数据
DROP TABLE 表名;

-- 只删除表中的数据，保留表结构
TRUNCATE TABLE 表名;

数据操作

插入数据

-- 基本语法
INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

-- 插入多行数据
INSERT INTO 表名 (列名1, 列名2, ...) 
VALUES 
    (行1值1, 行1值2, ...),
    (行2值1, 行2值2, ...),
    ...;

示例：

-- 插入单条学生记录
INSERT INTO students (student_id, name, age, gender, email, enrollment_date) 
VALUES (1, '张三', 20, 'M', 'zhangsan@example.com', '2023-09-01');

-- 插入多条学生记录
INSERT INTO students (student_id, name, age, gender, email, enrollment_date) 
VALUES 
    (2, '李四', 21, 'F', 'lisi@example.com', '2023-09-01'),
    (3, '王五', 19, 'M', 'wangwu@example.com', '2023-09-02');

查询数据

基本查询条件查询排序和分页聚合函数

-- 查询所有列
SELECT * FROM 表名;

-- 查询特定列
SELECT 列名1, 列名2, ... FROM 表名;

-- 使用条件过滤
SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

-- 等于
SELECT * FROM students WHERE age = 20;

-- 不等于
SELECT * FROM students WHERE gender <> 'M';

-- 大于、小于
SELECT * FROM students WHERE age > 20;

-- 范围查询
SELECT * FROM students WHERE age BETWEEN 18 AND 22;

-- 集合查询
SELECT * FROM students WHERE student_id IN (1, 3, 5);

-- 模糊查询
SELECT * FROM students WHERE name LIKE '张%';  -- 以"张"开头
SELECT * FROM students WHERE email LIKE '%@example.com';  -- 以"@example.com"结尾

-- 排序
SELECT * FROM students ORDER BY age ASC;  -- 升序
SELECT * FROM students ORDER BY enrollment_date DESC;  -- 降序
SELECT * FROM students ORDER BY gender, age DESC;  -- 多列排序

-- 分页
SELECT * FROM students LIMIT 10;  -- 返回前10条
SELECT * FROM students LIMIT 10 OFFSET 20;  -- 返回第21-30条

-- 计数
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT gender) FROM students;  -- 不同性别的数量

-- 求和
SELECT SUM(age) FROM students;

-- 平均值
SELECT AVG(age) FROM students;

-- 最大值/最小值
SELECT MAX(age), MIN(age) FROM students;

-- 分组统计
SELECT gender, COUNT(*) FROM students GROUP BY gender;
SELECT gender, AVG(age) FROM students GROUP BY gender HAVING AVG(age) > 20;

更新数据

-- 基本语法
UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2, ... WHERE 条件;

示例：

-- 更新单个学生的信息
UPDATE students SET age = 22, email = 'new.zhangsan@example.com' WHERE student_id = 1;

-- 批量更新
UPDATE students SET enrollment_date = '2023-10-01' WHERE enrollment_date < '2023-09-15';

删除数据

-- 基本语法
DELETE FROM 表名 WHERE 条件;

示例：

-- 删除单个学生
DELETE FROM students WHERE student_id = 3;

-- 批量删除
DELETE FROM students WHERE enrollment_date < '2023-01-01';

关系和连接

关系型数据库的核心优势在于可以建立表之间的关系。让我们创建一个课程表和一个选课表，来展示表之间关系的用法：

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT,
    teacher VARCHAR(50)
);

-- 创建选课表（关联学生和课程）
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

表连接查询

内连接(INNER JOIN)左连接(LEFT JOIN)右连接(RIGHT JOIN)全连接(FULL JOIN)

返回两表中匹配的行

-- 查询所有学生及其选课信息
SELECT s.name, c.course_name, e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

返回左表所有行和右表匹配的行

-- 查询所有学生及其选课信息（包括未选课的学生）
SELECT s.name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;

返回右表所有行和左表匹配的行

-- 查询所有课程及其选课学生（包括无人选的课程）
SELECT c.course_name, s.name
FROM enrollments e
RIGHT JOIN courses c ON e.course_id = c.course_id
LEFT JOIN students s ON e.student_id = s.student_id;

返回两表中所有行（MySQL不直接支持，可用UNION模拟）

-- 查询所有学生和所有课程的关系（MySQL使用UNION模拟）
SELECT s.name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id
UNION
SELECT s.name, c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
LEFT JOIN students s ON e.student_id = s.student_id
WHERE s.student_id IS NULL;

子查询

子查询是嵌套在另一个查询内部的查询：

-- 查找选修了"数据库原理"课程的所有学生
SELECT name 
FROM students 
WHERE student_id IN (
    SELECT student_id 
    FROM enrollments 
    WHERE course_id = (
        SELECT course_id 
        FROM courses 
        WHERE course_name = '数据库原理'
    )
);

-- 查找每个学生选修的课程数量
SELECT s.name, (
    SELECT COUNT(*) 
    FROM enrollments 
    WHERE student_id = s.student_id
) AS course_count
FROM students s;

事务处理

事务是一组操作，要么全部成功，要么全部失败。事务具有ACID特性：

• 原子性（Atomicity）：事务是一个不可分割的工作单位
• 一致性（Consistency）：事务前后数据库状态必须一致
• 隔离性（Isolation）：多个事务并发执行时互不干扰
• 持久性（Durability）：事务一旦提交，结果永久保存

-- 开始事务
START TRANSACTION;

-- 执行操作
INSERT INTO students (student_id, name, age) VALUES (4, '赵六', 22);
UPDATE students SET age = 23 WHERE student_id = 2;

-- 检查是否有错误，如果有则回滚，否则提交
-- COMMIT;   -- 提交事务
-- ROLLBACK; -- 回滚事务

索引

索引可以大幅提高查询效率，但会降低写入和更新的性能：

-- 创建索引
CREATE INDEX idx_student_name ON students(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_student_email ON students(email);

-- 删除索引
DROP INDEX idx_student_name ON students;

SQL函数

SQL提供了许多内置函数，帮助我们处理数据：

字符串函数数值函数日期函数

-- 字符串连接
SELECT CONCAT(name, '(', age, '岁)') AS info FROM students;

-- 字符串长度
SELECT name, LENGTH(name) AS name_length FROM students;

-- 大小写转换
SELECT UPPER(name), LOWER(email) FROM students;

-- 子字符串
SELECT SUBSTRING(name, 1, 1) AS surname FROM students;

-- 四舍五入
SELECT ROUND(3.1415926, 2);  -- 结果: 3.14

-- 向上/向下取整
SELECT CEILING(3.14);  -- 结果: 4
SELECT FLOOR(3.14);    -- 结果: 3

-- 绝对值
SELECT ABS(-10);       -- 结果: 10

-- 当前日期/时间
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();

-- 提取日期部分
SELECT YEAR(enrollment_date) FROM students;
SELECT MONTH(enrollment_date) FROM students;
SELECT DAY(enrollment_date) FROM students;

-- 日期计算
SELECT name, DATEDIFF(CURRENT_DATE, enrollment_date) AS days_enrolled 
FROM students;

实战案例：学生成绩管理系统

让我们设计一个简单的学生成绩管理系统，包含学生、课程、教师和成绩几个表：

-- 创建教师表
CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    hire_date DATE,
    contact VARCHAR(100)
);

-- 创建改进后的课程表（与教师关联）
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- 创建学期表
CREATE TABLE semesters (
    semester_id INT PRIMARY KEY,
    semester_name VARCHAR(50),
    start_date DATE,
    end_date DATE
);

-- 改进选课表（加入学期信息）
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    semester_id INT,
    grade DECIMAL(4,1),  -- 使用小数表示分数
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    FOREIGN KEY (semester_id) REFERENCES semesters(semester_id)
);

插入示例数据

-- 插入教师数据
INSERT INTO teachers VALUES 
(1, '王教授', '计算机科学', '2010-08-15', 'wang@university.edu'),
(2, '李教授', '数学', '2012-02-20', 'li@university.edu'),
(3, '张教授', '物理', '2015-09-01', 'zhang@university.edu');

-- 插入课程数据
INSERT INTO courses VALUES 
(101, '数据库原理', 3, 1),
(102, '高等数学', 4, 2),
(103, '大学物理', 4, 3),
(104, '算法设计', 3, 1);

-- 插入学期数据
INSERT INTO semesters VALUES 
(1, '2023年春季学期', '2023-02-20', '2023-06-30'),
(2, '2023年秋季学期', '2023-09-01', '2024-01-15');

-- 插入选课和成绩数据
INSERT INTO enrollments VALUES 
(1, 1, 101, 1, 89.5),
(2, 1, 102, 1, 76.0),
(3, 2, 101, 1, 92.0),
(4, 2, 103, 1, 85.5),
(5, 3, 102, 1, 79.0),
(6, 3, 104, 1, 88.5);

常用查询示例

1. 查询每个学生的平均成绩：

SELECT s.student_id, s.name, AVG(e.grade) AS average_grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name
ORDER BY average_grade DESC;

1. 查询每门课程的最高分、最低分和平均分：

SELECT c.course_id, c.course_name, 
       MAX(e.grade) AS highest_grade,
       MIN(e.grade) AS lowest_grade,
       AVG(e.grade) AS average_grade,
       COUNT(e.student_id) AS student_count
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

1. 查询每位教师教授的课程数和学生数：

SELECT t.teacher_id, t.name,
       COUNT(DISTINCT c.course_id) AS course_count,
       COUNT(DISTINCT e.student_id) AS student_count
FROM teachers t
LEFT JOIN courses c ON t.teacher_id = c.teacher_id
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY t.teacher_id, t.name;

1. 查询成绩优秀（85分以上）的学生及其课程：

SELECT s.name AS student_name, c.course_name, e.grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE e.grade > 85
ORDER BY s.name, e.grade DESC;

性能优化技巧

1. 使用适当的索引：在常用于查询条件和连接操作的列上创建索引
2. 限制结果数量：使用LIMIT限制返回记录数
3. 只选择需要的列：避免使用SELECT *
4. 使用合适的数据类型：为数据选择最小的合适数据类型
5. 优化JOIN操作：确保连接列上有索引，减少连接表的数量
6. 避免使用OR：可能导致无法使用索引，考虑使用UNION ALL替代
7. 定期维护数据库：分析表结构，优化表，清理不必要的数据

练习题

1. 创建一个简单的图书管理系统数据库，包括书籍、作者、出版社和借阅记录表。
2. 编写SQL查询，找出借阅次数最多的前5本书。
3. 编写SQL查询，计算每位学生的成绩总和和平均分，并按平均分降序排列。
4. 创建一个视图，显示每门课程的选课人数和平均成绩。

常见问题解决

"语法错误"

遇到语法错误时，请检查： 1. SQL关键字拼写是否正确 2. 括号是否配对 3. 引号是否正确使用（字符串需要引号） 4. 表名和列名是否正确

"表不存在"

可能的原因： 1. 数据库名或表名拼写错误 2. 未选择正确的数据库（使用USE database_name） 3. 表尚未创建

"权限不足"

解决方法： 1. 确认你的用户账号有足够权限 2. 联系数据库管理员获取必要权限 3. 使用有权限的账号执行操作

小结

SQL是一门强大的语言，掌握它可以让你高效地管理和分析数据。本教程只是SQL的入门，随着你的深入学习，你将发现SQL在数据处理方面的更多可能性！

记住一句经典的数据库格言：

"数据是21世纪的石油，而SQL就是开采这些石油的工具。"

继续学习和实践，你将成为数据操作的专家！