MySQL数据库05

python操作MySQL

python中支持操作MySQL的模块很多，其中最常见的当属'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='123',  # 密码  也可以简写 passwd
      database='db1',  # 库名称  也可以简写 db
      charset='utf8mb4'  # 字符编码 千万不要加杠utf-8
      autocommit=True    # 执行增删改操作的时候，自动执行conn_obj.commit()
  )  # 要善于查看源码获取信息
  # 2.产生获取命令的游标对象
  cursor = conn_obj.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内不写参数 数据是元组,不够精确,添加参数则会将数据处理成字典
  # 3.编写SQL语句
  # sql1 = 'show tables;'
  sql1 = 'select * from teacher;'  # SQL语句会被高亮显示 不用惊慌
  # sql1 = 'select * from score;'  # SQL语句会被高亮显示 不用惊慌
  # 4.执行SQL语句
  affect_rows = cursor.execute(sql1)   # 发送给服务端
  print(affect_rows)  # 该方法的返回值res就是执行SQL语句之后表受影响的行数
  # conn_obj.commit()    # 修改数据库的操作需要二次确认

  # 5.获取命令的执行结果
  res = cursor.fetchall()    # 获取所有的数据，结果是列表套字典
  print(res)
复制代码
复制代码
'''
过程:

1. 建立连接
2. 设置一个可以执行sql语句的对象 - 游标
3. 通过该对象执行sql语句得到执行结果
4. 端口连接

'''
获取结果的注意事项：
import pymysql
# 1. 建立连接
conn = pymysql.connect(host="localhost", port=3306, db='db2', user='root', password='root')
# 2. 设置游标
cursor = conn.cursor(pymysql.cursors.DictCursor)  # 设置游标的具体类型, DictCursor拿到字段名
# 3. 执行sql语句
sql = 'select * from emp'

res = cursor.execute(sql)  # 结果的行数
print(res)

# 需求: 具体的一条条记录
tag = cursor.fetchone()
print(tag)
print(tag['salary'])
tag = cursor.fetchone()    # 获取结果集当中的一条数据
print(tag)

# cursor.scroll(1, mode='relative') # 指针相对于上一次位置往后偏移1条记录，基于数据集的当前位置
cursor.scroll(1, mode='absolute') # 指针绝对, 游标永远从头开始偏移，基于数据集开头的位置

tags = cursor.fetchall()    # 获取结果集当中的所有的数据
print(tags)

tags1 = cursor.fetchall(100)   # 获取结果集当中的指定条的数据，如果超出数据的最大条数，就显示所有条数据
print(tages1)

cursor.close()
conn.close()
复制代码

pymysql处理sql注入问题

什么是sql注入：

通过书写sql包含（注释相关的）特殊符号，让原有的sql执行顺序发生改变，从而改变执行得到的sql。

本质：利用一些特殊符号的组合产生了特殊的含义，从而逃脱了正常的业务逻辑。

措施：针对用户输入的数据，不要自己处理，交给专门的方法自动过滤

没有处理sql注入的写法：
 sql = 'select * from user where usr="%s" and pwd="%s"' % (usr, pwd)
res = cursor.execute(sql)
复制代码
# sql注入
# 1.知道用户名:  abc" -- hehe | ooo
# select * from user where usr="abc" -- hehe" and pwd="ooo"
# 2.不知道用户名 aaa" or 1=1 -- hehe | 000
# select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000"

# 处理sql注入：
sql = 'select * from user where usr=%s and pwd=%s'
res = cursor.execute(sql, (usr, pwd))
复制代码

解决SQL注入的问题其实也很简单>>>:就是想办法过滤掉特殊符号！！！

execute方法自带校验SQL注入问题，能够自动处理特殊符号。

sql = "select * from userinfo where name=%s and password=%s;"
    cursor.execute(sql, (name, password))
复制代码
'''
sql语法中
注释: /**/  | --  | #

什么是sql注入:
通过书写sql包含(注释相关的)特殊字符, 让原有的sql执行顺序发生改变, 从而改变执行得到的sql

目的:
绕过原有的sql安全认证, 达到对数据库攻击的目的
'''
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2')
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 登录

# 得到用户输入的账户密码
usr = input("请输入用户名: ") #abc
pwd = input("请输入密码: ") #123

# sql注入
# 1.知道用户名:  abc" -- hehe | ooo
# select * from user where usr="abc" -- hehe" and pwd="ooo"
# 2.不知道用户名 aaa" or 1=1 -- hehe | 000
# select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000"

# 处理方式
# 对输入的账户密码做完全处理 => 不可能形成达到sql注入的特殊语法 => 正则

