您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- mysql- mysql第四天使用 视图、触发器、存储、函数：

mysql第四天使用 视图、触发器、存储、函数：

bubuko 2022/1/25 19:56:33 mysql 字数 51572 阅读 675 来源 http://www.bubuko.com/infolist-5-1.html

1.python中mysql的使用： import pymysql #数据库模块的导入 user=input('username') pwd=input ('password') conn=pymysql.connect(host='localhost',user='root',password=' ...

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()#关闭数据库句柄
View Code

2.但是我们如果使用上述方法的过程中有可能会遇到sql注入的问题就是别人在输入错误的用户名也可进入因为sql中使用————代表注释，只要你的用户名中带有这种字符就可以直接sql注入，

解决方法1.把输入的用户名和密码放入到嗯学execute的执行语句中：

技术分享图片 
sql="select * from user where username=‘%s‘ and password =‘%s‘"#sql语句
cursor.execute(sql,user,pwd) #执行sql语句
View Code

　　2.把输入的用户名和密码放入到嗯学execute的执行语句中

技术分享图片 
sql="select * from user where username=‘%s‘ and password =‘%s‘"#sql语句
cursor.execute(sql,[user,pwd]) #执行sql语句
View Code

　　3.也是把输入的用户名和密码放入到嗯学execute的执行语句中

技术分享图片 
sql="select * from user where username=‘%(u)s‘ and password =‘(p)%s‘"#sql语句
cursor.execute(sql,{‘u‘:user,‘p‘:pwd}) #执行sql语句
View Code

4.在pycharm中执行pymysql的语句：在进行增删改之后都需要进行一个conn .commit 数据库里面的内容才会改变：

　　1.曾操作：

技术分享图片 
sql=‘insert into teacher (tname) values(%s)‘
cursor.execute(sql,(‘alex‘))
conn.commit()
View Code

　　2.删操作：

技术分享图片 
sql="delete from teacher where tname =‘alex‘"
cursor.execute(sql)
conn.commit()
View Code

　　3.该操作：

技术分享图片 
sql="update teacher set tname=‘alex‘ where tname=‘马贵‘"
cursor.execute(sql)
conn.commit()
View Code

　　4.一次性插入多个数据：

技术分享图片 
sql="insert into course (cname,teacher_id) values(%s,%s)"
cursor.executemany(sql,[(‘1ii‘,1),(‘fjk‘,2),(‘fji‘,3)])
conn.commit()
View Code

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()#关闭数据库句柄
View Code

　　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‘))
View Code

　　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‘))
View Code

　　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‘}]
View Code

6.查询新插入元素地索引及id号：lastrowid

技术分享图片 
sql="insert into teacher (tname) values(‘lajin‘)"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)
View Code

7.数据表的上下连接（条件是两个表的行数要相同：）

　　1.去重地使用union

技术分享图片 
SELECT sid ,sname from student
UNION
SELECT * from teacher
View Code

　　2.不去重使用union all

技术分享图片 
SELECT sid ,sname from student
UNION all 
SELECT sid ,sname from student
View Code

8.视图：有时候我们需要多次使用同一个临时表，但是使用同一个指令多行会使地sql地执行效率降低，我们这个时候可以使用视图进行解决：

　　1.创建视图：

技术分享图片 
create view t1 as
select sid,sname from student where sid>4;
View Code

　　2.查看视图：

技术分享图片 
SELECT * from t1 WHERE sid>5;
View Code

　　3.删除视图：

技术分享图片 
drop view t1
View Code

　　4.视图是从原有地图里虚拟出来地原来的数据修改了，视图地数据也会被修改：

　　5.进行视图的修改：这个是修改的sql语句并不能对数据内容进行修改：

技术分享图片 
ALTER view t1 as SELECT * from student where sid>5;
View Code

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>
View Code

　　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 (‘五覅和‘);
View Code

　　4.如果插入多个数据是否course能够关联多个数据：

技术分享图片 
-- 
insert into teacher (tname ) VALUES (‘五覅ji‘),(‘金发‘),(‘fjij‘);
View Code

　　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‘);
View Code

　　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;
View Code

10函数：和其他语言一样sql也分为内置函数和自己编写的函数：

　　1.内置函数：

　　　　　1.查看字符串的长度：

技术分享图片 
SELECT CHAR_LENGTH(‘fjdskljlkj‘)
View Code

　　　2.显示当前的时间：

技术分享图片 
SELECT CURRENT_TIMESTAMP()
View Code

　　3.时间格式化操作：

技术分享图片 
SELECT DATE_FORMAT(CURRENT_TIMESTAMP,‘%Y,%m‘)
View Code

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

11.存储过程：

　　1.创建存储过程的第一函数：(没有变量传输)

技术分享图片 
-- delimiter //
-- CREATE PROCEDURE p1()
-- BEGIN 
-- SELECT * from student WHERE sid>4;
-- insert into teacher (tname) values (‘alex‘);
-- end //
-- delimiter ;
-- 
call p1()
View Code

　　如果实在pycharm上使用的语句为：

技术分享图片 
cursor.callproc(‘p1‘)
ret=cursor.fetchall()
print(ret)
print(cursor.lastrowid)
View Code

　　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)
View Code

　　如果要在pycharm上运行：

技术分享图片 
cursor.execute(sql)
cursor.callproc(‘p2‘,(2,4))
ret=cursor.fetchall()
print(ret)
print(cursor.lastrowid)
View Code

　　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;
View Code
技术分享图片 
-- 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;
View Code

　　使用pycharm的语法：

技术分享图片 
cursor.callproc(‘p3‘,(2,4))
ret=cursor.fetchall()
print(ret)
cursor.execute(‘select @_p3_0,@_p3_1‘)
ret=cursor.fetchone()
print(ret)
View Code
技术分享图片 
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}
View Code

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 ;
View Code

2.

 

mysql第四天使用 视图、触发器、存储、函数：

原文：https://www.cnblogs.com/ab461087603/p/12733476.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