#使用视图的意义 #1.简化查询，用户不需要复杂的sql去查询多张复杂的关联表 #2. 安全，使用视图的用户只能访问被允许查询的结果，使用视图可以限制用户访问一些敏感信息
drop table if exists users; create table `users`( `u_id` int(4) not NULL auto_increment, `username` varchar(20) DEFAULT NULL, `age` int(4) default NULL, `country` varchar(20) default NULL, PRIMARY KEY (`u_id`) )ENGINE = INNODB auto_increment=1 DEFAULT CHARSET=utf8; drop table if exists course; CREATE TABLE `course`( `c_id` int(4) NOT NULL auto_increment, `coursename` varchar(20) default NULL, `description` varchar(255) default NULL, PRIMARY key(`c_id`) )ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8; drop table if exists user_course; CREATE TABLE `user_course`( `u_c_id` int(4) NOT NULL auto_increment, `userid` int(4) NOT NULL, `courseid` int(4) NOT NULL, PRIMARY KEY (`u_c_id`) )ENGINE=INNODB,auto_increment=1,default CHARSET= utf8; insert into users(username,age,country) values ('Tom',23,'china'), ('JIM',24,'USA'), ('Jean',25,'canda'), ('rof',26,'russa'); insert into course(coursename,description) values ('java','mysql'), ('web','web class'), ('c++','computer'), ('c','base class'); insert into user_course(userid,courseid) values (1,1), (1,2), (1,4), (2,3), (2,4), (3,1), (4,2); select * from users; select * from course; select * from user_course;子查询方式获取某用户的课程
select username,coursename from user_course left join course on user_course.courseid = course.c_id left join users on user_course.courseid = users.u_id where username='Tom';#创建视图的方式获取某用户的课程
create view user_course_view as select * from user_course left join course on user_course.courseid = course.c_id left join users on user_course.courseid = users.u_id; select username,coursename from user_course_view where username='Tom'#如果有些信息是隐秘的话，可以通过视图的方式进行隐藏 #创建视图，隐藏课程的描述信息
create view user_course_view as select user_course.*,users.*,course.c_id,course.coursename from user_course left join course on user_course.courseid = course.c_id left join users on user_course.userid = users.u_id;