利用mysql的预编译机制_Day44 of learning python --MySQL的视图-触发器-存储过程-函数-事务-索引...

一、视图

视图是一个虚拟表(非真实存在)，其本质是：根据SQL语句获取动态的数据集，并为其命名，用户使用时只需要使用【名称】即可以获取结果集，并可以将其当做表来使用。

一般情况下，使用临时表的搜索如下：SELECT

*

FROM(SELECTid,

NAMEFROMstudentWHEREid< 7)ASaWHEREa. NAME= '张翠花';

临时表搜索

1.创建视图

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

CREATE VIEW b AS SELECTid,

NAMEFROMstudentWHEREid< 7;

创建视图的SQL语句，在nav可以看到视图有此表

2.删除视图

--格式：drop view 视图名称

DROP VIEW a;

3.修改视图

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

ALTER VIEW v1 ASSELET A.nid,

B. NAMEFROMALEFT JOIN B ON A.id =B.nidLEFT JOIN C ON A.id =C.nidWHEREA.id> 2

AND C.nid < 5

alter，修改视图

4.使用视图

使用视图时，将其作为正常的表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

select * from v1

查询

二、触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

1.基本语句

# 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOREACH ROWBEGIN...END# 插入后CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOREACH ROWBEGIN...END# 删除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOREACH ROWBEGIN...END# 删除后CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOREACH ROWBEGIN...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOREACH ROWBEGIN...END# 更新后CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOREACH ROWBEGIN...END

根据对某个表的操作而分类触发

2.实例

--delimiter用来转换SQL结束符号

delimiter //

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON class FOREACH ROWBEGIN

INSERT INTO student(name) VALUES('alex');END//delimiter ;insert into class(tittle) values('全栈1期');

插入前操作

delimiter //

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOREACH ROWBEGIN

IF NEW. num = 666 THEN

INSERT INTOtb2 (NAME)VALUES('666'),

('666') ;

ELSEIF NEW. num= 555 THEN

INSERT INTOtb2 (NAME)VALUES('555'),

('555') ;END IF;END//delimiter ;

插入后操作，学会使用if-else-end if

3.删除触发器

DROP TRIGGER 触发器名字;

删除使用Drop

4.使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

insert into class(tittle) values('全栈1期')

只能触发，不能直接使用

三、存储过程

保存在MySQL上的一个别名() => 一坨SQL语句

在终端上输入：别名()，作用：用于替代程序员写SQL语句，把程序与SQL语句解耦了，节省传输的字节到MySQL。DBA：数据维护人员

方式一：

MySQL：存储过程

程序：调用存储过程

方式二：

MySQL：什么都不做

程序：SQL语句

方式三：

MySQL：什么都不做

程序：类和对象(SQL语句)

1.创建存储过程

delimiter //

CREATE PROCEDUREp2 ()BEGIN

SELECT

*

FROMstudent ;INSERT INTOclass (tittle)VALUES('全栈11期') ;END//delimiter ;

创建存储过程

--创建存储过程

delimiter \\create procedurep1(in i1 int,in i2 int,

inout i3int,

out r1int)BEGIN

DECLARE temp1 int;DECLARE temp2 int default 0;set temp1 = 1;set r1 = i1 + i2 + temp1 +temp2;set i3 = i3 + 100;end\\

delimiter ;--执行存储过程

set @t1 =4;set @t2 = 0;

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

有参数的存储过程

delimiter //

create procedurep1()begin

select * fromv1;end //delimiter ;

有结果集的存储过程，查表

delimiter //

create procedurep2(in n1 int,

inout n3int,

out n2int,

)begin

select * fromv1;set n2 = n1 + 100;set n3 = n3 + n1 + 100;end //delimiter ;

结果集+返回的结果

MySQL的游标(cursor)类似于指向数据结构堆栈中的指针，并且每次只能取一个数据。游标的设计是一种数据缓存区的思想，用来存放SQL语句执行的结果。游标是先从数据表中检索出数据之后再灵活的操作数据的技术。

游标的优点：因为游标是针对行操作的，所以对从数据库中select查询得到的每一行可以进行分开的独立的相同或不同的操作。可以满足对某个结果行进行特殊的操作。

游标的缺点：游标的缺点是针对有点而言的，也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。 数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。 当数据量大时，使用游标会造成内存不足现象

使用的场景：针对游标的优缺点，我总结游标的使用场景，主要用在循环处理、存储过程、函数中使用，用来查询结果集，就比如：我们需要从表中循环判断并得到想要的结果集，这时候使用游标操作很方便速度也很快。

游标的使用：

主要是：定义游标->打开游标->使用游标->关闭游标->释放游标。

例子：CREATE PROCEDURE`test`.`new_procedure` ()BEGIN

--需要定义接收游标数据的变量

