Mysql

基于CentOS的Mysql的安装：

注意：安装采用此方式强制安装 rpm -ivh mysql-community-common-8.0.11-1.el7.x86_64.rpm --nodeps --force

下载链接：https://dev.mysql.com/downloads/mysql/
选择下载版本
下载RPM Bundle的压缩包
解压tar压缩包

安装rpm软件

[root@iZbp13bwx64spr1p933f1yZ mysql]# rpm -qa | grep mysql
// 需要安装的rpm软件包
mysql-community-client-8.0.25-1.el8.x86_64
mysql-community-libs-8.0.25-1.el8.x86_64
mysql-community-common-8.0.25-1.el8.x86_64
mysql-community-server-8.0.25-1.el8.x86_64
// 初始化mysql
[root@iZbp13bwx64spr1p933f1yZ mysql]# mysqld --initialize
// 为mysql配置权限
[root@iZbp13bwx64spr1p933f1yZ mysql]# chown mysql:mysql /var/lib/mysql -R
// 启动mysql
[root@iZbp13bwx64spr1p933f1yZ mysql]# systemctl start mysqld.service
// 设置mysql开机自启
[root@iZbp13bwx64spr1p933f1yZ mysql]# systemctl enable mysqld

注意项：

安装后默认随机生成密码，查看密码方式：

cat /var/log/mysqld.log | grep password
2021-07-16T08:47:23.606714Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5cQ7Tz+w!foe

登录注意，因为随机密码会有特殊字符，所以登录可能失败。

[root@iZbp13bwx64spr1p933f1yZ ~]# mysql -u root -p 5cQ7Tz+w!foe
-bash: !foe: event not found

使用随机密码登录后需要立马更改密码，更改密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

命名注意项：

实体类的属性用驼峰命名对应的数据库属性用"_"来区分两个单词
数据库字段属性不能以数据库保留字段命名（delete、where、if、like等）

Mysql批量修改数据：

update 表名 set 字段名 = replace(字段名,'http://zencart.me','zencart.me')

子查询

出现在其他语句中的select语句，称为子查询或内查询

子查询分类

按子查询出现的位置：

select后面：

仅仅支持标量子查询

from后面：（必须为子查询起别名）

支持表子查询

where或having后面：

表量子查询（单行）
列子查询（多行）
行子查询

exists后面（相关子查询）：

表子查询

按结果集的行列数不同：

表量子查询（结果集只有一行一列）
列子查询（结果集只有一行多列）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

多行子查询：

返回多行
使用多行比较操作符

操作符：

IN/NOT IN：等于列表中的任意一个
ANY|SOME：和子查询返回某一个值比较
ALL：和子查询返回所有值比较

分页查询：

limit：放在sql语句最后

limit [offset],size：offset:起始索引（默认0开始）size：要显示的条目个数

联合查询

union（联合）：将多条查询结果合并成一个结果。

联合查询特点：

要求多条查询语句的查询列数一致。
要求多条查询语句的查询每一列的类型和顺序最好一致
union关键字默认去重，如果使用union all 可以包含重复列。

DML语言

数据库操作语言：插入（insert）、更新（update）、删除（delete）

插入语句

插入语句方式一：(推荐使用)

语法：insert into 表名(字段名，....) values(与字段名对应的值，....)

插入语句方式二：

语法：insert into 表名 set 字段名1 = 值, 字段名2 = 值，....;

特点：

插入的值要与字段名的类型相匹配
插入的列数和值的个数必须一致。
方式一支持子查询，方式二不支持子查询，推荐使用方式一

修改语句

修改单表：

语法：update 表名 set 字段1 = 值，字段2 = 值, ... where 条件；

修改多表：

语法：update 【表别名】inner(right、left) join 外表名【表别名】 on 条件（字段 = 值, ...）set 字段 = 值 where 条件

删除语句

单表删除：

语法：delete from 表名 where 筛选条件；

多表删除：

语法：delete from 表1 【别名】，表2 【别名】where 连接条件 and 筛选条件；

级联删除（同时删除两个表中的数据）：delete 表1，表2 from 表1 【别名】，表2 【别名】where 连接条件 and 筛选条件；

Truncate删除：

语法：truncate from 表名；

清除表中所有数据

Delete和Truncate的区别：

