TCL语言的学习
TCL :(Transaction Control Language):事务控制语言事务和事务处理
show engines;
set autocommit= 0 ;
start transaction ;
update account set balance= 1000 where username= '张无忌' ;
update account set balance= 1000 where username= '赵敏' ;
rollback ;
select * from account;
set autocommit= 0 ;
start transaction ;
delete from account where id= 25 ;
savepoint a;
delete from account where id= 28 ;
rollback to a;
select @@transaction_isolation ;
set transaction isolation level read committed ;
set global transaction isolation level read committed ;
视图
含义:mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成。 好处:1、简化sql语句,2、提高了sql的重用性,3、保护基表的数据,提高了安全性
create view 视图名
as
查询语句;
drop view myv1, myv2;
desc myv3;
show create view myv3;
create view emp_v1 as select last_name, salary, email from employees where phone_number like '011%' ;
create view emp_v3 as select max ( salary) mx, department_id from employees group by department_id;
select * from emp_v3;
create view emp_v2 as select d. * from departments d inner join emp_v3 on d. department_id= emp_v3. department_id where emp_v3. mx> 12000 ;
select * from emp_v2;
create or replace view myv3 as select last_name, email from employees;
select * from myv3;
insert into myv3 values ( '张飞' , 'zf@qq.com' ) ;
select * from employees;
update myv3 set last_name= '张无忌' where last_name= '张飞' ;
select * from employees;
delete from myv3 where last_name= '张无忌' ;
select * from employees;
create or replace view myv1 as select max ( salary) mx, department_id from employees group by department_id;
select * from myv1;
update myv1 SET mx= 9000 where department_id= 10 ;
create or replace view myv2 as select 'john' name;
select * from myv2;
update myv2 set name= 'lucy'
create or replace view myv3 as select ( select max ( salary) from employees) 最高工资;
select * from myv3;
update myv3 set 最高工资= 100000 ;
create or replace view myv4 as select last_name, department_name from employees e join departments d on e. department_id= d. department_id;
select * from myv4;
update myv4 set last_name= '张飞' where last_name= 'Whalen' ;
insert into myv4 values ( '陈真' , 'xxxx' ) ;
create or replace view myv5 as select * from myv3;
select * from myv5;
update myv5 set 最高工资= 10000 where department_id= 60 ;
create or replace view myv6 as select last_name, email, salary from employees where employee_id in ( select manager_id from employees where manager_id is not null ) ;
select * from myv6;
update myv6 set salary= 10000 where last_name= 'k_ing' ;
视图和表的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 没有( 只是保存了sql 逻辑) 增删改查,只是一般不能增删改
表 create table 占用( 保存了数据) 增删改查
set autocommit= 0 ;
start transaction ;
delete from account;
rollback ;
select * from account;
set autocommit= 0 ;
start transaction ;
truncate TABLE account;
rollback ;
select * from account;