一起学mysql 03.mysql 视图

最新推荐文章于 2025-08-05 17:47:02 发布

隔壁小翔

最新推荐文章于 2025-08-05 17:47:02 发布

阅读量155

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/tangwei0928/article/details/97168266

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍MySQL中视图的创建、修改、更新及删除等基本操作。包括单表和多表上的视图创建方法，以及使用CREATE OR REPLACE VIEW和ALTER VIEW进行视图结构调整的方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql 视图

创建视图

mysql> create table department (d_id int(4) NOT NULL PRIMARY KEY, d_name varchar(20) NOT NULL  UNIQUE, function varchar(50), address varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   | PRI | NULL    |       |
| d_name   | varchar(20) | NO   | UNI | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create table worker (num int(10) NOT NULL PRIMARY KEY, d_id int(4), name varchar(20) NOT NULL, sex varchar(4) NOT NULL, birthday datetime, homeaddress varchar(50), CONSTRAINT id_fk FOREIGN KEY(d_id) REFERENCES department(d_id) );
Query OK, 0 rows affected (0.35 sec)

mysql> desc worker;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num         | int(10)     | NO   | PRI | NULL    |       |
| d_id        | int(4)      | YES  | MUL | NULL    |       |
| name        | varchar(20) | NO   |     | NULL    |       |
| sex         | varchar(4)  | NO   |     | NULL    |       |
| birthday    | datetime    | YES  |     | NULL    |       |
| homeaddress | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

在单表上创建视图

mysql> create view department_view1 AS select * from department;
Query OK, 0 rows affected (0.00 sec)

mysql> desc department_view1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   |     | NULL    |       |
| d_name   | varchar(20) | NO   |     | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create view department_view2 (name,function,location) AS select d_name,function,addfrom department;
Query OK, 0 rows affected (0.00 sec)

mysql> desc department_view2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | NO   |     | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

在多表上创建视图

mysql> create ALGORITHM=MERGE view worker_view1 (name,department,sex,age,address) as 
    -> select name,department.d_name,sex,2009-birthday,address 
    -> from worker,department where worker.d_id = department.d_id with local check option;
Query OK, 0 rows affected (0.09 sec)

mysql> desc worker_view1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(20) | NO   |     | NULL    |       |
| department | varchar(20) | NO   |     | NULL    |       |
| sex        | varchar(4)  | NO   |     | NULL    |       |
| age        | bigint(16)  | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

查看视图

desc查看视图基本信息

mysql> desc worker_view1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(20) | NO   |     | NULL    |       |
| department | varchar(20) | NO   |     | NULL    |       |
| sex        | varchar(4)  | NO   |     | NULL    |       |
| age        | bigint(16)  | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

show table status 查看视图基本信息

mysql> show table status like "worker_view1" \G
*************************** 1. row ***************************
           Name: worker_view1
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

mysql> show table status like "worker" \G
*************************** 1. row ***************************
           Name: worker
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-07-24 19:55:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

show create view查看视图详细信息

mysql> show create view worker_view1 \G
*************************** 1. row ***************************
                View: worker_view1
         Create View: CREATE ALGORITHM=MERGE DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `worker_view1` AS select `worker`.`name` AS `name`,`department`.`d_name` AS `department`,`worker`.`sex` AS `sex`,(2009 - `worker`.`birthday`) AS `age`,`department`.`address` AS `address` from (`worker` join `department`) where (`worker`.`d_id` = `department`.`d_id`) WITH LOCAL CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

在view表中查看视图详细信息

mysql> select * from information_schema.views where table_name='worker_view1' \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: example
          TABLE_NAME: worker_view1
     VIEW_DEFINITION: select `example`.`worker`.`name` AS `name`,`example`.`department`.`d_name` AS `department`,`example`.`worker`.`sex` AS `sex`,(2009 - `example`.`worker`.`birthday`) AS `age`,`example`.`department`.`address` AS `address` from `example`.`worker` join `example`.`department` where (`example`.`worker`.`d_id` = `example`.`department`.`d_id`)
        CHECK_OPTION: LOCAL
        IS_UPDATABLE: YES
             DEFINER: skip-grants user@skip-grants host
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

修改视图

create or replace view 修改视图

mysql> desc department_view1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(4)      | NO   |     | NULL    |       |
| d_name   | varchar(20) | NO   |     | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create or replace algorithm=temptable  view department_view1 (department,function,location)  AS select d_name,function,address from department;
Query OK, 0 rows affected (0.00 sec)

mysql> desc department_view1;                                                                                                     
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| department | varchar(20) | NO   |     | NULL    |       |
| function   | varchar(50) | YES  |     | NULL    |       |
| location   | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

alter 语句修改视图

mysql> desc department_view2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | NO   |     | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> alter view department_view2 (department,name,sex,location) AS select d_name,worker.name,worker.sex,address from department,worker where department.d_id=worker.d_id with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> desc department_view2;                                                                                                     
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| department | varchar(20) | NO   |     | NULL    |       |
| name       | varchar(20) | NO   |     | NULL    |       |
| sex        | varchar(4)  | NO   |     | NULL    |       |
| location   | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

更新视图

mysql> insert into department (d_id,d_name,function,address) values (10001,'人事部','管理公司人事变动','2号楼3层');
Query OK, 1 row affected (0.00 sec)

mysql> insert into department (d_id,d_name,function,address) values (10002,'生成部','主管生产','5号楼1层');
Query OK, 1 row affected (0.00 sec)

mysql> select * from department;
+-------+-----------+--------------------------+-------------+
| d_id  | d_name    | function                 | address     |
+-------+-----------+--------------------------+-------------+
| 10001 | 人事部    | 管理公司人事变动         | 2号楼3层    |
| 10002 | 生成部    | 主管生产                 | 5号楼1层    |
+-------+-----------+--------------------------+-------------+
2 rows in set (0.00 sec)

mysql> create view department_view3(name,function,address) as select d_name,function,address from department where d_id=10001;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from department_view3;
+-----------+--------------------------+-------------+
| name      | function                 | address     |
+-----------+--------------------------+-------------+
| 人事部    | 管理公司人事变动         | 2号楼3层    |
+-----------+--------------------------+-------------+
1 row in set (0.00 sec)

mysql> update department_view3 set name='科研部',function='新产品科研',address='3号楼5层';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from department_view3;
+-----------+-----------------+-------------+
| name      | function        | address     |
+-----------+-----------------+-------------+
| 科研部    | 新产品科研      | 3号楼5层    |
+-----------+-----------------+-------------+
1 row in set (0.01 sec)

mysql> select * from department;
+-------+-----------+-----------------+-------------+
| d_id  | d_name    | function        | address     |
+-------+-----------+-----------------+-------------+
| 10001 | 科研部    | 新产品科研      | 3号楼5层    |
| 10002 | 生成部    | 主管生产        | 5号楼1层    |
+-------+-----------+-----------------+-------------+
2 rows in set (0.00 sec)

删除视图

mysql> drop view if exists department_view3;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view department_view1;
Query OK, 0 rows affected (0.00 sec)