MYSQL高级（下）

also_lucky
175 阅读7分钟

第五章 视图(掌握)

sql脚本

说明：创建国家和城市表。

  1. 1个国家有多个城市，一个城市只属于一个国家
  2. 国家和城市是：1对多

image-20200704183150041.png

-- 1个国家有多个城市，一个城市只属于一个国家
-- 国家和城市是：1对多
CREATE TABLE country (
    country_id int(11) PRIMARY KEY AUTO_INCREMENT,
    country_name varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE city (
    city_id int(11) PRIMARY KEY AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int(11) NOT NULL,
    constraint ref_country_fk foreign key(country_id) references country(country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
insert into country values(1,'China');
insert into country values(2,'America');
insert into country values(3,'Japan');
insert into country values(4,'UK');
​
​
insert into city values(1,'西安',1);
insert into city values(2,'NewYork',2);
insert into city values(3,'北京',1);
insert into city values(4,'上海',1);

5.1 视图介绍

1.概述

MySQL从5.0.1版本开始提供视图功能。视图（View）是一种虚拟存在的表，行和列的数据来源于定义视图的查询中使用的表，并且是在使用视图时动态生成的。和普通表一样使用，但是视图并不在数据库中实际存在。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。

2.应用场景

1）多个地方用到同样的查询结果

2）该查询结果使用的sql语句较复杂

生活举例：例如学校所有学科有30个班级，每个班级相当于一个数据库的表。假设消防大队过一段时间需要来检查有关的消防演习，所以会在每个班级抽取几名同学参加演习，然后组成一个临时班称为消防演习班，这个班实际上是不存在的，只是临时的。只要消防大队来检查，那么就会召集原来的人进行演习，演习完毕学员各自回到自己的班级。

其实视图的作用就和上述例子中的临时的消防班一样。属于临时的。

【示例】

需求：在城市和国家表中查看所有国家和对应城市的信息。

没有使用视图：

-- 在城市和国家表中查看所有国家和对应城市的信息
select t.*,c.country_name from country c inner join city t on c.country_id = t.country_id;

使用视图：

-- 创建视图
create view city_country_view
as 
select t.*,c.country_name from country c inner join city t on c.country_id = t.country_id;
​
select * from city_country_view; -- city_country_view表示创建的视图名

3.视图和表的区别

使用方式	  占用物理空间

视图	  完全相同	  不占用，仅仅保存的是sql逻辑，磁盘上只有表结构，没有数据

表	  完全相同	  占用

4.视图的好处

* 1、sql语句提高重用性，效率高
* 2、和表实现了分离，提高了安全性，使用视图的用户只能访问他们被允许查询的结果集

5.2 创建视图

创建视图的语法为：

create view 视图名
as
查询语句;

​

示例:

​

需求：在城市和国家表中查看所有国家和对应城市的信息。

创建city_country_view视图 , 执行如下SQL :

-- 创建视图
create view city_country_view
as 
select t.*,c.country_name from country c inner join city t on c.country_id = t.country_id;
-- 查看视图
select * from city_country_view; -- city_country_view表示创建的视图名

image.png

5.3修改视图

修改视图的语法有两种方式：

【1】方式一：

create or replace view 视图名
as
查询语句;
-- 说明：如果存在该视图名就是修改视图，如果不存在该视图名则创建视图。

案例：修改上述视图city_country_view，select语句变为查询城市名是上海的城市信息和所属国家信息

create or replace view city_country_view
as
select t.*,c.country_name from country c inner join city t on c.country_id = t.country_id
where t.city_name='上海';

-- 查看视图
select * from city_country_view;

【结果】 image-20200704205209148.png

【2】方式二：

alter view 视图名
as
查询语句;

案例：修改上述视图city_country_view，select语句变为查询城市名是北京的城市信息和所属国家信息

alter view city_country_view
as
select t.*,c.country_name from country c inner join city t on c.country_id = t.country_id
where t.city_name='北京';

-- 查看视图
select * from city_country_view;

【结果】

image-20200704205534401.png

5.4 查看视图

说明：查看视图时最好在dos窗口中查看，这样可以看得更详细些，可视化工具看的不详细。

查看视图有以下几种方式：

1）从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

image-20200615164647797.png

2）同样，在使用 desc 视图名; 可以查看视图的结构。

image-20200704210920244.png

3）如果需要查询某个视图的定义，可以使用 show create view 视图名 命令进行查看 ：

image-20200615171013883.png

说明：\G表示格式化的意思。这样显示的结果看起来更舒服一些。

5.5 删除视图

语法:

drop view 视图名,视图名,.....;

​

示例:

​

删除 city_country_view 视图

 DROP VIEW city_country_view ;

image-20200615171255846.png

第六章 触发器(了解)

6.1 触发器概述

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

也可以这样理解触发器主要是通过事件进行触发而被执行的。提前给某张表的所有记录(行)绑定一段代码，如果执行的操作触发，这段提前准备好的代码就会自动执行。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

说明：OLD表示操作前的对象，NEW 表示操作之后的对象。

触发器类型NEW 和 OLD的使用
INSERT 型触发器NEW 表示将要或者已经新增的数据，插入之前没有数据，所以不能使用OLD
UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据,删除之后没有数据了，所以不能使用NEW

6.2 创建触发器

​

语法:

​

create trigger trigger_name
before/after insert/update/delete
on tbl_name
for each row   -- 行级触发器
    trigger_stmt ;
说明：
    1）before/after位置称为触发时机，一个触发器只能选择一个
    2）insert/update/delete位置称为触发事件，一个触发器只能选择一个
    3）for each row称为行级触发器，触发器绑定实质是表中的所有行，因此当每一行发生指定改变的时候，就会触发触发器。

​

示例:

​

需求: 通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

sql脚本:创建员工表，员工信息

create table emp(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int,
    salary int
);
insert into emp(id,name,age,salary) values(null, '金毛狮王',40,2500);
insert into emp(id,name,age,salary) values(null, '蝙蝠王',38,3100);

sql脚本: 创建一张日志表,存放日志信息

create table emp_logs(
    id int(11) primary key auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的ID,emp表数据的id',
    operate_params varchar(500) comment '操作参数,插入emp中的数据'
)engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录 :

create trigger emp_logs_insert_trigger
after insert
on emp
for each row
insert into emp_logs values(null,'insert',now(),new.id,
concat('插入后(id:',new.id,', name:',new.name,',age:',new.age,', salary:',new.salary,')'));

【向emp表中插入数据:】

insert into emp values(null, '光明左使',30,3500);

image-20200706223939553.png 说明：我们发现向emp表中插入数据之后，日志表中自动添加插入的信息。

创建 update 型触发器，完成更新数据时的日志记录 :

create trigger emp_logs_update_trigger
after update
on emp
for each row
insert into emp_logs
values(null,'update',now(),new.id,concat('修改前(id:',old.id,',name:',old.name,',age:',old.age,', salary:',old.salary,') , 修改后(id:',new.id, 'name:',new.name,',age:',new.age,', salary:',new.salary,')'));

【将emp中id是3的用户更新为：name=张无忌,age=20,salary=20000】

update emp set name='张无忌',age=20,salary=20000 where id = 3;

image-20200706224818904.png 更新emp表的数据，日志表就会添加数据。

创建delete 行的触发器 , 完成删除数据时的日志记录 :

create trigger emp_logs_dalete_trigger
after delete
on emp
for each row
insert into emp_logs 
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,',name:',old.name,', age:',old.age,', salary:',old.salary,')'));

【删除id是3的用户】

delete from emp where id=3;

image.png

6.3 查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

show triggers ;

可视化工具：

image-20200706225538151.png

命令行：

image-20200706225650019.png

6.4 删除触发器

drop trigger trigger_name

image-20200706225917200.png

avatar
文章
阅读
粉丝