SQL数据库（游标、条件处理程序、存储函数、触发器、存储引擎、索引）

雪落之下

已于 2023-04-17 08:10:22 修改

阅读量365

点赞数

分类专栏：数据库 mysql 笔记文章标签：数据库 sql sqlserver

于 2023-04-14 09:52:29 首次发布

本文链接：https://blog.csdn.net/qq_52847866/article/details/130145251

版权

笔记同时被 3 个专栏收录

16 篇文章 1 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

游标

游标（cursor）是用来存储查询结果集的数据类型。在存储过程和函数中，可以使用游标对结果集进行循环处理。

游标的使用包括游标的声明、open、 fetch和close

A 声明游标

declare 游标名称 cursor for 查询语句;
B 打开游标

open 游标名称;
C 获取游标记录

fetch 游标名称 into 变量
D 关闭游标

close 游标名称

案例：根据传入的参数age参数，查询emp表中，所有的雇员年龄小于age的员工的姓名和职位，薪资，并
将用户的姓名、职位，薪资插入到所创建的一张新表中。

/*
案例： 根据传入的参数age参数，查询emp表中，所有的雇员年龄小于age的员工的姓名和职位，薪资，并将用户
的姓名、职位，薪资插入到所创建的一张新表中。
*/
/*
逻辑：
1 声明一个游标 存储查询结果
2 准备 创建新表的表结构
3 打开游标
4 获取游标中的数据
5 插入数据到新表中
6 关闭游标
*/
create procedure p11(in eage int)
BEGIN
DECLARE ename VARCHAR(20);
DECLARE ejob VARCHAR(20);
DECLARE sal int;
declare e_cursor CURSOR for select `name`,job,salary from emp where age < eage;
drop table if exists tb_emp;
create table if not exists tb_emp(
id int PRIMARY KEY auto_increment,
ename VARCHAR(100),
job VARCHAR(100),
sal int
) ;
-- 打开游标
open e_cursor;
while true do
fetch e_cursor into ename,ejob,sal;
insert into tb_emp VALUES(null,ename,ejob,sal);
end while;
CLOSE e_cursor;
END;
call p11(40);
select `name`,job,salary from emp where age <40
注意：fetch游标的时候 into后边的变量名称不能和字段名称一致

在这里插入图片描述

条件处理程序

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

declare handler_action handler for condition_value
—statement;
handler_action的取值：
continue：继续执行当前程序
exit: 终止程序的执行
condition_value的取值：
sqlstate 状态码：如：02000
sqlwarning 所有以01开头sqlstate代码的简写
not found：所有以02开头sqlwarning的sqlstate代码的简写
sqlexcetion 所有没有被sqlwarning或not found所捕获的sqlstate代码的简写

第一种方式：通过状态码处理（了解）

create procedure p11(in eage int)
BEGIN
DECLARE ename VARCHAR(20);
DECLARE ejob VARCHAR(20);
DECLARE sal int;
declare e_cursor CURSOR for select `name`,job,salary from emp where age < eage;
-- 声明一个条件处理 当sql状态码为02000时，就关闭游标
DECLARE exit HANDLER FOR SQLSTATE '02000' CLOSE e_cursor;
drop table if exists tb_emp;
create table if not exists tb_emp(
id int PRIMARY KEY auto_increment,
ename VARCHAR(100),
job VARCHAR(100),
sal int
) ;
-- 打开游标
open e_cursor;
while true do
fetch e_cursor into ename,ejob,sal;
insert into tb_emp VALUES(null,ename,ejob,sal);
end while;
CLOSE e_cursor;
END;
call p11(40);

第二种方式：通过sqlstate的代码简写方式 not found

create procedure p11(in eage int)
BEGIN
DECLARE ename VARCHAR(20);
DECLARE ejob VARCHAR(20);
DECLARE sal int;
declare e_cursor CURSOR for select `name`,job,salary from emp where age < eage;
-- 声明一个条件处理 当sql状态码为02000时，就关闭游标
DECLARE exit HANDLER FOR NOT found CLOSE e_cursor;
drop table if exists tb_emp;
create table if not exists tb_emp(
id int PRIMARY KEY auto_increment,
ename VARCHAR(100),
job VARCHAR(100),
sal int
) ;
-- 打开游标
open e_cursor;
while true do
fetch e_cursor into ename,ejob,sal;
insert into tb_emp VALUES(null,ename,ejob,sal);
end while;
CLOSE e_cursor;
END;
call p11(40);

存储函数

存储函数是有返回值的存储过程。存储函数的参数类型只能是in类型

create function 存储函数的名称（参数列表）
returns type [characteristic... ]
begin
语句;
return ...;
end;

characteristic说明：
deterministic：相同的输入参数总是产生相同的结果
no sql 不包含sql语句
reads sql data ：包含读取数据的语句，但是不包含写入数据的语句

