12.MySQL存储过程&索引

开局签到Python基础

已于 2022-02-28 21:00:07 修改

阅读量352

点赞数

分类专栏： 2.MySQL 文章标签： mysql 数据库 database

于 2021-08-01 20:44:04 首次发布

风

本文链接：https://blog.csdn.net/qq_46137324/article/details/119303325

版权

2.MySQL 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

文章目录

1.存储过程

存储过程内部包含一系列可以执行的sql语句，存储过程中存放于MySQL服务端中，
可以直接通过调用存储过程触发内部的sql语句执行。
存储过程类似于Python中的自定义函数。

1.1基本的使用

 语法:
create procedure 存储过程的名字 (形参1，···)
begin
    sql代码;
end

调用:
call 名称();

1.2三种开发模式

1.程序员不熟悉MySQL的情况

让专业部门将sql语句封装，通过传入参数来操作。

部门1：应用程序：程序员写代码开发
部门2：MySQL：提现编写好存储过程，供应用程序调用。
好处:开发、执行效率提升。
缺点:后续存续过程拓展性差。

2.程序员熟悉MySQL

应用程序：程序员写代码开发，涉及到数据库操作自己写。
优点：拓展性高
缺点：开发效率降低，编写sql语句繁琐后期还需要优化。

3.使用框架

应用程序：程序员写代码开发 不写sql语句，基于别人写好的MySQL的框架直接调用操作。
ORM 框架。
优点：开发效率比上两种情况都高。
缺点：语句扩展性差，可能会出现效率低下的问题。

1.3存储过程演示

#  创建 
delimiter $$
create procedure p1 (
	in m int,  # in 只进不出，只接受参数，没有返回值
	in n int,
	out res int  # out 该形参可以返回出去
)
begin
    select tname from teacher where tid > m and  tid < n;
    set res = 0;  # 将res变量修改，用来标识当前存储过程代码确实执行了
end $$
delimiter ;

看成是一个函数. 变量传入10,返回0。

# 针对形参res 不能直接传入一个值,因该传一个变量
# 声明变量
set @ret=10;

# 查看变量
select @ret;

+------+
| @ret |
+------+
|   10 |
+------+

# 调用 
call p1(1, 5, @ret)

+-----------------+
| tname           |
+-----------------+
| xx老师          |
| xxx老师         |
| xxxx老师        |
+-----------------+

select @ret;  # 查看变量

+------+
| @ret |
+------+
|    0 |
+------+

2.存储过程使用

在pymysql模块中调用存储过程.

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',   # 编码不能加-（utf-8）
    database='kid'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 调用存储过程
cursor.callproc('p1', (1, 5, 10))
print(cursor.fetchall())

[{'tname': 'XX老师'}, {'tname': 'XXX老师'}, {'tname': 'XXXX老师'}]

cursor.callproc('p1', (1, 5, 10))
# 内部方法
@_p1_0 = 1
@_p1_1 = 5
@_p1_2 = 10

cursor.callproc('p1', (1, 5, 10))

cursor.execute('select @_p1_2;')
print(cursor.fetchall())   # 0没数据

3.内置函数

