SQL 的基础知识汇总

一、数据库基本概念

1. 数据库（Database）：是数据的集合，存储在计算机系统中，用于管理和组织数据。可以包含多个表、视图、存储过程等对象。
2. 表（Table）：由行和列组成的数据结构，用于存储特定类型的数据。每一列代表一个属性，每一行代表一条记录。
3. 列（Column）：表中的一个字段，具有特定的数据类型和约束。
4. 行（Row）：表中的一条记录，包含各个列的值。
5. 主键（Primary Key）：用于唯一标识表中的每一行，确保数据的完整性和一致性。
6. 外键（Foreign Key）：建立表与表之间的关系，确保数据的参照完整性。

二、数据类型

1. 数值类型：如整数（INT）、小数（FLOAT、DECIMAL）等。
2. 字符串类型：如固定长度字符串（CHAR）、可变长度字符串（VARCHAR）等。
3. 日期和时间类型：如 DATE、TIME、DATETIME 等。
4. 布尔类型：如 BIT、BOOLEAN 等。

三、数据查询

1. SELECT语句：用于从一个或多个表中检索数据。
   • 语法：SELECT column1, column2,... FROM table_name;
   • 示例：SELECT name, age FROM students; 选取 “students” 表中的 “name” 和 “age” 列。
2. WHERE子句：用于筛选满足特定条件的行。
   • 语法：SELECT column1, column2,... FROM table_name WHERE condition;
   • 示例：SELECT * FROM students WHERE age > 18; 选取年龄大于 18 的学生记录。
3. ORDER BY子句：用于对查询结果进行排序。
   • 语法：SELECT column1, column2,... FROM table_name ORDER BY column_name [ASC|DESC];
   • 示例：SELECT * FROM students ORDER BY age DESC; 按照年龄降序排列学生记录。
4. LIMIT和OFFSET：用于限制查询结果的数量和指定偏移量。
   • 语法：SELECT column1, column2,... FROM table_name LIMIT n [OFFSET m];
   • 示例：SELECT * FROM students LIMIT 10 OFFSET 20; 选取从第 21 条开始的 10 条学生记录。
5. DISTINCT关键字：用于去除查询结果中的重复行。
   • 语法：SELECT DISTINCT column1, column2,... FROM table_name;
   • 示例：SELECT DISTINCT city FROM customers; 选取客户表中的不同城市。

四、数据插入

1. INSERT INTO语句：用于向表中插入新的行。
   • 语法：INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...);
   • 示例：INSERT INTO students (name, age) VALUES ('Tom', 20); 向 “students” 表中插入一条新记录。

五、数据更新

1. UPDATE语句：用于更新表中的现有数据。
   • 语法：UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition;
   • 示例：UPDATE students SET age = 21 WHERE name = 'Tom'; 将名字为 “Tom” 的学生年龄更新为 21。

六、数据删除

1. DELETE FROM语句：用于删除表中的行。
   • 语法：DELETE FROM table_name WHERE condition;
   • 示例：DELETE FROM students WHERE age < 18; 删除年龄小于 18 的学生记录。

七、数据库对象管理

1. 创建表：CREATE TABLE语句用于创建新表。
   • 语法：CREATE TABLE table_name (column1 datatype constraint1, column2 datatype constraint2,...);
   • 示例：CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), age INT); 创建一个名为 “employees” 的表。
2. 删除表：DROP TABLE语句用于删除表。
   • 语法：DROP TABLE table_name;
   • 示例：DROP TABLE employees; 删除 “employees” 表。
3. 创建索引：CREATE INDEX语句用于创建索引，提高查询性能。
   • 语法：CREATE INDEX index_name ON table_name (column1, column2,...);
   • 示例：CREATE INDEX idx_students_name ON students (name); 在 “students” 表的 “name” 列上创建索引。
4. 删除索引：DROP INDEX语句用于删除索引。
   • 语法：DROP INDEX index_name;
   • 示例：DROP INDEX idx_students_name; 删除 “students” 表上的 “idx_students_name” 索引。

