python操作MySQL

使用过程:
  引用API模块
  获取与数据库的连接
  执行sql语句与存储过程
  关闭数据库连接

  由于能操作MySQL的模块是第三方模块，我们需要pip安装。
    pip3 install pymysql


import pymysql
1.连接数据库
conn = pymysql.connect(
  host='localhost', #MySQL服务端的IP地址或者'127.0.0.1'
  port = 3306, # MySQL默认PORT地址(端口号)
  user = 'root', # 用户名
  passwd = '123456', # 密码  也可以简写 passwd
  database = 'test', # 库名称  也可以简写 db(db='test')
  charset = 'utf8' # 字符编码 千万不要加杠utf-8
)
'''
  要想操作数据库，光连接数据是不够的，必须拿到操作数据库的游标，才能进行后续的操作，比如
读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标。游标用来接收返回结果
'''
2.产生游标对象
cursor=conn.cursor(
  cursor=pymysql.cursors.DictCursor# 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典
)  #产生游标

3.SQL语句编写
sql = 'select * from class'

4.执行SQL语句
affect_rows = cursor.execute(sql)
print(affect_rows)  # 执行SQL语句之后受影响的行数

5.获取结果
res = cursor.fetchall()
print(res)

执行sql语句execute和executemany

execute(query,args=None)
  函数作用：执行单条的sql语句，执行成功后返回受影响的行数
  参数说明：
  query：要执行的sql语句，字符串类型
  args：可选的序列或映射，用于query的参数值。如果args为序列，query中必须使用%s做占位符；如果args为映射，query中必须使用%(key)s做占位符

executemany(query,args=None)
  函数作用：批量执行sql语句，比如批量插入数据，执行成功后返回受影响的行数
  参数说明：
  query：要执行的sql语句，字符串类型
  args：嵌套的序列或映射，用于query的参数值

创建数据库案例

'''创建数据库'''
import pymysql
#打开数据库连接，不需要指定数据库，因为需要创建数据库
conn = pymysql.connect('localhost',user = "root",passwd = "123456")
#获取游标
cursor=conn.cursor()
#创建pythonBD数据库
cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
print('创建pythonBD数据库成功')

插入单条数据

'''插入单条数据'''
import pymysql
#打开数据库连接，不指定数据库
conn=pymysql.connect('localhost','root','123456')
conn.select_db('pythondb')

cur=conn.cursor()#获取游标

#创建user表
cur.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cur.execute(sql)

insert=cur.execute("insert into user values(1,'tom',18)")
print('添加语句受影响的行数：',insert)

#另一种插入数据的方式，通过字符串传入值
sql="insert into user values(%s,%s,%s)"
cur.execute(sql,(3,'kongsh',20))

cur.close()
conn.commit()
conn.close()
print('sql执行成功')

批量插入多条数据

'''插入多条数据'''
import pymysql
#打开数据库连接，不指定数据库
conn=pymysql.connect('localhost','root','123456')
conn.select_db('pythondb')
#获取游标
cur=conn.cursor()

#另一种插入数据的方式，通过字符串传入值
sql="insert into user values(%s,%s,%s)"
insert=cur.executemany(sql,[(4,'wen',20),(5,'tom',10),(6,'test',30)])
print ('批量插入返回受影响的行数：',insert)
cur.close()
conn.commit()
conn.close()
print('sql执行成功')

注意：批量插入多条sql语句采用的是executemany(sql,args)函数，返回受影响的行
数。args参数是一个包含多个元组的列表，每个元组对应一条mysql中的一条数据。这里的%s不需要
加引号，否则插入数据的数据会类型错误。

查询数据

使用execute()函数得到的只是受影响的行数，并不能真正拿到查询的内容。cursor对象还提供了3
种提取数据的方法：fetchone、fetchmany、fetchall.。每个方法都会导致游标动，所以必须
注意游标的位置。

cursor.fetchone():获取游标所在处的一行数据，返回元组，没有返回None
cursor.fetchmany(size):接受size行返回结果行
cursor. fetchall():接收全部的返回结果行。

fetchone()

  从execute()函数的查询结果中取数据，以元组的形式返回游标所在处的一条数据，如果游标
所在处没有数据，将返回空元组，该数据执行一次，游标向下移动一个位置。fetchone()函数必须
跟exceute()函数结合使用，并且在exceute()函数之后使用。