# 和数据库的账户密码进行配对
# sql = 'select * from user where usr="%s" and pwd="%s"' % (usr, pwd)
# select * from user where usr="abc" and pwd="123"
# res = cursor.execute(sql)

# pymysql已经处理了sql注入
sql = 'select * from user where usr=%s and pwd=%s'
res = cursor.execute(sql, (usr, pwd))

# print(res)
if res:
    print('login success')
else:
    print('login failed')

cursor.close()
conn.close()
复制代码

pymysql二次确认>>>:增、删、改

pymysql针对增、改、删三个操作，都设置了二次确认，如果不确认，则不会真正影响数据库。

方式1：代码直接编写

# 手动二次确认
affect_row = cursor.execute(sql)
conn_obj.commit()

方式2：配置固定参数

# 自动二次确认
conn_obj = pymysql.connect(
autocommit=True  
)
复制代码
增：
增加sql的语句：
sql1 = 'insert into user(usr, pwd) values (%s, %s)'

在内存中一次插入一条：
cursor.execute(sql1, ("opq", "123"))

在内存中插入多条
cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])

将内存中的数据提交到硬盘中：
conn.commit()


删：
sql2 = 'delete from user where usr=%s'
cursor.execute(sql2, ("aaa"))
conn.commit()


改：
sql3 = 'update user set pwd=%s where usr=%s'
res = cursor.execute(sql3, ("222", "bbb"))
conn.commit()
复制代码
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2')
cursor = conn.cursor(pymysql.cursors.DictCursor)
 
# 增
# sql1 = 'insert into user(usr, pwd) values (%s, %s)'
# cursor执行sql语句,在内存中完成了对数据的插入, 但不能将数据存放到硬盘
# 会将id完成自增
# 插入单条
# res = cursor.execute(sql1, ("opq", "123"))
# 插入多条
#res = cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])
# print(res)
# 将内存中的数据提交给硬盘, 完成真实意义上的数据存储
# conn.commit()
 
# 删
# sql2 = 'delete from user where usr=%s'
# res = cursor.execute(sql2, ("aaa"))
# print(res)
# conn.commit()
 
# 改
sql3 = 'update user set pwd=%s where usr=%s'
res = cursor.execute(sql3, ("222", "bbb"))
conn.commit()
 
cursor.close()
conn.close()

　　

视图

  • 什么是视图：

视图是由一张表或多张表的查询结果构成的一张虚拟表。

通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为'视图'。

  • 为什么使用视图（视图的作用）：

将复杂常用的查询结果保留下来重复使用（将一张大表拆分成多张小表）。

如果需要频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度。

  • 视图相关语法：

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

复制代码
create [or replace] view 视图名[(查询字段别名)] as 查询语句
create view new_emp as (select * from emp)

注：
1.查询字段别名们 要与 查询语句的查询字段对应
2.create or replace :操作视图，没有则创建，有则替换
create or replace view new_emp(id,姓名,工资) as (select id ,name,salary from emp where dep_id = 2);

视图的修改：alter 等价于 create or replace,且语法一致
alter view new_emp(id,姓名,工资) as (select id,name,salary from emp where dep_id = 1);


视图中字段的操作：不允许alter操作字段(下列操作是不被允许的)
alter table new_emp reneme new_emp1;
alter view new_emp modify id tinyint;


视图中记录的操作：等价于普通表，完成增删改查
update new_emp set 姓名='san' where id = 3;
delete fro new_emp where id = 3;
insert into new_emp(id, 姓名, 工资) values (10, "Bob", 10000);    #操作的是实体表，虚拟表要重新创建才能拿到最新数据

视图的删除：
drop view 视图名；

总结：虚拟表的作用——查询
复制代码

ps：

视图只能用于数据的查询，不能做增、删、改的操作；视图里面的数据是直接来源于原始表，而不是拷贝一份！！！

触发器

  • 什么是触发器：

在对表数据进行增、删、改的具体操作下，自动触发的功能。即：当表数据发生更新时，会自动触发的功能称之为触发器。

  • 为什么使用触发器（触发器的作用）：

当一个表在数据发生更新时，需要去完成一些操作，可以为具体数据更新的方式添加触发器。即：专门针对表数据的操作，定制个性化配套功能。

  • 触发器种类：
  1. 表数据新增之前、新增之后
  2. 表数据修改之前、修改之后
  3. 表数据删除之前、删除之后
复制代码
语法：
delimiter //
create trigger 触发器名 before | after insert | update | delete on 表名 for each row
begin
需要触发执行的sql代码
end //
delimiter;


#触发器名：表名_(before|after)_(insert|update|delete)_trigger

注：
delimiter 是用来修改sql的语句结束标识符

删除触发器：drop trigger 触发器名；
查看当前库下所有的触发器信息：show triggers\G;
复制代码

