Blogs

0%

MySQL基础知识总结

发表于 更新于 分类于
本文字数： 10k 阅读时长 ≈ 17 分钟

总结 MySQL 中的一些基础知识点。

本文尽量按照MySQL 8.0 版本的规则，但是很多参考书籍和资料还是基于 5.* 版本，可能有疏漏。

SQL基础

SQL 全称是 Structure Query Language（结构化查询语言）。主要分为三个类别：

  • DDL（Data Definiton Languages）语句：数据定义语言，这些语句定义的不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的关键字包括create、drop、alter等。
  • DML（Data Manipulation Language）语句：数据操纵语句、用于添加、删除、更新和查询数据库记录，并检查数据完整性，常见的语句关键字包括insert、delete、update和select等。
  • DCL（Data Control Language）语句：数据控制语句，用于控制不同的数据段直接的许可和访问级别的语句。这些语句定义的数据库、表、字段、用户的访问权限和安全级别。主要语句关键字包括grant、revoke等。

DDL语句

数据库增删改查

数据库创建的格式如下：

1
CREATE DATABASE db_name
2
    [[DEFAULT] CHARACTER SET charset_name]
3
    [[DEFAULT] COLLATE collation_name]

常用的字符集参数为：

1
create database test_database default CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

建议使用utf8mb4代替utf8，后者在存储比如emoji表情的时候会出错。collate关键字代表的是字符排序规则，会影响结果展示的顺序和order by等排序参数。

删除数据库语句：

1
drop database db_name;

要修改字符集参数的话：

1
ALTER DATABASE db_name
2
    [[DEFAULT] CHARACTER SET charset_name]
3
    [[DEFAULT] COLLATE collation_name]

MySQL似乎是没有提供对数据库重命名的方法。

如果想查看一共有多少个数据库，可以用：

1
show databases;

会显示包含系统库在内的所有数据库。

表的增删改查

创建表的语句比较复杂，直接把官网文档上的完整表述贴了过来：

1
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
2
    (create_definition,...)
3
    [table_options]
4
    [partition_options]
5
6
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
7
    [(create_definition,...)]
8
    [table_options]
9
    [partition_options]
10
    [IGNORE | REPLACE]
11
    [AS] query_expression
12
13
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
14
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
15
16
create_definition:
17
    col_name column_definition
18
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
19
      [index_option] ...
20
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
21
      [index_option] ...
22
  | [CONSTRAINT [symbol]] PRIMARY KEY
23
      [index_type] (key_part,...)
24
      [index_option] ...
25
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
26
      [index_name] [index_type] (key_part,...)
27
      [index_option] ...
28
  | [CONSTRAINT [symbol]] FOREIGN KEY
29
      [index_name] (col_name,...)
30
      reference_definition
31
  | check_constraint_definition
32
33
column_definition:
34
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
35
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
36
      [COMMENT 'string']
37
      [COLLATE collation_name]
38
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
39
      [STORAGE {DISK|MEMORY}]
40
      [reference_definition]
41
      [check_constraint_definition]
42
  | data_type
43
      [COLLATE collation_name]
44
      [GENERATED ALWAYS] AS (expr)
45
      [VIRTUAL | STORED] [NOT NULL | NULL]
46
      [UNIQUE [KEY]] [[PRIMARY] KEY]
47
      [COMMENT 'string']
48
      [reference_definition]
49
      [check_constraint_definition]
50
51
data_type:
52
    (see Chapter 11, Data Types)
53
54
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
55
56
index_type:
57
    USING {BTREE | HASH}
58
59
index_option:
60
    KEY_BLOCK_SIZE [=] value
61
  | index_type
62
  | WITH PARSER parser_name
63
  | COMMENT 'string'
64
  | {VISIBLE | INVISIBLE}
65
66
check_constraint_definition:
67
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
68
69
reference_definition:
70
    REFERENCES tbl_name (key_part,...)
71
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
72
      [ON DELETE reference_option]
