MySQL 基础学习 - Pardofelis

DDL - 数据库与表操作#

以下尖括号表示需要替换的名称或值，方括号表示可选部分；执行前应删除占位符。

建库

1
CREATE DATABASE <DATABASE_NAME>;

使用库

1
USE <DATABASE_NAME>;

查询当前数据库所有表

1
SHOW TABLES;

查询表结构

1
DESC <TABLE_NAME>;

查询指定表的建表语句

1
SHOW CREATE TABLE <TABLE_NAME>;

创建表

1
CREATE TABLE IF NOT EXISTS <TABLE_NAME> (
2
    <COLUMN_NAME> <DATA_TYPE> [NOT NULL] [DEFAULT <VALUE>] [COMMENT '注释'],
3
    <COLUMN_NAME> <DATA_TYPE> [NOT NULL] [DEFAULT <VALUE>]
4
) COMMENT = '表注释';

更新表字段(X)

1
-- 加列
2
ALTER TABLE <TABLE_NAME> ADD COLUMN <COLUMN_NAME> <DATA_TYPE> [COMMENT '注释'];
3
-- 改列类型
4
ALTER TABLE <TABLE_NAME> MODIFY COLUMN <COLUMN_NAME> <DATA_TYPE>;
5
-- 改列名
6
ALTER TABLE <TABLE_NAME> CHANGE COLUMN <OLD_NAME> <NEW_NAME> <DATA_TYPE>;
7
-- 删列
8
ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;

删除表

1
DROP TABLE IF EXISTS <TABLE_NAME>;

删除重建表(X)

1
TRUNCATE TABLE <TABLE_NAME>;

重命名表(X)

1
RENAME TABLE <OLD_TABLE_NAME> TO <NEW_TABLE_NAME>;

删除数据库

1
DROP DATABASE <DATABASE_NAME>;

加索引

1
CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>(<COLUMN_NAME>);

删索引

1
DROP INDEX <INDEX_NAME> ON <TABLE_NAME>;

数据类型#

数值类型

TINYINT
1. 大小：1 byte
2. 有符号范围：（-128，127）
3. 无符号范围（UNSIGNED）：（0，255）
4. 描述：小整数值
SMALLINT
1. 大小：2 bytes
2. 有符号范围：（-32768，32767）
3. 无符号范围（UNSIGNED）：（0，65535）
4. 描述：大整数值
MEDIUMINT
1. 大小：3 bytes
2. 有符号范围：（-8388608，8388607）
3. 无符号范围（UNSIGNED）：（0，16777215）
4. 描述：大整数值
INT/INTEGER
1. 大小：4 bytes
2. 有符号范围：（-2147483648，2147483647）
3. 无符号范围（UNSIGNED）：（0，4294967295）
4. 描述：大整数值
BIGINT
1. 大小：8 bytes
2. 有符号范围：（-2^63，2^63-1）
3. 无符号范围（UNSIGNED）：（0，2^64-1）
4. 描述：极大整数值
FLOAT
1. 大小：4 bytes
2. 描述：单精度近似数，精度约 7 位十进制数字
3. UNSIGNED 属性已弃用，需要非负值时优先使用 CHECK 约束
DOUBLE
1. 大小：8 bytes
2. 描述：双精度近似数，精度约 15 位十进制数字
3. UNSIGNED 属性已弃用，需要非负值时优先使用 CHECK 约束
DECIMAL
1. 有符号范围：依赖于M（精度）和D（标度）的值
2. UNSIGNED 属性已弃用，需要非负值时优先使用 CHECK 约束
3. 例子：12.3->M=3, D=1
4. 描述：小数值（精确定点数）

字符串类型

CHAR
1. 长度：0-255 个字符（实际字节数取决于字符集）
2. 描述：定长字符串
VARCHAR
1. 长度：最多 65,535 个字符；实际最大长度受字符集和行大小限制
2. 描述：不定长字符串
TINYBLOB
1. 大小：最多 255 bytes
2. 描述：不超过 255 字节的二进制数据
TINYTEXT
1. 大小：最多 255 bytes
2. 描述：短文本数据
BLOB
1. 大小：最多 65,535 bytes
2. 描述：二进制数据
TEXT
1. 大小：最多 65,535 bytes
2. 描述：长文本数据
MEDIUMBLOB
1. 大小：最多 16,777,215 bytes
2. 描述：中等长度的二进制数据
MEDIUMTEXT
1. 大小：最多 16,777,215 bytes
2. 描述：中等长度文本数据
LONGBLOB
1. 大小：最多 4,294,967,295 bytes
2. 描述：大容量二进制数据
LONGTEXT
大小：最多 4,294,967,295 bytes 2. 描述：极大文本数据

日期时间类型

