MySQL笔记五

MySQL高级进阶：

 

事务控制：

什么是事务控制（有点像OS的原子性）：

事务（Transaction）是指作为一个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么 全部失败。事务确保对多个数据的修改作为一个单元来处理。

1.在MySQL中，只有使用了Innodb存储引擎的数据库或表才支持事务。

2.事务用于维护数据库的完整性，保证成批的sql语句要么都执行，要么都不执行。

 3.事务用于管理INSERT、UPDATE和DELETE语句。

事务的四个特性：

如果某个数据库声称支持事务，那么该数据库必须具备ACID四个特性，即Atomicity（原子性）、 Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。

MySQL的事务控制：

在默认情况下，MySQL是自动提交事务的，即每一条INSERT、UPDATE、DELETE的SQL语句提交 后会立即执行COMMIT操作。因此，要开启一个事务，可以使用start transaction或begin，或者 将autocommit的值设置为0。

#开启一个事务
mysql> start transaction;

#提交事务
mysql> commit;

#回滚
mysql> rollback;

#查看autocommit的值
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

#查看autocommit的值
mysql>  show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

#修改autocommit的值
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

视图：

什么是视图：

视图是一个虚拟表，其内容由select查询语句定义。和真实的表一样，视图也包含行和列，对视图 的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成，视图中的数据都存储在基 表中。

视图绑定的是单表的时候（视图表的数据变化会影响到基表，基表的数据变化也会影响视图表），如果视图绑定的是多表则显示不能修改（通常绑定的都是多表，不然视图就没有出现的意义）。

#创建视图
CREATE VIEW view_name AS SELECT…;
#修改视图
ALTER VIEW view_name AS SELECT…;
#查看视图创建语句
SHOW CREATE VIEW view_name;
#查看有哪些视图
SHOW TABLE STATUS WHERE comment='view';
#删除视图
DROP VIEW view_name;

个人理解:视图和表的表现形式，和对数据的语法形式都是一样的，他的出现只是为了封装

例：

use mydb;

#创建一个单表的视图
create view student_view as select * from student;
#查看有哪些视图
show table status where comment='view';
#查看视图的数据
select * from student_view;
#修改视图中的数据
update student_view set name='张晓燕' where stu_no='2016003';

#创建多表的视图
create view student_score_view as select A.*,B.course,B.score from student A left join score B on(A.stu_no=B.stu_no);
#查看视图的数据
select * from student_score_view;
#修改视图中的数据
update student_score_view set name='张晓燕' where stu_no='2016003';
#Error: ER_NON_UPDATABLE_TABLE: The target table student_score_view of the UPDATE is not updatable

存储过程的基本使用：

什么是存储过程：

存储过程（Stored Procedure）是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中， 用户可通过指定存储过程的名字并给定参数(需要时)来调用执行，类似于编程语言中的方法或函数。

存储过程的优点：

1. 存储过程是对SQL语句的封装，增强可复用性 2. 存储过程可以隐藏复杂的业务逻辑、商业逻辑 3. 存储过程支持接收参数，并返回运算结果。

存储过程的缺点：

1. 存储过程的可移植性较差，如果更换数据库，要重写存储过程 2. 存储过程难以调试和扩展 3. 无法使用Explain对存储过程进行分析 4. 《阿里巴巴Java开发手册》中禁止使用存储过程

例：不使用SQL语句

 note：（因为SQL的默认终结符是;但是在存储过程中，有多条语句所以就使用声明语句结束符）

#存储过程定义：计算1+2+...+n的和
delimiter //
create procedure my_n_sum(in n int, out result int)
begin
 declare i int default 1;
 declare sum int default 0;
 while i<=n do
 set sum = sum + i;
 set i = i + 1;
 end while;
 set result = sum;
end;
//
delimiter ;

使用SQL语句：

#表的创建
drop table if exists user_info;
drop table if exists email_info;
create table user_info(
 id int not null auto_increment primary key,
 name varchar(30),
 email varchar(50)
);
insert into user_info(id, name, email) values(1, '柳峰', 'liufeng@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');
create table email_info(
 id int not null auto_increment primary key,
 email varchar(50),
 content text,
 send_time datetime
);

#存储过程示例：根据用户id和邮件内容content给用户发邮件
delimiter //
create procedure send_email(in user_id int, in content text)
begin
 /* 根据用户id查询邮箱email */
 set @user_email=(select email from user_info where id=user_id);
 /* 模拟发送邮件 */
 insert into email_info(email, content, send_time) values(@user_email, content, now());
end;
//
delimiter ;
call send_email(1, '欢迎加入MySQL阵营！');

 触发器：

触发器（trigger）用于监视某种情况并触发某种操作，它是与表事件相关的特殊的存储过程，它的 执行不是由程序调用，而是由事件来触发。例如，当对某张表进行insert、delete、update操作时 就会触发执行它。

例：

drop table if exists user_info;
drop table if exists email_info;
create table user_info(
 id int not null auto_increment primary key,
 name varchar(30),
 email varchar(50)
);
insert into user_info(id, name, email) values(1, '柳峰', 'liufeng@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');
create table email_info(
 id int not null auto_increment primary key,
 email varchar(50),
 content text,
 send_time datetime
);

#触发器实战：给新用户发邮件
delimiter //
CREATE TRIGGER send_email_trigger AFTER INSERT ON user_info FOR EACH ROW
BEGIN
 insert into email_info(email, content, send_time) values(NEW.email, '欢迎加入MySQL阵营！ ', now());
END
//
delimiter ; 

#当我向user_info插入一条新的数据，email会有经过处理的数据新增。