DECLARE a CHAR(16);--游标

DECLARE cur CURSOR FOR SELECT i FROMtest.t;--遍历数据结束标志

DECLARE done INT DEFAULTFALSE;--将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;--打开游标

OPENcur;--开始循环

read_loop: LOOP--提取游标里的数据，这里只有一个，多个的话也一样；

FETCH cur INTOa;--声明结束的时候

IF done THENLEAVE read_loop;END IF;--这里做你想做的循环的事件

INSERT INTO test.t VALUES(a);ENDLOOP;--关闭游标

CLOSEcur;END注意：注意，变量的定义不要和你的select的列的键同名！不然，fetch into 会失败！

游标的概念及使用

create procedurep3()begin

declare ssid int; --自定义变量1

declare ssname varchar(50); --自定义变量2

DECLARE done INT DEFAULTFALSE;DECLARE my_cursor CURSOR FOR select sid,sname fromstudent;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;openmy_cursor;

xxoo: LOOPfetch my_cursor intossid,ssname;if done thenleave xxoo;END IF;insert into teacher(tname) values(ssname);endloop xxoo;closemy_cursor;end ；

游标的实测例子，不需要添加delimiter

在nva终端上使用的是：call p2();

在pymysql使用：

import pymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='baicells', db='djangopratice',charset='utf8')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.callproc('p2')

conn.commit()

result= cursor.fetchall() # 得到的返回值是select的SQL语句cursor.close()

conn.close()print(result)

注意如果有数据表有中文，需要连接时添加charset='utf8'

对于存储过程，可以接收参数，其参数三类：1. in仅用于传入参数用2. out 仅用于返回值用3. inout 既可以传入又可以当返回值

2.删除存储过程

drop procedure proc_name

删除格式

3.执行存储过程

--无参数

call proc_name()--有参数，全in

call proc_name(1,2)--有参数，有in，out，inout

set @t1=0;set @t2=3;

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

在nav执行

import pymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行存储过程cursor.callproc('p1', args=(1, 22, 3, 4))

# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

#p1有多少个参数输入，就会多几个返回值

result= cursor.fetchall()

conn.commit()cursor.close()

conn.close()print(result)

在pymysql上执行

四、函数

函数分为两种：内置函数与自定义函数，存放在数据库中，自定义函数内部不能出现SQL语句(如：select * from student)，且函数必须要有返回值。

CHAR_LENGTH(str)

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

对于一个包含五个二字节字符集, LENGTH()返回值为10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)

字符串拼接

如有任何一个参数为NULL ，则返回值为NULL。

CONCAT_WS(separator,str1,str2,...)

字符串拼接(自定义连接符)

CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的NULL)。

CONV(N,from_base,to_base)

进制转换

例如：SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。

例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'

INSERT(str,pos,len,newstr)

在str的指定位置插入字符串

pos：要替换位置其实位置len：替换的长度

newstr：新字符串

特别的：

如果pos超过原字符串长度，则返回原字符串

如果len超过原字符串长度，则由新字符串完全替换

INSTR(str,substr)

返回字符串str中子字符串的第一个出现位置。LEFT(str,len)

返回字符串str 从开始的len位置的子序列字符。LOWER(str)

变小写UPPER(str)

变大写LTRIM(str)

返回字符串str，其引导空格字符被删除。RTRIM(str)

返回字符串str，结尾空格字符被删去。SUBSTRING(str,pos,len)

获取字符串子序列

LOCATE(substr,str,pos)

获取子序列索引位置

REPEAT(str,count)

返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。

若count <= 0,则返回一个空字符串。

若str 或count 为 NULL，则返回 NULL。REPLACE(str,from_str,to_str)

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)

返回字符串str，顺序和字符顺序相反。RIGHT(str,len)

从字符串str 开始，返回从后边开始len个字符组成的子序列SPACE(N)

返回一个由N空格组成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)

返回字符串str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

mysql> SELECT TRIM('bar');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx'部分内置函数

部分内置函数

重点注意的是：DATA_FORMAT函数，用于格式化时间格式。相关链接：https://dev.mysql.com/doc/refman/5.7/en/functions.html

1.自定义函数

delimiter \\create functionf1(

i1int,

i2int)returns int

BEGIN

declare num int;set num = i1 +i2;return(num);END\\

delimiter ;

自定义函数

2.删除函数

drop function func_name;

删除函数

3.执行函数

select f1(11,nid) ,name fromtb2;select DATA_FORMAT(ctime,"%Y-%m"),count(1) from tb1 group by ctime;

执行函数

五、事务

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

