Web 01 - MySQL

一个 FisherMan

已于 2023-08-09 10:14:57 修改

阅读量78

点赞数

于 2022-11-21 18:29:30 首次发布

本文链接：https://blog.csdn.net/m0_65735686/article/details/127970039

版权

Java Web 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

1、MySQL 介绍
2、数据类型
3、数据库、表、列操作
4、查询
5、函数
6、事务与索引
- - 6.1、事务
  - 6.2、索引
7、用户与权限
8、数据库设计规范
- - 8.1、三大范式
  - 8.2、数据库设计规范
9、总结
- - 9.1、重点内容
  - 9.2、常见问题

1、MySQL 介绍

1.1、MySQL 介绍

数据库分类

关系型数据库：SQL 数据库（Structured Query Language，结构化查询语言），通常用于数据持久化。使用数据表维护数据，数据通常存储在硬盘上，虽然数据可以永久保存但是存取速度较慢。常见关系型数据库有：MySQL、Oracle、SQL Server、DB2、SQLlite 等等。
非关系型数据库：NoSQL 数据库（Not Only SQL，不仅仅是 SQL），作为关系型数据库的补充，通常用于缓存。使用键值对维护数据，数据通常存储在内存中，虽然存取速度较快但是数据断电即失。常见非关系型数据库有：Redis、MongoDB 等等。

MySQL

MySQL 关系型数据库管理系统，最早由瑞典 MySQL AB 公司开发，现在属于 Oracle 公司的产品。在 Web 领域中，MySQL 是目前最好的 RDBMS（Relational Database Management System，关系数据库管理系统）应用软件之一。主要特点如下：

开源、免费。
支持大型数据库。
使用标准 SQL 语句。
支持多种存储引擎。
支持多线程。

相关网址

官网下载：https://downloads.mysql.com/archives/。
- MySQL Installer：安装包，这里使用 mysql-installer-community-8.0.26.0.msi。
- MySQL Community Server：压缩包，这里使用 mysql-8.0.26-winx64.zip 。
- MySQL Connector/J：MySQL 驱动 Java 语言版本。

1.2、Windows 版安装

使用安装包 mysql-installer-community-8.0.26.0.msi

一、双击安装，期间需要配置 root 密码。
二、配置环境变量。

MYSQL_HOME=C:\Program Files\MySQL\MySQL Server 8.0
PATH=%MYSQL_HOME%\bin

三、登录验证。注意：如果没有配置 PATH 环境变量，需要进入 C:\Program Files\MySQL\MySQL Server 8.0\bin 目录。

C:\Users\yto> mysql -h localhost -u root -p                     # 登录（回车然后输入密码，连接 localhost）
C:\Users\yto> mysql -u root -p                                  # 登录（回车然后输入密码，默认连接 localhost）

mysql> exit                                                     # 退出
mysql> quit                                                     # 退出

四、更改配置。打开 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini 文件更改相关配置，然后重启服务即可。

C:\Users\yto> net stop mysql                                    # 关闭服务
C:\Users\yto> net start mysql                                   # 启动服务

使用压缩包 mysql-8.0.26-winx64.zip

一、解压后复制到 D:\mysql-8.0.26-winx64 目录，进入该目录并新建全局配置文件 my.ini。

[client] # MySQL 客户端配置

[mysql]  # MySQL 客户端配置

[mysqld] # MySQL 数据库配置
port=3306 									                   # 端口号 3306
basedir=D:\\mysql-8.0.26-winx64      		                   # MySQL 安装目录
datadir=D:\\mysql-8.0.26-winx64\\data                          # 数据存放目录
max_connections=20                                             # 允许最大连接数
character-set-server=utf8mb4                                   # 字符集
collation-server=utf8mb4_0900_ai_ci                            # 校验规则
default-storage-engine=INNODB                                  # 存储引擎
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES            # 创建模式

# skip-grant-tables                                            # 跳过权限检查表

二、配置环境变量。

PATH=D:\mysql-8.0.26-winx64\bin

三、初始化、安装、启动服务、登录验证。注意：如果没有配置 PATH 环境变量，需要进入 D:\mysql-8.0.26-winx64\bin 目录。

C:\Users\yto> mysqld --initialize                               # 初始化
C:\Users\yto> mysqld –-install                                  # 安装
C:\Users\yto> net start mysql                                   # 启动服务

C:\Users\yto> mysql -h localhost -u root -p                     # 登录（回车然后输入密码，连接 localhost）
C:\Users\yto> mysql -u root -p                                  # 登录（回车然后输入密码，默认连接 localhost）

mysql> exit                                                     # 退出
mysql> quit                                                     # 退出

四、更改配置。打开 D:\mysql-8.0.26-winx64\bin\my.ini 文件更改相关配置，然后重启服务即可。

C:\Users\yto> net stop mysql                                    # 关闭服务
C:\Users\yto> net start mysql                                   # 启动服务

1.3、图形化工具

MySQL 自带
SQLyog
IDEA 内置
Navicat

1.4、SQL 语言分类

DDL（Data Definition Language，数据定义语言）：用于操作数据库、表、列。常用语句有 show、use、create、drop、alter 等等。
DML（Data Manipulation Language，数据操纵语言）：用于操作数据库表中的数据。常用语句有 insert、delete、update。
DQL（Data Query Language，数据查询语言）：用于查询数据库表中的数据。常用语句有 select。
DCL（Data Control Language，数据控制语言）：用于操作访问权限与安全级别。常用语句有 grant、revoke。
TPL（Transaction Process Language，事务处理语言）：用于事务提交与回滚。常用语句有 commit、rollback。

1.5、字符集与校验规则

