mysql知识补充

在python中操作mysql的代码

import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',  # 可以写成passwd
    database='t2',    # 可以写成db
    charset='utf8'
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)  # 生成游标对象等待命令输入
sql='select * from teacher'
res=cursor.execute(sql)
print(res)
res2=cursor.fetchall()
print(res2)

 

 

cursor.execute 返回执行sql语句之后影响的具体数据行数

 

 

 cursor.fetchall  这个是返回所有的具体数据

 

 

 cursor.fetchone() 从结果中获取一条数据

 

 

 print (cursor.fetchmany(2)) 括号内写几个就获取几个数据

print(cursor.fetchone())

cursor.scroll(2, 'relative')

print(cursor.fetchone())

先获取第一条数据,再往后移动两位 到4的位置再读取 

 

 

 正数往右,负数往左

print(cursor.fetchone())

cursor.scroll(-1, 'relative')print

(cursor.fetchone())

 

 

相对于数据的开头往后移动位数 也叫绝对引用 

print(cursor.fetchone())

cursor.scroll(2, 'absolute')

print(cursor.fetchone())

 

 

 SQL注入

pymysql模块针对增删改 需要二次确认

需要在连接mysql 中写入autocommit=True

以登录为例

import pymysql
username = input('username>>>:').strip()
password = input('password>>>:').strip()
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',  # 可以写成passwd
    database='t2',    # 可以写成db
    charset='utf8',
    autocommit=True,
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)  # 生成游标对象等待命令输入
sql="select * from userinfo where username='%s'and password='%s'"%(username,password)
affect_rows=cursor.execute(sql)
res=cursor.fetchall()
if res:
    print(res,'登录成功')
else:
    print('用户名或密码错误')

 

 

 

 

 

 现象1:当输入了正确的用户名而不输密码也可登录

 

 

 现象2:用户名和密码都不需要但获取到了所有数据

 

 

 这两个现象简单点来说就是用了mysql中的 - -注释功能以及where 后面的条件有个恒成立就相当于没有这个条件

 select * from userinfo where name='max' -- ajsdkjaskldjklasd' and password=''

 select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password=''

即利用了一些特殊符号和特殊语法的形式拼接出了违背常理的语句

解决方法

对一些关键性的数据不要自己拼接 交由电脑拼接

即

sql="select * from userinfo where username=%sand password=%s"

 

affect_rows=cursor.execute(sql,(username,password))

删除%(username,password) 以及去除%s的引号

再讲username,password 放入execute中即可

用户管理

创建用户

create user 用户名 identified by '密码';
修改密码
set password for 用户名 = Password('新密码');
set password = Password('新密码'); 对当前登录的用户

重命名

rename user 新用户名 to 旧用户名; 

删除用户

drop user 用户名

查看用户权限

show grants for 用户名

授予访问权限

grant select on 表名.* to 用户名 # 指定一个表给用户权限

撤销权限

revoke select on 表名.* from 用户名

事物

事物的四大特性:ACID

A:原子性 atomicity

一个事物是完整整体, 同时失败或者同时成功,

C:一致性consistency

一致性是指事物必须使数据库从一个一致性状态转换到另一个一致性状态

I:独立性 isolation

事务与事务之间彼此不干扰 相互独立

D:持久性 durability

一旦事务提交 ,对数据库的数据更改是永久性的

sql语句:

start transaction; 开启事务操作

rollback;回滚到上个状态

commit;确认事务

 

 

 

 

 

 使用rollback即可回到上个状态

 

 

 使用commit确认一旦确认了 rollback就没用了

 

 

 视图

关于视图:

视图就是通过查询得到一张虚拟表,然后保存下来可以使用

可以避免频繁使用的表不用反复查询

但是在硬盘中 视图只有表结构文件,没有表数据文件

视图通常用于查询,无法修改视图中的数据

 

 

 触发器

达到某个条件之后自动执行的功能

对数据增删改的情况下 自动触发的功能称为触发器

对 增数据(前 后) 改数据(前 后) 删数据(前 后) 共六种情况

语法结构为:

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    sql语句
end

trigger 为触发器的关键字

触发器名字一般为 tri_before_insert_t1 可以做到见名知意 

由于触发器语法中需要用到;结束符号

所以需要修改结束符号

关键字为

delimiter $$

一旦设置之后 mysql 的结束符就是delimiter

所以在写触发器之前和之后 都要写delimiter $$ 写完之后 写delimiter ;

删除触发器:

drop trigger tri_after_insert_cmd;

存储过程

类似于python中的自定义函数

# 相对于python中的自定义函数
delimiter $$
create procedure p1()
begin
    select * from cmd;
end $$
delimiter ;

# 调用
call p1()

函数

相当于python中的内置方法

可以通过help函数名 查看帮助信息

 

 

 移除指定字符:

Trim、LTrim、RTrim

大小写转换

upper,lower

获取左右起始指定个数字符

Left、Right

返回读音相似值(对英文效果)

Soundex

日期格式:date_format

 

 

 

MySQL流程控制

# if条件语句
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

# while循环语句
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

索引

类似于数据结构,好比书的目录

可以帮助加快查询数据

主键 primary key

唯一键 unique

索引建 index key

索引的影响:

在表格中有大量数据时,创建索引速度会很慢

在索引创建完毕后,对表的查询性能会大幅提升,但是写的性能会降低　

覆盖索引(primary key)

非覆盖索引(unique,index)

#通常情况下遇到查询缓慢时会想到建立索引
但是索引也不是越多越好,以后创建销毁的过程会变复杂,通过优化sql语句提升查询效率