C++ (week8)：数据库 MySQL

一、数据库简介

1.数据库

1.为什么必要数据库

2.数据库的分类
(1)关系型数据库：MySQL、Oracle
(2)非关系型数据库：Redis、MongoDB

3.数据库排名
国际：https://db-engines.com/en/ranking
国内：https://www.modb.pro/dbRank

4.数据库的汗青

5.关系模子

2.MySQL

(1)数据库的布局

(2)MySQL的三种使用方式

我们可以使用三种方式来与MySQL服务器进行交互：
①直接通过命令行方式
②图形化界面工具：Navicat
③C语言API方式：开辟程序时

(3)Navicat Premium

1.图形化界面的客户端程序(数据库管理工具)：Navicat Premium

2.Navicat连接Ubuntu的mysql数据库的方法：
①vim /etc/mysql/mysql.conf.d/mysqld.cnf，将这两行注释

②让更改的设置生效：sudo service mysql restart
③使用Navicat远程连接Ubuntu的mysql数据库

3.升级mycli
vim ~/./myclirc
(1)增加 vim 方式

(2)修改单双行颜色

修改颜色网址：https://flatuicolors.com/palette/nl
(3)改为多行

二、SQL

1.SQL (Structured Query Language)，即布局化查询语言

1.SQL语句分类：
(1)DDL (数据界说语言)
(2)DML (数据控制语言)
(3)DQL (数据查询语言)
2.SQL语句中的关键字，不区分巨细写
数据库名、表名、字段(列)名，严格区分巨细写
3.SQL的注释语句：
单行注释： --空格 、#
多行注释：/* */
4.进入数据库常用命令

1. mycli -uroot
2. show databases;
4. use DBName;
5. show tables;
6. SELECT * FROM TableName;
7. ...
8. exit  /   quit

复制代码

5.检察存储引擎

1. show engines;

复制代码

2.数据界说语言 DDL (Data Definition Language) ，创建、修改、删除数据库、表布局

(1)操作数据库

1.登录/启动数据库服务器：

1. mysql -u root -p          # mysql -u 用户名 -p

复制代码

1. mycli -u root

复制代码

密码：123456
2.退出/关闭数据库服务器

1. quit 或 exit

复制代码

3.检察数据库

1. show databases;

复制代码

4.创建数据库

1. CREATE DATABASE DbName;
2. //设置数据库字符集、字符校对集
3. CREATE DATABASE DBName DEFAULT character SET utf8mb4 COLLATE utf8mb64_0900_ai_ci;

复制代码

5.使用数据库

1. use  DbName;

复制代码

6.字符校对集

1. show character set;  //查看数据库支持的字符集
2. show collation;          //查看相应字符集的校对规则

复制代码

7.修改数据库

1. Alter DATABASE DbName default character set CharacterSetName;

复制代码

只能修改数据的默认字符集和校对集(排序方式)
不能修改数据库的名字。只能重建新数据库，再把数据拷贝过来。
8.删除数据库

1. DROP DATABASE DbName;

复制代码

9.数据库改密码
(1)Navicat

(2)命令行

(2)操作表

①创建表：CREATE

1.创建表

1. CREATE TABLE TableName (
2.         field datatype [constrain],
3.         列名 类型 [约束],
4.         ...
5. );

复制代码

1. CREATE TABLE student (
2.         id INT PRIMARY KEY,
3.         name VARCHAR(20),
4.         birth DATE,
5.         chinese FLOAT
6. );

复制代码

2.复制表
(1)复制表布局 (复制属性，不复制数据)

1. CREATE TABLE newTName LIKE oldTName;

复制代码

(2)复制表布局和数据

1. CREATE TABLE newTName SELECT * FROM oldTName;

复制代码

②检察表：SHOW

1.检察表

1. SHOW TABLES;

复制代码

2.检察表布局 (检察表的属性组成)
(1)desc

1. desc tName; //

复制代码

(2)show create table 【信息比desc更全面】

1. show create table tName;
2. show create table student;

复制代码

③修改表：ALTER

ALTER：用于修改已有的数据库对象，例如添加或删除列，修改列的数据类型等。
1.修改表布局：ALTER TABLE tbName
①添加字段：add

1. ALTER TABLE tName ADD field datatype;
2. ALTER TABLE students ADD email VARCHAR(100);

复制代码

②修改字段名：change

1. ALTER TABLE tName CHANGE oldfield  newfield datatype;

复制代码

③修改字段名(属性)的数据类型和束缚：modify

1. ALTER TABLE tName MODIFY field newDataType;

复制代码

④删除表的一个字段：drop

1. ALTER TABLE tName DROP field;

复制代码

④删除表：DROP

1.删除一张表

1. DROP TABLE TableName;

复制代码

⑤TRUNCATE

TRUNCATE：用于清空表中的所有数据，但不删除表布局。

1. TRUNCATE TABLE students;

复制代码

3.数据控制语言 DML (Data Manipulation Language)，表已经存在，对表中的数据进行添加、修改、删除的操作

(1)添加数据：INSERT

(1)对指定列进行数据的添加 (一行)

1. INSERT INTO TName(field1, field2,...)
2. VALUES (fiedl1Value, field2Value);

复制代码

1. INSERT INTO student (id, name, birth, Chinese, English, Math)
2. VALUES (1, 'Ed', '2024-06-08', 100, 100, 100);

复制代码

(2)对指定列进行数据的添加 (多行)

1. INSERT INTO TName(field1, field2,...)
2. VALUES
3. (fiedl1Value, field2Value),
4. (filed1Value, field2Value),...;

复制代码

(3)对所有列都进行数据的添加 （一行）

1. INSERT INTO TName
2. VALUES (field1Value, field2Value, ...);

复制代码

(4)对所有列都进行数据的添加 （多行）

1. INSERT INTO TName
2. VALUES
3. (field1Value, field2Value...),
4. (filed1Value, field2Value...),
5. ...;

复制代码

(2)修改数据：UPDATE