默认字符集与校验规则

MySQL8 总计支持 41 种字符集、272 种校验规则。每种字符集可以对应多种校验规则，但是只有一个默认校验规则。

MySQL8 的默认字符集与校验规则分别为 utf8mb4 与 utf8mb4_0900_ai_ci，通常使用默认即可，或者 utf8mb4 与 utf8mb4_general_ci。注意：不要使用 utf8 与 utf8_general_ci，因为他们分别是 utf8mb3 与 utf8mb3_general_ci 的别名。utf8mb3 最多支持每个字符三个字节（虚假的 utf8），后续版本不再支持。utf8mb4 最多支持每个字符四个字节（真正的 utf8）。

创建数据库、表、列时，可以同时指定字符集与校验规则。如果仅仅指定字符集，则校验规则使用字符集对应的默认值。如果两者都不指定，则依赖上个优先级中的默认值。优先级依次如下：

优先级最高：全局配置文件 my.ini。必须指定。
优先级较高：数据库。如果创建数据库时不指定，依赖全局配置文件 my.ini。
优先级中等：表。如果创建表时不指定，依赖数据库。
优先级最低：列。如果创建列时不指定，依赖表。

CHARSET 字符集

语法

SHOW CHARSET [WHERE ...];-- 查看支持的字符集

示例

SHOW CHARSET;
SHOW CHARSET WHERE `maxlen` > 2;
SHOW CHARSET WHERE `charset` LIKE '%utf8%';

Charset（字符集）	Description（描述）	Default collation（默认编码格式）	Maxlen（最大长度。单位：字节）
eucjpms	UJIS for Windows Japanese	eucjpms_japanese_ci	3
gb18030	China National Standard GB18030	gb18030_chinese_ci	4
ujis	EUC-JP Japanese	ujis_japanese_ci	3
utf16	UTF-16 Unicode	utf16_general_ci	4
utf16le	UTF-16LE Unicode	utf16le_general_ci	4
utf32	UTF-32 Unicode	utf32_general_ci	4
utf8	UTF-8 Unicode	utf8_general_ci	3
utf8mb4	UTF-8 Unicode	utf8mb4_0900_ai_ci	4

COLLATION 校验规则

语法

SHOW COLLATION [WHERE ...];-- 查看支持的校验规则

示例

SHOW COLLATION;
SHOW COLLATION WHERE `charset` = 'utf8mb4';
SHOW COLLATION WHERE `collation` LIKE '%utf8%';

1.6、存储引擎

存储引擎是数据库底层存取数据的格式，关系型数据库通常使用表存储数据，因此也称为表类型。MySQL8 总计支持 8 种存储引擎。

InnoDB（默认）：支持事务、行级锁、外键，但是速度较慢。
MyISAM：不支持事务，但是速度较快。
MEMORY：基于哈希，存储在内存中，用于临时表。

只有创建表时可以指定存储引擎。如果不指定，则依赖全局配置文件 my.ini，默认为 InnoDB。

语法

SHOW ENGINES [WHERE ...];-- 查看支持的存储引擎

示例

SHOW ENGINES;

Engine（存储引擎）	Support（是否支持）	Comment（注释说明）	Transactions（是否支持事务）	XA	Savepoints
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	\N	\N	\N
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO

2、数据类型

数据类型分为六种：整数、浮点数、字符串、文本、日期与时间、二进制。注意：所有数据类型都可能为 NULL 值，不要使用 NULL 值进行运算，因为结果也是 NULL 值。

2.1、整数类型

数据类型	说明	占用空间	取值范围（无符号）	取值范围（有符号）
TINYINT	极小整数	1 个字节	0~255	-128~127
SMALLINT	小整数	2 个字节	0~65535	-32768~32767
MEDIUMINT	中等整数	3 个字节	0~16777215	-8388608~8388607
INT	标准整数	4 个字节	0~4294967295	-2147483648~ 2147483647
BIGINT	大整数	8 个字节	0~18446744073709551615	-9223372036854775808~9223372036854775807

2.2、浮点类型

数据类型	说明	占用空间
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL(M, N)	定长浮点数	M+2 个字节

DECIMAL(M, N) 为定长浮点数，小数部分长度为 N，整数与小数部分长度之和不超过 M。优点是几乎不会有精度问题，一般用于金融行业。

示例

DECIMAL(4, 2) 小数部分长度为 2，整数与小数部分长度之和不超过 4。

USE `mytest`;
CREATE TABLE `type` (
	`money` DECIMAL(4, 2)
);
INSERT INTO `type` (`money`) VALUE (1.1);-- 实际存储内容 1.10
INSERT INTO `type` (`money`) VALUE (1.12345);-- 实际存储内容 1.12
INSERT INTO `type` (`money`) VALUE (12.12345);-- 实际存储内容 12.12
INSERT INTO `type` (`money`) VALUE (123.12345);-- 超出范围
SELECT * FROM `type`;

2.3、字符串类型

CHAR 与 VARCHAR 的区别

CHAR 固定长度字符串：CHAR(M) 总是占用 M 个字节的空间，与插入内容的长度无关，最多存储 M-1 个字节的内容。
VARCHAR 可变长度字符串：VARCHAR(M) 占用的空间取决于插入内容的长度，最多存储 M 个字节的内容。

示例

CHAR(3)

插入内容	插入内容大小	实际存储内容	实际占用空间
‘’	0 个字节	‘’	3 个字节
‘a’	1 个字节	‘a’	3 个字节
‘ab’	2 个字节	‘ab’	3 个字节
‘abc’	3 个字节	‘ab’	3 个字节
‘abcd’	4 个字节	‘ab’	3 个字节

