MariaDB [yinzhengjie]> HELP CREATE VIEW

Name: 'CREATE VIEW'

Description:

Syntax:

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]

The CREATE VIEW statement creates a new view, or replaces an existing

one if the OR REPLACE clause is given. If the view does not exist,

CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does

exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.

The select_statement is a SELECT statement that provides the definition

of the view. (When you select from the view, you select in effect using

the SELECT statement.) select_statement can select from base tables or

other views.

The view definition is "frozen" at creation time, so changes to the

underlying tables afterward do not affect the view definition. For

example, if a view is defined as SELECT * on a table, new columns added

to the table later do not become part of the view.

The ALGORITHM clause affects how MySQL processes the view. The DEFINER

and SQL SECURITY clauses specify the security context to be used when

checking access privileges at view invocation time. The WITH CHECK

OPTION clause can be given to constrain inserts or updates to rows in

tables referenced by the view. These clauses are described later in

this section.

The CREATE VIEW statement requires the CREATE VIEW privilege for the

view, and some privilege for each column selected by the SELECT

statement. For columns used elsewhere in the SELECT statement you must

have the SELECT privilege. If the OR REPLACE clause is present, you

must also have the DROP privilege for the view. CREATE VIEW might also

require the SUPER privilege, depending on the DEFINER value, as

described later in this section.

When a view is referenced, privilege checking occurs as described later

in this section.

A view belongs to a database. By default, a new view is created in the

default database. To create the view explicitly in a given database,

specify the name as db_name.view_name when you create it:

MariaDB> CREATE VIEW test.v AS SELECT * FROM t;

Within a database, base tables and views share the same namespace, so a

base table and a view cannot have the same name.

Columns retrieved by the SELECT statement can be simple references to

table columns. They can also be expressions that use functions,

constant values, operators, and so forth.

Views must have unique column names with no duplicates, just like base

tables. By default, the names of the columns retrieved by the SELECT

statement are used for the view column names. To define explicit names

for the view columns, the optional column_list clause can be given as a

list of comma-separated identifiers. The number of names in column_list

must be the same as the number of columns retrieved by the SELECT

statement.

Unqualified table or view names in the SELECT statement are interpreted

with respect to the default database. A view can refer to tables or

views in other databases by qualifying the table or view name with the

proper database name.

A view can be created from many kinds of SELECT statements. It can

refer to base tables or other views. It can use joins, UNION, and

subqueries. The SELECT need not even refer to any tables. The following

example defines a view that selects two columns from another table, as

well as an expression calculated from those columns:

MariaDB> CREATE TABLE t (qty INT, price INT);

MariaDB> INSERT INTO t VALUES(3, 50);

MariaDB> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

MariaDB> SELECT * FROM v;

+------+-------+-------+

| qty  | price | value |

+------+-------+-------+

|    3 |    50 |   150 |

+------+-------+-------+

A view definition is subject to the following restrictions:

o The SELECT statement cannot contain a subquery in the FROM clause.

o The SELECT statement cannot refer to system or user variables.

o Within a stored program, the definition cannot refer to program

  parameters or local variables.

o The SELECT statement cannot refer to prepared statement parameters.

o Any table or view referred to in the definition must exist. However,

  after a view has been created, it is possible to drop a table or view

  that the definition refers to. In this case, use of the view results

  in an error. To check a view definition for problems of this kind,

  use the CHECK TABLE statement.

o The definition cannot refer to a TEMPORARY table, and you cannot

  create a TEMPORARY view.

o Any tables named in the view definition must exist at definition

  time.

o You cannot associate a trigger with a view.

o Aliases for column names in the SELECT statement are checked against

  the maximum column length of 64 characters (not the maximum alias

  length of 256 characters).

ORDER BY is permitted in a view definition, but it is ignored if you

select from a view using a statement that has its own ORDER BY.

For other options or clauses in the definition, they are added to the

options or clauses of the statement that references the view, but the

effect is undefined. For example, if a view definition includes a LIMIT

clause, and you select from the view using a statement that has its own

LIMIT clause, it is undefined which limit applies. This same principle

applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that

follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE,

LOCK IN SHARE MODE, and PROCEDURE.

If you create a view and then change the query processing environment

by changing system variables, that may affect the results that you get

from the view:

MariaDB> CREATE VIEW v (mycol) AS SELECT 'abc';

Query OK, 0 rows affected (0.01 sec)

MariaDB> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

MariaDB> SELECT "mycol" FROM v;

+-------+

| mycol |

+-------+

| mycol |

+-------+

1 row in set (0.01 sec)

MariaDB> SET sql_mode = 'ANSI_QUOTES';