DATE
1. 大小：3
2. 范围：1000-01-01 ~ 9999-12-31
3. 格式：YYYY-MM-DD
4. 描述：日期值
TIME
1. 大小：3
2. 范围：-838:59:59 ~ 838:59:59
3. 格式：HH:MM
4. 描述：时间值或持续时间
YEAR
1. 大小：1
2. 范围：1901 ~ 2155
3. 格式：YYYY
4. 描述：年份值
DATETIME
1. 大小：8
2. 范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
3. 格式：YYYY-MM-DD HH:MM
4. 描述：混合日期值和时间值
TIMESTAMP
1. 大小：4
2. 范围：1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC；读写时按会话时区转换
3. 格式：YYYY-MM-DD HH:MM
4. 描述：时间戳

DML - 数据操作#

新增

1
INSERT INTO <TABLE_NAME> (<COLUMN_1>, <COLUMN_2>) VALUES (<VALUE_1>, <VALUE_2>);
2
/*
3
插入数据时，需要保持值与列对应；需要保证值符合列的限制；字符串和日期类型数据应该包含在引号中
4
*/

更新(X)

1
UPDATE <TABLE_NAME> SET <COLUMN_NAME> = <VALUE> WHERE <CONDITION>;

删除(X)

1
DELETE FROM <TABLE_NAME> WHERE <CONDITION>;

替换：主键或唯一键冲突时先删后插

1
REPLACE INTO <TABLE_NAME> (<COLUMN_1>, <COLUMN_2>) VALUES (<VALUE_1>, <VALUE_2>);

插入冲突时更新

1
INSERT INTO <TABLE_NAME> (<COLUMN_1>, <COLUMN_2>) VALUES (<VALUE_1>, <VALUE_2>) AS new
2
ON DUPLICATE KEY UPDATE <COLUMN_2> = new.<COLUMN_2>;

DQL - 数据查找#

查询

1
SELECT <COLUMN_1>, <COLUMN_2> FROM <TABLE_NAME>
2
[WHERE <CONDITION>] [GROUP BY <COLUMN_1>] [HAVING <GROUP_CONDITION>]
3
[ORDER BY <COLUMN_1>] [LIMIT <OFFSET>, <PAGE_SIZE>];

设置别名

1
SELECT <COLUMN> [AS 别名] FROM <TABLE_NAME>;

去重

1
SELECT DISTINCT <COLUMN> FROM <TABLE_NAME>;

聚合函数#

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均数
sum	总计

语法

1
SELECT COUNT(*) FROM <TABLE_NAME>;
2
-- COUNT(*) 统计所有行；COUNT(列名)、SUM、AVG 等忽略 NULL 值
3

4
-- 分组与分组后过滤
5
SELECT <GROUP_COLUMN>, COUNT(*) FROM <TABLE_NAME> [WHERE <CONDITION>] GROUP BY <GROUP_COLUMN> [HAVING <GROUP_CONDITION>];
6

7
-- 排序（升序asc,降序desc,默认为升序）
8
SELECT <COLUMN> FROM <TABLE_NAME> ORDER BY <COLUMN> [ASC | DESC];
9
-- 当存在多个排序条件时，当前一个字段值相同时，才会根据第二个字段进行排序
10

11
-- 分页
12
SELECT <COLUMN> FROM <TABLE_NAME> LIMIT <OFFSET>, <PAGE_SIZE>;
13
-- 起始索引一般为(查询页码-1)页x每页显示记录数量，MYSQL的方言是LIMIT，如果查询的是第一页，起始索引(0)可以省略

WHERE & HAVING的区别

执行时机：WHERE要先于聚合，HAVING后于聚合
判断条件：WHERE不能对聚合函数进行判断，HAVING可以

执行顺序#

1
FROM
2
WHERE
3
GROUP BY
4
HAVING
5
SELECT
6
ORDER BY
7
LIMIT

DCL - 用户与权限#

用户管理#

查询用户

1
USE mysql;
2
SELECT User, Host FROM mysql.user;

创建用户

1
CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';

修改用户密码

1
ALTER USER 'USERNAME'@'HOST' IDENTIFIED BY 'NEWPASSWORD';

删除用户

1
DROP USER 'USERNAME'@'HOST';

权限管理#

权限	说明
ALL/ ALL PRIVILEGES	全部权限
SELECT	查询
INSERT	插入
UPDATE	更新
DELETE	删除
ALTER	修改表
DROP	删除表、数据库、视图
CREATE	创建数据库、表

查询权限

1
SHOW GRANTS FOR 'USERNAME'@'HOST';

授予权限

1
GRANT <PRIVILEGE> ON <DATABASE_NAME>.<TABLE_NAME> TO 'USERNAME'@'HOST';

撤销权限

1
REVOKE <PRIVILEGE> ON <DATABASE_NAME>.<TABLE_NAME> FROM 'USERNAME'@'HOST';

