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 n int ,
out res int
)
begin
select tname from teacher where tid > m and tid < n;
set res = 0 ;
end $$
delimiter ;
看成是一个函数 . 变量传入 10 , 返回 0 。
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' ,
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( ) )
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 |
+ ---- + --------- + --------------------- +
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 ;
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 通过辅助索引后还需要去主键中查。