delete可以加where条件，truncate不能加
truncate删除效率比delete高
delete删除后，再插入数据，自增长列的值从断点开始，truncate删除后，再插入数据，自增长列的值从1开始。
truncate删除没有返回值，delete删除有返回值。
truncate删除不能回滚，delete删除可以回滚。

DDL语言

数据定义语言，即库和表的管理。对库和表的管理方式分为创建（create）、修改（alter）、删除（drop）

库的管理

库的管理：

在Mysql 5.0以后没有关键词修改库名，一般不建议更改库名

创建库：create database if not exists 库名;
选择使用的库：use 库名;
更改库的字符集：alter database 库名 character set 字符集;
库的删除：drop database if exists 库名;

表的管理

表的管理：

创建表：create table 表名(字段名1 数据类型(数据长度)，字段名2 数据类型(数据长度)，....);
删除表：drop table if exists 表名;
查看表的结构：desc 表名;
修改列名：alter table book **change** column 旧字段名新字段名 [类型、约束];
修改列的类型或约束：alter table 表名 modify column 字段 [类型、约束];
添加新列：alter table 表名 add column 字段类型;
删除列：alter table 表名 drop column 字段;
修改表名：alter table 表名 rename to 新表名;
修改列的类型或约束：alter table 表名 modify column 字段名 [类型、约束];

表的复制：

表的复制（仅复制表的结构）：create table 表名 like 表名（被复制的表）;
表的复制（复制表的结构和数据）：create table 表名 select * from 表名（被复制的表）;
表的复制（复制表的部分结构）：create table 表名 select 字段1,字段2, ... from 表名（被复制的表） where 0；
表的复制（复制表的部分结构和数据）：create table 表名 select 字段1,字段2, ... from 表名（被复制的表） where 0；
表的复制（跨库复制）：create table 表名 select * from 库名.表名（被复制的表）;

数据类型

数值型：

整形、小数（定点数、浮点数）

整形：

TinyInt：1字节
Smallint：2字节
Mediumint：3字节
Int、integer：4字节
Bigint：8字节

特点：

如果不设置无符号还是有符号，默认是有符号。无符号设置添加unsigned关键字。
如果插入的数值超出了整形的范围，会报out of range 异常，并且插入临界值。
如果不设置长度，会有默认的长度。
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill关键字使用。

浮点型：

float(M,D)
double(M,D)

定点型：

dec(M,D)
decimal(M,D)

特点：

M：整数部位+小数部位的总长度
D：小数部位
如果超过范围，则插入临界值
M和D都可以省略
顶点型的精度较高

字符型：

较短的文本：char、varchar。较长的文本：text、blob（较长的二进制数据）

M：代表最多字符数

char(M)：固定长度字符类型
varchar(M)：可变长度字符类型
enum('value1','value2',...)：枚举型，插入只能为列举值里面的一个
set('value1','value2',...)：集合型，与枚举类似，不同的是set可以插入多个列举的值

日期型：

日期值需要用单引号引用起来。

data：只保存日期
datatime：保存日期+时间
timestamp：时间戳，保存时间+日期。受时区影响
time：只保存时间
year：只保存年份

数据约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性。

六大常见约束：

NOT NULL：非空，保证该字段的值不为空
DEFAULT：默认值，保证字段有默认值
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空，一个表中只能有一个主键，允许组合（但不推荐）
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空，一个表中可以有多个唯一，允许组合（但不推荐）
CHEKE：检查约束（mysql中不支持，但不会报错，也不会有效果）
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。

表级约束和字段约束：

约束格式：

CREATE TABLE table_name(
	字段名 数据类型 字段约束,
	...,
	表级约束
)

列级约束：

从语法上来讲，列级约束支持6大约束类型（只是不报错），但外键约束对列级约束不会有任何效果。
表级约束：

除了非空、默认约束类型，其他的约束类型都支持。

语法：在字段的最下面添加CONSTRAINT 约束名约束类型(字段名)

外键的特点：
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
- 主表的关联列必须是一个Key（一般是主键或唯一）
- 插入数据时，先插入主表，在插入从表
- 删除数据时，先删除从表，再删除主表
标识列

标识列又称自增长列，即可以不用手动的插入值，系统提供默认的序列值。

标识列的特点：
- 标识列不一定和主键搭配，但要求是一个Key
- 一个表只能有一个标识列
- 标识列的类型只能是数值型
添加标识列：字段后面添加auto_increment

TCL语言

