MySQL(2)
MySQL 注入(简单)：
我们在输入账号密码通过MySQL语句进行数据库操操作登陆的时候，除了输入正确账号密码进行正常登陆，还可以输入错误的用户密码进行登录，利用的就是MySQL -- 的注释作用
首先创建一个userInfo表格：
mysql> select *from userInfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | alex | 123 | +-----+------+----------+ 1 row in set (0.00 sec)
通过python中的pysql进行登录
import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='zhaoyinghan', db='db1') # 创建游标. 负责操作 cursor = conn.cursor() while True: inp_name = input('姓名：') inp_password = input('密码：') cammand = "select name from userInfo WHERE name = '%s' and password = '%s'"%(inp_name,inp_password) print('cammand = ',cammand) # 执行SQL，并返回收影响行数 request = cursor.fetchone() print(effect_row) if request: print('登陆成功',request) else: print('密码或账号错误') # 执行SQL，并返回受影响行数 # effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数 # effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交，不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() >>> 姓名：alex 密码：123 cammand = select name from userInfo WHERE name = 'alex' and password = '123' 1 登陆成功 ('alex',) # 输入正确用户名 密码 登录成功 姓名：alex' -- dfadf 密码：asdf cammand = select name from userInfo WHERE name = 'alex' -- dfadf' and password = 'asdf' 1 登陆成功 ('alex',) # 输入错误用户名密码 也登录成功，这是因为 在MySQL命令语句中， -- 代表着注释之后的内容 ,执行的"cammand"命令中 只是执行了 select name from userInfo WHERE name = 'alex' 语句，
所以可以登录成功，这就是SQL注入，以后也是需要注意的地方，而pymysql中 提封装了可输入参数的MySQL函数-> execute(query,args =None)我们只需要更改一下用到的execute函数就可以避免了 effect_row = cursor.execute("select name from userInfo WHERE name = '%s' and password = '%s'"，(inp_name,inp_password)) 这样我们再次输入 ' -- '就不会出现上述情况了
视图：
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
临时表搜索：
mysql> select * from Person_1; +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 21 | red | +----+-------+-----+--------+ mysql> select * from(select * from Person_1 where id < 4) as new_P where new_P.color = 'white';
# 通过（）将筛选条件扩阔入，然后从筛选出来的结果中继续筛选 +----+------+-----+-------+ | id | name | age | color | +----+------+-----+-------+ | 1 | tom | 11 | white | | 3 | jack | 14 | white | +----+------+-----+-------+ 2 rows in set (0.00 sec)
创建视图：
CREATE VIEW 视图名称 AS 条件语句
当需要在筛选后的表格内做重复操作的时候，我们可以通过视图将所筛选的表进行保存，然后通过视图继续进行重复性的操作。
>>> mysql> create view new_P as select* from Person_1 where id <4; # 创建名为new_P的视图 Query OK, 0 rows affected (0.01 sec)
mysql> select *from new_P; # 显示视图 +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | +----+-------+-----+--------+ 3 rows in set (0.00 sec)
删除视图：
DROP VIEW 视图名称 ,和删除表格一个格式
drop view new_P;
修改视图：
ALTER VIEW 视图名称 AS 条件语句
mysql> alter view new_P as select *from new_P where id<3; #更改时，不可根据自身进行更改 ERROR 1146 (42S02): Table 'db1.new_p' doesn't exist mysql> mysql> alter view new_P as select *from Person_1 where id<3; # 进行更改数据 Query OK, 0 rows affected (0.00 sec) mysql> select *from new_P; +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | +----+-------+-----+--------+ 2 rows in set (0.00 sec)
存储过程:
存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行（类似于函数）。
创建：
CREATE PROCEDURE 名称(参数) START 存储过程 END
mysql> delimiter // # sql语句中是以;为结束语句，但创建procedure时，也是会有；为了避免提前结束，更改结束语句为 // mysql> drop procedure if exists p_1// # 如果procedure p_1存在的话，就删除，防止procedure 命名冲突 Query OK, 0 rows affected (0.00 sec) mysql> create procedure p_1() #创建procedure p_1 ,括号内的是参数 -> begin #开始 -> select *from Person_1; -> end// #结束，用//来告知系统语句执行结束位置 Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; # 将结束符号更改回； mysql> call p_1; # 调用procedure p_1 +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 21 | red | +----+-------+-----+--------+ 5 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
参数：
in 参数名称 参数类型 - 传入参数
inout 参数名称 参数类型 - 传入、返回参数
out 参数名称 参数类型 - 返回参数
delimiter // DROP PROCEDURE IF EXISTS p_2// CREATE PROCEDURE p_2(IN i INT , INOUT j INT , OUT k INT) # 创建带参数的 PROCEDURE IF i = 1 THEN SET j = 100 + j ; SET k = 100 + i ; ELSE SET j = 200 + j ; SET k = 200 + i ; END IF ; END// delimiter ; #调用 SET @j = 2; SET @k = 0; CALL p_2(1 ,@j ,@k); SELECT @j ,@k mysql> select @j,@k; +------+------+ | @j | @k | +------+------+ | 102 | 101 | +------+------+ 1 row in set (0.00 sec)
通过pymysql进行操作:
# 创建PROCEDURE 命名为p_3 delimiter // DROP PROCEDURE IF EXISTS p_3// CREATE PROCEDURE p_3(IN i INT , INOUT j INT , OUT k INT) IF i = 1 THEN SET j = 100 + j ; SET k = 100 + i ; select *from Person_1; ELSE SET j = 200 + j ; SET k = 200 + i ; END IF ; END// delimiter ; import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='zhaoyinghan', db='db1') # 创建游标. 负责操作 cursor = conn.cursor() # 执行存储过程 传参数的时候，只有in和inout才会传入进去，out的就直接忽略了 row = cursor.callproc('p_3',(1,1,1)) # 当执行过程中有查询语句的时候，可以在cursor.executer 之前获取查询结果（必须在cursor.executer之前） result = cursor.fetchall() # 执行 获取 返回值 effect_row = cursor.execute('select @_p_3_0, @_p_3_1, @_p_3_2') # 获取存储过程的返回值 sel = cursor.fetchall() print(result,sel) # 关闭游标 cursor.close() # 关闭连接 conn.close() >>> ((1, 'tom', 11, 'white'), (2, 'jerry', 12, 'yellow'), (3, 'jack', 14, 'white'), (4, 'rose', 14, 'blue'), (5, 'herry', 21, 'red'))
((1, 101, 101),) # 执行后返回的结果
触发器：
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
# 插入前 CREATE TRIGGER trigger名称 BEFORE INSERT ON 触发的表 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER trigger名称 AFTER INSERT ON 触发的表 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER trigger名称 BEFORE DELETE ON 触发的表 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER trigger名称 AFTER DELETE ON 触发的表 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER trigger名称 BEFORE UPDATE ON 触发的表 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER trigger名称 AFTER UPDATE ON 触发的表 FOR EACH ROW BEGIN ... END
创建触发器：
以 insert 和 before为例：
delimiter // drop TRIGGER if EXISTS t_1// create trigger t_1 before insert on Person_1 fro each row
#create Trigger t_1 创建触发器命名为t_1 #before insert on Person_1 在对表Person_1进行insert操作的时候 #for each row 每一行insert 都触发，比如在同一语句中添加了两行值 insert into Person_1(xx,xx,xx) values(xx,xx,xx)(xx,xx,xx) ,那么添加的所有值都会影响到 BEGIN INSERT INTO userInfo(name,password) VALUES(NEW.name,NEW.color)); #在userInfo表格中添加新添加到Person_1中的 name 和password的值，其中NEW代表着Person_1新插入的值 END// delimiter ; mysql> select *from Person_1; +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 21 | red | +----+-------+-----+--------+ 5 rows in set (0.00 sec) mysql> select *from userInfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | alex | 123 | +-----+------+----------+ 1 row in set (0.00 sec) #对Person_1执行insert mysql> insert into Person_1(name,age,color) values('tony',32,'cyan'); Query OK, 1 row affected (0.00 sec) mysql> select *from Person_1; +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 21 | red | | 6 | tony | 32 | cyan | +----+-------+-----+--------+ 6 rows in set (0.00 sec) mysql> select *from userInfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | alex | 123 | | 2 | tony | cyan | +-----+------+----------+ 2 rows in set (0.00 sec)
当然，触发器中有befroe也会有after，而在insert 中我们是用NEW关键字来获取新插入Person_1中的数据。在delect数据的时候，因为删除的数据是旧的，所以我们就用old来获取删除的数据。
以删除数据为例：
delimiter // drop TRIGGER if EXISTS t_2// CREATE TRIGGER t_2 BEFORE DELETE on Person_1 FOR EACH ROW BEGIN DELETE FROM userInfo WHERE NAME = old.name; END// delimiter ; # 执行Person_1 删除语句 mysql> delete from Person_1 where name = 'tony'; Query OK, 1 row affected (0.00 sec) mysql> select *from userInfo; +-----+------+----------+ | nid | name | password | +-----+------+----------+ | 1 | alex | 123 | +-----+------+----------+ 1 row in set (0.00 sec) mysql> select *from Person_1; +----+-------+-----+--------+ | id | name | age | color | +----+-------+-----+--------+ | 1 | tom | 11 | white | | 2 | jerry | 12 | yellow | | 3 | jack | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 21 | red | +----+-------+-----+--------+ 5 rows in set (0.00 sec)
删除触发器：
drop trigger 触发器名称 mysql> drop TRIGGER if EXISTS t_2; Query OK, 0 rows affected (0.01 sec)
MySQL 函数：
同样的，在MYSQL中也是有内置的相关函数，上边提到执行Procedure的时候用call 而执行函数的时候用select。
查看字符串长度：char_length(str)
mysql> select char_length('alex'); +---------------------+ | char_length('alex') | +---------------------+ | 4 | +---------------------+ 1 row in set (0.00 sec)
拼接字符串：concat(str_1，str_2) 如有任何一个参数为NULL，则返回NULL
mysql> select concat('My','SQL'); +--------------------+ | concat('My','SQL') | +--------------------+ | MySQL | +--------------------+ 1 row in set (0.00 sec) # 如有任何一个参数为NULL ，则返回值为 NULL。 mysql> select concat('My',Null); +-------------------+ | concat('My',Null) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec)
字符串拼接（自定义连接符）: concat_ws(separator,str1,str2)
mysql> select concat_ws('_','alex','22','red'); +----------------------------------+ | concat_ws('_','alex','22','red') | +----------------------------------+ | alex_22_red | +----------------------------------+ 1 row in set (0.00 sec)
其他：
CONV(N,from_base,to_base) 进制转换 例如： SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos：要替换位置其实位置 len：替换的长度 newstr：新字符串 特别的： 如果pos超过原字符串长度，则返回原字符串 如果len超过原字符串长度，则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
自定义函数：
创建函数：
delimiter // drop function IF EXISTS f1// create function f1( i1 int, i2 int) returns int # 告知系统有一个int类型的返回值 BEGIN #开始 declare num int; # 需要注意的是函数体内，是不可以有查询语句，否则会报错。 set num = i1 + i2; return(num); END // #结束 delimiter ; # 调用函数 mysql> select f1(1,2); +---------+ | f1(1,2) | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) # 这里需要注意的是，函数和存储过程的区别是 调用Procedure用call 而 函数是select 并且 函数可以和表格操作仪一块使用： mysql> select f1(1,id),id,name from Person_1; +----------+----+-------+ | f1(1,id) | id | name | +----------+----+-------+ | 2 | 1 | tom | | 3 | 2 | jerry | | 4 | 3 | jack | | 5 | 4 | rose | | 6 | 5 | herry | +----------+----+-------+ 5 rows in set (0.00 sec)
删除函数：
drop fuction 函数名 mysql> drop function f1; Query OK, 0 rows affected (0.00 sec)
事物操作：
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。（回滚操作）
delimiter // drop PROCEDURE if EXISTS p1// CREATE PROCEDURE p1( OUT p_return_code TINYINT # 返回值 ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN -- ERROR SET p_return_code = 1 ; ROLLBACK ; #回滚 END ; DECLARE EXIT HANDLER FOR SQLWARNING BEGIN -- WARNING SET p_return_code = 2 ; ROLLBACK ; #回滚 END ; START TRANSACTION ; # 开始执行 UPDATE Person_1 set name = 'lala' where id = 3; UPDATE Person_1 set age = 33 where id = 5; COMMIT ; # 提交 -- SUCCESS SET p_return_code = 0 ; END// delimiter ; # 执行 mysql> drop function f1; Query OK, 0 rows affected (0.00 sec) mysql> set @i = 0; Query OK, 0 rows affected (0.00 sec) mysql> call p1(@i); Query OK, 0 rows affected (0.00 sec) mysql> select @i; +------+ | @i | +------+ | 0 | +------+ 1 row in set (0.00 sec)
需要了解到的是 在pymsql中 是默认支持回滚操作。
动态执行MySQL语句：
创建 Procedure 动态执行 delimiter \\ DROP PROCEDURE IF EXISTS proc_sql \\ CREATE PROCEDURE proc_sql (in sql_str varchar(125),in num ) BEGIN declare p1 int; set p1 = 11; set @p1 = p1; PREPARE prod FROM 'select * from tb2 where nid > ?'; #预编译 EXECUTE prod USING @p1; #将MySQL语句中的占位符 " ? "替换成@p1 DEALLOCATE prepare prod; #删除语句 prod END\\ delimiter ; 动态执行SQL:
mysql> call proc_sql('select *from Person_1 where id > ?',2); +----+-------+-----+-------+ | id | name | age | color | +----+-------+-----+-------+ | 3 | lala | 14 | white | | 4 | rose | 14 | blue | | 5 | herry | 33 | red | +----+-------+-----+-------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) #pymysql 中执行： import mysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='zhaoyinghan', db='db1') # 创建游标. 负责操作 cursor = conn.cursor() # 输入带有占位符的Mysql语句与参数 row = cursor.callproc('proc_sql',('select * from Person_1 where id > ?',1)) # 当执行过程中有查询的时候，可以在cursor.executer 之前 获取一下 查询结果（必须在cursor.executer之前） result = cursor.fetchall() print(result) # 关闭游标 cursor.close() # 关闭连接 conn.close() >>> ((2, 'jerry', 12, 'yellow'), (3, 'lala', 14, 'white'), (4, 'rose', 14, 'blue'), (5, 'herry', 33, 'red'))
