当前位置：首页 > article >正文

初阶mysql修炼手册

article 2025/1/31 8:27:27

0.MySQL 在 Centos 7环境安装

0.1 卸载不要的环境

ps ajx |grep mariadb # 先检查是否有 mariadb 存在
systemctl stop mariadb.service # 停⽌ mariadb 服务
ps ajx |grep mariadb # 再检查是否有 mariadb 存在

0.2 删除多余的安装包

rpm -qa | grep mysql #查看默认安装包
rpm -qa | grep mysql | xargs yum -y remove #删除所有默认安装包

0.3 获取mysql官⽅yum源

Index of /232905

cat /etc/redhat-release #查看版本
找到一个和自己的系统一致的并安装

0.4 安装mysql yum 源，对⽐前后yum源

rpm -Uvh 安装包 #安装mysql yum源

0.5 查看是否能正常工作

yum list | grep mysql #查看所有的mysql文件

0.6 安装mysql服务

yum install -y mysql-community-serve #安装mysql服务

Failing package is: mysql-community-client-5.7.39-1.el7.x86_64
GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 #解决秘钥过期问题

0.7 数据存储位置查看配置⽂件

ls /var/lib/mysql#查看数据存储位置
ls /etc/my.cnf #查看配置文件

0.8 启动服务

systemctl start mysqld.service #启动服务
service mysqld restart #启动服务

0.9 配置

vim /etc/my.cnf #打开配置文件
skip-grant-tables 选项 , 并保存退出
systemctl restart mysqld #重启mysqld服务

1. 数据库基础

数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合

虽然单纯的使用文件也可以存储数据，但会存在如下缺点：

安全性问题：数据误操作后无法进行回滚。
不利于数据的查询和管理：没有将存储的数据以某种数据结构组织起来。
控制不方便：数据的控制需要用户自己来完成。
不利于存储海量数据：数据量越大用户操控数据的成本越高。

1.1 数据库的存储介质

磁盘，比如MySQL就是一种磁盘数据库
内存，比如redis就是一种内存数据库

1.2 常见主流数据库

sql server, mysql, oracle,postgersql,sqlite,h2

MySQL：甲骨文产品，世界上最受欢迎的数据库，并发性好，但不适合做复杂的业务。主要用在电商、SNS、论坛，对简单的SQL处理效果好

1.3 基本使用

1.3.1 连接服务器

本地连接时：mysql -u用户 -p 或者直接mysql

远程连接时：mysql -h地址 -P端口号 -u用户 -p

退出数据库，直接使用quit

1.3.2 服务器管理

停止服务器: systemctl stop mysqld 或者 service mysqld stop

启动服务器: systemctl start mysqld 或者 service mysqld start

重启启动服务器: systemctl restart mysqld 或者 service mysqld restart

1.3.3 数据库服务器&&数据库&&表关系

1.4 数据库重要路径

MySQL配置文件的绝对路径为 /etc/my.cnf
配置文件中datadir对应的值为 /var/lib/mysql

注：创建数据库，本质就是在MySQL的数据存储路径下新建了一个目录，而当我们将这个数据库删除后，这个目录其实也就不存在

数据库的默认字符编码和字符校验规则

student.frm是表结构文件，student.ibd是表数据和索引的文件

1.5 数据逻辑存储

表中的数据是以二维表格的形式进行呈现的，包括行和列。如下：

其中每一行我们称之为是一条记录，而每一列都代表一个属性（属性列）。

1.6 mysql命令本质

mysql命令本质是一个可执行程序，通过file命令可以看到该可执行程序是采用动态链接的方式生成的，通过ldd命令可以看到该可执行程序依赖的C/C++库文件。如下：

也就是说，mysql命令本身就是C/C++编写的，因此在编写mysql程序时，一定需要调用MySQL提供给C/C++的语言接口客户端。当然，MySQL不仅仅提供了C/C++对应的语言接口，像Python、Java、PHP等都有对应的MySQL接口

1.7 SQL分类

SQL（Structured Query Language，结构化查询语言）是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

DDL（Data Definition Language）数据定义语言，用来维护存储数据的结构。比如create语句、drop语句、alter语句等。
DML（Data Manipulation Language）数据操作语言，用来对数据进行操作。比如insert语句、delete语句、update语句等。
DCL（Data Control Language）数据控制语言，主要负责权限管理和事务。比如grant语句、commit语句。

说明一下： DML中又单独分了一个DQL（Data Query Language）数据查询语言，比如select语句、from语句、where语句等。

1.8 存储引擎

存储引擎就是数据库管理系统如何存储数据、如何为存储的数据建立索引、如何更新数据、如何查询数据等技术的实现方法，MySQL中的存储引擎是插件式的存储引擎，它可以支持多种存储引擎。

其中MySQL底层默认使用的存储引擎是InnoDB，该存储引擎支持事务、行级锁、外键等

注： InnoDB存储引擎是支持事务的，而MyISAM存储引擎是不支持的

2. 库操作

2.1 创建数据库

默认的编码格式和校验规则创建数据库

指定utf8编码格式和utf8_general_ci校验规则创建数据库

2.2 字符集和校验规则

查看系统默认的字符集

show variables like 'character_set_database'

查看系统默认的字符集校验规则

show variables like 'collation_database'

查看数据库支持的字符集

使用show charset可以查看数据库支持的字符集。如下：

说明一下： 字符集主要是控制用什么语言，比如utf8就可以使用中文。

查看数据库支持的字符集校验规则

使用show collation可以查看数据库支持的字符集校验规则。如下：

2.2.1 校验规则对数据库的影响

数据库的编码和效验规则,本质会影响对应的数据库内部的表,比如查数据,比较数据

2.3 查看数据库

使用show database可以查看系统中所有的数据库。如下：

2.4 显示创建语句

使用show create database 数据库名可以查看对应数据库的创建语句。如下

2.5 修改数据库

使用alter database 数据库名 charset=xxxx collate=xxxx可以修改数据库的语句。如下

2.6 删除数据库

使用drop database 数据库名可以删除数据库语句。如下

2.7 数据库备份

mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径

为了演示数据库备份，下面我们创建一个数据库，并在该数据库中创建两个表。如下：

在student表中插入两条记录。如下：

这时在命令行中执行如下命令即可将该数据库进行备份，并指定将备份后产生的文件存放在当前目录下。如下：

打开back.sql文件即可看到，文件中的内容实际就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句。如下：

2.8 数据库恢复

source 数据库备份存储的文件路径
为了演示数据库恢复，我们先将刚才创建的数据库删除。如下：

这时让MySQL服务器执行如下命令即可对数据库进行恢复。如下

实际恢复数据库的时候就是按顺序执行数据库备份文件中的SQL语句，执行完毕后数据库也就恢复出来了。如下：

同时该数据库下的两张表，以及表当中的数据也都恢复出来了。如下：

2.9 数据库指定表的备份

mysqldump -P 端口号 -u 用户名 -p 密码数据库名 表名1 表名2 ... > 表备份存储的文件路径

比如在上述数据库中除了student和teacher表之外，还有其他的表。如下：

如果只想备份数据库中的student表和teacher表，这时就可以在命令行中执行如下命令，并指定将备份后产生的文件存放在当前目录下。如下：

这时历史上与student和teacher表相关的SQL语句，就会被保存到备份文件当中。如下：

2.10 数据库指定表的恢复

表恢复之前需要先选中一个数据库，表明需要将表恢复到哪一个数据库中，为了防止恢复出来的表与该数据库中已有的表的表名重复，一般在恢复表时会选择创建一个空的数据库，然后在该数据库中进行表的恢复。

在数据库中使用如下命令即可对指定表进行恢复：source 表备份存储的文件路径

为了演示表恢复，我们先将刚才的数据库删除。如下：

这时创建一个空的数据库并在该数据库中执行如下命令即可对表进行恢复。如下：

当备份文件中的SQL语句执行完毕后，该数据库下就恢复出了student和teacher表，并且表当中的数据也都恢复出来了。如下：

2.11 查看mysql连接情况

使用show processlist即可查看当前连接MySQL的用户。比如：

说明一下：

Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL
Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
Time列：表示该线程处于当前状态的时间，单位是秒。
State列：显示使用当前连接的SQL语句的状态。
info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。

3.表操作

3.1 查看引擎

使用命令：show engines;

3.2 创建表

数据库创建完毕后选中数据库，在该数据库中创建一个user表，并在建表时指定采用MyISAM存储引擎。如下：

表创建完毕后在数据库的数据存储路径下的table_operation目录中，就会对应增加三个文件。如下：

说明一下：

采用不同的存储引擎，创建表时所产生的文件不一样。
采用InnoDB存储引擎建表，会产生对应的xxx.frm（表结构）和xxx.ibd（表数据+表索引）文件。
采用MyISAM存储引擎建表，会产生对应的xxx.frm（表结构）、xxx.MYD（表数据）和xxx.MYI（表索引）文件

3.3 查看表结构

使用desc 表名SQL可以查看表的结构。如下：

3.4 查看创建表信息

如果想要查看创建表时的相关细节，可以使用show create table 表名。如下：

3.5 修改表的内容

语法	操作
alter table 表名 add 新列名新列名的属性	新增表的列
alter table 表名 modify 列名修改列名的属性	修改表的列
alter table 表名 drop 列名删除列名	删除表的列
alter table 表名 rename 表名	更改表名
alter table 表名 change 旧列名新列名新列的属性	更改表的列

4.数据类型

4.1 作用

决定了存储数据时应该开辟的空间大小，如何识别一个特定的二进制序列，决定了数据的取值范围

说明一下：MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

4.2 数值类型-tinyint类型

表示范围：-128到127

如果插入的数据不在这个范围就会报错

4.2.1 无符号tinyint范围测试

创建一个表，表当中包含一个tinyint类型的列，并指定其为无符号类型。如下：

由于tinyint类型占用1字节，因此无符号tinyint的取值范围为0~255，插入该范围的数据时都能成功插入。如下：

但如果插入的数据不在0~255范围内，那么插入数据时就会产生报错。如下：

除非场景要求数值类型必须是无符号，否则尽量不要使用无符号，因为有符号的数值类型存不下的数据，其对应的无符号类型同样可能存不下，这时应该直接将数值类型进行提升

4.3 数值类型-bit类型

4.3.1 bit类型的显示方式

创建一个表，表当中包含一个int类型的id列和一个8位bit类型的a列。如下：

向表中插入一条记录，记录中指定id和a的值均为10，插入记录后查看表会发现a的值显示的并不是10。如下

根本原因是因为bit类型在显示时，是按照ASCII码对应的值进行显示的，而在ASCII码表中10对应的是控制字符LF，表示换行的意思。如果向表中插入记录时指定id和a的值均为65，由于ASCII码表中65对应的是字符A，因此插入记录后查看表就会发现a的值显示的是A。如下：

4.3.2 bit类型的范围测试

创建一个表，表当中包含用户名name和用户性别gender，其中gender的类型可以指定为1位bit类型，因为性别只有男和女两种取值，使用1个比特位来表示用户的性别就可以节省空间。如下：

如果规定gender列插入0表示男，插入1表示女，那么在插入用户信息时就可以通过插入0和1来指定用户的性别。如下：

如果插入gender列的数据不是0或1，那么插入数据时就会产生报错。如下：

虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。

