<Java>22 MySQL

本文最后更新于：2023年6月27日 上午

22 MySQL

22.1 数据库

安装数据库，就是在主机安装一个数据库管理系统。该系统可以管理多个数据库。

一个数据库中可以拥有多张表，以保存数据（信息）。这些表的本质仍是文件

表的一行称为一条记录。在 Java 中，一行记录往往用对象表示

下载地址

安装方法

可视化 MySQL 管理软件：Navicat

连接到 MySQL 服务的指令：mysql -h 主机名 -P 端口 -u 用户名 -p密码

  1. -p密码 不要有空格
  2. -p 后面不写密码，回车会要求输入密码
  3. 如果不写 -h 默认是本机
  4. 如果不写 -p 默认是 3306

SQL 语句分类

  • DDL（数据定义语句）：[create 表，库]
  • DML（数据操作语句）：[增加 insert]、[修改 update]、[删除 delete]
  • DQL（数据查询语句）：[select]
  • DCL（数据控制语句）：[管理数据库：如用户权限 revoke grant]

关于数据库的详细说明，见本章附录

22.1.1 创建数据库

CREATE DATABASE IF NOT EXISTS `Melody` CHARACTER SET 'utf8' COLLATE 'utf8_bin';

  1. CHARACTER SET：指定数据库采用的字符集。

    不指定的场合，默认 UTF-8

  2. COLLATE：指定数据库字符集校对规则

    • utf8_bin [区分大小写]（常用）
    • utf8_general_ci [不区分大小写]（默认）

  3. 在创建数据库 · 表时，为了规避关键字，可以使用反引号 ``` `

  4. 创建表时，不指定字符集 · 校对规则的场合，默认和数据库相同

22.1.2 查看 · 删除数据库

SHOW DATABASES;										#1
SHOW CREATE DATABASE `Melody`;						#2
DROP DATABASE [IF EXISTS] `Melody`;					#3
USE `sys`											#4
  1. 显示数据库语句
  2. 显示数据库创建语句（当初创建时的语句）
  3. 数据库删除语句（必须慎用）
  4. 切换数据库

22.1.3 备份 · 恢复数据库

备份（DOS）：mysqldump -u 用户名 -p -B 数据库1 数据库2 > 路径\文件名.sql

恢复（DOS，进入mysql）：Source 文件名.sql;

备份库的表：mysqldunp -u 用户名 -p 数据库 表1 表2 > 路径\文件名.sql

22.2 MySQL 常用数据类型（列类型）

数值类型

  • 整形
    • bit(M) [M 指定位数，默认1，范围 1 ~ 64]
    • tinyint [1 byte]
    • smallint [2 byte]
    • mediumint [3 byte]
    • int [4 byte]（常用）
    • bigint [8 byte]
  • 小数类型
    • float [4 byte 单精度]
    • double [8 byte 双精度]（常用）
    • decimal(M,D) [大小不确定]（常用）

文本类型（字符串）

  • char [0 ~ 255]（常用）
  • carchar [0 ~ 65535]（常用）
  • text [0 ~ 2^16^ - 1]（常用）
  • longtext [ 0 ~ 2^32^ - 1]

二进制数据类型

  • blob [0 ~ 2^16^ - 1]
  • longblob [0 ~ 2^32^ - 1]

时间日期类型

  • date [YYYY-MM-DD]
  • time [HH:mm:SS]
  • datetime [YYYY-MM-DD HH:mm:SS]（常用）
  • timestamp [时间戳]（常用）
  • year [年]

22.2.1 数组类型

#22.2.1.1 整形

  1. 使用规范：在满足需求的情况下，尽量使用占用空间小的类型

  2. 如何定义一个无符号整数：在后面加入 unsigned

    CREATE TABLE `T1` (`ID` INT UNSIGNED);

    无符号是咋呢？以 tinyint 为例，有符号的范围是 [-128, 127]，无符号范围是 [0, 255]

#22.2.1.2 bit（位类型）

  1. bit(m) 中 m 的范围在 [1, 64]
  2. 添加数据的范围按照给定的位数（m）来确定。m = 8 的场合表示一个字节，范围是 255
  3. 显示时，按照 bit 格式（bit(8) 的场合 7 就显示为 00000111）
  4. 查询时，仍能按照十进制数查询

#22.2.1.3 小数类型

  1. float 单精度，double 双精度

  2. decimal(M,D) 可以支持更加精确的小数位。其中 M 是小数位数的总数（精度），D 是小数点后的位数（标度）。

    D = 0 的场合，值没有小数部分。M 最大值是 65，D 最大值是 30

    D 省略的场合，默认为 0；M 省略的场合，默认为 10

    希望小数精度高的场合，推荐使用 decimal(M,D)

22.2.2 文本类型

  1. char(size)：固定长度字符串，size 范围 [0, 255]，最大 255 字符。

  2. varchar(size)：可变长度字符串，（UTF-8）size 范围 [0, 21844]，最大 65532 字节（1 ~ 3 字节要用于记录大小；UTF8 最大为 (65535 - 3) / 3 = 21844 字符）

  3. size 表示字符数，不是字节数。无论中文英文，都最多存放 size 个字符

  4. char(4) 是定长。这个场合，不管输入什么（如 ‘a’）都会占用 4 个字符的空间

    相对的，varchar(4) 是变长。实际占用空间取决于输入的字符（实际数据大小 + 额外的 1 ~ 3 字节）

  5. 存放文本时，也可以使用 text 数据类型。可以把 text 列视为 varchar 列。text 不能有默认值，最大 2^16^ 字节

    希望存放更多字符，还能选择 mediumtext（最大 2^24^ 字节）或 longtext（最大 2^32^ 字节）

22.2.3 时间日期类型

CREATE TABLE `T2` 
		(T1 DATETIME, 
  	    T2 TIMESTAMP 
      		NOT NULL DEFAULT CURRENT_TIMESTAMP 
      		ON UPDATE CURRENT_TIMESTAMP);

