MySQL 数据库 day-06

**步骤1：开启事务

set autocommit=0;

start transaction; 可选

步骤2：编写事务中的sql语句(select insert update delete)

语句1;

语句2;

……

步骤3:结束事务

commit; 提交事务

rollback; 回滚事务(若事务中有执行失败的操作，则操作被撤销)

savepoint 节点名; 设置保存点**

#演示：

#开启事务

SET autocommit=0;

START TRANSACTION;

#编写一组事务的语句

UPDATE boys SET balance=500 WHERE username=‘张无忌’;

UPDATE boys SET balance=1500 WHERE username=‘赵敏’;

#结束事务

COMMIT;

#回滚事务

ROLLBACK;

CREATE DATABASE test;

事务并发问题

---

当同时运行多个事务，当这些事务访问数据库中相同的数据时，

如果没有采取必要的隔离机制，就会导致各种并发问题。类似于java的线程并发。

问题：

脏读：事务1读取了事务2更新但没有被提交的字段

不可重复读： 事务1，2读取了一个字段，然后事务2更新之后，事务1再次读取，值不同了

幻读：事务1读取了一个字段，然后事务2在表中插入了新的行，之后事务1再次读取，就会读出新插入行

**数据库事务的隔离性：数据库系统必须隔离并发运行各个事务的能力，

使他们不会相互影响，避免各种并发问题**

数据库提供的4中事务隔离级别：

READ UNCOMMITTED:读未提交数据 允许事务读取未被其他事务提交的变更，脏读，不可重复读，幻读问题仍然存在

READ COMMITED：读已提交数据 只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读仍然存在

REPEATABLE READ:确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新

可避免脏读和不可重复读，但幻读问题仍然存在

SERILIZBLE：串行化 确保事务可以从一个表中读取相同的行，在这个事务执行期间，禁止其他事务对该表执行插入

更新和删除操作，所有并发问题都可以避免，但性能十分低下

Mysql默认事务隔离级别：REPEATBLE READ

1、每启动一个mysql程序，就会获得一个单独的数据库连接，

每个数据库连接都有一个全局变量 @@tx_isolation,表示当前事务隔离级别

2、查看当前的隔离级别：SELECT @@tx_isolation;

3、设置当前mysql连接的隔离级别：

set transaction ioslation level read committed;

4、设置数据库系统的全局的隔离级别：

set global transaction isolation level read committed;

演示savepoint的使用

搭配ROLLBACK使用

SET autocommit=0;

START TRANSACTION;

DELETE FROM employees WHERE id=25;

SAVEPOINT a; #设置保存点

DELETE FROM emplouees WHERE id=28;

ROLLBACK TO a; #回滚到保存点

#运行结果：25号删除了，28号未删除（回滚了）

delete和truncate

delete支持回滚

truncate不支持回滚

视图

=====================================================================

含义：虚拟表，和普通表一样使用

是通过表动态生成的数据

_**特点：临时性;重复利用

1、实现了sql的语句的重用

2、简化复杂的sql操作，不必知道它的查询细节

3、保护数据，提高安全性**_

一、视图的创建

---

语法：

create view 视图名

as

查询语句;

案例1：查询姓名中包含a字符的员工名、部门名和工种信息

#①创建

CREATE VIEW myv1

AS

SELECT last_name,department_name,job_title

FROM employees e

JOIN departments d ON d.department_id=e.department_id

JOIN jobs j ON j.job_id=e.job_id;

#②使用

SELECT * FROM myv1 WHERE last_name LIKE ‘%a%’;

案例2：查询各部门的平均工资级别

#①创建

CREATE VIEW myv2

AS

SELECT AVG(salary) ag

FROM employees

GROUP BY department_id;

#②使用

SELECT grade_level

FROM myv2

JOIN job_grades

ON ag BETWEEN lowest_sal AND highest_sal;

案例3：查询平均工资最低的部门信息

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

二、视图的修改

---

方式一：★

语法：

create or replace view 视图名

as

查询语句;

特点：若视图不存在则创建，视图存在则修改

SELECT * FROM myv3;

CREATE OR REPLACE VIEW myv3

AS

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;

方式二：

语法：

alter view 视图名

as

查询语句;

ALTER VIEW myv3

AS

SELECT * FROM employees;

三、删除视图

---

语法：

drop view 视图名,视图名,……;

可以一次删除多个视图

DROP VIEW myv1,myv2;

四、查看视图

---

DESC myv3;

SHOW CREATE VIEW myv3;

案例1 创建视图emp_v1,要求查询电话点好以‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1

