AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL实战系列3：视图、存储过程、函数、索引全解析

2017-05-11 2103
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介：

作者介绍

索宁，擅长Python开发、MySQL、前端等众多技术领域，曾负责众多企业安全架构解决方案 ，涉猎行业有媒体、出版社、航空运输、医疗、军队、政府、教育等。

 

一、视图

 

视图是查询命令结果构成的一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集合，并可以当作表来查询使用。

 

1
创建视图

 

--格式：CREATE VIEW 视图名称 AS  SQL语句

CREATE VIEW v1 AS

SELET nid,

    name

FROM

    A

WHERE

nid > 4

 

2
删除视图

--格式：DROP VIEW 视图名称

DROP VIEW v1

 

3
修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS

SELET A.nid,

    B. NAME

FROM

    A

LEFT JOIN B ON A.id = B.nid

LEFT JOIN C ON A.id = C.nid

WHERE

    A.id > 2

AND C.nid < 5

 

4
使用视图

 

视图的使用和普通表一样，由于视图是虚拟表，所以无法对其真实表进行创建、更新和删除操作，仅做查询用。

select * from v1

 

二、触发器

 

对某个表进行【增/删/改】操作的前后触发一些操作即为触发器，如果希望触发增删改的行为之前或之后做操作时，可以使用触发器，触发器用于自定义用户对表的行进行【增/删/改】前后的行为。

 

1
基本语法

# 插入前

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

# 更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

    ...

END

 

2
创建触发器

 

创建触发器基本语法是以下code：

 

但有一点要注意，触发器内关键字NEW表示即将插入的数据行，OLD表示即将删除的数据行。

# 插入前触发器

delimiter //

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

IF NEW. NAME == 'nick' THEN

    INSERT INTO tb2 (NAME)

VALUES

    ('aa')

END

END//

delimiter ;

# 插入后触发器

delimiter //

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

    IF NEW. num = 666 THEN

        INSERT INTO tb2 (NAME)

        VALUES

            ('666'),

            ('666') ;

    ELSEIF NEW. num = 555 THEN

        INSERT INTO tb2 (NAME)

        VALUES

            ('555'),

            ('555') ;

    END IF;

END//

delimiter 

 

3
删除触发器

DROP TRIGGER tri_after_insert_tb1;

 

4
使用触发器

 

触发器是由于对表的增、删、改操作时被动执行的。

insert into tb1(num) values(666)

 

三、存储过程

 

存储过程是一个SQL语句集合，类似函数，需要主动调用。

 

1
创建存储过程

# 无参数存储过程

# 创建存储过程

delimiter //

create procedure p1()

BEGIN

    select * from t1;

END//

delimiter ;

# 执行存储过程

call p1()

 

都说了类似函数，那必须得可以接收参数，且参数有三类：

  • in          仅用于传入参数用

  • out        仅用于返回值用

  • inout     既可以传入又可以当作返回值

 

# 有参数存储过程

# 创建存储过程

delimiter \\    # 结尾分号改为\\

create procedure p1(

    in i1 int,

    in i2 int,

    inout i3 int,

    out r1 int

)

BEGIN

    DECLARE temp1 int;    # 创建申明局部变量

    DECLARE temp2 int default 0;

    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;

    set i3 = i3 + 100;

end\\

delimiter ;

# 执行存储过程

DECLARE @t1 INT default 3;

DECLARE @t2 INT;

CALL p1 (1, 2 ,@t1, @t2);

SELECT @t1,@t2;

 

2
删除存储过程
drop procedure proc_name;

 

3
执行存储过程

 

执行为函数名加括号；

 

DECLARE代表创建一个局部变量

# 无参数

call proc_name()

# 有参数，全in

call proc_name(1,2)

# 有参数，有in，out，inout

DECLARE @t1 INT;

DECLARE @t2 INT default 3;

call proc_name(1,2,@t1,@t2)

 

  • pymysql执行存储过程

 

 

四、函数

 

该讲讲真正的函数了，与存储过程不同的是有return值。

 

