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

最新推荐文章于 2024-05-25 20:15:39 发布
最新推荐文章于 2024-05-25 20:15:39 发布
阅读量356 收藏 1
点赞数
分类专栏： 数据库 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45668041/article/details/119863991
版权

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 @sum=@sum+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;
滚去学习……
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
索引、视图与存储过程
weixin_46316835的博客
08-07 562
索引 一，概念 索引室友数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度。类似于图书的目录，方便快速定位，寻找指定的内容。 索引也是一张表，它保存了主键与索引字段，并指向实体表的记录。 二，索引的优缺点 优点：大大提高了查询数据的速度。 缺点：会大大降低更新表的速度，如对表进行INSERT UPDATE和DELETE。因为在更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。 三，索引的分类 1.普通索引 是最基本的索引，没有任何限制。 创建索引： C
触发器、存储过程、游标、索引和视图
weixin_42959073的博客
03-31 291
一、触发器 1、概念：用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均有服务器地洞激活相应的触发器，在DBMS核心层进行集中的完整性控制，类似于约束。 2、前触发器：只执行触发器指定的内容而不执行引发触发器的SQL语句。 二、存储过程 1、在开发数据库应用系统时，使用存储过程可以提高数据访问效率，降低网络流量。 3、存储过程的输入参数和输出参数均有默认值，可接...
oracle约束、视图、索引、序列、PL/SQL、存储过程
level_Tiller的博客
08-14 153
1、约束 主键约束 外键约束 非空约束 唯一约束 检查约束 ALTER TABLE TBL表名 ADD CONSTRAINT 约束的标识名 具体的约束; ALTER TABLE S ADD CONSTRAINT CHK_S_001 CHECK ( SSEX IN ('男','女') ); INSERT INTO S VALUES( 1008 , '任盈盈' , '...
MySQL的视图、触发器、存储过程、外键约束、内外连接查询、锁
MKC__jiaoq的博客
12-16 1394
一、视图 视图：就是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据 作用：缓存数据；把一条sql的查询结果放在一个视图里，下次查询时，就不用走sql了，直接取视图里的就可以，还可以修改这个视图里面的数据，等于修改了原表数据 视图的特点： 1、视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系 2、视图是由基本表（实表）产生的表（虚表） 　　3、视图的建立和删除不影响基本表 　　4、对视图内容的更新（添加、删除和修改）直接影响基...
存储过程——游标
baidu_35525949的博客
04-18 5474
游标 可以使用游标(cursor)获取查询返回的行。在通过查询将行检索到游标中后，可以一次从游标中取出一行。使用游标时一般需要遵循以下5个步骤： ①声明一些变量，用于保存一行的列值。 ②声明游标，它包含一个查询。 ③打开游标。 ④一次从游标中获取一行，并将列值存储在步骤①声明的变量中。然后对这些变量执行某些操作，例如将它们显示在屏幕上、使用它们进行某种计算。 ⑤关闭游标 declare –step 1: 声明变量 v_product_id PRODUCTS.PRODUCT_ID%type; v_name P
MySQL-存储过程-函数-触发器-游标
码赛客1024的博客
09-27 984
本文讲解了MySQL中的存储过程，函数，触发器，游标 。
MYSQL数据库的索引、视图、触发器、游标和存储过程
12-28
MYSQL数据库的索引、视图、触发器、游标和存储过程
mysql高级部分--包含索引建立优化_函数_存储过程_触发器_及游标
06-05
mysql高级部分--包含索引建立优化_函数_存储过程_触发器_及游标
MYSQL数据库的索引、视图、触发器、游标和存储过程.pdf
09-30
MYSQL数据库的索引、视图、触发器、游标和存储过程.pdf
MYSQL数据库的索引、视图、触发器、游标和存储过程[收集].pdf
10-11
MYSQL数据库的索引、视图、触发器、游标和存储过程[收集].pdf
2-数据库学习-约束、查询、存储过程 procedure、触发器 Trigger、函数、数据库的权限、数据库表设计
weixin_42401546的博客
04-21 211
2-数据库学习-约束、查询、存储过程 procedure、触发器 Trigger、函数、数据库的权限、数据库表设计 1.约束 作用: 为了保证数据的有效性和完整性 mysql中常用的约束: 主键约束(primary key) 自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用 唯一约束(unique) 非空约束(not null) ...
MySQL高级篇——索引、视图、存储过程和函数、触发器的相关概念及操作
胜天半子祁同伟
09-22 7022
1.索引 MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 : 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和
【MySQL精通之路】SQL优化(1)-查询优化(1)-WHERE子句
Anakki的博客
05-22 642
与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL。由于MySQL会自动进行类似的优化，因此您通常可以避免这项工作，并将查询保留为更易于理解和维护的形式。因为MySQL优化器的开发工作还在进行中，所以这里并没有记录MySQL优化器所有的优化。您可能会试图重写查询以加快算术运算，同时牺牲可读性。，这有助于简化join连接。有关更多信息和示例，请参见“有关更多信息，请参见“空表或只有一行的表。
Linux文件操作——实现cp指令以及模拟修改配置文件
shion1114的博客
05-23 342
cp 1.c 2.c功能是将(内容相同，实现拷贝)main函数的参数由编译结果可知，总共有三个输入参数，同时输出每个参数字符串内容。
Mongodb分布式id
最新发布
littleschemer的博客
05-25 669
分布式ID是指在分布式系统中用于唯一标识每个元素的数字或字符串。本文主要讲解mongodb分布式id的解决方案，并从实际测试代码验证产生的id在不同集合是否也是保证全局唯一。
《Python编程从入门到实践》day36
m0_37565294的博客
05-22 1770
我们要编写一个名为“学习笔记”的Web应用程序，让用户能够记录感兴趣的主题，并在学习每个主题的过程中添加日志条目。django-admin startproject learning_log . # 新建一个名为learning_log的项目，末尾的句点让新项目使用合适的目录结构。运行命令ls，其输出表明Django有创建一个db.sqlite3.SQLite使用一种使用单个文件的数据库。# 包含4个文件（settings.py、urls.py、wsgi.py、__init__.py）
Redis 完整
m0_73173239的博客
05-24 828
什么是 redis?是一种基于键值对的 NoSql 型数据库。与 hashMap 不同的是，Redis 中的 value 支持 string（字符串）、hash（哈希）、 list（列表）、set（集合）、zset（有序集合）、Bitmaps（位图）、GEO（地理信息定位）等多种数据结构。而 HashMap 只是 redis 中的 hash ，他们俩结构是相同的。
数据访问与Spring Data JPA
无远弗届
05-25 521
假设我们要处理一个用户表（users），可以创建一个User@Entity // 标识这是一个实体类 public class User {@Id // 标识这是主键字段 @GeneratedValue(strategy = GenerationType . AUTO) // 主键生成策略 private Long id;// Getter 和 Setter ... }@Entity // 标识这是一个实体类 public class User {
MySQL开发技巧 - 存储过程
10-08
MySQL存储过程是一种在MySQL数据库中存储和执行一系列SQL语句的方式。它可以提高数据库的性能和安全性，并且可以将复杂的业务逻辑封装在数据库中，方便重用。下面是一些MySQL存储过程的开发技巧： 1. 参数传递：...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值