Database: MySQL built-in features-view

Source: Internet
Author: User
Tags dba

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.