- pymysql
- # 增删改操作
- import pymysql
- client=pymysql.connect(
- host='127.0.0.1',
- port=3306,
- user='root',
- password='egon123',
- database='db2',
- charset='utf8'
- )
- cursor=client.cursor()
- # insert 的三种方法:
- # 方法一
- sql='insert into t1 values(1,"egon"),(2,"lxx");' # 原生 sql 语句
- try: # 下面省略异常捕捉
- res=cursor.execute(sql)
- print(res) # 返回的是执行表操作条数
- client.commit() # 递交后才会改变数据
- except Exception:
- client.rollback() # 如果字段有误就能恢复修改前数据
- # 方法二
- userinfo=[
- (3,"alex"),
- (4,"lxx"),
- (5,"yxx")
- ]
- for user in userinfo:
- sql = 'insert into t1 values(%s,"%s");' %(user[0],user[1])
- cursor.execute(sql)
- # 方法三
- sql = 'insert into t1 values(%s,%s);'
- cursor.executemany(sql,userinfo)
- # 删除方法
- cursor.execute('delete from t1 where id=3;')
- client.commit()
- cursor.close()
- client.close()
- # 查询操作
- Import pymysql
- client=pymysql.connect(
- host='127.0.0.1',
- port=3306,
- user='root',
- password='egon123',
- database='db6',
- charset='utf8'
- )
- cursor =client.cursor()
- inp_user=input('输入账号名:').strip()
- inp_pwd=input('输入密码:').strip()
- sql='select id from user where name ="%s"and pwd = password("%s");' %(inp_user,inp_pwd)
- rows = cursor.execute(sql)
- # 这个方法可以防止注入问题
- # sql = 'select id from t1 where name =%s and password = %s;' # 注意: 账号密码不用引号了
- # rows = cursor.execute(sql,(inp_user,inp_pwd)) # 这句防注入问题
- if rows:
- print('登录成功')
- else:
- print('用户名或密码错误')
- cursor.close()
- client.close()
- # 不做任何处理会产生注入问题, 比如输入账号时输入 alex "-- sadafas
或 sadgasd " or 1=1 - sfaf 类似的可以直接登录
视图
强调: 1. 字段名不能重复
2. 视图是为了简化查询的 sql 语句, 不应该修改视图中的记录
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on
emp.dep_id = dep.id;
触发器
准备表
- 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_id int
- );
- delimiter $$ # 改变结束条件
- CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW # 创建触发器
- BEGIN
- if NEW.success = 'no' then # if
- insert into errlog(err_id) values(NEW.id);
- end if;
- END $$
- delimiter ; # 必须改回结束条件
- INSERT INTO cmd (
- USER,
- priv,
- cmd,
- sub_time,
- success
- )
- VALUES
- ('egon','0755','ls -l /etc',NOW(),'yes'),
- ('egon','0755','cat /etc/passwd',NOW(),'no'),
- ('egon','0755','useradd xxx',NOW(),'no'),
- ('egon','0755','ps aux',NOW(),'yes');
- # 查询错误日志, 发现有两条
- # 触发器可用于插入删除更新的前后
删除触发器: drop trigger tri_after_insert_cmd;
transaction: 事务, 交易
- # 1, 原子性: 事务可以包含一系列的 sql 语句, 事务的执行具有原子性
- #2, 回滚: 包含多条 sql 语句要么都执行成功, 要么都执行不成功
- create table user(
- id int primary key auto_increment,
- name char(32),
- balance int
- );
- insert into user(name,balance)
- values
- ('wsb',1000),
- ('egon',1000),
- ('ysb',1000);
- # 出现异常, 回滚到初始状态
- start transaction;
- update user set balance=900 where name='wsb'; #买支付 100 元
- update user set balance=1010 where name='egon'; #中介拿走 10 元
- uppdate user set balance=1090 where name='ysb'; #卖家拿到 90 元, 出现异常没有拿到
- rollback;
- commit;
存储过程
方案一:
应用程序:
mysql: 编写存储过程
方案二:
应用程序: 原生 sql
mysql:
方案三:
应用程序: ORM(类 / 对象 ---> 原生 sql)
mysql:
执行效率:
方案一 > 方案二 -> 方案三
开发效率:
方案一 > 方案三 -> 方案二
由于方案一所需的人员成本较高, 我们一般使用方案三
- # 无参
- create table s1(
- id int,
- name varchar(20),
- gender char(6),
- email varchar(50)
- )
- delimiter $$
- create procedure p2()
- BEGIN
- declare n int default 1; #设置整型 n 的初始值为 1
- while (n <100) do
- insert into s1 values(n,concat('egon',n),'male',concat('egon',n,'@163.com'));
- set n = n+1 # 注意不能用 n+=1
- end while;
- END $$
- delimiter;
- # 有参
- delimiter $$
- create procedure p3(
- in n int, # 传入的值
- out res int # 传出的值
- )
- BEGIN
- select * from blog where id>n;
- set res = 0;
- END $$
- delimiter;
- # 直接在 mysql 中调用:
- mysql> set @x=111;
- mysql> call p3(3,@x);
- mysql> select @x;
可以查到 @x 的值变为了 0
- # 在 python 中调用:
- cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111
- print(cursor.fetchall())
- cursor.execute('select @_p4_1;')
- print(cursor.fetchone())
来源: https://www.cnblogs.com/luck-L/p/9365014.html