MySQL微讲解(五)

MySQL微讲解(五)

python操作MySQL

import pymysql

conn_obj = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的IP地址
    port=3306,  # MySQL默认的端口号
    user='root',  # 用户名
    password='root',  # 密码
    database='ji02',  # 数据库名
    charset='utf8'  # 字符编码，注意不要写成utf-8的格式，会报错
)
cursor = conn_obj.cursor(  # 括号内不写参数，输出的数据是元组套元组
    cursor=pymysql.cursors.DictCursor  # 括号内加入参数，输出的数据就会处理成字典，便于识别
)
sql1 = 'select * from class'  # 编写SQL语句，会高亮显示，但不会报错，结尾可以加分号，也可以不加
affect_rows = cursor.execute(sql1)  # execute会执行SQL语句，会自动识别SQL语句是否有分号
print(affect_rows)  # 输出执行SQl语句之后受影响的行数
# print(cursor.fetchall())  # 获取结果集中的所有数据
# print(cursor.fetchone())  # 获取结果集中的第一个数据
# print(cursor.fetchmany(2))  # 获取结果集中指定个数的数据
cursor.scroll(1,'relative')  # 相对于当前位置往后移动一个单位
print(cursor.fetchall())
cursor.scroll(1,'absolute')  # 相对于起始位置往后移动一个单位
print(cursor.fetchall())

SQL注入问题

# 1.先创建一个基于数据库的登录jinc
	1.数据库代码
    create table login(
    id int primary key auto_increment,
    name varchar(255),
    password int
    );
    insert into login(name,password) values('oscar',123);
    
    2.python代码
    import pymysql

    conn_obj = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='root',
        database='jp03',
        charset='utf8'
    )
    cursor = conn_obj.cursor(
        cursor=pymysql.cursors.DictCursor
    )
    username = input('username>>>:').strip()
    password = input('password>>>:').strip()
    sql = "select * from login where name = '%s' and password = '%s';"%(username,password)
    cursor.execute(sql)
    res = cursor.fetchall()
    if res:
        print('登录成功')
    else:
        print('用户名或密码错误')

# 写正确的用户名和错误的密码也可以登录成功
	用户名:oscar' -- asfsdcxddfweacs
	密码:直接回车
# 用户名和密码都不需要也可以登录成功
	用户名:asdx' or 1=1 -- ascascdcasc
    密码:直接回车
# 注入问题
	上面两种情况就是MySQl典型的注入问题，利用的是MySQL注释语法及逻辑运算符
    execute方法自带校验SQL注入问题，会自动处理特殊符号，所以上述代码我们只要稍作修改就可以解决问题:sql = "select * from login where name = %s and password = %s;"
    cursor.execute(sql(username,password))

二次确认

# 1.操作的等级划分
	数据的增删改查四个操作是有轻重之分的，'查'不会真正的影响数据，重要程度低，'增、删、改'都会真正的影响数据，重要程度较高，pymysql针对增、改、删这三个操作，都设置了二次确认的操作，如果二次确认不通过就不会真正的影响数据库
# 2.添加二次确认的操作
	方式一:代码直接编写
        affect_row = cursor.execute(sql)
        conn_obj.commit()  # 受到二次确认
	方式二:配置固定参数
        conn_obj = pymysql.connect(
        	autocommit = True  # 字典二次确认
        )

修改表的SQL语句

# 1.修改表的名字
alter table 旧表名 rename 新表名;
# 2.添加字段
alter table 表名 add 字段名称 字段类型;  # 默认是尾部追加字段
alter table 表名 add 字段名称 字段类型 after 追加位置的字段名称;  # 指定追加位置
alter table 表名 add 字段名称 字段类型 first;  # 指定头部添加字段
# 3.修改字段
	修改字段有两个关键字，'change'和'modify'，change可以修改字段名称和字段类型，modify只能修改字段类型，不能修改字段名称
    alter table 表名 change 旧字段名称 新字段名称 新字段类型;
    alter table 表名 modify  字段名称 新字段类型;
# 4.删除字段
	alter table 表名 drop 字段名称;

视图

# 1.视图的概念
	通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为'视图'
# 2.视图的作用
	如果频繁的操作、使用一张虚拟表，可以考虑制作成视图，降低操作难度
# 3.视图的制作
	create view 视图名 as SQL语句
# 4.视图的缺点
	视图虽然很好用，但是不建议多建立，因为会造成表的混乱，毕竟视图不是真正的数据；视图只能用于数据的查询，不能做增、删、改的操作，因为可能会影响到原始的数据，视图里面的数据是直接来源于原始表的，并不是拷贝的。

触发器

# 1.触发器的概念
	在对表数据进行增、删、改的具体操作下，自动触发的功能
# 2.触发器的作用
	专门针对表数据的操作，定制个性化配套功能
# 3.触发器的种类
	1.表数据新增之前、之后
    2.表数据修改之前、之后
    3.表数据删除之前、之后
# 4.触发器创建
	create trigger 触发器名字 before/after insert/update/delete
    on 表名 for each row 
    	begin
        	SQL语句
        end
'''
触发器的名字一般情况下建议采用下列布局:
tri_after_insert
tri_before_update
tri_before_delete
'''
# 5.代码演示
	1.临时修改SQL语句的结束符
    	delimiter $$
        临时修改结束符的原因是因为创建触发器需要使用到分号，如果不修改就没有办法书写书完整的代码。
	2.创建两张表
    create table cmd(
    id int primary key auto_increment,
    user char(32),
    priv char(32),
    cmd char(32),
    sub_time datetime,
    success enum ('yes','no')
    );
    create table errlog(
    id int primary key auto_increment,
    err_cmd char(32),
    err_time datetime
    );
    3.需求:cmd插入数据的时候，如果'success'的值是'no'，就去errlog表中也插入一条记录
        delimiter $$  # 修改结束符号
        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 ;  # 修改结束符号
        # 往cmd表中插入数据，发现errlog表中也有了两条数据
        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');