VARCHAR(3)

插入内容	插入内容大小	实际存储内容	实际占用空间
‘’	0 个字节	‘’	1 个字节
‘a’	1 个字节	‘a’	2 个字节
‘ab’	2 个字节	‘ab’	3 个字节
‘abc’	3 个字节	‘abc’	4 个字节
‘abcd’	4 个字节	‘abc’	4 个字节

2.4、文本类型

用于存储文本类型的数据，本质也是字符串类型。例如：文章、评论、详情等等。

数据类型	储存范围
TINYTEXT	0~255 个字节
TEXT	0~65535 个字节
MEDIUMTEXT	0~16777215 个字节
LONGTEXT	0~4294967295 个字节

2.5、日期与时间类型

数据类型	说明	空间大小	格式
YEAR	年	1 个字节	YYYY
DATE	日期	4 个字节	YYYY-MM-DD
TIME	时间、持续时间	3 个字节	HH:MM:SS
DATETIME	日期与时间	8 个字节	YYYY-MM-DD HH:MM:SS
TIMESTAMP	时间戳（1970.1.1 到现在的毫秒数）	4 个字节	YYYY-MM-DD HH:MM:SS

2.6、二进制类型

用于存储二进制类型的数据。例如：图片、PDF 文档等等。

数据类型	存储范围
TINYBLOB	0~255 个字节
BLOB	0~65535 个字节
MEDIUMBLOB	0~16777215 个字节
LONGBLOB	0~4294967295 个字节

3、数据库、表、列操作

同义词

CHARACTER SET 与 CHARSET 同义。
DATABASES 与 SCHEMAS 同义。
DATABASE 与 SCHEMA 同义。

3.1、数据库操作

语法

字符集与校验规则：指定校验规则使用 COLLATE 而不是 COLLATION，并且 “=” 可以使用空格替换。
IF NOT EXISTS 与 IF EXISTS：先判断再操作，操作失败不会报错。IF NOT EXISTS 用于数据库不存在时再创建，IF EXISTS 用于数据库存在时再删除。
DEFAULT：用处不大可以省略不写。

SHOW DATABASES [WHERE ...];-- 查看数据库
SHOW CREATE DATABASE 数据库名称;-- 查看创建数据库的 SQL 语句
USE 数据库名称;-- 使用数据库、切换数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT] [CHARSET=... COLLATE=...];-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT] [CHARSET ... COLLATE ...];-- 创建数据库

DROP DATABASE [IF EXISTS] 数据库名称;-- 删除数据库

ALTER DATABASE 数据库名称 [DEFAULT] [CHARACTER SET=... COLLATE=...];-- 修改数据库的字符集与校验规则
ALTER DATABASE 数据库名称 [DEFAULT] [CHARACTER SET ... COLLATE ...];-- 修改数据库的字符集与校验规则

示例

SHOW DATABASES;-- 查看数据库
SHOW DATABASES WHERE `database`='mysql';-- 查看数据库
SHOW CREATE DATABASE `mysql_test`;-- 查看创建数据库的 SQL 语句
USE `mysql_test`;-- 使用数据库、切换数据库

CREATE DATABASE IF NOT EXISTS `mytest` DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE IF NOT EXISTS `mytest` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE IF NOT EXISTS `mytest` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE IF NOT EXISTS `mytest` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE `mytest` CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE `mytest` CHARSET utf8mb4 COLLATE utf8mb4_general_ci;-- 创建数据库
CREATE DATABASE `mytest`;-- 创建数据库

DROP DATABASE IF EXISTS `mytest`;-- 删除数据库
DROP DATABASE `mytest`;-- 删除数据库

ALTER DATABASE `mytest` DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 修改数据库
ALTER DATABASE `mytest` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;-- 修改数据库
ALTER DATABASE `mytest` DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 修改数据库
ALTER DATABASE `mytest` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;-- 修改数据库
ALTER DATABASE `mytest` CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;-- 修改数据库的字符集与校验规则
ALTER DATABASE `mytest` CHARSET utf8mb4 COLLATE utf8mb4_general_ci;-- 修改数据库的字符集与校验规则

3.2、表、列操作

语法

必选表属性：列名称、数据类型与长度。
可选表属性：
- AUTO_INCREMENT 主键自增：主键列必须为数值类型，添加数据时使用 NULL 即可。
- COMMENT 注释：添加列注释。
- UNSIGNED 无符号的：列必须数值类型，存储负值自动去符号。
- ZEROFILL 零填充：列必须数值类型，长度不够时用零补齐。
约束：
- PRIMARY KEY 主键约束。
- NOT NULL 非空约束。
- UNIQUE 唯一约束。
- DEFAULT 默认约束。
- FOREIGN KEY 外键约束。

-- 查看表
SHOW TABLES;-- 查看当前数据库中的所有表
SHOW CREATE TABLE 表名;-- 查看创建表的 SQL 语句
DESC 表名;-- 查看表结构

-- 创建表
CREATE TABLE [IF NOT EXISTS] 表名称 (
    主键名称 列属性 列约束等,
    列名称 列属性 列约束等,
    ...
    PRIMARY KEY(`主键名称`)
) [ENGINE ...] [DEFAULT] [CHARSET ... COLLATE ...];

-- 删除表
DROP TABLE [IF EXISTS] 表名称;

-- 截断表
TRUNCATE 表名;

