MySQL

一、MySQL基础

• 1.14.74.208MySQL启动命令 ```shell

  重启数据库

  /etc/init.d/mysqld restart

进度数据库

[root@VM-0-13-centos bin]# ./mysql -uroot -p

<a name="6a050b6c"></a>
### 1.1、基础命令操作
<a name="57dcc315"></a>
#### 1.1.1、连接数据库

连接数据库

mysql -u root -p1234546;

查询数据库版本

select version();

修改密码，MD5加密

update user set authentication_string=PASSWORD(“123456”) where user=”root”;

刷新权限

flush privileges;

退出mysql

quit;/exit;

<a name="9ab72fe6"></a>
#### 1.1.2、数据库权限命令操作

查看数据库

show databases;

使用数据库

use school;

查看数据库中所有的表

show tables;

查看表中所有数据

describe school;

创建数据库

create databases if not exists school;

创建用户

create user MyName identified by ‘123456’;

修改当前用户密码

set passworld = passworld(‘123456’);

修改指定密码

set passworld for sql_user = passworl(‘123456’);

重命名

rename user 原名 to 新名字;

授予用户权限

grant all privileges on . to 用户名; 特权授予不能选择grant(这是root用户才有的权限)

撤销权限 撤销所有的权限

revoke all privileges on . from 用户名;

删除用户

drop user 用户名;

SQL命令备份 备份 -主机 -用户 -密码 库名 表名1 表名2…. >备份路径

mysqldump -hlocalhost -uroot -p123456 school student >D:/project

导入数据库 在进入用户条件下

source D:project

<a name="acee030f"></a>
#### 1.1.3、操作数据库(1)
操作数据库—>操作数据库表—>操作数据库表字段；

创建数据库

create databases if not exists school;

删除数据库

drop databases if exists school;

创建数据库表

create table if not exists student;

查看数据库创建语句

show create databases school;

查看数据库表定义语句

show create table student;

查看数据库表的具体结构

DESC student;

修改表

alter table student rename as student1;

增加表的字段

alter table student add card_ID int(18);

修改表字段类型,modify只能修改字段类型和约束,不能修改字段名

alter table student modify gender varchar(2);

修改字段名,change只能修改字段名,不能修改字段类型和约束

alter table student change gender age int(2);

删除字段

alter table student drop gender;

创建外键约束

ALTER TABLE student; ADD CONSTRAINT FK_tea_Id FOREIGN KEY(tea_Id) REFERENCES teacher(tea_Id);

<a name="b893d346"></a>
#### 1.1.4、数据库列类型
> 数值
| tinyint | 非常小的数据 | 1字节 |
| --- | --- | --- |
| smallint | 十分小的数据 | 2字节 |
| mediumint | 较小的数据 | 3字节 |
| **int** | 标准数据 | 4字节 |
| bigint | 较大的数据 | 8字节 |
| float | 浮点数单精度 | 4字节 |
| double | 浮点数双精度 | 8字节 |
| decimal | 字符串形式浮点数（用于金融领域） | 8字节 |
| Boolean | 布尔类型,只有是与非 | 4字节 |
**int类型的创建大小是显示宽度,与0填充有关,并不代表int(1)只能存一个数字**
> 字符串
| **char** | 字符串固定大小1-255 | 一般不用 |
| --- | --- | --- |
| **varchar** | **可变字符串0-65536** | String类型 |
| tinytext | 微型文本 2^8-1 | 微型文本 |
| text | 文本字符串 | 多字符串文本 |
> 时间日期 java.util.Data
| data | YYYY-MM-DD | 日期格式 |
| --- | --- | --- |
| time | HH:MM:SS | 时间格式 |
| datatime | YYYY-MM-DD HH:MM:SS | 常用时间格式 |
| timestamp | 获取时间戳 | 1970.1.1至今的毫秒数 |
<a name="daccdf72"></a>
#### 1.1.5、数据库字段属性(重点)
**Unsigned**
- 无符号整数
- 声明该字段不能为负数
**zerofill**
- 0填充字符
- 不足数位使用0来填充
**自增**
- 设置ID或index时需要用到
- 命令使用 AUTO_INCREMENT
**非空**
- 该字段不能为空,必须填值
- 命令使用 NOT NULL
> default
- 一般情况会结合布尔类型使用
- 默认 1=没有发生的状态 2=已发生状态
> 阿里使用规范
```bash
version          #乐观锁
is_delte         #伪删除
gmt_create        #创建时间
gmt_update        #修改时间

1.2、数据库操作（2）

1.2.1、新建数据库及数据库表

#创建数据库
CREATE DATABASE IF NOT EXISTS `school`;
#使用数据库
USE `school`;
#创建数据库表
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(12) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
    `lavel` VARCHAR(10) COMMENT '学生年级',
    `name` VARCHAR(32) NOT NULL COMMENT '学生姓名',
    `gender` BOOLEAN DEFAULT 1 COMMENT '默认为1，表示男',
    `college` VARCHAR(32) COMMENT '所属学院',
    `major` VARCHAR(32) COMMENT '学生专业',
    `Telephone` INT(12) NOT NULL COMMENT '联系电话',
    `address` VARCHAR(255) COMMENT '家庭住址',
    `remarks` VARCHAR(255) COMMENT '备注',
    #设置主键,方便关联
    PRIMARY KEY(`id`)  
    #设置数据库引擎及编码格式
)ENGINE=INNODB DEFAULT CHARSET=utf8;

• 效果
  

1.2.2、MyISAM与InnoDB的区别

1.3、数据库级别的主键与外键

• 主键关联
• 一对一关联
• 外键关联
  • 一对多关联或多对多关联
• 外键事物理外键,关联查询时尽量在应用曾解决,降低数据库表于表之间的操作耦合

  #创建外键约束
  ALTER TABLE `student`;
  ADD CONSTRAINT `FK_tea_Id` FOREIGN KEY(`tea_Id`) REFERENCES `teacher`(`tea_Id`);

1.4、DML语言

DML=Data Manage Language(数据库管理语言)

#插入语句
insert into 表名(字段1,字段2,字段3...) values ('值1','值2','值3'....);
#若插入全部,字段名可以省略
insert into 表名 values ('值1','值2','值3'....);
#插入数据
INSERT INTO `student` (
  `id`,`lavel`,`Name`, `gender`, `college`,`major`,`Telephone`, `address`, `remarks`) 
VALUES
  (
  184002,'2018级', '李四', 1,'信息工程学院', '计算机科学与技术', 1322354965, '遵义市红花岗区遵义师范学院新蒲校区','无' ) ;
