038.视图 view

  • 1,007

视图:view

view又称为虚拟表,view是sql的查询结果

有什么用?

1:权限控制时可以用

答:比如,某几个列,允许用户查询,其他列不允许。

可以通过视图,开放其中一列或者几列,知道权限控制的作用。

2:简化复杂的查询

答:查询每个栏目下商品的平均价格,并按平均价格排序,查询出平均前三高的栏目

3:视图能不能 更新,删除,添加?

答:如果视图的每一行,是与物理表一一对应,则可以

view的行是由物理表多行经过计算得到的结果,view是不可以更新的!!!

    mysql> show tables;
    mysql>#获取表单
    +----------------+
    | Tables_in_test |
    +----------------+
    | a              |
    | b              |
    | boy            |
    | category       |
    | girl           |
    | goods          |
    | m              |
    | mian           |
    | result         |
    | t              |
    | t10            |
    | t11            |
    | t12            |
    | t13            |
    | t14            |
    | t15            |
    | t2             |
    | t3             |
    | t4             |
    | t5             |
    | t6             |
    | t7             |
    | t8             |
    | t9             |
    | tmp            |
    | user           |
    +----------------+
    26 rows in set (0.00 sec)
    mysql> select goods_id,goods_name,(market_price-shop_price) as sheng from goods;
    mysql>#获取节省的市场价
    +----------+----------------------------------------+----------+
    | goods_id | goods_name                             | sheng    |
    +----------+----------------------------------------+----------+
    |        1 | kd876                                  |   277.60 |
    |        4 | 璇哄熀浜歯85鍘熻鍏呯數鍣?                   |    11.60 |
    |        3 | 璇哄熀浜氬師瑁?800鑰虫満                     |    13.60 |
    |        5 | 绱㈢埍鍘熻m2鍗¤鍗″櫒                     |     4.00 |
    |        6 | 鑳滃垱kingmax鍐呭瓨鍗?                     |     8.40 |
    |        7 | 璇哄熀浜歯85鍘熻绔嬩綋澹拌€虫満hs-82           |    20.00 |
    |        8 | 椋炲埄娴?@9v                             |    79.79 |
    |        9 | 璇哄熀浜歟66                              |   459.60 |
    |       10 | 绱㈢埍c702c                              |   265.60 |
    |       11 | 绱㈢埍c702c                              | -1300.00 |
    |       12 | 鎽╂墭缃楁媺a810                           |   196.60 |
    |       13 | 璇哄熀浜?320 xpressmusic                 |   262.20 |
    |       14 | 璇哄熀浜?800xm                           |   525.00 |
    |       15 | 鎽╂墭缃楁媺a810                           |   157.60 |
    |       16 | 鎭掑熀浼熶笟g101                           |   164.67 |
    |       17 | 澶忔柊n7                                 |   460.00 |
    |       18 | 澶忔柊t5                                 |   575.60 |
    |       19 | 涓夋槦sgh-f258                           |   171.60 |
    |       20 | 涓夋槦bc01                               |    56.00 |
    |       21 | 閲戠珛 a30                               |   400.00 |
    |       22 | 澶氭櫘杈総ouch hd                         |  1199.80 |
    |       23 | 璇哄熀浜歯96                              |   740.00 |
    |       24 | p806                                   |   400.00 |
    |       25 | 灏忕伒閫?鍥鸿瘽50鍏冨厖鍊煎崱                  |     9.59 |
    |       26 | 灏忕伒閫?鍥鸿瘽20鍏冨厖鍊煎崱                  |     3.80 |
    |       27 | 鑱旈€?00鍏冨厖鍊煎崱                        |     5.00 |
    |       28 | 鑱旈€?0鍏冨厖鍊煎崱                         |     5.00 |
    |       29 | 绉诲姩100鍏冨厖鍊煎崱                        |   -90.00 |
    |       30 | 绉诲姩20鍏冨厖鍊煎崱                         |     3.00 |
    |       31 | 鎽╂墭缃楁媺e8                             |   267.39 |
    |       32 | 璇哄熀浜歯85                              |   602.00 |
    +----------+----------------------------------------+----------+
    31 rows in set (0.07 sec)
    mysql>#编码修改为gbk
    mysql> set names gbk;
    Query OK, 0 rows affected (0.00 sec)
    mysql>#把获取节省的价格表创建一个         vgoods视图
    mysql> create view vgoods as select goods_id,goods_name,(market_price-shop_price) as sheng from goods;
    Query OK, 0 rows affected (0.07 sec)
    mysql>#获取vgoods视图!
    mysql> select * from vgoods;
    +----------+------------------------------+----------+
    | goods_id | goods_name                   | sheng    |
    +----------+------------------------------+----------+
    |        1 | kd876                        |   277.60 |
    |        4 | 诺基亚n85原装充电器                       |    11.60 |
    |        3 | 诺基亚原装5800耳机                      |    13.60 |
    |        5 | 索爱原装m2卡读卡器                         |     4.00 |
    |        6 | 胜创kingmax内存卡                  |     8.40 |
    |        7 | 诺基亚n85原装立体声耳机hs-82                  |    20.00 |
    |        8 | 飞利浦9@9v                         |    79.79 |
    |        9 | 诺基亚e66                         |   459.60 |
    |       10 | 索爱c702c                        |   265.60 |
    |       11 | 索爱c702c                        | -1300.00 |
    |       12 | 摩托罗拉a810                        |   196.60 |
    |       13 | 诺基亚5320 xpressmusic            |   262.20 |
    |       14 | 诺基亚5800xm                      |   525.00 |
    |       15 | 摩托罗拉a810                        |   157.60 |
    |       16 | 恒基伟业g101                       |   164.67 |
    |       17 | 夏新n7                           |   460.00 |
    |       18 | 夏新t5                           |   575.60 |
    |       19 | 三星sgh-f258                     |   171.60 |
    |       20 | 三星bc01                         |    56.00 |
    |       21 | 金立 a30                         |   400.00 |
    |       22 | 多普达touch hd                    |  1199.80 |
    |       23 | 诺基亚n96                         |   740.00 |
    |       24 | p806                         |   400.00 |
    |       25 | 小灵通/固话50元充值卡                     |     9.59 |
    |       26 | 小灵通/固话20元充值卡                     |     3.80 |
    |       27 | 联通100元充值卡                       |     5.00 |
    |       28 | 联通50元充值卡                        |     5.00 |
    |       29 | 移动100元充值卡                       |   -90.00 |
    |       30 | 移动20元充值卡                        |     3.00 |
    |       31 | 摩托罗拉e8                          |   267.39 |
    |       32 | 诺基亚n85                         |   602.00 |
    +----------+------------------------------+----------+
    31 rows in set (0.02 sec)
    mysql>#创建v2goods视图!获取goods_id,goods_name,sheng表
    mysql> create view v2goods as select goods_id,goods_name,shop_price as sheng from goods;
    Query OK, 0 rows affected (0.18 sec)
    mysql>#打印v2goods视图
    mysql> select * from v2goods;
    +----------+------------------------------+---------+
    | goods_id | goods_name                   | sheng   |
    +----------+------------------------------+---------+
    |        1 | kd876                        | 1388.00 |
    |        4 | 诺基亚n85原装充电器                       |   58.00 |
    |        3 | 诺基亚原装5800耳机                      |   68.00 |
    |        5 | 索爱原装m2卡读卡器                         |   20.00 |
    |        6 | 胜创kingmax内存卡                  |   42.00 |
    |        7 | 诺基亚n85原装立体声耳机hs-82                  |  100.00 |
    |        8 | 飞利浦9@9v                         |  399.00 |
    |        9 | 诺基亚e66                         | 2298.00 |
    |       10 | 索爱c702c                        | 1328.00 |
    |       11 | 索爱c702c                        | 1300.00 |
    |       12 | 摩托罗拉a810                        |  983.00 |
    |       13 | 诺基亚5320 xpressmusic            | 1311.00 |
    |       14 | 诺基亚5800xm                      | 2625.00 |
    |       15 | 摩托罗拉a810                        |  788.00 |
    |       16 | 恒基伟业g101                       |  823.33 |
    |       17 | 夏新n7                           | 2300.00 |
    |       18 | 夏新t5                           | 2878.00 |
    |       19 | 三星sgh-f258                     |  858.00 |
    |       20 | 三星bc01                         |  280.00 |
    |       21 | 金立 a30                         | 2000.00 |
    |       22 | 多普达touch hd                    | 5999.00 |
    |       23 | 诺基亚n96                         | 3700.00 |
    |       24 | p806                         | 2000.00 |
    |       25 | 小灵通/固话50元充值卡                     |   48.00 |
    |       26 | 小灵通/固话20元充值卡                     |   19.00 |
    |       27 | 联通100元充值卡                       |   95.00 |
    |       28 | 联通50元充值卡                        |   45.00 |
    |       29 | 移动100元充值卡                       |   90.00 |
    |       30 | 移动20元充值卡                        |   18.00 |
    |       31 | 摩托罗拉e8                          | 1337.00 |
    |       32 | 诺基亚n85                         | 3010.00 |
    +----------+------------------------------+---------+
    31 rows in set (0.00 sec)
    mysql>#取cat_id的  pj的平均值
    mysql> select cat_id,avg(shop_price) as pj from goods group by cat_id;
    +--------+-------------+
    | cat_id | pj          |
    +--------+-------------+
    |      2 |  823.330000 |
    |      3 | 1746.066667 |
    |      4 | 2297.000000 |
    |      5 | 3700.000000 |
    |      8 |   75.333333 |
    |     11 |   31.000000 |
    |     13 |   33.500000 |
    |     14 |   54.000000 |
    |     15 |   70.000000 |
    +--------+-------------+
    9 rows in set (0.00 sec)
    mysql>#创建v3视图(取cat_id的  pj的平均值)
    mysql> create view v3 as select cat_id,avg(shop_price) as pj from goods group by cat_id;
    Query OK, 0 rows affected (0.10 sec)
    mysql> select * from v3;
    +--------+-------------+
    | cat_id | pj          |
    +--------+-------------+
    |      2 |  823.330000 |
    |      3 | 1746.066667 |
    |      4 | 2297.000000 |
    |      5 | 3700.000000 |
    |      8 |   75.333333 |
    |     11 |   31.000000 |
    |     13 |   33.500000 |
    |     14 |   54.000000 |
    |     15 |   70.000000 |
    +--------+-------------+
    9 rows in set (0.00 sec)
    mysql>#分组查询0-3的平均值
    mysql> select * from v3 order by pj limit 0,3;
    +--------+-----------+
    | cat_id | pj        |
    +--------+-----------+
    |     11 | 31.000000 |
    |     13 | 33.500000 |
    |     14 | 54.000000 |
    +--------+-----------+
    3 rows in set (0.00 sec)
    mysql>#分组查询0-3的平均值  降序
    mysql> select * from v3 order by pj desc limit 0,3;
    +--------+-------------+
    | cat_id | pj          |
    +--------+-------------+
    |      5 | 3700.000000 |
    |      4 | 2297.000000 |
    |      3 | 1746.066667 |
    +--------+-------------+
    3 rows in set (0.00 sec)
    mysql> #视图能不能   更新 删除 添加?
    mysql>#获取use表
    mysql> select * from user;
    +-----+-------+-----+
    | uid | name  | age |
    +-----+-------+-----+
    |   1 | lisi  |  89 |
    |   3 | lilei |  19 |
    |   2 | luky  |  18 |
    +-----+-------+-----+
    3 rows in set (0.03 sec)
    mysql>#创建vuser视图()
    mysql> create view vuser as select uid,name from user;
    Query OK, 0 rows affected (0.07 sec)
    mysql>#查询vuser表
    mysql> select * from vuser;
    +-----+-------+
    | uid | name  |
    +-----+-------+
    |   1 | lisi  |
    |   3 | lilei |
    |   2 | luky  |
    +-----+-------+
    3 rows in set (0.00 sec)
    mysql>#修改vuser表  (id3 修改为 lidalei)
    mysql> update vuser set name='lidalei' where uid=3;
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql>#查询vuser表
    mysql> select * from vuser;
    +-----+---------+
    | uid | name    |
    +-----+---------+
    |   1 | lisi    |
    |   3 | lidalei |
    |   2 | luky    |
    +-----+---------+
    3 rows in set (0.00 sec)
    mysql>#查询user表
    mysql> select * from user;
    +-----+---------+-----+
    | uid | name    | age |
    +-----+---------+-----+
    |   1 | lisi    |  89 |
    |   3 | lidalei |  19 |
    |   2 | luky    |  18 |
    +-----+---------+-----+
    3 rows in set (0.00 sec)
    mysql>#查询v3表
    mysql> select * from v3;
    +--------+-------------+
    | cat_id | pj          |
    +--------+-------------+
    |      2 |  823.330000 |
    |      3 | 1746.066667 |
    |      4 | 2297.000000 |
    |      5 | 3700.000000 |
    |      8 |   75.333333 |
    |     11 |   31.000000 |
    |     13 |   33.500000 |
    |     14 |   54.000000 |
    |     15 |   70.000000 |
    +--------+-------------+
    9 rows in set (0.01 sec)
    mysql> update v3 set pj=80 where cat_id=15;
    ERROR 1288 (HY000): The target table v3 of the UPDATE is not updatable
    mysql> #以上是经过物理表计算得到的结果,不可以修改!!!
    mysql>

 

weinxin
我的微信
这是我的微信扫一扫
开拓者博主
  • 本文由 发表于 2016年7月22日13:29:10
  • 转载请务必保留本文链接:https://www.150643.com/216.html
匿名

发表评论

匿名网友 填写信息