视图的简单使用

Leave a reply

1、什么是视图

  • 视图是表虚拟表，通过sql语句组合查询的结果，视图是原始数据的一种变换
  • 定义：create view xxx (a, b, c, d) as select h, i, j, k from xxxx xxxxx；
  • 删除：drop view xxxx；
mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
|  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 15:57:29 |
|  2 | 余西安    | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    |    1.00 |  33 | 2019-04-21 17:12:55 |
|  3 | Jack      | 男  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |
|  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
|  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
|  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
6 rows in set (0.00 sec)


mysql> create view v_user(id, user, pass, email) as select id, user_name, password, email from user where id < 5;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from v_user;
+----+-----------+----------------------------------+-----------------+
| id | user      | pass                             | email           |
+----+-----------+----------------------------------+-----------------+
|  1 | sian      | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  |
|  2 | 余西安    | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  |
|  3 | Jack      | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com |
|  4 | Rose      | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com |
+----+-----------+----------------------------------+-----------------+
4 rows in set (0.00 sec)


mysql> select id, user, pass from v_user;
+----+-----------+----------------------------------+
| id | user      | pass                             |
+----+-----------+----------------------------------+
|  1 | sian      | e10adc3949ba59abbe56e057f20f883e |
|  2 | 余西安    | c4ca4238a0b923820dcc509a6f75849b |
|  3 | Jack      | c81e728d9d4c2f636f067f89cc14862c |
|  4 | Rose      | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
+----+-----------+----------------------------------+
4 rows in set (0.00 sec)

mysql> show create view v_user\G
*************************** 1. row ***************************
                View: v_user
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `user`.`id` AS `id`,`user`.`user_name` AS `user`,`user`.`password` AS `pass`,`user`.`email` AS `email` from `user` where (`user`.`id` < 5)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

Leave a Reply