数据库（SQL注入问题、视图、触发器、事务、存储过程、内置函数、流程控制、索引）

import pymysql
# 链接MySQL服务端
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'db6',
    charset = 'utf8',
    autocommit = True # 针对 增 改 删自动二次确认
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:').strip()
password = input('>>>:').strip()
sql = "select * from userinfo where username='%s' and password='%s'" % (username, password)
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
res = cursor.execute(sql) # 自动识别sql里面的%s用后面元组里面的数据替换
if res:
    print("登录成功")
    print(cursor.fetchall())
else:
    print("用户名或密码错误")

import pymysql
# 链接MySQL服务端
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'db6',
    charset = 'utf8',
    autocommit = True # 针对 增 改 删自动二次确认
)

# 产生一个游标对象,字典形式
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

username = input('username:').strip()
password = input('password:').strip()

# 编写SQL语句
# 因为拼接问题，产生注入问题
# sql = "select * from userinfo where username='%s' and password='%s'" % (username,password)

# 解决注入问题，execute内部会自动对username，password进行拼接，不需要手动拼接
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,(username,password))
# 取出表中的数据
data = cursor.fetchall()

if data:
    print(data)
    print('登录成功')
else:
    print('用户名或密码错误')

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db6',
    charset='utf8',
    autocommit=True#  默认是autocommit=False 自动提交,后面执行SQL语句时不需要再commit

)

cursor = conn.cursor(pymysql.cursors.DictCursor)
# 向user表里增加数据
sql = "insert into userinfo(username,password) values (%s,%s)"
rows = cursor.execute(sql,('kkk',123))
print(rows)

# # 删
sql = "delete from userinfo where id=2"
rows = cursor.execute(sql)
print(rows)

# 改
sql = "update userinfo set username='jackjack' where id=3"
rows = cursor.execute(sql)
print(rows)

# 查
sql = "select username from userinfo where id=3"
cursor.execute(sql)
print(cursor.fetchall())  # [{'username': 'jackjack'}]

  # 向user表里增加数据
sql = "insert into user(name,password) values (%s,%s)"rows = cursor.execute(sql,('kkk',123))print(rows)

# 可以改成 # 向user表里增加数据

sql = "insert into user(name,password) values (%s,%s)"# rows = cursor.execute(sql,('kkk',123))rows = cursor.executemany(sql,[('qjwnb',123),('qjwhaonb',123)])print(rows)

create view teacher2course asselect * from teacher inner join course on teacher.tid = course.teacher_id;

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

create trigger tri_before_insert_t1  
before insert on t1
for each row
begin
    sql语句
end
create trigger tri_after_insert_t1
after insert on t1
for each row
begin
    sql语句
end
"""针对删除和修改 书写格式一致"""

delimiter $$     将默认的结束符号由;改为$$
delimiter ;

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
);

delimiter $$
create trigger tri_after_insert_cmd 
after insert on cmd 
for each row
begin
    if NEW.success = 'no' then
        insert into 
errlog(err_cmd,err_time) 
values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),            
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

drop trigger tri_after_insert_cmd;

start transaction;

rollback;

commit；

create table user(
    id int primary key auto_increment, 
    name char(16), 
    balance int
);

insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction；

# 修改操作
update user set balance=900 where name='jason'; # 买支付100元
update user set balance=1010 where name='egon'; # 中介拿走10元
update user set balance=1090 where name='tank'; # 卖家拿到90元

# 回滚到上一个状态
rollback；

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit；
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

create procedure 存储过程的名字(形参1,形参2,...)
begin
    sql代码
end
# 调用
call 存储过程的名字();

"""
    应用程序：程序员写代码开发
    MySQL：提前编写好存储过程，供应用程序员调用
    
    好处：开发效率提升了，执行效率也提升了
    缺点：考虑到人为因素、跨部门沟通的问题后续的存储过程的扩展性差
"""

"""
    应用程序：程序员写代码开发之外，涉及到数据库操作也自己动手写
    优点：扩展性很高
    缺点：开发效率降低
    编写sql语句太过繁琐，而且后续还需要考虑到sql优化的问题

"""

"""
    应用程序：只写程序代码，不写sql语句，基于别人写好的操作MySQL的python框架（ORM框架）直接调用操作
    优点：开发效率比上面两种情况都要高 
    缺点：语句的扩展性差，可能会出现执行效率低下的问题
"""

对于存储过程，可以接收参数，其参数有三类：

#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值

delimiter $$ # 把结束符号；改为$$
create procedure p1(
    in m int,  # 只进不出  m不能返回出去
    in n int,  # 只进不出 n不能返回出去
    out res int  # 该形参可以返回出去
)
begin
    select tname from teacher where tid>m and tid<n;
    set res=666;  # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ; # 把结束符号$$改为；

# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret; 

# 1. 直接在mysql中调用
set @res=10   # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,@res);

# 查看结果
select @res；  # 执行成功。@res变量值发生了改变

# 2. 在python程序中调用
pymysq连接mysql
# 产生的游表
cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程 传参
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
print(cursor.fetchall())
# cursor.execute('select @_p1_2;')
print(cursor.fetchall())