MySql视图

MySql视图

一、为什么使用视图

在数据库中关于数据的查询有时候非常麻烦,例如表连接、子查询等,这种查询会非常痛苦,因为逻辑比较复杂、编写语句比较多。当这种查询需要重复使用时,则不会每次都能编写正确,从而降低了数据库的实用性。

在具体操作表前,有时候要求只能操作部分字段,而不是全部字段。

为了提高复杂sql语句的复用性和表操作的安全性,mysql数据库管理系统提供 视图特性。所谓的视图,本质上是一种虚拟表,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值形式存在。行和列的数据来自自定义视图的查询所引用的基本表,并且在具体引用视图时动态生成。

视图主要关注某些特定数据和他们所查询的数据内容,这样只能看到视图中所定义的数据,而不是视图所引用表中的数据,从而提高了数据库中数据的安全性。

视图的特点如下:

l  视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系

l  视图是由基本表(实表)产生的表(虚表)

l  视图的建立和删除不影响基本表

l  对视图内容的更新(添加、删除和修改)直接影响基本表

l  当视图来自多个基本表时,不允许添加和删除数据

二、创建视图

视图的操作包括创建视图,查看视图、删除视图和修改视图。在创建视图时,首先要确保拥有CREATE VIEW的权限,并且同时确保对创建视图所引用的表也具有相应的权限。

2.1、创建视图的语法形式

         虽然视图可以被看成是一种虚拟表,但是在其物理上是不存在的,即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念可以发现其数据来源于查询语句,因此创建视图的语法为:

         CREATE VIEW VIEW_NAME AS 查询语句

         和创建表一样,视图名不能和表名、也不能和其他的视图名称重名。根据上述语法可以发现,视图的功能实际上就是封装了复杂的查询语句。

注:在SQL语句命名规范中,视图一般以view_XXX或者v_XXX的样式来命名。例子如下:

         【列子1】创建students表中年龄小于20的视图名字为s

                  MySQL [yuancheng]> create view s as select * from students where Age < 20;

Query OK, 0 rows affected (0.09 sec)

 

MySQL [yuancheng]>

         查看所有表是否有s

         MySQL [yuancheng]> show tables;

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

| Tables_in_yuancheng |

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

| classes             |

| coc                 |

| courses             |

| s                   |

| scores              |

| students            |

| teachers            |

| toc                 |

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

8 rows in set (0.03 sec)

 

MySQL [yuancheng]>

查看s表中的数据是否满足条件

         MySQL [yuancheng]> select * from s;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    12 | Wen Qingqing |  19 | F      |       1 |         2 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

|    19 | Xue Baochai  |  18 | F      |       6 |         6 |

|    20 | Diao Chan    |  19 | F      |       7 |         6 |

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

8 rows in set (0.02 sec)

 

MySQL [yuancheng]>

在上面的代码语句中创建了一个名为s的视图,通过对代码的观察可以发现,实际上sql语句里写的就是一个表查询语句,只不过是把这个查询语句封装起来重新起了一个别名,以便以后可以重复使用。

2.2、创建各种视图

         由于视图的功能实际上是封装查询语句,那么是不是任何形式的查询语句都可以封装在视图里面呢,通过实例介绍各种形式的视图。

2.2.1、创建查询常量语句的视图,即常量视图

         mysql> create view view_test1 as select 3;

Query OK, 0 rows affected (0.02 sec)

 

mysql> select * from view_test1;

+---+

| 3 |

+---+

| 3 |

+---+

1 row in set (0.02 sec)

 

mysql>

2.2.2、创建聚合函数视图

         比如 sum maxmincount

mysql> create view  view_test2  as select sum(Age) from students;

Query OK, 0 rows affected (0.03 sec)

 

mysql> select * from view_test2;

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

| sum(Age) |

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

|      670 |

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

1 row in set (0.01 sec)

 

mysql>

