MySQL ----- 约束,视图,索引,存储过程,触发器,游标

程序员文章站 2022-03-03 21:01:01

...

7. 约束

约束可在创建表时, 直接设置, 也可以后期修改.

mysql不支持check约束, 但可以通过设置为enum枚举类型达到相同效果.

默认值约束
- 创建时: create table student(gender varchar(10) default '男');
- 添加: alter table student modify column sname varchar(10) default '男';
- 删除: alter table student modify column sname varchar(10);
非空约束
- 创建时: create table student(sname varchar(10) NOT NULL);
- 添加: alter table student modify column sname varchar(10) NOT NULL;
- 删除: alter table student modify column sname varchar(10);
唯一性约束
- 创建时: create table student(id int(3) UNIQUE);
- 添加: alter table student add UNIQUE(id); 或 alter table student modify column id int(3) unique;
- 删除: alter table student DROP INDEX id;
检查约束

为了保证输入数据的有效性, mysql不支持check约束, 但可以通过ENUM值和SET值来实现
主键约束

全局只有一个, 且包含唯一约束+非空约束

通用格式: [constraint <约束名>] primary key [字段名]
- 创建时: create table student(id int(3) primary key); 或:
  - 联合主键: create table student(id int(3),name varchar(10),primary key(id,name));
- 添加: alter table student add primary key(id);
  - 联合主键: alter table student add primary key(id,name);
- 删除: alter table student drop primary key
- 主键自增长, 默认初始值1
  - 一个表中只能有一个自增字段且为主键一部分, 类型必须时整数
  - create table student(id int(3) primary key auto_increment) ;
  - 指定初始值: create table student(id int(3) primary key auto_increment)auto_increment=100;
外键约束

外键可有多个, 即其他表的主键, 外键中字段数和其值类型必须对应相同

外键值可以为空值, 若非空则必须等于父表中的某个值

删除数据时会级联删除.

通用格式: [constraint <约束名>] references key(外键) references 主表(主键)
- 创建时: create table class(class_id int(3) primary key,teacher_id int(3),CONSTRAINT fk_c_t FOREIGN KEY(teacher_id) REFERENCES teacher(id));
- 添加: alter table class add constraint fk_class_teacher foreign key(Tname) references teacher(name);
- 删除: alter table class drop foreign key fk_class_teacher;

8. 视图

视图时一种虚拟存在的表, 实际存储的是视图的定义, 数据都是在使用视图时动态从其他真实表中取出的.

优点: 聚焦指定数据, 复用SQL语句, 简化操作, 提高安全性,可更改数据格式

注意: 创建视图需要足够权限, 视图可以嵌套, 视图不能索引, 不能关联触发器

用于: 格式化输出数据, 复杂的表连接, 过滤数据

8.1 创建

格式: CREATE VIEW <视图名> AS <SELECT语句> select可查询真实表或其他视图

单表: CREATE VIEW view_students_info AS select * from tb_students_info;
多表: CREATE VIEW v_students_info(s_id,s_name,d_id,s_age,s_sex,s_height,s_date) AS select id,name,dept_id,age,sex,height,login_date from tb_students_info; select * from v_students_info

8.2 查看

格式: DESCRIBE <视图名>

字段信息: DESCRIBE v_students_info 即 DESC v_students_info
详细信息: SHOW CREATE VIEW v_students_info \G;
视图的定义存储在information_schema 的 views 表中, select * from information_schema.views table_name='v_studetns_info' \G;

8.3 修改

格式: ALTER VIEW <视图名> AS <SELECT语句>

注意: 可通过视图来更新真实表中的数据, 聚合函数,distinct, group by, having, union, 子查询等会使得视图不可更新.

update view_students_info set age=25 where id=1; 会使和视图相关的真实表中的数据发生更新.

8.4 删除

DROP VIEW <视图名>

9. 索引

表会按设置为 索引 的列值顺序来存储, 这样可快速查询表中有某一特定值的记录;

