一.索引，分页性能，执行计划，慢日志
（1）索引的种类，创建语句，名词补充(最左前缀匹配，覆盖索引，索引合并，局部索引等)：
import sys # http://www.cnblogs.com/wupeiqi/articles/5716963.html 武老师索引补充 ''' 索引种类： 单列： 1.普通索引 ： 加速查找 2.主键索引 ： 加速查找 + 不能为空+ 唯一 3.唯一素银 ： 加速查找 + 唯一 多列： 4.联合(组合)索引 ： 1.联合唯一 2.联合不唯一 联合主键索引 联合唯一索引 联合普通索引（自己取名来的） ''' #主键索引 #生成普通索引： # create index ix_name on userinfo(列名) #drop index ix_name on table_name #生成唯一索引： #create unique index index_name on table_name(field) #生成联合索引： # create index ix_name on userinfo(列名，列名...) #drop index ix_name on table_name #局部索引: #create index index_name on table_name(列明(16)) 表示列名前16个字符做索引 #以下几个类型一定要做局部索引: #1. Text类型 '''#最左前缀匹配 # create index ix_name_email on userinfo(name,email,lie3,lie4..); 多列还是最左前缀 #设置普通联合索引，还是会以name为准，只有带上name作为条件，才能查得快，只有email还是慢 ''' #因此组合索引 效率比 索引合并效率高 #但是组合索引，因为有 最左前缀匹配。 # 要依据实际情况去创建索引！！！ ''' 两种不是真实的索引，是概念： 覆盖索引：在索引文件中直接获取数据,（而不用去数据表再获取其他数据） 覆盖索引：select email from userinfo where email = 'email213' 但是如果 select * 则还要去数据表中获取其他列数据 索引合并：把多个单列索引，合并使用 select * from userinfo where emai=xxx and id = xxx; （email和id都设置了索引） #局部索引: 如果字段前后有重复值,那么重复值就不取,只取该字段不重复部分做索引 ''' #全文索引：生产过程中不借助mysql，用第三方工具，知道就好 ''' 总结： 索引的种类1.主键索引，2.普通索引，3.唯一索引，4.联合索引，5.覆盖索引和索引合并 索引创建的命令语句 #特点：1.查找速度快，但是更删改慢(索引不是越多越好！！！) 2.额外硬盘空间放索引的存储特殊的数据结构：1.哈希索引 2.btree索引 3.索引要命中，才有效果 #联合索引：最左前缀匹配 #索引无法命中的情况 ''' #create table t1( id int auto_increment primary key, # num int, # name char(10), #unique 索引名 (num) #单列唯一索引 #index 索引名 (num) #创建普通索引 #unique 索引名 (num,name) #联合唯一索引： 不能由两行一模一样的 num和name
#-- select * from userinfo where name='alex359613'; 3.379秒 # select * from userinfo where id = 359613; #id作为索引 查询只用了 0.063秒 #生成普通索引： # create index ix_name on userinfo(列名) #drop index ix_name on table_name #生成唯一索引： #create unique index index_name on table_name(field) #生成联合索引： # create index ix_name on userinfo(列名，列名...) #drop index ix_name on table_name #局部索引: #create index index_name on table_name(列明(16)) 表示列名前16个字符做索引 #以下几个类型一定要做局部索引: #1. Text类型 ''' 无索引：从前到后一次查找 索引： name 创建额外文件（某种格式存储）， name,email 创建额外文件（某种格式存储）， 当搜索的时候，先来这个文件里找数据在哪个文职，然后去表里定位 ''' # create index ix_name_email on userinfo(name,email); #最左前缀匹配 #设置普通联合索引，还是会以name为准，只有带上name作为条件，才能查得快，只查email还是慢 ''' 索引种类 hash索引：创建了一个索引表，把所有的name都转换为hash值+这行所在的内存地址，然后直接根据地址，去找到数据 哈希表的值，和数据库表里的值，顺序是不一样的 缺点：取范围速度没那么快。 比如我们找id>3在普通表就很快找了，因为遍历只要到3，但是哈希索引因为是乱序的所以这种情况下反倒没那么快 优点： 但是哈希索引在寻找单个值的时候，速度是非常快的 btree索引：二叉树的格式，用二分法查找方法，应用较广（innodb引擎）。 ''' #要想查找速度快 #建立索引：查询快，增改删变慢 #1.额外的文件，保存特殊的数据结构， #2.创建了索引，导致我们 insert或者update或者delete的时候也要去更新索引，所以插入和更新速度会变慢 #3.命中索引：创建了要用，才能有价值
#索引无法命中的情况 #执行计划explain: 但是以后在公司的时候 如果忘记了以下情况,可以去sql里面自己运行语句测试一下 # 不走索引的情况,避免以下情况 ''' - like '%xx' (生产过程中数据量大会用到第三方工具) select * from tb1 where name like '%cn'; 关于like： 用户量少的时候： 用like和占位符模糊匹配 用户量巨大的时候： 第三方工具 sifinks(发音) 他会把字段 存在文件里， 比如：title字段 镇化，增长率，省份.. 对应的id有 1,,4,5, 然后获取到对应id后,再到 sql中进行 select * from userinfo where id in (...) - 使用函数 select * from tb1 where reverse(name) = 'wupeiqi'; 所以让自己的字符串条件翻转就好啦 - or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999; 如果和数据表里数据类型不一致,就会失效 #主键索引除外~~ - != select * from tb1 where name != 'alex' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123 - > select * from tb1 where name > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 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 -- 不使用索引 '''
import os '''打星号为基础中的基础,要背下来 1.******* 避免使用select * (联想覆盖索引) 2.*******count(1)或者count(列),代替 count(*) 3.*******创建表的时 char 代替 varchar 4.表的字段顺序固定长度字段优先 5.组合索引代替多个单列索引 (这个要根据实际情况,如果单列查得多,还是用单列好),注意有 最左前缀匹配 6.尽量使用短索引: 局部索引,参见此day39 文件1 7.使用连接(join)来代替子查询(Sub-Queries):mysql里可以先不用在意这个,sql sever中有差别 8.连表时注意条件类型一致 9.*******索引散列值(重复少)不适合建索引,比如:性别这个列 '''
(2).limit分页性能几种方案
import os ''' limit 数值越大越慢，比如 limit 300000,10 它会先扫描30万行，然后再取下10行 ''' ''' 那要怎么解决这个分页问题呢： 1.不让看 比如博客园，首页最多只能浏览 200页的内容 2.索引表中扫描： 用到了覆盖索引，但是速度还是不快 select * from userinfo where id in ( select id from userinfo limit 200000,10) 3.较优解法： 结合python语句，记录当前页最大或最小id： 1.页面只有上一页和下一页 下一页： select * from userinfo where id > 200000 limit 10; 上一页： select * from userinfo where id < 200000 order by id desc limit 10; 那比如 197 198 199 200 201 我们网页上是这么显示的 此时如果我在197 想跳转到200页怎么操作呢？每页10行 select * from userinfo where id > 200000 limit 10*(200-197)，10; #大于200000后，从第30行开始，取10行 或者 select * from userinfo where id in （select id from (select * from userinfo where id > [当前页maxid] limit 30) as N order by N.id desc limit 10 ）; #大于200000后，取30行，设置临时表，再从大到小排序，取前十行的id，再表中选择 那如果没有上下页关联，我就是想从第1页跳转到第1万页呢 这在sql中目前实现不了，只能慢慢等待扫描了。 有个想法是 between and select * from userinfo where id between 1 and 10; 但是要考虑到 id是否一定是连续的呢， 比如 delete from userinfo where id = 4; 那么 此时1 - 10 就只有9条数据了 ！！！*** 因此有个重要前提是 【limit的时候，一定要考虑到 id是否是连续的！！】 ''' #武老师博客的分页代码 ''' 每页显示10条： 当前 118 120， 125 倒序： 大 小 980 970 7 6 6 5 54 43 32 21 19 98 上一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10; 下一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) order by nid desc limit 10; '''
(3).执行计划介绍：
import os #执行计划 #预估一条sql语句的执行时间 ''' mysql> explain select * from userinfo G; *************************** 1. row *************************** id: 1 #注释1 select_type: SIMPLE :代指简单查询 table: userinfo #注释2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2837841 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ''' #注释1: #id:1 标识符,代指现在预估哪一条sql,和顺序无关. 这里只有一句所以就1 #select * from tb where id in (select id from tb2); 这里就有2句sql了 #注释2: #ALL : 全表扫描 #但是all不一定慢,比如 select * from tb limit 1; #range : where id > 3 #const : where id = 3 #constant #ref : 通过索引查询 #总结:查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const #具体各个值代指什么,见最下方 ''' #那如果查询的字段有 索引,他是怎么预估的呢 mysql> explain select * from userinfo where name='alex465456' G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: userinfo partitions: NULL type: ref possible_keys: ix_name_email key: ix_name_email key_len: 91 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) '''
''' type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; '''
(4).慢日志记录：
import os #DBA工作 #在mysql的服务端进行记录 ''' mysql> show variables like '%quer%'; +----------------------------------------+---------------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | NO | | log_queries_not_using_indexes | OFF # 语句未使用索引 | | log_throttle_queries_not_using_indexes | 0 | | long_query_time | 10.000000 #运行时间大于10秒 则记录 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | slow_query_log | OFF # 慢查询日志是否开启 | | slow_query_log_file #慢日志目录 | D:mysql-8.0.12-winx64dataGkx-PC-slow.log | +----------------------------------------+---------------------------------------------+ 10 rows in set, 1 warning (0.00 sec) 注：查看当前配置信息： show variables like '%query%' 修改当前配置： set global 变量名 = 值 set global slow_query_log = ON; ''' #配置: #以上是依据内存进行设置: 设置完马上生效 #还可以依据配置文件: mysqld # mysqld --defaults-file='D:mysql-8.0.12-winx64my.ini' # 就是我们最先设置的 my.ini 啦,所以可以告诉它这个ini配置文件的路径,然后我们在ini里写好配置即可 #比如所在 d:/my.ini 然后在这个 ini文件里 把slow_query_log = ON; #想要设置的变量写进去 # slow_query_log = ON # slow_query_log_file = '路径'... ''' 修改配置文件前,一定要备份 修改配置文件后,要重启才能生效 '''
二.视图
视图view三.触发器
# 定义 ： 对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，（可以插入多张表也没问题） # 触发器用于定制用户对表的行进行【增/删/改】前后的行为。 #注意触发器是没有 select 的引发行为的，只有 insert，delete，update #如果tb1 在一行语句插入多行数据，那么触发器也会对应插入多行数据 # 要创建触发器前，应该先修改分隔符 delimiter #insert into tb (....) # -- delimiter // # -- create trigger t1 BEFORE INSERT on student for EACH ROW # -- BEGIN # -- INSERT into teacher(tname) values(NEW.sname); #因为不修改的话，执行到这里的分号语句就结束了，下面的执行不到 # -- INSERT into teacher(tname) values(NEW.sname); #NEW.sname 表示你插入tb1 的那条字段的新数据 # -- INSERT into teacher(tname) values(NEW.sname); # -- INSERT into teacher(tname) values(NEW.sname); # -- END // # -- delimiter ; #修改完记得改回来 # -- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根'); # -- NEW,代指新数据 （用于 insert） # -- OLD,代指老数据 （用于 delete / update） #总结触发器如下： # # 插入前 # CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW # BEGIN # INSERT into teacher(tname) values(NEW.sname); (在插入数据之前，要做这个操作)也就是你每往tb1插入一条数据，tb2也会插入数据 # END #如果tb1 在一行语句插入多行数据，那么触发器也会对应插入多行数据 # # # 插入后 # 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
四.函数
#MySQL也有 内置函数 和 自定义函数 ：函数是必定有返回值的 # select now() # 老师博客： http://www.cnblogs.com/wupeiqi/articles/5713323.html # 官方解释：https://dev.mysql.com/doc/refman/5.7/en/functions.html #时间格式化 #select DATE_FORMAT(时间字段,'%Y-%m'),count(1) from table_name group by DATE_FORMAT(时间字段,'%Y-%m') #格式类似 time模块 #自定义函数： # delimiter \ # create function f1( # i1 int, # i2 int) #传参 # returns int #返回值数据类型 # BEGIN # declare num int; #一定不能写 select # set num = i1 + i2; # return(num); # END \ # delimiter ; #删除函数：drop function func_name; #执行函数： select f1(0,100) ; 输出 100 # # 获取返回值 # declare @i VARCHAR(32); # select UPPER('alex') into @i; # SELECT @i; # # # 在查询中使用 # select f1(11,nid) ,name from tb2;
#部分内置函数—字符串相关的 # 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' # # 部分内置函数
最常用的：字符串去重，拼接，取子序列,时间的操作
五.存储过程
import time ''' #存储过程，相比 视图，触发器，函数 更为重要 #存储过程在服务端的mysql（视图，触发器，函数也是），客户端只要知道存储过程的名字就可以了 #定义： 保存在mysql上的一个别名 ---> 一串SQL语句，使用别名就能查到语句 #没有返回值的概念 有三个参数类型 in(传进去要使用的) out(传进去要拿出来的) inout(传进去既可以使用也可以拿出来的) ''' #和视图的区别： #视图：别名代指临时表，只能select，动态的显示表，并不建议修改 #存储过程：存了很多sql语句，比视图多。调用更简单，实现功能更多 #用来替代sql语句的，让程序员不在写程序语句了 #直接调用 存储过程 别名，就可以直接使用sql语句了 #将sql语句和代码进行解耦了，比如网络传输只需要传别名 #程序员写sql，然后交给dba审核 #慢日志：pymysql 可以记录执行较慢的 sql语句。 # 方式一： # mysql：存储过程 # 程序：调用存储过程 # 方式二： # mysql：啥也不做 #程序：写SQL语句 #方式三： #mysql：啥也不做 #程序：写类和对象 ---> 转化为 SQL语句 #存储过程初识： # 一 不带参数 # delimiter // # create procedure p1() # begin # select * from student; # insert into teacher(tname) values('ct'); # end // # delimiter ; #执行procedure ： call p1(); #cursor.callproc('p1') #二 带参数的：in （传进去使用的） 参数表示有 int out inout # delimiter // # create procedure p2( # in n1 int, # in n2 int # ) # begin # select * from student where sid between n1 and n2; # # end // # delimiter ; # 执行procedure ： call p1(12,2); # cursor.callproc('p2',(12,2)) ''' #三. 带参数的：out （传进去后要拿出来的） 参数表示有 int out inout # delimiter // # create procedure p3( # in n1 int, # out n2 int #n2必须是变量 # ) # begin # set n2 = 11111； # select * from student where sid > n1; # # end // # delimiter ; # 执行procedure ： # set @v1 = 1; # call p3(12,@v1); 此时 n2 作为out类型，要传一个变量，然后可以在外部再获取到这个变量 # select @v1; # cursor.callproc('p2',(12,2)) 首先拿结果集 # cursor.execute('select @_p3_0,@_p3_1') # ret = cursor.fetchone();print(ret)------> ((12，11111))----》（（n1的值，n2修改后的值）） #mysql中有一个session的概念 # set @v1 = 0; #表示创建了一个 session级别的变量 v1 . 【@】类似python中的 global # # 执行procedure ： call p3(12,@v1); #然后再使用 @v1的时候，@v1值就变为了 11111 --> (select @v1;) ''' # 特性======> #1.可传参 in out inout #2.MySQL中用call调用，pymysql用 cursor.callproc(procedure,(arg1,arg2..)) #3.没有返回值，但是可以用 out 伪造一个返回值 ''' 为什么有结果集又有out伪造的返回值？ out 返回值，一般情况下是：用于标识存储过程中的执行结果 比如返回 1，2，3 1：成功 2：失败 3：一部分成功 语句1执行 执行完 set n1 =1 表示成功 语句2执行 执行完 set n2 =2 表示失败..... '''
import sys #1.无参 2.带参数in 3.带参数 in out # #接下来第四种： 事务：原子性操作 # 在pymysql中可以进行 try一下，如果报错，则回滚 # ~~~~~~~~~~中文描述 # delimiter // # create procedure p4( # out status int # ) # BEGIN # 1. 声明如果出现异常则执行{ # set status = 1; # rollback; # } # # 开始事务 # -- 由秦兵账户减去100 # -- 方少伟账户加90 # -- 张根账户加10 # commit; # 结束 # set status = 2; ''' #接下来第四种： 事务：原子性操作 在pymysql中可以进行 try一下，如果报错，则回滚 代码如下： delimiter \ create PROCEDURE p4( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception #如果出现异常，执行下面begin里的语句 # https://www.cnblogs.com/datoubaba/archive/2012/06/20/2556428.html BEGIN -- ERROR set p_return_code = 1; #错误返回1 rollback; END; START TRANSACTION; #开始事物 DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 2; #正确返回2 END\ delimiter ; ''' #第四代码：
#防 sql注入 #1.pymysql 程序级别去避免 #2.动态的存储过程 #第五： #游标：老师没讲 #第六： # 动态的执行 SQL ''' 6. 动态执行SQL（防SQL注入） delimiter // create procedure p7( in tpl varchar(255), in arg int ) begin 1. 预检测某个东西 SQL语句合法性 2. SQL =格式化 tpl + arg 3. 执行SQL语句 set @xo = arg; #arg 要赋值给一个 session变量 PREPARE xxx FROM 'select * from student where sid > ?'; EXECUTE xxx USING @xo; DEALLOCATE prepare prod; end // delimter ; # call p7("select * from tb where id > ?",9) ''' #真代码如下： # ===> # # delimiter \ # CREATE PROCEDURE p8 ( # in nid int # ) # BEGIN # set @nid = nid; # PREPARE prod FROM 'select * from student where sid > ?'; # EXECUTE prod USING @nid; # DEALLOCATE prepare prod; # END\ # delimiter ;
import pymysql conn = pymysql.connect(host='localhost',user='root',password='gkx321',database='sql_homework',charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.callproc('p3',(12,2)) #n1是12，无所谓n2是多少，不影响 ret = cursor.fetchall() print(ret) #out的值要这么获取，固定写法 cursor.execute('select @_p3_0,@_p3_1') #可以只写一个 0获取n1，1获取n2 ret = cursor.fetchall() print(ret) ''' 为什么格式是这样呢，因为pymysql内部帮我们做了这样子的操作 set @ _p3_0 = 12 set @ _p3_1 = 2 call p3( @ _p3_0,@_p3_1) select @ _p3_0,@_p3_1 ''' # cursor.execute('select * from teacher') # ret = cursor.fetchall() # print(ret) cursor.close() conn.close()
存储过程总结：
1.无参数
2.有参数 in
3.有参数 in out
为什么有结果集又要有out？ ----> 用于标识存储过程中的执行结果
4.事务
5.游标
6.动态执行sql（防sql注入）
数据库相关操作：
1.sql语句：自己写sql全部都能实现
想要利用函数的时候，先找mysql内置函数，找不到再自己写 #函数可能会造成性能低:又索引，函数不会使用索引
-- select xx() from xx;
最常用的：字符串去重，拼接，取子序列
时间的操作
2.利用mysql内部提供的功能：视图，触发器，函数，存储过程