2.2.3、创建排序功能(order by)的查询语句功能

         mysql> create view view_test4 as select * from students where Age<=19 order by Age desc;

Query OK, 0 rows affected (0.02 sec)

 

mysql>

mysql> select * from view_test4;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    12 | Wen Qingqing |  19 | F      |       1 |         2 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

|    20 | Diao Chan    |  19 | F      |       7 |         6 |

|    19 | Xue Baochai  |  18 | F      |       6 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

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

8 rows in set (0.01 sec)

 

mysql>

2.2.4、创建内连接查询语句视图

         mysql> create view view_deal as SELECT farmer_deal_record.order_id ,order_info.create_time ,vendor.`name` ,(CASE vendor.type WHEN 1 THEN '' WHEN 2 THEN '' END),vendor.real_name,vendor.phone ,operator.`name` ,operator.phone,farmer_deal_record.farmer_id ,(CASE farmer.vendor WHEN 0 THEN '' WHEN 1 THEN '' END) ,farmer.`name` ,farmer.phone , order_info.contact_name ,order_info.contact_phone ,order_info.crop ,order_info.fee_real ,order_info.area_real,farmer_deal_record.pay_type ,farmer_deal_record.deal_type ,farmer_deal_record.deal_time ,farmer_deal_record.amount ,farmer_deal_record.fee_deal, farmer_deal_record.deduction  FROM farmer_deal_record,order_info,vendor,farmer,operator_order,operator WHERE NOT deal_type=102 AND farmer_deal_record.order_id=order_info.id AND order_info.vendor_id=vendor.id AND farmer_deal_record.farmer_id=farmer.id AND order_info.id=operator_order.order_id AND operator_order.operator_id=operator.id ORDER BY farmer_deal_record.deal_time,vendor.`name` DESC ;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

         mysql> select * from view_deal;

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

| 订单ID             | 下单时间            | 嗡服中心                                   | 嗡服中心性质       | 嗡服中心姓名       | 嗡服中心联系电话         | 飞手姓名     | 飞手联系电话       | 农户ID   | 是否为×××商       | 农户姓名     | 农户电话     | 订单联系人      | 订单联系人电话        | 农作物    | 实际费用     | 实际面积     | 支付类型     | 交易类型     | 支付时间            | 总金额    | 支付金额     | 钱包抵扣金额       |

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

| 152247404135 | 2018-03-31 13:29:26 | 中心                 | 直营               |               | 1827              |          | 1686        |       94 |                  | NULL         | 676  |               | 13312345678           |       |         0.01 |         1.00 |            1 |           99 | 2018-03-31 15:58:02 |      0.01 |         0.01 |               0.00 |

| 152248060 | 2018-03-31 16:00:42 |                  | 直营               |                | 1527              |          | 1386        |       94 |                  | NULL         | 1776  |               | 17310376676           |       |         0.01 |         0.96 |            2 |           99 | 2018-03-31 16:10:55 |      0.01 |         0.01 |               0.00 |

| 1559 | 2018-04-20 22:08:52 |                  |                |              | 145              |          | 151345        |       98 |                  | NULL         | 145  |             | 15072371345           |       |      2420.00 |       242.00 |            1 |           99 | 2018-04-20 22:28:41 |   2420.00 |      2420.00 |               0.00 |

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

2.2.5、创建外链接(LEFT JOIN RIGHT JOIN)查询语句视图

         mysql> create view view_test5 as SELECT StuID,teachers.Name  FROM students LEFT JOIN teachers ON students.TeacherID=teachers.TID;

Query OK, 0 rows affected (0.02 sec)

 

mysql>

2.2.6、创建联合查询(UNION UNION ALL)语句的视图

         mysql> create view view_test6 as select Name from students union all select Name from teachers;;

Query OK, 0 rows affected (0.03 sec)

 

mysql>

         mysql> select * from view_test6;

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

| Name           |

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

| Shi Zhongyu    |

