Day49

今日总结

用python操作MySQL

# 第三方模块pymysql
    # pymysql使用
	pip3 install pymysql
	import pymysql
    
    # 1.链接服务端
        conn_obj = pymysql.connect(
            host='127.0.0.1',  # MySQL服务端的IP地址
            port=3306,  # MySQL默认的PORT地址(端口号)
            user='root',  # 用户名
            password='jason123',  # 密码  可以简写 passwd
            database='jp04_3',  # 库名称  可以简写 db
            charset='utf8'  # 字符编码 千万不要加杠utf-8
        )
       
     # 2.产生获取命令的游标对象
        cursor = conn_obj.cursor() 
        # 括号内不写参数 数据是元组要元组 不够精确 添加参数则会将数据处理成字典
        cursor = conn_obj.cursor(
            cursor=pymysql.cursors.DictCursor
        )  
    
      # 3.编写SQL语句
          sql1 = 'show tables;'
          sql2 = 'select * from teacher;'
          sql3 = 'select * from score;' 
    
      # 4.执行SQL语句
          affect_rows = cursor.execute(sql1)
          print(affect_rows)  # 执行SQL语句之后受影响的行数
        
      # 5.获取结果
          res = cursor.fetchall()
          print(res)

      '''补充说明'''
      获取SQL语句执行的结果 跟读取文件内容的read方法几乎一致(光标)
      fetchone()	
      fetchmany()
      fetchall()
      cursor.scroll(1, 'relative')  # 相对于当前位置往后移动一个单位
      cursor.scroll(1, 'absolute')  # 相对于起始位置往后移动一个单位

SQL注入、二次确认

# 写正确的用户名错误的密码也可以登录
	用户名:jason' -- jhahsdjasdjasd
 	密码:直接回车
# 用户名和密码都不需要也可以登录
	用户名:xxx' or 1=1 -- asdjasjdkajsd
  	密码:直接回车
"""上述现象就是SQL注入问题"""
	利用MySQL注释语法及逻辑运算符

# 解决SQL注入的问题就是过滤掉特殊符号
	execute方法自带校验SQL注入问题 自动处理特殊符号
 	ps:设计到敏感数据的拼接 全部交给execute方法即可!!!
    sql = "select * from userinfo where name=%s and password=%s;"
	cursor.execute(sql, (name, password))
# 二次确认    
    数据的增删改查四个操作有轻重之分
        查                           不会影响真正的数据，重要程度最低
        增、改、删                    都会影响真正的数据，重要程度较高
    pymysql对增、改、删三个操作都设置了二次确认，如果不确认则不会真正影响数据库
    
    # 方式一
    	affect_row = cursor.execute(sql)
		conn_obj.commit()  # 手动二次确认
	# 方式二
    	conn_obj = 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语句
    
# 视图只能用于数据的查询，不能做增、删、改的操作，视图里面的数据是直接来源于原表，而不是拷贝一份，因此可能会影响原始数据，视图也会造成表的混乱

触发器

# 触发器
	对表数据进行增、删、改的具体操作下，自动触发的功能
# 触发器种类
	表数据新增之前、后
	表数据修改之前、后
	表数据删除之前、后
# 触发器创建方法
	create trigger 触发器名字 before/after insert/update/delete
	on 表名 for each row
		begin
			SQL语句
		end
        
 # 命名
	触发器的名字一般情况下建议采用下列布局形式
		tri_after_insert_t1
		tri_before_update_t2
		tri_before_delete_t3
        
 # 补充
  	查看当前库下所有的触发器信息
    	show triggers\G;
    删除当前库下指定的触发器信息
    	drop trigger 触发器名称;

事务

# 事务
	事务可以包含诸多SQL语句并且这些SQL语句
	要么同时执行成功 要么同时执行失败 这是事务的原子性特点
    
# 事务的四大特性***
	ACID
		A:原子性
			一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
		C:一致性
			事务必须使数据库从一个一致性状态变到另外一个一致性状态
		I:隔离性
			并发编程中 多个事务之间是相互隔离的 不会彼此干扰
		D:持久性
			事务一旦提交 产生的结果应该是永久的 不可逆的
            
# 事务操作
  	# 开启一个事务的操作
    	start transaction;
    # 编写SQL语句(同属于一个事务)
    	update user set balance=900 where name='jason';
			update user set balance=1010 where name='kevin'; 
			update user set balance=1090 where name='tank';
    # 事务回滚(返回执行事务操作之前的数据库状态)
    	rollback;  # 执行完回滚之后 事务自动结束
    # 事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
    	commit;  # 执行完确认提交之后 无法回滚 事务自动结束

存储过程

# 相当于定义函数
delimiter $$
create procedure p1()
begin
	select * from cmd;
end $$
delimiter ;

# 相当于调用函数
call p1()

# 类似于有参函数
delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from userinfo where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

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

函数(mysql内置的函数只能在sql语句中使用)

# 移除指定字符
Trim、LTrim、RTrim

# 大小写转换
Lower、Upper

# 获取左右起始指定个数字符
Left、Right

# 返回读音相似值(英文)
Soundex
"""
eg:表中有一个用户名为jerry可以使用soundex('berry')匹配发音类似的
		where Soundex(name)=Soundex('berry')
"""

流程控制

# js if判断
	if(条件){
    子代码
  }else if(条件){
    子代码
  }else{
    子代码
  }
    
# MySQL if判断
	if 条件 then
        子代码
  elseif 条件 then
        子代码
  else
        子代码
  end if;

# MySQL while循环
	DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT num ;
        SET num = num + 1 ;
    END WHILE ;

索引

# 索引就是一种数据结构
	类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据
  
    索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
        primary	key	主键
        unique key	唯一键
        index key	索引键
    # 前两种除了有加速查询的效果之外还有额外的约束条件,index key没有任何约束功能只会加速查询,foreign key不是用来加速查询用的
    
# 注意事项
	索引虽无限制的创建
    在表中有大量数据的前提下，创建索引速度会很慢
    在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
    
'''
    索引的底层数据结构是b+树
        只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
    查询次数由树的层级决定，层级越低次数越少
'''

要学框架了！！！