因为查询位类型数据时，默认会按照ASCII码对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的

4.4 数值类型-float类型

float(m,n) 表示范围，总长度为m，小数长度为n

列如：float(4,2)时，99.44是ok的，而99.111是error的

4.4.1 无符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，并指定其为无符号类型。如下：

无符号float类型的取值范围，实际就是把对应有符号float类型中的负数部分拿走了，因此float(4,2)的取值范围为0~99.99，实际可插入的范围是0~99.99，如下：

如果插入的数据不在0~99.99围内，那么插入数据时就会产生报错。如下：

4. 数值类型-decimal类型

decimal类型和float类型类似，但是decimal类型的精度更高

4.1 精度测试

创建一个表，表当中分别包含一个float(10,8)的列和一个decimal(10,8)的列。如下：

向表当中插入一条记录，指定float和decimal的值均为23.12345612，但最终查表时会发现decimal保持了数据的原貌，而float则会存在一定的精度损失。如下：

4.5 字符串类型-char类型

需要注意的是，这里所说的字符并不只是指一个英文字母，一个汉字也是一个字符，因此只要插入的汉字个数不超过6个也是可以插入的。如下：

在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了

4.6 字符串类型-varchar类型

4.6.1 archar类型可指定的字符个数上限

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

而varchar类型可指定的字符个数上限，与表的编码格式有关：

对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为
65532除以3 = 21844
对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为
65532除以2 = 32766

因此在定义编码格式为utf8的表时，varchar(L)中的L如果超过了21844，则会产生报错。如下：

而在定义编码格式为gbk的表时，varchar(L)中的L如果超过了32766，则会产生报错。如下：

4.6.2 char和varchar的区别

存储字符：char类型可存储字符上限为255，varchar类型可存储字符上限与表的编码格式有关。

空间开辟：

char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错
varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错

4.6.3 char和varchar的选择

char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）

varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）

简单来说：
如果这个属性是身份证号，手机号等长度固定的，就使用定长的char，
如果属性是地址长度不确定的，那么使用可变的varchar

4.7 时间日期类型-date/datetime/timestamp

常用的三种时间日期类型如下：

date：日期格式为YYYY-MM-DD，占用三字节
datetime：时间日期格式为YYYY-MM-DD HH:MM:SS，占用八字节
timestamp：时间戳，格式为YYYY-MM-DD HH:MM:SS，占用四字节

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列。如下

查看表结构可以看到，timestamp类型的t3列是不允许为空的，它的默认值为CURRENT_TIMESTAMP(当前时间戳)。如下：

因此如果插入数据时不插入t3列，那么就会自动插入当前的时间戳。如下：

4.7.1 timestamp类型使用案例

创建一个评论表，表当中包含评论人的昵称、评论的内容和评论的发布时间。如下：

向评论表中插入记录时，只需要指明评论人的昵称和评论的内容，评论的发布时间默认会设置成该记录的插入时间。如下：

如果评论人修改了评论内容，那么就需要对评论表进行更新，更新表的同时评论的发布时间也会更新为修改表的时间。如下：

4.8 enum和set类型

在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值

在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值

创建一个调查表，表当中包含被调查人的姓名、性别和爱好。如下：

向表中插入记录时，被调查人的性别只能从男和女中进行二选一，被调查人的爱好可以从提供的若干个选项中进行多选一或多选多，多个爱好之间需要通过英文逗号隔开。如下

注：这里的enum中的值最好用英文，不要用中文，且插入数据时可以使用数字插入，
从1开始，且同样适用于set,insert into votes values('张三'，'1'，'跑步')，这里的1为男

4.8.1 enum和set查找

如果想要筛选出调查表中所有女同志的信息，那么直接在筛选时指明gender='女'即可，因为enum类型的值只能多选一。如下：

但如果要筛选出调查表中爱好包含敲代码的人的信息就比较麻烦了，如果继续使用上述方式，那么最终筛选出来的是爱好仅为敲代码的人的信息。如下：

4.8.2 find_in_set函数

为了解决上面set的查询结果不正确问题，就需要引入find_in_set(str,strlist)函数

该函数的作用是查询strlist中是否包含str，如果包含则返回str在strlist中的位置（从1开始），否则返回0

通过select可以对find_in_set函数进行验证，依次查找集合a,b,c中是否包含字符a、b、d，这时在查找字符a和b时就会得到其在集合中的下标，而在查找字符d时就会得到0值。如下：

这时就可以通过select搭配find_in_set函数，来筛选出爱好包含敲代码的人的信息了。如下：

此外，我们还可以继续添加筛选条件，比如筛选出爱好中包含敲代码，但爱好又不仅仅是敲代码的人的信息。如下：

其中hobby<>'敲代码'中的<>表示不等于

5. 表的约束

真正约束字段的是数据类型，如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败。

但是数据类型的约束很单一，为了更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，MySQL中出现了表的约束，目的就是为了尽可能保证数据安全，减少用户的误操作可能性

5.1 null

空属性有两个值，分别是null和not null，数据库默认字段基本都是允许为空的，但在实际开发中我们要尽可能保证字段不为空，因为空值无法参与运算

如果要让某个字段不允许为空，在创建表的时候就可以给对应字段设置not null属性。比如我们创建一个班级表，表当中包含班级名和该班级所在的教室，如果插入数据时不想让这两个字段为空，就可以在创建表时给这两个字段设置not null属性。如下：

这时如果在insert插入时不给具体的数值，就会报错

5.2 default

如果某一个字段会经常性的出现某个值，那么就可以考虑将这个值设置成该字段的默认值,
向表中插入数据时如果不给带有默认值的字段赋值，那么就会使用默认值进行插入

比如创建一个用户表，表当中包含用户的姓名、年龄和性别，将用户的年龄默认设置成0，将用户的性别默认设置成男。如下：

创建表完毕后查看表结构，可以看到默认值已经设置成功了。如下：

5.3 comment

列描述是在创建表的时候用来对各个字段进行描述的，列描述会根据表创建语句保存，一般是用来给程序员或DBA了解表的相关信息的，相当于一种注释。

比如创建一个用户表，表当中包含用户名、用户的年龄和用户的性别，在每一个字段后面添加上对应的列描述。如下：

创建表完毕后，通过show create table 表名可以看到创建表时的相关细节，包括列描述。如下：

5.4 zerofill

数值类型后面的圆括号中的数字，代表的是显示宽度，对应数值类型设置zerofill属性后，如果数据的宽度小于设定的宽度则自动填充0

比如创建一个表，表当中包含a和b两列整型数据，将它们的显示宽度都设置成5，但是没有设置zerofill属性。如下：

向表中插入一条记录，指明a和b的值均为1，由于我们没有给a和b字段设置zerofill属性，因此查看表中数据时显示出来的都是1，并没有显示宽度的概念。如下：

修改表结构，给a列添加上zerofill属性，由于a列数据的显示宽度为5，因此查看表中数据可以看到a列数据中宽度不足5位的数据都自动在前面填充0了。如下：

需要注意的是，zerofill属性的作用就是让数据以特定的方式进行显示而已，数据底层的储存方式并没有发生变化，通过hex函数可以看到a列中显示的00001在底层实际储存的还是1。如下：

5.5 auto_increment

设置了自增长属性的字段，插入数据时如果不给该字段值，那么系统会自动找出当前字段当中已有的最大值，将最大值进行加一后的值插入该字段

任何一个字段要做自增长，前提是其本身必须是一个索引（Key一栏有值），并且自增长字段必须是数值类型，一张表最多只能有一个自增长字段

自增长通常和主键搭配使用，作为逻辑主键。一般而言，建议将主键设计成与当前业务无关的字段，避免因为业务逻辑的调整而需要修改主键

比如创建一个表，表当中包含id和name，将id同时设置成主键和自增长字段。如下：

创建表完毕后查看表结构，可以看到id的Extra列中出现了auto_increment标志。如下：

向表中插入第一条记录时如果没有指明自增长字段的值，那么自增长字段的值默认将会从1开始。如下：

后续向表中插入记录时如果也不指明自增长字段的值，那么自增长字段的值就会依次递增。如下：

当然你也可以指定增长值，insert into auto_increment_table(name,id) value('xiaoyu',100)
这时下一次增长就会从100开始，101.....102...103...104
但在开发中更多的还是默认从1开始，不改变它

5.6 primary key

我们向表当中插入一条条记录后，为了方便后续进行查找，我们可以选择其中的某一字段作为键值key，当需要查找记录时就根据这个键值key来查找对应的记录。
主键用来唯一的约束该字段里面的数据，表当中每条记录的主键不能重复也不能为空，并且一张表里面只能有一个主键，此外，主键所在的列通常是整数类型

比如创建一个学生表，表当中包含学生的学号和姓名，由于学生的学号是不会重复的，因此可以将其设置成主键。如下：

创建表成功后查看表结构，可以看到id对应的Key列出现了PRI，这表示我们已经成功将学号设置成这张表的主键了。此外，虽然在创建表的时候没有给学号设置not null属性，但由于主键本身就是不能为空的，因此id默认也就不能为空了。如下：

主键约束：插入表中的记录的主键字段不能重复，

如果插入记录的主键与表中已有记录的主键重复，这时就会因为主键冲突而插入失败。如下：

使用alter table 表名 drop primary key即可删除指定表的主键，因为一个表只有一个主键，因此删除主键时只用指明要删除哪张表的主键即可。比如这里删除学生表的主键后再查看表结构，可以看到id对应的Key列的PRI已经没有了。如下：

对于已经创建好了的表，使用alter table 表名 add primary key 列名可以给指定列设置成主键，但是需要注意的是，只有列当中的值不为空并且不重复的列才能被设置成主键。比如这里重新将学号设置成学生表的主键后再查看表结构，可以看到id对应的Key列的PRI又回来了。如下：

5.6.1 复合主键

一张表里面只能有一个主键，但一个主键可以由多个字段来承担，这种主键叫做复合主键，复合主键用来唯一约束多个字段里面的数据，

只要不是同时出现冲突，就不会出现主键冲突，表当中每条记录的这多个字段不能同时重复也不能为空

比如创建一个进程表，表当中包含进程的IP地址、端口号和进程的相关信息，并将IP地址和端口号组合起来形成一个复合主键。如下：

表创建完毕后查看表结构，可以看到ip和port的Key列都有PRI标志，并且它们都是不允许为空的。如下：

在向进程表中插入数据时，只有插入进程的IP和端口同时出现冲突时才会产生主键冲突，否则就允许插入，如下：

查看表中插入的数据可以看到，表当中有重复的IP地址，也有重复的端口号，但是不会出现IP和端口均重复的，这就是复合主键的作用。如下：

5.7 unqiue key

一张表中往往有很多字段需要唯一性，但一张表中只能有一个主键，而唯一键就可以解决表中有多个字段需要唯一性约束的问题

唯一键和主键都能保证字段中数据的唯一性，但唯一键允许字段为空，并且可以多个字段为空，空字段不做唯一性比较

需要注意的是，不是主键具有唯一性，而是某个具有唯一性的字段被选择成为了主键，而那些不是主键但是同样需要唯一性约束的字段就应该设置成唯一键

比如创建一个学生表，表当中包含学生的id、姓名和电话号码，将我们选择id作为主键，但同时每个学生的电话号码作为唯一键，因此应该将电话号码设置成唯一键。如下：