多个权限之间可以用逗号隔开授权时，数据库和表名可以用*代替，代表所有

内置函数#

字符串#

函数	说明
CONCAT(S1, S2,…)	拼接字符串
LOWER(STR)	转小写
UPPER(STR)	转大写
LPAD(STR,N,PAD)	用PAD左填充字符直到字符串长度为N
RPAD(STR,N,PAD)	用 PAD 右填充字符直到字符串长度为 N
TRIM(STR)	清除字符串前后空格
SUBSTRING(STR,START,LEN)	取出第START及以后LEN个字符作为子字符串

语法：SELECT FUN(参数);

数值函数#

函数	说明
CEIL(X)	向上取整
FLOOR(X)	向下取整
MOD(X,Y)	取余
RAND()	0~1随机数
ROUND(X,Y)	四舍五入X是原数，Y是保留位数

日期函数#

函数	说明
CURDATE()	当前日期
CURTIME()	当前时间
NOW()	当前日期时间
YEAR(DATE)	日期的年
MONTH(DATE)	日期中的月
DAY(DATE)	日期中的天
DATE_ADD(DATE, INTERVAL EXPR UNIT)	在日期上加 EXPR 个时间单位
DATEDIFF(DATE1, DATE2)	返回 DATE1 与 DATE2 相差的天数

流程函数#

函数	说明
IF(VALUE, T,F)	VALUE为真就返回T，否则返回F
IFNULL(VALUE1, VALUE2)	VALUE1为空就返回VALUE2，否则返回VALUE1
CASE WHEN COND1 THEN RES1 WHEN COND2 THEN RES2 ELSE RESN END	返回第一个满足条件的结果
CASE EXPR WHEN VAL1 THEN RES1 ELSE RESN END	返回与 EXPR 匹配的结果

约束#

概念：作用于表中的字段，限制表中的数据

约束	描述	关键字
非空约束	限制该字段必填	NOT NULL
唯一约束	限制非 `NULL` 值重复；允许 `NULL` 时可有多个 `NULL`	UNIQUE
主键约束	记录的唯一标识，非空且唯一	PRIMARY KEY
默认约束	插入时未指定该字段则使用默认值	DEFAULT
检查约束	字段值必须满足条件	CHECK
外键约束	必须严格根据父表参考字段进行输入	FOREIGN KEY

主键约束自增：AUTO_INCREMENT

外键约束#

让两个表产生联系，保证数据的一致性和完整性拥有外键的叫子表，被参考的叫父表

父表更新、删除行为：

行为	说明
NO ACTION	父表有外键则不允许删除、更新
RESTRICT	与NO ACTION一致
CASCADE	级联删除、更新
SET NULL	父表删除时子表外键置空
SET DEFAULT	父表删除时使用默认值（InnoDB 不支持）

语法：

1
ALTER TABLE <CHILD_TABLE> ADD CONSTRAINT <FK_NAME>
2
FOREIGN KEY (<CHILD_COLUMN>) REFERENCES <PARENT_TABLE> (<PARENT_COLUMN>)
3
ON UPDATE CASCADE ON DELETE CASCADE;

多表关系#

一对多在多的一方建立外键，指向一的一方的主键

多对多需要有中间表，中间表至少包含两个外键，分别关联两方主键

一对一在任意一方加入外键，关联另一张表的主键，并且设置外键为唯一（UNIQUE）的

查询#

内连接#

隐式内连接

1
SELECT <COLUMN> FROM <TABLE1>, <TABLE2>, ... WHERE <OPTION>;

显式内连接

1
SELECT <COLUMN> FROM <TABLE1> INNER JOIN <TABLE2> ON <CONDITION>;

一般需要条件,否则会导致完整的笛卡儿积被查询内连接查询的是两张表的交集部分

外连接#

左外连接

1
SELECT <COLUMN> FROM <TABLE1> LEFT JOIN <TABLE2> ON <CONDITION>;

右外连接

1
SELECT <COLUMN> FROM <TABLE> RIGHT JOIN <TABLE2> ON <OPTION>;

左外连接，需要完整包含左表（第一张表）的所有记录，其查询结果为左表的所有记录加上右表中左右表的交集部分右外连接保留右表（第二张表）的所有记录，并匹配左表中的对应记录；没有匹配时左表列为 NULL。

自连接#

1
SELECT <COLUMN> FROM <TABLE> AS <别名> JOIN <TABLE> AS <别名> ON <OPTION>;

自连接查询可以是内连接也可以是外连接

UNION联合查询#

就是把多次查询的结果合并起来，形成新的查询结果集 UNION 时表示去重合并 UNION ALL时表示不去重合并

1
SELECT <COLUMN> FROM <TABLE>...
2
UNION
3
SELECT <COLUMN> FROM <TABLE>...;