73
      [ON UPDATE reference_option]
74
75
reference_option:
76
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
77
78
table_options:
79
    table_option [[,] table_option] ...
80
81
table_option:
82
    AUTO_INCREMENT [=] value
83
  | AVG_ROW_LENGTH [=] value
84
  | [DEFAULT] CHARACTER SET [=] charset_name
85
  | CHECKSUM [=] {0 | 1}
86
  | [DEFAULT] COLLATE [=] collation_name
87
  | COMMENT [=] 'string'
88
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
89
  | CONNECTION [=] 'connect_string'
90
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
91
  | DELAY_KEY_WRITE [=] {0 | 1}
92
  | ENCRYPTION [=] {'Y' | 'N'}
93
  | ENGINE [=] engine_name
94
  | INSERT_METHOD [=] { NO | FIRST | LAST }
95
  | KEY_BLOCK_SIZE [=] value
96
  | MAX_ROWS [=] value
97
  | MIN_ROWS [=] value
98
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
99
  | PASSWORD [=] 'string'
100
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
101
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
102
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
103
  | STATS_SAMPLE_PAGES [=] value
104
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
105
  | UNION [=] (tbl_name[,tbl_name]...)
106
107
partition_options:
108
    PARTITION BY
109
        { [LINEAR] HASH(expr)
110
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
111
        | RANGE{(expr) | COLUMNS(column_list)}
112
        | LIST{(expr) | COLUMNS(column_list)} }
113
    [PARTITIONS num]
114
    [SUBPARTITION BY
115
        { [LINEAR] HASH(expr)
116
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
117
      [SUBPARTITIONS num]
118
    ]
119
    [(partition_definition [, partition_definition] ...)]
120
121
partition_definition:
122
    PARTITION partition_name
123
        [VALUES
124
            {LESS THAN {(expr | value_list) | MAXVALUE}
125
            |
126
            IN (value_list)}]
127
        [[STORAGE] ENGINE [=] engine_name]
128
        [COMMENT [=] 'string' ]
129
        [DATA DIRECTORY [=] 'data_dir']
130
        [INDEX DIRECTORY [=] 'index_dir']
131
        [MAX_ROWS [=] max_number_of_rows]
132
        [MIN_ROWS [=] min_number_of_rows]
133
        [TABLESPACE [=] tablespace_name]
134
        [(subpartition_definition [, subpartition_definition] ...)]
135
136
subpartition_definition:
137
    SUBPARTITION logical_name
138
        [[STORAGE] ENGINE [=] engine_name]
139
        [COMMENT [=] 'string' ]
140
        [DATA DIRECTORY [=] 'data_dir']
141
        [INDEX DIRECTORY [=] 'index_dir']
142
        [MAX_ROWS [=] max_number_of_rows]
143
        [MIN_ROWS [=] min_number_of_rows]
144
        [TABLESPACE [=] tablespace_name]
145
146
query_expression:
147
    SELECT ...   (Some valid select or union statement)

可以看到创建表的时候，主要分为三个部分，包括创建定义、表选项、分区选项等，实际上大多数时候用不到这么多功能。

不手动指定的话，模式使用InnoDB作为数据库引擎，InnoDB最大支持40亿张表。

一个简单的创建语句：

1
create table test_table
2
(
3
    id    int not null auto_increment primary key,
4
    name  varchar(20),
5
    class varchar(20),
6
    age   int
7
)engine=InnoDB character set utf8mb4;

创建完成后，可以使用desc关键字查看表结构：

1
desc tablename;

desc命令输出的信息相对比较简单，可以使用如下语句查看详细的建表语句：

1
show create table tablename;

修改表结构使用alter table语句：

  • 修改表类型：ALTER TABLE table_name MODIFY [COLUMN] column_definition [FIRST|AFTER colname]

  • 增加表字段：ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST|AFTER col_name]

  • 删除表字段：ALTER TABLE table_name DROP [COLUMN] col_name

  • 修改字段名：ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

  • 修改字段排列顺序：前面介绍的的字段增加和修改语法(ADD/CNAHGE/MODIFY)中,都有一个可选项 first|after
    column_name,这个选项可以用来修改字段在表中的位置,默认 ADD 增加的新字段是加在
    表的最后位置,而 CHANGE/MODIFY 默认都不会改变字段的位置。

  • 重命名表，老版本的MySQL一般使用：ALTER TABLE test_table RENAME to test_table02;

    MySQL 8.0中支持rename关键字：rename table test_table02 to test_table;

