Shulou(Shulou.com)05/31 Report--

How to create a view in MySQL, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

MariaDB [test] > CREATE TABLE t (qty INT, price INT)

Query OK, 0 rows affected (0.15 sec)

MariaDB [test] > INSERT INTO t VALUES (3,50)

Query OK, 1 row affected (0.01sec)

-create a view

MariaDB [test] > CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t

Query OK, 0 rows affected (0.09 sec)

MariaDB [test] > SELECT * FROM v

+-+

| | qty | price | value | |

+-+

| | 3 | 50 | 150 | |

+-+

1 row in set (0.00 sec)

MariaDB [test] > explain select * from v

+-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+

| | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 1 |

+-+

1 row in set (0.00 sec)

-- View view statu

MariaDB [test] > show table status from test like 'v'\ G

* * 1. Row *

Name: v

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)

-- View the creation statement

MariaDB [test] > show create view v\ G

* * 1. Row *

View: v

Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `v`AS select `t`.`qty` AS `qty`, `t`.`price` AS `price`, (`t`.`qty` * `t`.`price`) AS `value`from `t`

Character_set_client: utf8

Collation_connection: utf8_general_ci

MariaDB [test] > select * from information_schema.views where table_name ='v'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: v

VIEW_DEFINITION: select `test`.`t`.`qty`AS `qty`, `test`.`t`.`price`AS `price`, (`test`.`t`.`qty` * `test`.`t`.`price`) AS `value`from `test`.`t`

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

ALGORITHM: UNDEFINED

1 row in set (0.02 sec)

After reading the above, have you mastered how to create views in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.