mysql视图 触发器 事务 存储过程

创建视图

create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;

 

 

 

mysql> update emp2dep set name="EGON" where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp2dep;
+----+-----------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | dep_name |
+----+-----------+--------+------+--------+--------------+
| 1 | EGON | male | 18 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 技术 |
+----+-----------+--------+------+--------+--------------+

 

mysql> select * from emp;

+----+------------+--------+------+--------+

| id | name | sex | age | dep_id |

+----+------------+--------+------+--------+

| 1 | EGON | male | 18 | 200 |

| 2 | alex | female | 48 | 201 |

| 3 | wupeiqi | male | 38 | 201 |

| 4 | yuanhao | female | 28 | 202 |

| 5 | liwenzhou | male | 18 | 200 |

| 6 | jingliyang | female | 18 | 204 |

| 7 | lili | female | 48 | NULL |

+----+------------+--------+------+--------+

7 rows in set (0.00 sec)

 

 

修改视图

alter view emp2dep as 查询语句;

删除视图

drop view emp2dep;

 

触发器  trigger

针对数据的增、删、改操作时前后的行为

增------>insert

create trigger tri_before_insert_t1 before insert on t1 for each row   #创建了一个触发器x,在忘这个表插入数据之前，针对每一行
begin
    sql语句;
end

create trigger tri_after_insert_t1 after insert on t1 for each row
begin
    sql语句;
end

 

删delete

create trigger tri_before_delete_t1 before delete on t1 for each row
begin
    sql语句;
end

create trigger tri_after_delete_t1 after delete on t1 for each row
begin
    sql语句;
end

修改update

insert into tt1 values(1,"egon",'male');

delimiter //   
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
    insert into tt2 values(NEW.name);
end //

delimiter ;



insert into tt1 values(2,"tom",'female');

#在mysql中；是结束 ，这里我们除开最后一句都不是结束 所以使用

delimiter //  

别忘记在结束后改回来

delimiter ;

 

 

事务

处理一件事情，要么一起成功，要么一起失败

import pymysql
conn=pymysql.connect(host="127.0.0.1",post=3306, user="root", password="123", db="db13", charset="utf8mb4")
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)


try:
    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    
    conn.commit()  #事务提交,真正提交到数据库内
　　#print('事务处理成功',cursor,rowcount)#关闭连接

except Exception as e:
    conn.rollback() #事务回滚

cursor.close()
conn.close()

 

create table user1(
    id int primary key auto_increment,
    name varchar(10),
    balance int
);

insert into user1(name,balance) values
('qq',1000),
('zz',1000),
('ss',1000);

start transaction;
update user1 set balance=800 where id=1;
update user1 set balance=1100 where id=2;
update user1 set balance=1100 where id=3;

存储过程

 

程序与数据库结合使用的三种方式

复制代码 #方式一： MySQL：存储过程 程序：调用存储过程

#方式二： MySQL： 程序：纯SQL语句

#方式三： MySQL: 程序：类和对象，即ORM（本质还是纯SQL语句）

 

创建无参存储过程

delimiter $$
create procedure p1()
begin
    select * from emp;
end $$

delimiter ;



#在cmd-------mysql中执行：
call p1();

 

 

创建有参存储过程

t1.py:

delimiter $$
create procedure p2(
in n int,  #in是输入
out res int  #out是出
)
begin
select * from emp where id > n;
set res=1;
end $$

delimiter ;

#在cmd-------mysql中执行：

先定义一个变量：
set @x=1111;
call p2(3,@x);







t2.py:

import pymysql
conn=pymysql.connect(host="127.0.0.1",post=3306, user="root", password="123", db="db13", charset="utf8mb4")
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p2',(3,0))  #@_p2_0=3,@_p2_1=0
cursor.execute("select @_p2_1;")
print(cursor.fetchall())

cursor.execute("select @_p2_0;")

print(cursor.fetchall())

cursor.close()
conn.close()