跳到主要内容

MySQL 备忘清单

入门​

介绍​

MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成，如下所示的一个表格：

  • 表头（header）：每一列的名称
  • 列（Col）：具有相同数据类型的数据的集合
  • 行（row）：每一行用来描述某个事物的具体信息
  • 值（value）：行的具体信息，每个值与该列数据类型相同
  • 键（key）：用来识别某个特定事物的方法，具有唯一性

登录 MySQl​

-- 默认用户名<root>，-p 是密码，
-- ⚠️参数后面不需要空格
mysql -h 127.0.0.1 -u <用户名> -p<密码>
mysql -D 数据库名 -h 主机名 -u 用户名 -p
mysql -h <host> -P <端口号> -u <user> -p [db_name]
mysql -h <host> -u <user> -p [db_name]

示例：

mysql -u root -p

退出 MySQL 会话​

exit

-- 或
quit

-- 或 
\q

查看 MySQL 信息​

-- 显示当前mysql的version的各种信息
status;

-- 显示当前mysql的version信息
select version(); 

-- 查看 MySQL 端口号
show global variables like 'port';

常用​

数据库 Database：

-- 列出所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE db;
-- 示例
CREATE DATABASE IF NOT EXISTS school;

-- 切换到数据库
USE db;
-- 或
CONNECT db;

-- 删除数据库
DROP DATABASE db;

表 Table：

-- 列出当前数据库的表
SHOW TABLES;

-- 表的列表字段
SHOW FIELDS FROM t;

-- 显示表格结构
DESC t;

-- 显示创建表的 SQL 语句
SHOW CREATE TABLE t;

-- 删除表中的所有数据
TRUNCATE TABLE t;

-- 删除表格
DROP TABLE t;

Process：

-- 列出进程
show processlist;

-- 杀死进程
kill pid;

备份​

-- 创建备份
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

增删改查​

管理表格​

-- 创建表
CREATE TABLE t (
    id INT,
    name VARCHAR NOT NULL,
    price INT DEFAULT 0,
    PRIMARY KEY(id)
);
-- 示例：COMMENT 为表注释
CREATE TABLE IF NOT EXISTS first_table(
    first_column INT,
    second_column VARCHAR(100)
) COMMENT '第一个表';

-- 删除表
DROP TABLE t;

-- 添加新列
ALTER TABLE t ADD column;

-- 删除列
ALTER TABLE t DROP column;

-- 添加约束
ALTER TABLE t ADD constraint;

-- 将表t1重命名为t2
ALTER TABLE t1 RENAME t2;

-- 将列c1重命令为c2
ALTER TABLE t1 RENAME c1 TO c2;

-- 将列c1的数据结构改为datatype
ALTER TABLE t1 MODIFY c1 datatype;

-- 删除表中的所有数据
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]

-- 跳过行的偏移并返回下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

修改数据​

-- 在表格中插入一行
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;

使用 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列中的设置值不为NULL
CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);

管理视图​

-- 创建由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;

数据类型​

Strings​

# String (0 - 255)
CHAR

# String (0 - 255)
VARCHAR

# String (0 - 255)
TINYTEXT

# String (0 - 65535)
TEXT

# String (0 - 65535)
BLOB

# String (0 - 16777215)
MEDIUMTEXT

# String (0 - 16777215)
MEDIUMBLOB

# String (0 - 429496­7295)
LONGTEXT

# String (0 - 429496­7295)
LONGBLOB

# One of preset options
ENUM

# Selection of preset options
SET

Date & time​

-- yyyy-MM-dd
DATE

-- hh:mm:ss
TIME

-- yyyy-MM-dd hh:mm:ss
DATETIME

-- yyyy-MM-dd hh:mm:ss
TIMESTAMP

-- yyyy
YEAR

Numberic​

-- Integer (-128 to 127)
TINYINT x

-- Integer (-32768 to 32767)
SMALLINT x

-- Integer (-8388608 to 8388607)
MEDIUMINT x

-- Integer (-2147­483648 to 214748­3647)
INT x

-- Integer (-9223­372­036­854­775808 to 922337­203­685­477­5807)
BIGINT x

-- Decimal (precise to 23 digits)
FLOAT

