文章归档文章分类文章标签复制本文标题复制本文地址
新窗口打开转到链接复制链接
随便逛逛

MySQL 备忘清单

本备忘单旨在快速理解 MySQL 所涉及的主要概念，提供了最常用的SQL语句，供您参考。

入门

介绍

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

1
2
3
4
5
6
7
8
9
    name ▼ 键            ▼ 列(col)
┌┈┈┈┈┬┈┈┈┈┈┈┈┈┬┈┈┈┈┈┈┬┈┈┈┈┈┈┈┐
┆ id ┆ name   ┆ uid  ┆ level ┆  ◀ 表头header
├┈┈┈┈┼┈┈┈┈┈┈┈┈┤┈┈┈┈┈┈┤┈┈┈┈┈┈┈┤
┆  1 ┆ mysql  ┆ 0    ┆ 3     ┆
├┈┈┈┈┼┈┈┈┈┈┈┈┈┤┈┈┈┈┈┈┤┈┈┈┈┈┈┈┤
┆  2 ┆ redis  ┆ 12   ┆ 1     ┆  ◀ 行 row
└┈┈┈┈┴┈┈┈┈┈┈┈┈┴┈┈┈┈┈┈┴┈┈┈┈┈┈┈┘
    redis ▲ 值
  • 表头(header) 每一列的名称
  • 列(col) 具有相同数据类型的数据的集合
  • 行(row) 每一行用来描述某个人/物的具体信息
  • 值(value) 行的具体信息，每个值与该列数据类型相同
  • 键(key) 用来识别某个特定的人/物的方法，有唯一性

登录MySQL

1
2
3
4
5
6
# 默认用户名<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]

常用的

数据库 Database

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

表 Table

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

Proccess

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

查看 MySQL 信息

1
2
3
4
5
6
# 显示当前mysql的version的各种信息
mysql> status;
# 显示当前mysql的version信息
mysql> select version(); 
# 查看 MySQL 端口号
mysql> show global variables like 'port';

退出MySQL会话

1
mysql> exit

退出 quit; 或 \q; 一样的效果

备份

创建备份

1
mysqldump -u user -p db_name > db.sql

导出不带架构的数据库

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

恢复备份

1
mysql -u user -p db_name < db.sql

MySQL 示例

管理表格

创建一个包含三列的新表

1
2
3
4
5
6
CREATE TABLE t (
    id    INT,
    name  VARCHAR DEFAULT NOT NULL,
    price INT DEFAULT 0
    PRIMARY KEY(id)
);

从数据库中删除表

1
DROP TABLE t ;

向表中添加新列

1
ALTER TABLE t ADD column;

从表中删除列c

1
ALTER TABLE t DROP COLUMN c ;

添加约束

1
ALTER TABLE t ADD constraint;

删除约束

1
ALTER TABLE t DROP constraint;

将表从t1重命名为t2

1
ALTER TABLE t1 RENAME TO t2;

将列c1重命名为c2

1
ALTER TABLE t1 RENAME c1 TO c2 ;

将列c1的数据类型改为datatype

1
ALTER TABLE t1 MODIFY c1 datatype;

删除表中的所有数据

1
TRUNCATE TABLE t;

从表中查询数据

从表中查询列c1、c2中的数据

1
SELECT c1, c2 FROM t

查询表中的所有行和列

1
SELECT * FROM t

查询数据并使用条件筛选行

1
2
SELECT c1, c2 FROM t
WHERE condition

查询表中的不同行

1
2
SELECT DISTINCT c1 FROM t
WHERE condition

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

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

跳过行的偏移并返回下n行

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

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

1
2
3
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1

使用HAVING子句筛选组

1
2
3
4
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition

从多个表查询

内部连接 t1 和 t2

1
2
3
SELECT c1, c2 
FROM t1
INNER JOIN t2 ON condition

左连接t1和t1

1
2
3
SELECT c1, c2 
FROM t1
LEFT JOIN t2 ON condition

右连接t1和t2

1
2
3
SELECT c1, c2 
FROM t1
RIGHT JOIN t2 ON condition

执行完全外部连接

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

生成表中行的笛卡尔积

1
2
3
SELECT c1, c2 
FROM t1
CROSS JOIN t2

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

1
2
SELECT c1, c2 
FROM t1, t2

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

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

使用SQL运算符，合并两个查询中的行

1
2
3
SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2

返回两个查询的交集

1
2
3
SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2

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

1
2
3
SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2

使用模式匹配%查询行_

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

查询列表中的行

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

查询两个值之间的行

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

检查表中的值是否为NULL

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

使用 SQL 约束

将c1和c2设置为主键

