Linux command: six of MySQL series-- VIEW view / mysql-e
Introduction to the View VIEW command:
The VIEW view is the stored SELECT statement data
1. Create view command format:
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = {user | CURRENT_USER}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
Format: CREATE VIEW view_name AS select_statement creates a file named view_name
The view, the content of the view, is the content queried by the select statement.
Usage: CREATE VIEW sct AS SELECT Name,Cname,Tname FROM students,courses,teachers WHERE
Students.CID1=courses.CID AND courses.CID=teachers.TID
Store the CID equivalent data in the query students,courses,teachers table in the sct view.
2. Command function:
The SELECT data of the database is stored as a view, and the view is also equivalent to a virtual table. The table in the SELECT statement stored in the view is called the base table. The view can not be modified, but can only be modified based on the base table. Therefore, it is generally not recommended that MySQL create views.
3. Command parameters:
DISTING unique, non-repetitive
SELECT [DISTINCT] * FROM tb_name WHERE qualification
Select a unique, non-repeating entry that meets the criteria from the tb_ name table.
4. Example
1. See how the created table is created
Mysql > SHOW CREATE TABLE class\ G
* * 1. Row *
Table: class
Create Table: CREATE TABLE `class` (
`ID`int (11) NOT NULL AUTO_INCREMENT
`Name` char (20) NOT NULL
`Age` tinyint (4) NOT NULL
`Gender` varchar (10) NOT NULL
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
ERROR:
No query specified
5. Directly do not log in to the mysql client to modify the data in the mysql database
[root@lamp] # mysql-e 'CREATE DATABASE edb;' directly set up database edb
[root@lamp] # mysql-e 'SHOW DATABASES;'
+-+
| | Database |
+-+
| | information_schema |
| | edb |
| | hellodb |
| | mydb |
| | mysql |
| | performance_schema |
| | students |
| | test |
| | testdb |
+-+
[root@lamp ~] # mysql-e 'DROP DATABASE edb;' directly delete edb database
[root@lamp] # mysql-e 'SHOW DATABASES;'
+-+
| | Database |
+-+
| | information_schema |
| | hellodb |
| | mydb |
| | mysql |
| | performance_schema |
| | students |
| | test |
| | testdb |
+-+
[root@lamp ~] # mysql-e 'SELECT * FROM students.class;'# does not log in to the mysql client, but directly queries the data in the class table in the students database.
+-- +
| | ID | Name | Age | Gender | |
+-- +
| | 1 | Yang Guo | 22 |
| | 2 | Guo Jing | 46 |
| | 3 | Xiao Longnv | 18 |
| | 4 | Huang Rong | 40 |
+-- +