| Shi Potian     |

| Xie Yanke      |

| Ding Dian      |

| Yu Yutong      |

| Shi Qing       |

| Xi Ren         |

| Lin Daiyu      |

| Ren Yingying   |

| Yue Lingshan   |

| Yuan Chengzhi  |

| Wen Qingqing   |

| Tian Boguang   |

| Lu Wushuang    |

| Duan Yu        |

| Xu Zhu         |

| Lin Chong      |

| Hua Rong       |

| Xue Baochai    |

| Diao Chan      |

| Huang Yueying  |

| Xiao Qiao      |

| Ma Chao        |

| Xu Xian        |

| Sun Dasheng    |

| Shi Jin        |

| Liu Bei        |

| Lu Zhi         |

| Zhu Ge Liang   |

| Huang Yue Ying |

| Zhao Yun       |

| Pang Tong      |

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

32 rows in set (0.03 sec)

 

mysql>

2.3、查看视图

         创建完视图后,经常会查询视图信息,在mysql中可以使用 SHOW TABLES SHOW TABLE STATUSSHOW CTRATE VIEW等语句查询数据,如果使用这些语句来查询,首先要确保拥有SHOW VIEW的权限。

2.3.1、使用SHOW TABLES语句查看视图名

         通过SHOW TABLES语句查看数据库中的表和视图的表

         mysql> show tables;

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

| Tables_in_yuancheng |

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

| classes             |

| coc                 |

| courses             |

| s                   |

| scores              |

| students            |

| teachers            |

| toc                 |

| view_test1          |

| view_test2          |

| view_test3          |

| view_test4          |

| view_test5          |

| view_test6          |

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

14 rows in set (0.02 sec)

 

mysql>

2.3.2、使用SHOW TABLE STATUS语句查看视图或表的详细信息

         SHOW TABLE STATUS  [LIKE TABLE_NAME];

         mysql> show table status like  'wx_order' \G;

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

           Name: wx_order

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 43

 Avg_row_length: 381

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 52

    Create_time: 2018-05-16 17:02:05

    Update_time: NULL

     Check_time: NULL

      Collation: utf8mb4_general_ci

       Checksum: NULL

 Create_options:

        Comment: 微信支付订单

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

mysql>

注:其中各个字段的含义如下:

2.3.3、使用SHOW CREATE VIEW 语句查看视图定义信息

         如果想查看关于创建视图的定义信息,可以通过SHOW CREATE VIEW 语句来实现,语法为:

         SHOW CREATE VIEW view_name

         上述参数中view_name参数为表示要查看定义信息的视图名称。

【例子1】查看view_test1的定义信息

         mysql> show create view view_test3;

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

| View       | Create View                                                                                                                                                                                                                                                                                                                                                              | character_set_client | collation_connection |

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

| view_test3 | CREATE ALGORITHM=UNDEFINED DEFINER=`zhang`@`%` SQL SECURITY DEFINER VIEW `view_test3` AS select `students`.`StuID` AS `StuID`,`students`.`Name` AS `Name`,`students`.`Age` AS `Age`,`students`.`Gender` AS `Gender`,`students`.`ClassID` AS `ClassID`,`students`.`TeacherID` AS `TeacherID` from `students` where (`students`.`Age` < 15) order by `students`.`Age` desc | utf8                 | utf8_general_ci      |

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

1 row in set (0.02 sec)

 

mysql> show create view view_test3\G;

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

                View: view_test3

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`zhang`@`%` SQL SECURITY DEFINER VIEW `view_test3` AS select `students`.`StuID` AS `StuID`,`students`.`Name` AS `Name`,`students`.`Age` AS `Age`,`students`.`Gender` AS `Gender`,`students`.`ClassID` AS `ClassID`,`students`.`TeacherID` AS `TeacherID` from `students` where (`students`.`Age` < 15) order by `students`.`Age` desc

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.02 sec)

 