表创建完毕后查看表结构，可以看到tel的Key列出现了UNI标志，这就表明tel已经成功被设置成唯一键了。如下：

向表中插入记录时，如果插入记录中的电话号码与表中已有记录的电话号码出现重复，那么就会因为唯一键冲突而插入失败。如下：

此外，向表中插入的记录可以不指明唯一键字段的值，此时该字段默认为空，不做唯一性比较。如下：

5.8 foregin key

foreign key(字段) reference 主表(字段)

外键用来定义主表和从表之间的关系，外键约束主要定义在从表上，主表必须有主键约束或唯一键约束

外键定义后，要求插入外键列的数据必须在主表对应的列存在或为null

比如先创建一个班级表作为主表，表当中包含班级的id和班级名，并将班级id设置为主键。如下

再创建一个学生表作为从表，表当中包含学生的id、姓名以及学生所在班级对应的id，并将学生表中的班级id列设置成外键，关联到班级表中的班级id列。如下：

表创建完毕后查看学生表的表结构，可以看到学生表中的班级id对应的Key列出现了MUL标志，这表明class_id已经成功被设置成了外键。如下：

为了演示外键约束，我们先向班级表中插入两条记录。如下：

这时向学生表中插入记录时，如果插入的记录对应的班级id是班级表中存在的，或者插入的班级id为null，那么此时是允许进行插入的。如下：

但如果插入学生表的记录对应的班级id是3，相当于插入学生表的这条记录对应的班级并不存在，此时将会插入失败，这就是外键约束。如下：

理论上来说，我们创建班级表和学生表后就算不设置外键，在语义上其实也已经有了外键，但这样我们没办法保证后续插入学生表的记录中的班级id的正确性
而我们给学生表中的班级id设置成外键后，外键约束就能保证只有班级id在班级表中存在的记录才能插入学生表，否则就会插入失败
实际建立外键的本质就是把相关性交给MySQL去审核了，提前告诉MySQL表之间的约束关系，当用户插入不符合业务逻辑的数据时，MySQL就不允许你进行插入

6. 表的增删查改

6.1 增加

在插入记录时，只有允许为空的列或自增长字段可以不指定值插入，不允许为空的列必须指定值插入，否则会报错

6.1.1 单行数据+全列插入