后面一坨意为：默认时间戳为当前时间戳

22.3 表

22.2.1 创建表

CREATE TABLE `table_yuheng` (`name` VARCHAR(255), `id` INT, `age` INT) CHARACTER SET 'utf8' COLLATE 'utf8_bin' ENGINE INNODB;

field：指定列名

datatype：指定列类型

character set：字符集

collate：校对规则

engine：引擎

CREATE TABLE genshin2 LIKE genshin;

这个意思是：以 genshin 的结构创建一个表 genshin2

22.2.2 删除 · 修改表

  • 添加列

    ALTER TABLE `表名` ADD `列名` NOT NULL 列类型 DEFAULT 默认值 AFTER 某列名;

    在 表名 的 某列名 后面加入一个 列名 列，类型是 列类型 默认值为 默认值

  • 修改列

    ALTER TABLE `表名` MODIFY `列名` 列类型 NOT NULL DEFAULT 默认值;

    把 表名 的 列名 列修改为 列类型 ，默认值改为 默认值

    ALTER TABLE `表名` CHANGE `列名` `新列名` 列类型 NOT NULL DEFAULT 默认值;

    把 表名 的 列名 列修改为 新列名 ，类型是 列类型 默认值为 默认值

  • 删除列

    ALTER TABLE `表名` DROP `列名`;

    删除 表名 的 列名 列

  • 查看表的结构

    DESC `表名`;

  • 修改表名

    RENAME TABLE `表名` TO `新表名`;

  • 修改表字符集

    ALTER TABLE `表名` CHARACTER SET '字符集';

  • 修改存储引擎

    ALTER TABLE `表名` ENGINE = 引擎;

22.4 数据库的增删改查

C（create 创建）R（read 查找）U（update 修改）D（delete 删除）

22.4.1 INSERT 语句

INSERT INTO `表名` (列名1, 列名2, 列名3)
VALUES (值1, 值2, 值3), (值1a, 值2a, 值3a);

  1. 当不给某个字段值时，如有默认值会添加默认值，否则会报错。

  2. 另外，给表中 所有字段 添加数据的场合，也能不写列名：

    INSERT INTO `表名` VALUES (值1, 值2, 值3);

22.4.2 UPDATE 语句

UPDATE `表名` SET `列名` = 值, `列名2` = 值2 WHERE `条件列` = 条件值;
  1. 对于所有 条件列 = 条件值 的记录，将 列名 列改为 值
  2. SET 子句指示要修改哪些列和给予哪些值
  3. 如果不带 where 条件，会修改所有记录

22.4.3 DELETE 语句

DELETE FROM `表名` WHERE `条件列` = 条件值;
  1. 不能删除一列的值。若要如此做，可以使用 update 语句将一列置空
  2. delete 语句仅能删除记录，不能删除表本身。要如此做，可以使用 [22.2.2] 中的 drop 语句
  3. 如果不带 where 条件，会删除所有记录

22.4.4 SELECT 语句（单表）

SELECT DISTINCT `列1`, `列2` FROM `表名` WHERE 条件;
SELECT * FROM `表名2`;
  1. DISTINCT 是可选的。表示显示结果时，是否去掉重复数据（查询记录的所有字段都相同才会去重）
  2. SELECT 指定查询哪些列的数据。
  3. SELECT * 的场合代表查询所有列
  4. FROM 指定查询哪张表

  • 使用表达式对查询的列进行运算

    SELECT `name`, (chinese + math + english) FROM `student`;

    这个场合，显示的时候是 name 和 chinese + math + english 两列

  • AS 语句

    可以用 AS 语句起个别名

    SELECT `name`, (chinese + math + english) AS `all` FROM `student`;

    这个场合，显示的时候是 name 和 all 两列

  • 用 ORDER BY 语句排序查询结果

    SELECT * FROM `student` ORDER BY `math` DESC;

    以 math 降序排列

    • 降序：DESC
    • 升序：ASC（不写也是默认升序）
    SELECT * FROM `genshin` ORDER BY `age` ASC, `salary` DESC;

    以 age 升序并以 salary 降序排列

  • 统计函数 COUNT

    SELECT COUNT(*) FROM `student`;

    统计表中有多少记录

    SELECT COUNT(*) FROM `student` WHERE `math` > 60;

    统计表中 math > 60 的数目

    SELECT COUNT(`name`) FROM `student`;

    统计表中有多少记录，排除 name 为空的数目

    SELECT COUNT(name), sum(`age`), sum(`salary`) / COUNT(*) 
		FROM `genshin`;

    这样也是可以的

    SELECT COUNT(IF (`name` IS NULL, 1, NULL)) FROM `genshin`;
SELECT COUNT(DISTINCT `name`) FROM `genshin`;

    还有很多方式

  • 分组语句 GROUP BY

    SELECT AVG(`salary`), `age` FROM `genshin` group by `age`;

    按 age 分组统计

    SELECT MAX(`salary`) FROM `genshin` group by `age`, `name`;

    按 age 和 name 分组统计

  • 用 HAVING 语句过滤

    SELECT AVG(salary) FROM genshin GROUP BY country HAVING AVG(age) > 18;

    按 country 分组统计，显示其中 AVG(age) > 18 的组

  • WHERE 语句

    SELECT * FROM `genshin` WHERE `create_time` > '2011-01-01';

    日期可以直接比较

    比起 HAVING，WHERE 是在分组前过滤

  • LIKE 字符

    SELECT * FROM `genshin` WHERE `name` LIKE '_E%';

    下划线 _ 代表一个任意字符

    百分号 % 代表任意个任意字符

  • 分页查询

    SELECT * FROM `genshin` LIMIT num, rows;

    从 num + 1 行开始取，取出 rows 行。num 从 0 开始计数

    SELECT * FROM `genshin` LIMIT 0, 3;
