二、MySQL 基础

2.1 MySQL 简介

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

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

2.1.1 MySQL 发展历史

为什么没有 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 结构化查询语言

2.6 MySQL 数据类型

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

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

2.6.1 数值类型

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

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

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

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

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

2.6.2 日期类型

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

2.6.3 字符串

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 字段的约束及属性

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 存储引擎比较

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的帮助信息