mysql视图

#使用视图的意义
#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;