#######################################################################################################  
#修改语句
updata 表名 set name='修改值' where id = ____;
#不限定条件,执行全部
updata 表名 set name='修改值'
#修改数据
UPDATE `student` SET `college`='外国语学院' WHERE id=184003;
#修改多个字段,逗号隔开
UPDATE `student` SET `name`='小五',`college`='外国语学院',`major`='翻译' WHERE id=184003;
########################################################################################################
#删除数据
#删除指定数据
delete from 表名 where id = _____; 
delete from `student` where id =184001;
#删除全部数据(1)
delete from `student`  #不建议使用
#删除全部数据(2)
truncate 表名;
truncate `student`;
#delete与truncate的区别
相同点:
    都能删除数据
不同点:
    truncate能重新设置自增长列,计数器归零
    truncate不会影响事务
    delete删除后重启数据库自增长会从1开始,原因是InnoDB引擎使数据存储在内存中,断电即失,而MyISAM存储在硬盘里,重启数据库后自增长从上一个数据开始

1.5、DQL语言

DQL=Data Query Language (数据库查询语言)

#查询全部数据
select * from `student`;
#查询指定数据
select 字段名 from `student`;
#自定义查询结果的表头
select `id` as 学号,`name` as 姓名 from `student`;
SELECT `id` AS 学号,`name` AS 姓名 FROM `student`;
#自定义表名
select `id` as 学号,`name` as 姓名 from `student` as 学生信息;
SELECT `id` AS 学号,`name` AS 姓名 FROM `student` as 18级学生表;
#拼接查询
select concat('拼接内容',字段名) as 别名 from `student`;
SELECT CONCAT('姓名:',`name`) AS 姓名信息 FROM `student`;
#去重查询 用于重复数据的查询
select distinct 字段名 from `student`;
select distinct `name` from `studnt`;
#查询数据库版本
select version();
#查看计算表达式
select 100*3-1 as resullt;
#查看自增步长
select @@auto_increment_increment

• 条件查询where
  | 操作符 | 描述 | 使用 | | —- | —- | —- | | = | 等于 | where id = 1 | | != | 不等于 | where id != 1 | | > | 大于 | where id > 1 | | < | 小于 | where id < 1 | | >= | 大于等于 | where id >= 1 | | <= | 小于等于 | where id <= 1 | | BETWEEN | 在某个范围内 | where id between 1 and 10 | | LIKE | 搜索某种模式 | where like %张% |

• 模糊查询 | 运算符 | 语法 | 描述 | | —- | —- | —- | | is null | a is null | 操作位null,结果为真 | | is not null | a is not null | 操作不为null,结果为真 | | between | a between b and c | 若a在b~c之间，结果为真 | | like | a like b | SQL模糊匹配，若a配置b，结果为真 | | in | a in (a1,a2,a3….) | 假设a是a1,a2,a3其中某个值，结果为真 |

#查询姓李的数据
SELECT `name`,`major` FROM `student` WHERE `name` LIKE '李%'; 
#查询姓李单名的数据
SELECT `name`,`major` FROM `student` WHERE `name` LIKE '李_'; 
#查询姓李双名的数据
SELECT `name`,`major` FROM `student` WHERE `name` LIKE '李__'; 
#查询名字中带有某个字的
SELECT `name`,`major` FROM `student` WHERE `name` LIKE '%世%'; 
#查询多个同一区间内数据
SELECT `name`,`major` FROM `student` WHERE `id`in (184001,184002);

1.6、关联查询

• 语法顺序不能乱
  

jionON理论

• 其中关联查询

