网站建设目的和功能定位,国网商旅内网网址,外贸模板网站深圳,国内wordpress主题免费今日内容 python操作MySQL(重要) SQL注入问题(安全相关的xss,csrf) 视图(了解) 触发器(了解) 事务(重要) 存储过程(了解) 内置函数(了解#xff0c;很多) 流程控制(了解) 索引(重点)
python操作MySQL
MySQL本身就是一款c/s架构#xff0c;有服务端、有客户端…今日内容 python操作MySQL(重要) SQL注入问题(安全相关的xss,csrf) 视图(了解) 触发器(了解) 事务(重要) 存储过程(了解) 内置函数(了解很多) 流程控制(了解) 索引(重点)
python操作MySQL
MySQL本身就是一款c/s架构有服务端、有客户端自身带了有客户端mysql.exe python这门语言成为了MySQL的客户端对于一个服务端来说客户端可以有很多 操作步骤 1.先连接MySQL host、port、username、password、charset、库等 2.在python中书写SQL语句 3.开始执行SQL语句拿到结果 4.在python中做处理(进一步对数据处理 需要使用第三方一个模块pymysql mysqldb mysqlclient pip install pymysql import pymysql 1.先连接MySQL
connpymysql.connect(host127.0.0.1,port3306,userroot,password1234,dbdb8,charsetutf8,autocomitTrue # 自动执行二次提交
)2.获取游标
curconn.cursor(cursorpymysql.cursors.DictCursor)3.写SQL语句
sqlinseert into teacher(tid,tname) values (7,ly1)4.开始执行SQL语句
affect_rowscur.execute(sql) # 16 影响的行数
需要执行二次确认除了查询之外都要二次确认提交
print(affect_rows)5.想获取到结果
#rescur.fetchone() # 取单个
#rescur.fetchmany(5) # 取指定个数
rescur.fetchall() # 取所有 元组类型
print(res)for i in res:print(i.get(sid))SQL注入问题
import pymysqlconn pymysql.connect(host127.0.0.1,port3306,userroot,password123,databasedb8,charsetutf8,autocommitTrue
)cur conn.cursor(cursorpymysql.cursors.DictCursor)inp_name input(username:).strip()
inp_pwd input(password:).strip()sql select * from userinfo where username%s and password%s % (inp_name,inp_pwd)affect_rowscur.execute(sql)rescur.fetchone()
if res:print(登陆成功)
else:print(登陆失败)
SQL注入的原因是由于特殊符号的组合会产生特殊的效果 实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用 原因也是一样的 结论设计到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
在使用代码进行数据操作的时候不同操作的时候 不同操作的级别是不一样的 针对查无所谓 针对增 改 删都需要二次确认 conn.commit()
视图
1.什么是视图 视图就是通过查询得到一张虚拟表然后保存下来下次直接使用即可 2.为什么要用视图 如果要频繁使用一张虚拟表可以不用重复查询 3.如何使用视图 create view teacher2course as select * from teacher inner jion course on teacher,tid course.teacher_id; 创建好了之后 验证它的存在navicat验证 cmd终端验证 最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表 delete from teacher2course where id1 强调
1.在硬盘中视图只有表结构文件没有表数据文件 2.视图通常是用于查询尽量不要修改视图中的数据
思考开发过程中会不会去使用视图
不会视图是mysql的功能如果你的项目里面大量的使用到了视图那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改意味着你需要先在mysql这边将视图先修改一下然后再去应用程序中修改对应的sql语句这就涉及到跨部门沟通的问题所以通常不会使用视图而是通过重新修改sql语句来扩展功能 触发器
在满足对某张表数据的增、删、改的情况下自动触发的功能称之为触发器
为何要用触发器
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为这类行为一旦执行就会触发触发器的执行即自动运行另外一段sql代码
创建触发器语法
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
beginsql语句
end# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
beginsql代码...
end
create trigger tri_after_insert_t2 before insert on 表名 for each row
beginsql代码...
end# 针对删除
create trigger tri_after_delete_t1 afer delete on 表名 for each row
beginsql代码...
end
create trigger tri_after_delete_t2 afer delete on 表名 for each row
beginsql代码...
end# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
beginsql代码...
end
create trigger tri_after_update_t2 before update on 表名 for each row
beginsql代码...
end
需要注意 在书写sql代码的时候结束符是而整个触发器的结束也需要分号 这就会出现语法冲突 需要我们临时修改结束符号 delimiter $$ delimiter ; 该语法只在当前窗口有效
案例
mysql CREATE TABLE cmd (- id INT PRIMARY KEY auto_increment,- USER CHAR (32),- priv CHAR (10),- cmd CHAR (64),- sub_time datetime, #提交时间- success enum (yes, no) #0代表执行失败- );
Query OK, 0 rows affected (0.03 sec)mysql create table errlog(- id int primary key auto_increment,- err_cmd char(64),- err_time datetime);
Query OK, 0 rows affected (0.02 sec)mysql create trigger tri_after_insert_cmd after insert on cmd for each row- begin- if NEW.success no then # 新记录都会被MySQL封装成NEW对象- insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);- end if;- end $$
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;
mysql insert into cmd (- user,- priv,- cmd,- sub_time,- success- )- values- (egon,0755,ls -l /etc,NOW(),yes),- (egon,0755,cat /etc/passwd,NOW(),no),- (egon,0755,useradd xxx,NOW(),no),- (egon,0755,ps aux,NOW(),yes);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0查询errlog表记录
mysql select * from errlog;
-------------------------------------------------------------------------------------------
| id | err_cmd | err_time |
-------------------------------------------------------------------------------------------
| 1 | cat /etc/passwd | 2023-10-26 17:16:39 |
| 2 | useradd xxx | 2023-10-26 17:16:39 |
-------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)删除触发器
mysql drop trigger tri_after_insert_cmd;
Query OK, 0 rows affected (0.00 sec) 事务
什么是事务 开启一个事务可以包含一些sql语句这些sql语句要么同时成功 要么一个都别想成功称之为事务的有原子性 事务的作用
保证了对数据操作的数据安全性 举例用交行的卡操作建行ATM机给工商的账户转钱
事务应该具有4个属性原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 原子性atomicity一个事务是一个不可分割的工作单位事务中包括的诸操作要么都做要么不做。 一致性consistency事务是必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 隔离新isolation一个事务的执行不能被其他事务干扰即一个事务内部的操作及使用的数据对并发的其他事务时隔离的并发执行的各个事务之间不能干扰。 持久性durabili持久性也称永久性permanence指一个事务一旦提交他对数据库中数据的改变就是永久性的接下来的其他操作或故障不应该对其有任何影响。
如何用
mysql create table user(- id int primary key auto_increment,- name char(32),- balance int- );
Query OK, 0 rows affected (0.03 sec)mysql insert into user(name,balance)- values- (jason,1000),- (egon,1000),- (tank,1000);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from user;
-----------------------------------------------
| id | name | balance |
-----------------------------------------------
| 1 | jason | 1000 |
| 2 | egon | 1000 |
| 3 | tank | 1000 |
-----------------------------------------------
3 rows in set (0.00 sec)mysql start transaction;
Query OK, 0 rows affected (0.00 sec)mysql update user set balance900 where namejsaon;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0mysql update user set balance1010 where nameegon;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql update user set balance1090 where nametank;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from user;
-----------------------------------------------
| id | name | balance |
-----------------------------------------------
| 1 | jason | 1000 |
| 2 | egon | 1010 |
| 3 | tank | 1090 |
-----------------------------------------------
3 rows in set (0.00 sec)mysql rollback; # 回滚到上一个状态
Query OK, 0 rows affected (0.00 sec)mysql commit; # 开启事务之后只要没有执行commit操作数据其实都没有真正刷新到硬盘
Query OK, 0 rows affected (0.00 sec)
站在python代码的角度应该实现的伪代码逻辑 try:# 少了开事务...update user set balance900 where namejason; #买支付100元update user set balance1010 where nameegon; #中介拿走10元update user set balance1090 where nametank; #卖家拿到90元
except 异常:rollback;
else:commit;
存储过程
存储过程包含了一系列可执行的sql语句存储过程存放于MySQL中通过调用它的名字可以执行其内部的一堆sql类似于python中的自定义函数
基本使用
mysql delimiter $$
mysql create procedure p1()- begin- select * from user;- end $$
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;
mysql call p1()- ;
-----------------------------------------------
| id | name | balance |
-----------------------------------------------
| 1 | jason | 1000 |
| 2 | egon | 1000 |
| 3 | tank | 1000 |
-----------------------------------------------
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.01 sec) 三种开发者模型 第一种 应用程序只需要开发应用程序的逻辑 mysql编写好存储过程以供应程序调用 优点开发效率执行效率都高 缺点考虑到人为因素、跨部门沟通等问题会导致扩展性差 第二种 应用程序除了开发应用程序逻辑还需要编写原生sql 优点比方式1扩展性高非技术性 缺点 1.开发效率执行效率都不如方式1 2.编写原生sql太过于复杂而且需要考虑到sql语句的优化问题 第三种 应用程序开发应用程序的逻辑不需要编写原生sql基于别人写好的框架来处理ORM 优点不用再编写纯生sql这意味着开发效率比方式2高同时兼容方式2扩展性高的好处 缺点执行效率连方式2都比不过 创建存储过程
mysql set res10;
Query OK, 0 rows affected (0.00 sec)mysql delimiter $$
mysql create procedure p2(- in m int, # in表示这个参数必须只能是传入不能被返回出去- in n int,- out res int # out表示这个参数可以被返回出去- )- begin- select tname from teacher where tid m and tid n;- set res0; # 用来标志存储过程是否执行- end $$
Query OK, 0 rows affected (0.00 sec)mysql call p2(1,5,res);
-----------------
| tname |
-----------------
| 李平老师 |
| 刘海燕老师 |
| 朱云海老师 |
-----------------
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)# 针对res需要先提前定义
set res10; 定义
select res; 查看
call p1(1,5,res) 调用
select res 查看
如何用存储过程
大前提存储过程在哪个库下面创建的只能在对应的库下面才能使用 1.直接在mysql中调用 set res10 # res的值是用来判断存储过程是否被执行成功的依据所以需要先定义一个变量res存储10 call p1(2,4,10); # 报错 call p1(2,4,res); 查看结果 select res# 执行成功。res变量值发生了变化 2、在python中调用 pymysql连接mysql 产生的游表cursor.callproc(p1,(2,4,10)) # 内部原理_p1_02,_p1_14,_p1_210; cursor.execute(select _p1_2;) 3、存储过程与事务使用举例(了解) delimiter // create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code 2; rollback; END; START TRANSACTION; update user set balance900 where id 1; update user123 set balance1010 where id 2; update user set balance1090 where id 3; COMMIT; -- SUCCESS set p_return_code 0; #0代表执行成功 END // delimiter ; 函数
注意与存储过程的区别mysql内置的函数只能在sql语句中使用 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);---------------------------------------------------------------
| id | NAME | sub_time | month
---------------------------------------------------------------
| 1 | 第1篇 | 2015-03-01 11:31:21 | 2015-03
| 2 | 第2篇 | 2015-03-11 16:31:21 | 2015-03
| 3 | 第3篇 | 2016-07-01 10:21:31 | 2016-07
| 4 | 第4篇 | 2016-07-22 09:23:21 | 2016-07
| 5 | 第5篇 | 2016-07-23 10:11:11 | 2016-07
| 6 | 第6篇 | 2016-07-25 11:21:31 | 2016-07
| 7 | 第7篇 | 2017-03-01 15:33:21 | 2017-03
| 8 | 第8篇 | 2017-03-01 17:32:21 | 2017-03
| 9 | 第9篇 | 2017-03-01 18:31:21 | 2017-03
---------------------------------------------------------------select count(*) from blog group by month;select date_format(sub_time,%Y-%m),count(id) from blog group by date_format(sub_time,%Y-%m);
https://blog.csdn.net/GG_Bruse/article/details/131484538
流程控制
# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGINdeclare i int default 0;if i 1 THENSELECT 1;ELSEIF i 2 THENSELECT 2;ELSESELECT 7;END IF;END //
delimiter ;# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGINDECLARE num INT ;SET num 0 ;WHILE num 10 DOSELECTnum ;SET num num 1 ;END WHILE ;END //
delimiter ;
索引
数据都是存在硬盘上的那查询数据不可避免的需要IO操作
索引就是一种数据结构类似于书的目录。意味着以后再查数据应该先找目录再找数据而不是用翻页的方式查询数据
索引在MySQL中也叫做’键‘是存储引擎用于快速找到记录的一种数据结构 primary key、unique key、index key 注意上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一unique key:唯一)而index key没有任何约束功能只会帮你加速查询
本质都是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果同时把随机的事件变成顺序的事件也就是说有了这种索引机制我们可以总是用同一种查找方式来锁定数据。
索引的影响 在表中有大量数据的前提下创建索引速度会很慢(建表的时候如果明显需要索引就提前加上 以后实际添加索引的时候尽量在空表的时候添加在创建表的时候就添加索引此时添加索引是最快的 如果表中数据已经有了还需要添加索引也可以只不过创建索引的速度会很慢不建议这样做 在索引创建完毕后对表的查询性能会大幅度提升但是写的性能会降低 但是写的性能影响不是很大因为在实际中写的频率很少大部分操作都是查询 如何添加索引到底给哪些字段加索引呢? 没有固定答案具体给哪个字段加索引要看你实际的查询条件 select * from user where name and password; 索引的使用其实是需要大量的工作经验才能正确的判断出 不要一创建表就加索引在一张表中最多最多不要超过15个索引索引越多性能就会下降 如何数据量比较小不需要加索引100w一下一般不用加mysql针对于1000w一下的数据性能不会下降太多. b树
只有叶子结点存放真实数据根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定层级越低次数越少
一个磁盘块儿的大小是一定的那也就意味着能存的数据量是一定的。如何保证树的层级最低呢一个磁盘块儿存放占用空间比较小的数据项 以后加索引的时候尽量给字段中存的是数字的列加,我们使用主键查询速度很快 select * from user where name select * from user where id # 主键查询的更快一些 思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度 主键id字段
