目录
1.存储过程
1.存储过程基本概念:
存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句,存储过程存放于MySQL服务端中,直接通过调用存储过程触发内部sql语句的执行
2.基本使用
create procedure 存储过程的名字(形参1,形参2,...)
begin
sql代码
end
call 存储过程的名字(); # 调用
3.MySQL终端与python端执行封装操作:
(1)MySQL服务器封装:
delimiter $$ # 改执行符号
create procedure p1(
in m int, # in 只进不出,没返回值
in n int,
out res int # 该形参执行完结果,返回出去
)
begin
select tname from teacher where tid>m and tid<n;
set res=666; # 将res变量修改用来标识当前的存储过程代码确实执行了,即返回值变说明已执行
end $$
delimiter ;
(2)调用:
a.在MySQL终端:
注意:在MySQL终端,针对形参res 不能直接传数据 应该传一个变量名
事先定义变量:
set @ret = 10;
调用:
call p1(1, 5, @ret);
查看out变量对应的值:
select @ret;
b.在pymysql下操作:
import pymysql
conn = pymysql.connect(host='127.0.0.1',
port=3306,
user='root',
passwd='123456',
db='day48',
charset='utf8',
autocommit=True)
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p1', (1, 5, 10)) # 这时不用写变量来代替
# 因为在pymysql下默认以以下方式传值
'''@_p1_0=1
@_p1_1=5
@_p1_2=10
'''
cursor.execute('select @_p1_2;') # 查看out变量对应的值
print(cursor.fetchall())
2.内置函数
函数
1.存储过程是自定义函数,函数就类似于是内置函数如:NOW()
2.函数很多,参考菜鸟教程https://www.runoob.com/mysql/mysql-functions.html
3.内置函数处理数据案例:
(1).造表,存数据
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');
(2)用内置函数处理数据
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
# 即按月分组分篇数统计
3.流程控制
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 ;
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 ;
4.索引
1.索引(俗称目录)基本概念
(1)引入:数据都是存在与硬盘上的,查询数据不可避免的需要进行IO操作
(2)定义:索引:就是一种数据结构,类似于书的目录。
意味着以后在查询数据的应该先找目录再找数据,而不是从头到尾依序查找,从而提升查询速度降低IO操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
(3)索引分类:
a primary key
b unique key
c index key
注意:foreign key不是用来加速查询用的
上面的三种key,前面两种除了可以增加查询速度之外各自还具有约束条件,
而最后一种index key没有任何的约束条件,只是用来帮助你快速查询数据
(4)本质
通过不断的缩小想要的数据范围筛选出最终的结果,同时将随机事件(一页一页的翻)
变成顺序事件(先找目录、找数据)
也就是说有了索引机制,可以总是用一种固定的方式查找数据
(5)补充:
a.一张表中可以有多个索引(多个目录)
b.索引虽然能够帮助你加快查询速度但是也有缺点:
当表中有大量数据存在的前提下 创建索引速度会很慢
在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
因为每改一条数据,该索引处之后都默认清除重做,索引也不要随意的创建
2.b+树
(1)只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据
仅仅是用来指路的树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
(2)如果把每片叶子的大小比喻一个固定的磁盘,叶子(磁盘)里放的是索引+数据
索引占的空间越多,那数据占的空间反而越少,导致数据存储让树的层次越高,索引找到数据时间越慢
所有要让索引占空间相对小,数据占多
因此用id字段作为索引,int型占位比char型占位小
占得空间少 一个磁盘块能够存储的数据多
那么久降低了树的高度 从而减少查询次数
3.聚集索引(primary key)
聚集索引指的就是主键
(1)Innodb 只有两个文件 直接将主键存放在了idb表中
(2)MyIsam 三个文件 单独将索引存在一个文件
4.辅助索引(unique,index)
(1)查询数据的时候不可能一直使用到主键,也有可能会用到name,password等其他字段
当这个时候没有办法利用聚集索引,可以根据情况给其他字段设置辅助索引(也是一个b+树)
(2)叶子节点存放的是数据对应的主键值
先按照辅助索引拿到数据的主键值
之后还是需要去主键的聚集索引里面查询数据
5.覆盖索引(辅助索引直接拿到数据)
在辅助索引的叶子节点就已经拿到了需要的数据
(1)给name设置辅助索引
select name from user where name='zcy';
(2)非覆盖索引
select age from user where name='wsx'; # 还要通过主键索引找数据