1. UPDATE tbName SET field1=value1, filed2=value2
2. [WHERE ...];  //加where子句,针对某些行。不加where，针对所有行

复制代码

举例：

1. UPDATE Student SET Math=92
2. WHERE id = 1;

复制代码

1. update t_user set balance=balance+100 where id=1;

复制代码

(3)删除数据：DELETE、truncate

1. DELETE FROM tableName;                  #删除表中所有数据
2. DELETE FROM tableName [Where condition]; //加where子句,针对某些行。不加where，针对所有行
3. TRUNCATE TABLE tableName;                #删除表中所有数据

复制代码

4.数据查询语言 DQL (Data Query Language)

(1)简朴查询

1.查询子句：SELECT

1. SELECT * FROM TableName;   //查询所有列

复制代码

1. SELECT filed1,filed2 FROM TableName;  //查询某些列

复制代码

在SELECT子句中还可以使用表达式，加减乘除等

2.去掉重复的数据：DISTINCT

1. SELECT DISTINCT filed1,filed2 FROM TableName;  //DISTINCT只能放在第一个字段之前

复制代码

3.条件筛选：WHERE 子句
(1)比较运算符 != <>
(2)范围查找：BETWEEN .. AND ...
(3)含糊查询：LIKE
  通配符:
①_ : 代表恣意一个字符
②%： 代表的是0个或者多个字符

1. SELECT * FROM Student WHERE name LIKE '李%';

复制代码

4.排序：ORDER BY 子句
升序排序ASC(默认)，降序DESC

1. SELECT * FROM Student ORDER BY English;              //不写默认升序
2. SELECT * FROM Student ORDER BY English DESC;  //降序

复制代码

接多个字段:先按第一个字段排完序,再按第二个字段排序

1. SELECT * FROM Student
2. ORDER BY English, Math, Chinese; //第一个字段是主排序字段,后续是副排序字段

复制代码

5.分页查询： LIMIT
(1)获取前m条数据

1. LIMIT m;

复制代码

1. SELECT * FROM Student LIMIT 3;  //获取结果的前3行

复制代码

(2)跳过n条，再表现m条数据

1. LIMIT m,n;   //跳过前m条，从这开始显示n行

复制代码

1. LIMIT m OFFSET n;    //每页m条,跳过n条。n为 (页码-1)*每页条数

复制代码

(2)复杂查询

①连接查询

1. select * from left table join_type right table [on ...]

复制代码

<1>交织连接 (笛卡尔积) cross join ：全连接

AS：给表取别名 [AS可省略]

<2> 内连接 inner join ：求交集

INNER JOIN可简写为JOIN，默认内连接。
内连接必须跟ON子句，不然效果等同于交织连接。

1. SELECT columns
2. FROM table1
3. INNER JOIN table2
4. ON table1.column = table2.column;

复制代码

举例：

1. SELECT * FROM student
2. INNER JOIN t_order
3. ON student.id = t_order.s_d;

复制代码

1. SELECT * FROM Student
2. INNER JOIN Score
3. ON Student.s_id = Score.s_id;

复制代码

3.外连接 [必须要跟on子句，否则会报错]
<3>左外连接 left [outter] join： 以左表为主


<4>右外连接 right [outter] join：以右表为主

②嵌套查询 (子查询)

嵌套查询也叫子查询，是指在where子句 或 from子句 中又嵌入select查询语句。 (先执行子查询，再执行外层查询)
1.where子句的子查询

1. SELECT * FROM torder WHERE s_id IN (SELECT id FROM student WHERE chinese>=80);
2. SELECT * FROM torder WHERE s_id NOT IN (SELECT id FROM student WHERE chinese>=80);

复制代码

2.from子句的子查询
   注意：派生表必须有别名
  

③联合查询：UNION

将左查询和右查询进行并集的操作，去除重复的行。
左查询和右查询的属性列要相同。
关键字UNION
联合查询能够归并两条查询语句的查询效果，去掉此中的重复数据行，
然后返回没有重复数据行的查询效果。联合查询使用union关键字

④报表查询：GRUOP BY

<1>分组查询

1.根据某一个字段进行分组 (常与统计函数联合使用)
2.对分组查询之后的效果进行筛选：Having子句

<2>聚合查询、统计函数

聚合函数如AVG必须在GROUP BY之后使用，而且应该在HAVING子句中使用，而不是在WHERE子句中。

1. SELECT s_id
2. FROM Score
3. GROUP BY s_id
4. HAVING AVG(s_score) > 90;

复制代码

---

举例：查所有学生的选课数

1. SELECT s_id, count(*) AS num_courses
2. FROM Score
3. GROUP BY s_id;

复制代码

---

5.MySQL数据类型

<null> 表现空值

6.数据完整性

(1)实体完整性：针对行

1.实体完整性：表中的每一行数据都是唯一的，不允许重复。
所以要设置主键，每张表都要用一个整型数据表现主键。

2.主键 PRIMARY KEY
主键不允许重复，唯一，非空

1. //创建表时设置主键
2. CREATE TABLE student (
3.         id int PRIMARY KEY,        //设置主键
4.         name varchar(20),
5.         birth date,
6.         chinese float
7. );
9. //表已经存在,设置主键
10. (1) ALTER TABLE tbName ADD PRIMARY KEY(id);  //推荐这种方法
11. (2) ALTER TABLE tbName MODIFY id INT PRIMARY KEY;

复制代码

3.自增：AUTO_INCREMENT。 注意，每张表只能有一个列自增 (须为整型数据)，一样寻常设置为主键自增。

1. ALTER TABLE tbName MODIFY id INT AUTO_INCREMENT;

复制代码

4.主键的删除：
(1)先删除自增束缚

1. ALTER TABLE tbName MODIFY id INT;

复制代码

(2)再删除主键

1. ALTER TABLE tbName DROP PRIMARY KEY;

复制代码

5.联合主键
多个 字段/属性/列名 联合为一个主键

(2)域完整性：针对属性列