delimiter \\create PROCEDUREp1(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexceptionBEGIN

--ERROR

set p_return_code = 1;rollback;END;DECLARE exit handler forsqlwarningBEGIN

--WARNING

set p_return_code = 2;rollback;END;

STARTTRANSACTION;DELETE fromtb1;insert into tb2(name)values('seven');COMMIT;--SUCCESS

set p_return_code = 0;END\\

delimiter ;

利用的还是存储过程procedure

六、索引

索引：是数据库中专门用于帮助用户快速查询数据的一种数据结构。查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

MySQL中常见的索引有：

-普通索引

-唯一索引

-主键索引

-组合索引

1.索引

作用：-约束-加速查找

索引分类：-普通索引：加速查找-主键索引：加速查找+不能为空+不能重复-唯一索引：加速查找+不能重复-联合索引(联合唯一)-联合主键索引-联合唯一索引-联合普通索引

创建索引：create index ix_name on userinfo无索引：从前到后依次查找

索引：

创建额外文件(某种格式存储)

假设：

id name email

...

...

索引：

id 创建额外文件(某种格式存储)

name 创建额外文件(某种格式存储)

email 创建额外文件(某种格式存储)create index ix_name on userinfo,drop index ix_name on userinfoname email 创建额外文件(某种格式存储)

索引种类：1)hash索引：索引表(因为hash表存的数据不是按顺序的)

单值快

查范围很慢2)btree索引(innodb)

二叉树(比如查多少次才能查到)

建立索引：-a.额外的文件保存特殊的数据结构-b.查询快：插入更新删除慢-c.命中索引

索引的基本知识

1.普通索引

普通索引仅有一个功能：加速查询

create tablein1(

nidint not null auto_increment primary key,

namevarchar(32) not null,

emailvarchar(64) not null,

extratext,indexix_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));

指定length

2.唯一索引

唯一索引有两个功能：加速查询 和 唯一约束(可含null)

create tablein1(

nidint not null auto_increment primary key,

namevarchar(32) not null,

emailvarchar(64) not null,

extratext,uniqueix_name (name)

)

创建表时直接创建索引

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

创建唯一索引

drop unique index 索引名 on 表名

删除唯一索引

3.主键索引

主键有两个功能：加速查询 和 唯一约束(不可含null)

create tablein1(

nidint not null auto_increment primary key,

namevarchar(32) not null,

emailvarchar(64) not null,

extratext,indexix_name (name)

)OR

create tablein1(

nidint not nullauto_increment,

namevarchar(32) not null,

emailvarchar(64) not null,

extratext,primary key(ni1),indexix_name (name)

)

创建表+创建主键

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

创建主键

alter table 表名 drop primary key;

删除主键

4.组合索引

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

create tablein3(

nidint not null auto_increment primary key,

namevarchar(32) not null,

emailvarchar(64) not null,

extratext

index ix_name_email onin3(name,email)

)

创建表+创建联合索引

create index ix_name_email on in3(name,email);

创建联合索引

如上创建组合索引之后，查询：

name and email  -- 使用索引

name                 -- 使用索引

email                 -- 不使用索引

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

七、存储过程的扩展

1.添加语句

delimiter \\CREATE PROCEDUREproc_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 PROCEDUREproc_repeat ()BEGIN

DECLARE i INT;SET i = 0;

repeatselecti;set i = i + 1;

until i>= 5

endrepeat;END\\

delimiter

repeat循环

BEGIN

declare i int default 0;

loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;selecti;endloop loop_label;END

loop循环语句

3.动态执行SQL语句

SQL注入：是一种sql代码添加到输入参数中，传递到sql服务器解析并执行的一种攻击手法

常见的有：

1)数字注入

sql中where条件的参数值为数字的语句进行修改攻击，例如 select * from tb1 where id = -1 OR 1=1

2)字符串注入

-以sql中的注释符号('#)来实现攻击，比如用户登入的，密码随意。('单引号用来闭合用户名的输入，# 用来注释掉后面的查询条件)

-以注释符号('-- )来实现攻击，以为这样sql语句执行都会跳过密码验证，在不需要知道密码的情况下就可以实现登陆了。

预防SQL注入：

1)严格检查输入变量的类型和格式

-对数据类型的参数id的强校验(empty()为空验证和is_numeric()进行数字验证)

-对字符串类型的参数用正则校验preg_match("/^[a-zA-Z0-9]{6,}$/")

2)过滤和转义特殊字符

3)利用预编译机制(mysqli和pdo)

参考：https://www.cnblogs.com/gyrgyr/p/9876569.html

存储过程，防止SQL注入

delimiter \\DROP PROCEDURE IF EXISTSproc_sql \\CREATE PROCEDUREproc_sql ()BEGIN

declare p1 int;set p1 = 2;set @p1 =p1;PREPARE prod FROM 'select * from teacher where id > ?';EXECUTE prod USING @p1;DEALLOCATE prepareprod;END\\

delimiter ;

动态执行