-- 更改表
ALTER TABLE 表名称 RENAME AS 新表名称;-- 重命名表名称
ALTER TABLE 表名称 ADD 列名称 列属性 列约束等;-- 添加列
ALTER TABLE 表名称 DROP 列名称;-- 删除列
ALTER TABLE 表名称 MODIFY 列名称 列属性 列约束等;-- 修改列属性（MODIFY 只能修改列属性，不能修改列名称）
ALTER TABLE 表名称 CHANGE 列名称 新列名称 列属性 列约束等;-- 修改列名称、列属性（CHANGE 可以同时修改列名称、列属性）

示例

-- 查看表
SHOW TABLES;
SHOW TABLES WHERE `tables_in_mytest`='student';
SHOW CREATE TABLE `student`;
DESC `student`;

-- 创建表
CREATE TABLE IF NOT EXISTS `student` (
    `sid` INT AUTO_INCREMENT COMMENT '主键',
    `sname` VARCHAR(100) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `spwd` VARCHAR(100) NOT NULL DEFAULT '123456' COMMENT '密码',
    PRIMARY KEY(`sid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 删除表
DROP TABLE IF EXISTS `student`;

-- 截断表
TRUNCATE `student`;

-- 更改表
ALTER TABLE `student` RENAME AS `student2`;
ALTER TABLE `student` ADD `saddress` VARCHAR(100);
ALTER TABLE `student` DROP `saddress`;
ALTER TABLE `student` MODIFY `saddress` VARCHAR(200);
ALTER TABLE `student` CHANGE `saddress` `saddress1` VARCHAR(100);

3.3、约束

CONSTRAINT 约束

PRIMARY KEY 主键约束：包含非空约束、唯一约束。
NOT NULL 非空约束：该列数据不能为空。
UNIQUE 唯一约束：该列数据不能重复。
DEFAULT 默认约束：如果添加、修改时为 NULL，则该列数据使用默认值。通常与 NOT NULL 一起使用。
FOREIGN KEY 外键约束：该列数据依赖主表的主键。

CREATE TABLE IF NOT EXISTS `teacher` (
    `tid` INT AUTO_INCREMENT COMMENT '主键',
    `tname` VARCHAR(100) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    PRIMARY KEY(`tid`)
);
CREATE TABLE IF NOT EXISTS `student` (
    `sid` INT AUTO_INCREMENT COMMENT '主键',
    `sname` VARCHAR(100) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `tid` INT,
    PRIMARY KEY(`sid`),
    CONSTRAINT `FK_tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
);
ALTER TABLE `student` DROP CONSTRAINT `FK_tid`;-- 删除外键约束
ALTER TABLE `student` ADD CONSTRAINT `FK_tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`);-- 添加外键约束

3.4、插入

语法

INSERT INTO 表名 (列名, 列名 ...) [VALUE|VALUES] (值, 值 ...), (值, 值 ...) ...;-- 插入

示例

CREATE TABLE IF NOT EXISTS `student` (
    `sid` INT AUTO_INCREMENT COMMENT '主键',
    `sname` VARCHAR(100) COMMENT '姓名',
    PRIMARY KEY(`sid`)
);

-- 添加
INSERT INTO `student` (sid, sname) VALUE
(NULL, 'zs'),
(NULL, 'ls'),
(NULL, 'ww');
INSERT INTO `student` (sid, sname, spwd, scard) VALUES
(NULL, 'zs'),
(NULL, 'ls'),
(NULL, 'ww');

VALUE 与 VALUES 的区别

相同点：两者都可以同时插入多条数据。
不同点：插入多行时 VALUE 效率更高，插入单行时 VALUES 效率更高。

3.5、删除

语法

DELETE FROM 表名 [WHERE ...];-- 删除
TRUNCATE 表名;-- 截断

示例

DELETE FROM `student` WHERE `sname`='ww';

DELETE 与 TRUNCATE 的区别

相同点：两者都可以一次性删除表中的所有数据，并且都不会删除表结构。
不同点：DELETE 属于 DML 语言，可以使用 WHERE 关键字，并且数据可以回滚。TRUNCATE 属于 DDL 语言，不能使用 WHERE 关键字，并且数据不能回滚。最重要的：TRUNCATE 会重置自增列使计数器归零。

3.6、修改

语法

UPDATE 表名 SET 列名=值, 列名=值 ... [WHERE ...];-- 修改

示例

UPDATE `student` SET `sname`='lisi', `spwd`='111222' WHERE `sname`='ls';

4、查询

4.1、查询语法

SELECT-- 查询
[ALL|DISTINCT]-- 查询结果是否去重（ALL 为所有，DISTINCT 为去重复，默认为所有）
列名-- 列名（可使用：*、列名、列名 AS 别名、表名.列名、表名.列名 AS 别名。多个列名使用逗号隔开）
FROM 表名-- 表名（可使用：表名 AS 别名）
[INNER JOIN|LEFT JOIN|RIGHT JOIN 表名 ON 连接条件]-- 内连接、左连接、右连接（可使用：表名 AS 别名）
[WHERE ...]-- 查询条件
[GROUP BY ...]-- 按照列名分组
[HAVING ...]-- 分组条件
[ORDER BY ...]-- 按照列名排序
[ASC|DESC]-- 排序规则（ASC 为升序，DESC 为降序，默认为升序）
[LIMIT OFFSET, COUNT|LIMIT COUNT]-- 分页查询（OFFSET 为开始索引，COUNT 为分页大小，默认开始索引为 0）

准备

CREATE TABLE `employee` (
	`id` INT,
	`name` VARCHAR(100),
	PRIMARY KEY (`id`)
);
INSERT INTO `employee` VALUE
(1, '张三'),
(2, '张三'),
(3, '张三'),
(4, '张三'),
(5, '张三'),
(6, '张三');

4.2、SELECT 查询