fetchmany()

  从exceute()函数结果中获取游标所在处的size条数据，并以元组的形式返回，元组的每一个元素
都也是一个由一行数据组成的元组，如果size大于有效的结果行数，将会返回cursor.arraysize条
数据，但如果游标所在处没有数据，将返回空元组。查询几条数据，游标将会向下移动几个位置。
fetmany()函数必须跟exceute()函数结合使用，并且在exceute()函数之后使用。

fetchall()

  获取游标所在处开始及以下所有的数据，并以元组的形式返回，元组的每一个元素都也是一个由一
行数据组成的元组，如果游标所在处没有数据，将返回空元组。执行完这个方法后，游标将移动到数
据库表的最后。

控制光标

移动光标，scroll(value, mode)方法
参数：
    当mode='relative'时，代表相对移动，默认值，value就是移动的长度，
    value>0向后移动（从位置0移动到位置2），value<0向前移动（比如从位置2移动到位置0）

    当mode='absolute'时，代表绝对移动，value就代表移动的绝对位置，
    value=0就代表移动到位置0处，就是结果集开头，value=3就是移动到位置3处，也就是第4条记录处。
  cursor.scroll(1, 'relative')  # 相对于当前位置往后移动一个单位
  cursor.scroll(1, 'absolute')  # 相对于起始位置往后移动一个单位

SQL注入

  SQL 注入是一种非常常见的数据库攻击手段，SQL 注入漏洞也是网络世界中最普遍的漏洞 之一。
  SQL 注入其实就是恶意用户通过在表单中填写包含 SQL 关键字的数据来使数据库执行非常 规代码
的过程。简单来说，就是数据「越俎代庖」（yuè zǔ dài páo）做了代码才能干的 事情。这个问题
的来源是，SQL 数据库的操作是通过 SQL 语句来执行的，而无论是执行代 码还是数据项都必须写在
 SQL 语句之中，这就导致如果我们在数据项中加入了某些 SQL 语 句关键字（比如说 SELECT、
DROP 等等），这些关键字就很可能在数据库写入或读取数据 时得到执行。


在正常的情况下，在登入时，我们需要输入正确的用户名和密码才能登入。
import pymysql
1.连接数据库
conn = pymysql.connect(
  host='localhost',
  port = 3306,
  user = 'root',
  passwd = '123456',
  database = 'test',
  charset = 'utf8'
)
cursor=conn.cursor(
  cursor=pymysql.cursors.DictCursor
)
# 1.获取用户名和密码
name = input('请输入您的用户名>>>:').strip()
password = input('请输入您的密码>>>:').strip()
# 2.拼接查询语句
sql = "select * from userinfo where name=%s and password=%s;"
# 3.执行SQL语句
cursor.execute(sql, (name, password))
res = cursor.fetchall()
if res:
    print('登录成功')
else:
    print('用户名或密码错误')

# 写正确的用户名错误的密码也可以登录
    用户名:春游去动物园' -- 1111
相当与select * from userinfo where name='春游去动物园' --1111 and password=%s;在MySQL中--表示注释，那么密码验证部分就被注释了。
        密码:直接回车
# 用户名和密码都不需要也可以登录
    用户名:xxx' or 1=1 -- 1111
相当与select * from userinfo where name='春游去动物园' or 1=1 --1111 and password=%s;因为or运算且1=1恒成立，所以不需要用户名和密码直接登入。
        密码:直接回车
"""上述现象就是典型的SQL注入问题"""
    上述情况利用的是MySQL注释语法及逻辑运算符

# 解决SQL注入的问题其实也很简单 就是想办法过滤掉特殊符号
    execute方法自带校验SQL注入问题 自动处理特殊符号
  ps:设计到敏感数据的拼接 全部交给execute方法即可!!!
    sql = "select * from userinfo where name=%s and password=%s;"
    cursor.execute(sql, (name, password))

二次确认

"""
数据的增删改查四个操作是有轻重之分的
    查                           不会影响真正的数据 重要程度最低
    增、改、删                    都会影响真正的数据 重要程度较高
pymysql针对增、改、删三个操作 都设置了二次确认 如果不确认则不会真正影响数据库
"""
方式1:代码直接编写
    affect_row = cursor.execute(sql)
    conn.commit()  # 手动二次确认