1
自定义函数

delimiter \\

create function f1(

    i1 int,

    i2 int)

returns int

BEGIN

    declare num int;

    set num = i1 + i2;

    return(num);

END \\

delimiter ;

 

2
删除函数

drop function func_name;

 

3
执行函数

# 获取返回值

declare @i VARCHAR(32);

select UPPER('nick') into @i;

SELECT @i;

# 在查询中使用

select f1(11,nid) ,name from tb2;

 

4
内置函数

 

当然MySQL也不例外，有着非常好用的内置函数。

 





 

五、事务

 

事务用于将某些操作的多个SQL语句作为原子性操作，只有出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

 

delimiter 为改变结束符号，默认";"

# 支持事务的存储过程

delimiter \\

create PROCEDURE p1(

    OUT p_return_code tinyint

)

BEGIN

  DECLARE exit handler for sqlexception

  BEGIN

    -- ERROR

    set p_return_code = 1;

    rollback;

  END;

  DECLARE exit handler for sqlwarning

  BEGIN

    -- WARNING

    set p_return_code = 2;

    rollback;

  END;

  START TRANSACTION;

    DELETE from tb1;

    insert into tb2(name)values('seven');

  COMMIT;

  -- SUCCESS

  set p_return_code = 0;

  END\\

delimiter ;

# 执行存储过程

DECLARE @i TINYINT;

call p1(@i);

select @i;

 

六、索引

 

1
索引概述

 

  • 索引是表的索引目录，在查找内容之前先查目录中查找索引位置，从而快速定位查询数据；

  • 可以理解成新华字典中的索引；

  • 索引会保存在额外的文件中。

 

2
索引种类

 

一般的索引种类及功能：

  • 普通索引：仅加速查询

  • 唯一索引：加速查询 + 列值唯一（可以有null）

  • 主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）

  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

  • 全文索引：对文本的内容进行分词，进行搜索 

 

  • 索引合并：使用多个单列索引组合查询搜索。

  • 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

 

a、普通索引

# 创建表 + 索引

create table in1(

    nid int not null auto_increment primary key,

    name varchar(32) not null,

    email varchar(64) not null,

    extra text,

    index ix_name (name)

)

# 创建索引

create index index_name on table_name(column_name)

# 删除索引

drop index_name on table_name;

# 查看索引

show index from table_name;

#注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index ix_extra on in1(extra(32));

 

b、唯一索引

# 创建表 + 唯一索引

create table in1(

    nid int not null auto_increment primary key,

    name varchar(32) not null,

    email varchar(64) not null,

    extra text,

    unique ix_name (name)

)

# 创建唯一索引

create unique index 索引名 on 表名(列名)

# 删除唯一索引

drop unique index 索引名 on 表名

 

c、主键索引

# 创建表 + 创建主键

create table in1(

    nid int not null auto_increment primary key,

    name varchar(32) not null,

    email varchar(64) not null,

    extra text,

    index ix_name (name)

)

OR

create table in1(

    nid int not null auto_increment,

    name varchar(32) not null,

    email varchar(64) not null,

    extra text,

    primary key(ni1),

    index ix_name (name)

)

# 创建主键

alter table 表名 add primary key(列名);

# 删除主键

alter table 表名 drop primary key;

alter table 表名  modify  列名 int, drop primary key;

 

d、组合索引

 

组合索引是多个列组合成一个索引来查询。

 

应用场景：频繁的同时使用多列来进行查询，如：where name = 'nick' and age = 18。

# 创建表

create table mess(

    nid int not null auto_increment primary key,

    name varchar(32) not null,

    age int not null

)

# 创建组合索引

create index ix_name_age on mess(name,age);

 

如上创建组合索引之后，查询一定要注意：

  • name and email  -- >使用索引，name一定要放前面

  • name                 -- >使用索引

  • email                 -- >不使用索引

 

注意：同时搜索多个条件时，组合索引的性能效率好过于多个单一索引合并。

 

3
相关命令

# 查看索引

    show index from  表名