查询列：*、列名。
查询具体的值：字符串、数字、NULL 等等。
查询函数或者表达式结果。
查询系统变量。

-- 查询列
SELECT * FROM `employee`;
SELECT `name` FROM `employee`;

-- 查询具体的值
SELECT '员工', `name` FROM `employee`;
SELECT 1, `name` FROM `employee`;
SELECT NULL, `name` FROM `employee`;

-- 查询函数或者表达式结果
SELECT VERSION();-- MySQL 版本
SELECT 100*5-1 AS `计算结果`;-- 表达式结果

-- 查询系统变量
SELECT @@auto_increment_increment;-- 步长
SELECT @@autocommit;-- 事务提交状态

4.3、ALL|DISTINCT 去重

查询结果是否去重（ALL 为所有，DISTINCT 为去重复，默认为所有）。

SELECT ALL `name` FROM `employee`;-- ALL 为所有
SELECT DISTINCT `name` FROM `employee`;-- DISTINCT 为去重复
SELECT `name` FROM `employee`;-- 默认为所有

4.4、WHERE 条件

比较：=、<、>、<=、>=、!=。
集合：IN (…)、NOT IN (…)。
区间：BETWEEN … AND…、NOT BETWEEN … AND…。
模糊：LIKE、NOT LIKE。_ 表示一个任意字符，% 表示多个任意字符。
NULL 值：IS NULL、IS NOT NULL。
逻辑运算：AND 与 &&、OR 与 ||、NOT 与 !。

-- 比较
SELECT * FROM `employee` WHERE `id`=3;
SELECT * FROM `employee` WHERE `id`<3;
SELECT * FROM `employee` WHERE `id`>3;
SELECT * FROM `employee` WHERE `id`<=3;
SELECT * FROM `employee` WHERE `id`>=3;
SELECT * FROM `employee` WHERE `id`!=3;

-- 集合
SELECT * FROM `employee` WHERE `id` IN (2, 4);
SELECT * FROM `employee` WHERE `id` NOT IN (2, 4);

-- 区间
SELECT * FROM `employee` WHERE `id` BETWEEN 2 AND 4;
SELECT * FROM `employee` WHERE `id` NOT BETWEEN 2 AND 4;

-- 模糊
SELECT * FROM `employee` WHERE `name` LIKE '张_';
SELECT * FROM `employee` WHERE `name` LIKE '张%';

-- NULL 值
SELECT * FROM `employee` WHERE `id` IS NULL;
SELECT * FROM `employee` WHERE `id` IS NOT NULL;

-- 逻辑且
SELECT * FROM `employee` WHERE `id`>=2 AND `id`<=4;
SELECT * FROM `employee` WHERE `id`>=2 && `id`<=4;
-- 逻辑或
SELECT * FROM `employee` WHERE `id`=2 OR `id`=4;
SELECT * FROM `employee` WHERE `id`=2 || `id`=4;
-- 逻辑非
SELECT * FROM `employee` WHERE NOT `id`=2;-- 正确
SELECT * FROM `employee` WHERE NOT (`id`=2);-- 正确
SELECT * FROM `employee` WHERE ! (`id`=2);-- 正确
SELECT * FROM `employee` WHERE ! `id`=2;-- 异常

注意：如果存在多个逻辑运算，尽量使用括号。如果没有括号，逻辑且（AND 与 &&）的优先级大于逻辑或（OR 与 ||），并且逻辑非使用 “!” 不加括号会产生异常。

4.5、LIMIT 分页

默认开始索引为 0。

SELECT * FROM `employee` LIMIT 0, 3;-- 第一页，每页三行
SELECT * FROM `employee` LIMIT 3, 3;-- 第二页，每页三行
SELECT * FROM `employee` LIMIT 3;-- 第一页，每页五行

4.6、GROUP BY 分组与聚合函数

按照哪列分组，查询结果就需要有哪列，通常配合聚合函数一起使用。

SELECT `id`, COUNT(*) FROM `employee` GROUP BY `id`;
SELECT `id`, COUNT(*) FROM `employee` GROUP BY `id` HAVING `id`>=2;

4.7、ORDER BY 排序

SELECT * FROM `employee` ORDER BY `id` ASC;-- ASC 为升序
SELECT * FROM `employee` ORDER BY `id` DESC;-- DESC 为降序
SELECT * FROM `employee` ORDER BY `id`;-- 默认为升序

4.8、UNION 联合结果

联合结果是否去重，ALL 为不去重，DISTINCT 为去重，默认为去重。

-- ALL 为不去重
SELECT `name` FROM `employee` WHERE `id`<=3
UNION ALL
SELECT `name` FROM `employee` WHERE `id`>=3;

-- DISTINCT 为去重
SELECT `name` FROM `employee` WHERE `id`<=3
UNION DISTINCT
SELECT `name` FROM `employee` WHERE `id`>=3;

-- 默认为去重
SELECT `name` FROM `employee` WHERE `id`<=3
UNION
SELECT `name` FROM `employee` WHERE `id`>=3;

4.9、JOIN ON 连表

准备

-- 老师表
CREATE TABLE `teacher` (
    `tid` INT AUTO_INCREMENT,
    `tname` VARCHAR(100),
    PRIMARY KEY(`tid`)
);
INSERT INTO `teacher` VALUES
(1, '老师1'),
(2, '老师2'),
(3, '老师3');

-- 学生表
CREATE TABLE `student` (
    `sid` INT AUTO_INCREMENT,
    `sname` VARCHAR(100),
    `tid` INT,
    PRIMARY KEY(`sid`)
);
INSERT INTO `student` VALUES
(1, '学生1', 1),
(2, '学生2', 1),
(3, '学生3', 2),
(4, '学生4', 2),
(5, '学生5', NULL),
(6, '学生6', NULL);

