MySQL 数据库

精选 MySQL 常用连接命令、表结构管理 DDL、数据增删改查 DML、多表联结 Join、视图 View、触发器 Trigger、索引及内置函数极速备忘单。

Star 项目
关注 X (Twitter)
赞赏饮咖啡

#🚀 入门指引

#连接 MySQL 数据库 (Connect MySQL)

mysql -u <user> -p

mysql [db_name]

mysql -h <host> -P <port> -u <user> -p [db_name]

mysql -h <host> -u <user> -p [db_name]

#常用快捷命令 (Commons)

数据库相关命令 (Database)

命令语法 功能描述
CREATE DATABASE db ; 创建新数据库
SHOW DATABASES; 列出所有数据库
USE db; 切换/使用指定数据库
CONNECT db ; 切换/使用指定数据库
DROP DATABASE db; 删除指定数据库

数据表相关命令 (Table)

命令语法 功能描述
SHOW TABLES; 列出当前数据库中的所有表
SHOW FIELDS FROM t; 查看指定表的所有字段属性
DESC t; 查看数据表结构说明
SHOW CREATE TABLE t; 查看创建数据表的建表 SQL
TRUNCATE TABLE t; 清空数据表中的所有记录
DROP TABLE t; 删除数据表

进程控制命令 (Process)

命令语法 功能描述
show processlist; 查看当前正在运行的进程列表
kill pid; 强制终止指定进程 ID

其他常用命令 (Other)

命令语法 功能描述
exit 或 \q 退出 MySQL 会话终端

#数据库备份与还原 (Backups)

导出创建完整备份

mysqldump -u user -p db_name > db.sql

仅导出数据（不包含建表结构）

mysqldump -u user -p db_name --no-data=true --add-drop-table=false > db.sql

从 sql 文件还原恢复数据库

mysql -u user -p db_name < db.sql

#MySQL 常用 SQL 示例

#数据表管理 (Managing tables)

创建带有三列的新数据表

CREATE TABLE t (
     id    INT,
     name  VARCHAR DEFAULT NOT NULL,
     price INT DEFAULT 0,
     PRIMARY KEY(id)
);

从数据库中删除数据表

DROP TABLE t ;

向数据表中添加新字段

ALTER TABLE t ADD column;

从数据表中删除字段 c

ALTER TABLE t DROP COLUMN c ;

向数据表添加约束条件

ALTER TABLE t ADD constraint;

从数据表中删除约束条件

ALTER TABLE t DROP constraint;

重命名数据表名 (将 t1 改为 t2)

ALTER TABLE t1 RENAME TO t2;

重命名字段名 (将 c1 改为 c2)

ALTER TABLE t1 RENAME c1 TO c2 ;

清空表中所有数据

TRUNCATE TABLE t;

#查询表数据 (Querying data)

查询表中 c1, c2 指定列的数据

SELECT c1, c2 FROM t

查询表中的所有行与所有列

SELECT * FROM t

带条件过滤查询数据

SELECT c1, c2 FROM t
WHERE condition

查询并去重 (DISTINCT)

SELECT DISTINCT c1 FROM t
WHERE condition

结果集升序或降序排序 (ORDER BY)

SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC]

跳过指定偏移量并返回后续 n 行 (分页 LIMIT/OFFSET)

SELECT c1, c2 FROM t
ORDER BY c1
LIMIT n OFFSET offset

结合聚合函数进行分组查询 (GROUP BY)

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1

使用 HAVING 子句过滤分组后的数据

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition

#多表联结查询 (Querying multiple tables)

内联结 t1 与 t2 (INNER JOIN)

SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition

左外联结 t1 与 t2 (LEFT JOIN)

SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition

右外联结 t1 与 t2 (RIGHT JOIN)

SELECT c1, c2
FROM t1
RIGHT JOIN t2 ON condition

全外联结 (FULL OUTER JOIN)

SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 ON condition

笛卡尔积交叉联结 (CROSS JOIN)

SELECT c1, c2
FROM t1
CROSS JOIN t2

交叉联结隐式写法

SELECT c1, c2
FROM t1, t2

自联结 (Self Join)

SELECT c1, c2
FROM t1 A
INNER JOIN t1 B ON condition

组合两个查询的结果集 (UNION / UNION ALL)

SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2

求两个查询结果集的交集 (INTERSECT)

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2

求两个查询结果集的差集 (MINUS)

SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2

通配符模糊匹配查询 (LIKE %)

SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern

集合范围查询 (IN)

SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list

介于两值之间的区间查询 (BETWEEN)

SELECT c1, c2 FROM t
WHERE  c1 BETWEEN low AND high

检查字段是否为空 (IS NULL)

SELECT c1, c2 FROM t
WHERE  c1 IS [NOT] NULL

#数据库约束 (Constraints)

将 c1 与 c2 联合设置为主键