ERROR:

No query specified

 

mysql>

2.3.4、使用DESC语句查看表结构

         如果想查看关于视图的表结构可以通过DESC语句来查看。

mysql> desc view_test3;

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

| Field     | Type                | Null | Key | Default | Extra |

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

| StuID     | int(10) unsigned    | NO   |     | 0       |       |

| Name      | varchar(50)         | NO   |     | NULL    |       |

| Age       | tinyint(3) unsigned | NO   |     | NULL    |       |

| Gender    | enum('F','M')       | NO   |     | NULL    |       |

| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |       |

| TeacherID | int(10) unsigned    | YES  |     | NULL    |       |

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

6 rows in set (0.02 sec)

 

mysql>

2.3.5、通过系统表查看视图信息

         mysql数据库安装成功后,会自动创建系统数据库information_schema,在该数据库中存在一个包含视图信息的表views,可以通过查看表views来查看相关信息。如下:

         mysql> use information_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql>

mysql> select * from views where table_name='view_test3'\G;

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

       TABLE_CATALOG: def

        TABLE_SCHEMA: yuancheng

          TABLE_NAME: view_test3

     VIEW_DEFINITION: select `yuancheng`.`students`.`StuID` AS `StuID`,`yuancheng`.`students`.`Name` AS `Name`,`yuancheng`.`students`.`Age` AS `Age`,`yuancheng`.`students`.`Gender` AS `Gender`,`yuancheng`.`students`.`ClassID` AS `ClassID`,`yuancheng`.`students`.`TeacherID` AS `TeacherID` from `yuancheng`.`students` where (`yuancheng`.`students`.`Age` < 15) order by `yuancheng`.`students`.`Age` desc

        CHECK_OPTION: NONE

        IS_UPDATABLE: YES

             DEFINER: zhang@%

       SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

1 row in set (0.03 sec)

 

ERROR:

No query specified

 

mysql>

2.4、删除视图

         视图的操作包括创建视图、查看视图、删除视图和修改视图。在删除视图时,要确保拥有DROP VIEW 的权限。

2.4.1、删除视图

         在删除视图时课堂通过DROP VIEW语句删除一个或者多个视图,语法格式如下:

         DROP VIEW View_name [,View_name]….

         在上述语句中,view_name参数是要删除视图的名称

【列子1】删除view_test1视图

         mysql> show tables;

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

| Tables_in_yuancheng |

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

| classes             |

| coc                 |

| courses             |

| s                   |

| scores              |

| students            |

| teachers            |

| toc                 |

| view_test1          |

| view_test2          |

| view_test3          |

| view_test4          |

| view_test5          |

| view_test6          |

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

14 rows in set (0.02 sec)

 

mysql> drop view view_test1;

Query OK, 0 rows affected (0.02 sec)

 

mysql> show tables;

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

| Tables_in_yuancheng |

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

| classes             |

| coc                 |

| courses             |

| s                   |

| scores              |

| students            |

| teachers            |

| toc                 |

| view_test2          |

| view_test3          |

| view_test4          |

| view_test5          |

| view_test6          |

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

13 rows in set (0.02 sec)

 

mysql>

【例子2】删除多个视图

         mysql> drop view s,view_test4,view_test3;

Query OK, 0 rows affected (0.02 sec)

 

mysql>

         mysql> show tables;

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

| Tables_in_yuancheng |

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

| classes             |

| coc                 |

| courses             |

| scores              |

| students            |

| teachers            |

| toc                 |

| view_test2          |

| view_test5          |

| view_test6          |

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

10 rows in set (0.02 sec)

 

mysql>