SELECT * FROM `genshin` LIMIT 3, 3;
SELECT * FROM `genshin` LIMIT 6, 3;
...

总结：

SELECT 语句顺序是 GROUP BY >> HAVING >> ORDER BY >> LIMIT

22.4.5 常用的运算符

运算符 说明
比较运算符 > < <= >= = != <> 大于、小于、大于等于、不等于
BETWWEEN ... AND ... 显示某一区间的值
IN(值1, 值2, 值3) 显示在 IN 列表中的值
LIKE '值' NOT LIKE '值' 模糊查询（LIKE 'A%' 即 A 开头就行）
IS NULL 判断是否为空
逻辑运算符 AND 多个条件同时成立
OR 多个条件任一成立
NOT 不成立

22.5 函数

22.5.1 统计函数

  • 计数：COUNT(列)
  • 平均值：AVG(列)
  • 合计值：SUM(列)
  • 最大 · 最小值：MAX(列) MIN(列)

22.5.2 字符串函数

  • CHARSET(str)：返回字串字符集

    举个栗子：

    select charset(name) from genshin;

    输出这个：

    +---------------+
| charset(name) |
+---------------+
| utf8          |
| utf8          |
| utf8          |
+---------------+

  • CONCAT(str1, str2, ...)：连接字串

    举个花生：

    select concat ('我是 ', name, ' 我来自 ', country) from genshin;

    输出这个：

    +-------------------------------------------+
| concat ('我是 ', name, ' 我来自 ', country) |
+-------------------------------------------+
| 我是 Amber 我来自 蒙德                      |
| 我是 QiQi 我来自 保底                       |
| 我是 KeQing 我来自 璃月                     |
+-------------------------------------------+

  • UCASE(str)：转成大写

  • LCASE(str)：转成小写

  • LENGTH(str)：字符串长度

    返回时是按字节返回

  • REPLACE(str, str1, str2)：在 str 中用 str2 替换 str1

  • SUBSTRING(str, po, len)：从 str 的 po 位取 len 个字符

    这里 po 是从 1 开始计数的

  • LEFT(str, len)：从 str 左边取 len 个字符

    RIGHT(str, len)：从 str 右边取 len 个字符

  • INSTR(str, sub)：返回 sub 在 str 出现的位置（没有就返回 0）

  • STRCMP(str1, str2)：逐字符比较两字串大小

  • LTRIM(str)：去除前端空格

    RTRIM(str)：去除后端空格

    TRIM(str)：去除左右空格

22.5.3 数学函数

  • ABS(num)：绝对值

  • BIN(num)：十进制转为二进制

  • CEILING(num)：向上取整

  • CONV(num, from_base, to_base)：进制转换

    SELECT CONV(5, 10, 2) FROM genshin;

    把 5 从（当作） 10 进制转换成 2 进制

  • FLOOR(num)：向下取整

  • FORMAT(num, decimal_places)：保留小数位数

    SELECT FORMAT(3.1415926535897932384626, 2) FROM genshin;

    这个数字四舍五入保留 2 位

  • HEX(num)：转十六进制

  • LEAST(num1, num2, num3, ...)：最小值

  • MOD(num, num2)：求余

  • RAND([seed])：返回随机数（范围 [0, 1]）。如果 seed 不变，返回的随机数也不变

    RAND()：每次返回不同随机数

22.5.4 时间和日期函数

  • CURRENT_DATE()：当前日期

  • CURRENT_TIME()：当前时间

  • CURRENT_TIMESTAMP()：当前时间戳

    CREATE TABLE `T1` ('date' TIMESTAMP 
              NOT NULL DEFAULT CURRENT_TIMESTAMP 
              ON UPDATE CURRENT_TIMESTAMP);

    这一列内容是自动更新的时间戳

  • DATE(datetime)：返回 datetime 的日期部分

  • DATE_ADD(date, INTERVAL D_VALUE D_TYPE)：在 date 中加上日期或时间

    D_VALUE：时间的数值

    D_TYPE：时间类型。可以是 YEAR、MINUTE、HOUR、DAY、SECOND 等

    SELECT * FROM genshin 
WHERE DATE_ADD(`date`, INTERVAL 10 MINUTE) >= NOW();

    显示那些 date 列加上 10 分钟，比现在时间大的记录

  • DATE_SUB(date, INTERVAL D_VALUE D_TYPE)：在 date 上减去一个时间

    SELECT * FROM genshin 
WHERE `date` >= DATE_SUB(NOW(), interval 10 minute);

    显示那些现在时间减去 10 分钟，比 date 小的记录

  • DATEDIFF(date1, date2)：两个日期差（天数）

    是 date1 - date2 的结果，因此结果可能是负数

  • TIMEDIFF(date1, date2)：两个时间差（多少小时、分钟、秒）

  • NOW()：当前日期 + 时间

  • YEAR(datetime)：返回日期的年份

    MONTH(datetime)：返回日期的月份

    DAY(datetime)：返回日期的日数

  • UNIX_TIMESTAMP()：返回 1970-1-1 到现在的毫秒数

  • FROM_UNIXTIME(num)：把一个 UNIX_TIMESTAMP 转换为时间

    FROM_UNIXTIME(num, format)：含有格式

    select distinct from_unixtime(100, '%Y-%m-%d %H:%i:%s') from genshin;

    这个会输出 1970-01-01 08:01:40

    在实际开发中，经常使用 int 保留一个 UNIX_TMIESTAMP。使用时转换为时间

  • LAST_DAY(date)：返回该日期所在月份的最后一天的日期

22.5.5 加密和系统函数

  • USER()：查看用户

    可以查看登录到 MySQL 的右哪些用户，及其 IP

  • DATABASE()：查询当前使用的数据库名称

  • MD5(str)：为字符串计算出一个 MD5 32 的字符串

  • PASSWORD(str)：（另一个）加密函数，常用于对 MySQL 数据库的用户密码加密