DML语句

插入记录

表创建好后,就可以往里插入记录了,插入记录的基本语法如下:

1
INSERT INTO tablename (field1,field2,......fieldn) VALUES(value1,value2,......valuesn);

更新记录

对于表里的记录值,可以通过 update 命令进行更改,语法如下:

1
UPDATE tablename SET field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]

删除记录

如果记录不再需要,可以用 delete 命令进行删除,语法如下:

1
DELETE FROM tablename [WHERE CONDITION]

查询记录

SELECT 的语法很复杂,所有这里只介绍最基本的语法:

1
SELECT * FROM tablename [WHERE CONDITION]

太复杂的就不重复了，这些都是基础内容，网上的教程一大把。

DCL语句

DCL 语句主要是 DBA 用来管理系统中的对象权限时所使用,一般的开发人员很少使用。只记录几条经常用的吧。

这里经常遇到的就是MySQL 8.0和之前版本的不一致，往往在网上搜到一个命令，拿过来不能用，MySQL 8.0的变动以下几个方面：

验证插件和密码加密方式的变化

在 MySQL 8.0 中，caching_sha2_password 是默认的身份验证插件而不是之前版本的 mysql_native_password，默认的密码加密方式是 SHA2 。

用户授权和修改密码

之前版本：

1
GRANT ALL PRIVILEGES ON *.* TO `mike`@`%` IDENTIFIED BY '000000' WITH GRANT OPTION;

MySQL 8.0版本中正确的授权语句：

1
CREATE USER 'mike'@'%' IDENTIFIED BY '000000';
2
GRANT ALL ON *.* TO 'mike'@'%' WITH GRANT OPTION;

可以看到，创建用户和授权是分开的，不再能一个语句搞定。

修改密码：

1
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
2
FLUSH PRIVILEGES;

也可以使用mysqladmin命令:

1
mysqladmin -u root -p password 新密码

还有很多其他内容，作为开发不用完全掌握，可以详见这个博文：MySQL 8.0用户和角色管理

MySQL数据类型

数值类型

MySQL 支持所有标准 SQL 中的数值类型,其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION),并在此基础上做了扩展。扩展后增加了TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整型,并增加了 BIT 类型,用来存放位数据。

整型数据

对于整型数据,MySQL 还支持在类型名称后面的小括号内指定显示宽度,例如 int(5)表示当数值宽度小于 5 位的时候在数字前面填满宽度,如果不显示指定宽度则默认为 int(11)，一般配合 zerofill 使用。

设置了宽度限制后,如果插入大于宽度限制的值,不会对插入的数据有任何影响,还是按照类型的实际精度进行保存。

小数

对于小数的表示，MySQL 分为两种方式:浮点数和定点数。浮点数包括 float(单精度)和 double(双精度),而定点数则只有 decimal 一种表示。定点数在 MySQL 内部以字符串形式存放,比浮点数更精确,适合用来表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示 M 位数字（整数位 + 小数位），其中 D 位位于小数点后面，M 和 D 又称为精度和标度。

由于float和double分别以 32 位和 64 位存储，所以实际上是可能产生精度丢失的。比如一个 float 类型的列，插入 0.123456789 的时候，实际存储到数据库的只是近似值 0.123457,，当位数更多的时候这个问题更明显，比如分别向 float 和 double 类型的列中插入 987654321.123456789，会得到 987654000 和 987654321.1234568。