-- Decimal (24 to 53 digits)
DOUBLE

-- "­DOU­BLE­" stored as string
DECIMAL

函数​

聚合函数​

-- 计算一列值的总和
SUM()

-- 计算一列值的平均值
AVG()

-- 计算行数，可选择性地忽略NULL值
COUNT()

-- 找出一列的最大值
MAX()

-- 找出一列的最小值
MIN()

数学函数​

-- 返回数值的绝对值
ABS(x)
-- 示例
ABS(-5) // 5

-- 四舍五入到指定的小数位数，y为小数位数，默认为0
ROUND(x,y)
-- 示例
ROUND(3.1415,2) // 3.13

-- 向下取整至最接近的整数
FLOOR(x)
-- 示例
FLOOR(3.7) // 3

-- 向上取整至最接近的整数
CEIL(x)
-- 示例
CEIL(3.3) // 4

-- 返回一个数的平方根
SQRT(x)
-- 示例
SQRT(16) // 4

-- 返回x除以y的余数
MOD(x,y)
-- 示例
MOD(10,3) // 1

-- 返回0到1之间的随机数，可选种子值
RAND([seed])
-- 示例
RAND() 或 RAND(123) // 0.345...

日期和时间函数​

-- 返回当前日期和时间
NOW()

-- 返回当前日期
CURDATE()

-- 返回当前时间
CURTIME()

-- 格式化日期时间输出
DATE_FORMAT()

-- 计算两个日期之间相差的天数
DATEDIFF()

-- 将字符串转换为日期格式
STR_TO_DATE()

字符串函数​

-- 连接两个或更多字符串
CONCAT(s1,s2,...)
-- 示例
CONCAT('Hello, ','World!')

-- 转换为小写
LOWER(str)
-- 示例
LOWER('HELLO')

-- 转换为大写
UPPER(str)
-- 示例
UPPER('world')

-- 去除字符串两端空格
TRIM(str)
-- 示例
TRIM(' Hello ')

-- 提取字符串左侧的若干字符
LEFT(str,len)
-- 示例
LEFT('Hello', 3) 

-- 提取字符串右侧的若干字符
RIGHT(str,len)
-- 示例
RIGHT('Hello', 2)

-- 提取字符串中的一部分
SUBSTR(str,pos,len) 
-- 示例
SUBSTR('Hello', 2, 3)

-- 替换字符串中的部分文本
REPLACE(str,from_str,to_str)
-- 示例
REPLACE('Hello', 'l', 'L')

高级函数​

-- 返回 x 的二进制编码，x 为十进制数
BIN(x)
-- 示例
BIN(2)

-- 将字符串 s 转换为二进制字符串
BINARY(s)
-- 示例
BINARY 'RUNOOB'

-- 复合条件函数，根据条件返回不同结果
CASE
-- 示例
CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END

-- 转换数据类型
CAST(x AS type)
-- 示例
CAST('2017-08-29' AS DATE)

-- 返回第一个非空表达式的值
COALESCE(expr1, expr2, ..., expr_n)
-- 示例
COALESCE(NULL, NULL, 'runoob.com', NULL, 'google.com')

-- 返回当前连接的唯一ID
CONNECTION_ID()
-- 示例
CONNECTION_ID()

-- 将 f1 进制数转换为 f2 进制数
CONV(x, f1, f2)
-- 示例
CONV(15, 10, 2)

-- 转换字符串 s 的字符集为 cs
CONVERT(s USING cs)
-- 示例
CHARSET(CONVERT('ABC' USING gbk))

-- 返回当前用户
CURRENT_USER()
-- 示例
CURRENT_USER()

-- 返回当前数据库名
DATABASE()
-- 示例
DATABASE()

-- 条件表达式，expr 为真则 v1，否则 v2
IF(expr, v1, v2)
-- 示例
IF(1 > 0, '正确', '错误')

-- 如果 v1 不为 NULL，则返回 v1，否则返回 v2
IFNULL(v1, v2)
-- 示例
IFNULL(NULL, 'Hello Word')

-- 判断表达式是否为 NULL
ISNULL(expression)
-- 示例
ISNULL(NULL)