22.5.6 流程控制函数

  • IF(expr1, expr2, expr3)：如果 expr1 为 true 则返回 expr2，否则返回 expr3

  • IFNULL(expr1, expr2)：如果 expr1 为 NULL 则返回 expr2，否则返回 expr1

  • ...
SELECT CASE 
		WHEN expr1 THEN expr2
		WHEN expr3 THEN expr4
		ELSE expr5 END
...

    
多重分支。若 expr1 则 expr2，否则若 expr3 则 expr4，否则 expr5

举个蚕豆：

```mysql
SELECT `name`, (SELECT CASE
             WHEN `age` > 100 THEN '?'
             WHEN `age` < 18 THEN '!'
             ELSE `age` END) AS 'age', `country`, `sex` 
		FROM `genshin`;
    输出这个 
    +------+-----+---------+-----+
| name | age | country | sex |
+------+-----+---------+-----+
| 刻晴 | 23  | 璃月    | 女  |
| 凝光 | 28  | 璃月    | 女  |
| 胡桃 | 20  | 璃月    | 女  |
| 甘雨 | ?   | 璃月    | 否  |
| 影   | ?   | 稻妻    | 女  |
| 八重 | ?   | 稻妻    | 女  |
| 温迪 | ?   | 蒙德    | 男  |
| 莫娜 | 22  | 蒙德    | 女  |
| 申鹤 | 25  | 璃月    | 女  |
| 可莉 | !   | 蒙德    | 女  |
+------+-----+---------+-----+

22.6 MySQL 多表查询

多表查询是基于两个和两个以上的表查询。

SELECT * FROM `honkai3`, `genshin`;

这个场合，输出的是两个表相乘（即：取出第一张表的每一行，与第二张表的每一行进行组合）

……这样，就需要加入筛选条件，比如

SELECT * FROM `honkai3`, `genshin` 
		WHERE honkai3.group = genshin.country;

多表查询的条件不能少于 表的个数 - 1，否则会出现笛卡尔集

22.6.1 自连接

自连接：指在同一张表连接查询（将同一表视为两张表）

SELECT * 
		FROM genshin g1, genshin g2
		WHERE g1.name = g2.name;

这样，表 genshin 获得了 g1、g2 的别名，就能自连接了

这里取别名，也可以加上 AS。另外，列名不明确的场合，也也要特别指定

22.6.2 子查询

子查询：指嵌入在其他 SQL 语句中的 select 语句。也叫嵌套查询

  • 单行子查询：指只返回一行数据的子查询语句

    SELECT * FROM genshin WHERE age > (
	SELECT age FROM genshin WHERE name = '可莉'
);

  • 多行子查询：返回多行数据的子查询语句。使用关键字 IN

    SELECT * FROM genshin WHERE country IN (
	SELECT DISTINCT country FROM genshin WHERE age < 30
);

    子查询也可以当作临时表使用。

  • ALL 操作符

    SELECT * FROM genshin WHERE age > ALL(
	SELECT age FROM genshin WHERE country = '蒙德'
);

    上面的代码相当于这个：

    SELECT * FROM genshin WHERE age > (
	SELECT MAX(age) FROM genshin WHERE country = '蒙德'
);

  • ANY 操作符

    SELECT * FROM genshin WHERE age > ANY(
	SELECT age FROM genshin WHERE country = '蒙德'
);

    上面的代码相当于这个：

    SELECT * FROM genshin WHERE age > (
	SELECT MIN(age) FROM genshin WHERE country = '蒙德'
);

  • 多列子查询：指返回多个列数据的子查询语句

    SELECT * FROM honkai3 WHERE (age, sex) = (
	SELECT age, sex 
	FROM genshin 
	WHERE name = '凝光'
);

    子查询列数要和条件列数相等，顺序对应

  • 表的复制

    INSERT INTO genshin_copy (name, age, sex, country)
	SELECT name, age, sex, country FROM genshin;

  • 合并查询：把两个（结构相同的）查询结果合并。

    SELECT * FROM genshin
UNION ALL
SELECT * FROM genshin2;

    这个方法（UINON ALL）不会去重

    SELECT * FROM genshin
UNION
SELECT * FROM genshin2;

    这个方法（UINON）会去重

22.7 外连接

左外连接：左侧的表完全显示（即使没有匹配的记录，也会把左侧表完全显示）

右外连接：右侧的表完全显示

原来的代码：

SELECT genshin.name, genshin.age, honkai3.name
	FROM genshin, honkai3 
	WHERE genshin.age = honkai3.age;

无匹配记录的项不会显示。

左外连接：

SELECT genshin.name, genshin.age, honkai3.name
	FROM genshin LEFT JOIN honkai3 
	ON genshin.age = honkai3.age;

这个场合，genshin 中无匹配记录的项也会显示

右外连接：

SELECT genshin.name, honkai3.age, honkai3.name
	FROM genshin RIGHT JOIN honkai3 
	ON genshin.age = honkai3.age;

这个场合，honkai3 中无匹配记录的项也会显示

22.8 约束

约束：用于确保数据库的数据满足特定的商业规则

约束包括：

  • not null
  • unique
  • primary key
  • foreign key
  • check

22.8.1 primary key（主键）

...
字段名 字段类型 primary key
...

主键：用于唯一的表示表行的数据。当定义主键约束后，该列不能重复

#22.8.1.1 使用细节

  1. 主键不能重复，也不能为 NULL

  2. 一张表只能有一个主键。但，可以是复合主键

    CREATE TABLE `temp1` 
	(`id` INT,
 `name` CHAR(5),
 `location` varchar(32),
 PRIMARY KEY(`id`, `name`));

    这里，(id, name) 构成复合主键。id 和 name 都相同的，不能重复

  3. 主键的指定方式有两种：

    • 字段名 字段类型 primary key
    • （在表定义的最后写）PRIMARY KEY(列名)

  4. 在实际开发中，每个表往往都会设置一个主键

22.8.2 not null（非空）

非空：定义非空约束后，插入数据时，必须为该列提供数据。

22.8.3 unique（唯一）

...
字段名 字段类型 unique
...

唯一：定义了唯一约束后，该列值是不能重复的。

……但是如果没有 not null 约束，则可以有多个 null 值

22.8.4 foreign key（外键）

# 从表
...
foreign key (从表列名) references 主表名(列名)
...

外键：用于定义主表和从表间的关系。外键约束要定义在从表上，主表必须有主键或唯一约束。定义外键约束后，要求外键列数据必须在主表的主键列存在或为 null

#22.8.4.1 使用细节

  1. 外键指向的表的字段，要求是 primary key 或 unique

  2. 表的类型是 innodb，这样的表才支持外键

  3. 外键字段的类型要和主子段类型一致（可以长度不同）

  4. 外键字段的值，必须在主键字段中出现过，或（在外键字段允许的情况下）为 null

  5. 一旦确立主外键关系，数据就不能随意删除了（有任意记录指向主表某记录，该主表记录就不能删除）

  6. 如何删除外键：

    SHOW CREATE TABLE 表名;							#1
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;		#2
    1. 查看该表创建语句，获取外键名称
    2. 删除外键

22.8.5 check

...
字段名 字段类型 check (条件)
...

check：用于强制行数据必须满足的条件。（特别的，MySQL 5.7 中有 check 语法校验，但实际不生效）

22.8.6 自增长

...
字段名 字段类型 primary key auto_increment
...

#22.8.6.1 使用细节

  1. 如果希望自增长，插入数据时该列插入 null

  2. 一般来说自增长是配合 primary key 使用的

  3. 自增长也能（配合 unique）单独使用

  4. 自增长修饰的字段是整数型的（小数类型也可以，但很少这样用）

  5. 自增长从 1 开始。

    alter table 表名 auto_increment = 新的开始值;

    这样，能修改这个自增长的默认值

  6. 如果添加数据时，给自增长字段指定了具体的值，则以指定的值为准

    一般来说，指定了自增长，就按自增长的规则添加顺序

22.9 索引

索引：提高数据库性能，增加查询速度

CREATE INDEX 索引名 ON 表名 (列名);

  • 没有索引时：

    查询的场合总是会进行全表扫描

  • 有索引时：

    形成一个索引的数据结构（如二叉树），根据该结构进行查找

    会造成磁盘的占用，且对 update delete insert 语句的效率产生影响

索引的类型

  1. 主键索引：primary key。主键自动为主索引

  2. 唯一索引：unique

  3. 普通索引：index

  4. 全文索引：fulltext

    一般开发不用 MySQL 自带的全文索引，而是使用全文搜索（Solr）和ElasticSearch（ES）

22.9.1 索引的操作

  1. 查询索引：

    SHOW INDEXES FROM 表名;
SHOW INDEX FROM 表名;
SHOW KEYS FROM 表名;

  2. 添加唯一索引：

    CREATE UNIQUE INDEX 索引名 ON 表名(列名);

  3. 添加普通索引：

    CREATE INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD INDEX 索引名(列名);

    如果某列的值是不会重复的，则优先考虑唯一索引。否则用普通索引。

  4. 添加主键索引（添加主键）：

    ALTER TABLE 表名 ADD PRIMARY KEY (列名);

  5. 删除索引：

    DROP INDEX 索引名 ON 表名;

  6. 删除主键索引：

    ALTER TABLE 表名 DROP PRIMARY KEY;

    因为主键索引只有一个，所以不用指定列名

  7. 修改索引：

    先删除，再添加新的索引 (●'◡'●)

22.9.2 创建规则

  1. 较频繁地作为查询条件字段 适合 作为索引
  2. 唯一性太差的字段（即使频繁作为查询条件）不适合 单独创建索引
  3. 更新非常频繁的字段 不适合 创建索引
  4. 不会出现在 WHERE 子句的字段 不应该 创建索引

22.10 事务

事务：用于保证数据的一致性，由一组相关的 dml 语句（update、insert、delete 语句）组成。该组 dml 语句要么全部成功，要么全部失败。比如转账就要用事务处理，以保证数据的一致性。

锁：执行事务操作时，MySQL 会在表上加锁，防止其他用户修改表的数据。这对用户来讲是非常重要的。

22.10.1 事务的操作

  • start transaction：开始一个事务

  • savepoint 保存点名：设置保存点

  • rollback to 保存点名：回退事务到特定保存点

  • rollback：回退所有事务

  • commit：提交事务，让所有操作生效。

    执行该语句后，会确认事务的变化、删除保存点、释放锁，并让数据生效。

    该语句不能回退。使用该语句后，其它会话（其他连接）将可以查看到事务变化后的新数据。

22.10.2 使用细节

  1. 如果不提交事务，默认情况下，dml 操作是自动提交的，不能回滚。

  2. 开始一个事务，没有创建保存点的场合，可以执行 rollback，默认回退到事务开始时的状态

  3. 可以在这个事务中创建多个保存点

  4. 可以在事务没有提交前选择回退到哪个保存点

  5. MySQL 需要 innoDB 的存储引擎才可以使用。MyISAM 不行

  6. 开始事务：start transaction

    关闭默认自动提交事务：set autocommit = off;

22.10.3 隔离级别

隔离：多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

隔离级别：定义了事务与事务之间的隔离程度。隔离级别是和事务相关的。

查看当前会话隔离级别：select @@tx_isolation;

如果不考虑隔离性，可能引发问题，如：

  • 脏读（dirty read）：

    当一个事务读取另一个事务尚未提交的修改时，产生脏读

  • 不可重复读（nonreapeatable read）：

    同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读

  • 幻读（phantom read）：

    同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

MySQL 隔离级别（4种） 脏读 不可重复读 幻读 加锁读
读未提交（Read uncommitted） √ √ √ 不加锁
读已提交（Read committed） × √ √ 不加锁
可重复读（Reapeatable read） × × × 不加锁
可串行化（Serializable） × × × 加锁

MySQL 的默认隔离级别是 Reapeatable read，一般情况下不需要修改

#22.10.3.1 隔离级别的操作

  1. 查看当前会话隔离级别：

    select @@tx_isolation;

  2. 查看系统隔离级别：

    select @@global.tx_isolation;

  3. 设置当前会话隔离级别：

    set session transaction isolation level 隔离级别

  4. 设置系统隔离级别：

    set global transacion isolation level 隔离级别

  5. 全局修改：修改 mysql.ini 配置文件。

    在 mysql.ini 文件的末尾加上：

    #设置默认隔离级别
transaction-isolation = REPEATABLE-READ

    可选参数有：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE

22.10.4 事务的 ACID 特性

  1. 原子性（Atomicity）：

    事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

  2. 一致性（Consistency）：

    事务必须使数据库从一个一致性状态变换到另外一个一致性状态

  3. 隔离性（Isolation）：

    多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰。多个并发事务之间相互隔离

  4. 持久性（Durability）：

    一个事务一旦被提交，它对数据库中数据的改变就是永久性的。接下来即使数据库发生故障也不应该对其有任何影响。

22.11 MySQL 表类型和存储引擎

MySQL 的表类型由存储引擎（Storage Engines）决定，主要支持六种类型：CSV、Memory（常用）、ARCHIVE、MRG_MYISAM、MyISAM（常用）、InnoDB（常用）

这六类又分为两类：

  • 事务安全型（transaction-safe）：InnoDB
  • 非事务安全型（non-transaction-safe）：其他类型都是非事务安全型
特点 MyISAM InnoDB Memory Archive
批量插入速度 高 低 高 非常高
事务安全 支持
全文索引 支持
锁机制 表锁 行锁 表锁 行锁
存储限制 无 64TB 有（内存） 无
B树索引 支持 支持 支持
哈希索引 支持 支持
集群索引 支持
数据缓存 支持 支持
索引缓存 支持 支持 支持
数据可压缩 支持 支持
空间使用 低 高 无 非常低
内存使用 低 高 中等 低
支持外键 支持
  1. MyISAM 存储引擎不支持事务，也不支持外键。但其访问速度快，对事务完整性没有要求
  2. InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但比起 MyISAM，InnoDB 写入的处理效率差一些，并且会占用更多磁盘空间以保留数据和索引。
  3. Memory 存储引擎使用存在内存中的内容来创建表。每个 Memory 表只实际对应一个磁盘文件。Memory 类型的表访问得非常快，因为它的数据是放在内存中的，并且默认使用 HASH 索引。但是一旦 MySQL 服务关闭，表中的数据就会丢掉（表的结构还在）。

22.11.1 如何选择存储引擎

  • InnoDB：支持事务、支持外键、支持行级锁
  • MyISAM：添加速度快、不支持事务和外键、支持表级锁
  • Memory：数据存储在内存中、执行速度很快、默认支持索引（HASH 表）
  1. 如果你的应用不需要事务，处理的只是基本的 CRUD 操作，那么 MyISAM 速度最快
  2. 如果需要支持事务，选择 InnoDB
  3. Memory 数据存储在内存中，没有 I/O 操作故而速度极快。但内存存储使得其任何修改都会在服务器重启后消失

22.12 视图

视图：一个虚拟表。其内容由查询定义。和其他表一样，视图含有列，其数据来源于真实的表（基表）

  1. 视图是根据基表来创建的。视图是虚拟的表
  2. 视图也有列，其中数据来自基表
  3. 通过视图可以改变基表的数据，基表的改变也会影响视图数据。

22.12.1 基本使用

  • 创建视图：

    CREATE VIEW 视图名 AS SELECT语句;

  • 修改视图：

    ALTER VIEW 视图名 AS SELECT语句;

  • 显示创建视图的指令：

    SHOW CREATE VIEW 视图名;

  • 删除视图：

    DROP VIEW  视图名1,视图名2;

22.12.2 使用细节

  1. 创建视图后，数据库中只有一个视图结构文件，没有视图数据
  2. 视图的数据变化会影响基表，基表数据变化也会影响视图
  3. 视图中可以再使用视图

22.12.3 视图最佳实践

  • 安全：

    一些数据有不能让用户看到的重要信息。藉由建立一个视图，用户可以查询自己需要的字段，而不能查看保密的字段。

  • 性能：

    关系数据库的数据常常分表存储，通过外键建立这些表之间的联系。此时，查询数据库会使用低效的连接（JOIN）。藉由建立一个视图，可以避免使用 JOIN 查询数据。

  • 灵活：

    系统中有一张采用过时设计，即将废弃的旧表，但这张旧表不能轻易修改。藉由建立一个视图，把数据映射到新表，少做很多改动的同时达到了升级数据表的目的。

22.13 MySQL 管理

22.13.1 用户管理

MySQL 的用户数据都储存在系统数据库 mysql 的表 user 中

不同的数据库用户登录到 DBMS 后，根据相应权限，可以操作的数据库和数据对象各不相同

  1. user 表的重要字段：

    • host：允许登录的位置。localhost 的场合表示该用户只允许本机登录。

      也可以指定 ip 地址，如 912.168.3.16

    • user：用户名

    • authentication_string：（通过 password() 函数加密后的）密码

  2. 创建用户：

    CREATE USER '用户名'@'允许登录位置' identified by '密码';

    创建用户，同时指定密码。

    • @ 后面不能加空格

    • 创建用户时，如果不指定 Host，默认为 %。

      % 表示所有 IP 都有连接权限

    • 创建用户也可以这样：

      CREATE USER 'puppet'@'192.168.3.%'

      表示 192.168.3.* 的 IP 都可以登录用户

  3. 删除用户：

    DROP USER '用户名'@'允许登录位置';

    删除用户时，如果 host 不是 %，需要明确指定 '用户'@'host'

  4. 用户修改密码：

    SET PASSWORD = PASSWORD('密码');							#修改自己密码
SET PASSWORD FOR '用户名'@'登录位置' = PASSWORD('密码');		#修改任意用户密码

    修改其他用户密码，需要拥有相应权限

22.13.2 权限管理

  1. 授予权限

    GRANT 权限列表 ON 库.对象名 TO '用户名'@'登录位置' identified by '密码';

    库.对象名 是 *.* 的场合，代表本系统所有库的所有对象

    identified by '密码' 可省略。若写出，则用户存在即修改密码，不存在即创建用户。

  2. 回收用户权限：

    REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登录位置';

  3. 权限生效指令：

    FLUSH PRIVILEGES;

    如果权限没有生效，执行这个指令。

权限列表：

权限 意义
ALL 设置除 GRANT OPTION 外所有权限
ALTER 允许使用 ALTER TABLE
ALTER ROUTINE 更改或取消已存储的子程序
CREATE 允许使用 CREATE TABLE
CREATE ROUTINE 创建已存储的子程序
CREATE TEMPORARY TABLES 允许使用 CREATE TEMPORARY TABLE
CREATE USER 允许使用 CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES
CREATE VIEW 允许使用 CREATE VIEW
DELETE 允许使用 DELETE
DROP 允许使用 DROP TABLE
EXECUTE 允许用户运行已存储的子程序
FILE 允许使用 SELECT…INTO OUTFILE、LOAD DATA INFILE
INDEX 允许使用 CREATE INDEX、DROP INDEX
INSERT 允许使用 INSERT
LOCK TABLES 允许对拥有 SELECT 权限的表使用 LOCK TABLES
PROCESS 允许使用 SHOW FULL PROCESSLIST
REFERENCES -
RELOAD 允许使用 FLUSH
REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE 用于复制星从属服务器（从主服务器中读取二进制日志事件）
SHOW DATABASE 允许使用 SHOW DATABASE
SHOW VIEW 允许使用 SHOW CREATE VIEW
SHUTDOWN 允许使用 mysqladmin shutdown
SUPER 允许使用 CHANGE MASTER、KILL、PURGE MASTER LOGS、SET GLOBAL 语句，mysqladmin debug 命令。允许连接（一次），即使达到 max_connetions
UPDATE 允许使用 UPDATE
USAGE 无权限
GRANT OPTION 允许授予权限

附录

F1 数据库

数据、数据库、数据库管理系统、数据库系统 是数据库中最常用的 4 个基本概念。

数据（Data）：数据是描述事物的符号记录，是信息存在的一种形式。只有通过解释或处理的数据才能称为有用的信息。

数据库（Database）：数据按照一定格式存储在数据库内。数据库中存储的数据具有永久存储、有组织、可共享 这 3 个基本特点。

数据库管理系统（Database Manage System，DBMS）：专门用于建立和管理数据库的一套软件。实现对数据有效的管理和维护。其主要功能包括：

  • 数据定义功能：通过数据库管理系统提供的数据定义语言（Data Definition Language，DDL）定义数据库中的数据对象（表、视图、存储过程、触发器等）。
  • 数据操作功能：通过数据库管理系统提供的数据操作语言（Data Manipulation Language，DML）实现对数据的增删改查
  • 数据库的运行管理功能：数据库的数据是可供多个用户同时使用的共享数据。DBMS 提供了统一的控制管理机制，保证并发使用的安全性，并在发生故障时能对数据库进行正确的恢复
  • 数据库的建立维护功能：数据空间的维护、数据库的备份与恢复功能、数据库的重组织功能、性能监视、分析等
  • 数据组织、存储、管理功能：数据库能对数据进行分类存储和管理以提高存取效率。例如索引查找、顺序查找等
  • 其他功能：主要包括与其他软件的网络通信功能、不同数据库管理系统之间的数据传输及互相访问功能等

数据库系统（Database System）：在计算机中引入数据库技术之后的系统。一个完整的数据库系统包括数据库、数据库管理系统、相关实用工具、应用程序、数据库管理员和用户。其中数据库管理员最终通过 DBMS 维护数据库，用户（程序员和数据库终端用户）则是数据库的服务对象。

F1.1 数据库的三级模式结构

不同的数据库管理系统基本上都遵循三级模式结构，即：用户级、概念级、物理级

具体而言，数据库系统的三级模式是指数据库系统是由：概念模式（Schema）、外模式（Exterrnal Schema）、内模式（Internal Schema）三级构成的

graph BT
A(数据库) --- B[内模式] --模式/内模式映像--- C[概念模式]
C --外模式/模式映像--- C1[外模式1]
C --外模式/模式映像--- C2[外模式2]
C --外模式/模式映像--- C3[外模式3]
C1 --- C1A[应用A]
C1 --- C1B[应用B]
C2 --- C2C[应用C]
C3 --- C3D[应用D]
C3 --- C3E[应用E]

#概念模式

模式也称为概念模式或逻辑模式，它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

模式实际上是数据库数据在逻辑级上的视图，即概念视图。其形式比数据的物理存储方式更为抽象。它不包含数据库的实现细节，仅定义数据的逻辑结构、数据间关系，以及数据相关的安全性、完整性等要求。

数据库按外模式的描述向用户提供数据，按内模式的描述存储数据。而模式是这两者的中间层。

模式不涉及物理存储细节和硬件环境，也无关于具体应用程序和开发语言。

一个数据库只有一个模式，且相对稳定。数据库管理系统提供了模式描述语言（Schema DDL），以严格定义模式。

#外模式

外模式也称为子模式或用户模式，它是数据库用户能看见和使用的局部数据的逻辑结构和特征的描述。

外模式是用户视图，通常是模式的子集，是对数据库整体数据结构的局部重构。如果不同用户在应用需求、看待数据的方式、对数据保密的要求方面存在差异，则外模式描述就不相同。模式中同样的数据，在外模式的表现可以不同。

一个数据库可以有多个不同外模式，且允许彼此间有重叠。一个外模式也可以被多个应用程序共享。

外模式简化了数据库系统的用户接口，便于用户使用，并有效支持了数据独立性和共享性。

数据库管理系统提供了子模式描述语言（Subschema DDL），以严格定义子模式。

#内模式

内模式也称为存储模式，是对数据库中数据物理结构和存储方式的描述，是数据库在内部的表示形式。

内模式是整个数据库的最底层表示。不同于物理层，是数据库管理员看到的，特定数据库管理系统所处理的数据库的内部结构，即内部视图或存储视图

一个数据库只有一个内模式。内模式不是最底层的物理层。数据库管理系统提供了内模式描述语言，以严格定义内模式。

F1.2 映像与数据独立性

构成数据库系统的三级模式分别是对数据的三级抽象。它们彼此间具有以下特点

  • 一个数据库的整体逻辑结构和特征的描述（概念模式）独立于数据库其他层次结构（内/外模式）。概念模式是数据库的核心，也是数据库设计的关键
  • 一个数据库的内部存储模式依赖于概念模式，但独立于外模式，也独立于具体的存储设备。
  • 外模式是在全局逻辑结构基础上定义的，其面向具体的应用程序，特定的应用程序依赖于特定的外模式。外模式独立于概念模式和内模式。

数据库管理系统通过在内部提供三级模式间的两层映像，实现三级抽象和它们间的联系与转换。

这两层映像是：外模式/模式映像、模式/内模式映像

由于这两层映像的存在，使得数据库系统中的数据能具有较高 逻辑独立性 和 物理独立性。数据的定义和描述从应用程序中分离，从而简化了程序开发，也降低了维护难度。

#外模式/模式映像

所谓映像，即是一种对应规则。外模式/模式映像 定义了各个外模式和概念模式间的关系，这些映像通常在各自的外模式中加以描述。对于每个外模式，都会有一个 外模式/模式映像

数据库系统的概念模式改变时，外模式/模式映像 都要相应改变，以使外模式保持不变。由于 外模式/模式映像 的存在，数据库管理员不必修改依赖外模式的应用程序本身。这样，就实现了外模式不受概念模式变化的影响，也保证了数据与程序的逻辑独立性。

#模式/内模式映像

模式/内模式映像 定义了数据库全局逻辑结构和物理存储间的关系。这种映像定义通常是在概念模式中加以描述的。数据库只有一个概念模式，也就只有一个 模式/内模式映像。

数据库系统的物理存储改变时，数据库管理员要对 模式/内模式映像 进行调整，以使概念模式保持不变。如此也实现了概念模式不受内模式变化的影响，并保证了数据与程序的物理独立性。

F1.3 数据模型

一般而言，数据具有动态、静态两种特征。

  • 数据的静态特征：数据的基本结构、数据间的联系、对数据取值范围的约束
  • 数据的动态特征：对数据可以进行符合一定规则的操作

数据模型是一组概念和定义，用来描述数据的结构、定义、在结构上的操纵以及数据间的约束

#数据模型的三个要素

数据模型通常有三个要素：数据结构、数据操作、数据约束

  • 数据结构

    数据结构描述系统的静态特性。其反映了数据模型最基本的特征。

    常按照数据结构类型来命名数据模型。如：层次模型、网状模型、关系模型、面向对象模型

  • 数据操作

    数据操作描述系统的动态特性，是对对象实例允许执行的操作的集合。

    数据操作分为两类：更新（插入、删除、修改）、检索

  • 数据约束

    数据约束描述数据结构中数据间的语法和语义关联，以保证数据的正确性、有效性和相容性

    数据约束包括：完整性约束、数据安全性约束、并发控制约束

#数据模型的分类

graph LR
A(现实世界) --抽象--> B(概念模型) --转换/组织--> C(逻辑模型/物理模型)

  • 概念层数据模型

    概念层是数据抽象级别的最高层。概念层数据模型也称 概念模型 或 信息模型。

    概念模型描述现实世界的事物，而与具体计算机系统无关。

  • 逻辑层数据模型

    逻辑层是数据抽象的中间层，也称为数据的逻辑模型

    逻辑模型描述数据整体的逻辑结构，既要考虑用户理解，也要照顾具体实现

    任何数据库都是基于某种逻辑数据模型。

    • 层次模型

      是数据库最早使用的一种类型。其数据结构是一棵有向树，每个结点对应一个记录集。

      现实中的组织结构就是层次结构。但层次模型难以表达现实中实体间的复杂联系。

    • 网状模型

      是层次模型的扩展，以网状结构表示实体间的联系。

      网状模型可以方便地表示实体间的联系，但结构复杂，难以规范化地实现。

    • 关系模型

      是层次模型的扩展，用二维表来表示实体及实体间联系。

      关系模型建立在严格的数学概念之上。其概念单一，数据结构简单，便于理解。而且，关系模型的存取路径对用户透明，具有更高的数据独立性和安全性，也便于程序员开发和管理。

    • 面向对象模型

      面向对象方法与数据库结合所构成的数据模型称为面向对象模型。

      面向对象模型既是概念模型又是逻辑模型。其表达能力丰富，对象复用性强，维护方便，是数据库的发展方向之一。

  • 物理层数据模型

    也称为数据的物理模型。

    物理模型描述数据在存储介质上的组织结构，是逻辑模型的物理实现。每种逻辑模型都有对应的物理模型。

    物理模型的设计目标是提高数据库性能和有效利用空间。其不仅由数据库设计决定，也与操作系统、计算机硬件密切相关。

Java
#笔记 #Java
<Java>22 MySQL
https://i-melody.github.io/2022/01/22/Java/入门阶段/22 MySQL/
作者
Melody
发布于
2022年1月22日
许可协议