您可以使用创建表选择语法从视图创建表。语法如下 -
create table yourtablename as selectyourcolumnname1,yourcolumnname2,yourcolumnname3,........n from yourviewname;
要运行上述查询，首先需要创建一个表，然后需要在该表上创建一个视图。之后运行查询。
首先，您需要创建一个表。创建表的查询如下 -
mysql> create table stuedntinformation -> ( -> id int, -> name varchar(100) -> );query ok, 0 rows affected (0.54 sec)
上面，我们创建了一个表。之后您需要创建一个视图。创建视图的查询如下 -
mysql> create view view_student as select id,name from stuedntinformation;query ok, 0 rows affected (0.11 sec)
现在我创建了一个名为“view_student”的视图。使用show命令检查视图。
查询如下 -
mysql> show create view view_student;
输出+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| view | create view | character_set_client | collation_connection |+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| view_student | create algorithm=undefined definer=`root`@`%` sql security definer view `view_student` as select `stuedntinformation`.`id` as `id`,`stuedntinformation`.`name` as `name` from `stuedntinformation` | utf8 | utf8_general_ci |+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.00 sec)
我们将使用上面的视图名称“view_student”来创建一个表。以下是使用视图创建表的查询 -
mysql> create table creatingtableusingviewstudent as -> select id,name from view_student;query ok, 0 rows affected (0.50 sec)records: 0 duplicates: 0 warnings: 0
现在您可以使用 show 命令检查表的 ddl。查询如下 -
mysql> show create table creatingtableusingviewstudent;
输出+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table | create table |+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| creatingtableusingviewstudent | create table `creatingtableusingviewstudent` ( `id` int(11) default null, `name` varchar(100) default null ) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci |+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
以上就是如何在 mysql 中轻松地“从视图创建表”语法？的详细内容。