1
2
3
4
CREATE TABLE t(
    c1 INT, c2 INT, c3 VARCHAR,
    PRIMARY KEY (c1,c2)
);

将c2列设置为外键

1
2
3
4
5
CREATE TABLE t1(
    c1 INT PRIMARY KEY,  
    c2 INT,
    FOREIGN KEY (c2) REFERENCES t2(c2)
);

使c1和c2中的值唯一

1
2
3
4
CREATE TABLE t(
    c1 INT, c1 INT,
    UNIQUE(c2,c3)
);

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

1
2
3
4
CREATE TABLE t(
  c1 INT, c2 INT,
  CHECK(c1> 0 AND c1 >= c2)
);

c2列中的设置值不为NULL

1
2
3
4
CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);

修改数据

在表格中插入一行

1
2
INSERT INTO t(column_list)
VALUES(value_list);

在表格中插入多行

1
2
3
INSERT INTO t(column_list)
VALUES (value_list), 
       (value_list), …;

将行从t2插入t1

1
2
3
INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

更新列c1中所有行的新值

1
2
UPDATE t
SET c1 = new_value;

更新列c1、c2中与条件匹配的值

1
2
3
4
UPDATE t
SET c1 = new_value, 
        c2 = new_value
WHERE condition;

删除表中的所有数据

1
DELETE FROM t;

删除表中的行子集

1
2
DELETE FROM t
WHERE condition;

管理视图

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

1
2
3
4
CREATE VIEW v(c1,c2) 
AS
SELECT c1, c2
FROM t;

使用选中选项创建新视图

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

创建递归视图

1
2
3
4
5
CREATE RECURSIVE VIEW v 
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part

创建临时视图

1
2
3
4
CREATE TEMPORARY VIEW v 
AS
SELECT c1, c2
FROM t;

删除视图

1
DROP VIEW view_name;

管理触发器

创建或修改触发器

1
2
3
4
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上创建索引

1
2
CREATE INDEX idx_name 
ON t(c1,c2);

在t表的c3、c4上创建唯一索引

1
2
CREATE UNIQUE INDEX idx_name
ON t(c3,c4)

删除索引

1
DROP INDEX idx_name ON t;

MySQL 数据类型

Strings

--
CHARString (0 - 255)
VARCHARString (0 - 255)
TINYTEXTString (0 - 255)
TEXTString (0 - 65535)
BLOBString (0 - 65535)
MEDIUMTEXTString (0 - 16777215)
MEDIUMBLOBString (0 - 16777215)
LONGTEXTString (0 - 429496­7295)
LONGBLOBString (0 - 429496­7295)
ENUMOne of preset options
SETSelection of preset options

Date & time

Data TypeFormat
DATEyyyy-MM-dd
TIMEhh:mm:ss
DATETIMEyyyy-MM-dd hh:mm:ss
TIMESTAMPyyyy-MM-dd hh:mm:ss
YEARyyyy

Numeric

--
TINYINT xInteger (-128 to 127)
SMALLINT xInteger (-32768 to 32767)
MEDIUMINT xInteger (-8388608 to 8388607)
INT xInteger (-2147­483648 to 214748­3647)
BIGINT xInteger (-9223­372­036­854­775808 to 922337­203­685­477­5807)
FLOATDecimal (precise to 23 digits)
DOUBLEDecimal (24 to 53 digits)
DECIMAL“­DOU­BLE­” stored as string

函数

聚合函数

函数解释
SUM()计算一列值的总和
AVG()计算一列值的平均值
COUNT()计算行数，可选择性地忽略NULL值
MAX()找出一列的最大值
MIN()找出一列的最小值

数学函数

函数解释示例语法结果
ABS(x)返回数值的绝对值ABS(-5)5
ROUND(x,y)四舍五入到指定的小数位数，y为小数位数，默认为0ROUND(3.1415,2)3.14
FLOOR(x)向下取整至最接近的整数FLOOR(3.7)3
CEIL(x)向上取整至最接近的整数CEIL(3.3)4
SQRT(x)返回一个数的平方根SQRT(16)4
MOD(x,y)返回x除以y的余数MOD(10,3)1
RAND([seed])返回0到1之间的随机数，可选种子值RAND() 或 RAND(123)0.345…

日期和时间函数

函数解释
NOW()返回当前日期和时间
CURDATE()返回当前日期
CURTIME()返回当前时间
DATE_FORMAT()格式化日期时间输出
DATEDIFF()计算两个日期之间相差的天数
STR_TO_DATE()将字符串转换为日期格式

字符串函数

