Mysql之视图、触发器、事物、存储过程、函数
视图
视图是一张虚拟表，通过sql语句得到的一个临时表，可为其命名，下次可将其当成真实表使用。
# 以下有2张有关系的真实表 mysql> select * from course; +-----+-------------+------------+ | cid | course_name | teacher_id | +-----+-------------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 化学 | 3 | | 4 | 语文 | 1 | +-----+-------------+------------+ mysql> select * from teacher; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 0 | | | 1 | 张磊 | | 2 | 李一 | | 3 | 王杰 | +-----+--------------+ #通过左连接得到一张虚拟表 mysql> select * from course left join teacher on teacher_id = teacher.tid; +-----+-------------+------------+------+--------------+ | cid | course_name | teacher_id | tid | teacher_name | +-----+-------------+------------+------+--------------+ | 1 | 生物 | 1 | 1 | 张磊 | | 4 | 语文 | 1 | 1 | 张磊 | | 2 | 物理 | 2 | 2 | 李一 | | 3 | 化学 | 3 | 3 | 王杰 | +-----+-------------+------------+------+--------------+
创建视图
#语法 create view 视图名称 as sql语句 #示例 create view teacher_view as select tid from teacher where teacher_name='张磊'; select * from teacher_view; +-----+ | tid | +-----+ | 1 | +-----+ select course_name from course where teacher_id = (select tid from teacher_view)； +-------------+ | course_name | +-------------+ | 生物 | | 语文 | +-------------+ #通过利用视图teacher_view查询
使用视图
#有如下表 mysql> select *from teacher; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 1 | 张磊 | | 2 | 李一 | | 3 | 王杰 | +-----+--------------+ create view teacher_view as select * from teacher where tid=2; mysql> select * from teacher_view; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 2 | 李一 | +-----+--------------+ #修改视图 update teacher_view set teacher_name="于今"; mysql> select * from teacher_view; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 2 | 于今 | +-----+--------------+ mysql> select * from teacher; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 1 | 张磊 | | 2 | 于今 | | 3 | 王杰 | +-----+--------------+ # 修改视图数据后，原表对应的数据也被修改
视图中的记录被修改后，原来的表中对应的数据也被修改，因此一般不应该修改视图中的记录。而且在视图设计到多个表时是无法修改器记录的。
修改视图
#有如下表 mysql>select * from teacher_view; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 2 | 于今 | +-----+--------------+ mysql> select * from teacher; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 1 | 张磊 | | 2 | 于今 | | 3 | 王杰 | +-----+--------------+ #修改表 语法：alter view 视图名称 as sql语句 alter view teacher_view as select * from teacher where tid <3; mysql> select * from teacher_view; +-----+--------------+ | tid | teacher_name | +-----+--------------+ | 1 | 张磊 | | 2 | 于今 | +-----+--------------+
删除视图
语法：drop view 视图名称 drop view teacher_view
触发器
满足某种条件后，自动触发的功能称为触发器。具体是指在满足对某张表的增、删、改的前提下，触发指定sql语句的执行。
创建语法
create trigger 触发器名称 after 操作 on 表名 for each row begin sql语句 end # 在对某张表进行某些操作之后自动执行其sql语句 create trigger 触发器名称 before 操作 on 表名 for each row begin sql语句 end # 在对某张表进行某些操作之前自动执行其sql语句 # 操作是指对某张表的操作，包括插入insert，删除delete，更新update
示例
# 创建2张表 create table score( id int primary key auto_increment, tname varchar(32), score int ); create table good_student( id int primary key auto_increment, tname varchar(32) ); # 创建触发器 delimiter $$ create trigger tri_after_insert_score after insert on score for each row begin if new.score >=85 then insert into good_student(tname) values (new.tname); # 必须加分号 end if ; # 必须加分号 end $$ delimiter ; # delimiter $$，修改sql的语句结束符，因为触发器中必须加分号，但语句并未结束，则结束符修改成别的符号，触发器创建完后再修改回分号。 # 插入2条测试数据 insert into score(tname,score) values ('上官',80), ('东方',90); # 触发器触发，将符合条件的插入另一张表 mysql> select * from good_student; +----+--------+ | id | tname | +----+--------+ | 1 | 东方 | +----+--------+
删除触发器
drop trigger 触发器名称；
事务
开启的事务中可包含多个sql语句，这多个sql语句的执行要么同时成功，要么都不成功。该特性称为事务的原子特性。
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
事务一般用于转账操作。
示例
# 创建表 create table user( id int primary key auto_increment, name varchar(16), balance int ); insert into user(name,balance) values ('Chris',1000), ('Kris',1000); # 开启事务，原子性操作，2条命令要么同时成功，要么同时失败 start transaction update user set balance=500 where name='Chris' update user set balance=1500 where name=''Kris' commit; start transaction update user set balance=500 where name='Chris' update user set balance=1500 where name=''Kris' rollback; commit; # 只要在执行commit前执行rollback，数据会回滚到之前的状态。
事务的性质
严格上来说，事务必须同时满足四个特性，即通常所说的ACID属性：
A（atomicity）原子性。一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。
C（consistency）一致性。一个事务的执行不应该破坏数据库的完整性约束。
I（isolation）隔离性。事务之间相互独立，互不干挠。
D（durability）持久性。事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关。
存储过程
存储过程中包装了一系列的sql语句，存放于MySQL中，通过调用其名字执行一系列的sql语句。
连表操作报错
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (gbk_chinese_ci,COERCIBLE) for operation '='
该错误是因为创建的表的字符编码不统一，须统一字符编码。