Type	Range	Remark
DATE	‘1000-01-01’`to`‘9999-12-31’	只有日期部分，没有时间部分
DATETIME	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’	时间格式为 Y`\YYY-MM-DD hh:mm:ss，默认精确到秒
TIMESTAMP	‘1970-01-01 00:00:01’ UTC to `'2038-01-19 03:14:07'`UTC	默认精确到秒

date_format 格式化时间

# 创建表
create table blog (
    id int primary key auto_increment,
    name char (32),
    sub_time datetime
);

# 录值
insert into blog (name, sub_time)
values
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

select * from blog;

+----+---------+---------------------+
| id | NAME    | sub_time            |
+----+---------+---------------------+
|  1 | 第1篇   | 2015-03-01 11:31:21 |
|  2 | 第2篇   | 2015-03-11 16:31:21 |
|  3 | 第3篇   | 2016-07-01 10:21:31 |
|  4 | 第4篇   | 2016-07-22 09:23:21 |
|  5 | 第5篇   | 2016-07-23 10:11:11 |
|  6 | 第6篇   | 2016-07-25 11:21:31 |
|  7 | 第7篇   | 2017-03-01 15:33:21 |
|  8 | 第8篇   | 2017-03-01 17:32:21 |
|  9 | 第9篇   | 2017-03-01 18:31:21 |
+----+---------+---------------------+

# 按时间分组  时间的格式为 %Y年-%m月
select date_format(sub_time,'%Y-%m'),count(id) 
	from blog group by date_format(sub_time,'%Y-%m');

+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2016-07                       |         4 |
| 2017-03                       |         3 |
+-------------------------------+-----------+

4.流程控制

1. if 判断
2.while 循环

4.1 if条件语句

delimiter //
create procedure proc_if ()
begin
    declare i int default 0;  # declare声明 
    if i = 1 then
        select 1;
    elseif i = 2 then
        select 2;
    else
        select 7;
    end if;

end //
delimiter ;

声明i是整型 int 类型 默认值为0

4.2 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 ;

5.索引

数据都是存在于硬盘上的，查询数据不可避免的进行IO操作。
索引：就是一种数据结构。类似于书的目录。意味着以后在查询数据的时候因该先找目录
再找数据，而不是一页页的翻书，而从提升查询速度，降低IO操作。

索引在MySQL中也叫键， 是存储引擎用于快速查找记录的一种数据结构。

三个键可以帮助快速查询的
1.primary key
2.uniquw key
3.index key
三种key，前两种除了可以增加查询速度还可以具有约束条件(唯一 非空)，而最后一种没有任何的约束条件，只是单单的帮助快速查询。

本质：不断的缩小想要的数据范围筛选出最总结果，同时将随机事件（一页页查找）变成顺序事件（先找目录，再找数据）。
有了索引机制，可以用固定的方式查找数据。

索引虽然能帮助加快查询速度但也有缺点。
1.当表中有大量数据存在的前提下，创建索引速度会很慢。
2.在索引创建完毕之后，对表的查询行嫩会大幅度的提升，但写的性能也会打幅度的降低。

索引不要随意的创建，不要所有的字段加索引。

6.b+树

只有叶子节点存放真实的数据，其他的节点存放虚拟的数据，指路的作用。
树的层级越高查询数据需要经历的步骤就越多（树有几层查询数据就需要有几步）

在这里插入图片描述

来模拟下查找文件29的过程：

    (1) 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作1次】

    (2) 此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。

    (3) 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作2次】

    (4) 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。

    (5) 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作3次】

    (6) 此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地

推荐以id作为主键，占的空间少，一个磁盘块能够存储的数据多，那么降低了树的高度从而减少查询次数。
一个磁盘块存储不完将数据分多个磁盘块存储，本层存放虚拟数据，向下多产生一层存储整数数据。三层树结构又加一层。

7.聚集索引

聚集索引指的就是主键 primary key
Innodb 只有两个文件  直接将主键存放在idb数据表中。
MyIsam  三个文件     单独将索引存在一个文件。

8.辅助索引

unique 和 index 是辅助索引。

查询数据的时候
where id = xxx  利用设置id索引查询速度快

查询数据的时候
where name = xxx 没法使用书目录

查询数据的时候不可能一直使用到主键的，也可能使用其他的字段。这个时候根据情况设置辅助索引，（辅助索引也是一个b+数）。

叶子节点存放的就是数据对应的主键值
先按照赋值索引拿到数据的主键值，之后还是需要去主键的聚集索引里查找数据。

9.覆盖索引

在赋值索引的叶子节点就拿到了需要的数据。

# 在name设置复制索引
select name from user where name = 'kid';  # 需要name 直接通过辅助索引拿到了name 这个时候不需要在去主键中查数据了。

10.非覆盖索引

# 在name设置复制索引
select age from user where name = 'kid'  #  需要age 通过辅助索引后还需要去主键中查。

开局签到Python基础

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
12.MySQL存储过程&索引

文章目录1.存储过程1.1基本的使用固定的语法调用1.2三种开发模式1.程序员不熟悉MySQL的情况。2.程序员熟悉MySQL3.使用框架1.3存储过程演示创建调用1.2pymysql模块中使用3.函数4.流程控制if条件语句while循环5.索引6.b+树7.聚集索引8.辅助索引9.覆盖索引10.非覆盖索引1.存储过程内部包含一系列可以执行的sql语句，存储过程中存放于MySQL服务端中，可以直接通过调用存储过程触发内部的sql语句执行。存储过程类似于python中的自定义函数。1.1基本的使
复制链接

扫一扫