TCL（Transaction control language）：事务控制语言

事务的ACID(acid)属性：
1. 原子性（Atomicity )：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3. 隔离性（lsolation)事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（ Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
事物的创建：

事务针对的是增、删、查、改

开启隐式事务的前提是关闭自动提交功能

隐式事务：事务没有明显的开启和结束标记，比如insert、delete、update、select语句

显示事务：事务有明显的开启和结束标记。
```
set autocommit=0; #关闭自动提交
start transaction; # 开启事务的标记，可有可无
	update account set balance=9999 where id = 1;
	update account set balance=9998 where id = 2;
commit; #事务提交 
rollback; #事务回滚 
end transaction;#事务结束标记
```

事务的隔离级别

对于同时运行的多个事物，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题

脏读：

对于两个事务T1,T2 ，T1读取了已经被T2更新但还没有被提交的字段之后,若T2回滚，T1读取的内容就是临时且无效的。即一个事务读取了其他事物还没有提交的数据，读到的是其他事物“更新”的数据。

不可重复读：

对于两个事务T1,T2，T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了。即一个事务多次读取，结果不一样。

幻读：

对于两个事务T1,T2，T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行之后,如果T1再次读取同一个表,就会多出几行。即一个事务读取了其他事物还没有提交的数据，读到的是其他事物“插入”的数据。

数据库提供的隔离级别：

Mysql支持4种事务隔离级别，默认隔离级别为REPEATABLE READ（可重复读）

READ UNCOMMITTED（读未提交数据）：允许事务读取未被其他事物提交的变更.脏读,不可重复读和幻读的问题都会出现
READ COMMITED（读已提交数据）：只允许事务读取已经被其它事务提交的变更.可以避免脏读,但不可重复读和幻读问题仍然可能出现
REPEATABLE READ（可重复读）：确保事务可以多次从一个字段中读取相同的值。在这个事务持续期问，禁止其他事物对这个字段进行更新。可以避免脏读和不可重复读,但幻读的问题仍然存在。
SERIALIZABLE（串行化）：确保事务可以从一个表中读取相同的行。在这个事务持续期问，禁止其他事务对该表执行插入、更新和删除操作.所有并发问题都可以避免.但性能十分低下.

SavePoint（保存点）：

SavePoint只能搭配rollback使用。

语法：

set autocommit=0; #关闭自动提交
start transaction; 
	update account set balance=9999 where id = 1;
	savepoint a;#设置保存点名为a
	update account set balance=9998 where id = 2;
rollback to a; #事务回滚到保存点，保存点以下的将不会被生效

视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

创建视图：

语法：

create view 视图名;
as 
查询语句;

视图的特点：

重用Sql语句
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高安全性

视图的修改：

方式一（如果存在就修改，不存在则创建），语法：
```
create or replace view 视图名;
as
查询语句;
```

方式二（直接修改），语法：

alter view 视图名;
as 
查询语句;

删除视图：

语法：
```
drop view 视图名,视图名,...;
```

查看视图：

语法：
```
desc 视图名;
```

视图的更新：

对视图进行增删改会影响到元数据表中的数据。不建议通过视图更新元数据表。一般为视图配置权限为只读。

变量

系统变量

系统变量分为：全局变量和会话变量。

系统变量：

变量是由系统提供的，不是用户定义，属于服务器层面。注意：如果是全局级别，则需要加Global，如果是会话级别，则需要加Session，如果不写，则默认Session使用的语法。

全局变量跨连接有效。作用域为所有连接到mysql服务器的用户有效

查看所有的系统变量：show variables; 全局变量：show global variables; 会话变量：show session variables;
查看满足条件的部分系统变量：show global | session variables like ‘匹配条件’;
查看指定的某个系统变量的值：select @@global | session.系统变量名;

常用系统变量：

@@datadir：数据存放位置

@@sql_mode：数据库模式

会话变量：

会话变量的作用域仅针对当前会话有效；不会对其他连接造成影响。

自定义变量

自定义变量分为：用户变量和局部变量。

自定义变量：

作用域：针对当前会话（连接）有效，等同于会话变量的作用域

赋值的操作符： =或 :=

声明并初始化

#方式一
set @用户变量名 = 值;
#方式二
set @用户变量名 := 值;
#方式三
select @用户变量名 :=值;

赋值（更新用户变量的值）

select count(*) into  @变量名 from 表名;