2.5、修改视图

         对于已经已经创建好的视图,在使用过一段时间之后,如果需要进行一些表结构上的修改,即视图的修改,除了可以使用ALTER语句实现修改视图之外还可以通过CREATE OR REPLACE VIEW语句来修改视图。

         对于已经创建好的表,尤其是已经有大量数据的表。通过先删除,然后按照新的表定义重建表的方式来修改表时,需要做许多额外的工作,比如数据的重新加载等,对于视图来说,本身就是一张虚表,并没有实质性存储数据,所有可以通过该方式来修改视图。

2.5.1CREATE OR REPLACE VIEW 语句修改视图

l  创建视图view_1

mysql> create view view_1 as select students.StuID,students.Name,students.Age from students,teachers where students.TeacherID=teachers.TID and students.Age<20;

Query OK, 0 rows affected (0.01 sec)

mysql>

l  查看视图内容

mysql> select * from view_1;

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

| StuID | Name         | Age |

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

|    12 | Wen Qingqing |  19 |

|     7 | Xi Ren       |  19 |

|     8 | Lin Daiyu    |  17 |

|    10 | Yue Lingshan |  19 |

|    14 | Lu Wushuang  |  17 |

|    15 | Duan Yu      |  19 |

|    19 | Xue Baochai  |  18 |

|    20 | Diao Chan    |  19 |

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

8 rows in set (0.01 sec)

 

mysql>

l  修改视图

mysql> create  or replace  view view_1 as select students.StuID,students.Name,students.Age,students.ClassID,teachers.name as Tname  from students,teachers where students.TeacherID=teachers.TID and students.Age<<20;

Query OK, 0 rows affected (0.02 sec)

mysql>

l  查看修改后的视图内容

mysql> select * from view_1;                                                                                                                                                                                    

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

| StuID | Name         | Age | ClassID | Tname     |

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

|    12 | Wen Qingqing |  19 |       1 | Lu Zhi    |

|     7 | Xi Ren       |  19 |       3 | Pang Tong |

|     8 | Lin Daiyu    |  17 |       7 | Pang Tong |

|    10 | Yue Lingshan |  19 |       3 | Pang Tong |

|    14 | Lu Wushuang  |  17 |       3 | Pang Tong |

|    15 | Duan Yu      |  19 |       4 | Pang Tong |

|    19 | Xue Baochai  |  18 |       6 | Pang Tong |

|    20 | Diao Chan    |  19 |       7 | Pang Tong |

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

8 rows in set (0.01 sec)

 

mysql>

2.5.2ALTER语句修改视图

使用ALTER语句来修改视图,其语法格式如下:

                  ALTER VIEW View_Name  AS 查询语句

【例子1】通过2.5.1上面的视图添加查询字段名称

                  mysql> alter view view_1 as select students.StuID,students.Name,students.Age,students.ClassID,teachers.name as Tname,teachers.Age as TAge,teachers.Gender as TGender  from students,teachers where students.TeachherID=teachers.TID and students.Age<20;

Query OK, 0 rows affected (0.02 sec)

 

mysql> select * from view_1;                                                                                                                                                                                    

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

| StuID | Name         | Age | ClassID | Tname     | TAge | TGender |

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

|    12 | Wen Qingqing |  19 |       1 | Lu Zhi    |   30 | F       |

|     7 | Xi Ren       |  19 |       3 | Pang Tong |   44 | F       |

|     8 | Lin Daiyu    |  17 |       7 | Pang Tong |   44 | F       |

|    10 | Yue Lingshan |  19 |       3 | Pang Tong |   44 | F       |

|    14 | Lu Wushuang  |  17 |       3 | Pang Tong |   44 | F       |

|    15 | Duan Yu      |  19 |       4 | Pang Tong |   44 | F       |

|    19 | Xue Baochai  |  18 |       6 | Pang Tong |   44 | F       |

|    20 | Diao Chan    |  19 |       7 | Pang Tong |   44 | F       |

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

8 rows in set (0.01 sec)

 

mysql>

2.6、利用视图操作基本表

         mysql中可以通过视图检索(查询)基本表数据,除此之外还可以通过视图修改基本表中的数据。