# 6.删除当前库下指定的触发器
	1.查询当前库下所有的触发器信息
    	show triggers\G;
	2.删除当前库下指定的触发器信息
    	drop trigger 触发器名称;

事物

# 1.事务的概念
	事务可以包含诸多SQL语句，并且这些SQL语句要么同时执行成功，要么同时执行失败，这是事务的原子性特点
# 2.事务四大特性
	1.原子性:一个事务是一个不可分割的整体，里面的操作要么都成立，要么都不成立
	2.一致性:事务必须使数据库从一个一致性状态变到另一个一致性状态
	3.隔离性:并发编程中，多个事务之间是相互隔离的，不会彼此干扰
	4.持久性:事务一旦提交，产生的结果应该是永久性的、不可逆的
# 3.代码演示
create table user(
    id int primary key auto_increment,
    name char(255),
    balance int);
insert into user(name,balance) values('oscar',1000),('jason',1000),('tom',1000);

start transaction;  # 开启一个事务
rollback;  # 事务回滚，返回执行事务之前的数据库状态，执行完回滚事务之后，事务自动结束
commit;  # 确认事务，执行完确认事务之后，就没有办法执行回滚事务了，事务自动结束

存储过程

# 相当于定义函数
delimiter $$
create procedure z1()
begin
     select * from cmd;
end $$
delimiter ;
call z1()  # 相当于调用函数
# 类似于有参函数
delimiter $$
create procedure z2(
     in m int,  # in表示这个参数必须只能是传入不能被返回出去
     in n int,
     out res int  # out表示这个参数可以被返回出去，还有一个inout表示既可以传入，也可以被返回出去
)
     begin
         select user from cmd where id > m and id < n;
         set res = 0;  # 用来标志存储过程是否执行
     end $$
delimiter ;
set @res=10;  # 定义，针对res需要提前定义
select @res;  # 查看
call z2(3,2,@res);  # 调用
show create procedure z2;  # 查看存储过程具体信息
show procedure status;  # 查看所有存储过程
drop procedure z2;  # 删除存储过程

函数

	注意与存储过程的区别，mysql内置的函数只能在sql语句中使用
# 1.查看帮助信息
help 函数名
# 2.移除指定字符
Trim、LTrim、RTrim
# 3.大小写转换
Lower、Upper
# 4.返回读音相似值(对英文有效)
Soundex
例子:客户表中有一个顾客的用户名是J.Lee,但如果是输错了，真实姓名其实是J.Lie,可以使用Soundex匹配发音类似的:
        where Soundex(name) = Soundex('J.Lie');
# 5.日期格式
create table blog(
    id int primary key auto_increment,
    name varchar(255),
    sub_time datetime
    );
INSERT INTO blog (NAME, sub_time)
    VALUES
         ('one','2015-03-01 11:31:21'),
         ('two','2015-03-11 16:31:21'),
         ('three','2016-07-01 10:21:31'),
         ('four','2016-07-22 09:23:21'),
         ('five','2016-07-23 10:11:11'),
         ('six','2016-07-25 11:21:31'),
         ('seven','2017-03-01 15:33:21'),
         ('eight','2017-03-01 17:32:21'),
         ('nine','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
'''
adddate:增加一个日期
addtime:增加一个时间
datediff:计算两个日期差值
'''

流程控制

# 1.if判断
if 条件 then
	子代码
elseif 条件 then
	子代码
else
	子代码
end if;
# 2.while循环
declare num int;
set num = 0;
while num < 10 do
select num;
set num = num + 1;
end while;

索引

# 1.索引是一种数据结构
	类似于书的目录，意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据，索引在MySQL中也叫做'键'，是存储引擎用于快速找到记录的一种数据。
    primary key  # 主键
    unique ker  # 唯一键
    index key   # 索引键
    上面三种键，前面两种处了有加速查询的效果之外，还有额外的约束条件(primary key:非空且唯一,unique key:唯一)，而index key没有任何的约束功能，只会帮你加速查询
# 2.索引的影响
	1.在表中有大量数据的前提下，创建索引速度会很慢
    2.在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
# 3.索引的底层数据结构
	索引的底层数据结构是'b+树'，还有很多的结构，比如b树，红黑树，二叉树，b*树等等，这些结构都是为了更好地基于树查找到相应的数据。
    只有叶子节点存放真实数据，根和树枝节点存的仅仅是虚拟数据，查询次数由树的层级决定，层级越低次数越少。
    聚焦索引:primary key
	辅助索引:unipue key,index key
	查询数据的时候不可能都是用id作为筛选条件的，也可能会用name，password等等，那么这个时候就无法利用聚集索引的加速查询效果，就需要给其他字段建立索引，这些索引就叫辅助索引。
    叶子节点存放的是辅助索引字段对应的那条记录的主键的值，(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})数据查找，如果一开始使用的是辅助索引，那么还需要使用聚焦索引才可以获取到真实数据。
    覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
	非覆盖索引:虽然查询的时候用了索引字段name，但是要查的是age字段，所以还需要利用主键去查找。

这里是IT小白陆禄绯，欢迎各位大佬的指点！！！