二、MySQL 基础

二、MySQL 基础

2.1 MySQL 简介

MySQL 是一款流行的开源数据库，也是一个关系型数据库管理系统

在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一

2.1.1 MySQL 发展历史

时间	里程碑
1996 年	MySQL 1.0 发布。它的历史可以追溯到 1979 年，作者 Monty 用 BASIC 设计的一个报表工具
1996 年	10 月 3.11.1 发布。MySQL 没有 2.x 版本
2000 年	ISAM 升级成 MyISAM 引擎。MySQL 开源
2003 年	MySQL 4.0 发布，集成 InnoDB 存储引擎
2005 年	MySQL 5.0 版本发布，提供了视图、存储过程等功能
2008 年	MySQL AB 公司被 Sun 公司收购，进入 Sun MySQL 时代
2009 年	Oracle 收购 Sun 公司，进入 Oracle MySQL 时代
2010 年	MySQL 5.5 发布，InnoDB 成为默认的存储引擎
2016 年	MySQL 发布 8.0.0 版本

为什么没有 MySQL6、MySQL7？

MySQL5.6 可以当成 MySQL6.x

MySQL5.7 可以当成 MySQL7.x

2.1.2 MySQL 的优势

• 运行速度快

• 使用成本低

  • 价格：MySQL对多数个人来说是免费的

• 容易使用

  • 与其他大型数据库的设置和管理相比，其复杂程度较低，容易学习

• 可移植性强

  • 能够工作在众多不同的系统平台上，例如：Windows 、Linux、Unix、MacOS等

• 适用用户广

• 丰富的接口

  • 提供了用于C 、C++、Eiffel、Java、Perl、PHP、Python、Rudy和TCL 等语言的API

• 支持查询语言

  • MySQL可以利用标准SQL语法和支持ODBC（开放式数据库连接）的应用程序

• 安全性和连接性

  • 安全性和连接性; 十分灵活和安全的权限和密码系统，允许主机验证。连接到服务器时，所有的密码均采用加密形式，从而保证了密码安全

  • 并且由于MySQL是网络化的，因此可以在因特网网上的任何地方访问，提高数据共享效率

2.1.3 MySQL 安装教程

官网 https://downloads.mysql.com/archives/community/

CSDN安装教程 https://blog.csdn.net/qq_62283694/article/details/129869634

安装教程见第一章、课程软件的安装与卸载

---

2.2 命令行连接 MySQL

1. 首先检查 MySQL 服务是否启动

2. 电脑按下Win + R 输入 cmd打开dos窗口

2.2.1 启动服务命令

net start mysql

2.2.2 停止服务命令

net stop mysql

2.2.3 登录数据库命令

mysql –h服务器主机地址 –u用户名 –p密码

参数说明：

-h 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略

-u 登录的用户名

-p 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OoGExnrM-1680502495283)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152901.png)]

2.2.4 查看当前版本和用户信息

SELECT VERSION(),USER();

分号为结束符，不可缺

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OdjHY8aN-1680502495285)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205152928.png)]

2.3 数据库管理指令

2.3.1 创建数据库

CREATE DATABASE 数据库名;

Query OK：SQL语句执行成功

1 row affected：操作影响的行数

(0.00 sec)：操作执行时间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U8eL8sOQ-1680502495285)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153046.png)]

2.3.2 查看数据库列表

SHOW databases;

student：用户数据库

其他的是系统数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-63nmnfl6-1680502495285)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153258.png)]

2.3.3 选择数据库

USE 数据库名;

使用数据表前，必须先选择该数据库！

ERROR 1049 (42000): Unknown database 'a'

数据库不存在，报错

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nDeRTiIb-1680502495286)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205153925.png)]

2.3.4 删除数据库

DROP DATABASE 数据库名;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jhVeMGyb-1680502495286)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154227.png)]

---

2.4 Navicat 数据库管理工具

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Wh4c8l6-1680502495286)(./assets/image-20230205154315466-1680219755644-4.png)]

Navicat Premium是一款功能强大的、可支持多连接的数据库管理工具