AS

SELECT last_name,salary,email

FROM employees

WHERE phone_number LIKE ‘011%’;

案例2 创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW emp_v3

AS

SELECT MAX(salary) ms,department_id

FROM employees

GROUP BY department_id

HAVING MAX(salary)>12000;

SELECT d.*,m.ms

FROM departments d

JOIN emp_v3 m

ON m.department_id=d.department_id;

五、视图的更新

---

CREATE OR REPLACE VIEW myv1

AS

SELECT last_name,email

FROM employees;

SELECT * FROM myv1;

1、插入数据

INSERT INTO myv1 VALUES(‘张飞’,‘zhangyd@163.com’);

SELECT * FROM employees;

2、修改数据

UPDATE myv1 SET last_name=‘张无忌’ WHERE last_name=‘张飞’;

3、删除

DELETE FROM myv1 WHERE last_name=‘张无忌’;

_说明：视图的可更新性和视图中查询的定义有关系，一下类型的视图是不能更新的

1、包含一下关键字的sql语句:分组函数、distinct、group by、having、union或者union all

2、常量视图

3、select中包含子查询

4、join

5、from一个不能更新的视图

6、where子句的子查询引用了from子句中的表_

表和视图的对比

视图 ： create view 　　　 只是保存了sql语句 　　增删改查，一般不能增删改

表： 　 create table 　　 　保存了数据 　　　　　增删改查

变量

=====================================================================

系统变量；

全局变量

会话变量

自定义变量：

用户变量

局部变量

一、系统变量

---

说明：变量由系统提供，不是用户定义，shu’yu服务器层面

使用语法：

1、查看所有的系统变量

show global|[session] variables; #默认为会话变量

2、查看满足条件的部分系统变量

show global|[session] variables like ‘%char%’;

3、查看指定的某个系统变量的值

select @@global.系统变量名;

4、为某个系统变量复制

set global|[session] 系统变量名=值;

set @@global|[session].系统变量名=值;

注意：

如果是全局级别，则需要加global，如果是会话级别，则需要加session，不添加的话默认为session

1、全局变量

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的绘画（连接）有效，但不能跨重启

2、会话变量

作用域：仅仅针对于当前会话（连接）有效

二、自定义变量

---

说明：变量是用户自定义的，不是由系统的

使用步骤：

声明

赋值

使用（查看、比较、运算等）

1、用户变量

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

应用在任何地方，也就是begin end里面或者begin end外面

①声明并初始化

SET @用户变量名=值;

SET @用户变量名:=值;

SELECT @用户变量名:=值;

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

方式一：通过 SET 或者 SELECT

SET @用户变量名=值;

SET @用户变量名:=值;

SELECT @用户变量名:=值;

方式二：通过 SELECT INTO

SELECT 字段 INTO 变量名

FROM 表;

③使用（查看用户变量的值）

SELECT @查看用户变量名

#案例：

#声明初始化

SET @name=‘john’;

SET @name=100;

SET @count=1;

#赋值

SELECT COUNT(*) INTO @count

FROM employes;

#查看

SELECT @count #结果：107

2、局部变量

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

应用在begin end中的第一句话

①声明

DECLARE 变量名 类型;

DECLARE 变量名 类型 DEFAULT 值;

②赋值

方式一：通过 SET 或者 SELECT

SET 局部变量名=值;

SET 局部变量名:=值;

SELECT @局部变量名:=值;

方式二：通过 SELECT INTO

SELECT 字段 INTO 局部变量名

FROM 表;

③使用

SELECT 局部变量名;

作用域 定义和使用的位置 语法

用户变量 当前会话 会话中的任何地方 必须加@符号，不用限定类型

局部变量 BEGIN end中 只能在 BEGIN END 中，且为第一句话 一般不加@符号，需要限定类型

存储过程和函数

==========================================================================

存储过程和函数：类似于java的方法

好处：

1、提高代码的重用性

2、简化操作

一、存储过程

---

含义：一组预先编译好的sql语句的集合，理解成批处理语句

好处：

1、提高代码的重用性

2、简化操作

3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程的语法

1、使用

**CREATE PROCEDURE 存储过程名(参数列表)

BEGIN

存储过程体(一组合法的SQL语句)

END**

注意：

1、参数列表包含三部分

参数模式 参数名 参数类型

IN stuname VARCHAR(20)

参数模式

IN :该参数可以作为输入，也就是改该参数需要调用才可传入值

OUT :该参数可以作为输出，也就是该参数可以作为返回值

