【Mysql】之視圖操作

標籤：col   sel   c++   資料   engine   nbsp   code   關聯查詢   creat   

一、視圖執行個體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 coursenameFROM    USER uLEFT JOIN user_course uc ON ((u.id = uc.userid))LEFT JOIN course c ON ((uc.courseid = c.id))WHERE    u. NAME = ‘小王‘;

最後，建立視圖進行三表關聯查詢：

DROP VIEWIF 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 vucWHERE    vuc.username = ‘小張‘;

二、視圖執行個體2-增刪改資料操作

首先， 建立視圖：

視圖與表是一對一關聯性情況：如果沒有其它約束（如視圖中沒有的欄位，在基本表中是必要欄位情況），是可以進行增刪改資料操作；

create view st_view as(select u.id as st_id,u.`name` as st_name,u.account as st_userfrom `user` u );

#視圖增刪改查insert into st_view (st_name,st_user)values(‘小孫‘,‘user4‘);delete from st_view where st_name = ‘小孫‘;update st_viewset st_name = ‘小田‘where st_id = 7

 

【Mysql】之視圖操作