实质上, 索引是一张列值(指针)与原表索引列值一一对应的有序映射表

优点: 遍历索引列可快速查询, 不像顺序访问要全表扫描

缺点: 占用额外存储空间, 数据改变则索引也会动态维护

9.1 索引类型

索引类型和存储引擎有关

按存储分:

B-树索引

即BTREE索引, B-树只有一个根且叶子间串连,
哈希索引, 默认索引

用散列算法变换成固定长度的散列值, 访问速度快, 但散列计算耗时且不能用于排序,不能部分匹配索引列值, 只支持等值比较

按逻辑分:

普通索引, 没有限制, 允许索引列值为重复值, 空值, 关键字 INDEX 或 KEY.
唯一索引,不能有重复值但可为空值, 关键字 UNIQUE.
主键索引, 不允许重复值和空值, 关键字 PRIMARY KEY, 不能使用create index来创建
空间索引, 作用于空间数据类型, 关键字 SPATIAL, 常用于GEOMETRY地理类型字段
全文索引, 允许重复值和空值, 用于查找文本中的关键字, 只能用于CHAR VARCHAR TEXT 类型字段, 存储引擎必须是MyISAM

9.2 创建

create index专用语法:

格式: CREATE <索引名> ON <表名> <列名> [<长度>] [ ASC | DESC]; 默认ASC升序

普通索引: create INDEX index_id ON student(id);
唯一索引: create UNIQUE INDEX index_id ON student(id);
空间索引: create SPATIAL INDEX index_line ON student(line);
全文索引: create FULLTEXT INDEX index_info ON student(info);

创建表时:

格式: …, key|index [<索引名>] [<索引类型>] (<列名>…);

create table student(id int, height int default null, INDEX(height));
create table student(id int, UNIQUE index(id));
create table student(id int, primary key(height));
create table student(id int, foreign key(height));

9.3 查看

desc <索引名>;
show index from <表名> [from 数据库];
- Non_unique 是否为唯一索引, 0为是
- Seq_in_index 列在索引中的位置, 1开始
- Collation 存储顺序, A升序, NULL无分类
- Cardinality 基数, 即索引中唯一值数目的估计值, 值越大则联合时使用该索引的机会越大
- Sub_part 值的部分作为索引值的字符数, 全部作为索引值则为NULL
- Packed 表示关键值如何压缩, 没压缩则为NULL
- NULL 是否包含NULL值
- Index_type 缩影使用的类型, 如BTREE, FULLTEXT, HASH, RTREE

9.4 修改

索引: alter table student ADD INDEX <索引名> <索引类型> <列名>;
唯一索引: alter table student ADD UNIQUE index [索引名] (列名);
主键索引: alter table student ADD primary key [索引名] (列名);
外键索引: alter table student ADD foreign key [索引名] (列名);

9.5 删除

drop index <索引名> on <表名>
alter table student drop index 索引名;
alter table student drop primary key;
alter table student drop foreign key 外键名;

9.6 索引失效

下面的情况会使mysql不使用索引进行查询:

or 操作的字段不是全部都是索引
查询字段不是组合索引的第一部分
like 查询以 % 开头
对索引字段有进行运算或函数的
mysql估计全表扫描更快, 则不使用索引

10. 存储过程

存储过程是一组为了完成特定功能迭代SQL语句集合, 编译优化后存储在数据库服务器中.

格式:

create PROCEDURE <过程名> ([过程参数[,...]])
<过程体>
# [过程参数[,...]] 格式:
#[IN|OUT|INOUT] <参数名> <类型>

过程名: 默认在当前数据库下
过程参数: 包括参数名和类型, 多个参数用 , 分隔, 可以没有参数但也必须保留()
过程体: 主体部分, 执行的SQL语句, 以 BEGIN开始, END结束, 只有一条SQL语句则可省略 BEGIN-END
- 多条SQL语句若以;分隔, 则会被当作程序的结束符, 所以需要使用定界符命令:
  
  mysql> DECLIMITER ?? 任何命令、语句或程序的结束标志就换为两个问号用户自定义结束符??, 但不能是转义字符 \

