SQL 基础知识讲解-基本操作

SQL 基础知识讲解

1. SQL 简介

SQL（Structured Query Language）是一种用于管理关系数据库系统的标准语言。它允许用户对数据库进行各种操作，包括数据的查询、插入、更新和删除。

2. 数据库和表的基本概念

• 数据库（Database）：是一个有组织的数据集合，通常以电子方式存储在计算机系统中。
• 表（Table）：数据库中的数据存储在表中，表由行和列组成。

3. SQL 增删改查操作

3.1 插入数据（INSERT）

插入数据是将新数据添加到数据库表中的过程。

语法：

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

示例：
假设有一个名为 students 的表，包含 id, name, age 和 gender 列。

INSERT INTO students (id, name, age, gender)
VALUES (1, '张三', 20, '男');

3.2 查询数据（SELECT）

查询数据是从数据库表中检索数据的过程。

语法：

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

示例：
从 students 表中查询所有学生的姓名和年龄。

SELECT name, age
FROM students;

如果只想查询年龄大于18岁的学生：

SELECT name, age
FROM students
WHERE age > 18;

3.3 更新数据（UPDATE）

更新数据是修改数据库表中现有数据的过程。

语法：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;

示例：
将 students 表中 id 为1的学生姓名改为 ‘李四’。

UPDATE students
SET name = '李四'
WHERE id = 1;

3.4 删除数据（DELETE）

删除数据是从数据库表中移除数据的过程。

语法：

DELETE FROM 表名
WHERE 条件;

示例：
从 students 表中删除 id 为1的学生。

DELETE FROM students
WHERE id = 1;

4. 高级查询

4.1 使用 WHERE 子句

WHERE 子句用于过滤记录，只选择满足条件的记录。

示例：
查询年龄在18到25岁之间的学生。

SELECT name, age
FROM students
WHERE age BETWEEN 18 AND 25;

4.2 使用 ORDER BY 子句

ORDER BY 子句用于对结果集进行排序。

示例：
按年龄升序排列学生。

SELECT name, age
FROM students
ORDER BY age ASC;

4.3 使用 GROUP BY 子句

GROUP BY 子句用于对结果集进行分组。

示例：
按性别分组并统计每组的学生人数。

SELECT gender, COUNT(*)
FROM students
GROUP BY gender;

4.4 使用 JOIN 子句

JOIN 子句用于根据两个或多个表之间的相关列组合它们的数据。

示例：
假设有两个表 students 和 scores，分别存储学生信息和成绩信息。

SELECT students.name, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;

5. 数据类型和约束

5.1 数据类型

• INT：整数类型
• VARCHAR(n)：可变长度字符串，最大长度为 n
• DATE：日期类型

5.2 约束

• PRIMARY KEY：主键，唯一标识表中的每一行
• FOREIGN KEY：外键，引用另一个表的主键
• UNIQUE：唯一约束，确保列中的所有值都是唯一的
• NOT NULL：非空约束，确保列中的值不能为空

示例：
创建一个包含约束的表。

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender VARCHAR(10)
);

6. 索引

索引是用于加速数据库查询的数据结构。

示例：
在 students 表的 name 列上创建索引。

CREATE INDEX idx_name
ON students (name);

7. 事务

事务是数据库操作的逻辑单元，包含一组数据库操作，要么全部执行，要么全部不执行。

示例：
使用事务插入和查询数据。

BEGIN;
INSERT INTO students (id, name, age, gender)
VALUES (2, '王五', 22, '女');
COMMIT;

8. 存储过程和函数

存储过程和函数是预编译的数据库对象，用于执行特定任务。

示例：
创建一个存储过程，查询所有学生信息。

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM students;
END //
DELIMITER ;

调用存储过程：

CALL GetAllStudents();

9. 触发器

触发器是与表相关联的特殊类型的存储过程，当表发生特定事件时自动执行。

示例：
创建一个触发器，在插入学生信息时自动记录日志。

CREATE TRIGGER log_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO logs (message)
    VALUES (CONCAT('New student inserted: ', NEW.name));
END;

10. 数据库备份和恢复

数据库备份和恢复是确保数据安全的重要操作。

示例：
使用 mysqldump 工具备份数据库。

mysqldump -u username -p database_name > backup.sql

恢复数据库：

mysql -u username -p database_name < backup.sql