Python-4.46 数据库视图

视图是一个虚拟表，根据SQL语句获取表（可以进行命名），用户使用时只需使用名称即可获取，可以将该结果当做表来使用。

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)```


mysql> select * from department;
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  202 | 销售     |
|  203 | 运营     |
+------+----------+
4 rows in set (0.00 sec)


mysql> select name from employee where sex='male' and dep_id=(select id from department where name='技术');
+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)


#子查询出临时表，作为dep_id等判断依据
select id from department where name='技术'

• 创建视图

mysql> create view dep_view as select id from department where name='技术';
Query OK, 0 rows affected (0.05 sec)

mysql> select name from employee where sex='male' and dep_id=(select id from dep_view);
+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+
2 rows in set (0.02 sec)