回头看系列文章序
自大三起我认识到，随着应用知识的扩展，基础知识的重要性愈加明显。时至今日，已经到了无法忽视的地步，以至于我们必须采取有效措施，认真巩固语言基础、数据库、操作系统等一系列重要基础知识，将零散的知识点体系化，我将这一计划称之为“回头看”。

SQL（Structured Query Language，结构化查询语言），用于关系数据库管理，也是一位熟悉又陌生的老朋友。SQL除了支持对数据库结构的创建、修改以及CRUD操作以外还支持多表联查、简单的逻辑操作等。

此外，SQL作为一个标准，在各类DBMS具体实现时并不一定完全遵守，它们虽然在基础的CRUD操作上没有什么分别，但具体特性却各有设计，被称之为SQL方言。

由此诸多特性，SQL其实不是一个学一遍记住就能完事的知识点，同Linux等诸多实践知识一样，SQL需要结合具体使用场景、具体数据结构具体分析，随用随学，必要时还要结合非关系型数据库管理方式对数据进行整合处理。本篇整合是对常用SQL语句的简单梳理，以备随时查询。

  • SQL对大小写不敏感；
  • 每条SQL语句末都应接分号以表结束；
  • 关系型数据库的层级概念：数据库 - 表 - 记录（行），视图。

数据库结构管理

SQL 对数据库结构的管理包括数据库对象的创建、修改和删除。这些对象包括数据库、表、视图、索引、约束和存储过程等。

数据库操作

-- 创建数据库
CREATE DATABASE database_name;
-- 删除数据库
DROP DATABASE database_name;
-- 切换到数据库
USE DATABASE;

表管理

-- 创建表
CREATE TABLE table_name (
	column1 datatype constraints, 
	column2 datatype constraints, 
	demo_id INT PRIMARY KEY AUTO_INCREMENT, 
	demo_name VARCHAR(100) NOT NULL,
	...
)

-- 修改表
-- 添加新列
ALTER TABLE table_name ADD column_name datatype;
-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

-- 删除表
DROP TABLE table_name;

视图

注意，更新视图无法直接使用UPDATE，应该更新原表数据后重新查询视图。

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 修改视图
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW view_name;

索引

-- 编制索引
CREATE INDEX index_name
ON table_name (column_name);

-- 删除索引
DROP INDEX index_name ON table_name;

约束

常用约束类型

  • PRIMARY KEY：主键，唯一标识表中的每一行。
  • FOREIGN KEY：外键，确保引用完整性。
  • UNIQUE：唯一约束，确保列中的所有值唯一。
  • NOT NULL：非空约束，确保列中的值不能为空。
  • CHECK：检查约束，确保列中的值满足指定条件。
-- 添加约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

-- 删除约束
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

触发器

-- 创建触发器
CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

--删除触发器
DROP TRIGGER trigger_name;

存储过程与函数

-- 创建存储过程
CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

-- 创建函数
CREATE FUNCTION function_name (parameters) RETURNS return_datatype
BEGIN
    -- SQL statements
    RETURN return_value;
END;

-- 删除存储过程/函数
DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

基本CRUD操作

增（Create）删（Delete）改（Update）查（Read），是每个程序员都逃不过去的坎。此外，SQL也可以和Excel配合使用完成一些数据操作。

查询 SELECT

-- 查询全部
SELECT * FROM table_name
-- 指定查询
SELECT column1, column2 FROM table_name;
-- 条件查询
SELECT * FROM table_name WHERE condition;
-- 查询结果排序
SELECT * FROM table_name
ORDER BY column1 [ASC|DESC];
--限制输出前n条数据
SELECT * FROM table_name
LIMIT n;

插入 INSERT

-- 向表格中插入值
INSERT INTO table_name VALUES (value1, value2, ....)

-- 向指定列插入值
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

修改 UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

删除 DELETE

DELETE FROM table_name WHERE condition;

连接 JOIN