Query OK, 0 rows affected (0.00 sec)

MariaDB> SELECT "mycol" FROM v;

+-------+

| mycol |

+-------+

| abc   |

+-------+

1 row in set (0.00 sec)

The DEFINER and SQL SECURITY clauses determine which MySQL account to

use when checking access privileges for the view when a statement is

executed that references the view. The valid SQL SECURITY

characteristic values are DEFINER and INVOKER. These indicate that the

required privileges must be held by the user who defined or invoked the

view, respectively. The default SQL SECURITY value is DEFINER.

If a user value is given for the DEFINER clause, it should be a MySQL

account specified as 'user_name'@'host_name' (the same format used in

the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default

DEFINER value is the user who executes the CREATE VIEW statement. This

is the same as specifying DEFINER = CURRENT_USER explicitly.

If you specify the DEFINER clause, these rules determine the valid

DEFINER user values:

o If you do not have the SUPER privilege, the only valid user value is

  your own account, either specified literally or by using

  CURRENT_USER. You cannot set the definer to some other account.

o If you have the SUPER privilege, you can specify any syntactically

  valid account name. If the account does not actually exist, a warning

  is generated.

o Although it is possible to create a view with a nonexistent DEFINER

  account, an error occurs when the view is referenced if the SQL

  SECURITY value is DEFINER but the definer account does not exist.

For more information about view security, see

https://mariadb.com/kb/en/stored-routine-privileges/.

Within a view definition, CURRENT_USER returns the view's DEFINER value

by default. For views defined with the SQL SECURITY INVOKER

characteristic, CURRENT_USER returns the account for the view's

invoker. For information about user auditing within views, see

http://dev.mysql.com/doc/refman/5.5/en/account-activity-auditing.html.

Within a stored routine that is defined with the SQL SECURITY DEFINER

characteristic, CURRENT_USER returns the routine's DEFINER value. This

also affects a view defined within such a routine, if the view

definition contains a DEFINER value of CURRENT_USER.

View privileges are checked like this:

o At view definition time, the view creator must have the privileges

  needed to use the top-level objects accessed by the view. For

  example, if the view definition refers to table columns, the creator

  must have some privilege for each column in the select list of the

  definition, and the SELECT privilege for each column used elsewhere

  in the definition. If the definition refers to a stored function,

  only the privileges needed to invoke the function can be checked. The

  privileges required at function invocation time can be checked only

  as it executes: For different invocations, different execution paths

  within the function might be taken.

o The user who references a view must have appropriate privileges to

  access it (SELECT to select from it, INSERT to insert into it, and so

  forth.)

o When a view has been referenced, privileges for objects accessed by

  the view are checked against the privileges held by the view DEFINER

  account or invoker, depending on whether the SQL SECURITY

  characteristic is DEFINER or INVOKER, respectively.

o If reference to a view causes execution of a stored function,

  privilege checking for statements executed within the function depend

  on whether the function SQL SECURITY characteristic is DEFINER or

  INVOKER. If the security characteristic is DEFINER, the function runs

  with the privileges of the DEFINER account. If the characteristic is

  INVOKER, the function runs with the privileges determined by the

  view's SQL SECURITY characteristic.

Example: A view might depend on a stored function, and that function

might invoke other stored routines. For example, the following view

invokes a stored function f():

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

Suppose that f() contains a statement such as this:

IF name IS NULL then

  CALL p1();

ELSE

  CALL p2();

END IF;

The privileges required for executing statements within f() need to be

checked when f() executes. This might mean that privileges are needed

for p1() or p2(), depending on the execution path within f(). Those

privileges must be checked at runtime, and the user who must possess

the privileges is determined by the SQL SECURITY values of the view v

and the function f().

The DEFINER and SQL SECURITY clauses for views are extensions to

standard SQL. In standard SQL, views are handled using the rules for

SQL SECURITY DEFINER. The standard says that the definer of the view,

which is the same as the owner of the view's schema, gets applicable

privileges on the view (for example, SELECT) and may grant them. MySQL

has no concept of a schema "owner", so MySQL adds a clause to identify

the definer. The DEFINER clause is an extension where the intent is to

have what the standard has; that is, a permanent record of who defined

the view. This is why the default DEFINER value is the account of the

view creator.

The optional ALGORITHM clause is a MySQL extension to standard SQL. It

affects how MySQL processes the view. ALGORITHM takes three values:

MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if

no ALGORITHM clause is present. For more information, see

https://mariadb.com/kb/en/view-algorithms/.

Some views are updatable. That is, you can use them in statements such

as UPDATE, DELETE, or INSERT to update the contents of the underlying

table. For a view to be updatable, there must be a one-to-one

relationship between the rows in the view and the rows in the

underlying table. There are also certain other constructs that make a

view nonupdatable.

The WITH CHECK OPTION clause can be given for an updatable view to

prevent inserts or updates to rows except those for which the WHERE

clause in the select_statement is true.

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and

CASCADED keywords determine the scope of check testing when the view is

defined in terms of another view. The LOCAL keyword restricts the CHECK

OPTION only to the view being defined. CASCADED causes the checks for

underlying views to be evaluated as well. When neither keyword is

given, the default is CASCADED.

For more information about updatable views and the WITH CHECK OPTION

clause, see

https://mariadb.com/kb/en/inserting-and-updating-with-views/.

URL: https://mariadb.com/kb/en/create-view/

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'students'\G　　　　　　　　　　#创建视图之前查看基表状态

*************************** 1. row ***************************

           Name: students

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 25

 Avg_row_length: 655

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 26

    Create_time: 2019-10-27 17:15:07

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

row in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> SHOW TABLES;

+-----------------------+

| Tables_in_yinzhengjie |

+-----------------------+

| classes               |

| coc                   |

| courses               |

| emp                   |

| scores                |

| students              |

| teachers              |

| toc                   |

+-----------------------+

8 rows in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> CREATE VIEW v_students AS SELECT stuid,name,age FROM students;　　　　#创建一个名称为"v_students"的视图

Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW TABLES;

+-----------------------+

| Tables_in_yinzhengjie |

+-----------------------+

| classes               |

| coc                   |

| courses               |

| emp                   |

| scores                |

| students              |

| teachers              |

| toc                   |

| v_students            |

+-----------------------+

9 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'v_students'\G　　　　　　#查看视图虚表的状态

*************************** 1. row ***************************

           Name: v_students

         Engine: NULL

        Version: NULL

     Row_format: NULL

           Rows: NULL

 Avg_row_length: NULL

    Data_length: NULL

Max_data_length: NULL

   Index_length: NULL

      Data_free: NULL

 Auto_increment: NULL

    Create_time: NULL

    Update_time: NULL

     Check_time: NULL

      Collation: NULL

       Checksum: NULL

 Create_options: NULL

        Comment: VIEW　　　　　　#注意，看这里的注释是一个视图哟

1 row in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> SELECT * FROM v_students;　　　　　　　　　　　　#不难发现视图就是一个"基表"通过SELECT命令查询的结果，可以将比较麻烦的SQL语句定义成视图，这样每次查询的时候就不用写一长串啦！

+-------+---------------+-----+

| stuid | name          | age |

+-------+---------------+-----+

|     1 | Shi Zhongyu   |  22 |

|     2 | Shi Potian    |  22 |

|     3 | Xie Yanke     |  53 |

|     4 | Ding Dian     |  32 |

|     5 | Yu Yutong     |  26 |

|     6 | Shi Qing      |  46 |

|     7 | Xi Ren        |  19 |

|     8 | Lin Daiyu     |  17 |

|     9 | Ren Yingying  |  20 |

|    10 | Yue Lingshan  |  19 |

|    11 | Yuan Chengzhi |  23 |

|    12 | Wen Qingqing  |  19 |

|    13 | Tian Boguang  |  33 |

|    14 | Lu Wushuang   |  17 |

|    15 | Duan Yu       |  19 |

|    16 | Xu Zhu        |  21 |

|    17 | Lin Chong     |  25 |

|    18 | Hua Rong      |  23 |

|    19 | Xue Baochai   |  18 |

|    20 | Diao Chan     |  19 |

|    21 | Huang Yueying |  22 |

|    22 | Xiao Qiao     |  20 |

|    23 | Ma Chao       |  23 |

|    24 | Xu Xian       |  27 |

|    25 | Sun Dasheng   | 100 |

+-------+---------------+-----+

rows in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SELECT stuid,name,age FROM students;

+-------+---------------+-----+

| stuid | name          | age |

+-------+---------------+-----+

|     1 | Shi Zhongyu   |  22 |

|     2 | Shi Potian    |  22 |

|     3 | Xie Yanke     |  53 |

|     4 | Ding Dian     |  32 |

|     5 | Yu Yutong     |  26 |

|     6 | Shi Qing      |  46 |

|     7 | Xi Ren        |  19 |

|     8 | Lin Daiyu     |  17 |

|     9 | Ren Yingying  |  20 |

|    10 | Yue Lingshan  |  19 |

|    11 | Yuan Chengzhi |  23 |

|    12 | Wen Qingqing  |  19 |

|    13 | Tian Boguang  |  33 |

|    14 | Lu Wushuang   |  17 |

|    15 | Duan Yu       |  19 |

|    16 | Xu Zhu        |  21 |

|    17 | Lin Chong     |  25 |

|    18 | Hua Rong      |  23 |

|    19 | Xue Baochai   |  18 |

|    20 | Diao Chan     |  19 |

|    21 | Huang Yueying |  22 |

|    22 | Xiao Qiao     |  20 |

|    23 | Ma Chao       |  23 |

|    24 | Xu Xian       |  27 |

|    25 | Sun Dasheng   | 100 |

+-------+---------------+-----+

rows in set (0.00 sec)

MariaDB [yinzhengjie]>
 

[root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/data/yinzhengjie/

total 808

-rw-rw---- 1 mysql mysql  1277 Oct 27 17:15 classes.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 classes.ibd

-rw-rw---- 1 mysql mysql   976 Oct 27 17:15 coc.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 coc.ibd

-rw-rw---- 1 mysql mysql  1251 Oct 27 17:15 courses.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 courses.ibd

-rw-rw---- 1 mysql mysql    61 Oct 27 17:15 db.opt

-rw-rw---- 1 mysql mysql   494 Oct 28 06:30 emp.frm

-rw-rw---- 1 mysql mysql 98304 Oct 28 06:39 emp.ibd

-rw-rw---- 1 mysql mysql  1001 Oct 27 17:15 scores.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 scores.ibd

-rw-rw---- 1 mysql mysql  1208 Oct 27 17:15 students.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 students.ibd

-rw-rw---- 1 mysql mysql  1298 Oct 27 17:15 teachers.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 teachers.ibd

-rw-rw---- 1 mysql mysql   973 Oct 27 17:15 toc.frm

-rw-rw---- 1 mysql mysql 98304 Oct 27 17:15 toc.ibd

-rw-rw---- 1 mysql mysql   660 Oct 28 07:40 v_students.frm

[root@node105.yinzhengjie.org.cn ~]#

[root@node105.yinzhengjie.org.cn ~]# ll /mysql/3306/data/yinzhengjie/ | grep v_students;　　　　#视图只有表结构存储文件并没有单独的数据存储文件。

-rw-rw---- 1 mysql mysql   660 Oct 28 07:40 v_students.frm　　　　　　

[root@node105.yinzhengjie.org.cn ~]# 

MariaDB [yinzhengjie]> SHOW CREATE VIEW v_students;

+------------+-----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------+----------------------+----------------------+| View       | Create View

                                                                         | character_set_client | collation_connection |+------------+-----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------+----------------------+----------------------+| v_students | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_students` AS select `students`.`StuID` AS `stui

d`,`students`.`Name` AS `name`,`students`.`Age` AS `age` from `students` | utf8mb4              | utf8mb4_general_ci   |+------------+-----------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW CREATE VIEW v_students\G

*************************** 1. row ***************************

                View: v_students

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_students` AS select `students`.`StuID` A

S `stuid`,`students`.`Name` AS `name`,`students`.`Age` AS `age` from `students`character_set_client: utf8mb4

collation_connection: utf8mb4_general_ci

1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> HELP DROP VIEW

Name: 'DROP VIEW'

Description:

Syntax:

DROP VIEW [IF EXISTS]

    view_name [, view_name] ...

    [RESTRICT | CASCADE]

DROP VIEW removes one or more views. You must have the DROP privilege

for each view. If any of the views named in the argument list do not

exist, MySQL returns an error indicating by name which nonexisting

views it was unable to drop, but it also drops all of the views in the

list that do exist.

The IF EXISTS clause prevents an error from occurring for views that

don't exist. When this clause is given, a NOTE is generated for each

nonexistent view. See [HELP SHOW WARNINGS].

RESTRICT and CASCADE, if given, are parsed and ignored.

URL: https://mariadb.com/kb/en/drop-view/

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> 

MariaDB [yinzhengjie]> SHOW TABLES;

+-----------------------+

| Tables_in_yinzhengjie |

+-----------------------+

| classes               |

| coc                   |

| courses               |

| emp                   |

| scores                |

| students              |

| teachers              |

| toc                   |

| v_students            |

+-----------------------+

9 rows in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> DROP VIEW v_students;　　　　　　#删除名为"v_students"的视图

Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW TABLES;

+-----------------------+

| Tables_in_yinzhengjie |

+-----------------------+

| classes               |

| coc                   |

| courses               |

| emp                   |

| scores                |

| students              |

| teachers              |

| toc                   |

+-----------------------+

8 rows in set (0.00 sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]>