2.6.1、查询数据

         通过视图查询数据,与通过表查询数据完全相同,通过视图查询比表更安全,简单和实用。只需在查询时把表名换成视图名称即可

         【例子1】查询视图信息

                  mysql> select * from view_1;                                                                  

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

| StuID | Name         | Age | ClassID | Tname     | TAge | TGender |

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

|    12 | Wen Qingqing |  19 |       1 | Lu Zhi    |   30 | F       |

|     7 | Xi Ren       |  19 |       3 | Pang Tong |   44 | F       |

|     8 | Lin Daiyu    |  17 |       7 | Pang Tong |   44 | F       |

|    10 | Yue Lingshan |  19 |       3 | Pang Tong |   44 | F       |

|    14 | Lu Wushuang  |  17 |       3 | Pang Tong |   44 | F       |

|    15 | Duan Yu      |  19 |       4 | Pang Tong |   44 | F       |

|    19 | Xue Baochai  |  18 |       6 | Pang Tong |   44 | F       |

|    20 | Diao Chan    |  19 |       7 | Pang Tong |   44 | F       |

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

8 rows in set (0.01 sec)

 

mysql>

2.6.2、利用视图操作基本表数据

         通过创建的视图不仅可以对视图进行查询数据,而且还可以对视图进行更新(增加、删除和更新)数据操作。由于视图是虚表,所以对视图数据进行的更新操作,实际上是对其基本表数据进行更新操作。在具体更新视图数据时,需要注意以下两点:

l  对视图数据进行添加、删除和更新操作直接影响基本表。

l  视图来自多个基本表时,不允许添加和删除数据。

2.6.2.1、添加数据

         mysql> create view view_1 as select * from students where Age < 20;

Query OK, 0 rows affected (0.02 sec)

 

mysql> select * from view_1;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    12 | Wen Qingqing |  19 | F      |       1 |         2 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

|    19 | Xue Baochai  |  18 | F      |       6 |         6 |

|    20 | Diao Chan    |  19 | F      |       7 |         6 |

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

8 rows in set (0.02 sec)

 

mysql>

         mysql> insert into view_1 (StuID,Name,Age,Gender,ClassID,TeacherID) values(27,'Zhang Wang',10,''F',3,2);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from view_1;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    12 | Wen Qingqing |  19 | F      |       1 |         2 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

|    19 | Xue Baochai  |  18 | F      |       6 |         6 |

|    20 | Diao Chan    |  19 | F      |       7 |         6 |

|    27 | Zhang Wang   |  10 | F      |       3 |         2 |

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

9 rows in set (0.02 sec)

 

mysql>

注意:VALUSE后的数据常量与视图中的列要一一对应

2.6.2.2、删除数据操作

         mysql> select * from view_1;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    12 | Wen Qingqing |  19 | F      |       1 |         2 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

|    19 | Xue Baochai  |  18 | F      |       6 |         6 |

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

7 rows in set (0.01 sec)

 

mysql> delete from view_1 where StuID=19 or StuID=12;

Query OK, 2 rows affected (0.02 sec)

 

mysql> select * from view_1;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  19 | M      |       4 |         6 |

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

5 rows in set (0.01 sec)

 

mysql>

2.6.2.3、更新数据

         mysql> update view_1 set Age='15' where Name='Duan Yu' and Age=19;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from view_1;

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

| StuID | Name         | Age | Gender | ClassID | TeacherID |

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

|     7 | Xi Ren       |  19 | F      |       3 |         6 |

|     8 | Lin Daiyu    |  17 | F      |       7 |         6 |

|    10 | Yue Lingshan |  19 | F      |       3 |         6 |

|    14 | Lu Wushuang  |  17 | F      |       3 |         6 |

|    15 | Duan Yu      |  15 | M      |       4 |         6 |

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

5 rows in set (0.01 sec)

 

mysql>

相关文章
相关标签/搜索