语法

内连接：WHERE、JOIN ON、INNER JOIN ON，相当于 A∩B。
左（外）连接：LEFT JOIN ON、LEFT OUTER JOIN ON，相当于 A∪B。
右（外）连接：RIGHT JOIN ON、RIGHT OUTER JOIN ON，相当于 B∪A。

-- 内连接
SELECT ... FROM A, B WHERE A.id=B.id;
SELECT ... FROM A JOIN B on A.id=B.id;
SELECT ... FROM A INNER JOIN B on A.id=B.id;

-- 左（外）连接
SELECT ... FROM A LEFT JOIN B ON A.id=B.id;
SELECT ... FROM A LEFT OUTER JOIN B ON A.id=B.id;

-- 右（外）连接
SELECT ... FROM A RIGHT JOIN B ON A.id=B.id;
SELECT ... FROM A RIGHT OUTER JOIN B ON A.id=B.id;

示例

A LEFT JOIN B 与 B RIGHT JOIN A 完全等价，都相当于 A∪B。
B LEFT JOIN A 与 A RIGHT JOIN B 完全等价，都相当于 B∪A。

-- 内连接
SELECT * FROM `student`, `teacher` WHERE `student`.`tid`=`teacher`.`tid`;
SELECT * FROM `student` JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;
SELECT * FROM `student` INNER JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;

-- 左（外）连接
SELECT * FROM `student` LEFT JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;
SELECT * FROM `student` LEFT OUTER JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;

-- 右（外）连接
SELECT * FROM `student` RIGHT JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;
SELECT * FROM `student` RIGHT OUTER JOIN `teacher` ON `student`.`tid`=`teacher`.`tid`;

4.10、子查询

示例

如果查询结果是一张表，可以将其作为查询对象。注意：需要为表取个别名。

SELECT COUNT(`stu`.`tid`) FROM (SELECT `sname`, `tid` FROM `student`) AS `stu`;

如果查询结果只有一列，则可以将其作为查询条件再次查询。

SELECT * FROM student WHERE `tid` IN (SELECT `tid` FROM `teacher`);

5、函数

5.1、聚合函数

示例

COUNT()：计数。
SUM()：求和。
AVG()：平均值。
MAX()：最大值。
MIN()：最小值。

-- 计数
SELECT COUNT(*) FROM `student`;
SELECT COUNT(1) FROM `student`;
SELECT COUNT(`sid`) FROM `student`;

-- 求和、平均值、最大值、最小值
SELECT SUM(`sid`) FROM `student`;
SELECT AVG(`sid`) FROM `student`;
SELECT MAX(`sid`) FROM `student`;
SELECT MIN(`sid`) FROM `student`;

count(*)、count(1)、count(列名) 的区别

查询结果的区别：count(*) 与 count(1) 都是统计总行数，包含 NULL 值。count(列名) 只统计该列，不包含 NULL 值。
查询速度的区别：
- 如果列名为主键，则 count(列名) 比 count(1) 快。
- 如果列名不为主键，则 count(1) 比 count(列名) 快。
- 如果表中有主键，则 count(主键) 最快。
- 如果表中只有一列，则 count(*) 最快。
- 如果表中有多个列并且没有主键，则 count(1) 比 count(*) 快。

5.2、数学函数

示例

ABS()：取绝对值。
CEILING()：向上取整。
FLOOR()：向下取整。
RAND()：取 0~1 之间的随机数。
SIGN()：取符号，负数为 -1，0 为 0，正数为 1。

SELECT ABS(-10);-- 10
SELECT CEILING(1.8);-- 2
SELECT FLOOR(1.8);-- 1
SELECT RAND();-- 0.13993784612038904
SELECT SIGN(-1.8), SIGN(0), SIGN(1.8);-- -1 0 1

5.3、字符串函数

示例

CHAR_LENGTH()：字符串长度。
CONCAT()：拼接字符串。
UPPER()：转换为大写。
LOWER()：转换为小写。

SELECT *, CHAR_LENGTH(`sname`) FROM `student`;
SELECT CONCAT(`sid`, '-', `sname`) FROM `student`;
SELECT * FROM `student` WHERE `sname` LIKE CONCAT('%', 1);
SELECT UPPER('Hello');
SELECT LOWER('Hello');

5.4、日期与时间函数

示例

CURRENT_DATE()、CURDATE()：日期。
CURRENT_TIME()、CURTIME()：时间。
NOW()、LOCALTIME()、SYSDATE()：日期与时间。
YEAR(NOW())、MONTH(NOW())、DAY(NOW())、HOUR(NOW())、MINUTE(NOW())、MINUTE(NOW())、SECOND(NOW())、DAYOFWEEK(NOW())-1：年、月、日、时、分、秒、星期。

SELECT CURRENT_DATE(), CURDATE();-- 2022-05-08
SELECT CURRENT_TIME(), CURTIME();-- 09:51:22
SELECT NOW(), LOCALTIME(), SYSDATE();-- 2022-05-08 09:51:22
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()), DAYOFWEEK(NOW())-1;-- 2022 5 8 9 53 21 1

5.5、系统函数

示例

USER()、SYSTEM_USER()：当前系统用户。
VERSION()：MySQL 版本。

SELECT USER(), SYSTEM_USER();-- root@localhost
SELECT VERSION();-- 8.0.26

5.6、MD5 加密函数

MD5（MD5 Message-Digest Algorithm，MD5 信息摘要算法），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（Hash Value），用于确保信息传输完整一致。MD5 由 MD4、MD3、MD2 改进而来，主要增强算法复杂度和不可逆性。MD5 算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域。

