Database: MySQL built-in features-view

a View

A view is a virtual table (not a real one), which is essentially "getting a dynamic dataset from an SQL statement and naming it", which you can use as a table by simply using "name" to get a result set.

Using views we can take out the temporary table in the query process, and use the view to implement, so that later on to manipulate the temporary table data without rewriting the complex SQL, directly to the view to find, but the view has obvious efficiency problems, and the view is stored in the database, If the SQL used in our program relies too much on the view in the database, which is strongly coupled, it means that it is very inconvenient to extend SQL, so it is not recommended to use

#两张有关系的表mysql > select * from course;+-----+--------+------------+| CID | CNAME  | teacher_id |+-----+--------+------------+|   1 | Biology |          1 | |   2 | Sports |          1 | |   3 | Physics |          2 | |   4 | language |          3 | |   5 | Math |          4 | |   6 | English |          5 | |   7 | Geography |          2 |+-----+--------+------------+7 rows in Set (0.00 sec) mysql> SELECT * FROM teacher;+-----+-----------+| Tid | Tname     |+-----+-----------+|   1 | Zhang San    | |   2 | John Doe    | |   3 | Harry    | |   4 | Egon      | |   5 | Zhang Mowgli |+-----+-----------+5 rows in Set (0.07 sec) # query Professor Zhang San's course name mysql> Select CNAME from course where teacher_id = (select t ID from teacher where tname= ' Zhang San '); +--------+| CNAME  |+--------+| Biology | | Sports |+--------+2 rows in Set (0.00 sec) # sub-query out temporary table, as teacher_id, etc. judging by select Tid from teacher whe Re Tname= ' Zhang San '

　　

Create a View

# syntax: CREATE VIEW name as SQL statement mysql> CREATE VIEW Teacher_view as select Tid from teacher where tname = ' Zhang San '; Query OK, 0 rows affected (0.15 sec) # So SQL that queries Zhang San Professor's course name can be rewritten as mysql> select CNAME from course where teacher_id = (select t ID from Teacher_view); +--------+| CNAME  |+--------+| Biology | | Sports |+--------+ Note Note: #1. You don't have to rewrite the SQL for subqueries every time you use a view, but it's not as efficient as we write subqueries. And there is a fatal problem: The view is stored in the database, if the SQL in our program is too dependent on the view stored in the database, it means that once the SQL needs to be modified and related to the part of the view, you must go to the database to modify, and usually in the company database has a dedicated DBA responsible for You have to pay a lot of communication costs if you want to complete the change. The DBA may be able to help you with the modification, which is extremely inconvenient.

　　

Two use views

Mysql> SELECT * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | biology | 1 |
| 2 | sports | 1 |
| 3 | physics | 2 |
| 4 | chinese | 3 |
| 5 | math | 4 |
| 6 | english | 5 |
| 7 | geography | 2 |
+-----+--------+------------+

Mysql> CREATE VIEW Course_view as SELECT * from course; Query OK, 0 rows affected (0.05 sec) mysql> select * FROM course_view;+-----+--------+------------+| CID | CNAME |   teacher_id |+-----+--------+------------+| 1 |          Biology |   1 | | 2 |          Sports |   1 | | 3 |          Physics |   2 | | 4 |          language |   3 | | 5 |          Math |   4 | | 6 |          English |   5 | | 7 |          Geography | 2 |+-----+--------+------------+7 rows in Set (0.00 sec) mysql> Update course_view Set cname = ' chemistry ' where cname = ' creature '; Q Uery OK, 1 row affected (0.08 sec) Rows matched:1 changed:1 warnings:0mysql> insert INTO Course_view values (8, ' history ',  3);   # Insert data into view query OK, 1 row affected (0.02 sec) mysql> select * from course; # The records of the original table have also been modified with the following changes +-----+--------+------------+| CID | CNAME |   teacher_id |+-----+--------+------------+| 1 |          Chemistry |   1 | | 2 |          Sports |   1 | | 3 |          Physics |   2 | | 4 |          language |   3 | | 5 |          Math |   4 | | 6 |          English |   5 | | 7 |          Geography | 2 | |   8 |          History | 3 |+-----+--------+------------+8 rows in Set (0.00 sec)

　　

three-Modified view

Syntax: ALTER VIEW name as SQL statement mysql> ALTER view Teacher_view as SELECT * from course where cid>3; Query OK, 0 rows affected (0.04 sec) mysql> select * FROM teacher_view;+-----+-------+------------+| CID | CNAME | teacher_id |+-----+-------+------------+|   4 | XXX   |          2 | |   5 | YYY   |          2 |+-----+-------+------------+rows in Set (0.00 sec)

　　

Four Deleting a view

Syntax: Drop view Name drop view Teacher_view

　　

Database: MySQL built-in features-view