八、聚合函数

1. COUNT()：计算行数。
   • 示例：SELECT COUNT(*) FROM students; 计算 “students” 表中的行数。
2. SUM()：计算数值列的总和。
   • 示例：SELECT SUM(age) FROM students; 计算 “students” 表中 “age” 列的总和。
3. AVG()：计算数值列的平均值。
   • 示例：SELECT AVG(age) FROM students; 计算 “students” 表中 “age” 列的平均值。
4. MAX()和MIN()：分别返回数值列的最大值和最小值。
   • 示例：SELECT MAX(age) FROM students; 返回 “students” 表中 “age” 列的最大值。

九、子查询

子查询是嵌套在另一个查询中的查询，可以用于在一个查询中使用另一个查询的结果。

• 示例：SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students); 选取年龄最大的学生的名字。

十、连接（JOIN）

用于从多个表中检索数据，根据不同的连接条件组合表中的行。

1. 内连接（INNER JOIN）：只返回两个表中满足连接条件的行。
   • 语法：SELECT column1, column2,... FROM table1 INNER JOIN table2 ON table1.column = table2.column;
   • 示例：SELECT students.name, courses.course_name FROM students INNER JOIN enrollments ON students.student_id = enrollments.student_id INNER JOIN courses ON enrollments.course_id = courses.course_id; 选取学生的名字和他们所选修的课程名称。
2. 外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）：返回满足连接条件的行以及左表（LEFT JOIN）、右表（RIGHT JOIN）或两个表（FULL JOIN）中的所有行。
   • 语法和示例类似内连接，只是连接类型不同。

十一、事务处理

1. 事务是一组 SQL 语句的集合，要么全部成功执行，要么全部回滚。
2. BEGIN TRANSACTION：开始一个事务。
3. COMMIT：提交事务，使事务中的所有更改永久生效。
4. ROLLBACK：回滚事务，撤销事务中所做的所有更改。

十二、视图

1. 视图是一个虚拟表，其内容由查询定义。视图可以简化复杂的查询，提供数据的安全性和独立性。
2. CREATE VIEW语句用于创建视图。
   • 语法：CREATE VIEW view_name AS SELECT column1, column2,... FROM table_name WHERE condition;
   • 示例：CREATE VIEW student_grades AS SELECT students.name, courses.course_name, grades.grade FROM students INNER JOIN enrollments ON students.student_id = enrollments.student_id INNER JOIN courses ON enrollments.course_id = courses.course_id INNER JOIN grades ON enrollments.enrollment_id = grades.enrollment_id; 创建一个名为 “student_grades” 的视图，显示学生的名字、课程名称和成绩。

十三、存储过程和函数

1. 存储过程是一组预编译的 SQL 语句，可以接受参数并返回结果。存储过程可以提高数据库的性能和安全性，减少网络流量。
2. CREATE PROCEDURE语句用于创建存储过程。
   • 语法：CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype,...) AS BEGIN SQL statements END;
   • 示例：CREATE PROCEDURE get_student_names (IN age INT) AS BEGIN SELECT name FROM students WHERE age = age; END; 创建一个名为 “get_student_names” 的存储过程，接受一个年龄参数，返回该年龄的学生名字。
3. 函数是一段可以接受参数并返回结果的 SQL 代码。函数可以在 SQL 语句中像内置函数一样使用。
4. CREATE FUNCTION语句用于创建函数。
   • 语法：CREATE FUNCTION function_name (parameter1 datatype, parameter2 datatype,...) RETURNS datatype AS BEGIN SQL statements END;
   • 示例：CREATE FUNCTION calculate_total_age (student_id INT) RETURNS INT AS BEGIN DECLARE total_age INT; SELECT SUM(age) INTO total_age FROM students WHERE student_id = student_id; RETURN total_age; END; 创建一个名为 “calculate_total_age” 的函数，接受一个学生 ID 参数，返回该学生所在班级的总年龄。