数据库之视图和触发器

MySQL程序设计
#数据库视图和触发器

数据库有坑，大家慎重对待。

1.在job数据库中，有聘任人员信息表—Work_Info表，其结构表已知。
a.创建视图info_view，显示年龄大于20岁的聘任人员的id、name、sex、address信息
b.查看视图info_view的基本结构和详细结构
c.查看视图info_view的所有信息
d.修改视图info_view，显示年龄大于20岁的聘任人员的id、name、sex、address信息
e.更新视图，将id为3的聘任员的性别有“男”改为“女”
f.删除info_view视图

create database work;
use work;
create table work_info(
id int(4) NOT NULL  UNIQUE   PRIMARY KEY,
name varchar(20) NOT NULL,
sex varchar(4) NOT NULL,
age int(4) ,
address varchar(50) NOT NULL,
tel varchar(20));
insert into work_info(id,name,sex,age,address,tel)
values
(1,'张明','男',19,'北京市朝阳区','1234567'),
(2,'李广','男',21,'北京市昌平区','2345678'),
(3,'王丹','女',18,'湖南省永州市','3456789'),
(4,'赵一枚','女',24,'浙江省宁波市','4567890');
create algorithm = merge view info_view(id,name,sex,address) 
as 
select id,name,sex,address 
from work_info where age>20 
with local check option;
desc info_view;
show create view info_view\G;
select *from info_view;
alter algorithm = merge view info_view(id,name,sex,address)
as 
select id,name,sex,address 
from work_info where age<20 
with local check option;
update info_view set sex='女' where id=3;
drop view info_view;

2.在学生管理系统中有学生信息表studentinfo，其结构表已知。
a.使用create view语句来创建视图college_view，显示studentinfo表中的number、name、age、major，并将字段名显示为student_num、student_name、student_age、department
b.执行show create view语句来查看视图的详细结构
c.更新视图。向视图中插入三条记录：
0901,’张三’,20,’外语’
0902,’李四’,22,’计算机’
0903,’王五’,19,’计算机’
d.修改视图，使视图中只显示专业为“计算机”的信息
e.删除视图

create database student;
use student;
create table student_info(
number int(4)not null unique primary key auto_increment,
name varchar(20)not null,
age varchar(20) not null,
major int(4));
create view college_view(student_num,student_name,student_age,department)
as select number,name,age,major
from student_info;
show create view college_view;
insert into college_view(student_num,student_name,student_age,department)
values
(0901,'zhangsan',20,'英语'),
(0902,'lisi',22,'Computer'),
(0903,'wangwu',19,'Computer')；
create view collage_view as select * from student_info 
where major='计算机';
drop view college_view;

Incorrect integer value: ‘英语’ for column ‘major’ at row 1
在这一过程中，上一句的语句报错经常出现，百度或者相关咨询都没有修正，不过原因很多，但是没有找到适合这一个的，希望有好心人纠正，谢谢。

3.某同学定义产品信息product表,主要信息有:产品编号、产品名称、主要功能、生产厂商、厂商地址,生产product表已知
(1)在product表上分别创建BEFORE INSERT、AFTER UPDATE和AFTER DELETE三个触发器，触发器的名称分别为Tproduct_bf_insert、Tproduct_af_update和Tproduct_af_del执行语句部分都是向operate表插入操作方法和操作时间。
a.创建Tproduct_bf_insert触发器的代码已知。
b.创建Tproduct_af_update触发器的代码已知
c.创建Tproduct_af_del触发器的代码已知
(2)对product表分别执行INSERT、UPDATE、DELETE操作，分别查看operate表
a.对product表中插入一条记录：1,’abc’,’治疗感冒’,’北京’,’北京abc制药厂’,’北京市昌平区’
b.更新记录，将产品编号为1的厂商住址改为“北京市海淀区”
c.删除产品编号为1的记录
(3)删除Tproduct_bf_update触发器

create database product_info;
use product_info;
create table product(
id int(4) not null unique primary key,
name varchar(20),
function varchar(50),
company varchar(20),
address varchar(50));
create table operate(
op_id int(10) not null unique primary key auto_increment,
op_name varchar(20) not null,
op_tiem time not null);
create trigger product_bf_insert before insert
on product for each row
insert into operate values(null,'insert product',now());
create trigger product_af_update after update
on product for each row
insert into operate values(null,'update product',now());
create trigger product_af_del after delete
on product for each row
insert into operate values(null,'delete product',now());
insert into product values(1,'abc','治疗感冒','北京abc制药厂','北京市昌平区');
update product set address = '北京市海淀区' where id=1;
delete from product where id=1;
drop trigger product_bf_insert;

4.(1) 在product表上分别创建BEFORE INSERT、AFTER UPDATE和AFTER DELETE三个触发器，触发器的名称分别为Tproduct_bf_insert、Tproduct_af_update和Tproduct_af_del执行语句部分都是向operate表插入操作方法和操作时间。
(2)查看product_bf_del触发器的基本结构
(3)对product表分别执行已知的INSERT、UPDATE和DELETE操作，分别查看operate表

create database product_info;
use product_info;
create table product(
id int(4) not null unique primary key,
name varchar(20),
function varchar(50),
company varchar(20),
address varchar(50));
create table operate(
op_id int(10) not null unique primary key auto_increment,
op_name varchar(20) not null,
op_tiem time not null);
create trigger product_af_insert before insert
on product for each row
insert into operate values(null,'insert product',now());
create trigger product_bf_update after update
on product for each row
insert into operate values(null,'update product',now());
create trigger product_bf_del after delete
on product for each row
insert into operate values(null,'delete product',now());
insert into product values(2,'止血灵','止血','北京止血灵制药厂','北京市昌平区');
update product set address = '天京市开发区' where id=2;
delete from product where id=2;
drop trigger product_bf_update;

第一次写这个博客，有点不会，慢慢熟悉。