查询
```
select @变量名;
```

局部变量：

作用域：仅仅在定义它的begin-end中有效；

声明：

declare 变量名 类型;
declare 变量名 类型 default 值;

赋值：

#方式一
set 用户变量名 = 值;
#方式二
set 用户变量名 := 值;
#方式三
select @用户变量名 :=值;
#方式四
select 字段 into 局部变量名 from 表;

使用
```
select 局部变量名;
```

用户变量和局部变量的区别：

作用域：
- 用户变量：当前会话有效。
- 局部变量：begin end 中有有效。
定义和使用的位置：
- 用户变量：会话中的任何地方。
- 局部变量：只能在begin end 中，且为第一句话
语法：
- 用户变量：必须加@符号，不用限定类型
- 局部变量：一般不用加@符号，需要限定类型

存储过程和函数

存储过程

一组预先编译好的Sql语句集合，理解成批处理语句。类似于Java中的方法，好处：1、提高代码的重用性。2、简化操作。3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

存储过程的创建与管理：

语法：

create procedure 存储过程(参数列表)
begin
	存储过程体（一组合法的Sql语句）
end

调用语法：call 存储过程名(实参列表);
删除存储过程：drop procedure 存储过程名;
查看存储过程的信息：show create procedure 存储过程名; 或 desc 存储过程名;
注意项：
1. 参数列表包含三部分：参数模式参数名参数类型，列如：in ikart varchar(20)
2. 参数模式：
  1. in：该参数可以作为输入，也就是该参数需要调用方传入值。
  2. out：该参数可以作为输出，也就是该参数可以作为返回值。
  3. inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回。
3. 存储过程体
  1. 如果存储过程体只有一句话，begin end 可以省略。
  2. 存储过程体每条Sql语句的结尾要求必须加分号。
  3. 存储过程的结尾可以使用delimiter重新设置。语法：
```
delimiter $ (结束标记)
案例
delimiter $
```

函数

与存错过程一样，函数是一组预先编译好的Sql语句集合，理解成批处理语句。类似于Java中的方法，好处：1、提高代码的重用性。2、简化操作。3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

存储过程和函数的区别：

存储过程可以有0到n个返回值，适合做批量插入、更新。函数有且仅有一个返回，适合做处理数据后返回一个结果。

函数的创建及使用：

创建：

create function 函数名(参数列表) returns 返回类型
begin 
	函数体;
end

注意：
1. 参数列表包含两个部分：参数名参数类型。
2. 函数体：肯定会有return语句，如果没有会报错。建议将return语句放在函数体后面。
3. 函数体中仅有一句sql语句，可以省略begin 和 end。
4. 使用delimiter语句设置结束标记。
调用函数：select 函数名(参数列表)
删除函数：drop function 函数名;

流程控制结构

分支结构

分支结构-CASE：

语法：

case 变量|表达式|字段
	when 条件表达式 then 返回的值1或语句1；
	when 条件表达式 then 返回的值2或语句2；
	...
	else 要返回的值n或语句n;
end case:

特点：
1. 可以作为独立表达式，嵌套在其他语句中使用，可以放在任何地方，Begin end 中或begin end 的外面，可以作为独立的语句去使用，只能放在begin end 中。
2. 如果when 中的值满足或条件成立，则执行对应的Then后面的语句。并且结束case，如果条件不满足，则执行else中的语句或值。
3. else可以省略，但是当所有条件都不成立时，省略了else则返回null。

分支结构-IF：

语法：

if 条件表达式1 then 语句1
elseif 条件表达式2 then 语句2
...
else 语句n;

循环结构

分类：while、loop、repeat 。循环控制：iterate（继续、类似于continue）、leave（跳出，类似于break）。

循环结构-while：

语法：

[标签:] while 循环条件 do 
	循环体;
end while [标签];

循环结构-loop：

语法：

[标签:] loop
	循环体;
end loop [标签];

循环结构-repeat：

语法：

[标签:] repeat
	循环体;
until 结束循环的条件;
end repeat [标签];

单词

procedure：/prəˈsiːdʒər/ n. 程序，手续；步骤
delimiter：/dɪˈlɪmɪtər/ n. 定界符

参考资料

官方文档：

MySQL 8.0 Reference Manual

阿里云RDS Mysql 参考文档：

RDS MySQL 数据库

Q.E.D.

Moiaussi