MySql学习day4

一、备份和导入

mysqldump -u root (-d只备份结构) db1>db1.sql -p #备份

mysqldump -u root db4(新创建的数据库)<db1.sql -p #导入

二、pymysql模块

 

conn = pymysql.connect(host="localhost",user="root",password="",database="db666",charset="utf8")
cursor = conn.cursor(pymysql.cursors.DictCursor)


sql = "select * from table"
line_infected = cursor.execute(sql) #受影响的行数 #sql注入
#查找
res = cursor.fetchone()
res = cursor.fetchall()
#增删改
conn.commit() #提交修改
cursor.lastrowid #新增自增id查询

三、union上下连表

union 去重
union all不去重

四、视图

动态的临时表，使用方法和临时表一样

增:
create view 视图名称 as sql语句
删:
drop view 视图名称
改:
alter view 视图名称 as sql语句

五、触发器

类似于回调函数，当增删改时触发运行

delimiter //
create trigger t1 before insert into student for each row
begin
select * from xxx;
insert ...
end
delimiter ;

new指代新数据，old指代老数据

六、函数

6.1 内建函数

date_format() #格式化时间

其他参考官方

6.2 自定义函数

delimiter //
create function f1(
x int,
y int
)
returns int
begin
...
end //
delimiter ;

6.3 调用函数

select  f1();

注意：函数无法使用select等语句，和函数式编程的思路一样

七、存储过程

一段sql语句，可以进行各种操作，类似于一段程序

7.1 创建

delimiter //
create procedure p1(
in x int, 
out y int,
inout z int
)
begin
...
end //
delimiter ;

7.2 调用

数据库内

call p1()
#对于out应该使用全局变量@v来传入参数

pymysql

cursor.callproc("p1",(12,1))
cursor.execute("select @_p1_0,@_p1_1")
r2 = cursor.fetchall()

7.3 事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

delimiter \\
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; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\\
delimiter ;

支持事务的存储过程

八、动态执行sql语句--数据库层面防注入

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

动态执行SQL