mysql之视图&mysql连接案例&索引

一、视图

1.1 含义

虚拟表，和普通表一样使用

1.2 操作

1.2.1 创建视图

create view 视图名
as
查询语句;

1.2.2 视图的修改

方法①：create or replace view 视图名 as 查询语句;

方法②：alter view 视图名 as 查询语句;

1.2.3 删除视图

drop view 视图名,视图名,…

1.2.4 查看视图

DESC 视图名;–查看视图相关字段

SHOW CERATE VIEW 视图名;–查看视图相关语句

二、连接案例

01)查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT
	s.*,
	(CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
	(CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
	t_mysql_student s,
	( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
	( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
	s.sid = t1.sid 
	AND t1.sid = t2.sid 
	AND t1.score > t2.score	

02)查询同时存在" 01 “课程和” 02 "课程的情况

SELECT
	s.*,
	(CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
	(CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
	t_mysql_student s,
	( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
	( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 
WHERE
	s.sid = t1.sid 
	AND t1.sid=t2.sid

03）查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT
	s.*,
	( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
	( CASE WHEN t2.cid = '02' THEN t2.score END )