MySQL基础重要知识点及其面试点--第三篇-CSDN博客

本文链接：https://blog.csdn.net/public_toString/article/details/132835406

视图

视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视

图的查询中使用的表，并且是在使用视图时动态生成的。

语法

1). 创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [

CASCADED | LOCAL ] CHECK OPTION ]

2). 查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;

3). 修改

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]

4). 删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称]

演示示例：

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;
-- 查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;
-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;
-- 删除视图
drop view if exists stu_v_1;

注：视图是可以插入、更新数据的。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合。

1).创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])

BEGIN

-- SQL语句

END ;

2). 调用

CALL 名称 ([ 参数 ]);

3). 查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指
定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

4). 删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

演示示例

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
select count(*) from student;
end;
#或者
DELIMITER //
CREATE PROCEDURE p_user()
BEGIN
SELECT * FROM t_user;
END//
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;
-- 删除
drop procedure if exists p1;

变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方
式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

注：1、如果没有指定SESSION|GLOBAL,默认是SESSION。

2、全局变量：针对于所有会话有效，会话变量：会话变量针对于单个会话，在另外一个会话窗口就不生效了。

3、mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

演示示例

-- 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量

名" 使用就可以。其作用域为当前连接。

赋值

SET @var_name = expr
SET @var_name := expr

查看

select @var_name

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的

局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

声明

DECLARE 变量名 变量类型 [DEFAULT ... ] ;

赋值

SET 变量名 = 值 ;

演示示例:

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p2();

参数

1). 介绍

参数的类型，主要分为以下三种：IN、OUT、INOUT。

演示示例

#参数in、out用法
create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);

#参数inout用法
create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;

游标

游标（CURSOR）是用来存储查询结果集的数据类型。

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句 ;

打开游标

OPEN 游标名称 ;

获取游标记录

FETCH 游标名称 INTO 变量 [, 变量 ] ;

关闭游标

CLOSE 游标名称 ;

演示示例

#根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
#（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
#(id,name,profession)中。

create procedure p11(in uage int)
begin
#普通变量的声明应该在游标声明之前
declare uname varchar(100);
declare upro varchar(100);

declare u_cursor cursor for select name,profession from tb_user where age <=
uage;

-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;

drop table if exists tb_user_pro;

create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);

open u_cursor;

#执行能够插入成功但是会报错，使用条件处理程序解决报错问题
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;

close u_cursor;

end;

#调用存储过程
call p11(30);

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

语法

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;

handler_action的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序

condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

触发器

指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。使用别名OLD和NEW来引用触发器中发生变化的记录内容，在update触发器中，old代表更新前的那条数据，new代表更新后的那条数据。现在触发器还只支持行级触发，不支持语句级触发。

创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;

查看

SHOW TRIGGERS ;

删除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数
据库 。

演示示例


#在向tb_suer表插入数据时触发向user_logs插入数据的触发器，new代表tb_user新插入的那条数据
delimiter //
create trigger tb_user_insert_trigger
after insert on tb_user for each row

begin

insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'insert', now(), new.id, concat('插入的数据内容为:
id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',
profession=', NEW.profession));
end //

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表。

表级锁：每次操作锁住整张表。

行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语

句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整

性。

语法

加全局锁

flush tables with read lock ;

释放锁

unlock tables ;

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

对于表级锁，主要分为以下三类：

表锁

对于表锁，分为两类：

表共享读锁（read lock）

表独占写锁（write lock）

元数据锁（meta data lock，MDL）

意向锁

表锁

语法

加锁：lock tables 表名... read/write。

释放锁：unlock tables / 客户端断开连接。

读锁特点：不会阻止读，但会阻止写操作

写锁特点：会阻塞其他客户端的读写操作，不会阻止当前（即加锁的客户端）客户端的读写操作

元数据锁（MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维

护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与

DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁；当对表结构进行变更操作（alter）时，加MDL写锁。MDL写锁与MDL读锁互斥。可以理解为两个事务在进行增删改查操作时两个事务都可以正常进行，当事务进行增删改查或修改表操作时会自动加MDL锁。但若其中一个事务想修改表结构，则会进入阻塞状态，直到另一个事务提交。