1.非空束缚 NOT NULL
2.默认值束缚 DEFAULT
3.唯一束缚 UNIQUE (不允许重复，但允许为空)

1. UNIQUE KEY (name,birht);   //联合多个字段

复制代码

(3)参照完整性

1.外键束缚 FOREIGN KEY
在某一张表中的外键，必须参考另一张表中的主键。

1. //表不存在的情况下
2. CREATE TABLE tbName(
3. ...
4. FOREIGN KEY(field) REFERENCES othertbName (field1)
5. );
7. //表已经存在的情况下
8. ALTER TABLE tbName ADD constrain fk1
9. FOREIGN KEY(field) REFERENCES othertbName (field1);

复制代码

7.数据库的备份与恢复

(1)备份

1. mysqldump -uroot -p dbName>dbName.sql
2. mysqldump -u root -p 58th>58th.sql

复制代码

(2)恢复 (复制)

1.先要在mysql服务器上创建一个空的数据库 58th
2.有两种恢复方式：
(1)不消连接mysql服务器，直接使用命令行执行恢复操作

1. mysql -uroot -p dbName<dbName.sql

复制代码

1. mysql -u root -p 58th<57th.sql

复制代码

(2)先连接mysql服务器

1. mysql > use dbName;
2. mysql > source dbName.sql;

复制代码

1. mysql > use 57th;
2. mysql > source 57th.sql;

复制代码

8.API和库：C语言

0.头文件

1. #include <mysql/mysql.h>

复制代码

1. /usr/include/mysql/mysql.h

复制代码

1.相干布局体：
MYSQL：代表一个MSQL连接
MYSQL_RES：代表效果集
MYSQL_ROW：代表效果会合的一行数据
MYSQL_FILEF：代表列(字段/属性)

2.初始化

1. MYSQL* mysql_init(MYSQL* mysql);

复制代码

1. //初始化MYSQL连接
2. MYSQL conn;
3. MYSQL* pconn = mysql_init(&conn);

复制代码

3.创建连接：连接mysql服务器 (与mysql服务器创建连接)
mysql_real_connect 是 MySQL C API 提供的一个函数，用于创建与 MySQL 数据库服务器的连接。它是 mysql_init 初始化后，用来现实连接到数据库服务器的函数。

1. #include <mysql/mysql.h>
2. 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 client_flag);

复制代码

1. //MYSQL服务器建立连接
2. pconn = mysql_real_connect(&conn, "localhost", "root", "1234", "cpp58", 0, NULL,0);
3. if(pconn == NULL){
4.         printf("%s\n", mysql_error(&conn));
5.         return EXIT_FAILURE;
6. }

复制代码

4.进行查询操作

1. int mysql_query()

复制代码

1. int mysql_real_query()

复制代码

  初始化和创建连接可以封装在一起，但查询不可以。要单独查询。否则每次查询前都会重新创建一遍连接，效率低。
封装在一起，只必要传出一个MYSQL*

  

5.获取查询效果
(1)mysql_store_result

1. MYSQL_RES* mysql_store_result(MYSQL* mysql)

复制代码

一次性将查询的效果全部读取到客户端。针对于数据量比较小的情况。

(2)mysql_use_result

1. MYSQL_RES* mysql_use_result(MYSQL* mysql)

复制代码

mysql_use_result()并不会真正获取数据，只是初始化效果集。
当使用了mysql_use_result函数之后，每一次调用mysql_fetch_row()
才真正传输数据。
每调用一次mysql_fetch_row获取一行数据。

(3)获取属性列的信息

1. mysql_fetch_field()
2. mysql_fetch_fields()

复制代码

(4)获取每一行的数据信息

1. mysql_fetch_row()

复制代码

(5)释放效果集

1. mysql_free_result()

复制代码

(6)关闭连接

1. mysql_close()

复制代码

(7)Makefile更新

1. #Makefile
2. SRCS:=$(wildcard *.c)
3. OBJS:=$(patsubst %.c, %, $(SRCS))
4. CC:=gcc
5. LIBS:=-lmysqlclient
6. FLAGS:=-g
8. ALL:$(OBJS)
10. %:%.c
11.         $(CC)  $^ -o $@ $(FLAGS) $(LIBS)
13. clean:
14.         rm -rf $(OBJS)

复制代码

8.示例代码
(1)插入

1. //insert.c#include <func.h>#include <mysql/mysql.h>
2. #include <stdlib.h>int main(){    //初始化MYSQL连接    MYSQL conn;    MYSQL* pconn = mysql_init(&conn);    //与MySQL服务器创建连接    pconn = mysql_real_connect(&conn, "localhost", "root", "1234", "cpp58", 0, NULL, 0);    if(pconn == NULL) {        printf("%s\n", mysql_error(&conn));        return EXIT_FAILURE;    }    //进行查询操作    //const char * query = "INSERT INTO torder VALUES(5, '平底锅', 7)";    const char * query = "INSERT INTO Student VALUES(6, 'Kris', '2005-01-01',90,100,80)";    int ret = mysql_query(pconn, query);    if(ret !=0) {        printf("(%d, %s)\n", mysql_errno(pconn), mysql_error(pconn));        return EXIT_FAILURE;    } else {        printf("QUERY OK, %ld row afftected.\n",               mysql_affected_rows(pconn));    }    mysql_close(pconn);    return 0;}

复制代码

  若报错：

则必要安装mysql客户端

  
9.SQL注入攻击、预处理语句

(1)SQL注入题目

SQL注入题目：用户在使用的过程中，拼凑一些恶意的SQL语句，导致数据库发生了安全性的事件。

1.恶意的SQL语句，可能造成数据库信息泄露

(2)预处理SQL语句

1.预处理SQL语句 (Prepared SQL Statements)：
①防止SQL注入攻击
②模板化SQL语句，进步查询效率

2.什么是预处理SQL语句？
   1.预处理SQL（Prepared SQL Statement）是一种在数据库操作中进步效率和安全性的方法。它通过将SQL语句的编译和执行分离，从而淘汰每次执行SQL时的编译开销，并防止SQL注入攻击。