#创建数据库表1
CREATE TABLE tbl_dep(
    id INT(11) NOT NULL AUTO_INCREMENT,
    deptName VARCHAR(22) DEFAULT NULL,
    addr VARCHAR(22) DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET=utf8;
#创建数据库表2
CREATE TABLE tbl_emp(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(22) DEFAULT NULL,
`deptId` VARCHAR(22) DEFAULT NULL,
PRIMARY KEY (`id`)
#constraint `fk_deptId` foreign key (`deptId`) references tbl_dep(id);
)engine=innodb default charset =utf8;
########################################################################################################
#插入数据1
INSERT INTO tbl_dep(deptName,addr) VALUES('hr','111');
INSERT INTO tbl_dep(deptName,addr) VALUES('bd','112');
INSERT INTO tbl_dep(deptName,addr) VALUES('vb','113');
INSERT INTO tbl_dep(deptName,addr) VALUES('sd','114');
INSERT INTO tbl_dep(deptName,addr) VALUES('yy','115');
#插入数据2
INSERT INTO tbl_emp(name,deptId) VALUES('k8',1); 
INSERT INTO tbl_emp(name,deptId) VALUES('k6',2); 
INSERT INTO tbl_emp(name,deptId) VALUES('k4',3); 
INSERT INTO tbl_emp(name,deptId) VALUES('k4',11);
########################################################################################################
#别名AS n的AS是可省的
#内连接(两表的共有部分) 交集
SELECT * FROM tbl_dep d INNER JOIN tbl_emp e ON d.id=e.deptId;
#左连接（左表的全部，右表不满足补NULL）
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId;
#右连接（右表的全部，左表不满足的补NULL）
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;
#特殊的左连接，（显示为左表的独有的数据）
说明：查询tbl_dep 表中跟tbl_emp 表无关联关系的数据，即tbl_dep 独占，且tbl_emp 表的显示列补NULL；
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
#特殊的右连接（显示为右表的独有的数据 ）
说明：查询tbl_emp 表中跟tbl_dep 表无关联关系的数据，即tbl_emp 独占，且tbl_dep 表的显示列补NULL；
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;
#全连接（显示全部数据）（mysql 不支持 full outer join）
#UNION ：有去重的功能。
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;
#显示两表的独有的数据
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;
########################################################################################################
#多级连接等式查询
select t1,table_1_id,table_1_name,table_3_name,table_2_result
from table_1 as t1
inner jion table_2 as t2
on t1.table_1_id = t2.table_2_id
inner jion table_3 t3
on t2.table_2_id = t3.table_3_id
where table_3_name = '待查内容';

自查询

• 用于多级目录包含关系的使用
• 原理：先将一张表拆分，然后 ```

  创建数据库表

  CREATE TABLE IF NOT EXISTS category( categoryid INT(5) AUTO_INCREMENT NOT NULL COMMENT ‘一级编号’, pid INT(5) NOT NULL COMMENT ‘二级编号’, categoryName VARCHAR(32) NOT NULL COMMENT ‘工种名称’, PRIMARY KEY(categoryid) )ENGINE=INNODB DEFAULT CHARSET =utf8;

插入数据

INSERT INTO category(categoryid,pid,categoryName) VALUES(7,5,’PS技术’); INSERT INTO category(categoryid,pid,categoryName) VALUES(6,2,’Web开发’); INSERT INTO category(categoryid,pid,categoryName) VALUES(2,1,’信息技术’); INSERT INTO category(categoryid,pid,categoryName) VALUES(8,2,’办公信息’); INSERT INTO category(categoryid,pid,categoryName) VALUES(4,3,’数据库’); INSERT INTO category(categoryid,pid,categoryName) VALUES(5,1,’美工设计’); INSERT INTO category(categoryid,pid,categoryName) VALUES(3,1,’软件开发’);

自查询

SELECT a.categoryName AS ‘一级工种’,b.categoryName AS ‘二级工种’ FROM category AS a,category AS b WHERE a.categoryid=b.pid;

   -  未自查询时<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210405150034882.png#crop=0&crop=0&crop=1&crop=1&id=UvE3K&originHeight=258&originWidth=396&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
   -  自查询时<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210405150055537.png#crop=0&crop=0&crop=1&crop=1&id=fNIlq&originHeight=174&originWidth=420&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210405155938695.png#crop=0&crop=0&crop=1&crop=1&id=uHDmq&originHeight=562&originWidth=1269&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
<a name="0cc69b3d"></a>
### 1.7、排序与分页
> 排序 order by
- 升序 order by asc
- 降序 order by desc

升序

SELECT pid,categoryName FROM category ORDER BY pid ASC;

降序

SELECT pid,categoryName FROM category ORDER BY pid DESC;

> 分页    limit  起始页,页面大小
-  缓解数据库的压力 
-  提高用户体验  
| pages | pageNum | pageSize |
| --- | --- | --- |
| 第 1 页 | 0（1-1） | （1-1）*5 |
| 第 2 页 | 1（2-1） | （2-1）*5 |
| 第 3 页 | 2（3-1） | （3-1）*5 |
| …… | ……. | ……. |
| 第 N 页 | n-1 | （n-1）*5 |

分页查询

SELECT pid,categoryName FROM category ORDER BY pid DESC LIMIT 1,2;

<a name="570584a5"></a>
## 二、MySQL进阶
<a name="42fd9032"></a>
### 2.1、函数使用
<a name="0aad4f68"></a>
#### 2.1.1、常用函数

基础数学运算

绝对值

select abs(-8);

向上取整

select ceiling(9.3);

向下取整

select flood(9.7);

随机生成数

select random();

判断数值正负 整数返回1，复数返回-1

select sign(10);

判断字符长度

select char_length();

拼接字符串

select coucat();

定位插入

select insert(‘原数据’,起始位置,插入大小,’插入内容’); SELECT INSERT(‘我爱你’,2,1,’超级爱’); #我爱你—>我超级爱你

转换大写

select uppek();

转换小写

select lower();

索引字符串中首次出现字串的位置

select instr(‘huang’,’u’); # ->2

字符串替换

select replace(‘努力学习准没错’,’努力’,’坚持’); #->坚持学习准没错

倒叙输出

select reverse(‘努力学习准没错’);

获取当前日期

select curdate();

详细时间

select now(); select localtime();

获取系统时间

select sysdate();

取年月日

select year(now()); select month(now()); select day(now());

查看系统权限

select user();

查看版本

select version()

<a name="a913e0f7"></a>
#### 2.1.2、聚合函数
> 计算函数

计数

select count(‘id’) from student; #指定字段，会忽略所有null值，精确 select count(*) from student; #不会忽略null值,扫描所有行 select count(1) from student; #不会忽略null值，不扫描

计算

select sum(‘id’) from student;

求值得聚合函数还包括以下

sum、avg、max、min

-  分组聚合函数

根据城市查询对应得人数

select city,count(*) from student group by city;

根据城市查询对应的人数及性别

select city,sex count(*) from student group by city,sex;

group by 是查询得条件，select中有的实例属性在group by中必须有，否则无法查询

<a name="6d601b5d"></a>
#### 2.1.3、数据库级别的MD5加密
- **信息摘要算法，是一个不可逆加密过程**
- 一般不在数据库层使用，而是在应用层解决加密问题

创建登陆信息表

CREATE TABLE IF NOT EXISTS testMD5( id INT(5) NOT NULL COMMENT ‘序号’, name VARCHAR(100) NOT NULL COMMENT ‘用户名’, pwd VARCHAR(100) NOT NULL COMMENT’密码’,
PRIMARY KEY(id) )ENGINE=INNODB DEFAULT CHARSET=utf8;

插入用户信息

INSERT INTO testMD5 VALUES (1,’张三’,’123456’), (2,’李四’,’654321’), (3,’王五’,’145236’);

加密现有数据库信息

UPDATE testmd5 SET pwd=MD5(pwd) ;

插入数据时加密，应用层解决的事情

INSERT INTO testMD5 VALUES(5,’小黄’,MD5(111111));

查询验证用户信息

SELECT * FROM testmd5 WHERE name=’小黄’ AND pwd = MD5(‘111111’);

<a name="a8c9d628"></a>
### 2.2、事务处理
> 事务原则 ACID原则
- **原子性（A）**     针对同一个事务，要么全部成功，要么全部失败
- **一致性（C）**     在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏，即完整性及业务逻辑上的一致性
- **隔离性（I）**       用于并发控制，隔离能够确保并发执行的事务按顺序一个接一个地执行，未完成事务不影响另外未完成事务
- **持久性（D）**      一旦一个事务被提交，不可逆持久保存，不会因为与其他操作冲突而取消这个事务
> 隔离出现干扰的事务
- **脏读**                      一个事务读取了另外一个未提交事务的数据    （问题严重）
- **不可重复读**           在事务内读取某一行数据，多次读取结果不同  （不一定是错误，可能场合不对，事务更新过快）
- **幻读**                       在一个事务内读取到别的事务插入的数据，导致数据前后不一致 （问题严重）
> 事务处理流程

手动接管事务

关闭事务自动提交

set autocommit = 0

开启事务

start transaction;

提交事务 事务正确无误时提交,提交后持久化保存

commit;

事务提交失败时回滚事务

rollback;

事务回滚后开启事务自动提交 恢复默认值

set autocommit = 0

关闭事务

close transaction;

> 模拟事务流程

转账测试

CREATE TABLE account( id INT(5) NOT NULL AUTO_INCREMENT COMMENT ‘用户id’, name VARCHAR(50) COMMENT ‘用户名’, money DECIMAL(9,2) COMMENT ‘金额’, PRIMARY KEY(id) )ENGINE=INNODB DEFAULT CHARSET=utf8;

添加用户

INSERT INTO account VALUES(1,’张三’,1000.00),(2,’李四’,2000.00);

模拟事务流程

关闭事务自动提交

SET autocommit = 0;

开启事务

START TRANSACTION ;

模拟事务

UPDATE account SET money = money-500 WHERE name = ‘张三’ #A-500 UPDATE account SET money = money+500 WHERE name = ‘李四’ #B+500

提交事务 事务正确无误时提交,提交后就持久化保存了

COMMIT;

事务提交失败时回滚事务

ROLLBACK;

事务回滚后开启事务自动提交 恢复默认值

SET autocommit = 0

<a name="e4f22460"></a>
### 2.3、数据库索引
> **所有内容均以innoDB为数据库引擎**
<a name="ef6604ed"></a>
#### 2.3.1、索引及其使用原则
> 索引 : 帮助MySQL高效获取数据的数据结构
-  **主键索引         (primary key)** 
   - 单表唯一标识,一个表只有一个主键,不可重复
-  **唯一索引         (unique key)** 
   - 避免重复列出现,唯一索引可以重复,多个列可以识别唯一索引
-  **常规索引         (Key/index)** 
   - 默认的,index key关键字设置
-  **全文索引          (FullText)** 
   -  在特定数据库引擎下才有 MyISAM 
   -  快速定位 
> 索引的优劣及使用场合
-  优点 
   -  **索引能大大减小服务器扫描的数据量，从而加快索引速度** 
   -  **索引让服务器由随机IO查询变为顺序定位查询，避免服务器排序和创建临时表** 
   -  索引查询，使用优化隐藏器，提高服务器性能 
   -  总的来说，就是索引能够加快查询速度，提高服务器性能 
-  缺点 
   -  创建和维护索引需要消耗大量时间，随着数量的增加随之增加 
   -  索引需要占用除数据存储之外的物理空间，建立聚簇索引所需空间更大 
   -  表中数据进行增删改查时，索引也要动态更新，维护成本较高 
-  适用场景 
   - 大批量数据，最少超过5w个数据使用索引才能体现得出他的作用
   - 修改不频繁的数据，修改不频繁的数据创建索引，查询效率高，维护成本低
   - 经常需要搜索的数据列，创建索引加快查询速度
   - 数据量大的主键，强制该列的唯一性和组织表中数据的排列结构
   - 经常使用jion关联的数据列，创建外键索引
   - 经常需要判断范围between进行搜索的数据列
   - 经常需要排序order by的数据列
   - 经常使用条件查询where的数据列
   - 索引不是越多越好
<a name="c32b250f"></a>
#### 2.3.2、索引结构
-  MySQL索引结构中，底层索引有 
   -  B-Tree 
   -  B+Tree 
   -  Hash 
-  B-Tree：B数，多路搜索树，树高一层意味着多一次I/O 
   -  关键字集合分布在整棵树中 
   -  任何一个关键字只会出现在一个结点中 
   -  搜索有可能在非叶子结点结束 
   -  搜索性能等价于关键字全集做一次二分查找 
   -  自动层次控制 
-  B+Tree：B数的升级版 
-  索引关键字都出现在叶子结点的链表中，也称稠密索引，是一种有序索引 
-  不可能在非叶子结点命中 
-  更适合用于文件索引系统 
-  Hash：哈希值 
   - 哈希索引采用了哈希算法，将值换算成哈希值，具有唯一性，因此只需索引一次就能查询到数据
<a name="4e0172de"></a>
#### 2.3.4、索引分类与创建
> 索引分为逻辑索引和物理索引
-  逻辑索引 
   - 按功能，索引数列等划分 
      -  按数列划分 
      -  单例索引：一个索引只包含一个列，一个表可以有多个列 
      -  组合索引：一个组合可以包含两个及以上的列 
-  索引创建

追加索引

创建主键索引 一张表只能有一个主键

alter table tableName add primary key(column_list);

创建唯一索引 一张表可以有多个为一键，但只能用一个作为索引的唯一键

create unique index indexName on tableName(‘字段名’(length)); alter table tableName add unique(column_list);

普通索引 一张表可以有多个普通索引键，可以重复

create index indexName on tableName(‘字段名’(length)); alter table tableName add index indexName(‘字段名’(length));

普通组合索引

create index indexName on table_name(column_1,column_2);

全文索引

fulltext key keyName(column1,column2);

追加创建

create fulltext index keyNameon tableName(column1,column2); alter table tableName add fulltext index keyName(column1,column2);

#

创建表时直接创建

create table table_name( id int(12) not null, column_name varchar(16) not null,

#创建索引
index index_name(column_name)
#创建前缀索引
index index_name(id,column_name);
#全文索引
fulltext key keyName(column1,column2);

)engine=innoDB default chatset=utf8;

删除索引

drop index index_name on tableName;

修改表结构索引

alter table tableName drop index indexName;

查看所有信息

show index from tableName;

-  物理索引 
   -  聚簇索引 
      - 它不是单独的一种索引类型，而是一种数据存储方式，依靠B+数来实现，提高某个属性的查询速度
   -  优点 
      - 数据访问更快，因为数据和索引都是在同一个B+树中
      - 聚簇索引对于主键的排序查找和范围查找速度极快
   -  缺点 
      - 插入速度严重依赖插入顺序，要依托主键自增长才能达到最高效
      - 更新主键的成本高，因为存储直接放到物理层了
      - 二级索引需要两次索引查找
   -  非聚簇索引 
      - 数据和索引分开，在B+数中存的并不是数据包记录
<a name="a571c96f"></a>
#### 2.3.5、索引查询
> 所有举例**排除覆盖索引**
-  不分逻辑索引和物理索引

普通查询

select * from tableName;

索引查询

explain select * tableName;

explain的使用 —不论如何使用查询条件都适用，但一定要是排除覆盖索引

<a name="cc91ac4a"></a>
#### 2.3.6、伪数据模拟查询
-  执行SQL脚本创建伪数据，插入查询

创建数据库表

CREATE TABLE user( id INT(12) NOT NULL AUTO_INCREMENT COMMENT ‘用户id’, name VARCHAR(100) DEFAULT ‘’ COMMENT’用户名’, phone INT(30) COMMENT ‘电话’, gender BOOLEAN DEFAULT ‘0’ COMMENT ‘性别’, password VARCHAR(100) NOT NULL COMMENT ‘密码’, age INT(5) DEFAULT ‘0’ COMMENT ‘年龄’, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(id) )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT=’用户表’;

插入数据

删除函数头

DROP FUNCTION IF EXISTS mock_data;

伪代码标志，必须写

delimiter $$

创建模拟数据函数并设置返回值类型

create function mock_data() returnS int

创建函数体

begin

定义100000个数据

declare num int default 100000;

定义起始变量

declare i int default 0;

定义循环自增

while i< num do

插入函数体

INSERT INTO `user`(`name`, `phone`,`gender`,`password`,`age`) VALUES
    (CONCAT('用户',i),CONCAT('18',FLOOR(RAND()*((99999999-                                            10000000)+10000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
set i++

循环结束

end while; return i; end;

查看数据生成情况

SELECT mock_data();

普通查询

SELECT * FROM user WHERE name=’用户99949’;

解析查询

EXPLAIN SELECT * FROM user WHERE name = ‘用户9949’;

-  生成100w条数据的时间

查看数据生成情况

SELECT mock_data();

<br />    ![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406150623255.png#crop=0&crop=0&crop=1&crop=1&id=NZdwQ&originHeight=380&originWidth=888&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  普通查询及结果的时间

普通查询

SELECT * FROM user WHERE name=’用户99949’;

![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406150533514.png#crop=0&crop=0&crop=1&crop=1&id=TeQLr&originHeight=224&originWidth=757&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
-  解析普通查询

解析查询

EXPLAIN SELECT * FROM user WHERE name=’用户99949’;

<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406151259405.png#crop=0&crop=0&crop=1&crop=1&id=xfqZF&originHeight=207&originWidth=1122&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  创建索引的时间

创建索引

CREATE INDEX user_name ON user(name); ALTER TABLE user ADD INDEX user_name(name);

<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406151703076.png#crop=0&crop=0&crop=1&crop=1&id=kt2xX&originHeight=254&originWidth=900&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  索引查询及结果的时间

索引查询

SELECT * FROM user WHERE name=’用户96789’;

<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406151853704.png#crop=0&crop=0&crop=1&crop=1&id=xwG2k&originHeight=215&originWidth=754&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  解析索引查询

解析索引查询

EXPLAIN SELECT * FROM user WHERE name=’用户96789’;

<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406152141473.png#crop=0&crop=0&crop=1&crop=1&id=yqXun&originHeight=261&originWidth=1312&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406152335144.png#crop=0&crop=0&crop=1&crop=1&id=Xpaki&originHeight=225&originWidth=1292&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
<a name="f9c8b710"></a>
### 2.4、高级函数使用
-  **if()** 
   -  if(判断条件，真，假) 
   -  现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量  
| id | device_id | gender | age | university | gpa |
| --- | --- | --- | --- | --- | --- |
| 1 | 2138 | male | 21 | 北京大学 | 3.4 |
| 2 | 3214 | male |  | 复旦大学 | 4 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 |
| 6 | 2131 | male | 28 | 山东大学 | 3.3 |
| 7 | 4321 | male | 26 | 复旦大学 | 3.6 |

SELECT IF(age>=25,’25岁及以上’,’25岁以下’) AS age_cut, COUNT(device_id) AS number FROM user_profile GROUP BY age_cut

| age_cut | number |
| --- | --- |
| 25岁以下 | 4 |
| 25岁及以上 | 3 |
-  **case** 
   -  case when 条件 then 结论 else 其他结论 end 
   -  现在运营想要将用户划分为**20岁以下，20-24岁，25岁及以上**三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若**年龄为空**请返回**其他**。）  
| id | device_id | gender | age | university | gpa | question_cnt | answer_cnt |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | 2138 | male | 21 | 北京大学 | 3.4 | 2 | 12 |
| 2 | 3214 | male |  | 复旦大学 | 4 | 5 | 25 |
| 3 | 6543 | female | 20 | 北京大学 | 3.2 | 3 | 30 |
| 4 | 2315 | female | 23 | 浙江大学 | 3.6 | 1 | 2 |
| 5 | 5432 | male | 25 | 山东大学 | 3.8 | 15 | 70 |
| 6 | 2131 | male | 28 | 山东大学 | 3.3 | 7 | 13 |
| 7 | 4321 | male | 26 | 复旦大学 | 3.6 | 6 | 52 |

select device_id,gender, case when age<20 then '20岁以下' when age<25 then '20-24岁' when age>=25 then ‘25岁及以上’ else ‘其他’ end age_cut from user_profile;

| device_id | gender | age_cut |
| --- | --- | --- |
| 2138 | male | 20-24岁 |
| 3214 | male | 其他 |
| 6543 | female | 20-24岁 |
| 2315 | female | 20-24岁 |
| 5432 | male | 25岁及以上 |
| 2131 | male | 25岁及以上 |
| 4321 | male | 25岁及以上 |
-  **day()** 
   -  现在运营想要计算出**2021年8月每天用户练习题目的数量**，请取出相应数据  
| id | device_id | question_id | result | date |
| --- | --- | --- | --- | --- |
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |

Select day(date) as day, count(question_id) as question_cnt From question_practice_detail Where year(date)=2021 and month(date)=08 Group by day;

| day | question_cnt |
| --- | --- |
| 13 | 5 |
| 14 | 2 |
| 15 | 3 |
| 16 | 1 |
| 18 | 1 |
-  **substring_index** 
   -  现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果  
| device_id | profile | blog_url |
| --- | --- | --- |
| 2138 | 180cm,75kg,27,male | http:/url/bigboy777 |
| 3214 | 165cm,45kg,26,female | http:/url/kittycc |
| 6543 | 178cm,65kg,25,male | http:/url/tiger |
| 4321 | 171cm,55kg,23,female | http:/url/uhksd |
| 2131 | 168cm,45kg,22,female | http:/urlsy |

select substring_index(profile,’,’,-1) as gender,count(device_id) from user_submit group by gender

— 函数解析 1、locate(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0； 2、position(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同； 3、left(str, length)：从左边开始截取str，length是截取的长度； 4、right(str, length)：从右边开始截取str，length是截取的长度； 5、substring_index(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串; 6、substring(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符； 7、replace(str, n, m)：将字符串str中的n字符替换成m字符； 8、length(str)：计算字符串str的长度。

| gender | number |
| --- | --- |
| male | 2 |
| female | 3 |
   -  现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select substring_index( substring_index(profile, ‘,’, -2),’,’,1) age, count(device_id) number from user_submit group by age;

— 解析 SUBSTRING_INDEX(profile,’,’,-2)只是27，male substring_index( substring_index(profile, ‘,’, -2),’,’,1) 是 27

| age | number |
| --- | --- |
| 27 | 1 |
| 26 | 1 |
| 25 | 1 |
| 23 | 1 |
| 22 | 1 |
<a name="2b20dc6e"></a>
## 三、MySQL高级
<a name="03a34865"></a>
### 3.1、数据库约束机制
<a name="14174145"></a>
#### 3.1.1、三大范式
> 数据库设计规范主要以下几点
- 信息不重复
- 更新无异常
- 插入无异常
- 删除无异常
> 三大范式
-  **第一范式** 
   -  原子性：保证每一列数据都不可再分割<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406200324629.png#crop=0&crop=0&crop=1&crop=1&id=Vnqh2&originHeight=628&originWidth=970&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  **第二范式** 
   -  必须满足第一范式的原子性，确保数据库表中每一列都与主键相关，做到一张表之描述一件事情<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406200745644.png#crop=0&crop=0&crop=1&crop=1&id=UBzco&originHeight=624&originWidth=1182&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  **第三范式** 
   -  在第一/第二范式基础上，只与主键直接相关，同样一张表只能表述一个事情<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210406201117733.png#crop=0&crop=0&crop=1&crop=1&id=n9cQw&originHeight=623&originWidth=1085&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  **规范与性能** 
   - 鱼和熊掌不可兼得
   - 在Ali数据库规范中强调，数据库表关联不得超过3张
   - 范式越高，性能越渣
<a name="fac4489c"></a>
#### 3.1.2、数据库引擎的区别
> InnoDB AND MyISAM
-  操作区别  
|  | MyISAM | InnoDB |
| --- | --- | --- |
| 事务操作 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大, 2*MyISAM |
| 读取速度 | 较快 | 相比MySIAM较慢 |
| 安全性 | 较低 | 较高 |
| CRUD操作 | 高频查询最优 | CRUD各项性能极佳 |
-  使用场景 
   - InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
   - MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
<a name="1cb71f83"></a>
### 3.2、JDBC的使用
<a name="0039d10a"></a>
#### 3.2.1、第一个JDBC程序
> JDBC在J3EE中是一个重难点，桥接了java与数据库操作
-  以Maven项目连接MySQL为例，需要以下依赖 
```xml
    <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
     </dependency>

• Java实现连接

       //1.加载驱动
       Class.forName("com.mysql.jdbc.Driver");
       //2.填充用户信息和url
       String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
       String name = "root";
       String passworld = "123456";
       //3.连接成功
       Connection connection = DriverManager.getConnection(url, name, passworld);
       //4.执行SQL对象
       Statement statement = connection.createStatement();
       //5.执行SQL语句
       String sql ="select * from users";
       ResultSet resultSet = statement.executeQuery(sql);
       while (resultSet.next()){
           System.out.println("id="+resultSet.getObject("id"));
           System.out.println("name="+resultSet.getObject("name"));
           System.out.println("pwd="+resultSet.getObject("pwd"));
           System.out.println("email="+resultSet.getObject("email"));
           System.out.println("birthday="+resultSet.getObject("birthday"));
           System.out.println("------------------------------------------------------");
       }
       //6.关闭数据库连接，释放连接
       resultSet.close();
       statement.close();
       connection.close();

• 途中会出现的问题
  • Communications link failure（通信链路故障）
• 解决方案 ```java //方法一： url: jdbc:mysql://xxx.xx.xx.xx:3306/xxxxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false

方法二： url: jdbc:mysql://xxx.xx.xx.xx:3306/xxxxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8

<a name="31029c46"></a>
#### 3.2.2、JDBC中的对象解释
<a name="a8190908"></a>
##### 1、加载驱动
```java
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");

• 在底层注册Driver()，在使用时相当于固定写法
  

2、获取连接

//3.连接成功
Connection connection = DriverManager.getConnection(url, name, passworld);
url=//jdbc:mysql://主机地址：3306/数据库名？参数1&参数2&.....
//connection 代表数据库
connection commit()    
//事务提交
connection rollback()
//事务回滚
connection setAutoCommit()  
//数据库设置自动提交

3、执行SQL对象

• Statement对象用于向数据库发生SQL语句，完成数据库的增删改查只需要Java向MySQL发送Statement对象操作即可

//4.执行SQL对象
Statement statement = connection.createStatement();

• 在JDBC中有两种SQL执行对象
  • Statement执行SQL对象
  • PrepareStatement执行SQL对象

//查询返回操作
statement.executeQuery()
//执行数据修改
statement.executeUpdate()  
//可以执行增删改查任意操作，但是效率极差
statement.execute();
//执行SQL
 statement.execute()  
//可以执行SQL所有操作

4、关闭数据库释放资源

//6.关闭数据库连接，释放连接
        resultSet.close();
        statement.close();
        connection.close();

• 必须关闭，否则会浪费内存资源

3.2.3、JdbcUtils工具类的实现

• JdbcUtils工具类可实现代码复用性，在一定程度上性能

package com.iflytek.mysql_02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
 * @author 黄果
 * @date2021/4/622:58
 * @title 现世安稳，岁月静好，佛祖保佑，永无bug!
 */
//工具类
public class JdbcUtils {
    private static String driver =null;
    private static String url =null;
    private static String username =null;
    private static String pqssworld =null;
    static {
        try {
            //返回的是一个流文件
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            //获取连接信息
            driver =properties.getProperty("driver");
            url =properties.getProperty("url");
            username =properties.getProperty("username");
            pqssworld =properties.getProperty("passworld");
            //加载驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, pqssworld);
        return connection;
    }
    //释放连接资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet !=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement !=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection !=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3.3、SQL安全问题

3.3.1、SQL注入

• SQL注入是一种网络安全层次的攻击方式，网络层难以阻断
• SQL漏洞被**or**攻击导致数据泄露
  • 恒等输入，无法判断准确性

    //SQL注入
    @Test
    public void SQLTset(){
       login("'or'1=1","'or'1=1");
    }

    ```java package com.iflytek.mysql_03;

import com.iflytek.mysql_02.utils.JdbcUtils; import org.junit.jupiter.api.Test;

import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement;

/**

• @author 黄果
• @date2021/4/70:32

• @title 现世安稳，岁月静好，佛祖保佑，永无bug! */ public class LoginTest {

  public static void login(String name,String pwd){

   Connection connection =null;
   Statement statement = null;
   ResultSet resultSet = null;
   try{
       Connection conn = JdbcUtils.getConnection();
       Statement st = conn.createStatement();
       String sql ="select * from users where `name`='"+name+"'and `pwd`='"+pwd+"'";
       //判断数据插入状态
      resultSet= st.executeQuery(sql);
       while (resultSet.next()){
           System.out.println(resultSet.getString("name"));
           System.out.println(resultSet.getString("pwd"));
           System.out.println("-------------------------------------");
       }
   } catch (Exception e) {
       e.printStackTrace();
   }finally {
       JdbcUtils.close(connection,statement,resultSet);
   }

  } //普通登陆 @Test public void LoginTest(){

   login("小黄","123123");

  }

  //SQL注入 @Test public void SQLTset(){

   login("'or'1=1","'or'1=1");

  } } ```

3.3.2、PreparedStatement对象

• 不同与Statement对象的是PreparedStatement对象需要手动传值，及拼接方式的不同
• 安全系数更高，在一定程度上可以避免SQL注入

package com.iflytek.mysql_03;
import com.iflytek.mysql_02.utils.JdbcUtils;
import org.junit.jupiter.api.Test;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.*;
/**
 * @author 黄果
 * @date2021/4/623:24
 * @title 现世安稳，岁月静好，佛祖保佑，永无bug!
 */
public class InsertTest {
    @Test
    public void InsertTest(){
        Connection connection =null;
        PreparedStatement statement = null;
        try{
            //获取连接对象
            connection = JdbcUtils.getConnection();
            //定义SQL语句
            String sql ="insert into users(`id`,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)";
            //预编译SQL
            statement = connection.prepareStatement(sql);
            //手动传参
            statement.setInt(1,5); //传入id
            statement.setString(2,"小陈");
            statement.setString(3,"123321");
            statement.setString(4,"369852@qq.com");
            statement.setTimestamp(5,new Timestamp(( new  java.util.Date()).getTime()));
            //返回插入状态
            int i =statement.executeUpdate();
            if(i>0){
                System.out.println("插入成功!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ResultSet resultSet = null;
            JdbcUtils.close(connection,statement,resultSet);
        }
    }
}

• PreparedStatement对象与Statement对象对比

//Statement对象
public class InsertTest {
    @Test
    public void JdbcUtilsTest(){
        Connection connection =null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            //获取连接对象
            connection  = JdbcUtils.getConnection();
            //编译SQL
            statement = connection.createStatement();
            //定义SQL语句
            String sql ="insert into users values(4,'小三','147852','123654@qq.com','2000-08-23')";
            //判断数据插入状态
            int i =st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(connection,statement,resultSet);
        }
    }
}
//PreparedStatement对象
public class InsertTest {
    @Test
    public void InsertTest(){
        Connection connection =null;
        PreparedStatement statement = null;
        try{
            //获取连接对象
            connection = JdbcUtils.getConnection();
            //定义SQL语句
            String sql ="insert into users(`id`,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)";
            //预编译SQL
            statement = connection.prepareStatement(sql);
            //手动传参
            statement.setInt(1,5); //传入id
            statement.setString(2,"小陈");
            statement.setString(3,"123321");
            statement.setString(4,"369852@qq.com");
            statement.setTimestamp(5,new Timestamp(( new  java.util.Date()).getTime()));
            //返回插入状态
            int i =statement.executeUpdate();
            if(i>0){
                System.out.println("插入成功!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ResultSet resultSet = null;
            JdbcUtils.close(connection,statement,resultSet);
        }
    }
}
//不同点
1.连接流程不同
2.SQL语句的值定义方式不同

3.4、浅谈锁机制

什么是锁

• 简单来说就是，就是数据库为了保证数据的一致性，是各种资源共同资源在被访问时变得有序设计的一种规则，主要用于多用户环境下保证数据库完整性和一致性
• 在MySQL中，InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
• 锁主要解决处理高并发问题

概述

MySQL锁大致分为三类：

* 表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
* 行级锁：开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高
* 页面锁：开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般

3.4.1、InnoDB行锁

1、记录锁(Record Lock)

• 记录锁作用封锁记录，外界无法操作，也称行锁
• 在默认情况下，只有加了索引的前提下，行锁才生效
• 不加索引时，生效的是表锁，全表锁定
• 使用记录锁不会产生间隙 ```

  创建索引

手动开启事务

begin; update student set school name=’张三’ where id=1; commit; =>success

不创建索引

begin; update student set school name=’张三’ where id=1; commit; =>error

-  测试

/ 开启事务1 / BEGIN; / 查询 id = 5 的数据并加记录锁 / SELECT FROM account WHERE id = 5 FOR UPDATE; / 延迟30秒执行，防止锁释放 */ SELECT SLEEP(30);

注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/ 事务2插入一条 name = ‘小张’ 的数据 / INSERT INTO account (id, name) VALUES (4, ‘小张’); # 正常执行

/ 事务3插入一条 name = ‘小张’ 的数据 / INSERT INTO account (id, name) VALUES (8, ‘小东’); # 正常执行

/ 提交事务1，释放事务1的锁 / COMMIT;

<a name="ed67752d"></a>
##### 2、间隙锁(GAP Lock)
-  **间隙锁是封锁索引记录中的间隔**，或者第一条索引记录之前的**范围**，又或者最后一条索引记录之后的**范围**，不允许在范围内插入数据 
-  **产生间隙锁的条件（RR事务隔离级别下）** 
   - 使用普通索引锁定
   - 使用多列唯一索引
   - 使用唯一索引锁定多行记录
-  **间隙锁设置** 
   -  打开间隙锁 
      - 默认值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。

查看间隙锁状态

show variables like ‘innodb_locks_unsafe_for_binlog’;

![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210410122646194.png#crop=0&crop=0&crop=1&crop=1&id=mCqAF&originHeight=203&originWidth=634&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
      -  关闭间隙锁

在 my.ini 里面的[mysqld]添加

[mysqld] innodb_locks_unsafe_for_binlog = 1

   -  查看隔离级别

查看各级级别

show variables like ‘%iso%’;

<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210410124210894.png#crop=0&crop=0&crop=1&crop=1&id=u5VTq&originHeight=250&originWidth=473&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
   -  表示<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210410125009205.png#crop=0&crop=0&crop=1&crop=1&id=sPCfA&originHeight=263&originWidth=355&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
   -  区间锁测试

/ 开启事务1 / BEGIN; / 查询 id 在 7 - 11 范围的数据并加记录锁 / SELECT FROM account WHERE id BETWEEN 5 AND 7 FOR UPDATE; / 延迟30秒执行，防止锁释放 */ SELECT SLEEP(30);

注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/ 事务2插入一条 id = 3，name = ‘小张1’ 的数据 / INSERT INTO account (id, name) VALUES (2, ‘小张1’); # 正常执行

/ 事务3插入一条 id = 4，name = ‘小白’ 的数据 / INSERT INTO account (id, name) VALUES (4, ‘小白’); # 正常执行

/ 事务4插入一条 id = 6，name = ‘小东’ 的数据 / INSERT INTO account (id, name) VALUES (6, ‘小东’); # 阻塞

/ 事务5插入一条 id = 8， name = ‘大罗’ 的数据 / INSERT INTO account (id, name) VALUES (8, ‘大罗’); # 阻塞

/ 事务6插入一条 id = 9， name = ‘大东’ 的数据 / INSERT INTO account (id, name) VALUES (9, ‘大东’); # 阻塞

/ 事务7插入一条 id = 11， name = ‘李西’ 的数据 / INSERT INTO account (id, name) VALUES (11, ‘李西’); # 阻塞

/ 事务8插入一条 id = 12， name = ‘张三’ 的数据 / INSERT INTO account (id, name) VALUES (12, ‘张三’); # 正常执行

/ 提交事务1，释放事务1的锁 / COMMIT;

   -  加锁产生间隙（5，7），因此（5，7）和（7，11）范围内会被锁住，无法插入数据 
   -  单点间隙锁测试

/ 开启事务1 / BEGIN; / 查询 id = 3 这一条不存在的数据并加记录锁 / SELECT FROM test WHERE id = 3 FOR UPDATE; / 延迟30秒执行，防止锁释放 */ SELECT SLEEP(30);

注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/ 事务2插入一条 id = 3，name = ‘小张1’ 的数据 / INSERT INTO test (id, name) VALUES (2, ‘小张1’); # 阻塞

/ 事务3插入一条 id = 4，name = ‘小白’ 的数据 / INSERT INTO test (id, name) VALUES (4, ‘小白’); # 阻塞

/ 事务4插入一条 id = 6，name = ‘小东’ 的数据 / INSERT INTO test (id, name) VALUES (6, ‘小东’); # 正常执行

/ 事务5插入一条 id = 8， name = ‘大罗’ 的数据 / INSERT INTO test (id, name) VALUES (8, ‘大罗’); # 正常执行

/ 提交事务1，释放事务1的锁 / COMMIT;

   -  单点上锁id=3,即产生了记录锁又产生了记录锁，（1，3）和（3，5）区间被锁住无法插入 
<a name="8f3849ea"></a>
#### 3.4.2、表锁
-  在设计数据库过程中，尽量不要使用表锁 
-  加了表锁会使高数据并发访问变慢，这是一个负优化的过程 
-  创建索引可以有效解决表锁对表数据库操作的影响 
-  表锁的使用场景（**一般都不会出现，除非公司业务大**） 
   - 事务需要大部分或全部更新，结构大，可以使用表锁
   - 事务涉及多表关系，结构复杂，容易引起死锁，造成大量事务回滚

关闭书屋自动提交

set autocommit =0;

对表teacher上锁

lock tables teacher write/read;

上锁写/读操作无法执行

提交事务

commit;

<a name="6dcab3ee"></a>
#### 3.4.3、InnoDB锁操作
> InnoDB锁类型分为 读锁（共享锁），写锁(排他锁)，意向锁，DML锁
- 读锁（共享锁） 
   - 多个事务能读，只有一个事务能写，也称s锁
- 写锁（排他锁） 
   - 一个事务操作后独占，也称x锁，一个事务获取一个数据的写锁，其他事务不能获取该数据的写锁
- 意向锁 
   - 意向锁是一种表级锁
   - 意向共享锁（IS）,一个数据行加共享锁前必须获取该表的意向共享锁
   - 意向排他锁（XS），一个数据行加排他锁前必须获取该表的意向排他锁
   - 意向锁和DML锁都是为了防止事务进行中，DDL语句数据不一致的影响
- DML锁 
   - 开启事务后会自动上一个DML锁，目的就是锁住表结构中的列，不让数据被意外修改
<a name="4051df2e"></a>
#### 3.4.4、乐观锁、悲观所
> 乐观锁 代码实现
-  乐观锁（ Optimistic Locking ） ，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210410131909859.png#crop=0&crop=0&crop=1&crop=1&id=jq1aE&originHeight=581&originWidth=1010&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
-  乐观锁的实现，一般给数据库表加一个`version`字段，就能开启乐观锁，**前提是先查写改**

加乐观锁

select name,version from teacher where id =1;

update teacher set name = ‘张三’，version = verion +1 where id =1 and version =3;

-  **锁的作用** 
   1.  控制并发， 在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突 
   1.  丢失更新，一个事务的更新覆盖了其它事务的更新结果，就是所谓的更新丢失。<br />    例如：用户A把值从6改为2，用户B把值从2改为6，则用户A丢失了他的更新。 
   1.  脏读，当一个事务读取其它完成一半事务的记录时，就会发生脏读取。<br />例如：用户A,B看到的值都是6，用户B把值改为2，用户A读到的值仍为6。 
-  **并发锁机制** 
   - 1.悲观锁：指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态
   - 2.乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。
> 悲观锁  SQL操作自带
- 只要检测到SQL操作，就会上锁，就成为悲观锁
<a name="857d02d9"></a>
#### 3.4.5、等待锁、死锁
-  等待锁： 
   - 一个事务过程中产生的锁，在进程中其他事务要等待上一个事务释放锁后，才能获取资源，如果事务未释放资源，那么就会一直等待下去

控制上锁时间的参数 单位=s

innoDB lock_wait_timeout

-  死锁： 
   - **必须有两个或两个以上的进程**在执行过程中，因争取资源造成了相互等待资源释放的现象，锁资源请求产生了回路现象
   - InnoDB可以自动检测死锁并回滚事务（尽量不要让死锁现象发生，不然业务就没了）
<a name="84d9e4bd"></a>
#### 3.4.6、死锁及处理方法
- 不同程序并发处理一个表或设计多行记录时，尽量约定使用相同顺序的访问表，可减少死锁的发生
- 业务中尽量采用小事务处理，灵活轻便已处理
- 同一个事务尽量做到一次锁定需要的所有资源
- 容易发生死锁的业务，可以尝试升级锁的力度
<a name="0cb61063"></a>
### 3.5、JDBC操作事务
-  事务操作是一个严格的过程，在整个事务处理中，隔离性是最容易出问题的，容易出现出现**脏读，不可重复读，幻读**等问题 
-  已转账流程为例<br />![](https://gitee.com/hg14150/blogiamges/raw/master/img/image-20210407224127545.png#crop=0&crop=0&crop=1&crop=1&id=CnqL0&originHeight=194&originWidth=502&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
```java
//转账过程
public class TransactionTest {
    @Test
    public void TransactionTest(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;
        try {
            //获取连接对象
            connection= JdbcUtils.getConnection();
            //关闭数据库事务自动提交
            connection.setAutoCommit(false);
            //定义SQL
            String sql = "update account set money = money + 200 where `name` = '张三'";
            //预编译SQL
            statement=connection.prepareStatement(sql);
            String sql2 = "update account set money = money - 200 where `name` = '李四'";
            //预编译SQL
            statement=connection.prepareStatement(sql2);
            //执行SQL提交
            statement.executeUpdate();
            //事务提交
            connection.commit();
            //打印结果
            System.out.println("转账成功！！！");
        } catch (Exception e) {
            try {
                //事务提交失败就回滚
                connection.rollback();
                e.printStackTrace();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }
    }
}