子查询#

SQL语句中嵌套SELECT语句，称为嵌套查询/子查询

1
SELECT * FROM T1 WHERE COLUMN1 = (SELECT COLUMN1 FROM T2);

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）
- 返回的结果是单个值（数字、字符串、日期等）。常用的操作符：= <> > >= < <=
列子查询（子查询结果为一列多个值）
- 常用操作符
  - IN在指定的集合范围之内多选一
  - NOT IN不在指定的集合范围内
  - ANY任意一个满足即可
  - SOME与ANY等价
  - ALL返回列表的所有值都必须满足
行子查询（子查询结果为一行一个记录）
- 常用操作符：=、<>、IN、NOT IN
表子查询（子查询结果为多行多列）
- 常放在 FROM 中作为派生表；多列 IN 比较需要对应的行构造器。

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后

事务#

开启事务

1
START TRANSACTION;
2
BEGIN;

提交事务

1
COMMIT;

回滚事务

1
ROLLBACK;

事务四大特性#

原子性：事务是不可分割的最小单元，要么全部成功要么全部失败
一致性：事务完成时，必须要使得所有数据保持一致状态
隔离性：事务会在不受外部并发操作的影响下运行
持久性：事务一旦提交，其修改会持久保存；回滚不会保存该事务的修改

并发事务问题#

脏读：读到了未提交的数据
不可重复读：一个事务先后读取同一个数据，两次读到的数据不同
幻读：同一事务按相同条件重复查询时，由于其他事务插入或删除了符合条件的行，结果集的行数发生变化

隔离机制#

隔离级别	脏读	不可重复读	幻读
Read uncommitted	Y	Y	Y
Read committed	N	Y	Y
Repeatable Read（InnoDB 默认）	N	N	视查询方式而定*
Serializable	N	N	N

*InnoDB 的一致性读在同一事务中使用快照；锁定读和写操作还会使用锁控制并发，因此不能只用一格“是/否”概括幻读行为。

查看事务隔离级别

1
SELECT @@transaction_isolation;

设置事务隔离级别

1
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL READ COMMITTED;

存储引擎#

存储引擎基于表，所以存储引擎也被成为表类型

在创建表时指定存储引擎

1
CREATE TABLE <TABLE_NAME> (
2
   ...,
3
   ...,
4
   ...
5
) ENGINE = InnoDB [COMMENT = '表注释'];

查看当前数据库支持的引擎

1
SHOW ENGINES;

InnoDB#

是MySQL默认的MySQL引擎特点： DML操作遵循ACID模型，支持事务行级锁，提高并发访问能力支持外键FOREIGN KEY约束，保证数据的完整性和正确性

文件：xxx.ibd 启用独立表空间时，表通常对应一个 .ibd 文件，保存表数据和索引；数据字典由 MySQL 管理。参数：innodb_file_per_table

逻辑存储结构 TableSpace：表空间，包含多个Segment Segment：段，包含多个区 Extent：区，包含多个页，大小为1M Page：页，包含多行，为数据表主力，大小为16K Row：行，包含各种字段

MyISAM#

是MySQL早期默认引擎特点：不支持事务，不支持外键支持表锁，不支持行锁访问速度快

MEMORY#

表数据存储在内存中。由于受到硬件问题、或者断电问题的影响，只能作为临时表或缓存使用。特点：内存存放 hash索引（默认）文件： xxx.sdi：存储表结构信息

存储引擎特点#

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+树索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持（5.6版本之后）	支持	-
空间时间	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

Pardofelis

DDL - 数据库与表操作#

数据类型#

DML - 数据操作#

DQL - 数据查找#

聚合函数#

执行顺序#

DCL - 用户与权限#

用户管理#

权限管理#

内置函数#

字符串#

数值函数#

日期函数#

流程函数#

约束#

外键约束#

多表关系#

查询#

内连接#

外连接#

自连接#

UNION联合查询#

子查询#

事务#

事务四大特性#

并发事务问题#

隔离机制#

存储引擎#

InnoDB#

MyISAM#

MEMORY#

存储引擎特点#

参考资料#

继续阅读

换条路线

MySQL 学习笔记

评论

Pardofelis

DDL - 数据库与表操作#

数据类型#

DML - 数据操作#

DQL - 数据查找#

聚合函数#

执行顺序#

DCL - 用户与权限#

用户管理#

权限管理#

内置函数#

字符串#

数值函数#

日期函数#

流程函数#

约束#

外键约束#

多表关系#

查询#

内连接#

外连接#

自连接#

UNION联合查询#

子查询#

事务#

事务四大特性#

并发事务问题#

隔离机制#

存储引擎#

InnoDB#

MyISAM#

MEMORY#

存储引擎特点#

参考资料#

分享文章

沿着主题读

换条路线

评论