insert into students values(100,1000,“张三'，NULL);

6.1.2 多行数据+指定列插入

使用insert语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。如下

6.1.3 更新插入

向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败。如下：

解决方案如下：

insert into students(id,sn,name) values(100,10010,'唐大师')
on duplicate key update sn = 10010,name = '唐大师'；

小知识：执行插入更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况

0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同，无影响
1 row affected：表中没有冲突数据，数据直接被插入。
2 rows affected：表中有冲突数据，并且数据已经被更新。

6.1.4 强行插入(替换数据)

如果表中没有冲突数据，则直接插入数据

如果表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据

要达到上述效果，只需要在插入数据时将SQL语句中的insert改为replace即可。比如：

6.2 删除

delete from exam_result wher name = '张三';

6.2.1 删除整表

该操作不会重置表中的auto_increment属性

delete from 表名，只是删除了表的内容，表的结构还在

注：该操作慎用

6.2.2 截断表

该操作会重置表中的auto_increment属性

truncate 表名

6.3 修改

在修改数据之前，先查看孙悟空同学当前的数学成绩。如下：

在update语句中指明要将筛选出来的记录的数学成绩改为80分，并在修改后再次查看数据确保数据成功被修改。如下：

update exam_result set math = 80 where name = '张三'

6.4 查找(重点)

为了进行演示，下面创建一个成绩表，表当中包含自增长的主键id、姓名以及该同学的语文成绩、数学成绩和英语成绩。如下：

创建表完毕后查看表结构，可以看到表结构如下：

接下来向表中插入几条测试记录，以供我们进行查找。如下：

6.4.1 全列查询

通常情况下不建议使用*进行全列查询,因为被查询到的数据需要通过网络从MySQL服务器传输到本主机，查询的列越多也就意味着需要传输的数据量越大，则效率越低，此外，进行全列查询还可能会影响到索引的使用

select * from exam_result;

6.4.2 指定列查询

在查询数据时也可以只对指定的列进行查询，这时将需要查询的列在column列表列出即可。如下：

select name from exam_result;

6.4.3 查询字段为表达式

column列表中的表达式可以包含表中已有的字段，这时每当一条记录被筛选出来时，就会将记录中对应的列值提供给表达式进行计算

select id,name,math+10 from exam_result;

6.4.4 为查询结果指定别名

select id,name chinese+math+english [as] 总分 from exam_result

6.4.5 结果去重

查询成绩表时指定查询数学成绩对应的列，可以看到数学成绩中有重复的分数。如下：

如果想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct。如下：

6.4.6 where用法

如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句

如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句

比较运算符

运算符	说明
>、>=、<、<=	大于、大于等于、小于、小于等于
=	等于。NULL不安全，例如NULL=NULL的结果是NULL而不是TRUE(1)
<=>	完全等于。NULL安全，例如NULL<=>NULL的结果就是TRUE(1)
!=、<>	不等于
between A and B	范围匹配。如果A<=value<=B，则返回TRUE(1)
in(option1, option2, …)	如果是in中的任意一个option，则返回TRUE(1)
is null	如果是NULL，则返回TRUE(1)
is not null	如果不是NULL，则返回TRUE(1)
like	模糊匹配。`%`表示任意多个字符（包括0个），`_`表示任意一个字符

逻辑运算符

运算符	说明
and	多个条件同时为TRUE(1)，则结果为TRUE(1)，否则为FALSE(0)
or	任意一个条件为TRUE(1)，则结果为TRUE(1)，否则为FALSE(0)
not	条件为TRUE(1)，则结果为FALSE(0)；条件为FALSE(0)，则结果为TRUE(1)

查询英语不及格的同学及其英语成绩

查询语文成绩在80到90分的同学及其语文成绩

查询数学成绩是58或59或98或99分的同学及其数学成绩

查询姓孙的同学

在where子句中通过模糊匹配来判断当前同学是否姓孙（需要用到%来匹配多个字符），在select的column列表中指明要查询的列为姓名。如下：

查询孙某同学

在where子句中通过模糊匹配来判断当前同学是否为孙某（需要用到_来严格匹配单个字符），在select的column列表中指明要查询的列为姓名。如下：

查询语文成绩好于英语成绩的同学

查询总成绩在200分以下的同学

注：需要注意的是，在where子句中不能使用select中指定的别名，因为存在先后顺序

查询数据时是先根据where子句筛选出符合条件的记录，然后再将符合条件的记录作为数据源来依次执行select语句

也就是说，where子句的执行是先于select语句的，所以在where子句中不能使用别名，如果在where子句中使用别名，那么在查询数据时就会产生报错。如下：

查询语文成绩大于80分并且不姓孙的同学

查询孙某同学，否则要求总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分

这里用之前演示新增数据的学生表来演示NULL查询，学生表中的内容如下：

查询QQ号已知的同学

查询QQ号未知的同学

在where子句中指明筛选条件为QQ号为NULL，在select的column列表中指明要查询的列为姓名和QQ号。如下：

需要注意的是，在与NULL值作比较的时候应该使用<=>运算符，使用=运算符无法得到正确的查询结果。如下：

因为=运算符是NULL不安全的，使用=运算符将任何值与NULL作比较，得到的结果都是NULL。如下：

但是<=>运算符是NULL安全的，使用<=>运算符将NULL和NULL作比较得到的结果为TRUE(1)，将非NULL值与NULL作比较得到的结果为FALSE(0)。如下

6.4.7 结果排序

select name name,math from exam_result order by math asc; asc表示升序，desc表示降序

查询同学及其QQ号，按QQ号排序显示

说明一下： NULL值视为比任何值都小，因此排升序时出现在最上面。

查询同学及其QQ号，按QQ号降序显示

查询同学的各门成绩，依次按数学降序、英语升序、语文升序显示

说明一下：

order by子句中可以指明按照多个字段进行排序，每个字段都可以指明按照升序或降序进行排序，各个字段之间使用逗号隔开，排序优先级与书写顺序相同。
比如上述SQL中，当两条记录的数学成绩相同时就会按照英语成绩进行排序，如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序，以此类推

查询同学及其总分，按总分降序显示

说明一下：

查询数据时是先根据where子句筛选出符合条件的记录
然后再将符合条件的记录作为数据源来依次执行select语句。
最后再通过order by子句对select语句的执行结果进行排序

也就是说，order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名

6.4.8 筛选分页结果

从第0条记录开始，向后筛选出n条记录：limit n

从第s条记录开始，向后筛选出n条记录：limit s n

从第s条记录开始，向后筛选出n条记录：limit n offset s

查询第3页记录时在查询全表数据的SQL后，加上limit子句指明从第6条记录开始，向后筛选出3条记录。如下：

注：分页数是从0开始的，如果从表中筛选出的记录不足n个，则筛选出几个就显示几个

6.4.9 插入查询结果

删除表中重复的记录，重复的数据只能有一份

创建一张测试表，表中包含id和姓名。如下：

向测试表中插入一些测试数据，数据中存在重复的记录。如下：

现在要求删除测试表中重复的数据，思路如下：

创建一张临时表，该表的结构与测试表的结构相同。
以去重的方式查询测试表中的数据，并将查询结果插入到临时表中。
将测试表重命名为其他名字，再将临时表重命名为测试表的名字，实现原子去重操作。

由于临时表和测试表的结构相同，因此在创建临时表的时候可以借助like进行创建。如下：

通过插入查询语句将去重查询后的结果插入到临时表中，由于临时表和测试表的结构相同，并且select进行的是全列查询，因此在插入时不用在表名后指明column列表。如下：

insert into no_duplicate_table select distinct * from duplicate_table;

将测试表重命名为其他名字（相当于对去重前的数据进行备份，如果不需要可以直接删除），将临时表重命名为测试表的名字，这时便完成了表中数据的去重操作。如下：

6.4.10 聚合函数

聚合函数对一组值执行计算并返回单一的值，常用的聚合函数如下：

函数	说明
count()	返回查询到的数据的数量
sum()	返回查询到的数据的总和，不是数字没有意义
avg()	返回查询到的数据的平均值，不是数字没有意义
max()	返回查询到的数据的最大值，不是数字没有意义
min()	返回查询到的数据的最小值，不是数字没有意义

聚合函数可以在select语句中使用，此时select每处理一条记录时都会将对应的参数传递给这些聚合函数。

统计班级共有多少同学

统计班级收集的QQ号有多少个

说明一下： 如果count函数的参数是一个确定的列名，那么count函数将会忽略该列中的NULL值。

统计本次考试数学成绩的分数个数（去重）

在使用count函数时（包括其他聚合函数），在传递的参数之前加上distinct，这时便能统计出表中数学成绩去重后的个数，如下：cout(distinct math)

6.4.11 分组查询

准备测试表，如下：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

上述SQL中创建了一个名为scott的数据库，在该数据库中分别创建了部门表（dept）、员工表（emp）和工资等级表（salgrade），并分别向三张表中插入了一些数据用于查询

将上述SQL保存到文件中，然后在MySQL中使用source命令依次执行文件中的SQL。如下：

显示每个部门的每种岗位的平均工资和最低工资

在group by子句中指明依次按照部门号和岗位进行分组，在select语句中使用avg函数和min函数，分别查询每个部门的每种岗位的平均工资和最低工资。如下：

说明一下：

group by子句中可以指明按照多个字段进行分组，各个字段之间使用逗号隔开，分组优先级与书写顺序相同
比如上述SQL中，当两条记录的部门号相同时，将会继续按照岗位进行分组。

6.4.12 having 条件

having子句和where子句的区别

where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。

在group by子句中指明按照部门号进行分组，在select语句中使用avg函数查询每个部门的平均工资。如下：

在上述SQL的基础上，在having子句中指明筛选条件为平均工资小于2000。如下：

6.5 SQL中各语句的执行顺序(重点)

查询数据时，SQL中各语句的执行顺序如下：

根据where子句筛选出符合条件的记录。
根据group by子句对数据进行分组。
将分组后的数据依次执行select语句。
根据having子句对分组后的数据进行进一步筛选。
根据order by子句对数据进行排序。
根据limit子句筛选若干条记录进行显示。

7.内置函数(了解)

7.1 日期函数

函数名称	描述
current_date()	获取当前日期
current_time()	获取当前时间
current_timestamp()	获取当前时间戳
now()	获取当前日期时间
date(datetime)	获取datetime参数的日期部分
date_add(date, interval d_value_type)	在date中添加日期或时间，interval后的数值单位可以是：year、month、day、hour、minute、second
date_sub(date, interval d_value_type)	在date中减去日期或时间，interval后的数值单位可以是：year、month、day、hour、minute、second
datediff(date1, date2)	获取两个日期的差，单位是天

7.2 字符串函数

函数名称	描述
charset(str)	获取字符串使用的字符集
concat(str1, str2 [, …])	获取连接后的字符串
instr(str, substr)	获取substr在str中首次出现的位置，没有出现返回0
ucase(str)	获取转换成大写后的字符串
lcase(str)	获取转换成小写后的字符串
left(str, length)	从字符串的左边开始，向后截取length个字符
length(str)	获取字符串占用的字节数
replace(str, search_str, replace_str)	将字符串中的search_str替换成replace_str
strcmp(str1, str2)	逐字符比较两个字符串的大小
substring(str, position [, length])	从字符串的position开始，向后截取length个字符
ltrim(str)、rtrim(str)、trim(str)	去除字符串的前空格、后空格、前后空格

7.3 数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换成二进制
hex(decimal_number)	十进制转换成十六进制
conv(number, from_base, to_base)	from_base进制转换成to_base进制
ceiling(number)	向上取整
floor(number)	向下取整
format(number, n)	格式化，保留n位小数（四舍五入）
rand()	生成随机浮点数，范围 [0.0, 1.0)
mod(number, denominator)	求余

7.4 其他函数

user函数

user函数用于获取MySQL连接的当前用户名和主机名。如下：

md5函数

md5函数用于对一个字符串进行md5摘要，摘要后得到一个32位字符串。如下：

拓展：

一般情况下公司内部数据库不会存储用户的明文密码，而会将用户密码形成摘要后存储对应的摘要，当用户登录账号时，将用户输入的的密码形成摘要后与数据库中存储的摘要做对比，如果对比成功则允许登录

这么做的好处主要有两个，第一个好处就是公司内部数据库中存储的不是用户的明文信息，就算用户信息泄露了也不会产生太大影响，第二个好处就是形成的摘要是定长的，这样有利于数据库表结构的设计

database函数

database函数用于显示当前正在使用的数据库。如下：

password函数

password函数用于对用户数据进行加密。如下：

ifnull函数

ifnull函数接受两个参数，如果第一个参数不为null则返回第一个参数值，否则返回第二个参数值。如下：

8. MySQL复合查询(重点)

8.1 多表查询

多表查询的本质：就是对给定的多张表取笛卡尔积，作为多表查询的初始数据源，然后再查询

在进行多表查询时，只需要将多张表的表名依次放到from子句之后，用逗号隔开即可，这时MySQL将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据源

所谓的对多张表取笛卡尔积，就是得到这多张表的记录的所有可能有序对组成的集合，比如下面对员工表和部门表进行多表查询，由于查询语句中没有指明筛选条件，因此最终得到的结果便是员工表和部门表的笛卡尔积

8.1.1 笛卡尔积的初步过滤

需要注意的是，对多张表取笛卡尔积后得到的数据并不都是有意义的，比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。如下：

说明一下： 进行笛卡尔积的多张表中可能会存在相同的列名，这时在选中列名时需要通过表名.列名的方式进行指明。

显示部门号为10的部门名、员工名和员工工资

由于部门名只有部门表中才有，而员工名和员工工资只有员工表中才有，因此需要同时使用员工表和部门表进行多表查询，在where子句中指明筛选条件为员工的部门号等于部门编号，并且部门号为10的记录。如下：

select dname,ename,sal from emp,dept where emp.depton=dept.deptno and emp.deptno = 10

8.2 自连接

自连接是指在同一张表进行连接查询，也就是说我们不仅可以取不同表的笛卡尔积，也可以对同一张表取笛卡尔积。

如果一张表中的某个字段能够将表中的多条记录关联起来，那么就可以通过自连接将表中通过该字段关联的记录组合起来

显示员工FORD的上级领导的编号和姓名

解决该问题可以使用子查询，先对员工表进行查询得到FORD的领导的编号，然后再根据领导的编号对员工表进行查询得到FORD领导的姓名。如下：

此外，解决该问题也可以使用自连接，因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。如下：

select leader.empno,leader.ename from emp worker,emp leader
where worker.ename = 'FORD' and worker.mgr = leader.empno

说明一下： 由于自连接是对同一张表取笛卡尔积，因此在自连接时至少需要给一张表取别名，否则无法区分这两张表中的列

8.3 子查询

子查询是指嵌入在其他SQL语句中的查询语句，也叫嵌套查询

8.3.1 单行子查询

显示SMITH同一部门的员工

select * from emp where deptno = (select depton from emp where ename='SMITH') and enmae <> 'SMITH'

8.3.2 多行子查询

in关键字：显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号，但是不包含10号部门的员工

select ename,job,sal,deptno from emp
where job in (select distinct job from emp where deptno = 10) and deptno<>10

all关键字：显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号

注：同一个案例，可以用不同的SQL语句完成
select ename,sal,deptno from emp
where sal > (select max(sal) from emp where deptno = 30)

any关键字：显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号，包含30号部门的员工

select ename,sal,deptno from emp
where sal > (select min(sal) from emp where deptno = 30);

8.3.3 多列子查询

显示和SMITH的部门和岗位完全相同的员工，不包含SMITH本人

多列子查询，是指返回多列数据的子查询

8.4 在from子句中使用子查询

select查询到的结果也是一张表,自然也可以跟其他表连接

建议：只要设计多表查询，就可以使用这个方案

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

获取各个部门的平均工资，将其看作临时表,
这里的新表需要加别名,像自连接一样,以示区分

8.4 合并查询

union用于取得两个查询结果的并集，union会自动去掉结果集中的重复行

union all也用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。

select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job='MANAGER';

select ename,sal,job from emp where sal >2500
union all
select ename,sal job form emp where job = 'MANGER';

说明一下：

待合并的两个查询结果的列的数量必须一致，否则无法合并。
待合并的两个查询结果对应的列属性可以不一样，但不建议这样做。

9.内外链接

9.1 内链接

select ename,dname from emp inner join dept on emp.deptno=dept.deptno and ename=''SMITH"

9.2 左外链接

以左表为基准，右表没有的值，用NULL表示

select * from stu left join exam on stu.id = exam.id

9.3 右外链接

以右表为基准，左表没有的值，用NULL表示

select dname,emp.* from emp right join dept on dept.deptno=emp.deptno;

10.索引

数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是 O ( N )

索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据

索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护

10.1 索引分类

主键索引（primary key），唯一索引（unique），普通索引（index），全文索引（fulltext）

10.2 索引价值

使用如下SQL创建一个海量数据表：

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

上述SQL中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录

将上述SQL保存到文件中，然后在MySQL中使用source依次执行文件中的SQL即可。如下：

由于EMP表中有八百万条记录，因此在查看EMP表中的数据时可以带上limit子句。如下：

通过desc命令可以看到，目前EMP员工表中没有建立任何索引。如下：

查询EMP表中指定工号的员工信息，可以看到每次查询过程都需要花费4.5秒左右。如下：

当我们给员工表中的工号建立索引后，数据库底层就会为员工表中的数据记录构建特定的数据结构，需要注意的是，由于当前员工表中的数据量较大，因此建立索引时也需要花费较长时间。如下：

alter table EMP add index(empno);

这时再查询EMP表中指定工号的员工信息，可以看到几乎检测不到查询时耗费的时间。如下：

根本原因就是，给员工工号创建索引后再根据员工工号来查询数据，这时就能够直接通过底层建立的数据结构来快速定位到目标数据，从而提高数据的检索速度，这就是索引的价值。

10.3 认识磁盘

MySQL给用户提供存储服务，存储的数据在磁盘这个外设当中,磁盘是计算机中的一个机械设备，相比于计算机的其他电子元件，磁盘的效率是比较低的,

10.3.1 磁盘的结构

永磁铁：机械硬盘的存储方式与磁带比较类似，磁体具有记忆的功能，永磁铁是为了保证磁性的稳定。
音圈马达：硬盘读取数据的关键部位，主要作用是将存储在磁盘上的信息转换为电信号向外传输。
主轴：保证电机稳定的转动，磁盘转动才能读出数据。
空气滤波片：过滤空气硬盘透气孔中进入的空气，保证硬盘内部清洁，同时还可以防止硬盘内部的零件氧化，确保硬盘安全使用。
磁盘：硬盘一般都是铝合金制作的，主要是用来存储文件的。
磁头：用来读取盘片上的信息。
串行接口：用来连接电脑与硬盘的接口，起到传输的作用

10.3.2 盘片

一个磁盘由多个盘片叠加而成，盘片的表面涂有磁性物质，这些磁性物质就是用来记录二进制数据的，因为盘片的正反两面都可涂上磁性物质，因此一个盘片有两个盘面

磁道： 磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有一个编号，最外面的是0磁道。
扇区： 每个磁道被划分成若干个扇区，每个扇区的存储容量为512字节，每个扇区都有一个编号

说明一下：

由于每个扇区的存储容量相同，因此最内侧磁道上的扇区数据密度最大，而最外侧磁道上的扇区数据密度最小。
近三十年来，扇区大小一直是512字节，但最近几年正在迁移到更大、更高效的4096字节扇区，通常称为4K扇区。
数据库文件就是保存在磁盘中的一个个扇区中的，因此找到一个文件本质就是，在磁盘上找到保存该文件的所有扇区。

10.3.3 扇区的定位方式

一个磁盘由多个盘片叠加而成，每个盘片有两个盘面，所有盘面中半径相同的同心磁道构成一个柱面，每个盘面都有一个对应的磁头，每个磁头都有一个编号，所有的磁头都是连在同一个磁臂上的

定位扇区时采用CHS寻址方式

磁头（Heads）：每个盘面都有一个对应的磁头，因此确定了磁头也就确定了数据在哪一个盘面。
柱面（Cylinder）：所有盘面中半径相同的同心磁道构成柱面，因此在确定了数据在哪一个盘面的基础上，再确定柱面也就确定了数据在该盘面上的哪一个磁道。
扇区（Sector）：每个磁道被划分成若干个扇区，因此在确定了数据在哪一个磁道的基础上，再确定扇区也就确定了数据在该磁道上的哪个扇区。

简单来说，CHS寻址方式就是先通过H确定数据所在的盘面，再通过C确定数据所在的磁道，最后通过S定位到目标扇区。

说明一下：

CHS寻址方式是磁盘定位扇区的方式，但实际CHS寻址方式对磁盘以外的设备来说没什么作用，因此系统软件在定位磁盘上的数据时采用的是LBA（Logical Block Address，逻辑区块地址）

LBA是描述计算机存储设备上数据所在区块的通用机制，LBA和CHS之间可以通过计算公式进行相互转换，LBA存在的意义就是对底层逻辑器件进行虚拟化，让系统软件可以不用关心底层硬件具体的寻址方式，而实际底层硬件采用的还是CHS寻址方式

10.3.4 操作系统与磁盘交互的基本单位

操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节

操作系统与磁盘以4KB作为IO交互的基本单位，一方面是为了提高IO效率，另一方面是为了实现硬件和系统的解耦

10.3.5 随机访问与连续访问

随机访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO

连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO

10.4 MySQL与磁盘交互的基本单位

MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。如下：

10.5 Buffer Pool

在MySQL中进行的各种CRUD操作时，都需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，因此为了便于CPU参与，就需要先将数据加载到内存当中
所以在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page
为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘
由于内核中是有内核缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘

因此所谓的操作系统和磁盘交互的基本单位是4KB，就是指内核缓冲区与磁盘之间是以4KB为单位进行交互的。而MySQL的Buffer Pool和磁盘实际并不是直接交互的，因此所谓的MySQL与磁盘交互的基本单位是16KB，

指的是MySQL的Buffer Pool与内核缓冲区之间是以16KB为单位进行交互的。只不过在说的时候更关注的是MySQL和磁盘之间的关系，所以直接说的是MySQL与磁盘交互的基本单位是16KB，相当于忽略了中间的内核缓冲区

10.6 Mysql存储底层(难点)

在Mysql看来，无论存储的是什么数据，都应该被管理起来，都需要用双链表连接起来

10.6.1 为什么每次插入主键时都是有序的

虽然插入的时候是无序的，但是由于是主键会建立数据结构存储，所以是有序的

数据会按照主键进行排序，则后面引入的页内目录才有意义，就像书中每一页都是按照页码进行排序的一样，如果一本书的页码是乱序的，那么它的目录根本就没有意

创建一个用户表，表当中包含用户的id、年龄和姓名，并将用户的id设置成主键。如下：

创建表完毕后向表中插入一些数据，并且插入数据时没有按照主键的大小顺序插入。如下：

但最终当我们查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的。如下：

10.6.2 为什么MySQL与磁盘交互的基本单位是Page

MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率

10.6.3 单个page

MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来

MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段

此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序

假设上述测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：

10.6.4 单个Page内创建页内目录

Page结构体内部存储的数据记录是以单链表的形式组织起来的，当页内部的数据量增多时，本质在页内部进行的还是线性遍历，效率低下

这时可以在Page结构体内部引入页内目录，将Page结构体内部存储的数据记录按照主键划分为若干区域，页内目录中就存储着这若干区域的最小键值

在Page结构体内部引入页内目录后，在页内部查询数据时就可以先通过页内目录找到目标数据所在区域的起始记录，然后再从该记录开始向后遍历找到目标记录(相当于分段了)

说明一下：

在每个Page结构体内部引入页内目录，目的是为了加速在单个Page内部数据查询的效率。由于这个页内目录也是保存在Page内部的，而单个Page的大小是固定的，因此添加页内目录后Page内部能够保存的数据记录变少了，所以在Page内部引入页内目录本质是一种空间换时间的做法，就像给书添加目录需要花费更多的纸张一样
每个Page结构体内部的数据会按照主键进行排序，其实就是为了引入页内目录，因为只有数据按照主键排序后引入页内目录才有意义，就像书中每一页都是按照页码进行排序的一样，如果一本书的页码是乱序的，那么它的目录根本就没有意义

10.6.5 多个Page

随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据。

这时在查询数据时就需要，先遍历Page双链表确定目标数据在哪一个Page，然后再在该Page内部找到目标数据

多个Page的示意图如下：

10.6.6 给多个page创建页目录

虽然在单个Page内部能够通过页内目录来快速定位数据，但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历

这时可以给各个Page结构体也建立页目录，页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值

在给各个Page结构体建立页目录后，在查询数据时就可以先通过遍历页目录找到目标数据所在的Page，然后再在该Page内部找到目标数据

给各个Page建立页目录后的示意图如下：

说明一下：

这里的页目录与之前的页内目录的区别在于，页目录管理的是一个个的Page，而页内目录管理的是一条条的记录。此外，页内目录与其管理的多条记录是保存在同一个Page中的，而页目录是重新申请的一个Page结构体来保存的

就算给各个Page结构体也建立了页目录，但随着数据量不断增大，页目录的数量也会越来越多，这时在遍历页目录寻找目标Page时本质进行的还是线性遍历

类似的，我们可以不断在页目录之上再创建页目录，最终就一定能够得到一个入口页目录，这时在查询数据时就可以从入口页目录开始不断查询页目录，最终找到目标数据所在的Page，然后再在该Page内部找到目标数据

最终构建出来的结构如下：

说明一下：

最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率

如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率

MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构

10.6.8 B+树中的Page结点是否需要全量加入到Buffer Pool中？

当对MySQL中的某张表进行增删查改操作时，不需要将其对应B+树的所有结点全量加入到Buffer Pool中，甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中

当后续访问表中的数据时，再将该数据对应路径上的结点加入到Buffer Pool中即可，对于其他不需要的结点根本不用加入到Buffer Pool中，这一点和操作系统中的页表是很像的

此外，在刷新数据时也不需要将B+树中所有的结点都进行刷新，在Page结构体中有一个标记位用来标记当前Page是否被修改过，如果被修改过则说明这是一个脏数据，在刷新数据时只有脏数据才需要被刷新到磁盘上

由于B+树中的结点都是16KB大小的Page，因此无论是刷新数据到磁盘函数从磁盘加载数据到Buffer Pool，都是以Page为单位进行的，这也就是所谓的MySQL与磁盘交互的基本单位是Page

如果把这棵B+树逆时针旋转90度，就会发现这其实就是操作系统中的页表结构，本质操作系统中的页表也是B+树结构，如下：

以32位平台为例，页表将一个虚拟地址转换成物理地址的过程如下：

选择虚拟地址的前10个比特位在页目录当中进行查找，找到对应的页表
再继续选择虚拟地址后续的10个比特位在对应的页表当中进行查找，找到物理内存中对应页框的起始地址
最后选择虚拟地址中剩下的12个比特位作为偏移量，从对应页框的起始地址处向后进行偏移，最终得到的就是转换后的物理地址

说明一下：

12个比特位有2^12种取值,而2^12对应的就是4kb,所以物理内存中一个页框的大小就是4KB,这也就是为什么操作系统与磁盘交互的基本单位是4KB的原因
此外，页表中的各个B+树结点也不需要全量加入到内存中，而只需要加入访问到的结点即可，所以页表占用的内存大小实际是可控的，这也就是为什么二级页表可行的原因

10.6.9 其他数据结构

链表：查找时是线性遍历，效率太低

普通二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。

AVL树和红黑树：虽然保证了二叉树是绝对或近似平衡的，不会退化成线性结构，但AVL树和红黑树都是二叉树结构，这就意味着树的层高会比较高，而查询数据时都是从根结点开始向下进行查找的，这也就意味着在查询过程中需要遍历更多结点，如果这些结点还没有被加载到Buffer Pool中，这时就需要进行更多次的IO操作，所以最终没有选择其作为索引结构

哈希表：官方的索引实现方式中MySQL是支持HASH的，只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是O(1),哈希表也有一个缺点就是不利于进行数据的范围查找

下面是几个常见的存储引擎，与其所支持的索引类型：

存储引擎	支持的索引类型
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH、BTREE
NDB	HASH、BTREE

10.6.10 为何选择B+树，而不选择B树

普通B树中的所有结点中都同时包括索引信息和数据信息，则树形结构一定会变得更高更瘦当查询数据时就可能需要与磁盘进行更多次的IO操作
B树的叶子结点没有链接，而B+数的叶子结点链接在一起了，而叶子结点链接在一起有利于范围查找，即先找到第一个数据然后继续向后遍历即可以找到所有数据

10.6.11 聚簇索引 && 非聚簇索引

聚簇索引： 像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引

非聚簇索引： 像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引

当采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件。如下：

当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件。如下：

说明一下：

.frm为表结构文件
.ibd为数据记录和索引结构的结合
.MYD为数据记录，.MYI为索引结构

10.7 索引操作

10.7.0 查看是否查询用到索引

explain select* from articles where body like '%database%'\G

10.7.1 创建主键索引-primary key

方式一

创建表时，直接在对应的字段名后指定primary key。如下：

方式二

在创建表的最后，指定某列或某几列为主键索引。如下：

方式三

创建表后，使用alter命令给指定字段添加主键索引。如下：

主键索引的特点

一个表中，最多只能有一个主键索引，一个主键可以由多个列同时承担。
主键索引的查询效率高。
创建主键索引的列，其列值不能为NULL，且不能重复。
主键索引的列一般是数字类型

10.7.2 创建唯一索引-unique key

方式一

在创建表时，直接在对应的字段名后指定unique。如下：

方式二

在创建表的最后，指定某列或某几列为唯一索引。如下：

方式三

创建表后，使用alter命令给指定字段添加唯一索引。如下：

唯一索引的特点

一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。
唯一索引的查询效率高。
创建唯一索引的列，其列值可以为NULL，但是不能重复。
如果给唯一索引设置NOT NULL属性，则等价于主键索引。

10.7.3 创建普通索引-index

方式一

在创建表的最后，指定某列或某几列为普通索引。如下：

方式二

创建表后，使用alter命令给指定字段添加普通索引。如下：

方式三

创建表后，使用create命令给指定字段创建普通索引，并指定索引名。如下：

create index idx_name on user9(name)

普通索引的特点

一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担
创建普通索引的列，其列值可以为NULL，也可以重复

10.7.4 创建全文索引-fulltex

全文索引案例

全文索引比较常见的案例就是对文章中的词进行搜索，比如下面创建一个文章表，表当中包含文章的id、文章名称、文章内容，并在创建表的最后通过fulltext给title和body列创建全文索引。如下：

下面向表当中插入一些测试数据。如下：

如果要查询哪些文章中包含database关键字，我们可以通过模糊匹配进行查找。如下：

但实际这种查找方式并没有用到全文索引，在SQL语句前面加上explain，可以看到key对应的值为NULL，表示这条SQL在执行过程中没有用到任何索引。如下：

如果要通过全文索引来查询，需要使用match against进行搜索。如下：

select * from articles where match(title,body) against('database');

在这条SQL语句前面加上explain，可以看到key对应的值为title，表示这条SQL在执行过程中用到了索引名为title的索引。如下：

说明一下：

MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的
同时使用title和body建立全文索引时，相当于建立了一个复合索引，默认会选择fulltext中的第一个列名作为这个复合索引的索引名，所以这里explain中key对应的索引名为title
由于是title和body共同建立的全文索引，所以如果文章当中没有出现关键字，但文章名称中出现了关键字则也会被筛选出来（当前示例没有体现出来）

10.7.5 查询索引

使用show keys from 表名查询，比如查询articles表中的索引信息。如下：

show keys from articles\G

方式二

使用show index from 表名SQL查询，比如查询articles表中的索引信息。如下：

show index from articles\G

方式三

使用desc 表名查询（信息比较简略），比如查询articles表中的索引信息。如下：

10.7.6 删除索引

创建测试表

创建一个用户表用于测试索引的删除，表中包含用户的id、姓名和邮箱，并将这三列分别设置为主键索引、唯一索引和普通索引。如下：

删除主键索引

使用alter table 表名 drop primary key即可删除主键索引。如下：

删除非主键索引(指定索引名)

alter table user10 drop index name;
drop index name on user10

说明一下：

一个表只有一个主键索引，所以在删除主键索引的时候不用指明索引名，而一个表中可能有多个非主键索引，所以在删除非主键索引时需要指明索引名

10.7.7 索引创建原则

创建索引的目的就是为了提高查询的效率

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
不会出现在where子句中的字段不应该创建索引

11.事务管理

事务由一条或多条SQL语句组成，这些语句在逻辑上存在相关性，共同完成一个任务

事务主要用于处理操作量大，复杂度高的数据

11.1 事务的四个属性

原子性：事务在执行过程中如果发生错误，则需要自动回滚到事务最开始的状态，就像这个事务从来没有执行过一样

持久性：事务处理结束后，对数据的修改必须是永久的，即便系统故障也不能丢失，

隔离性 ：多个事务同时访问同一份数据时，必须保证这多个事务在并发执行时，不会因为由于交叉执行而导致数据的不一

一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态，当数据库只包含事务成功提交的结果时，数据库就处于一致性状态，致性实际是数据库最终要达到的效果，一致性不仅需要原子性、持久性和隔离性来保证，还需要上层用户编写出正确的业务逻辑

11.2 脏读 && 不可重复读 &&

一个事务在执行过程中，读取到另一个执行中的事务所做的修改，但是该事务还没有进行提交，这种现象叫做脏读

一个事务在执行过程中，两个相同的select查询得到了不同的数据，这种现象叫做不可重复读

在可重复读隔离级别下，一个事务在执行过程中，相同的select查询得到的是相同的数据，这就是所谓的可重复读

一个事务在执行过程中，相同的select查询得到了新的数据，如同出现了幻觉，这种现象叫做幻读

注: MySQL是通过Next-Key锁（GAP+行锁）来解决幻读问题的

11.1 为什么会出现事务

简化我们的编程模型，减低各种各样的潜在错误和并发问题

11.2 事务的版本支持

通过show engines命令可以查看数据库引擎，如下

Transactions： 表示存储引擎是否支持事务，可以看到InnoDB存储引擎支持事务，而MyISAM存储引擎不支持事务

11.3 事务的提交方式

查看事务的提交方式

事务常见的提交方式有两种，分别是自动提交和手动提交

通过show命令查看autocommit全局变量，可以查看事务的自动提交是否被打开。如下：

show variables like 'autocommit'
这里的值value为on表示自动提交已打开

设置事务的提交方式

通过set命令设置autocommit全局变量的值，可以打开或关闭事务的自动提交。如下：

set autocommit = 1;表示自动提交
set autocommit = 0;表示手动提交

11.4 事务的相关演示

准备测试表

为了便于演示，我们将MySQL的隔离级别设置成读未提交，也就是把隔离级别设置的比较低，方便看到实验现象。如下：

set global transaction isolation level read uncommitted;

需要注意的是，设置全局隔离级别后当前会话的隔离级别不会改变，只会影响后续与MySQL新建立的连接，因此需要重启终端才能看到会话的隔离级别被成功设置。如下：

创建一个银行用户表，表中包含用户的id、姓名和账户余额。如下：

11.4.1 演示一：事务的常规操作

启动两个终端，左终端使用begin或start transaction命令启动一个事务，右终端查看银行用户表中的信息。如下：

start transaction;

左终端中的事务向表中插入一条记录，由于我们将隔离级别设置成了读未提交，因此在左终端中的事务使用commit提交之前，在右终端中就能查看到事务向表中插入的记录。如下：

insert into account values (1,'张三',100);

左终端中的事务使用savepoint命令创建一个保存点，然后继续向表中插入一条记录，这时在右终端中也能看到新插入的这条记录。如下：

savepoint save1;

左终端中的事务使用rollback命令回滚到保存点，这时右终端在查看表中数据时就看不到刚才插入的第二条记录了。如下

rollback to save1;

左终端中的事务使用rollback命令回滚到事务最开始，这时右终端在查看表中数据时就看不到任何记录了。如下：

rollback

说明一下：

使用begin或start transaction命令，可以启动一个事务。
使用savepoint 保存点命令，可以在事务中创建指定名称的保存点。
使用rollback to 保存点命令，可以让事务回滚到指定保存点。
使用rollback命令，可以直接让事务回滚到最开始。
使用commit命令，可以提交事务，提交事务后就不能回滚了

11.4.2 演示二：原子性

在左终端中启动一个事务，在右终端查看银行用户表中的信息。如下：

左终端中的事务向表中插入一条记录，由于隔离级别是读未提交，因此在右终端中能够查询到插入的这条记录。如下：

如果左终端中的事务在提交之前因为某些原因与MySQL断开连接，那么MySQL会自动让事务回滚到最开始，这时右终端中就看不到之前插入的记录了。如下：

11.4.3 演示三：持久性

在左终端中启动一个事务，在右终端查看银行用户表中的信息。如下：

左终端中的事务向表中插入一条记录，由于隔离级别是读未提交，因此在右终端中能够查询到插入的这条记录。如下：

左终端中的事务在提交后与MySQL断开连接，这时右终端中仍然可以看到之前插入的记录，因为事务提交后数据就被持久化了。如下：

11.4.4 演示四：begin会自动更改提交方式

通过show命令查看autocommit的值为ON，表示事务的提交方式是自动提交，此时银行用户表中有一条记录。如下：

在左终端中启动一个事务并向表中新插入一条记录，由于隔离级别是读未提交，因此在右终端中能够查询到新插入的这条记录。如下：

如果左终端中的事务在提交之前与MySQL断开连接，那么MySQL依旧会自动让事务回滚到最开始，这时右终端中就看不到之前新插入的记录了。如下：

也就是说，使用begin或start transaction命令启动的事务，都必须要使用commit命令手动提交，数据才会被持久化，与是否设置autocommit无关

11.4.5 不启动事务>>单个sql语句与事务关系

实际全局变量autocommit是否被设置影响的是单条SQL语句，InnoDB中的每一条SQL都会默认被封装成事务

autocommit为ON，则单条SQL语句执行后会自动被提交，如果为OFF，则SQL语句执行后需要使用commit进行手动提交

11.5 事务的隔离级别

数据库为了允许事务在执行过程中受到不同程度的干扰，就引入了事务的隔离级别

读未提交(Read Uncommitted)：读未提交是事务的最低隔离级别，几乎没有加锁，虽然效率高，但是问题比较多，所以严重不建议使用，会存在很多并发问题，如脏读、幻读、不可重复读等

读提交(Read Committed)：一个事务只能看到其他已经提交的事务所做的改变，但这种隔离级别存在不可重复读和幻读的问题，也是大多数数据库的默认隔离级别，但它不是MySQL默认的隔离级别

可重复读(Repeatable Read)：这是MySQL默认的隔离级别，该隔离级别确保同一个事务在执行过程中，多次读取操作数据时会看到同样的数据，即解决了不可重复读的问题，但insert数据会存在幻读问题，因为隔离性是通过对数据加锁完成的，而新插入的数据原本是不存在的，因此一般的加锁无法屏蔽这类问题。

串行化： 串行化是事务的最高隔离级别，多个事务同时进行读操作时加的是共享锁，因此可以并发执行读操作，但一旦需要进行写操作，就会进行串行化，效率很低，几乎不会使用

说明一下：

虽然数据库事务的隔离级别有以上四种，但一个稳态的数据库只会选择这其中的一种，作为自己的默认隔离级别。但数据库默认的隔离级别有时可能并不满足上层的业务需求，因此数据库提供了这四种隔离级别，可以让我们自行设置，
隔离级别基本上都是通过加锁的方式实现的，不同的隔离级别对锁的使用是不同的，常见的有表锁、行锁、写锁、间隙锁（GAP）、Next-Key锁（GAP+行锁）等

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（read uncommitted）	√	√	√	不加锁
读已提交（read committed）	X	√	√	不加锁
可重复读（repeatable read）	X	X	√	不加锁
可串行化（serializable）	X	X	X	加锁

√：会发生该问题
X：不会发生该问题

说明一下：

隔离级别越严格，安全性越高，但数据库的并发性能也就越低，在选择隔离级别时往往需要在两者之间找一个平衡点

表中只写出了各种隔离级别下进行读操作时是否需要加锁，因为无论哪种隔离级别，只要需要进行写操作就一定需要加锁

11.5.1 查看与设置隔离级别

查看全局隔离级别

通过select @@global.tx_isolation命令，可以查看全局隔离级别。如下：

select @@global.tx_isolation;

查看会话隔离级别

通过select @@session.tx_isolation命令，可以查看当前会话的隔离级别。如下：

select &&session.tx_isolation;

此外，通过select @@tx_isolation命令，也可以查看当前会话的隔离级别。如下：

select @@tx_isolation;

设置会话隔离级别

通过set session transaction isolation level 隔离级别命令，可以设置当前会话的隔离级别。如下：

set session transaction isolation level seriablizable;

设置全局隔离级别

通过set global transaction isolation level 隔离级别命令，可以设置全局隔离级别

11.7 多版本并发控制(难点)

多版本并发控制（Multi-Version Concurrency Control，MVCC）是一种用来解决读写冲突的无锁并发控制，主要依赖记录中的3个隐藏字段，undo日志和Read View实现

为事务分配单向增长的事务ID，为每个修改保存一个版本，将版本与事务ID相关联，读操作只读该事务开始前的数据库快照

MVCC保证读写并发时，读操作不会阻塞写操作，写操作也不会阻塞读操作，提高了数据库并发读写的性能，同时还可以解决脏读、幻读和不可重复读等事务隔离性问题

数据库的并发场景

读-读并发：不存在任何问题，也不需要并发控制
读-写并发：有线程安全问题，可能会存在事务隔离性问题，可能遇到脏读、幻读、不可重复读
写-写并发：有线程安全问题，可能会存在两类更新丢失问题

11.7.1 记录中的3个隐藏字段

创建一个学生表，表中包含学生的姓名和年龄。如下：

当向表中插入一条记录后，该记录不仅包含name和age字段，还包含三个隐藏字段。如下：

假设插入该记录的事务的事务ID为9，那么该记录的DB_TRX_ID字段填的就是9
因为这是插入的第一条记录，所以隐式主键DB_ROW_ID字段填的就是1
由于这条记录是新插入的，没有历史版本，所以回滚指针DB_ROLL_PTR的值设置为null

11.7.2 undo日志

redo log：重做日志，用于MySQL崩溃后进行数据恢复，保证数据的持久性。
bin log：逻辑日志，用于主从数据备份时进行数据同步，保证数据的一致性
undo log：回滚日志，用于对已经执行的操作进行回滚，保证事务的原子性。

MySQL会为上述三大日志开辟对应的缓冲区，用于存储日志相关的信息，必要时会将缓冲区中的数据刷新到磁盘，主要依赖三大日志中的undo log，记录的历史版本就是存储在undo log对应的缓冲区中的

11.7.3 快照

undo log中的一个个的历史版本就称为一个个的快照

现在有一个事务ID为10的事务，要将刚才插入学生表中的记录的学生姓名改为“李四”：

因为是要进行写操作，所以需要先给该记录加行锁
修改前，先将该行记录拷贝到undo log中，此时undo log中就有了一行副本数据。
然后再将原始记录中的学生姓名改为“李四”，并将该记录的DB_TRX_ID改为10，回滚指针DB_ROLL_PTR设置成undo log中副本数据的地址，从而指向该记录的上一个版本
最后当事务10提交后释放锁，这时最新的记录就是学生姓名为“李四”的那条记录

修改后的示意图如下：

现在又有一个事务ID为11的事务，要将刚才学生表中的那条记录的学生年龄改为38：

因为是要进行写操作，所以需要先给该记录（最新的记录）加行锁。
修改前，先将该行记录拷贝到undo log中，此时undo log中就又有了一行副本数据。
然后再将原始记录中的学生年龄改为38，并将该记录的DB_TRX_ID改为11，回滚指针DB_ROLL_PTR设置成刚才拷贝到undo log中的副本数据的地址，从而指向该记录的上一个版本。
最后当事务11提交后释放锁，这时最新的记录就是学生年龄为38的那条记录。

修改后的示意图如下：

注：快照读：读取历史版本，就叫做快照读

11.7.4 Read View

事务在进行快照读操作时会生成读视图Read View，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃的事务ID

Read View在MySQL源码中就是一个类，本质是用来进行可见性判断的，当事务对某个记录执行快照读的时候，对该记录创建一个Read View，根据这个Read View来判断，当前事务能够看到该记录的哪个版本的数据

12.视图

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据

视图中的数据并不会单独存储在数据库中，其数据来自定义视图时查询所引用的表（基表），在每次引用视图时动态生成

由于视图和基表用的本质是同一份数据，因此对视图的修改会影响到基表，对基表的修改也会影响到视图

12.1 准备测试表

准备测试表

下面用员工表和部门表作为测试表，员工表中的ename代表的是员工的姓名，deptno代表的是员工所在部门的部门号。如下：

部门表中的dname代表的是部门名，deptno代表的是部门的部门号。如下：

12.2 创建视图

当我们要查询每个员工及其对应的部门名称时，需要使用员工表和部门表进行多表查询，并筛选出员工的部门号等于部门的部门号的记录。如下：

如果该查询结果会被频繁用到，那我们就可以给上述查询结果创建视图，创建完毕后通过show命令就能看到这个视图。如下：

create view v_ename_dname as
select ...

并且在数据库对应的目录下，会增加一个对应的xxx.frm文件，但并没有与之对应的xxx.ibd文件，这也证明了视图和基表使用的是同一份数据。如下：

创建视图后就可以直接通过查询视图，来查看每个员工及其对应的部门名称了。如下：

12.3 删除视图

比如将刚才创建的视图删除后，在数据库中就看不到这个视图了。如下：

drop view v_ename_dname;

并且该视图在数据库目录下对应的xxx.frm文件也会被删除。如下：

12.3 视图规则和限制

视图可以和普通表一起使用，比如进行多表查询，内外连接等

与普通表一样，视图的命名也必须是唯一的，不能出现同名视图或表名

创建视图的数目无限制，但要考虑复杂查询创建为视图之后的性能影响

视图不能添加索引，也不能有关联的触发器或者默认值

视图可以提高安全性，在访问视图时必须具有足够的访问权限

13. 用户管理

与Linux操作系统类似，MySQL中也有超级用户和普通用户之分

如果一个用户只需要访问MySQL中的某一个数据库，甚至数据库中的某一个表，那么可以为其创建一个普通用户，并为该用户赋予对应的权限，而不让该用户看到数据库中的其他数据，防止该用户对其他数据进行误操作

13.1 用户信息

MySQL当中默认有一个名为mysql的数据库,其中就有个user表存储用户信息
select user,host,authentication_string from user;

需要注意的是，MySQL中可以存在同名的用户，只要这些同名用户对应的登录主机不同即可，因为user表中的主键是复合主键，由表中的user列和host列共同承担。如下：

13.2 创建用户

create user 'dragon'@'%' identified by 'XXXXXXXXXXXXX'

13.3 连接数据库

mysql -udragon -p
mysql -h49.232.66.206 -udragon -p

13.4 修改密码

用户自己修改自己的密码

set password=password('XXXXXXXXX');

超级用户修改任意用户的密码

set password for 'dragon'@'%' = password('XXXXXXXXXXX')

13.5 删除用户

drop user 'dragon'@'%';

13.6 用户权限

给用户查询某个数据库下所有表的权限

grant select on user_management.* to 'dragon'@'%' identified by 'XXXXXX'

给用户对某个数据库下所有表的所有权限

grant all on user_management.* to 'dragon'@'%';

查看用户的权限

show grants for 'dragon'@'%';

回收用户权限

revoke all on user_management.* from 'dragon'@'%';

说明一下：

回收用户在某一数据库下的权限后，在该用户下一次进入该数据库时才会起作用
如果回收权限时该用户正在使用对应数据库，那么回收权限后该用户仍然拥有对应的权限

14. 使用C语言连接数据库

如果我们使用的是Linux系统的话我们可以直接使用，下面的命令安装MySQL给我们提供的官方库：

sudo yum install mysql-community-devel

查看mysql的头文件：ls /usr/include/mysql/
查看mysql的动静态库：ls /usr/lib64/mysql/

14.1 测试连接

-I(i的大写)：用于指明头文件的搜索路径。
-L(l的大写)：用于指明库文件的搜索路径。
-l(l的小写)：用于指明需要连接库文件路径下的哪一个库。

Makefile编写完毕后，直接通过make命令即可编译代码生成可执行程序。如下：

ldd mysql_connect 查看可执行程序的各种属性信息

无法运行 && 解决方案

生成的可执行程序还不能直接运行，通过ldd命令可以看到，该可执行程序所依赖的mysqlclient库找不到。如下：

gcc/g++编译器默认都是动态链接的，编译代码时默认使用的是动态库，所以生成的可执行程序在运行时需要找到对应的动态库进行链接，可能你的mysqlclient库并不在系统的搜索路径下

需要注意的是，Makefile中的-I，-L和-l这三个选项，只是在编译期间告诉编译器头文件和库文件在哪里，而可执行程序生成后就与编译器无关了

解决该问题通常有三种做法(推荐第三种方法)：

将库文件拷贝到系统默认的库文件搜索路径下/lib64
将库文件所在的目录路径添加到LD_LIBRARY_PATH环境变量中，该环境变量可以用于指定查找动态库时的其他路径
将库文件所在的目录路径保存到以.conf为后缀的配置文件中，然后将该文件拷贝到/etc/ld.so.conf.d/目录下，并使用ldconfig命令对配置文件进行更新，该目录下所有配置文件中的路径也将作为查找动态库时的搜索路径

14.2 连接数据库

创建MySQL对象

在连接数据库之前，需要先创建一个MySQL对象，创建MySQL对象的函数如下：

MYSQL* mysql_init(MYSQL *mysql);

说明一下

该函数用来分配或者初始化一个MySQL对象，用于连接MySQL服务器。
如果传入的参数是NULL，那么mysql_init将自动为你分配一个MySQL对象并返回。
如果传入的参数是一个地址，那么mysql_init将在该地址处帮你完成初始化。

MYSQL对象中包含了各种信息，其类型定义如下：

typedef struct st_mysql {
	NET net;			/* Communication parameters */
    unsigned char	*connector_fd;		/* ConnectorFd for SSL */
    char *host,*user,*passwd,*unix_socket,*server_version,*host_info;
    char *info, *db;
    struct charset_info_st *charset;
    MYSQL_FIELD	*fields;
    MEM_ROOT field_alloc;
    my_ulonglong affected_rows;
    my_ulonglong insert_id;		/* id if insert on table with NEXTNR */
    my_ulonglong extra_info;		/* Not used */
    unsigned long thread_id;		/* Id for connection in server */
    unsigned long packet_length;
    unsigned int port;
    unsigned long client_flag,server_capabilities;
    unsigned int protocol_version;
    unsigned int field_count;
    unsigned int server_status;
    unsigned int server_language;
    unsigned int warning_count;
    struct st_mysql_options options;
    enum mysql_status status;
    my_bool	free_me;		/* If free in mysql_close */
    my_bool	reconnect;		/* set to 1 if automatic reconnect */

    /* session-wide random string */
    char scramble[SCRAMBLE_LENGTH+1];
    my_bool unused1;
    void *unused2, *unused3, *unused4, *unused5;

    LIST *stmts;                     /* list of all statements */
    const struct st_mysql_methods *methods;
    void *thd;
    /*
      Points to boolean flag in MYSQL_RES  or MYSQL_STMT. We set this flag
      from mysql_stmt_close if close had to cancel result set of this object.
    */
    my_bool *unbuffered_fetch_owner;
    /* needed for embedded server - no net buffer to store the 'info' */
    char *info_buffer;
    void *extension;
} MYSQL;

说明一下：

MYSQL对象中的methods变量是一个结构体变量，该变量里面保存着很多函数指针，这些函数指针将会在数据库连接成功以后的各种数据操作中被调用

创建完MySQL对象后就可以连接数据库了，连接数据库的函数如下：

MYSQL* mysql_real_connect(MYSQL *mysql, const char *host,
					const char *user,
					const char *passwd,
					const char *db,
					unsigned int port,
					const char *unix_socket,
					unsigned long clientflag);

参数说明：

mysql：表示在连接数据库前，调用mysql_init函数创建的MySQL对象。
host：表示需要连接的MySQL服务器的IP地址，"127.0.0.1"表示连接本地MySQL服务器。
user：表示连接MySQL服务器时，所使用用户的用户名。
passwd：表示连接MySQL服务器时，所使用用户的密码
db：表示连接MySQL服务器后，需要使用的数据库。
port：表示连接的MySQL服务器，所对应的端口号。
unix_socket：表示连接时应该使用的套接字或命名管道，通常设置为NULL。
clientflag：可以设置为多个标志位的组合，表示允许特定的功能，通常设置为0。

返回值说明：

如果连接数据库成功，则返回一个MySQL对象，该对象与第一个参数的值相同

如果连接数据库失败，则返回NULL

关闭数据库连接

与数据库交互完毕后，需要关闭数据库连接，关闭数据库连接的函数如下：

void mysql_close(MYSQL *sock);

说明一下：

该函数的参数，就是连接数据库前调用mysql_init创建的MySQL对象
如果传入的MySQL对象是mysql_init自动创建的，那么调用mysql_close时就会释放这个对象

连接示例

比如使用如下代码连接我的MySQL服务器：

#include <iostream>
#include <string>
#include <mysql.h>
using namespace std;

const string host = "1.15.227.10";
const string user = "oj_client";
const string passwd = "123456";
const string db = "tmp_db";
const int port = 3306;

int main()
{
    //1、创建MySQL对象
    MYSQL* ms = mysql_init(nullptr);
    //2、连接数据库
    if(mysql_real_connect(ms, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
    {
        cerr<<"数据库连接失败!"<<endl;
        return 1;
    }
    cout<<"数据库连接成功!"<<endl;

    //3、关闭数据库
    mysql_close(ms);
    cout<<"数据库关闭成功!"<<endl;
    return 0;
}

14.3 发送sql请求

发送SQL请求

与数据库建立连接期间，就可以向MySQL服务器下发SQL请求，下发SQL请求的函数如下：

int    mysql_query(MYSQL *mysql, const char *q);

参数说明：

mysql： 表示在连接数据库前，调用mysql_init函数创建的MySQL对象
q：表示向MySQL服务器下发的SQL请求，SQL最后可以不带分号

返回值说明：

返回值为0表示SQL执行成功，否则表示SQL执行失败

设置编码格式

与数据库建立连接期间，就可以向MySQL服务器下发SQL请求，下发SQL请求的函数如下：

int mysql_set_character_set(MYSQL *mysql, const char *csname);

参数说明：

mysql： 表示在连接数据库前，调用mysql_init函数创建的MySQL对象
csname： 表示要设置的编码格式，如"utf8"

返回值说明：

返回值为0表示设置成功，否则表示设置失败

向数据库中插入数据

在调用mysql_query函数时，向MySQL服务器下发一条insert SQL。如下：

#include <iostream>
#include <string>
#include <mysql.h>
using namespace std;

const string host = "1.15.227.10";
const string user = "oj_client";
const string passwd = "123456";
const string db = "tmp_db";
const int port = 3306;

int main()
{
    //1、创建MySQL对象
    MYSQL* ms = mysql_init(nullptr);
    //2、连接数据库
    if(mysql_real_connect(ms, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
    {
        cerr<<"数据库连接失败!"<<endl;
        return 1;
    }
    cout<<"数据库连接成功!"<<endl;
    mysql_set_character_set(ms, "utf8"); //设置编码格式为utf8
    
    //3、向数据库表中插入记录
    std:string sql = "insert into user values (4,'赵六',25)";
    if(mysql_query(ms, sql.c_str()) != 0)
    {
        cout<<"插入数据失败!"<<endl;
        return 2;
    }
    cout<<"插入数据成功!"<<endl;

    //4、关闭数据库
    mysql_close(ms);
    cout<<"数据库关闭成功!"<<endl;
    return 0;
}

使用make命令生成可执行程序，运行后在MySQL中即可看到对应数据被成功插入。如下：

说明一下：

其他操作命令同上，这里就不演示了，std::string sql = 'XXXXXX'

14.4 获取查询结果

获取查询结果

获取查询结果的函数如下：

MYSQL_RES* mysql_store_result(MYSQL *mysql);

说明一下：

该函数会调用指定MySQL对象中对应的函数指针来获取查询结果，并将获取到的查询结果保存到MYSQL_RES变量中进行返回
mysql_store_result() 分配的 MYSQL_RES 结构体的内存并不是通过 malloc() 分配的，而是由 MySQL C API 负责管理的。因此，必须使用 mysql_free_result(MYSQL_RES* res); 进行释放，否则会导致内存泄漏

获取查询结果的行数

获取查询结果的行数的函数如下：

my_ulonglong mysql_num_rows(MYSQL_RES *res);

获取查询结果的列数

获取查询结果的列数的函数如下：

unsigned int mysql_num_fields(MYSQL_RES *res);

获取查询结果的列属性

获取查询结果的列属性的函数如下：

MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES *res);

mysql_fetch_fields函数将会返回多个MYSQL_FIELD对象，每个MYSQL_FIELD对象中保存着对应列的各种列属性，其类型定义如下：

typedef struct st_mysql_field {
	char *name;                 /* Name of column */
	char *org_name;             /* Original column name, if an alias */
	char *table;                /* Table of column if column was a field */
	char *org_table;            /* Org table name, if table was an alias */
	char *db;                   /* Database for table */
	char *catalog;	      /* Catalog for table */
	char *def;                  /* Default value (set by mysql_list_fields) */
	unsigned long length;       /* Width of column (create length) */
	unsigned long max_length;   /* Max width for selected set */
	unsigned int name_length;
	unsigned int org_name_length;
	unsigned int table_length;
	unsigned int org_table_length;
	unsigned int db_length;
	unsigned int catalog_length;
	unsigned int def_length;
	unsigned int flags;         /* Div flags */
	unsigned int decimals;      /* Number of decimals in field */
	unsigned int charsetnr;     /* Character set */
	enum enum_field_types type; /* Type of field. See mysql_com.h for types */
	void *extension;
} MYSQL_FIELD;

获取查询结果中的一行数据

获取查询结果中的一行数据的函数如下：

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

MYSQL_ROW对象中保存着一行数据，这一行数据中可能包含多个字符串，对应就是这行数据中的多个列信息，因此MYSQL_ROW本质就是char**类型，其类型定义如下：

typedef char **MYSQL_ROW;		/* return data as array of strings */

查询示例

比如查询tmp_table表中的数据并进行打印输出。如下：

#include <iostream>
#include <string>
#include <mysql.h>
using namespace std;

const string host = "1.15.227.10";
const string user = "oj_client";
const string passwd = "123456";
const string db = "tmp_db";
const int port = 3306;

int main()
{
    //1、获取MySQL实例（相当于给我们创建了一个MySQL句柄）
    MYSQL* ms = mysql_init(nullptr);
    //2、连接数据库
    if(mysql_real_connect(ms, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
    {
        cerr<<"数据库连接失败!"<<endl;
        return 1;
    }
    cout<<"数据库连接成功!"<<endl;
    mysql_set_character_set(ms, "utf8"); //设置编码格式为utf8
    
    //3、查询数据库表中的记录
    //a、执行查询语句
    std:string sql = "select * from user";
    if(mysql_query(ms, sql.c_str()) != 0)
    {
        cout<<"查询数据失败!"<<endl;
        return 2;
    }
    cout<<"查询数据成功!"<<endl;
    //b、获取查询结果
    MYSQL_RES* res = mysql_store_result(ms);
    int rows = mysql_num_rows(res); //数据的行数
    int cols = mysql_num_fields(res); //数据的列数
    //获取每列的属性并打印列名
    MYSQL_FIELD* fields = mysql_fetch_fields(res);
    for(int i = 0;i < cols;i++)
    {
        cout<<fields[i].name<<"\t";
    }
    cout<<endl;
    for(int i = 0;i < rows;i++)
    {
        //获取一行数据并进行打印
        MYSQL_ROW row = mysql_fetch_row(res);
        for(int j = 0;j < cols;j++)
        {
            cout<<row[j]<<"\t";
        }
        cout<<endl;
    }
    free(res); //释放内存空间
    
    //4、关闭数据库
    mysql_close(ms);
    cout<<"数据库关闭成功!"<<endl;
    return 0;
}

使用make命令生成可执行程序，运行后在即可看到数据的查询结果。如下：

查看全文

http://www.kler.cn/a/525687.html

Linux文件原生操作

deepseek R1的确不错，特别是深度思考模式

二进制安卓清单 binary AndroidManifest - XCTF apk 逆向-2

C++/stack_queue

oracle比较一下统计信息差异吧

【apt源】RK3588 平台ubuntu20.04更换apt源

Vue.js 组合函数（Composables）

算法随笔_32: 移掉k位数字

供应链系统设计-供应链中台系统设计（十二）- 清结算中心设计篇（一）

C语言中的存储类

安卓(android)音乐播放器【Android移动开发基础案例教程（第2版）黑马程序员】

VLLM性能调优

WPS怎么使用latex公式？

Transformer+vit原理分析

Linux多路转接poll

解读Linux 6.x版本内核的sys目录作用

SQL注入漏洞之错误类型注入爆破表字段列名称以及mysql版本以及Limit使用方式解释以及靶场相关联系

「全网最细 + 实战源码案例」设计模式——桥接模式

7.抽象工厂(Abstract Factory)

P1002 [NOIP2002 普及组] 过河卒

Leetcode 131 分割回文串(纯DFS)

EtherCAT主站IGH-- 23 -- IGH之fsm_slave.h/c文件解析

在Ubuntu下编译VLC

【AI非常道】二零二五年一月（二），AI非常道

正态分布与柯西分布的线性组合与副本随机变量同分布

Spring Boot + Facade Pattern : 通过统一接口简化多模块业务

0.MySQL 在 Centos 7环境安装

0.1 卸载不要的环境

0.2 删除多余的安装包

0.3 获取mysql官⽅yum源

0.4 安装mysql yum 源，对⽐前后yum源

0.5 查看是否能正常工作

0.6 安装mysql服务

0.7 数据存储位置 查看配置⽂件

0.8 启动服务

0.9 配置

1. 数据库基础

1.1 数据库的存储介质

1.2 常见主流数据库

1.3 基本使用

1.3.1 连接服务器

1.3.2 服务器管理

1.3.3 数据库服务器&&数据库&&表关系

1.4 数据库重要路径

1.5 数据逻辑存储

1.6 mysql命令本质

1.7 SQL分类

1.8 存储引擎

2. 库操作

2.1 创建数据库

2.2 字符集和校验规则

2.2.1 校验规则对数据库的影响

2.3 查看数据库

2.4 显示创建语句

2.5 修改数据库

2.6 删除数据库

2.7 数据库备份

2.8 数据库恢复

2.9 数据库指定表的备份

2.10 数据库指定表的恢复

2.11 查看mysql连接情况

3.表操作

3.1 查看引擎

3.2 创建表

3.3 查看表结构

3.4 查看创建表信息

3.5 修改表的内容

4.数据类型

4.1 作用

4.2 数值类型-tinyint类型

4.2.1 无符号tinyint范围测试

4.3 数值类型-bit类型

4.3.1 bit类型的显示方式

4.3.2 bit类型的范围测试

4.4 数值类型-float类型

4.4.1 无符号float范围测试

4. 数值类型-decimal类型

4.1 精度测试

4.5 字符串类型-char类型

4.6 字符串类型-varchar类型

4.6.1 archar类型可指定的字符个数上限

4.6.2 char和varchar的区别

4.6.3 char和varchar的选择

4.7 时间日期类型-date/datetime/timestamp

4.7.1 timestamp类型使用案例

4.8 enum和set类型

4.8.1 enum和set查找

4.8.2 find_in_set函数

5. 表的约束

5.1 null

5.2 default

5.3 comment

5.4 zerofill

5.5 auto_increment

5.6 primary key

5.6.1 复合主键

5.7 unqiue key

5.8 foregin key

6. 表的增删查改

6.1 增加

6.1.1 单行数据+全列插入

6.1.2 多行数据+指定列插入

6.1.3 更新插入

6.1.4 强行插入(替换数据)

6.2 删除

6.2.1 删除整表

0.7 数据存储位置查看配置⽂件