MySQL的视图

#查看权限
SELECT SELECT_priv,CREATE_view_priv FROM mysql.user WHERE user = 'root'
#创建视图的方法
CREATE VIEW liziyi AS ( SELECT * FROM course );#1种
CREATE VIEW V_VIEW1(course_id,course_name,course_credit) AS SELECT course_id,course_name,course_credit FROM course;
#联动两个表
CREATE VIEW zuoye1(course_name,student_id,score) AS SELECT a.course_name,b.student_id,b.score FROM course as a,score as b WHERE a.course_id=b.course_id;
#修改视图1
CREATE OR REPLACE VIEW V_VIEW1(course_id,course_name,) AS SELECT course_id,course_name,course_credit FROM course;
#修改视图2
ALTER VIEW V_VIEW1(course_id,course_name,course_credit) AS SELECT course_id,course_name,course_credit FROM course;
#查看方法
SELECT * FROM V_VIEW1
DESC V_VIEW1  #desc是查看表结构，ame是查看表名称
SHOW CREATE VIEW V_VIEW1  #查看一些信息
SHOW TABLE STATUS LIKE 'V_VIEW1' #查看详细信息
#更新
UPDATE V_VIEW1 SET `course_name` ='小丑教学课' WHERE course_id=1
CREATE VIEW V_VIEW3(course_id,course_name,course_credit) AS SELECT course_id,course_name,course_credit FROM course WHERE course_credit=4 WITH LOCAL CHECK OPTION;     #(WHERE course_credit=4 WITH LOCAL CHECK OPTION)这里加了限定条件
INSERT INTO V_VIEW3(course_id,course_name,course_credit) VALUES(4,'红鼻子课堂',4) #所以这个插入是🆗的
INSERT INTO V_VIEW3(course_id,course_name,course_credit) VALUES(4,'绿鼻子课堂',3)  #这里就错了
#删除的方法
DROP V_VIEW1