2.绝大多数情况下，对于某需求或某一条 SQL 语句可能会被反复调用执行，或者每次执行的时间只有个别的值差别：比如 select 的 where 子句值差别，update 的 set 子句值差别，insert 的 values 值差别
3.预处理语句就是将此类 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者参数化，一样寻常称这类语句叫Prepared Statements。
4.预处理语句的优势在于归纳为：一次编译、多次运行，省去相识析优化等过程；而且预处理语句能防止 SQL 注入。
  
3.使用步骤：
①初始化并连接数据库
②创建预处理语句
③绑定参数
④设置第一个参数、设置第二个参数、…
⑤执行预处理语句
⑥处理效果集
⑦处理每一行效果
⑧清理资源

④语法形式 (三部曲)

1. -- 预处理SQL语句的三部曲操作:
2. PREPARE  FROM  
3. EXECUTE  USING
4. DEALLOCATE PREPARE

复制代码

例1：

1. PREPARE stmt1 FROM 'SELECT * FROM Student LIMIT ? OFFSET ?';  # ?是占位符
2. EXECUTE stmt1 USING @var_per_page, @var_offset;
3. DEALLOCATE PREPARE stmmt1;

复制代码

例2：

1. -- 设置变量
2. SET @var_lower = 10;
3. SET @var_upper = 20;
4. -- 预处理SQL语句 三部曲
5. PREPARE stmt FROM 'SELECT * FROM employee WHERE id >= ? and id < ?';-- 准备预处理语句
6. EXECUTE stmt USING @var_lower, @var_upper;        -- 执行预处理语句
7. DEALLOCATE PREPARE stmt;        -- 释放预处理语句

复制代码

⑤变量

<1>体系变量

由体系界说，不是用户界说，属于服务器层面
①全局变量：必要添加 GLOBAL关键字
②会话变量：必要添加 SESSION关键字。不写，默认会话级别。

<2>用户自界说变量

①界说：由用户自己界说的变量，而不是体系界说的
②作用域：生存期是当前会话(连接)，作用域同于会话变量应用在任何地方
③声明并初始化：

1. SET @var_name=value;
2. SET @var_name:=value;
3. SELECT @var_name:=value;

复制代码

④检察变量的值

1. SELECT @var_name;

复制代码

用自界说变量，设置排名 (MySQL逐行查询时，查询每行都会更新变量的值)

   注意，LIMIT子句中，无法直接使用变量，只能使用常量
假如要用，必须用预处理SQL

  
<3>局部变量

见下文DECLARE

(3)预处理SQL的C语言 数据布局

1.MYSQL_STMT 布局体
MYSQL_STMT 是 MySQL C API 中用于准备语句（prepared statements）的句柄。准备语句是一种可以在数据库中重复执行的 SQL 语句。它们允许你将 SQL 语句与数据分开，防止 SQL 注入攻击并进步查询的效率和性能。
(1)初始化准备语句：使用 mysql_stmt_init 初始化一个 MYSQL_STMT 布局体。
(2)准备 SQL 语句：使用 mysql_stmt_prepare 准备 SQL 语句。
(3)绑定参数：
①使用 mysql_stmt_bind_param 绑定输入参数。
②使用 mysql_stmt_bind_result 绑定输出参数（假如有）。
(4)执行语句：使用 mysql_stmt_execute 执行准备好的 SQL 语句。
(5)处理效果集：使用 mysql_stmt_fetch 获取效果会合的数据（假如有）。
(6)释放资源：使用 mysql_stmt_close 关闭准备语句并释放相干资源。

2.MYSQL_BIND 布局体

(4)预处理SQL的C语言 API

查手册
1.mysql_stmt_init()

2.mysql_stmt_prepare()

3.mysql_stmt_param_count()

4.mysql_stmt_bind_param()：绑定

①设置参数
②绑定参数
③传递参数

5.mysql_stmt_error()

mysql_stmt_errno()

6.mysql_stmt_execute()

7.mysql_stmt_result_metadata()：获取元数据

8.mysql_fetch_field()：获取每个字段的信息

9.mysql_stmt_fetch()：获取现实数据

10.mysql_stmt_bind_result()

   mysql_stmt_bind_result 的作用是将查询效果绑定到指定的变量中，这样当你执行 mysql_stmt_fetch 时，MySQL C API 就可以将效果会合的数据填充到这些变量中。这种方式使得效果处理更加灵活和高效，特别是在处理多列数据时。
    什么不直接使用字符串输出效果？这是因为 mysql_stmt_fetch 是一个低级别的函数，它直接将数据读取到你预先绑定的变量中。这种计划有几个利益：
    ①类型安全：你可以将效果绑定到差别类型的变量中（如 int, float, char 等），而不必要手动转换数据类型。
②性能优化：绑定变量后，数据可以直接填充到这些变量中，避免了不必要的拷贝和转换。
③灵活性：你可以根据必要绑定差别的变量，处理差别的数据类型和效果集格式。
直接用字符串输出效果是可以的，但那样就失去了类型安全和性能优化的优势。
  
11.mysql_stmt_store_result()

github代码：https://github.com/WangEdward1027/MySQL/tree/main



10.存储过程

1.使用存储过程的利益：存储过程是预编译的，因此执行速度更快

2.特点：存储过程可以封装一组SQL语句，看起来就像函数调用

• MySQL中的存储过程是一组预编译的SQL语句，可以像函数一样在数据库中进行存储和调用。
  
• 它们允许在数据库中界说一系列操作，然后通过简朴的调用来执行这些操作，而不必每次都重新编写相同的SQL代码。
  
• 存储过程通常用于实现复杂的业务逻辑或执行频仍的数据库操作。
  

语法格式：

举例：

1. DELIMITER  //
3. CREATE PROCEDURE GetEmployee(IN employee_id INT)
4. BEGIN
5.         SELECT * FROM employees WHERE id = employee_id;
6. END        //
8. DELIMITER ;

复制代码

2.用BEGIN END 封装