-- 从1 累加到n的值 END
create FUNCTION fun1(n int)
RETURNS int DETERMINISTIC
BEGIN
declare sum int default 0;
while n > 0 DO
set sum := sum + n;
set n := n -1;
END WHILE ;
RETURN sum;
END;
SELECT FUN1(50);

触发器

触发器指的是在insert /update/delete之前或者之后，触发并执行触发器中定义的sql语句集合。使用别名old 和new来引用触发器中发生变化的记录内容。这和其他的数据库类型。现在触发器只支持行级触发，不支持语句级。
触发器的类型：

insert型触发器：new表示将要或者已经新增的数据
update型的触发器：old表示修改之前的数据，new表示修改之后的数据
delete型的触发器：old表示将要删除或已经删除的数据

1.创建触发器

create trigger trigger—name
before/after insert/update/delete -- 指定触发的时机
on table-name for each row -- 行级触发器
begin
statement；
end;

2.查看

show triggers;

3.删除触发器

drop trigger 【db】.trigger-name -- db不写 默认为当前库

案例：通过触发器记录emp的数据的变更日志，将变更日志插入到日志表中emp_logs 包含增加，删除，修
改

-- 创建触发器
-- 插入数据的触发器
create TRIGGER tb_emp_insert_trigger
after insert on emp for each ROW
BEGIN
INSERT INTO emp_logs(id,operation,option_time,option_id,option_params)
VALUES(null,'insert',now(),new.id,concat('插入的数据内容为：
id=',new.id,'name=',new.name,'job=',new.job,'salary=',new.salary));
END;
INSERT into emp VALUES(null,'张三',35,'经理',20000,NOW(),1,1);
-- 修改触发器
create TRIGGER tb_emp_update_trigger
after update on emp for each ROW
BEGIN
INSERT INTO emp_logs(id,operation,option_time,option_id,option_params)
VALUES(null,'UPDATE',now(),new.id,concat('更新之前的数据内容为：
id=',old.id,'name=',old.name,'job=',old.job,'salary=',old.salary));
END;
-- 测试
update emp set name ='李四' ,job ='副总',salary=25000 where id=21;
update emp set salary = salary + 500 ;

存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎
可以使用 SHOW ENGINES 命令可以查看Mysql的所有执行引擎我们可以到默认的执行引擎是innoDB 支持事务，行级锁定和外键。
MyISAM**：**Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎
Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。
Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差
Federated ：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

-- 查询当前数据库支持的存储引擎：
show engines;
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;
-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table student;
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

– 修改MySQL默认存储引擎方法
1.关闭mysql服务
2.找到mysql安装目录下的my.ini文件：
3.找到default**-storage-engine=INNODB 改为目标引擎，
如：default-storage-engine=**MYISAM
4 启动mysql服务

索引

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，
MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如
果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将
会节省很大一部分时间。
索引类似一本书的目录，比如要查找’student’这个单词，可以先找到s开头的页然后向后查找，这个就类似索
引。

sql优化： 1 在查询的时候尽量明确要插叙的字段，不要使用* 2 对于高频查询字段一定要建立索引

索引的分类

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有Hash索引和B+Tree索引哈希索引：在这里插入图片描述
按照功能划分：

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了
查询数据更快一点。

create database mydb5;
use mydb5;
-- 方式1-创建表的时候直接指定
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
index index_name(name) -- 给name列创建索引
);
-- 方式2-直接创建
-- create index indexname on tablename(columnname);
create index index_gender on student(gender);
-- 方式3-修改表结构(添加索引)
-- alter table tablename add index indexname(columnname)
alter table student add index index_age(age);

查看表中的所有的索引

show index from emp;

删除索引

drop index 索引名 on 表名
-- 或
alter table 表名 drop index 索引名

唯一索引：
•唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则
列值的组合必须唯一。它有以下几种创建方式

-- 方式1-创建表的时候直接指定
create table student2(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名)
create unique index index_card_id on student2(card_id);
-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num)

主键索引：
每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索
引。主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。
组合索引
•组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索
引，同样的可以建立为普通索引或者是唯一索引。
复合索引的使用复合最左原则

-- 创建索引的基本语法
create index indexname on table_name(column1(length),column2(length));
-- 组合索引
use mydb5;
-- 创建索引的基本语法-- 普通索引
-- create index indexname on table_name(column1(length),column2(length));
create index index_phone_name on student(phone_num,name);
-- 操作-删除索引
drop index index_phone_name on student;
-- 创建索引的基本语法-- 唯一索引
create unique index index_phone_name on student(phone_num,name);

雪落之下

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SQL数据库（游标、条件处理程序、存储函数、触发器、存储引擎、索引）

游标的使用包括游标的声明、open、 fetch和closeA 声明游标declare 游标名称 cursor for 查询语句;B 打开游标open 游标名称;C 获取游标记录fetch 游标名称 into 变量D 关闭游标close 游标名称。
复制链接

扫一扫