-- 返回最近生成的 AUTO_INCREMENT 值
LAST_INSERT_ID()
-- 示例
LAST_INSERT_ID()

-- 若 expr1 等于 expr2，则返回 NULL，否则返回 expr1
NULLIF(expr1, expr2)
-- 示例
NULLIF(25, 25)

示例​

SHOW DATABASES ;

CREATE DATABASE IF NOT EXISTS db20240619;

SHOW TABLES;

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(50) NOT NULL COMMENT '学生名',
    gender VARCHAR(10) NOT NULL COMMENT '性别',
    age INT NOT NULL COMMENT '年龄',
    class VARCHAR(50) NOT NULL COMMENT '班级名',
    score INT NOT NULL COMMENT '分数'
) CHARSET = utf8mb4;

DESC student;

INSERT INTO student (name, gender, age, class, score)
    VALUES
        ('张三', '男',18, '一班',90),
        ('李四', '女',19, '二班',85),
        ('王五', '男',20, '三班',70),
        ('赵六', '女',18, '一班',95),
        ('钱七', '男',19, '二班',80),
        ('孙八', '女',20, '三班',75),
        ('周九', '男',18, '一班',85),
        ('吴十', '女',19, '二班',90),
        ('郑十一', '男',20, '三班',60),
        ('王十二', '女',18, '一班',95),
        ('赵十三', '男',19, '二班',75),
        ('钱十四', '女',20, '三班',80),
        ('孙十五', '男',18, '一班',90),
        ('周十六', '女',19, '二班',85),
        ('吴十七', '男',20, '三班',70),
        ('郑十八', '女',18, '一班',95),
        ('王十九', '男',19, '二班',80),
        ('赵二十', '女',20, '三班',75);

SELECT * FROM student;

SELECT name, score FROM student;

# 用 AS 重新命名列
SELECT name AS "名字", class AS "班级" FROM student;

# 带 WHERE 条件
SELECT name AS "名字", class AS "班级" FROM student WHERE age > 19;

# 条件可以是 AND 连接的多个
SELECT name AS "名字", class AS "班级" FROM student WHERE gender = '男' AND score >= 90;

# 用 LIKE 做模糊查询
SELECT name AS "名字", class AS "班级" FROM student WHERE name like '王%';

# 通过 IN 来指定一个集合
SELECT * FROM student WHERE class IN ('一班', '二班');

# NOT IN
SELECT * FROM student WHERE class NOT IN ('一班', '二班');

# 使用 BETWEEN 指定一个区间
SELECT * FROM student WHERE age BETWEEN 18 AND 20;

# 通过 LIMIT 实现分页返回
SELECT * FROM student LIMIT 0,5;
# 简写
SELECT * FROM student LIMIT 5;
# 第二页数据
SELECT * FROM student LIMIT 5,5;

# 通过 ORDER BY 来指定排序的列，ASC 表示升序，DESC 表示降序
SELECT name, score, age FROM student ORDER BY score ASC , age DESC;

# 分组统计每个班级的平均成绩
SELECT class as '班级', AVG(score) AS '平均成绩' FROM student GROUP BY class ORDER BY '平均成绩' ASC;

# 通过 COUNT 统计班级人数
SELECT class, count(*) as count from student group by class;

# 根据 HAVING 统计
SELECT class, AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;

# DISTINCT 去重
SELECT DISTINCT class FROM student;

# 聚合函数
SELECT AVG(score) as '平均成绩', COUNT(*) as '人数', SUM(score) as '总成绩',
       MIN(score) as '最低分', MAX(score) as '最高分' FROM student;

# 字符串函数
SELECT CONCAT('xx', name, 'yy'), SUBSTR(name,2,3), LENGTH(name), UPPER('aa'), LOWER('TT') FROM student;

# 数值函数
SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);

# 日期函数
SELECT YEAR('2023-06-01 22:06:03'), MONTH('2023-06-01 22:06:03'),DAY('2023-06-01 22:06:03'),DATE('2023-06-01 22:06:03'), TIME('2023-06-01 22:06:03');

# 条件函数
select name, if(score >=60, '及格', '不及格') from student;

SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '良好'ELSE '差' END AS '档次' FROM student;

# 系统函数
select VERSION(), DATABASE(), USER();

参考​