1. BEGIN
2.         SELECT * FROM employees WHERE id = employee_id;
3. END        //

复制代码

3.使用 CALL调用存储过程

4.变量的声明：DECLARE
①局部变量的作用范围在它被声明的BEGIN … END块内
②变量的更新：SET

1. -- 测试用例1
2. DROP PROCEDURE test1;
3. CREATE PROCEDURE test1()
4. BEGIN
5. -- 在存储过程内部可以定义局部变量
6.     DECLARE x INT;
7.     DECLARE y INT;
8.     SET x = 1;
9.     SET y = 2;
10.     SELECT x + y;
11. END;
12. CALL test1();

复制代码

优化：

1. -- 删除存储过程，如果它已经存在
2. DROP PROCEDURE IF EXISTS test1;
4. -- 改变结束符为 //
5. DELIMITER //
7. -- 创建存储过程
8. CREATE PROCEDURE test1()
9. BEGIN
10.     -- 在存储过程内部可以定义局部变量
11.     DECLARE x INT;
12.     DECLARE y INT;
13.     SET x = 1;
14.     SET y = 2;
15.     SELECT x + y;
16. END //
18. -- 改变结束符回到默认的 ;
19. DELIMITER ;
21. -- 调用存储过程
22. CALL test1();

复制代码

5.删除存储过程：DROP

6.流控制语句
在MySQL中，流控制语句用于控制程序的执行流程，包括条件判断、循环和跳转。
主要的流控制语句包括：
(1)IF-THEN-ELSE语句：用于基于条件执行差别的代码块

1. IF condition1 THEN
2.         statement1;
3. ELSEIF condition2 THEN
4.         statement2;
5. ELSE
6.         statement3;
7. END IF;

复制代码

(2)CASE语句

1. CASE expression
2.         WHEN value1 THEN statement;
3.         WHEN value2 THEN statement;
4.         ...
5.         ELSE statement;
6. END CASE;

复制代码

(3)WHILE循环：当指定条件为真时，重复执行一组语句

1. WHILE condition DO
2.         statement;
3. END WHILE;

复制代码

(4)REPEAT循环：先执行一组语句，然后重复执行，直到指定条件为真

1. REPEAT
2.         statement;
3. UNTIL condition END REPEAT;

复制代码

(5)LOOP循环：无限循环，直到碰到LEAVE语句跳出

1. LOOP
2.         statement
3.         IF condition THEN
4.                 LEAVE;
6.         END IF;
7. END LOOP;

复制代码

(6)LEAVE语句

1. LEAVE;

复制代码

其根本语法如下：
7.IF函数
IF函数是一个MySQL内置函数，它在条件为真时返回一个值，否则返回另一个值。(类似三目运算符)
其根本语法如下：

1. IF(condition), value_if_true, value_if_false)

复制代码

1. SELECT IF(1 < 5 ,'ABC', 'abc');

复制代码

8.CONCAT()：字符串拼接函数，可以拼多个

1. SET @i := 10;
2. SELECT CONCAT(@i, 'abc', 'ABC'); //得到10abcABC

复制代码

---

例1：大量的批处理操作
插入一万万条员工数据：[创建一张员工表，employee(id, name, email, salary, sex, birth), 往此中添加1万万条数据。请用尽可能快的方法进行插入。(提示：使用存储过程)]
(1)创建员工表

1. CREATE TABLE employee(
2. -> id INT PRIMARY KEY,
3. -> name VARCHAR(20),
4. -> email VARCHAR(20),
5. -> salary FLOAT,
6. -> sex VARCHAR(10),
7. -> birth DATE-> );

复制代码

(2)使用存储过程

1. -- 测试用例4
2. DROP PROCEDURE test4;
3. CREATE PROCEDURE test4(IN num INT)
4. BEGIN
5.         DECLARE x INT;
6.         SET x = 0;
7.         WHILE x < num DO
8.                         INSERT INTO employee(id, name,email,salary,sex,birth)
9.                         VALUES(x+1, CONCAT('Edward',x+1), CONCAT('123',x+1,'@qq.com'),10000+1000*(x+1), 'Male',CONCAT('200',x%10,'-01-01'));
10.                         SET x = x + 1;
11.         END WHILE;
12.         SELECT * FROM employee WHERE id <= 10000;
13. END;
15. CALL test4(10000);

复制代码

耗时16000秒

(3)优化：使用 存储过程 + 事务 (隐式事务改为显式事务，进步10倍插入速度)

1. DROP PROCEDURE test1;
2. CREATE PROCEDURE test1(IN num INT)
3. BEGIN
4.         DECLARE x INT;
5.         SET x = 0;
6.         START TRANSACTION;        -- 事务
7.         WHILE x < num DO
8.                         INSERT INTO employee(id, name,email,salary,sex,birth)
9.                         VALUES(x+1, CONCAT('Edward',x+1), CONCAT('123',x+1,'@qq.com'),10000+1000*(x+1), 'Male',CONCAT('200',x%10,'-01-01'));
10.                         SET x = x + 1;
11.         END WHILE;
12.         COMMIT;                                -- 提交
13.         SELECT * FROM employee WHERE id <= 10000000;
14. END;
16. CALL test1(10000000);

复制代码

耗时1976.264秒

例2：按各科成绩进行排序，并表现排名

1. -- 按各科成绩进行排序，并显示排名
2. SET @rownum := 0;
3. SELECT s_id, s_score
4. ,@rownum := IF(@c_id = c_id, @rownum := @rownum+1, 1) AS rank_
5. ,@c_id := c_id AS c_id
6. FROM Score
7. ORDER BY c_id ASC, s_score DESC;

复制代码

例3：case when then

---

三、MySQL优化

1.体系架构

(1)网络接入层：MySQL客户端各种API，C、C++
(2)服务核心层：连接池、SQL Interface、解析器、优化器、缓存(缓存的数据+索引)、管理服务(备份、恢复、主从复制、安全设置)
(3)存储引擎层：插件式管理 (服务层和存储引擎层 为 MySQL服务器)
(4)文件体系层