准备

USE `mytest`;
CREATE TABLE `user` (
  `username` VARCHAR(100),
  `password` VARCHAR(100)
);
INSERT INTO `user` VALUE
('张三', 'aaa'),
('李四', 'bbb'),
('王五', 'ccc');

示例

-- 添加
INSERT INTO `user` VALUES ('张三', MD5('aaa'));
-- 修改
UPDATE `user` SET `password`=MD5(`password`) WHERE `username`='张三';
-- 查询
SELECT * FROM `user` WHERE `username`='张三';

6、事务与索引

6.1、事务

事务的 ACID 原则

Atomicity 原子性：所有操作要么全部成功，要么全部失败。
Consistency 一致性：执行之前和执行之后保持数据一致性状态。例如：用户 AB 的钱总计 1000，无论 AB 之间如何转账，事务结束之后用户 AB 的钱总计还是 1000。
Isolation 隔离性：当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作干扰，多个并发事务之间相互隔离。数据库提供多种事务隔离级别，稍后会介绍到。
Durability 持久性：事务一旦提交，对数据库中数据的改变是永久性的，不能再进行回滚。

注意

MySQL 只有 InnoDB 存储引擎支持事务，其他存储引擎都不支持事务。

示例

一、关闭自动提交事务：SET autocommit=0。0 为关闭自动提交事务，1 为开启自动提交事务，默认为开启自动提交事务。
二、开启事务：START TRANSACTION。
三、执行增删改操作。
四、提交与回滚事务：COMMIT 与 ROLLBACK。
五、开启自动提交事务：SET autocommit=1。

-- 一、关闭自动提交事务
SET autocommit=0;
-- 二、开启事务
START TRANSACTION;
-- 三、执行增删改操作
UPDATE `student` SET `sgender`=2222 WHERE `sname`='lisi2';
-- 四、提交与回滚事务
COMMIT;-- 提交与回滚事务
ROLLBACK;-- 提交与回滚事务
-- 五、开启自动提交事务
SET autocommit=1;

6.2、索引

索引可以大大提高 MySQL 的查询速度，但是会降低 MySQL 的更新速度。为列添加索引，需要确保该列经常被作为查询条件使用。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。索引分为：

一、主键索引：PRIMARY KEY。
二、唯一索引：UNIQUE KEY，或者 UNIQUE INDEX。
三、普通索引：KEY，或者 INDEX。
四、全文索引：FULLTEXT。
五、空间索引：SPATIAL。
六、联合索引：
- 联合主键索引：PRIMARY KEY(列名, 列名 …)
- 联合唯一索引：UNIQUE KEY(列名, 列名 …)，或者 UNIQUE INDEX(列名, 列名 …)。
- 联合普通索引：KEY(列名, 列名 …)，或者 INDEX(列名, 列名 …)。

创建表时创建索引

USE `mytest`;
DROP TABLE IF EXISTS `student_index_test`;
-- 创建表时创建索引
CREATE TABLE `student_index_test` (
	`sid` INT,
	`sname` VARCHAR(100),
	`spwd` VARCHAR(100),
	`saddress` VARCHAR(100),
	`scard` VARCHAR(100),
	PRIMARY KEY(`sid`),-- 主键索引
	UNIQUE KEY(`sname`),-- 唯一索引
	UNIQUE INDEX(`spwd`),-- 唯一索引
	KEY(`saddress`),-- 普通索引
	INDEX(`scard`)-- 普通索引
);
-- 查看索引
SHOW INDEX FROM `student_index_test`;

ALTER 添加与删除索引

USE `mytest`;
DROP TABLE IF EXISTS `student_index_test02`;
-- 创建表
CREATE TABLE `student_index_test02` (
	`sid` INT,
	`sname` VARCHAR(100),
	`spwd` VARCHAR(100),
	`saddress` VARCHAR(100),
	`scard` VARCHAR(100)
);
-- 查看索引
SHOW INDEX FROM `student_index_test02`;
-- 添加索引
ALTER TABLE `student_index_test02` ADD PRIMARY KEY `sid`(`sid`);
ALTER TABLE `student_index_test02` ADD UNIQUE KEY `sname`(`sname`);
ALTER TABLE `student_index_test02` ADD UNIQUE INDEX `spwd`(`spwd`);
ALTER TABLE `student_index_test02` ADD KEY `saddress`(`saddress`);
ALTER TABLE `student_index_test02` ADD INDEX `scard`(`scard`);
-- 删除索引
ALTER TABLE `student_index_test02` DROP PRIMARY KEY;
ALTER TABLE `student_index_test02` DROP KEY `sname`;
ALTER TABLE `student_index_test02` DROP INDEX `spwd`;
ALTER TABLE `student_index_test02` DROP KEY `saddress`;
ALTER TABLE `student_index_test02` DROP INDEX `scard`;

CREATE 与 DROP 创建与删除索引

-- 创建普通索引
CREATE INDEX `saddress` ON `student_index_test02`(`sname`);
CREATE INDEX `scard` ON `student_index_test02`(`spwd`);
-- 删除唯一索引
DROP INDEX `sname` ON `student_index_test02`;
DROP INDEX `spwd` ON `student_index_test02`;
-- 删除普通索引
DROP INDEX `saddress` ON `student_index_test02`;
DROP INDEX `scard` ON `student_index_test02`;

7、用户与权限

7.1、查看用户

查看所有用户信息

-- 查询所有用户信息
SELECT * FROM `mysql`.`user`;
SELECT `host`, `user`, `plugin`, `authentication_string` FROM `mysql`.`user`;

