DDL（数据库定义语言）

新建时为了防止命名和关键字冲突，用``将数据库名、表名、字段名等括起来

常用字段类型：INT、DOUBLE、DECIMAL、VARCHAR、DATA、TIME、DATETIME、TIMESTAMP

CREATE DATABASE `数据库名`; -- 创建数据库
CREATE TABLE `表名` (
    `字段名` 字段类型(长度) [NOT NULL] [AUTO_INCREMENT] [DEFAULT '默认值'] COMMENT '备注',
    ...
    ...
    PRIMARY KEY (`主键名`)
)ENGINE=INNODB DEFAULT CHARSET=utf8; -- 创建表
CREATE TABLE `新表名` LIKE 旧表名; -- 复制一个相同结构的新表

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

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

SELECT DATABASE(); -- 查看当前使用的数据库

SHOW DATABASES; -- 查看所有数据库
SHOW CREATE DATABASE 数据库名; -- 查看数据库创建信息
SHOW TABLES; -- 查看所有表
SHOW CREATE TABLE 表名; -- 查看表创建信息

DESC 表名; -- 查看表结构

RENAME TABLE 表名 TO 新表名; -- 修改表名

ALTER TABLE 表名 ADD `字段名` 字段类型(长度); -- 添加字段
ALTER TABLE 表名 ADD CONSTRAINT `FK_字段名` FOREIGN KEY(`字段名`) REFERENCES `表名`(`字段名`); -- 绑定外键约束（物理外键，不推荐使用）
ALTER TABLE 表名 CHANGE 旧字段名 `新字段名` 字段类型(长度); -- 同时修改字段名和字段类型
ALTER TABLE 表名 MODIFY 字段名 新字段类型(长度); -- 修改字段类型
ALTER TABLE 表名 DROP 字段名; -- 删除字段

DML（数据库操作语言）

WHERE条件语句（布尔类型）：

• 关系运算符：=、>、>=、<、<=、!=或<>
• 逻辑运算符：AND或&&、OR或||、NOT或!
• 比较运算符：in、BETWEEN ... AND ...、LIKE、IS NULL、IS NOT NULL

INSERT INTO 表名 (字段名1, 字段名2...) VALUES (值1, 值2...)[, (值1, 值2...)...]; -- 插入数据
INSERT INTO 表名 VALUES (所有值); -- 字段名可以省略，但需要写明所有字段的值
INSERT INTO `新表名` SELECT * FROM 旧表名; -- 先复制一个相同结构的新表，然后再复制相同的值

UPDATE 表名 SET 字段名1 = 值1[, 字段名2 = 值2...] [WHERE 字段名3 = 值3 [AND 字段名4 = 值4...]]; -- 修改数据

DELETE FROM 表名 WHERE 字段名1 = 值1 [AND 字段名2 = 值2...]; -- 删除数据

TRUNCATE 表名; -- 清空表的内容，保留结构和索引约束

DELETE和TRUNCATE的区别：

• 相同点：都只是清空表的内容，保留结构和索引约束

• 不同点：TRUNCATE可以重置自增计数器，且不会影响事务，因此推荐使用TRUNCATE清空表

  但如果存储引擎是InnoDB，重启数据库以后，DELETE清空的表也会重置自增计数器，因为InnoDB是存在内存中的，断电会丢失

  而MyISAM是存在文件中的，不会丢失，重启也不会重置

DQL（数据库查询语言）

基本查询

SELECT * FROM 表名; -- 查询表的所有字段

SELECT 字段名1[, 字段名2...] FROM 表名; -- 查询指定字段
SELECT 字段名1 [AS] 别名1[, 字段名2 [AS] 别名2...] FROM 表名 [AS] 别名; -- 以别名代替显示字段名

SELECT DISTINCT 字段名 FROM 表名;  -- 去除重复的数据

SELECT 字段名1, 字段名2 + 1 AS `新字段名` FROM 表名; -- 查询时计算表达式

SELECT * FROM 表名 WHERE 字段名 in (列表); -- 精确匹配指定的列表
SELECT * FROM 表名 WHERE 字段名 BETWEEN 35 AND 40; -- 匹配区间范围
SELECT * FROM 表名 WHERE 字段名 LIKE '%通配符'; -- 模糊匹配（%代表任意个字符，_代表一个字符）
SELECT * FROM 表名 WHERE 字段名 IS NULL; -- 匹配NULL
SELECT * FROM 表名 WHERE 字段名 IS NOT NULL; -- 匹配非NULL