方式2:配置固定参数
    conn = pymysql.connect(
    autocommit=True  # 自动二次确认
)

修改表SQL语句

# 1.修改表的名字  rename
    alter table t1 rename ttt;
# 2.添加字段         add
    alter table ttt add pwd int;  '''默认是尾部追加字段'''
    alter table ttt add tid int after name;  '''指定追加位置'''
  alter table ttt add nid int first;  '''指定头部添加字段'''
# 3.修改字段         change(名字类型都可)/modify(只能改类型不能改名字)
    alter table ttt change pwd password tinyint;
# 4.删除字段             drop
    alter table ttt drop nid;

视图

  视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一
样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放
视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出
对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在
视图中的数据也会发生改变。

  视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数
据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另
一个原因是可使复杂的查询易于理解和使用。

视图的制作
    create view 视图名 as sql语句

触发器

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主
动调用而执行的；而存储过程则需要主动调用其名字执行

作用:
  1.可在写入数据前，强制检验或者转换数据(保证护数据安全)
  2.触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

基本语法

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end 自定义的结束符合

delimiter ;

触发时间
  当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后
  before：表中数据发生改变前的状态
  after：表中数据发生改变后的状态
  PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

触发事件
  触发器是针对数据发送改变才会被触发，对应的操作只有
  INSERT
  DELETE
  UPDATE

注意事项
  1.在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中
的两个表可能具有相同名字的触发器
  2.每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

查看当前库下所有的触发器信息
        show triggers\G;
删除当前库下指定的触发器信息
        drop trigger 触发器名称;

案例

    1.先创建两张表
      # 案例
    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代表执行失败
    );

    CREATE TABLE errlog (
        id INT PRIMARY KEY auto_increment,
        err_cmd CHAR (64),
        err_time datetime
    );
  2.需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
    delimiter $$  # 将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 $$
    delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
  3.仅仅往cmd表中插入数据
      INSERT INTO cmd (
          USER,
          priv,
          cmd,
          sub_time,
          success
    )VALUES
        ('kevin','0755','ls -l /etc',NOW(),'yes'),
        ('kevin','0755','cat /etc/passwd',NOW(),'no'),
        ('kevin','0755','useradd xxx',NOW(),'no'),
        ('kevin','0755','ps aux',NOW(),'yes');

事务

定义

  事务即 （transaction） ，是数据库系统区别于文件系统的重要特性之一。在文件系统中，如果
我们正在写文件，但是操作系统崩溃了，那么文件中的数据可能会丢失。但是数据库可以通过事务机
制来确保这一点。

使用事务的目的

  事务会把数据库从一中状态转换为另一种状态。在数据库提交工作时，可以确保要么所有工作都已
经保存了，要么所有修改都不保存。

事务的四大特性

原子性（atomicity）
  所谓原子性，是指整个数据库的每个事务都是不可分割的单位。只有事务中的所有 SQL 语句都执行成功，才算整个事务成功，事务才会被提交。如果事务中任何一个 SQL 语句执行失败，整个事务都应该被回滚。
  场景：如在银行取款机取款，要么取款成功、要么取款失败。不能存在卡里钱扣了，取款机并没有出金额；或者钱取到了，但是卡里并没有减去该金额。

一致性（consistency）
  所谓一致性，是指将数据库从一种一致性状态转换为下一种一致性状态。不允许数据库中的数据出现新老数据都有的情况，要么都是老数据，要么都是新数据。用更书面化的表达就是：数据的完整性约束没有被破坏。
  场景：如在一个用户表中，存在一个身份证号的字段，且身份证号满足唯一约束条件；如果一个事务对身份证号进行了修改，在事务进行提交或回滚后，身份号信息变的不具有唯一性了，这就破坏了事务的一致性。

隔离性（isolation）
  所谓隔离性，是指一个事务的影响在该事务提交前对其他事务都不可见，它通过锁机制来实现。
  场景：多个并行交叉的事务间的操作可以相互分离，即多个事务对于其他事务不可见。

持久性（durability）
  所谓持久性，是指事务一旦被提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。
  场景：事务提交后，所有变化都是永久的，即使数据库崩溃而需要恢复时也能保证恢复后提交的数不会丢失。

事务开启的标志？事务结束的标志？

