How to create views in MySQL
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!