# 查看执行时间

    set profiling = 1;  # 开启profiling

    SQL...              # 执行SQL语句

    show profiles;      # 查看结果

 

4
如何正确使用索引

# like '%xx'，避免%_写在开头

    select * from tb1 where name like '%n';

# 使用函数

    select * from tb1 where reverse(name) = 'nick';

# or

    select * from tb1 where nid = 1 or email = '630571017@qq.com';

    注：当or条件中有未建立索引的列才失效，否则会走索引

# 类型不一致

    如果列是字符串类型，传入条件是必须用引号引起来。

    select * from tb1 where name = 999;

# !=，不等于

    select * from tb1 where name != 'nick'

    注：如果是主键，则还是会走索引

        select * from tb1 where nid != 123

# >，大于

    select * from tb1 where name > 'nick'

    注：如果是主键或索引是整数类型，则还是会走索引

        select * from tb1 where nid > 123

        select * from tb1 where num > 123

# order by

    select email from tb1 order by name desc;

    当根据索引排序时候，选择的映射如果不是索引，则不走索引

    注：如果对主键排序，则还是走索引：

        select * from tb1 order by nid desc;

# 组合索引最左前缀

    如果组合索引为：(name,email)，查询使用：

    name and email       -- 使用索引

    name                 -- 使用索引

    email                -- 不使用索引

 

5
注意事项

 

# 避免使用select *

# count(1)或count(列) 代替 count(*)

# 创建表时尽量时 char 代替 varchar

# 表的字段顺序固定长度的字段优先

# 组合索引代替多个单列索引（经常使用多个条件查询时）

# 尽量使用短索引

# 使用连接（JOIN）来代替子查询(Sub-Queries)

# 连表时注意条件类型需一致

# 索引散列值（重复少）不适合建索引，例：性别不适合

 

6
执行计划

 

explain + 查询SQL  用于显示SQL执行信息参数，根据参考信息可以进行SQL优化。

 

 

id

        查询顺序标识

          

        特别的：如果使用union连接气值可能为null

 



 

7
慢日志查询

 

日志可以记录哪些查询语句慢，没有走索引，用户发现问题，从而解决优化

 

a、配置MySQL自动记录慢日志

 

 修改配置文件 my.ini，没有则添加

slow_query_log = OFF                   # 是否开启慢日志记录

long_query_time = 2                    # 时间限制，超过此时间，则记录

slow_query_log_file = /usr/slow.log    # 日志文件

log_queries_not_using_indexes = OFF    # 为使用索引的搜索是否记录

# 查看当前配置信息：

　  show variables like '%query%'

# 修改当前配置：

  　set global 变量名 = 值

 

b、查看MySQL慢日志

 

 

七、其它

 

自然所有的东西都离不开基本的语法语句啦，循环语句与判断语句

 

1
条件语句

 

if then\ elseif then\ else\ end if

# if条件语句

delimiter \\

CREATE PROCEDURE proc_if ()

BEGIN

    declare i int default 0;

    if i = 1 THEN

        SELECT 1;

    ELSEIF i = 2 THEN

        SELECT 2;

    ELSE

        SELECT 7;

    END IF;

END\\

delimiter ;

 

2
循环语句

 

分为三种循环语句：while循环、repeat循环、loop循环。

# while循环

delimiter \\

CREATE PROCEDURE proc_while ()

BEGIN

    DECLARE num INT ;

    SET num = 0 ;

    WHILE num < 10 DO

        SELECT

            num ;

        SET num = num + 1 ;

    END WHILE ;

END\\

delimiter ;

# repeat循环，相当于 do while

delimiter \\

CREATE PROCEDURE proc_repeat ()

BEGIN

    DECLARE i INT ;

    SET i = 0 ;

    repeat

        select i;

        set i = i + 1;

        until i >= 5

    end repeat;

END\\

delimiter ;

# loop

delimiter \\

CREATE PROCEDURE proc_loop ()

BEGIN

    declare i int default 0;

    loop_label: loop

        select i;

        set i=i+1;

        if i>=5 then

            leave loop_label;

            end if;

    end loop;