所以想要存储高精度、准确的数字，还是需要用 decimal 类型。

日期时间类型

MySQL中的事件类型有：DATE、DATETIME、TIMESTAMP、TIME、YEAR

这些数据类型的主要区别如下:

  • 如果要用来表示年月日,通常用 DATE 来表示。
  • 如果要用来表示年月日时分秒,通常用 DATETIME 表示。
  • 如果只用来表示时分秒,通常用 TIME 来表示。
  • 如果需要经常插入或者更新日期为当前系统时间,则通常使用 TIMESTAMP 来表示。TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串,显示宽度固定为 19 个字符。如果想要获得数字值,应在 TIMESTAMP 列添加+0。
  • 如果只是表示年份,可以用 YEAR 来表示,它比 DATE 占用更少的空间。YEAR 有 2 位或4 位格式的年。默认是 4 位格式。在 4 位格式中,允许的值是 19012155 和 0000。在2 位格式中,允许的值是 7069,表示从 1970~2069 年。 MySQL 以 YYYY 格式显示 YEAR值。

字符串类型

MySQL 包括了 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等多种字符串类型。下面的表详细列出了这些字符类型的比较。

CHAR和VCHAR类型

CHAR 和 VARCHAR 很类似,都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0255 的任何值；而 VARCHAR 列中的值为可变长字符串，长度可以指定为065535( 5.0.3以后)之间的值。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不包含非二进制字符串。

ENUM 类型

ENUM 中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对 1255 个成员的枚举需要 1 个字节存储；对于 25565535 个成员，需要 2 个字节存储。最多允许有 65535 个成员。

SET 类型

Set 和 ENUM 类型非常类似，也是一个字符串对象，里面可以包含 0~64 个成员。根据成员的不同，存储上也有所不同。

  • 1~8 成员的集合,占 1 个字节。
  • 9~16 成员的集合,占 2 个字节。
  • 17~24 成员的集合,占 3 个字节。
  • 25~32 成员的集合,占 4 个字节。
  • 33~64 成员的集合,占 8 个字节。

其他类型

MySQL 8中还支持多种空间类型，应该是用来存储地理信息之类的结构化信息的，但是没找到太多中文资料，平时也没用过，就不误人子弟了。

另外还有新增的 JSON 类型支持。

创建 JSON 字段

创建 JSON 类型的字段很简单，跟其他数据结构没什么区别：

1
CREATE TABLE t1 (jdoc JSON);

插入 JSON

1
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

数据库会对JSON的格式进行校验，如果插入错误的格式会报错。

1
mysql> INSERT INTO t1 VALUES('[1, 2,');
2
ERROR 3140 (22032) at line 2: Invalid JSON text:
3
"Invalid value." at position 6 in value (or column) '[1, 2,'.

查询 JSON

查询 JSON 中的数据用 column->path 的形式，其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

查询 testproject 表 student 字段中 JSON 对象 id 为 1 的记录：

1
SELECT * FROM testproject WHERE student->'$.id'= 1;

查询 testproject 表 student 字段中 JSON 对象 id 为 1 或 5 的记录：

1
SELECT * FROM testproject WHERE student->'$.id' in (1,5);
2
SELECT * FROM testproject WHERE student->'$.id' = 1 or student->'$.id' = 5;

更新数据

MySQL 并不支持 column->path 的形式进行更新操作。如果是整个 JSON 更新的话，和插入时类似的。如果需要更新JSON中的某个值，需要用系统提供的函数：

  • json_array_append：在json后面追加
  • json_array_insert：在指定下标插入
  • json_replace：只替换已经存在的旧值，不存在则忽略
  • json_set：替换旧值，并插入不存在的新值
  • json_insert：插入新值，但不替换已经存在的旧值
  • json_remove：删除元素

这部分详细操作可以参考官方文档The JSON Data Type和博客mysql支持原生json使用说明