允许在单一程序中同时连接多达7种数据库

1. MySQL

2. MariaDB

3. MongoDB

4. SQL Server

5. SQLite

6. Oracle

7. PostgreSQL

2.4.1 安装教程

官网 https://www.navicat.com/en/products

推荐使用：Navicat Premium 16 最新版

Navicate 涉及版权无法分享，加封面联系方式获取软件

2.4.2 Navicat 连接 MySQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-baWv1654-1680502495287)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205154815-1680247296828-4.png)]

2.4.3 使用 Navicat 创建数据库

1. 通过操作向导创建数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FVwLR3Ps-1680502495287)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160159-1680247296828-6.png)]

• 字符集要选择utf8点击 SQL 预览可以查看建表语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mbSnsGaj-1680502495287)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230205160210-1680247296828-7.png)]

2. 通过SQL语句创建数据库

---

2.5 结构化查询语言

名称	解释	命令举例
DML (数据操作语言)	操作数据库中所包含的数据	INSERT UPDATE DELETE
DDL (数据定义语言)	创建和删除数据库对象等操作	CREATE DROP ALTER
DQL (数据查询语言)	对数据库中的数据进行查询	SELECT
DCL (数据控制语言)	控制数据库组件的存取许可、存取权限等	GRANT COMMIT ROLLBACK

---

2.6 MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型

2.6.1 数值类型

类型	说明	取值范围	存储需求
TINYINT	非常小的数据	有符值：-27~27-1 无符号值：0~28-1	1字节
SMALLINT	较小的数据	有符值：-215~215-1 无符号值：0~216-1	2字节
MEDIUMINT	中等大小的数据	有符值：-223~223-1 无符号值：0~224-1	3字节
INT	标准整数	有符值：-231~231-1 无符号值：0~232-1	4字节
BIGINT	较大的整数	有符值：-263~263-1 无符号值：0~264-1	8字节
FLOAT	单精度浮点数	±1.1754351e-38	4字节
DOUBLE	双精度浮点数	±2.2250738585072014e-308	8字节
DECIMAL	字符串形式的浮点数	Decimal（M，D）	M+2个字节

**UNSIGNED 属性：**标识为无符号数，非负数

**ZEROFILL 属性：**宽度（位数）不足以0填充

若某数值字段指定了 ZEROFILL 属性，将自动添加 UNSIGNED 属性

数值位数小于宽度，以0填充

数值位数大于宽度，显示实际位数

2.6.2 日期类型

日期类型	格式	取值范围	用途
DATE	YYYY-MM-DD	1000-01-01 / 9999-12-31	日期值
TIME	HH:MM:SS	-835:59:59 / 838:59:59	时间值或持续时间
YEAR	YYYY	1901~2155	年份值
DATETIME	YYYY-MM-DD hh:mm:ss	1000-01-01 00：00：00 到 9999-12-31 23：59：59	混合日期和时间值
TIMESTAMP	YYYY-MM-DD hh:mm:ss	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	混合日期和时间值，时间戳

若某日期字段默认值为系统当前日期，可指定为TIMESTAMP类型

2.6.3 字符串

字符串类型	大小	用途
CHAR[(M)]	0-255 bytes	定长字符串
VARCHAR[(M)]	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char(m) 和 varchar(m) 中括号中 m 代表字符的个数，并不代表字节个数

比如 CHAR(30) 就可以存储 30 个字符

• CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换

• BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值

• BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同

• 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择

---

2.7 DDL (数据定义语言)

2.7.1 创建表

CREATE TABLE table_name (column_name column_type);

CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ],字段 2 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ],字段 n 数据类型 [ 字段属性 | 约束 ][ 索引 ][ 字段备注 ]
)[ 表类型 ][ 表字符集 ][ 表备注 ];

IF NOT EXISTS 判断表是否存在，存在就不重复建表了

多字段使用逗号分隔，保留字用撇号括起来

演示案例