函数解释示例语法结果
CONCAT(s1,s2,...)连接两个或更多字符串CONCAT('Hello, ','World!')‘Hello, World!’
LOWER(str)转换为小写LOWER('HELLO')‘hello’
UPPER(str)转换为大写UPPER('world')‘WORLD’
TRIM(str)去除字符串两端空格TRIM(' Hello ')‘Hello’
LEFT(str,len)提取字符串左侧的若干字符LEFT('Hello', 3)‘Hel’
RIGHT(str,len)提取字符串右侧的若干字符RIGHT('Hello', 2)‘lo’
SUBSTR(str,pos,len)提取字符串中的一部分SUBSTR('Hello', 2, 3)‘ell’
REPLACE(str,from_str,to_str)替换字符串中的部分文本REPLACE('Hello', 'l', 'L')‘HeLLo’

高级函数

函数解释示例语法结果
BIN(x)返回 x 的二进制编码，x 为十进制数。BIN(2)10
BINARY(s)将字符串 s 转换为二进制字符串。BINARY 'RUNOOB''RUNOOB'（显示效果，实际存储为二进制）
CASE复合条件函数，根据条件返回不同结果。CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END'1 > 0'
CAST(x AS type)转换数据类型。CAST('2017-08-29' AS DATE)2017-08-29
COALESCE(expr1, expr2, …, expr_n)返回第一个非空表达式的值。COALESCE(NULL, NULL, 'runoob.com', NULL, 'google.com')'runoob.com'
CONNECTION_ID()返回当前连接的唯一ID。CONNECTION_ID()4292835（示例值）
CONV(x, f1, f2)将 f1 进制数转换为 f2 进制数。CONV(15, 10, 2)1111
CONVERT(s USING cs)转换字符串 s 的字符集为 cs。CHARSET(CONVERT('ABC' USING gbk))gbk
CURRENT_USER()返回当前用户。CURRENT_USER()guest@%
DATABASE()返回当前数据库名。DATABASE()runoob
IF(expr, v1, v2)条件表达式，expr 为真则 v1，否则 v2。IF(1 > 0, '正确', '错误')'正确'
IFNULL(v1, v2)如果 v1 不为 NULL，则返回 v1，否则返回 v2。IFNULL(NULL, 'Hello Word')'Hello Word'
ISNULL(expression)判断表达式是否为 NULL。ISNULL(NULL)1
LAST_INSERT_ID()返回最近生成的 AUTO_INCREMENT 值。LAST_INSERT_ID()6（示例值）
NULLIF(expr1, expr2)若 expr1 等于 expr2，则返回 NULL，否则返回 expr1。NULLIF(25, 25)NULL

另见

评论
avatar
竹山一叶
技术分享 个人心得
文章
462
标签
31
分类
21
Follow Me
公告
欢迎光临小站，这里是我日常工作和学习中收集和整理的总结，希望能对你有所帮助：）

本站的内容经过个人加工总结而来，也参考了网友们分享的资料，如有侵权，请第一时间联系我，我将及时进行修改或删除😊

欢迎关注我的微信公众号
最新文章
hexo clean 卡住不动: Windows 上它删文件比 rm 慢百倍的真相
hexo clean 卡住不动: Windows 上它删文件比 rm 慢百倍的真相
gulp 压缩后网站 JS 莫名崩溃: uglify-js 只支持 ES5 的静默损坏与修复
gulp 压缩后网站 JS 莫名崩溃: uglify-js 只支持 ES5 的静默损坏与修复
Hexo 里用 pdf.js 预览 PDF 页面塌陷成一条线: viewer.html 必须独立渲染
Hexo 里用 pdf.js 预览 PDF 页面塌陷成一条线: viewer.html 必须独立渲染
Hexo 开启 pjax 后 APlayer 样式丢失或脚本失效: 讲透 pjax 的三个坑
Hexo 开启 pjax 后 APlayer 样式丢失或脚本失效: 讲透 pjax 的三个坑
依赖安装慢与超时: 从 npm 到 pnpm、镜像源与代理的完整排查
依赖安装慢与超时: 从 npm 到 pnpm、镜像源与代理的完整排查

💡 云服务器 / 域名

买服务器或域名时，用这些链接常能领新用户优惠，也能支持本站：
阿里云（返现）
腾讯云 99元/年
腾讯云 特惠

分类
标签
HexoGithubButterflyValineMarkdownGitLinuxSSHNginxIntelliJ IDEA/PyCharmAndroid StudioAndroidVSCodeUVCCameraGradleCMakeChromeToolsC++其他JNAPythonJavaSVNBATJavaScriptVisual Studiopjaxpdf.jsgulpWindows
网站信息
文章数目 :
462
运行时间 :
本站总字数 :
467.1k
本站访客数 :
本站总浏览量 :
最后更新时间 :