END\\

delimiter ;

 

3
动态执行SQL语句

# 动态执行SQL

delimiter \\

DROP PROCEDURE IF EXISTS proc_sql \\

CREATE PROCEDURE proc_sql ()

BEGIN

    declare p1 int;

    set p1 = 11;

    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';

    EXECUTE prod USING @p1;

    DEALLOCATE prepare prod;

END\\

delimiter ;

# 方式二

delimiter \\

DROP PROCEDURE IF EXISTS proc_sql \\　　# 如果不存在则添加，否则不做操作

CREATE PROCEDURE proc_sql (

    in strSQL char(128),

    in nidd int

)

BEGIN

        set @sqll = strSQL;

    set @p1 = nidd;

    PREPARE prod FROM @sqll;

    EXECUTE prod USING @p1;

    DEALLOCATE prepare prod;

END\\

delimiter ;

call proc_sql('select * from suoning2 where id > ?','2');

原文发布时间为：2016-11-21 本文来自云栖社区合作伙伴DBAplus

文章标签：
云数据库 RDS MySQL 版
云解析DNS
日志服务
索引
SQL
关系型数据库
存储
MySQL
关键词：
mysql云解析DNS
云数据库 RDS MySQL 版解析
云数据库 RDS MySQL 版函数
实战云解析DNS
云数据库 RDS MySQL 版索引
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
稀奇古怪
目录
相关文章
德国都芳最好
|
6天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
32 9
技术自由圈/原疯狂创客圈
|
29天前
|
存储 关系型数据库 MySQL
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
尼恩是一位资深架构师，他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构，主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型，如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树，它能够有效支持范围查询和顺序遍历，同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点，并提供了多个面试题及其解答，帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
技术自由圈/原疯狂创客圈
75 6
土木林森
|
20天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
96 1
肾透侧视攻城狮
|
21天前
|
存储 SQL NoSQL
2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]
MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法
肾透侧视攻城狮
20 0
2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]
跃@sir
|
30天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理？
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理？
跃@sir
63 1
土木林森
|
21天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
49 0
你都不懂
|
1月前
|
存储 SQL 关系型数据库
MySql数据库---存储过程
MySql数据库---存储过程
你都不懂
35 5
糜终
|
1月前
|
存储 关系型数据库 MySQL
MySQL 存储过程返回更新前记录
MySQL 存储过程返回更新前记录
糜终
54 3
糜终
|
1月前
|
存储 SQL 关系型数据库
MySQL 存储过程错误信息不打印在控制台
MySQL 存储过程错误信息不打印在控制台
糜终
61 1
秋夜雨巷
|
3月前
|
存储 关系型数据库 MySQL
Mysql表结构同步存储过程(适用于模版表)
Mysql表结构同步存储过程(适用于模版表)
秋夜雨巷
48 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    Linux操作系统上的mysql安装
  • 4
    mysql常用操作
  • 5
    C语言连接MySQL数据库
  • 6
    JDBC操作MySQL5日期类型字段的问题解决方法
  • 7
    Mariadb/Mysql TEMPORARY表
  • 8
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 9
    mysql 数据类型
  • 10
    mysql_5.6.21_安装使用说明
  • 1
    Cloudstack启动实例模板从二级存储复制到主存储过程
    24
  • 2
    PolarDB产品使用问题之在将RDS迁移到PolarDB后，原先由root用户创建的视图、存储过程等是否可以继续使用的
    52
  • 3
    SQLSERVER存储过程语法详解
    387
  • 4
    在MySQL中使用存储过程返回更新前的记录
    73
  • 5
    索引的威力--记一次MySQL存储过程优化
    129
  • 6
    MySQL数据库进阶第四篇（视图/存储过程/触发器）
    243
  • 7
    MySQL周内训参照5、存储过程创建
    52
  • 8
    MySQL存储过程_触发器_游标——Baidu Comate
    40
  • 9
    MySQL存储过程——if判断示例
    85
  • 10
    MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
    42

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    无影云桌面