CREATE TABLE IF NOT EXISTS xuanzi(id INT UNSIGNED AUTO_INCREMENT,name VARCHAR(100) NOT NULL,phone VARCHAR(40) NOT NULL,date DATE,PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 如果你不想字段为NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错
• AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1
• PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
• ENGINE 设置存储引擎，CHARSET 设置编码

2.7.2 修改表

• 修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;
# 修改表名称

ALTER TABLE XuanZi RENAME TO patient;

• 添加字段

ALTER TABLE 表名 ADD 字段名  数据类型  [属性];
# 添加字段

ALTER TABLE patient add XuanZi VARCHAR(20) NOT NULL;

• 修改字段

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];
#修改字段

ALTER TABLE patient CHANGE XuanZiemail email  VARCHAR(20);

• 删除表中的字段

ALTER TABLE 表名 DROP 字段名;
# 删除字段

ALTER TABLE patient DROP XuanZi;

• 添加主键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 PRIMARY KEY 表名（主键字段）;
# 添加主键约束     添加约束       约束名称  约束类型     表（字段）

ALTER TABLE patient ADD CONSTRAINT pk_patientID PRIMARY KEY patient(patientID);

• 添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）;
# 添加外键约束      添加约束      约束名称  约束类型 （添加外键的字段） 关联     外键（字段）

ALTER TABLE prescription ADD CONSTRAINT fk_patientID_patient FOREIGN KEY (patientID) REFERENCES patient(patientID);

MySQL中，MyISAM存储类型的表不支持外键。因此，可以通过建立逻辑关联的方式保证数据的完整性和一致性

2.7.3 删除表

DROP TABLE [IF  EXISTS] 表名;

在删除表之前，先使用IF EXISTS语句验证表是否存在，执行 DROP TABLE 的表是空表，则可以直接删除

如果表中已存储了业务数据，则需和数据库管理员联系，先对数据库数据进行备份并确认，再执行删除表及数据的操作

---

2.8 MySQL 注释

#  单行注释
-- 单行注释/** 多行注释
*/

---

2.9 MySQL 字段的约束及属性

名称	关键字	说明
主键约束	PRIMARY KEY(PK)	设置该字段为表的主键 可唯一标识该表记录
外键约束	FOREIGN KEY(FK)	用于在两表之间建立关系， 需要指定引用主表的哪一字段
自动增长	AUTO_INCREMENT	设置该列为自增字段 默认每条自增1 通常用于设置主键
非空约束	NOT NULL	字段不允许为空
默认约束	DEFAULT	赋予某字段默认值
唯一约束	UNIQUE KEY(UK)	设置字段的值是唯一的 允许为空，但只能有一个空值

2.9.1 主键约束

主键：使用数据库表中某一字段或某几个字段唯一标识所有记录

• 单字段主键

CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 PRIMARY KEY,#……
);
-- 定义字段的同时指定主键

CREATE TABLE [IF NOT EXISTS] 表名 (字段 1 数据类型 ,#……[CONSTRAINT< 约束名 >] PRIMARY KEY[ 列名 ]
);
-- 定义所有字段之后指定主键

• 多字段联合主键

CREATE TABLE [IF NOT EXISTS] 表名 (#……PRIMARY KEY [字段1,字段2,…… ]
);

2.9.2 外键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）;
# 添加外键约束      添加约束      约束名称  约束类型 （添加外键的字段） 关联     外键（字段）

其他约束的设置方式同理

2.9.3 主键与外键的区别

	定义	作用	个数
主键	唯一标识一条记录，不能出现重复且不能出现空值	保证数据完整性	主键只能有一个
外键	“从表”中对应于“主表”的列，在从表中称为外键或者引用键“主表”中的列应为主键或者唯一键	和其他表建立关联，保持数据的一致性	一个表可以有多个外键

2.9.4 主外键建立后注意事项

1. 当主表中没有对应的记录时，不能将记录添加到子表
2. 不能更改主表中的值而导致子表中的记录孤立
3. 子表存在与主表对应的记录，不能从主表中删除该行
4. 删除主表前，先删子表

---

2.10 MySQL 注释与字符集编码

2.10.1 注释

• COMMENT关键字