SQL中的JOIN可以根据两表的共有字段将两个表的数据匹配输出，常见JOIN类型包括内连接、左连接、右连接、全连接、交叉连接和自连接。
示例表：employees和departments

employee_idnamedepartment_id
1Alice1
2Bob2
3Charlie2
4Daniel4
department_iddepartment_name
1HR
2IT
3Finance

内连接（INNER JOIN）与全连接（FULL JOIN）

  • 内连接：返回两个表中满足连接条件的所有行。如果在两个表中找不到匹配项，结果集中将不会包含该行。
  • 全连接：返回两个表中的所有行。如果在一个表中没有匹配项，结果集中将包含NULL值。注：MySQL不支持全连接。
-- 内连接
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

-- 全连接
SELECT employees.name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
  • 内连接返回结果
namedepartment_name
AliceHR
BobIT
CharlieIT
  • 全连接返回结果
namedepartment_name
AliceHR
BobIT
CharlieIT
DanielNULL
NULLFinance

左连接（LEFT JOIN）与右连接（RIGHT JOIN）

  • 左连接：返回左表中的所有行，即使右表中没有匹配项。如果右表中没有匹配项，则结果集中对应的列将包含NULL值。
  • 右连接：返回右表中的所有行，即使左表中没有匹配项。如果左表中没有匹配项，则结果集中对应的列将包含NULL值。
-- 左连接
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

-- 右连接
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
  • 左连接返回结果
namedepartment_name
AliceHR
BobIT
CharlieIT
DanielNULL
  • 右连接返回结果
namedepartment_name
AliceHR
BobIT
CharlieIT
NULLFinance

交叉连接（CROSS JOIN）

交叉连接返回两个表的笛卡尔积，即每个表中的每一行都与另一个表中的每一行组合。注意：在使用Cross Join时，不用使用on关键字。

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
  • 交叉连接返回结果
namedepartment_name
AliceHR
AliceIT
AliceFinance
BobHR
BobIT
BobFinance
CharlieHR
CharlieIT
CharlieFinance
DanielHR
DanielIT
DanielFinance

自连接（SELF JOIN）

自连接是对自身表的连接，用于在同一个表中查找关系。
示例employees表：

employee_idnamemanager_id
1AliceNULL
2Bob1
3Charlie1
SELECT e1.name AS employee, e2.name AS manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
  • 自连接输出结果
employeemanager
AliceNULL
BobAlice
CharlieAlice

常用操作符

SQL 操作符

和许多编程语言一样，SQL提供了丰富的操作符，其类别包括：

  1. 算术操作符；
  2. 比较操作符；
  3. 逻辑操作符；
  4. 集合操作符；
  5. 位操作符；
  6. 字符串操作符；
  7. 特殊操作符。
    其中，算数、比较、逻辑和位运算在各类语言中都大差不差，我在这里着选常用的集合操作符UNION和字符串操作符LIKE进行记录。

联合 UNION

UNION操作符用于合并两个或多个SELECT语句的结果集。

  • 所有参与UNION的SELECT语句必须返回相同数量的列；
  • 每个SELECT语句中的对应列必须具有相同或兼容的数据类型；
  • UNION默认去除重复行。如果希望包含所有结果（包括重复行），可以使用UNION ALL;
  • 可以将UNION用于合并来自不同表、视图甚至不同数据库的数据源.
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

匹配 LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的匹配模式。LIKE常与_和%组合使用，

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern
  • % 通配符表示零个或多个字符；
  • _ 通配符表示单个字符。
-- 匹配以A开头的字符串
SELECT * FROM employees WHERE name LIKE 'A%';
-- 匹配以e结尾的字符串
SELECT * FROM employees WHERE name LIKE '%e';
-- 匹配包含特定字符串的字符串
SELECT * FROM employees WHERE name LIKE '%li%';
-- 匹配长度为3的字符串
SELECT * FROM employees WHERE name LIKE '___';
-- 匹配第二个字符是l的字符串
SELECT * FROM employees WHERE name LIKE '_l%';

参考阅读