10.1 创建

> delimiter //
> create procedure showStuScore()
BEGIN
select * from student_score;
END //
> create procedure ageGt17(IN age int(3))
BEGIN
select * from student where studetn.age>age;
END //

10.2 查看

show procedure status like '存储过程名'; 存储过程信息
show create procedure 存储过程名; 查看定义, 需要切换到其所在数据库
存储过程相关信息存储在information_schema的Routines表中, select * from information.Routines where routine_name='存储过程名' \G;

10.3 修改

格式: alter procedure 存储过程名 [特征…]

修改特征: alter procedure ageGt17 MODIFIES SQL DATA SQL SECURITY INVOKER;
修改命名和内容: 删除后再重新创建

特征:

DETERMINISTIC 不确定的
CONTAINS SQL: 表示包含SQL语句, 但不包含有关读写语句
NO SQL: 不包含SQL语句
READ SQL DATA: 包含读操作
MODIFIES SQL DATA: 包含写操作
SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行
DEFINER: 只有定义者可以执行
INVOKER 调用者可以执行
COMMENT ‘str’: 注释信息

10.4 删除

格式: drop procedure [if exists] <过程名>

drop procedure if exists ageGt17;
删除前要确认该存储过程没有依赖关系, 否则导致相关的存储过程无法运行
可以查询 information_schema.routines 表来确认是否删除成功
- select * from information_schema.routines where routine_name='ageGt17';

10.5 存储函数

官方文档: https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

格式: create FUNCTIOIN func_name([参数名参数类型,…]) RETURNS 值类型 [comment ‘xxx’] begin declare变量; SQL语句; RETUEN 变量; end

存储函数可以返回值, 用法和存储过程相同, 但是参数没有 IN|OUT|INOUT

创建:

mysql> delimiter //
mysql> create function func_student(id int(9))
returns varchar(20)
comment '查询某个学生的姓名'
BEGIN
return (select name from student where student.id=id);
END //

查看:

show create function func_student;
show function status like 'func_stu%';
select * from information_schema.routines where routine_name='func_student';

修改:

alter function func_student [特征...];

删除

drop function [if exists] <存储函数名>;

11. 触发器

自动触发执行的一段程序(不需手动调用), 用于保护表中的数据

mysql中, 只有INSERT UPDATE DELETE操作才会**触发器, 且必须关联真实表, 不能关联触发器, 临时表, 视图

优点: 自动执行, 可以实施比外键/检查约束更复杂的检查, 可实现表的级联更改(数据完整性)

缺点: 难以定位错误位置(维护困难), 数据量大时执行效率非常低

错误处理:

对于事务性表, 失败会自动回滚所有更改

对于非事务性表, 失败不能执行自动回滚, 之前的更改依然有效

INSERT触发器

在insert语句执行前/后自动触发, 其中BEFORE会被insert, LOAD DATA语句触发
UPDATE触发器

在update语句执行前/后自动触发
DELETE触发器

在delete语句执行前/后自动触发, delete和REPLACE语句都会触发

创建

格式: create TRIGGER 触发器名 <BEFORE|AFTER> <INSERT|UPDATE|DELETE> ON 表名 FOR EACH ROW BEGIN SQL语句; END;

before: 常用于验证条件是否允许

after: 常用于触发附加操作

for each row: 行级触发

插入记录前对salary求和: create trigger SumOfSalary before insert on tb_employ for each row set @[email protected]+NEW.salary;
向tb_emp1插入记录后, 附加向tb_emp2表中插入数据: create trigger double_salary after insert on tb_emp1 for each row insert into tb_emp2 value(NEW.id, NEW.name, 2*NEW.salary);
NEW指向当前访问的行