eg：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
eg：
  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');

　　

事务

  • 事务是什么：

事务是逻辑上的一组操作，要么都成功，要么都失败。

事务可以包含诸多SQL语句，并且这些SQL语句要么同时执行成功，要么同时执行失败，这是事务的原子性特点。

  • 为什么使用事务：

很多时候应该数据操作，不是一个sql语句就完成的，可能有很多个sql语句，如果部分sql执行成功而部分sql执行失败将导致数据错乱。

（例如：转账=>转入转出均成功，才能认为操作成功）

  • 事务的四大特性：（ACID）

A：原子性>>>:事务是一组不可分割的单位，里面的操作要么都成立要么都不成立。

C：一致性>>>:事务必须使数据库从一个一致性状态变到另外一个一致性状态。即：事务前后的数据完整性应该保持一致。

I：隔离性>>>:事务的隔离性是指多个用户并发访问数据时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。

D：持久性>>>:持久性是指一个事务一旦被提交，它对数据的改变就是永久性的，不可逆的，接下来几十数据库发生故障也不应该对其有影响。

  • 事务操作

开启一个事务的操作：

start transaction ；——开启事务，在这条语句之后的sql将处在同一事务，并不会立即修改数据库。

事务回滚：

返回执行事务操作之前的数据库状态。
rollback ;——回滚事务，取消这个事务，这个事务不会对数据库中的数据产生任何影响。执行完回滚之后，事务自动结束。

事务确认：

commit ;——提交事务，让这个事务中的sql立即执行事务的操作。（执行完事务的主动操作之后，确认无误之后，需要执行确认命令）。

  • 事务的用户隔离级别

数据库使用者可以控制数据库工作在哪个级别下，就可防止不同的隔离性问题。

复制代码
'''
read uncommitted ——不做任何隔离，可能脏读，幻读

read committed ——可以防止脏读，不能防止不可重复读合幻读

Repeatable read ——可以防止脏读，不可重复读，不能防止幻读

Serializable——数据库运行在串行化实现，所有问题都没有，就是性能低
'''
复制代码
  • 修改隔离级别
'''
select @@tx_isolation; ——查询当前级别

set[session|global] transaction isolation level ....;修改级别
'''

eg：set global transaction isolation level Repeatable read;

ps：修改后重新连接服务器生效。

存储过程

  • 存储过程是什么：

用于完成指定功能的sql语句块，类似于Python中的自定义函数。

  • 存储过程的优点：

将能指定功能的sql语句块建立成存储过程，不仅将sql语句逻辑化了，更是功能化了，那我们要完成相同的事，只需要重复使用建立的存储过程，不需要再重复书写sql语句了。

即>>>:存储过程可以让sql语句更具有复用性，从而提高开发效率。

复制代码
'''
语法：
delimiter $$
create procedure 存储过程名(
    输入输出类型1 参数名1 参数类型1(宽度), 
    ... ,
    输入输出类型n 参数名n 参数类型n(宽度)
)
begin
sql语句块
end $$
delimiter ;

注：1.输入输出类型：in | out | inout
   2.call存储过程名（实参们）来调用存储过程
     调用函数：call p1()
'''
复制代码

查看存储过程具体信息：show create procedure pro1;

查看所有存储过程：show procedure status;

删除存储过程：drop procedure pro1;

MySQL的内置函数

不同于存储过程，mysql内置的函数只能在sql语句中使用！！！

  1. 移除指定字符：Trim、LTrim、RTrim
  2. 大小写转换：Lower、Upper
  3. 获取左右起始指定个数字符：Left、Right
  4. 返回读音相似值(对英文效果)：Soundex
  5. 日期格式：date_format
复制代码
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','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');
复制代码

ps：其他的日期处理相关函数

  • adddate 增加一个日期
  • addtime 增加一个时间
  • datediff 计算两个日期差值

流程控制

  • if语句的使用
复制代码
第一种 if:
"""
if 条件 then
子语句;
end if;
"""
第二种 if elseif
"""
if 条件  then
子语句1;
elseif 条件 then
子语句2;
else 语句3;
end if;
"""
复制代码
  • while循环
复制代码
循环输出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while  i < 10 do
select "hello mysql";
set i  = i + 1;
end while;
end
复制代码
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 索引键
上面三种key前两种除了有加速查询的效果之外，还有额外的约束条件(primary key:非空且唯一，unique key:唯一)；而index key没有任何约束功能只会帮你加速查询。

索引的基本用法：

id name pwd post_comment  addr  age

 

posted @   *sunflower*  阅读(85)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
相关博文：
· 数据库简介与mysql基础
· MySQL数据库02
· Mysql Day05
· Python操作mysql
· MySQL杂论
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