1.python中mysql的使用：
import pymysql #数据库模块的导入 user=input(‘username‘) pwd=input (‘password‘) conn=pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘‘,database=‘db3‘)# 进行数据库连接拿到数据库句柄 cursor =conn.cursor()#进行数据库操作的游标 sql="select * from user where username=‘%s‘ and password =‘%s‘"%(user,pwd)#sql语句 cursor.execute(sql) #执行sql语句 ret=cursor.fetchall() #进行数据读取 cursor.close() #数据库使用完毕关闭游标 conn.close()#关闭数据库句柄
2.但是我们如果使用上述方法的过程中有可能会遇到sql注入的问题就是别人在输入错误的用户名也可进入因为sql中使用————代表注释，只要你的用户名中带有这种字符就可以直接sql注入，
解决方法1.把输入的用户名和密码放入到嗯学execute的执行语句中：
sql="select * from user where username=‘%s‘ and password =‘%s‘"#sql语句 cursor.execute(sql,user,pwd) #执行sql语句
2.把输入的用户名和密码放入到嗯学execute的执行语句中
sql="select * from user where username=‘%s‘ and password =‘%s‘"#sql语句 cursor.execute(sql,[user,pwd]) #执行sql语句
3.也是把输入的用户名和密码放入到嗯学execute的执行语句中
sql="select * from user where username=‘%(u)s‘ and password =‘(p)%s‘"#sql语句 cursor.execute(sql,{‘u‘:user,‘p‘:pwd}) #执行sql语句
4.在pycharm中执行pymysql的语句：在进行增删改之后都需要进行一个conn .commit 数据库里面的内容才会改变：
1.曾操作：
sql=‘insert into teacher (tname) values(%s)‘ cursor.execute(sql,(‘alex‘)) conn.commit()
2.删操作：
sql="delete from teacher where tname =‘alex‘" cursor.execute(sql) conn.commit()
3.该操作：
sql="update teacher set tname=‘alex‘ where tname=‘马贵‘" cursor.execute(sql) conn.commit()
4.一次性插入多个数据：
sql="insert into course (cname,teacher_id) values(%s,%s)" cursor.executemany(sql,[(‘1ii‘,1),(‘fjk‘,2),(‘fji‘,3)]) conn.commit()
5.进行数据地读取：
1.每一次读取一个数据：在读取数据时如果有中文需要在数据库连接时设定编码方式：
import pymysql #数据库模块的导入 conn=pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘‘,database=‘db3‘,charset=‘utf8‘)# 进行数据库连接拿到数据库句柄 cursor =conn.cursor()#进行数据库操作的游标 sql="select * from teacher" cursor.execute(sql) cursor.fetchone() ret=cursor.fetchone() #进行数据读取每次读取一行 print(ret) ret=cursor.fetchone() #进行数据读取 print(ret) ret=cursor.fetchone() #进行数据读取 print(ret) cursor.close() #数据库使用完毕关闭游标 conn.close()#关闭数据库句柄
2.每次进行数据读取每次读取设定地行数：
cursor.execute(sql) ret=cursor.fetchmany(4) #进行数据读取每次读取4行 print(ret) cursor.close() 结果为 C:\Users\OYMK\untitled\Scripts\python.exe "D:/python练习程序/day 64/01 s1.py" ((1, ‘李泽华‘), (2, ‘余华‘), (3, ‘蒋国‘), (4, ‘alex‘))
3.读取此表中所有地数据：
cursor.execute(sql) ret=cursor.fetchall() #进行数据读取每次读取4行 print(ret) 结果为 C:\Users\OYMK\untitled\Scripts\python.exe "D:/python练习程序/day 64/01 s1.py" ((1, ‘李泽华‘), (2, ‘余华‘), (3, ‘蒋国‘), (4, ‘alex‘))
4.从结果中我们可以发现我们是可以进行所有数据地读取但是我们对于读取到地数据变量含义有时候看起来会很模糊可以使用·以下方法获得数据表地变量名和变量地值：在设置游标地时候进行设定：
cursor =conn.cursor(cursor=pymysql.cursors.DictCursor)#进行数据库操作的游标 sql="select * from teacher" cursor.execute(sql) ret=cursor.fetchall() #进行数据读取每次读取4行 print(ret) 结果为 [{‘tid‘: 1, ‘tname‘: ‘李泽华‘}, {‘tid‘: 2, ‘tname‘: ‘余华‘}, {‘tid‘: 3, ‘tname‘: ‘蒋国‘}, {‘tid‘: 4, ‘tname‘: ‘alex‘}]
6.查询新插入元素地索引及id号：lastrowid
sql="insert into teacher (tname) values(‘lajin‘)" cursor.execute(sql) conn.commit() print(cursor.lastrowid)
7.数据表的上下连接（条件是两个表的行数要相同：）
1.去重地使用union
SELECT sid ,sname from student UNION SELECT * from teacher
2.不去重使用union all
SELECT sid ,sname from student UNION all SELECT sid ,sname from student
8.视图：有时候我们需要多次使用同一个临时表，但是使用同一个指令多行会使地sql地执行效率降低，我们这个时候可以使用视图进行解决：
1.创建视图：
create view t1 as select sid,sname from student where sid>4;
2.查看视图：
SELECT * from t1 WHERE sid>5;
3.删除视图：
drop view t1
4.视图是从原有地图里虚拟出来地原来的数据修改了，视图地数据也会被修改：
5.进行视图的修改：这个是修改的sql语句并不能对数据内容进行修改：
ALTER view t1 as SELECT * from student where sid>5;
9.触发器：
1.触发器发生时间无非两种一个是BEFORE (之前）AFTER(之后）语句实例 ： create trigger 变量名 BEFORE 数据表的操作方法 ON
数据表 FOR EACH ROW BEGIN （操作语句） end
2.再写这个之前我们先要看一个指令：改变结束语句的指令delimiter
mysql> delimiter // mysql> show databases; -> // +--------------------+ | Database | +--------------------+ | db1 | | db2 | | db3 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql>
3.在对teacher进行操作的时候，可以使用触发器自定义关联course行为：
-- delimiter // -- create TRIGGER teacher_student BEFORE INSERT on teacher for each row -- BEGIN -- INSERT into course (cname,teacher_id) VALUES(‘好久哦‘,1); -- end // -- delimiter ; 创建好之后就可以进行删除或者注释掉 -- insert into teacher (tname ) VALUES (‘五覅和‘);
4.如果插入多个数据是否course能够关联多个数据：
-- insert into teacher (tname ) VALUES (‘五覅ji‘),(‘金发‘),(‘fjij‘);
5.如果想要course想要关联的数据是我们插入的数据中的某一项我们可以使用new ，new可以获取新插入数据的所有内容：
-- delimiter // -- CREATE TRIGGER teacher_student BEFORE INSERT on teacher for each row -- BEGIN -- INSERT into course (cname,teacher_id) VALUES(NEW.tname,1); -- end // -- delimiter ; 创建好之后就可以进行删除或者注释掉 insert into teacher (tname ) VALUES (‘五覅ji‘),(‘金发‘),(‘fjij‘);
6.除了有new之外，数据库中还有old用法：old只要用于数据更新时会有旧的数据，drop时会有旧的数据：
-- delimiter // -- CREATE TRIGGER teacher_student BEFORE DELETE ON teacher for each row -- BEGIN -- INSERT into course (cname,teacher_id) VALUES(old.tname,1); -- end // -- delimiter ; 创建好之后就可以进行删除或者注释掉 -- delete from teacher where tname=‘fjij‘ -- DROP TRIGGER teacher_student;
10函数：和其他语言一样sql也分为内置函数和自己编写的函数：
1.内置函数：
1.查看字符串的长度：
SELECT CHAR_LENGTH(‘fjdskljlkj‘)
2.显示当前的时间：
SELECT CURRENT_TIMESTAMP()
3.时间格式化操作：
SELECT DATE_FORMAT(CURRENT_TIMESTAMP,‘%Y,%m‘)
2.自定义函数：在创建函数时如果出现函数无法进行创建就要使用一句；Set global log_bin_trust_function_creators=TRUE;
-- Set global log_bin_trust_function_creators=TRUE; -- delimiter // -- create FUNCTION r1( -- i1 int , -- i2 int ) -- RETURNS int -- BEGIN -- declare num int DEFAULT 0; -- set num =i1+i2; -- RETURN (num); -- END // -- delimiter; SELECT r1(1,4);
11.存储过程：
1.创建存储过程的第一函数：(没有变量传输)
-- delimiter // -- CREATE PROCEDURE p1() -- BEGIN -- SELECT * from student WHERE sid>4; -- insert into teacher (tname) values (‘alex‘); -- end // -- delimiter ; -- call p1()
如果实在pycharm上使用的语句为：
cursor.callproc(‘p1‘) ret=cursor.fetchall() print(ret) print(cursor.lastrowid)
2.创建带参数的第二个函数：
-- delimiter // -- CREATE PROCEDURE p2( -- in i1 int , -- in i2 int) -- BEGIN -- SELECT * from student WHERE sid> i1; -- insert into teacher (tname) values (‘alex‘); -- end // -- delimiter ; -- call p2(3,4)
如果要在pycharm上运行：
cursor.execute(sql) cursor.callproc(‘p2‘,(2,4)) ret=cursor.fetchall() print(ret) print(cursor.lastrowid)
3.存储过程中参数总共有三种：in 、out、 inout ： 在使用out时要使用@变量名：
-- delimiter // -- CREATE PROCEDURE p3( -- in i1 int , -- out i2 int) -- BEGIN -- set i2=1234; -- SELECT * from student WHERE sid> i1; -- insert into teacher (tname) values (‘alex‘); -- end // -- delimiter ; -- set @v1=1; call p3(3,@v1); SELECT @v1;
-- delimiter // -- CREATE PROCEDURE p4( -- in i1 int , -- inout i2 int) -- BEGIN -- set i2=i2+3; -- SELECT * from student WHERE sid> i1; -- insert into teacher (tname) values (‘alex‘); -- end // -- delimiter ; -- set @v1=1; call p4(3,@v1); SELECT @v1;
使用pycharm的语法：
cursor.callproc(‘p3‘,(2,4)) ret=cursor.fetchall() print(ret) cursor.execute(‘select @_p3_0,@_p3_1‘) ret=cursor.fetchone() print(ret)
cursor.callproc(‘p4‘,(2,4)) ret=cursor.fetchall() print(ret) cursor.execute(‘select @_p4_0,@_p4_1‘) ret=cursor.fetchone() 结果为 [{‘sid‘: 3, ‘sname‘: ‘小明‘, ‘gener‘: ‘女‘, ‘class_id‘: 1}, {‘sid‘: 4, ‘sname‘: ‘李红‘, ‘gener‘: ‘男‘, ‘class_id‘: 2}, {‘sid‘: 5, ‘sname‘: ‘红花‘, ‘gener‘: ‘男‘, ‘class_id‘: 3}, {‘sid‘: 6, ‘sname‘: ‘张明‘, ‘gener‘: ‘女‘, ‘class_id‘: 2}, {‘sid‘: 7, ‘sname‘: ‘小蜜‘, ‘gener‘: ‘男‘, ‘class_id‘: 2}, {‘sid‘: 8, ‘sname‘: ‘李红‘, ‘gener‘: ‘男‘, ‘class_id‘: 3}, {‘sid‘: 9, ‘sname‘: ‘小蜜‘, ‘gener‘: ‘女‘, ‘class_id‘: 2}, {‘sid‘: 10, ‘sname‘: ‘洪发‘, ‘gener‘: ‘男‘, ‘class_id‘: 3}] {‘@_p4_0‘: 2, ‘@_p4_1‘: 7}
14储存过程中的事件：
1. 首先创建一个储存事件（create procedure p4 ，然后定义一个外部变量（out status tinyint ) begin
1.声明出现异常的时候： set status =1; rollback (回滚）
2.开始事物： 所有的操作sql语句。commit
3结束：
delimiter // CREATE PROCEDURE p4 ( out status_1 TINYINT) BEGIN DECLARE exit HANDLER for SQLEXCEPTION BEGIN set status_1=1; 发生错误的时候 ROLLBACK; end; START TRANSACTION; DECLARE from class insert into teacher (tname) values(‘jin‘); COMMIT; set status_1=2;程序正确的时候 end// delimiter ;
2.