查看

show triggers; 查看当前数据库下的所有触发器
select * from information_schema.triggers where trigger_name='触发器名' \G;

删除, 更改

需要SUPER权限, 删除表的同时, 也会自动删除其上的触发器

drop trigger [if exists] [数据库名] 触发器名
触发器不可直接更改, 可删除后重新创建

12. 游标

相对一个标识, 用于记录查询的结果集所在位置, 可从游标中依次连续读取记录(只读), 有利于单/多条记录的处理

mysql中, 游标只能用于存储过程和存储函数中

声明

格式: declare 游标名 CURSOR FOR select子句;

create procedure processNames() BEGIN declare Cname CURROR FOR select name from student; END //

打开

格式: OPEN 游标名;

open Cname;

使用

格式: FETCH 游标名 INTO 变量名[,…]

fetch Cname into var; 将游标中的select结果保存到变量var中

关闭

游标使用完需要及时关闭, 释放游标使用的资源, 遇到END关键字会自动关闭它

CLOSE Cname;

相关标签：数据库数据库 sql mysql

上一篇： T-1.1 使用指定程序打开某一特定程序

下一篇： 2019-05-31 linux 使用 ip route 命令快速切换网关

MySQL ----- 约束,视图,索引,存储过程,触发器,游标

7. 约束

8. 视图

8.1 创建

8.2 查看

8.3 修改

8.4 删除

9. 索引

9.1 索引类型

9.2 创建

9.3 查看

9.4 修改

9.5 删除

9.6 索引失效

10. 存储过程

10.1 创建

10.2 查看

10.3 修改

10.4 删除

10.5 存储函数

11. 触发器

创建

查看

删除, 更改

12. 游标

声明

打开

使用

关闭

SQL server 数据库的索引和视图、存储过程和触发器

对比索引、视图、游标、存储过程和触发器

MySQL 系列（三）你不知道的视图、触发器、存储过程、函数、事务、索引、语句

Mysql-自带的一些功能，基本用法（视图，触发器，事务，存储过程，函数，流程控制）

MySql视图触发器存储过程详解

MySQL索引、触发器、常用函数、存储过程和函数、数据备份与还原的介绍

MySQL之视图、触发器、事务、存储过程、函数

MySQL视图索引存储过程触发器函数

MySQL系列之五视图、存储函数、存储过程、触发器

mysql导入导出触发器存储过程事件视图

MySQL ----- 约束,视图,索引,存储过程,触发器,游标

7. 约束

8. 视图

8.1 创建

8.2 查看

8.3 修改

8.4 删除

9. 索引

9.1 索引类型

9.2 创建

9.3 查看

9.4 修改

9.5 删除

9.6 索引失效

10. 存储过程

10.1 创建

10.2 查看

10.3 修改

10.4 删除

10.5 存储函数

11. 触发器

创建

查看

删除, 更改

12. 游标

声明

打开

使用

关闭

SQL server 数据库的索引和视图、存储过程和触发器

对比索引、视图、游标、存储过程和触发器

MySQL 系列（三）你不知道的 视图、触发器、存储过程、函数、事务、索引、语句

Mysql-自带的一些功能，基本用法（视图，触发器，事务，存储过程，函数，流程控制）

MySql视图触发器存储过程详解

MySQL索引、触发器、常用函数、存储过程和函数、数据备份与还原的介绍

MySQL之 视图、触发器、事务、存储过程、函数

MySQL视图 索引 存储过程 触发器 函数

MySQL系列之五 视图、存储函数、存储过程、触发器

mysql导入导出 触发器 存储过程 事件 视图

MySQL 系列（三）你不知道的视图、触发器、存储过程、函数、事务、索引、语句

MySQL之视图、触发器、事务、存储过程、函数

MySQL视图索引存储过程触发器函数

MySQL系列之五视图、存储函数、存储过程、触发器

mysql导入导出触发器存储过程事件视图