美文网首页
Mysql练习-视图的操作

Mysql练习-视图的操作

作者: A04 | 来源:发表于2018-04-23 21:20 被阅读0次

    一、练习要求

    ① 创建学生表stu,插入3条记录
    ② 创建报名表sign,插入3条记录
    ③ 创建成绩表stu_mark,插入3条记录
    ④ 创建考上Peking University的学生的视图
    ⑤ 创建考上Tsinghua University的学生的视图
    ⑥ XiaoTian的成绩在录入的时候因录入错误多录了50分,对其成绩进行更正
    ⑦ 查看更新后视图和表的情况
    ⑧ 查看视图的创建信息
    ⑨ 删除创建的视图


    stu表结构

    字段名 数据类型 主键 外键 非空 唯一 自增
    s_id INT(11)
    s_name VARCHAR(20)
    addr VARCHAR(50)
    tel VARCHAR(50)


    stu表内容

    s_id s_name addr tel
    1 XiaoWang Henan 0371-12345678
    2 XiaoLi Hebei 13889072345
    3 ZhengGu Henan 0371-1234570


    sign表结构

    字段名 数据类型 主键 外键 非空 唯一 自增
    s_id INT(11)
    s_name VARCHAR(20)
    s_sch VARCHAR(50)
    s_sign_sch VARCHAR(50)


    sign表内容

    s_id s_name s_sch s_sign_sch
    1 XiaoWang Middle School1 Peking University
    2 XiaoLi Middle School2 Tsinghua University
    3 ZhengGu Middle School3 Tsinghua University


    stu_mark表结构

    字段名 数据类型 主键 外键 非空 唯一 自增
    s_id INT(11)
    s_name VARCHAR(20)
    mark INT(11)


    stu_mark表内容

    s_id s_name mark
    1 XiaoWang 80
    2 XiaoLi 71
    3 ZhengGu 70

    二、操作记录


    创建学生表stu,插入3条记录

    mysql> CREATE TABLE stu
        -> (
        -> s_id INT PRIMARY KEY,
        -> s_name VARCHAR(20),
        -> addr VARCHAR(50),
        -> tel VARCHAR(50)
        -> );
    Query OK, 0 rows affected (0.28 sec)
    
    mysql>
    mysql> INSERT INTO stu
        -> VALUES(1,'XiaoWang','Henan','0371-12345678'),
        -> (2,'XiaoLi','Hebei','13889072345'),
        -> (3,'XiaoTian','Henan','0371-12345670');
    Query OK, 3 rows affected (0.08 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql>
    


    创建报名表sign,插入3条记录

    mysql> CREATE TABLE sign
        -> (
        -> s_id INT PRIMARY KEY,
        -> s_name VARCHAR(20),
        -> s_sch VARCHAR(50),
        -> s_sign_sch VARCHAR(50)
        -> );
    Query OK, 0 rows affected (0.32 sec)
    
    mysql>
    mysql> INSERT INTO sign
        -> VALUES(1,'XiaoWang','Middle School1','Peking University'),
        -> (2,'XiaoLi','Middle School2','Tsinghua University'),
        -> (3,'XiaoTian','Middle School3','Tsinghua University');
    Query OK, 3 rows affected (0.07 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql>
    


    创建成绩表stu_mark,插入3条记录

    mysql> CREATE TABLE stu_mark (s_id INT PRIMARY KEY ,s_name VARCHAR(20) ,mark int );
    Query OK, 0 rows affected (0.24 sec)
    
    mysql>
    mysql> INSERT INTO stu_mark VALUES(1,'XiaoWang',80),(2,'XiaoLi',71),(3,'XiaoTian',70);
    Query OK, 3 rows affected (0.11 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql>
    


    创建考上Peking University的学生的视图

    mysql> CREATE VIEW beida (id,name,mark,sch)
        -> AS SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark, sign.s_sign_sch
        -> FROM stu_mark ,sign
        -> WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=41 AND sign.s_sign_sch='Peking University';
    Query OK, 0 rows affected (0.06 sec)
    
    mysql>
    


    创建考上Tsinghua University的学生的视图

    mysql> CREATE VIEW qinghua (id,name,mark,sch)
        -> AS SELECT stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch
        -> FROM stu_mark ,sign
        -> WHERE stu_mark.s_id=sign.s_id  AND stu_mark.mark>=40 AND sign.s_sign_sch='Tsinghua University';
    Query OK, 0 rows affected (0.05 sec)
    
    mysql>
    


    XiaoTian的成绩在录入的时候因录入错误多录了50分,对其成绩进行更正

    mysql> UPDATE stu_mark SET mark = mark-50 WHERE stu_mark.s_name ='XiaoTian';
    Query OK, 1 row affected (0.06 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql>
    


    查看更新后视图和表的情况

    mysql> SELECT * FROM stu_mark;
    +------+----------+------+
    | s_id | s_name   | mark |
    +------+----------+------+
    |    1 | XiaoWang |   80 |
    |    2 | XiaoLi   |   71 |
    |    3 | XiaoTian |   20 |
    +------+----------+------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT * FROM qinghua;
    +----+--------+------+---------------------+
    | id | name   | mark | sch                 |
    +----+--------+------+---------------------+
    |  2 | XiaoLi |   71 | Tsinghua University |
    +----+--------+------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM beida;
    +----+----------+------+-------------------+
    | id | name     | mark | sch               |
    +----+----------+------+-------------------+
    |  1 | XiaoWang |   80 | Peking University |
    +----+----------+------+-------------------+
    1 row in set (0.00 sec)
    
    mysql>
    


    查看视图的创建信息

    mysql> SELECT * FROM information_schema.views where table_name = 'beida'\G
    *************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: index_test
              TABLE_NAME: beida
         VIEW_DEFINITION: select `index_test`.`stu_mark`.`s_id` AS `id`,`index_test`.`stu_mark`.`s_name` AS `name`,`index_test`.`stu_mark`.`mark` AS `mark`,`index_test`.`sign`.`s_sign_sch` AS `sch` from `index_test`.`stu_mark` join `index_test`.`sign` where ((`index_test`.`stu_mark`.`s_id` = `index_test`.`sign`.`s_id`) and (`index_test`.`stu_mark`.`mark` >= 41) and (`index_test`.`sign`.`s_sign_sch` = 'Peking University'))
            CHECK_OPTION: NONE
            IS_UPDATABLE: YES
                 DEFINER: root@localhost
           SECURITY_TYPE: DEFINER
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
    *************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: view
              TABLE_NAME: beida
         VIEW_DEFINITION: select `view`.`stu_mark`.`s_id` AS `id`,`view`.`stu_mark`.`s_name` AS `name`,`view`.`stu_mark`.`mark` AS `mark`,`view`.`sign`.`s_sign_sch` AS `sch` from `view`.`stu_mark` join `view`.`sign` where ((`view`.`sign`.`s_id` = `view`.`stu_mark`.`s_id`) and (`view`.`sign`.`s_sign_sch` = 'Peking University') and (`view`.`stu_mark`.`mark` >= 41))
            CHECK_OPTION: NONE
            IS_UPDATABLE: YES
                 DEFINER: root@localhost
           SECURITY_TYPE: DEFINER
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM information_schema.views where table_name = 'qinghua'\G
    *************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: index_test
              TABLE_NAME: qinghua
         VIEW_DEFINITION: select `index_test`.`stu_mark`.`s_id` AS `id`,`index_test`.`stu_mark`.`s_name` AS `name`,`index_test`.`stu_mark`.`mark` AS `mark`,`index_test`.`sign`.`s_sign_sch` AS `sch` from `index_test`.`stu_mark` join `index_test`.`sign` where ((`index_test`.`stu_mark`.`s_id` = `index_test`.`sign`.`s_id`) and (`index_test`.`stu_mark`.`mark` >= 40) and (`index_test`.`sign`.`s_sign_sch` = 'Tsinghua University'))
            CHECK_OPTION: NONE
            IS_UPDATABLE: YES
                 DEFINER: root@localhost
           SECURITY_TYPE: DEFINER
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
    *************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: view
              TABLE_NAME: qinghua
         VIEW_DEFINITION: select `view`.`stu_mark`.`s_id` AS `id`,`view`.`stu_mark`.`s_name` AS `name`,`view`.`stu_mark`.`mark` AS `mark`,`view`.`sign`.`s_sign_sch` AS `sch` from `view`.`stu_mark` join `view`.`sign` where ((`view`.`sign`.`s_id` = `view`.`stu_mark`.`s_id`) and (`view`.`sign`.`s_sign_sch` = 'Tsinghua University') and (`view`.`stu_mark`.`mark` >= 40))
            CHECK_OPTION: NONE
            IS_UPDATABLE: YES
                 DEFINER: root@localhost
           SECURITY_TYPE: DEFINER
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
    2 rows in set (0.00 sec)
    
    mysql>
    


    删除创建的视图

    mysql> DROP VIEW beida;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> DROP VIEW qinghua;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
    

    相关文章

      网友评论

          本文标题:Mysql练习-视图的操作

          本文链接:https://www.haomeiwen.com/subject/ybhrlftx.html