python操作MySQL

  1. # 导入第三方模块
  2. import pymysql
  4. # 链接服务端
  5. db = pymysql.connect(
  6.     host='127.0.0.1',  # MySQL服务的IP地址
  7.     port=3306,  # MySQL默认PORT端口号
  8.     user='root',  # 用户名
  9.     password='',  # 密码（简写：passwd）
  10.     database='test',  # 数据库名称（简写：db）
  11.     charset='utf8'  # 字符编码
  12.       # autocommit=True  # 自动二次确认
  13. )
  15. # 产生获取命令的游标对象
  16. cursor = db.cursor(
  17.     cursor=pymysql.cursors.DictCursor
  18. )  # 括号内不写参数，数据是元组要元组 不够精确 添加参数则会将数据处理成字典
  20. sql = 'show tables'
  22. # 执行SQL语句，并返回受影响的行数
  23. affect_row = cursor.execute(sql)
  24. print(affect_row)
  26. # 注意：insert、update、delete 等修改数据的语句需要二次确认
  27. conn_obj.commit()  # 手动二次确认
  30. # 获取SQL语句执行后的结果
  31. res = cursor.fetchall()
  32. print(res)

查询数据

  • fetchone()：获取查询结果的所有行。将所有行作为元组列表返回。如果没有要获取的记录，则返回一个空列表。
  • fetchmany(size)：返回参数指定的行数size。当重复调用时，此方法获取查询结果的下一组行并返回元组列表。如果没有更多行可用，则返回一个空列表
  • fetchall()：该方法返回单个行记录，如果没有更多行可用，则返回 None

游标控制

  • cursor.scroll(1, 'relative')：相对于当前位置游标往后移动一个单位
  • cursor.scroll(1, 'absolute')：相对于起始位置游标往后移动一个单位

事务处理

  • 开启事务 db.begin()
  • 提交修改 db.commit()
  • 回滚事务 db.rollback()

SQL注入问题

准备数据

  1. create table admin(username varchar(32),password varchar(32));
  2. insert into admin (username,password) values('kevin','123');

注入问题

  1. import pymysql
  3. db = pymysql.connect(
  4.     host='127.0.0.1',
  5.     port=3306,
  6.     user='root',
  7.     password='',
  8.     database='test',
  9.     charset='utf8'
  10. )
  12. cursor = db.cursor(
  13.     cursor=pymysql.cursors.DictCursor
  14. )
  16. username = input("请输入用户名>>>").strip()
  17. password = input("请输入密码>>>").strip()
  19. sql = "select username,password from admin where username = '%s' and password = '%s'" % (username, password)
  21. affect_row = cursor.execute(sql)
  22. res = cursor.fetchall()
  23. if res:
  24.     print("登录成功")
  25. else:
  26.     print("用户名或密码错误")

这样会导致如果用户输入' or 1=1 #恶意字符串，拼接在SQL语句也会登录成功

  1. select username,password from admin where username = '' or 1=1 #' and password = ''

解决办法

解决SQL注入的问题其实也很简单就是过滤掉特殊符号，execute方法自带校验SQL注入问题 自动处理特殊符号

  1. sql = "select username,password from admin where username = %s and password = %s"
  2. cursor.execute(sql, (username, password))

补充：以后涉及到敏感数据的拼接全部交给execute方法就好了

修改表SQL语句

修改表名字

关键字：alter 、rename

  1. alter table 表名称 rename 新表名;

添加字段

关键字：alter 、add

  1. alter table 表名称 add 列名称  数据类型;  -- 默认是尾部追加字段
  2. alter table 表名称 add 列名称  数据类型 after 列名称;  -- 指定追加位置
  3. alter table 表名称 add 列名称  数据类型 first;  -- 指定头部添加字段

修改字段

关键字：alter 、change（change名字类型都可以修改modify只能改类型不能改名字）

  1. alter table 表名称 change 列名称 新列名称 新数据类型;

删除字段

关键字：alter 、drop

  1. alter table 表名称 drop 列名称;

MySQL视图

  1. 视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
  3. 当频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度。但是会造成表的混乱 毕竟视图不是真正的数据源，而且视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)

视图的优点：

  1. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  2. 安全：使用视图的用户只能访问他们被允许查询的结果，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
  3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响，源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
    总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

视图的缺点：

  1. 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。
  2. 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

语法

  1. create view 视图名 as select 查询语句;

MySQL触发器

什么是触发器

  1. 触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。简单理解为：你执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句。

触发器的作用

  • 可在写入数据表前，强制检验或转换数据。
  • 触发器发生错误时，异动的结果会被撤销。
  • 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
  • 可依照特定的情况，替换异动的指令 (INSTEAD OF)。

触发器创建的四要素

  • 监视地点（table）
  • 监视事件（insert、update、delete）
  • 触发时间（after、before）
  • 触发事件（insert、update、delete）

触发器的使用语法

  1. create trigger 触发器名
  2. before|after delete|insert|update
  3. on 表名 for each row
  4. begin
  5. 触发SQL代码块;
  6. end;
  • before/after： 触发器是在增删改之前执行，还是之后执行
  • delete/insert/update： 触发器由哪些行为触发（增、删、改）
  • on 表名： 触发器监视哪张表的（增、删、改）操作
  • 触发SQL代码块： 执行触发器包含的SQL语句

案例

数据准备

  1. CREATE TABLE cmd (
  2.     id INT PRIMARY KEY auto_increment,
  3.     USER CHAR (32),
  4.     priv CHAR (10),
  5.     cmd CHAR (64),
  6.     sub_time datetime,  -- 提交时间
  7.     success enum ('yes', 'no')  -- 0代表执行失败
  8. );
  9. CREATE TABLE errlog (
  10.     id INT PRIMARY KEY auto_increment,
  11.     err_cmd CHAR (64),
  12.     err_time datetime
  13. );