INOUT :该参数既可以输入也可以作为输出，既可以传入值，也可以返回值

2、如果存储过程体仅仅只有一句话,BEGIN end可以省略

存储过程体中的每条SQL语句的结尾要求必须加分号，

存储过程的结尾可以使用 DELIMITER 重新设置

DELIMITER 结束标记

例： DELIMITER $

2、调用方法

CALL 存储过程名(实参列表);

①、空参列表

案例：插入到admin表中五条记录

DELIMITER $

CREATE PROCEDURE myp1()

BEGIN

INSERT INTO ADMIN(username,password)

VALUES(‘john’,‘0000’),(‘tom’,‘0001’),(‘jack’,‘0000’),(‘petter’,‘0000’),(‘denny’,‘0000’);

END $

②、in

案例1：创建存储过程实现，根据女神名，查询对应的男神名

DELIMITER $

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))

BEGIN

SELECT bo.*

FROM boys bo

RIGHT JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName;

END $

#调用

CALL myp2(‘王语嫣’)$

案例2：创建存储过程 实现用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))

BEGIN

DECLARE result INT DEFAULT 0;#初始化变量

SELECT COUNT(*) INTO result #赋值变量

FROM admin

WHERE admin.username=username

AND admin.password=PASSWORD;

SELECT IF(result>0,‘成功’,‘失败’); #使用变量

END $

#调用

CALL myp3(‘张飞’,8888);

③ out

案例1：根据女神名,返回对应的男神名

CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))

BEGIN

SELECT bo.boyName INTO boyName #赋值

FROM boys bo

INNER JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName;

END $

#调用

SET @bName$ #定义一个全局变量

CALL myp4(‘小昭’,@bName)$

SELECT @bName$

案例2：根据女神名，返回对应的男神名和男神魅力值

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)

BEGIN

SELECT bo.boyName,bo.userCP INTO boyName,userCP #赋值

FROM boys bo

INNER JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName;

END $

#调用

CALL myp5(‘小昭’,@bName,@userCP)$

SELECT @bNanme,@userCP$

⑤inout

案例1：传入a和b两个值，最终a和b都翻倍返回

CREATE PROCEDURE myp6(INOUT a INT,intout b INT)

BEGIN

SET a=a*2;

SET b=b*2;

END $

#调用

SET @m=10;

SET @n=8;

CALL mvp6(@m,@n);

SELECT @m,@n;

面试结束复盘查漏补缺

每次面试都是检验自己知识与技术实力的一次机会，面试结束后建议大家及时总结复盘，查漏补缺，然后有针对性地进行学习，既能提高下一场面试的成功概率，还能增加自己的技术知识栈储备，可谓是一举两得。

以下最新总结的阿里P6资深Java必考题范围和答案，包含最全MySQL、Redis、Java并发编程等等面试题和答案，用于参考~

重要的事说三遍，关注+关注+关注！

更多笔记分享

beautyName;

END $

#调用

SET @bName$ #定义一个全局变量

CALL myp4(‘小昭’,@bName)$

SELECT @bName$

案例2：根据女神名，返回对应的男神名和男神魅力值

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)

BEGIN

SELECT bo.boyName,bo.userCP INTO boyName,userCP #赋值

FROM boys bo

INNER JOIN beauty b ON bo.id=b.boyfriend_id

WHERE b.name=beautyName;

END $

#调用

CALL myp5(‘小昭’,@bName,@userCP)$

SELECT @bNanme,@userCP$

⑤inout

案例1：传入a和b两个值，最终a和b都翻倍返回

CREATE PROCEDURE myp6(INOUT a INT,intout b INT)

BEGIN

SET a=a*2;

SET b=b*2;

END $

#调用

SET @m=10;

SET @n=8;

CALL mvp6(@m,@n);

SELECT @m,@n;

面试结束复盘查漏补缺

每次面试都是检验自己知识与技术实力的一次机会，面试结束后建议大家及时总结复盘，查漏补缺，然后有针对性地进行学习，既能提高下一场面试的成功概率，还能增加自己的技术知识栈储备，可谓是一举两得。

以下最新总结的阿里P6资深Java必考题范围和答案，包含最全MySQL、Redis、Java并发编程等等面试题和答案，用于参考~

重要的事说三遍，关注+关注+关注！

[外链图片转存中…(img-BmCxybc0-1718711814874)]

[外链图片转存中…(img-k5AHs2u9-1718711814875)]

更多笔记分享

[外链图片转存中…(img-bFChMaAD-1718711814875)]