2.SQL语句的执行流程

MySQL服务器执行客户端发送过来的一个SQL语句的整体流程，一共分为6个步骤：
(1)连接：客户端通过Connectors与MySQL服务器进行交互，向MySQL服务器发送一条查询哀求。
(2)缓存：服务器首先检查查询缓存，假如命中缓存，则立即返回存储在此中的效果；否则进入下一个步骤。(MySQL8.0取消了查询缓存，查询缓存可以使用非关系型数据库来更好地实现，比如用中间件Redis代替)
(3)解析：服务器进行SQL解析（词法分析、语法分析）、预处理。
(4)优化：由优化器生成对应的执行计划。
(5)执行：根据执行计划，调用存储引擎的API来执行查询。
(6)效果：将效果返回给客户端，同时对效果进行缓存。

3.物理布局

MySQL物理布局主要包括
(1)安装目次
(2)数据目次
(3)设置文件
(4)日志文件

(1)安装目次

mysqld是服务器历程，d是守护历程daemon

(2)数据目次

(3)设置文件
①设置文件中存储的是程序的输入信息。
②使用设置文件的利益：后续必要改动时，只必要改动设置文件，不必要对可执行程序重新进行编译
③客户端的设置文件

④服务器的设置文件

(4)日志文件

4.性能优化的方向

1.优化的四个维度：
①SQL及索引
②数据库表布局
③体系设置
④硬件

2.数据库层面的优化：
①存储引擎
②事务和并发控制
③索引
④慢查询
⑤执行计划

5.存储引擎：InnoDB、MyISAM、MEMORY

1.MySQL的存储引擎是负责数据存储和检索的核心组件。
2.存储引擎要办理的题目如下：
(1)如何存储数据？
(2)如何为存储的数据创建索引？
(3)如何查询、更新数据？
3.MySQL支持的存储引擎，常见的有以下三种
(1)InnoDB (默认存储引擎，底层是B+树)
(2)MyISAM
(3)MEMORY

1. show engines;

复制代码

4.存储引擎功能

5.InnoDB
支持事务、支持行级锁、支持外键、支持各种索引。底层是B+树。

实用场景

InnoDB在磁盘中的情况
MySQL8.0后的物理存储布局：表布局、数据、索引 用一个.ibd文件 存放

6.MyISAM
不支持事务、不支持行级锁，仅支持表锁。支持全文索引

实用场景：管理服务器日志数据。读需求为主的场景。

MyISAM存储引擎在磁盘的情况：表布局、数据、索引 分为三个文件存放

7.MEMORY
①MEMORY存储引擎将数据存储到内存中。一旦服务器出现故障，数据将全部丢失。
②不支持TEXT和BLOB类型
③默认使用哈希索引
④锁粒度为表级锁

实用场景：实用于必要高速访问临时数据的场景，读写速度非常快

磁盘中的物理布局：只有一个sdi文件

6.事务

(1)事务的界说

• 事务，是数据库操作中的一组不可再分割的逻辑执行单元，也是数据库并发控制的根本单位。
  
• 这个逻辑执行单元中包罗了一个或一组SQL语句，它们是作为一个整体一起向体系提交的，它们要么完全地执行，要么完全不执行。
  
• 假如此中一个操作不成功，这些操作都不会执行，前面执行的操作也会回滚到原状态，用来包管数据的同等性和完整性。
  

(2)事务的特性：ACID

事务的四大特性分别是：原子性、同等性、隔离性、持久性，统称为ACID.
   为了实现同等性C，所以就必须要有原子性A、隔离性I、持久性D
  (1)原子性（Atomicity）：不可分割
原子操作，不可被分割。事务中一组操作要么全部成功，要么一条都不执行。若事务中的任何一个操作失败，整个事务都会回滚（Undo），使数据库返回到事务开始之前的状态。
(2)同等性（Consistency）：数据精确性
同等性确保了数据库在事务执行前后都处于同等的状态。同等性意味着事务必须将数据库从一个同等状态转移到另一个同等状态。在此过程中，数据库的完整性束缚、业务规则和数据束缚都必须得到满意
(3)隔离性（Isolation）：一个事务感受不到其他事务的存在，事务之间感受不到彼此的存在
隔离性确保并发事务的执行不会相互干扰。差别事务之间的操作是隔离的，事务之间的中间状态对其他事务是不可见的。隔离性通常通过数据库的隔离级别来实现，例如读未提交、读已提交、可重复读、序列化等。
(4)持久性（Durability）：一旦提交，永久生效。即使体系瓦解，数据也不受影响
持久性确保一旦事务提交，其效果将永久保存，即使体系发生故障也不会丢失。事务一旦提交，数据库体系会包管所有的修改都被持久化存储。

(3)事务的语法规则：显式事务、隐式事务、回滚

事务又分为显式事务和隐式事务
1.显式事务：指事务具有明显的开启或结束事务的标志

1. mysql> BEGIN / START TRANSACTION; -- 开启事务
2. mysql> SQL statements -- 具体的SQL语句,可以执行多条SQL语句
3. mysql> COMMIT        -- 提交事务

复制代码

2.回滚事务

1. mysql> BEGIN / START TRANSACTION; -- 开启事务
2. mysql> SQL statements -- 具体的SQL语句,可以执行多条SQL语句
3. mysql> ROLLBACK        -- 回滚事务，前提是不能提交COMMIT

复制代码

3.回滚点 与 部分回滚 (相当于存档)

1. SAVEPOINT identifier;        -- 创建回滚点
3. ROLLBACK TO [SAVEPOINT] identifier;        -- 回滚到某个回滚点
5. RELEASE SAVEPOINT identifier;  -- 释放删除回滚点

复制代码

4.隐式事务
隐式事务，是指事务没有明显的开启或结束的标志。执行SQL语句时，数据库会主动开启、提交事务。

5.显式事务与隐式事务的区别

(4)并发控制带来的题目

