前端小册

Appearance

On this page

视图 ​

创建视图 ​

连接查询，关键词 inner join

sql
mysql> select s1.number, s1.name, s1.major, s2.subject, s2.score from student_info as s1 inner join student_score as s2 where s1.number=s2.number and s1.sex="女";
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
+----------+-----------+--------------------------+-----------------------------+-------+
4 rows in set (0.11 sec)

mysql>

视图是查询语句的别名

sql
CREATE VIEW 视图名 AS 查询语句
sql
mysql> create view student_view_score as select s1.number, s1.name, s1.major, s2.subject, s2.score from student_info as s1 inner join student_score as s2 where s1.number=s2.number and s1.sex="女";
Query OK, 0 rows affected (0.10 sec)

mysql>

使用视图 ​

视图也可称为虚拟表，虚拟表中查询的列对应虚拟列

sql
mysql> select * from student_view_score;
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
+----------+-----------+--------------------------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql>
sql
mysql> select subject, avg(score) from student_view_score where score>60 group by subject having avg(score) > 75 limit 1;
+-----------------------+------------+
| subject               | avg(score) |
+-----------------------+------------+
| 母猪的产后护理        |   100.0000 |
+-----------------------+------------+
1 row in set (0.00 sec)

mysql>

也可以和真实表一起用

sql
mysql> select * from student_view_score where number in (select number from student_info where major="计算机科学与工程");
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
+----------+-----------+--------------------------+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql>

利用视图创建新视图 ​

sql
mysql> create view by_view as select number,name ,score from student_view_score;
Query OK, 0 rows affected (0.10 sec)

mysql>
sql
mysql> select * from by_view;
+----------+-----------+-------+
| number   | name      | score |
+----------+-----------+-------+
| 20180102 | 杜琦燕    |   100 |
| 20180102 | 杜琦燕    |    98 |
| 20180104 | 史珍香    |    55 |
| 20180104 | 史珍香    |    46 |
+----------+-----------+-------+
4 rows in set (0.00 sec)

mysql>

创建视图指定自定义列名 ​

sql
mysql> create view student_info_view (no, n, m) as select number,name,major from student_info;
Query OK, 0 rows affected (0.12 sec)

mysql> select no,n,m from student_info_view;
+----------+-----------+--------------------------+
| no       | n         | m                        |
+----------+-----------+--------------------------+
| 20180101 | 杜子腾    | 计算机科学与工程         |
| 20180102 | 杜琦燕    | 计算机科学与工程         |
| 20180103 | 范统      | 软件工程                 |
| 20180104 | 史珍香    | 软件工程                 |
| 20180105 | 范剑      | 飞行器设计               |
| 20180106 | 朱逸群    | 电子信息                 |
+----------+-----------+--------------------------+
6 rows in set (0.00 sec)

mysql>

如果仍旧使用与视图对应的查询语句的查询列表中的列名就会报错

sql
mysql> SELECT number, name, major FROM student_info_view;
ERROR 1054 (42S22): Unknown column 'number' in 'field list'
mysql>

查看视图 ​

查看视图 ​

sql
mysql> show tables;
+--------------------+
| Tables_in_my_test  |
+--------------------+
| by_view            |
| first_table        |
| second_table       |
| student_info       |
| student_info_view  |
| student_score      |
| student_view_score |
| t1                 |
| t2                 |
| test_table         |
| uses               |
+--------------------+
11 rows in set (0.06 sec)

mysql>

因为视图是一张虚拟表，所以新创建的视图的名称不能和当前数据库中的其他视图或者表的名称冲突！

查看视图定义 ​

sql
SHOW CREATE VIEW 视图名;
sql
mysql> show create view student_info_view\G;
*************************** 1. row ***************************
                View: student_info_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_info_view` (`no`,`n`,`m`) AS select `student_info`.`number` AS `number`,`student_info`.`name` AS `name`,`student_info`.`major` AS `major` from `student_info`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

可更新视图 ​

sql
mysql> update student_info_view set n="111" where no = 20180101;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student_info_view;
+----------+-----------+--------------------------+
| no       | n         | m                        |
+----------+-----------+--------------------------+
| 20180101 | 111       | 计算机科学与工程         |
| 20180102 | 杜琦燕    | 计算机科学与工程         |
| 20180103 | 范统      | 软件工程                 |
| 20180104 | 史珍香    | 软件工程                 |
| 20180105 | 范剑      | 飞行器设计               |
| 20180106 | 朱逸群    | 电子信息                 |
+----------+-----------+--------------------------+
6 rows in set (0.00 sec)

mysql>

不过并不是可以在所有的视图上执行更新语句的，在生成视图的时候使用了下边这些语句的都不能进行更新：

  • 聚集函数（比如SUM(), MIN(), MAX(), COUNT()等等）
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION 或者 UNION ALL
  • 某些子查询
  • 某些连接查询
  • 等等等等

删除视图 ​

sql
DROP VIEW 视图名

删除 by_view 视图

sql
mysql> drop view by_view;
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
+--------------------+
| Tables_in_my_test  |
+--------------------+
| first_table        |
| second_table       |
| student_info       |
| student_info_view  |
| student_score      |
| student_view_score |
| t1                 |
| t2                 |
| test_table         |
| uses               |
+--------------------+
10 rows in set (0.00 sec)

mysql>