需求：cmd表插入数据的success如果值为no 则去errlog表中插入一条记录

  1. delimiter $$  # 将mysql默认的结束符由;换成$$
  2.     create trigger tri_after_insert_cmd after insert on cmd for each row
  3.     begin
  4.         if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
  5.             insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  6.         end if;
  7.     end $$
  8.     delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
  1. INSERT INTO cmd (
  2.       USER,
  3.       priv,
  4.       cmd,
  5.       sub_time,
  6.       success
  7. )VALUES
  8.     ('kevin','0755','ls -l /etc',NOW(),'yes'),
  9.     ('kevin','0755','cat /etc/passwd',NOW(),'no'),
  10.     ('kevin','0755','useradd xxx',NOW(),'no'),
  11.     ('kevin','0755','ps aux',NOW(),'yes');

补充：

  1. 临时修改SQL语句的结束符delimiter 结束符，临时修改的原因是因为触发器存储过程等技术点，代码中也需要使用分号，如果不修改则会冲突
  2. 查看当前库下所有的触发器信息：show triggers\G;
  3. 删除当前库下指定的触发器信息：drop trigger 触发器名称;

更多文章：https://zhuanlan.zhihu.com/p/147736116

MySQL事务

  1. MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务
  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

事务的四大特性（ACID）

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

使用

数据准备

  1. create table user(
  2.   id int primary key auto_increment,
  3.   name char(32),
  4.   balance int
  5.   );
  6. insert into user(name,balance)
  7.   values
  8.   ('kevin',1000),
  9.   ('tom',1000),
  10.   ('jerry',1000);

事务操作

  1. start transaction; -- 开启一个事务的操作 or begin
  3. -- 编写SQL语句（同属于一个事务）
  4. update user set balance=900 where name='kevin';
  5. update user set balance=1010 where name='tom';
  6. update user set balance=1090 where name='jerry';
  8. -- 事务回滚（返回执行事务操作之前的数据库状态）
  9. rollback;  -- 执行完回滚之后，事务自动结束
  11. -- 事务确认（执行完事务的主动操作之后，确认无误之后，需要执行确认命令）
  12. commit;  -- 执行完确认提交之后，无法回滚事，务自动结束
  • begin ：开始一个事务
  • rollback ：事务回滚
  • commit ：事务确认

更多文章：https://zhuanlan.zhihu.com/p/29166694

MySQL存储过程

什么是存储过程

  1. 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

存储过程的特点

  • 能完成较复杂的判断和运算
  • 可编程行强，灵活
  • SQL编程的代码可重复使用
  • 执行的速度相对快一些
  • 减少网络之间的数据传输，节省开销

存储过程的使用语法

无参

  1. delimiter $$
  2. create procedure  存储过程名()
  3. begin
  4.     SQL语句;
  5. end $$
  6. delimiter ;
  8. -- 调用存储过程
  9. call 存储过程名();
  11. -- 查看存储过程具体信息
  12. show create procedure 存储过程名;
  14. -- 查看所有存储过程
  15. show procedure status;
  17. -- 删除存储过程
  18. show procedure 存储过程名;

有参

  1. delimiter $$
  2. create procedure 存储过程名([[in |out |inout ] 参数名 数据类形...])
  3. begin
  4.     SQL语句;
  5. end $$
  6. delimiter ;
  • in 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
  • out 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
  • inout 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

有参使用

  1. create table userinfo(
  2.     id int primary key auto_increment,
  3.   name varchar(32),
  4.   password varchar(32)
  6. );
  8. insert into userinfo (name,password) values ('kevin','123'),('tom','312'),('jason','312'),('jerry','132'),('emma','213');
  1. delimiter $$
  2. create procedure p(
  3.     in m int,  -- in表示这个参数必须只能是传入不能被返回出去
  4.   in n int,
  5.   out res int  -- out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
  6. )
  7. begin
  8.     select name from userinfo where id >m and id < n;
  9.     set res=0;  -- 用来标志存储过程是否执行
  10. end $$
  11. delimiter ;
  13. # 针对res需要先提前定义
  14. set @res=10; -- 定义
  15. select @res; -- 查看
  16. call p(1,5,@res) -- 调用
  17. select @res; -- 再次查看

MySQL函数

与存储对象区别在于，mysql内置的函数只能在SQL语句中使用

准备数据

  1. drop table if exists userinfo;
  2. create table userinfo(
  3.   id int primary key auto_increment,
  4.   sex tinyint,
  5.   name varchar(32)
  6. );
  8. insert into userinfo (sex,name) values(1,'kevin'),(1,'tom'),(2,'jerry'),(1,'jason'),(2,'emma');

if函数

语法

  1. if(条件表达式,值1,值2);
  • 当条件表达式为true返回值1，否则返回值2
  1. select name as 名字, if(sex=1,'男','女') as 性别 from userinfo;

更多：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

MySQL流程控制

if结构

语法

  1. if 条件语句1 then 
  2.     语句1;
  3. elseif 条件语句2 then 
  4.     语句2;
  5. else 
  6.     语句3;
  7. end if;

while循环

语法

  1. while 条件 DO
  2.       循环体;
  3.    end while;

更多：https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

MySQL索引

类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构

  • primary key：主键
  • unique key：唯一键
  • index key：索引键

上面三种“键”前两种除了有加速查询的效果之外，还有额外的约束条件（primary key:非空且唯一，unique key:唯一），而index key没有任何约束功能只会帮你加速查询

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响：

  • 在表中有大量数据的前提下，创建索引速度会很慢
  • 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

更多文章：https://juejin.cn/post/6931901822231642125