并发访问时，同时进行 读-读 没题目，同时进行 读-写、写-写 会出现题目：
1.丢失更新(Lost Update)：并发写-写，造成更新丢失，包括 回滚丢失、覆盖丢失
2.脏读 (Dirty Read)：一个事务读取到另一个事务未提交的数据 。【读-写】
3.不可重复读(Nonrepeatable Read)：一个事务内部，两次读数据，数据内容差别等 （条数相同，其他事务UPDATE） 【读-写】
4.幻读(Phantom Read)：一个事物内部，两次读，数据的条数差别等 (其他事务INSERT/DELETE) 【读-写】

1.丢失更新
(1)回滚丢失

(2)覆盖丢失

2.脏读

3.不可重复读

4.幻读

(5)事务的4种隔离级别：RU、RC、RR、S

1.为了办理并发读-写、并发写-写带来的四种题目，SQL标准界说了四种事务隔离级别：
(1)读未提交RU(Read Uncommitted)
(2)读已提交RC(Read Committed)
(3)可重复读RR(Repeatable Read)：默认RR级别
(4)可串行化S(Serializable)：严格串行，效率低

2.改变会话的事务隔离级别：
每个客户端可以认为是一个会话，而每个会话都可以单独设置事务的隔离级别，SQL命令如下：

1. mysql> set session transaction isolation level Read Uncommitted;
2. mysql> set session transaction isolation level Read Committed;
3. mysql> set session transaction isolation level Repeatable Read;
4. mysql> set session transaction isolation level Serializable;

复制代码

3.检察事务默认的隔离级别

1. SELECT @@transaction_isolation;

复制代码

4.读未提交RU
(1)办理丢失更新：直接壅闭。
InnoDB是行级锁，只会壅闭对同一行数据进行修改的操作

(2)RU未办理脏读题目

5.读已提交RC

1. mysql> set session transaction isolation level read committed;        -- 当前会话设置为读已提交的隔离级别

复制代码

1. mysql> SELECT @@transaction_isolation;
2.   -- 检察当前会话的隔离级别+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ         |+-------------------------+

复制代码

(1)RC办理了脏读题目
(2)RC没有办理不可重复读的题目

6.可重复读RR
(1)办理了不可重复读的题目。别人commit了，只要我没有commit，即我的事务没结束，事务内部每次对相同的值查询，值稳定

(2)RR级别没有办理幻读题目
固然看起来没有表现别的事务新插入的数据，但是本事务执行相同的插入操作却失败了【反证法】，证明没有办理幻读题目，依然能感受到其他事务存在。

7.串行化S
办理了所有并发题目，但效率低。
哪个事务先BEGIN，谁先执行。(底层实现可能是 加锁+队列)
严格串行，隔离级别最严格，但无法完成大并发的实现。

7.索引

(1)索引的界说

索引是一种数据布局，加速检索。
索引的存储原理：空间换时间，放在磁盘文件中

(2)索引的数据布局：B+树

1.索引该使用什么样的数据布局呢？需求：
①支持排序 order by colum ASC/DESC
②支持范围查找 where id > 5 and id < 100
③磁盘I/O次数要少，要求高效
每次磁盘I/O操作，读取一个块(磁盘块)，即一个扇区，一样寻常为4KB = 4096B
2.数据布局
(1)哈希表：哈希索引
(2)数组：二分查找
(3)搜索二叉树(BST / AVL / RBT)：
(4)B树、B+树
(1)哈希索引：
长处：查找快
缺点：
①只支持等值比较，不支持范围查询
②无法用于排序
(2)数组：
一连存储空间。添加和删除时，涉及大量磁盘IO操作
(3)二叉树
结点不是一连存储的，因此每访问一个数据节点就要一次磁盘IO操作。
比如1000个数据，树的层高就是10了，就要10次IO操作。
磁盘IO次数太多了，依然不适合作为数据库索引的数据布局。
(4)B树

那最终为什么没有使用B树作为存储引擎呢？
结点存放索引和现实数据，太大了，导致树高增加，必要更多次I/O操作

(5)B+树
①叶子结点才存放现实的记载数据，非叶子结点只存放索引 (比如主键id)。
因此B树查找可以在分支结点找到现实数据，B+树的查找必须找到叶子结点才气找到现实数据。
②B+树结点内部是单链表，结点之间是双向链表，除了支持树形查找外，还支持顺序查找。
③B+树的索引信息数=度数，而B树的索引信息数 = 度数-1
④B+树的根结点常驻内存，所以又可以淘汰一次磁盘I/O操作
   联合数据布局专栏的B树、B+树学习：https://blog.csdn.net/Edward1027/article/details/131129113
  

(3)索引的分类

MySQL中索引的名字有很多，我们必要对他们进行分类，然后再逐一解析。可以从以下几个方面来进行分类：

• 按字段特性可以分为：主键索引、唯一索引、普通索引。
  
• 按字段个数可以分为：单列索引、联合索引。
  
• 按数据布局可以分为：B+树索引、哈希索引、全文索引。
  
• 按物理存储可以分为：聚簇索引(主键索引)、二级索引(辅助索引)。
  

①普通索引 (单列索引，Single-Column Index)

创建索引

1. CREATE INDEX 索引名 on 表名(列名);

复制代码

检察索引

1. SHOW INDEX FROM 表名;

复制代码

删除索引

1. DROP INDEX 索引名 on 表名;

复制代码

若没有创建索引，则遍历速度很慢

优化：对普通字段创建普通索引
创建索引的时间有点慢，但创建索引后，再查询该字段(列、属性)，速度进步了700倍。
   优化1：隐式事务改显式事务，查询速度进步10倍左右
优化2：普通字段创建普通索引，查询速度提升700倍左右。
  

②联合索引、最左前缀匹配原则

1.联合索引
联合索引是针对多个列创建的索引。它可以加速涉及多个列的查询操作。
2.最左前缀匹配原则：
基于(column1,column2,column3,…)中的联合索引，先按col1查询，再按clo2查询，最后按clo3查询。不能跳过前面的属性列column1直接查询后面的column2和column3，否则会退化为O(n)的顺序查找。但可以单独查询clo1。

