SQL 快速参考

创建数据库

CREATE DATABASE <数据库名>;

删库跑路

DROP DATABASE <数据库名>;

选择数据库

USE <数据库名>;

创建数据表

CREATE TABLE <数据表名>(
    <表头1> <数据类型> NOT NULL AUTO_INCREMENT,
    <表头2> <数据类型> NOT NULL,
    ...
    PRIMARY KEY ( `<表头1>` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 如果不允许字段为 NULL 可以设置字段的属性为 NOT NULL。
2. AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
3. PRIMARY KEY关键字用于定义列为主键。可以使用多列来定义主键，列间以逗号分隔。
4. ENGINE 设置存储引擎，CHARSET 设置编码。
5. MySQL 数据类型

删除数据表

DROP TABLE <数据表名>;

约束

在 SQL 中，我们有如下约束：

• NOT NULL：不能存储 NULL 值
• UNIQUE：每行必须有唯一的值
• PRIMARY KEY：主键 NOT NULL和UNIQUE的结合
• FOREIGN KEY：外键 一个表中的外键指向另一个表中的主键
• CHECK：保证列中的值符合指定的条件
• DEFAULT：规定没有给列赋值时的默认值

索引

在不读取整个表的情况下 索引使数据库应用程序可以更快地查找数据

更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间 这是由于索引本身也需要更新 因此 理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

在表上创建一个简单的索引 允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

在表上创建一个唯一的索引 不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

插入数据

INSERT INTO <数据表名>
    ( <表头1>, <表头2>, ...<表头n> )
    VALUES
    ( <值1>, <值2>, ...<值3> )
;

返回数据表的所有记录

SELECT * FROM <数据表名>;

使用 WHERE 子句读取数据

SELECT <表头1>, <表头2>, ...<表头n> FROM <数据表名1>, <数据表名2>... WHERE 条件表达式;

1. 可以使用多个条件表达式 不使用括号时 优先级为NOT AND OR
2. 使用SELECT DISTINCT可以返回唯一不同的值（去重） 例：SELECT DISTINCT City FROM Customers;
3. 也可以使用IN操作符 例：... WHERE City IN ('Paris','London')
4. 也可以使用BETWEEN操作符 例：... WHERE Price BETWEEN 10 AND 20

数据表的复制

使用SELECT INTO可以从一个表复制数据到另一个表

SELECT * INTO CustomersBackup2013 FROM Customers;

也可以使用INSERT INTO SELECT语句

INSERT INTO CustomersBackup2013 SELECT * FROM Customers;

SELECT 输出排序

SELECT * FROM <数据表名> ORDER BY <表头1> [<表头2>] [ASC | DESC];

1. ASC 为升序排列 从小到大 可省略
2. DESC 为倒序排列 从大到小
3. 可以使用多个表头 越往后写的表头排序优先级越低

SELECT 分页查询

SELECT * FROM <数据表名> LIMIT <最多取出的条数> [OFFSET <从第多少条开始取>];

1. OFFSET 子句是可选的 默认为0
   
2. 数据库优化技巧：如果已知查询结果只有1条 则使用LIMIT 1 这样可以避免全表扫描 找到结果后就不再继续扫描了 从而加快查询速度

SQL 别名

列的 SQL Alias 语法

SELECT <表头> AS <表头别名> FROM <表名>;

表的 SQL Alias 语法

SELECT <表头> FROM <表名> AS <表别名>;

在下面的情况下，使用别名很有用：

• 在查询中涉及超过一个表
• 在查询中使用了函数
• 列名称很长或者可读性差
• 需要把两个列或者多个列结合在一起

聚合函数

• COUNT 计数
• SUM 计算某一列的合计值，该列必须为数值类型
• AVG 计算某一列的平均值，该列必须为数值类型
• MAX 计算某一列的最大值
• MIN 计算某一列的最小值

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

GROUP BY 子句

GROUP BY子句可实现分组

JOIN 查询

JOIN 查询需要先确定主表 然后把另一个表的数据“附加”到结果集上 INNER JOIN是最常用的一种JOIN查询

SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>;

JOIN查询仍然可以使用WHERE条件和ORDER BY排序

• INNER JOIN只返回同时存在于两张表的行数据
• RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
• LEFT OUTER JOIN返回左表都存在的行。
• FULL OUTER JOIN会把两张表的所有记录全部选择出来

更新数据

UPDATE <数据表名> SET <表头1>=<值1>, <表头2>=<值2> [WHERE 条件表达式 ...];

如果不加WHERE子句 将会对表中的所有数据进行更新

删除数据

DELETE FROM <数据表名> [WHERE 条件表达式 ...];

如果不加WHERE子句 将会删除表中的所有数据

LIKE 子句

LIKE 子句类似于正则表达式 其中%作为通配符 类似于shell中的*

SQL 通配符：

• %：匹配0个或多个字符
• _：匹配1个字符
• [charlist]：匹配charlist中的任一字符
• [^charlist]或[!charlist]：匹配不存在于charlist中的任一字符

UNION 操作符

UNION操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中 多个 SELECT 语句会删除重复的数据

事务

数据库事务具有ACID这4个特性

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败。

参考文献

1. SQL教程 - 廖雪峰的官方网站
2. MySQL 教程 | 菜鸟教程
3. SQL 教程 - 自强学堂