以第一个DML(insert、update、delete) 语句的执行作为开始，以下面的其中之一作为结束:

  COMMIT 或ROLLBACK 语句
  DDL 或DCL 语句（自动提交）
  用户会话正常结束
  系统异常终了
关于DDL和DCL参考下图：

案例

#事务的创建
/*
隐式的事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id=1;
显示事务：事物具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select、insert、update、delete)
语句1;
语句2;
......
步骤3：结束事务
commit;提交事务
rollback;回滚事务

开启事务的语句
update 表 set 张三丰的余额=500 where name='张三丰';
update 表 set 郭襄的余额=1500 where name='郭襄';
结束事务的语句;

*/
SHOW VARIABLES LIKE 'autocommit';
DROP TABLE IF EXISTS  account;
CREATE TABLE account(
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(20),
        balance DOUBLE

);
INSERT  INTO  account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
#演示事务的使用步骤

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
#结束事务
COMMIT;
SELECT * FROM account;

#试一下回滚的

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='张无忌';
UPDATE account SET balance=1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
#在没有明确的结束标志，只是滞留在内存了。

存储过程

# 相当于定义函数
1. 结束符
DELIMITER $$  #应用于shell指令行

2. 声明存储过程
CREATE PROCEDURE 名称(入参或回参)

3. 开始与结束符
BEGIN
...
END

4. 变量赋值
SET @变量名 = 1

5. 定义变量
DECLARE 变量名 int unsigned default 100;

6. 存储过程体
create function 存储函数名(参数)

7. 调用存储过程
call 存储过程名(入参)

8. 删除存储过程
drop procedure 名称

9. 查看某库中存储过程
show procedure status where db = '数据库名'

10. 查看特定存储过程
show create procedure 数据库名.存储过程名

'''
查看存储过程具体信息
    show create procedure pro1;
查看所有存储过程
    show procedure status;
删除存储过程
    drop procedure pro1;
'''

参数

存储过程的参数用在存储过程定义时，共有三种参数类型。
  IN：表示调用者需要对存储过程传入参数。
  OUT：表示调用者会得到一个或多个返回值。
  INOUT：表示调用者既要传入值，又要传出值。

in输入参数

create PROCEDURE test(in data_in int)
begin
     SELECT data_in;
     set data_in = 5;
     select data_in;
end

set @data = 1

call test(@data)  #首先查出data_in = 1 修改之后 data_in = 5

select @data  #data = 1,因为存储过程中修改的是局部变量，不影响全局

out回传参数

create PROCEDURE test(out data_out int)
begin
     set data_out = 5;
end

set @data = 1

call test(@data)

select @data  #data被修改，因为是回传参数。

inout输出输出参数

create PROCEDURE test(inout data_inout int)
begin
     select data_inout;   #data_inout = 1
     set data_inout = 5;
end

set @data = 1

call test(@data) 

select @data  #data = 5

变量

1. 定义变量
  declare 名称 类型 默认值(可选)

  declare data int default 50
  #或
  declare data int
  set data = 50

2. 变量赋值
  set 变量名 = 

3. 使用用户变量(全局变量)
  create PROCEDURE test()
  begin
       select @data;
       set @data = 5;
       select @data;
  end

  set @data = 1

  call test()

流程控制

IF

  if 条件 then
        子代码
  elseif 条件 then
        子代码
  else
        子代码
  end if;

while循环

  DECLARE num INT ;
  SET num = 0 ;
  WHILE num < 10 DO
    SELECT num ;
    SET num = num + 1 ;
  END WHILE ;

索引

什么是索引

    官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前
面的目录，能加快数据库的查询速度。

    一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的
（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

    我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别
说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引的优势和劣势

优势：
    可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

    通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

      被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
      如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

  索引会占据磁盘空间

  索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅
要保存数据，还有保存或者更新对应的索引文件。


索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
  primary       key     主键
  unique    key 唯一键
  index     key     索引键
'''索引虽然好用 但是不能无限制的创建!!!'''
**索引的影响:**
    * 在表中有大量数据的前提下，创建索引速度会很慢
    * 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

索引的底层数据结构是b+树
    b树 红黑树 二叉树 b*树 b+树
      上述结构都是为了更好的基于树查找到相应的数据

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

"""
聚集索引(primary key)
辅助索引(unique key,index key)
    查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
    select name from user where name='春游去动物园';
非覆盖索引:虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
    select age from user where name='春游去动物园';
"""