• 在表结构中可以看到，与代码注释不同、

CREATE TABLE test (id int(11) UNSIGNED COMMENT '编号'
)COMMENT='测试表';

2.10.2 设置字符集编码

• 默认情况下，MySQL所有数据库、表、字段等使用 MySQL 默认字符集

• 为了存储独特的数据，可在创建表时指定字符集

CREATE TABLE [IF NOT EXISTS] 表名（#……
）CHARSET = 字符集名;

2.10.3 演示案例

CREATE TABLE `checkitem`  (`checkItemID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',`checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称',`checkItemCost` float NOT NULL COMMENT '检查项目价格',PRIMARY KEY (`checkItemID`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8  COMMENT = '检查项目表';

---

2.11 MySQL 查看表

2.11.1 查看数据库中的表

SHOW TABLES;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jadfnpp4-1680502495287)(./assets/image-20230331173945642.png)]

2.11.2 描述表结构

DESCRIBE 表名;
DESC 表名;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K3MLOBK1-1680502495288)(./assets/image-20230331173500098.png)]

为了避免DOS窗口出现中文乱码，可执行SET NAMES gbk;

---

2.12 MySQL 的存储引擎

存储引擎，是存储数据的核心组件，指定了表的存储类型和存储形式

2.12.1 存储引擎的类型

• InnoDB
• MyISAM
• Memory
• CSV
• 等9种

2.12.2 查看系统所支持的存储引擎类型

SHOW ENGINES;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2fCfB4BK-1680502495288)(./assets/image-20230331232103064.png)]

2.12.3 常用的 MySQL 存储引擎

InnoDB 存储引擎

• 支持具有提交、回滚和崩溃恢复能力的事务控制

MyISAM 存储引擎

• 不支持事务，也不支持外键约束，访问速度比较快

2.12.4 InnoDB 和 MyISAM 存储引擎比较

功能	InnoDB	MyISAM
事务处理	支持	不支持
外键约束	支持	不支持
表空间大小	较大	较小
数据行锁定	支持	不支持

2.12.5 适用场合

• 使用 MyISAM：不需事务，空间小，以查询访问为主
• 使用InnoDB：多删除、更新操作，安全性高，事务处理及并发控制

2.12.6 查看当前存储引擎

MySQL 5.7 版本默认的存储引擎是InnoDB

• 查看当前默认存储引擎

SHOW VARIABLES LIKE 'default_storage_engine%';
-- default_storage_engine% 默认存储引擎

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eUFhiptZ-1680502495288)(./assets/image-20230331232525679.png)]

2.12.7 修改存储引擎

• 修改my.ini配置文件

default-storage-engine=MyISAM
-- MyISAM 改为其他类型的存储引擎

修改配置文件后，需要重启MySQL服务设置才会生效

2.12.8 设置表的存储引擎

数据表默认使用当前 MySQL 默认的存储引擎

创建表时，可以设置表的存储引擎类型

CREATE TABLE 表名 (
# ……
) ENGINE= 存储引擎 ;

演示案例

CREATE TABLE `checkitem`  (`checkItemID` int(4) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',`checkItemName` varchar(50) NOT NULL COMMENT '检查项目名称',`checkItemCost` float NOT NULL COMMENT '检查项目价格',PRIMARY KEY (`checkItemID`) USING BTREE
) ENGINE = MyISAM;

2.12.9 数据表的存储文件

MyISAM 类型表文件

• *.frm表结构定义文件

• *.MYD数据文件

• *.MYI索引文件

InnoDB 类型表文件

• *.frm表结构定义文件

• ibdata1文件

2.12.10 存储位置

因操作系统而异，可查my.ini

datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data"
innodb_data_home_dir="D:/MySQL Datafiles/"

---

2.13 MySQL 系统帮助

 HELP 查询内容;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0cETO9Ll-1680502495289)(./assets/image-20230331233452271.png)]

HELP contents;
#查看帮助文档目录列表
HELP Data Definition;
#查看数据定义
HELP CREATE DATABASE;
#查看CREATE DATABASE的帮助信息

---