host（主机）	user（用户名）	plugin（身份验证插件）	authentication_string（身份验证字符串）
%	root	mysql_native_password	*44612AC693E3B8F7AEA36B50168860122FE106A8
localhost	mysql.infoschema	caching_sha2_password	$A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost	mysql.session	caching_sha2_password	$A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost	mysql.sys	caching_sha2_password	$A $005$ THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
localhost	root	mysql_native_password	*44612AC693E3B8F7AEA36B50168860122FE106A8

查看当前用户

-- 查询当前用户
SELECT USER();-- root@localhost

用户命名规则：user@host。user 为用户名，host 为主机（IP 为具体主机，% 为其他任意主机，localhost 与 127.0.0.1 为本机）。例如：

root@localhost 表示本机上的 root 用户。
root@% 表示其他任意主机上的 root 用户。
zhangsan@192.168.100.111 表示具体主机上的具体用户。

身份验证插件

mysql_native_password（MySQL8 以前版本默认）：基于本机密码哈希方法实现身份验证。
caching_sha2_password（MySQL8 及其之后版本默认）：实现基本的 SHA-256 身份验证。并且在服务器端使用缓存以获得更好的性能，具有其他功能以提高适用性。
sha256_password：实现基本的 SHA-256 身份验证。

关于身份验证兼容性问题

存在问题：Navicat 连接 Mysql 8.0.26 出现异常 1251- Client does not support authentication protocol（客户端不支持用户协议）。

解决办法：还原身份验证插件为 mysql_native_password 即可。

-- 修改身份验证插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'password';
-- 刷新权限
FLUSH PRIVILEGES;

7.2、增删改用户

语法

-- 创建用户
CREATE USER 用户名@主机 IDENTIFIED [WITH 身份验证插件] BY 密码;
-- 修改身份验证插件与密码
ALTER USER 用户名@主机 IDENTIFIED [WITH 身份验证插件] BY 新密码;
-- 删除用户
DROP USER 用户名@主机;

示例

-- 创建、修改、删除用户，同时身份验证插件与密码
CREATE USER 'zhangsan'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
ALTER USER 'zhangsan'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
DROP USER 'zhangsan'@'%';
FLUSH PRIVILEGES;

-- 创建、修改、删除用户，使用默认加密规则
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';
ALTER USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';
DROP USER 'zhangsan'@'localhost';
FLUSH PRIVILEGES;

-- 仅仅修改身份验证插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'password';
FLUSH PRIVILEGES;

其他修改密码的方式

新版本

-- 修改密码
SET PASSWORD FOR 'username'@'host'='newpassword';
-- 修改当前用户密码
SET PASSWORD='newpassword';

旧版本

-- 修改密码
SET PASSWORD FOR 'username'@'host'=PASSWORD('newpassword');
-- 修改当前用户密码
SET PASSWORD=PASSWORD('newpassword');

忘记 root 密码

使用 DOS 输入 mysqld –skip-grant-tables 跳过权限表认证，然后重新打开一个窗口，修改 root 密码即可。

7.3、用户权限

查看用户权限

-- 查看当前用户的权限
SHOW GRANTS;
-- 查看某个具体用户的权限
SHOW GRANTS FOR 用户@主机;

授予与撤销权限

ALL 表示所有权限。
WITH GRANT OPTION 允许授予其他用户权限。
新建用户，默认只有 SELECT 权限。

-- 授予权限，不包含 Grant 权限
GRANT 权限 ON 数据库名.表名 TO 用户@主机;
-- 授予权限，包含 Grant 权限
GRANT 权限 ON 数据库名.表名 TO 用户@主机 WITH GRANT OPTION;
-- 撤销权限，包含 Grant 权限
REVOKE 权限 ON 数据库名.表名 FROM 用户@主机;

示例

-- 授予用户所有数据库中所有表的所有权限，不包含 Grant 权限
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-- 授予用户所有数据库中所有表的所有权限，包含 Grant 权限
GRANT ALL ON *.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;
-- 撤销用户所有数据库中所有表的所有权限，包含 Grant 权限
REVOKE ALL ON *.* FROM 'zhangsan'@'localhost';

8、数据库设计规范

8.1、三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。

第一范式（1NF）：确保每列的原子性，列不可再分。
第二范式（2NF）：确保每列都和主键相关。
第三范式（3NF）：确保每列都和主键直接相关，而不是间接相关。

8.2、数据库设计规范

考虑到成本、性能、用户体验等，可以适当放宽数据库设计规范。例如：

通常要求关联查询的表不得超过三张表。
故意给某些表增加一些冗余字段（从多表查询变为单表查询）。
故意增加一些计算列（从大数据量的查询变为小数据量的查询）。

9、总结

9.1、重点内容

存储引擎
字符集与校验规则
约束
函数
索引
事务

9.2、常见问题

Web 开发中如何存储图片。

有两种方式：一是将图片转换为二进制数据，然后存储到数据库中。二是将图片存储到服务器上，然后将图片地址存储到数据库中。

一个 FisherMan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Web 01 - MySQL

数据库分类关系型数据库：SQL 数据库（Structured Query Language，结构化查询语言），通常用于数据持久化。使用数据表维护数据，数据通常存储在硬盘上，虽然数据可以永久保存但是存取速度较慢。常见关系型数据库有：MySQL、Oracle、SQL Server、DB2、SQLlite 等等。非关系型数据库：NoSQL 数据库（Not Only SQL，不仅仅是 SQL），作为关系型数据库的补充，通常用于缓存。使用键值对维护数据，数据通常存储在内存中，虽然存取速度较快但是数据断电即失。
复制链接

扫一扫

专栏目录