r3f.cn
GitHub Repo stars

MySQL

SQL速查表为您提供了最常用的SQL语句以供参考。

#入门

#连接 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]

#常用命令

#数据库

- -
CREATE DATABASE db ; 创建数据库
SHOW DATABASES; 列出数据库
USE db; 切换数据库
CONNECT db ; 切换数据库
DROP DATABASE db; 删除数据库

#表

- -
SHOW TABLES; 列出当前数据库的表
SHOW FIELDS FROM t; 列出表的字段
DESC t; 显示表结构
SHOW CREATE TABLE t; 显示创建表的SQL
TRUNCATE TABLE t; 删除表中的所有数据
DROP TABLE t; 删除表

#进程

- -
show processlist; 列出进程
kill pid; 杀死进程

#其他

- -
exit 或 \q 退出MySQL会话

#备份

创建备份

mysqldump -u user -p db_name > db.sql

导出不带模式的数据库

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

恢复备份

mysql -u user -p db_name < db.sql

#MySQL 示例

#管理表

创建一个包含三列的新表

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;

#从表中查询数据

查询表中 c1, c2 列的数据

SELECT c1, c2 FROM t

查询表中的所有行和列

SELECT * FROM t

查询数据并使用条件过滤行

SELECT c1, c2 FROM t
WHERE condition

查询表中的不同行

SELECT DISTINCT c1 FROM t
WHERE condition

按升序或降序对结果集进行排序

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

跳过 offset 行并返回接下来的 n 行

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

使用聚合函数对行进行分组

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

使用 HAVING 子句过滤组

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

#从多个表查询

内连接 t1 和 t2

SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition

左连接 t1 和 t1

SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition

右连接 t1 和 t2

SELECT c1, c2
FROM t1
RIGHT JOIN t2 ON condition

执行全外连接

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

生成表中行的笛卡尔积

SELECT c1, c2
FROM t1
CROSS JOIN t2

执行交叉连接的另一种方法

SELECT c1, c2
FROM t1, t2

使用 INNER JOIN 子句将 t1 连接到自身

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

使用 SQL 运算符组合来自两个查询的行

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

返回两个查询的交集

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

从另一个结果集中减去一个结果集

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

使用模式匹配查询行 %, _

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

查询列表中的行

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

查询两个值之间的行

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

检查表中的值是否为 NULL

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

#使用 SQL 约束

将 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 中的值唯一

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

确保 c1 > 0 且 c1 中的值 >= c2

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
);

#修改数据

向表中插入一行

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;

#管理视图

创建一个由 c1 和 c2 组成的新视图

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

创建一个带检查选项的新视图

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

创建递归视图

CREATE RECURSIVE VIEW v
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part

创建临时视图

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

删除视图

DROP VIEW view_name;

#管理触发器

创建或修改触发器

CREATE OR MODIFY 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

#管理索引

在 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 数据类型

#字符串

- -
CHAR 字符串 (0 - 255)
VARCHAR 字符串 (0 - 255)
TINYTEXT 字符串 (0 - 255)
TEXT 字符串 (0 - 65535)
BLOB 字符串 (0 - 65535)
MEDIUMTEXT 字符串 (0 - 16777215)
MEDIUMBLOB 字符串 (0 - 16777215)
LONGTEXT 字符串 (0 - 429496­7295)
LONGBLOB 字符串 (0 - 429496­7295)
ENUM 预设选项之一
SET 预设选项的选择

#日期和时间

数据类型 格式
DATE yyyy-MM-dd
TIME hh:mm:ss
DATETIME yyyy-MM-dd hh:mm:ss
TIMESTAMP yyyy-MM-dd hh:mm:ss
YEAR yyyy

#数字

- -
TINYINT x 整数 (-128 到 127)
SMALLINT x 整数 (-32768 到 32767)
MEDIUMINT x 整数 (-8388608 到 8388607)
INT x 整数 (-2147­483648 到 214748­3647)
BIGINT x 整数 (-9223­372­036­854­775808 到 922337­203­685­477­5807)
FLOAT 小数 (精确到 23 位)
DOUBLE 小数 (24 到 53 位)
DECIMAL 作为字符串存储的 "­DOU­BLE­"

#MySQL 函数和运算符

#字符串

#日期和时间

#数字

#聚合

#JSON

#类型转换

#流程控制

#信息

#加密和压缩

#锁定

#位运算

#其他

#另请参阅