一、视图实例1-创建视图及查询数据操作
首先，创建三个表：user、course、user_course
表：user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(200) NOT NULL,
`name` varchar(200) NOT NULL,
`address` varchar(500) DEFAULT NULL,
`others` varchar(500) DEFAULT NULL,
`others2` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into user (account,name,address,others1,others2)
values
(user1,'小张','天津',1,1),
(user2,'小王','北京',2,2),
(user3,'小李','上海',3,3);
表：course
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`description` varchar(500) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into course (name,description) values
('JAVA','JAVA课程'),
('C++','C++课程'),
('C','C课程');
表：user_course
CREATE TABLE `user_course` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`courseid` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into user_course (userid,courseid) values
(1,2),
(1,3),
(2,4),
(3,5);
其次，利用表关联三表进行联合查询：
SELECT
uc.id AS id,
u. NAME AS username,
c.description AS coursename
FROM
USER u
LEFT JOIN user_course uc ON ((u.id = uc.userid))
LEFT JOIN course c ON ((uc.courseid = c.id))
WHERE
u. NAME = '小王';
最后，创建视图进行三表关联查询：
DROP VIEW
IF EXISTS view_user_course; CREATE VIEW view_user_course AS (
SELECT
uc.id AS id,
u. NAME AS username,
c.description AS coursename
FROM
USER u
LEFT JOIN user_course uc ON ((u.id = uc.userid))
LEFT JOIN course c ON ((uc.courseid = c.id))
);
SELECT
*
FROM
view_user_course vuc
WHERE
vuc.username = '小张';
二、视图实例2-增删改数据操作
首先， 创建视图：
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
create view st_view as(
select
u.id as st_id,
u.`name` as st_name,
u.account as st_user
from `user` u );
#视图增删改查
insert into st_view (st_name,st_user)
values('小孙','user4'); delete from st_view
where st_name = '小孙'; update st_view
set st_name = '小田'
where st_id = 7