最全MySQL8.0实战教程 13 MySQL的视图 13.3 视图的创建

川长思鸟来 2024-03-26 14:17 55阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  13 MySQL的视图
         *   *  13.3 视图的创建
             *   *  13.3.1 数据准备
                 *  13.3.2 操作

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 13 MySQL的视图 ####

##### 13.3 视图的创建 #####

创建视图的语法为：

create [or replace] [algorithm = {undefined | merge | temptable}]
     
    view view_name [(column_list)]
     
    as select_statement
     
    [with [cascaded | local] check option]

简化：

create [or replace] view view_name as select_statement

参数说明：

（1）algorithm：可选项，表示视图选择的算法。

（2）view\_name ：表示要创建的视图名称。

（3）column\_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。

（4）select\_statement：表示一个完整的查询语句，将查询记录导入视图中。

（5）\[with \[cascaded | local\] check option\]：可选项，表示更新视图时要保证在该视图的权限范围之内。

###### 13.3.1 数据准备 ######

创建 数据库mydb6\_view,

create database mydb6_view;

然后在该数据库下执行sql脚本view\_data.sql 导入数据

view\_data.sql

/*
     Navicat Premium Data Transfer
    
     Source Server         : conn-localhost
     Source Server Type    : MySQL
     Source Server Version : 80025
     Source Host           : localhost:3306
     Source Schema         : mydb6_view
    
     Target Server Type    : MySQL
     Target Server Version : 80025
     File Encoding         : 65001
    
     Date: 14/10/2021 11:39:07
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for dept
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept`  (
      `deptno` int NOT NULL,
      `dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `loc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`deptno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES (10, '教研部', '北京');
    INSERT INTO `dept` VALUES (20, '学工部', '上海');
    INSERT INTO `dept` VALUES (30, '销售部', '广州');
    INSERT INTO `dept` VALUES (40, '财务部', '武汉');
    
    -- ----------------------------
    -- Table structure for emp
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp`  (
      `empno` int NOT NULL,
      `ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `job` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `mgr` int NULL DEFAULT NULL,
      `hiredate` date NULL DEFAULT NULL,
      `sal` decimal(7, 2) NULL DEFAULT NULL,
      `COMM` decimal(7, 2) NULL DEFAULT NULL,
      `deptno` int NULL DEFAULT NULL,
      PRIMARY KEY (`empno`) USING BTREE,
      INDEX `fk_emp`(`mgr`) USING BTREE,
      CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
    INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
    INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
    INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
    INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
    INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
    INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
    INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);
    
    -- ----------------------------
    -- Table structure for salgrade
    -- ----------------------------
    DROP TABLE IF EXISTS `salgrade`;
    CREATE TABLE `salgrade`  (
      `grade` int NOT NULL,
      `losal` int NULL DEFAULT NULL,
      `hisal` int NULL DEFAULT NULL,
      PRIMARY KEY (`grade`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of salgrade
    -- ----------------------------
    INSERT INTO `salgrade` VALUES (1, 7000, 12000);
    INSERT INTO `salgrade` VALUES (2, 12010, 14000);
    INSERT INTO `salgrade` VALUES (3, 14010, 20000);
    INSERT INTO `salgrade` VALUES (4, 20010, 30000);
    INSERT INTO `salgrade` VALUES (5, 30010, 99990);
    
    SET FOREIGN_KEY_CHECKS = 1;

![在这里插入图片描述][68c57b9dd8964337be04c9aaa1116d2a.png_pic_center]

###### 13.3.2 操作 ######

create or replace view view1_emp
    as 
    select ename,job from emp;

![在这里插入图片描述][1e89d21db2fa44b4b43fa0cb8d04952a.png_pic_center]

看看

![在这里插入图片描述][484054e504114ebfac47d071cce7432f.png_pic_center]

-- 查看表和视图 
    show full tables;

![在这里插入图片描述][756747cbf78a4dd79f83c458e4c5666c.png_pic_center]

从视图中查询数据

![在这里插入图片描述][a1cc9696e58541e9b81885af4bf4415a.png_pic_center]

一定记得，视图只是从表中拿数据进行展示，表一旦发生变化，视图也会随之改变。

![在这里插入图片描述][c77e24e4226848419b25ed2c2cd178e0.png_pic_center]

再次看看视图

![在这里插入图片描述][dac75f2ff8ad438c84bda27bc87e9cc6.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[68c57b9dd8964337be04c9aaa1116d2a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/19c8eda61ae74f7381f436724625dd42.png
[1e89d21db2fa44b4b43fa0cb8d04952a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/cbc5d55bf3514ff69d15b29d605c4dbe.png
[484054e504114ebfac47d071cce7432f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c36e83887e154c8097ae60b653ce3b7d.png
[756747cbf78a4dd79f83c458e4c5666c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/568344258dbe4632864a7c21bd47cd61.png
[a1cc9696e58541e9b81885af4bf4415a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/28792f8091354ae6b0dae336ea94e4de.png
[c77e24e4226848419b25ed2c2cd178e0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/40106754c43343da9abf8eb1ea61bea4.png
[dac75f2ff8ad438c84bda27bc87e9cc6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c33527d5176549beab87f370ca502993.png