SELECT 字段名1, AVG(字段名2), MAX(字段名2), MIN(字段名2)... FROM 表名 GROUP BY 字段名3; -- 将结果按指定字段分组显示
SELECT 字段名1 FROM 表名 GROUP BY 字段名2 HAVING 子句; -- 分组后不能使用WHERE，只能使用HAVING过滤

SELECT * FROM 表名 WHERE 字段名 = 值 ORDER BY 字段名; -- 排序，默认升序
SELECT * FROM 表名 WHERE 字段名 = 值 ORDER BY 字段名 DESC; -- DESC关键字，降序

SELECT * FROM 表名 LIMIT 0, 20; -- 分页（起始下标, 页面大小）

联表查询

...JOIN ... ON ...

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a INNER JOIN 表2 [AS] b ON a.字段名1 = b.字段名1; -- 内连接

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a LEFT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1; -- 左外连接
SELECT a.字段名1, 字段名2... FROM 表1 [AS] a LEFT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1 WHERE b.字段名2 IS NULL; -- 左连接

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a RIGHT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1; -- 右外连接
SELECT a.字段名1, 字段名2... FROM 表1 [AS] a RIGHT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1 WHERE a.字段名2 IS NULL; -- 右连接

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a LEFT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1
UNION
SELECT a.字段名1, 字段名2... FROM 表1 [AS] a RIGHT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1; -- 全连接(左外连接+右外连接，去重)

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a LEFT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1 WHERE b.字段名2 IS NULL
UNION
SELECT a.字段名1, 字段名2... FROM 表1 [AS] a RIGHT JOIN 表2 [AS] b ON a.字段名1 = b.字段名1 WHERE a.字段名2 IS NULL; -- 全外连接（左连接+右连接，去重）

SELECT a.字段名1, 字段名2... FROM 表1 [AS] a INNER JOIN 表2 [AS] b ON a.字段名1 = b.字段名1 INNER JOIN 表3 [AS] c ON a.字段名3 = c.字段名3 WHERE 字段名4 = '目标值'; -- 多重联表查询

子查询

嵌套查询

SELECT 字段名1, 字段名2... FROM 表1 WHERE 字段名3 = (SELECT 字段名4, 字段名5... FROM 表2 WHERE 字段名6 = '目标值'...)...;

自连接

一张表拆为两张相同的表，自己的表和自己的表连接

SELECT a.字段名1 [AS] '父字段', b.字段名1 [AS] '子字段' FROM 表 [AS] a, 表 [AS] b WHERE a.父字段名2 = b.子字段名3

常用函数

SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- (0~1)随机数
SELECT SIGN(10); -- 判断符号，正数返回1，负数返回-1

SELECT CHAR_LENGTH('length'); -- 字符串长度
SELECT CONCAT('拼接字符串：', 字段名) [AS] 别名 FROM 表名; -- 可以拼接字段

INSERT INTO 表名 VALUES(`id`, `name`, MD5(`password`); -- MD5加密

聚合函数

SELECT COUNT(字段名) FROM 表名; -- 会忽略NULL值，结果可能不准确。如果是查主键，效率最高
SELECT COUNT(1) FROM 表名; -- 结果准确
SELECT COUNT(*) FROM 表名; -- 结果准确，和COUNT(1)的效率也是一样的

SELECT SUM(*) FROM 表名;
SELECT MAX(*) FROM 表名;
SELECT MIN(*) FROM 表名;
SELECT AVG(*) FROM 表名;

总结

JOIN...ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT子句的顺序有严格要求，不能前后颠倒

SELECT [ALL | DISTINCT]
{* | 表1.* | [表1.字段名1 [AS 别名1][, 表1.字段名2 [AS 别名2]][...]]}
FROM 表2 [AS 别名3]
[LEFT | RIGHT | INNER JOIN 表3 ON 子句] -- 联合查询
[WHERE 子句] -- 指定结果需要满足的条件
[GROUP BY 子句] -- 指定结果按照哪几个字段来分组
[HAVING 子句] -- 过滤分组的记录必须满足的次要条件（分组不能和WHERE连用，只能用HAVING）
[ORDER BY 子句] -- 指定查询记录按照一个或多个条件排序
[LIMIT 子句]; -- 指定查询的记录显示的分页大小