2.效果：
①创建联合索引后，直接查col2字段，没有遵循最左前缀匹配原则，不走联合索引，照旧顺序遍历，为秒级
②查col1，走联合索引。查询时间是毫秒级。

3.语法
创建索引

1. CREATE INDEX 索引名 on 表名(列名1,列名2,...);

复制代码

检察索引

1. SHOW INDEX FROM 表名\G;

复制代码

删除索引

1. DROP INDEX 索引名 on 表名;

复制代码

③哈希索引

InnoDB存储引擎创建哈希索引，但创建的照旧BTree索引。必要开启自顺应，才创建哈希索引。
MEMORY存储引擎的数据存储在内存中，默认使用HASH索引
哈希索引（Hash Index）
特点：基于哈希表实现，通过哈希函数将键值映射到对应的桶（bucket）中。
实用场景：实用于等值查询，如 = 或 IN 操作。
长处：查找时间复杂度为 O(1)。
缺点：不实用于范围查询和排序查询。


④全文索引

针对字符串
全文索引（Full-Text Index）
特点：用于快速全文搜索，支持基于关键词的文本查询。
实用场景：实用于大文本字段的搜索，如文章、书籍、日志等。
长处：全文检索效率高，支持复杂的搜索条件。
缺点：创建和维护成本较高。

⑤聚簇索引 (主键索引)

非叶结点存主键 (主键作为索引)，叶子节点存行数据。
现实实现时，聚簇索引的根结点常驻内存

⑥辅助索引 (非聚簇索引)

非叶节点存当索引的列的元素，叶子结点存主键。
若要查找非索引、非主键的元素，必要调用辅助索引找到主键后，再用聚集索引找行数据。这样的举动称为回表查询。

存在回表操作证明效率低，应当尽量避免回表。
如SELECT * 就必然会要查找不属于辅助索引和主键的列，必然会多次回表，导致效率低。

---

思考：若主键为 (id1, id2), 辅助索引为 (a, b)。
判断下面哪些 SQL 语句必要回表？

1. 1) select b from t where a = xxx;  //查找辅助索引，非叶结点有存，不需要回表
2. 2) select a from t where b = xxx;  //查找辅助索引，非叶结点有存，不需要回表
3. 3) select id2, b from t where a = xxx;  //查找主键，叶子结点有存，不需要回表
4. 4) select id1, a from t where b = xxx;  //查找主键，叶子结点有存，不需要回表

复制代码

---

⑦覆盖索引

当要查的内容属于辅助索引里key里(非叶节点或叶子结点)，或者是主键(叶子结点)，则不必要回表查询。这样的索引称为覆盖索引 (covering index)。 (上述思考题4道都不必要回表，都是覆盖索引。)

(4)何时创建索引

8.慢查询

ubuntu_slow.log 会记载超过指定时长的SQL查询命令，以便后续分析为什么执行的慢，并进行优化。

9.执行计划

1.MySQL EXPLAIN 的12个输出：

• id: 查询中每个SELECT语句的标识符。
  
• select_type: 查询的类型（如 SIMPLE, PRIMARY, UNION）。
  
• table: 表的名称。
  
• partitions: 匹配的分区信息（假如有）。
  
• type: 连接类型（如 ALL, index, range, ref, eq_ref, const, system）。这是重点优化方向，将ALL(全表扫描) 优化为 ref(范围查询) 或 const(只有一行)
  ①const或system只有一张表。system最快，没有IO操作
  ②eq_ref是值一对一，主键扫描。
  ref非主键非唯一束缚，可能相同值是一行对应多行，普通索引扫描。
  ③range是范围扫描
  ④index是只扫描非叶子节点的索引
  ⑤ALL是全表扫描
  
• possible_keys: 可能使用的索引。
  
• key: 现实使用的索引。
  
• key_len: 使用的索引的长度。
  
• ref: 使用索引用到的列或常量。
  
• rows: 估计必要读取的行数，越少越好。
  
• filtered: 通过条件过滤的行的百分比。
  
• Extra: 额外的信息（如 Using index，Using where，Using temporary，Using filesort）。
  

①select type：

②type

---

以下是一个具体的示例，展示如何使用执行计划进行查询优化：
假设有一个查询：

1. SELECT * FROM orders WHERE customer_id = 12345;

复制代码

使用 EXPLAIN 检察执行计划：

1. EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

复制代码

输出可能如下：

1. +----+-------------+--------+------+---------------+------+---------+------+---------+-------+
2. | id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra |
3. +----+-------------+--------+------+---------------+------+---------+------+---------+-------+
4. |  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | NULL  |
5. +----+-------------+--------+------+---------------+------+---------+------+---------+-------+

复制代码

从输出中可以看出：
type 为 ALL，表现全表扫描。
possible_keys 和 key 都为 NULL，表现没有使用索引。
通过添加索引来优化查询：

1. CREATE INDEX idx_customer_id ON orders (customer_id);

复制代码

再次检察执行计划：

1. EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

复制代码

输出可能如下：

1. +----+-------------+--------+------+-----------------+---------------+---------+-------+------+-------+
2. | id | select_type | table  | type | possible_keys   | key           | key_len | ref   | rows | Extra |
3. +----+-------------+--------+------+-----------------+---------------+---------+-------+------+-------+
4. |  1 | SIMPLE      | orders | ref  | idx_customer_id | idx_customer_id | 4       | const |   10 | NULL  |
5. +----+-------------+--------+------+-----------------+---------------+---------+-------+------+-------+

复制代码

优化后的输出表现：
type 为 ref，表现使用索引扫描。
key 为 idx_customer_id，表现使用了新创建的索引。
rows 为 10，表现大大淘汰了扫描的行数。
通过这种方式，使用执行计划可以明显优化查询性能，进步数据库体系的效率和响应速度。

---

VARCHAR必要额外两个字节，来记载varchar的长度
④Extra

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。