CREATE TABLE t(
    c1 INT, c2 INT, c3 VARCHAR,
    PRIMARY KEY (c1,c2)
);

将 c2 字段设置为外键

CREATE TABLE t1(
    c1 INT PRIMARY KEY,
    c2 INT,
    FOREIGN KEY (c2) REFERENCES t2(c2)
);

将 c1 与 c2 设置为唯一约束 (UNIQUE)

CREATE TABLE t(
    c1 INT, c2 INT,
    UNIQUE(c1,c2)
);

添加检查约束条件 (CHECK)

CREATE TABLE t(
  c1 INT, c2 INT,
  CHECK(c1> 0 AND c1 >= c2)
);

设置 c2 字段非空约束 (NOT NULL)

CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);

#修改数据操作 (Modifying Data)

插入单行记录

INSERT INTO t(column_list)
VALUES(value_list);

插入多行记录

INSERT INTO t(column_list)
VALUES (value_list),
       (value_list), …;

从 t2 表查询并批量插入到 t1 表

INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

更新表中所有行的 c1 字段值

UPDATE t
SET c1 = new_value;

更新符合条件的行的 c1, c2 字段值

UPDATE t
SET c1 = new_value,
        c2 = new_value
WHERE condition;

删除表中所有数据行

DELETE FROM t;

按条件删除表中指定数据行

DELETE FROM t
WHERE condition;

#视图管理 (Managing Views)

创建仅包含 c1, c2 的新视图

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;

带有 CHECK OPTION 校验选项的视图

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t
WITH [CASCADED | LOCAL] CHECK OPTION;

创建递归视图

CREATE 递归处理 VIEW v
AS
select-statement -- 锚点基础部分
UNION [ALL]
select-statement; -- 递归部分

创建临时视图

CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t;

删除视图

DROP VIEW view_name;

#触发器管理 (Managing triggers)

创建或修改触发器

CREATE OR REPLACE TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;

触发时机 (WHEN)

触发时机 说明
BEFORE 在事件发生之前触发执行
AFTER 在事件发生之后触发执行

事件类型 (EVENT)

事件类型 说明
INSERT 执行 INSERT 插入时触发
UPDATE 执行 UPDATE 更新时触发
DELETE 执行 DELETE 删除时触发

触发级别 (TRIGGER_TYPE)

触发级别 说明
FOR EACH ROW 行级触发器 (每影响一行触发一次)
FOR EACH STATEMENT 语句级触发器 (每条 SQL 触发一次)

#索引管理 (Managing indexes)

在 t 表的 c1, c2 字段上创建复合索引

CREATE INDEX idx_name
ON t(c1,c2);

在 t 表的 c3, c4 字段上创建唯一索引

CREATE UNIQUE INDEX idx_name
ON t(c3,c4);

删除指定索引

DROP INDEX idx_name ON t;

#MySQL 数据类型

#字符串类型 (Strings)

数据类型 占用长度/范围
CHAR 定长字符串 (0 - 255 字节)
VARCHAR 变长字符串 (0 - 255 字节)
TINYTEXT 短文本 (0 - 255 字节)
TEXT 标准文本 (0 - 65535 字节)
BLOB 二进制长对象 (0 - 65535 字节)
MEDIUMTEXT 中文本 (0 - 16777215 字节)
MEDIUMBLOB 中二进制长对象 (0 - 16777215 字节)
LONGTEXT 极大文本 (0 - 4294967295 字节)
LONGBLOB 极大二进制长对象 (0 - 4294967295 字节)
ENUM 单选枚举类型
SET 多选集合类型

#日期与时间类型 (Date & time)

数据类型 标准时间格式（格式记号保持英文）
DATE yyyy-MM-dd
TIME hh:mm:ss
DATETIME yyyy-MM-dd hh:mm:ss
TIMESTAMP yyyy-MM-dd hh:mm:ss
YEAR yyyy

#数值类型 (Numeric)

数据类型 范围与说明
TINYINT x 微小整型 (-128 至 127)
SMALLINT x 小整型 (-32768 至 32767)
MEDIUMINT x 中整型 (-8388608 至 8388607)
INT x 标准整型 (-2147483648 至 2147483647)
BIGINT x 极大整型 (-9223372036854775808 至 9223372036854775807)
FLOAT 单精度浮点数
DOUBLE 双精度浮点数
DECIMAL 高精度定点数 (以字符串形式存储，适合货币金额)

#MySQL 函数与运算符

#字符串函数 (Strings)

#日期与时间函数 (Date and Time)

#数学函数 (Numeric)

#聚合函数 (Aggregate)

#JSON 函数 (JSON)

#类型显式转换 (Cast)

#流程控制函数 (Flow Control)

#系统与信息函数 (Information)

#加密与压缩函数 (Encryption and Compression)

#锁函数 (Locking)

#位运算函数 (Bit)

#杂项工具函数 (Miscellaneous)

#🔗 参考资源