MySql总结之视图部分,MySql可以利用复杂查询来进行多表查询,其实它还提供了一种非常方便的方法,那就是视图,可以从单个数据表中筛选部分数据,也可以从多个数据表中筛选数据,然后整合。
/*************************视图************************************************/
功能类似于多表查询,可以将一个或多个表中的数据挑选出来独立成表
一、创建视图
1、单源表视图的创建
mysql> select * from studentInfo;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 2 | 小红 | 82.00 | 数学 | 王老师 |
| 3 | 小张 | 83.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
6 rows in set (0.00 sec)
mysql> create view hjf.view_score
-> AS(SELECT SI.id, SI.name, SI.score, SI.subject, SI.teacher
-> from studentInfo SI
-> where SI.score >= 85);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_score;
+----+--------+-------+-----------+-----------+
| id | name | score | subject | teacher |
+----+--------+-------+-----------+-----------+
| 1 | 小明 | 87.00 | 数学 | 王老师 |
| 4 | 小韩 | 88.00 | 数学 | 王老师 |
| 5 | 小胡 | 88.00 | 计算机 | 张老师 |
| 6 | 小胡 | 88.00 | 数据库 | 张老师 |
+----+--------+-------+-----------+-----------+
4 rows in set (0.00 sec)
2、多源表视图的创建
mysql> create view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname, teacherInfo.teachername
-> from newInfo, subjectInfo, teacherInfo
-> where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+-------------+
| id | name | score | subjectname | teachername |
+----+--------+-------+-------------+-------------+
| 1 | 小明 | 78.00 | 数学 | 张老师 |
| 2 | 小红 | 87.00 | 英语 | 王老师 |
| 3 | 小李 | 67.00 | 语文 | 吴老师 |
+----+--------+-------+-------------+-------------+
3 rows in set (0.02 sec)
二、修改视图
mysql> create or replace view hjf.view_test
-> AS(select newInfo.id, newInfo.name, newInfo.score, subjectInfo.subjectname
-> from newInfo, subjectInfo
-> where newInfo.subjectid = subjectInfo.id);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from view_test;
+----+--------+-------+-------------+
| id | name | score | subjectname |
+----+--------+-------+-------------+
| 3 | 小李 | 67.00 | 语文 |
| 1 | 小明 | 78.00 | 数学 |
| 2 | 小红 | 87.00 | 英语 |
+----+--------+-------+-------------+
3 rows in set (0.00 sec)
三、删除视图
mysql> drop view view_test;
Query OK, 0 rows affected (0.00 sec)
四、查看视图
1、查看视图的字段信息
mysql> describe(desc) view_test;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| score | decimal(4,2) | YES | | NULL | |
| subjectname | varchar(30) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2、查看创建视图的语句 \G 格式化显示结果
mysql> show create view view_test\G;
*************************** 1. row ***************************
View: view_test
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS (select `newInfo`.`id` AS `id`,`newInfo`.`name` AS `name`,`newInfo`.`score` AS `score`,`subjectInfo`.`subjectname` AS `subjectname` from (`newInfo` join `subjectInfo`) where (`newInfo`.`subjectid` = `subjectInfo`.`id`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
/*****************************************************************************/