【MySQL】第三周作业

• 1、在数据库example下创建college表。
• 2、在student表上创建视图college_view。
• 3、查看视图college_view的详细结构
• 4、 更新视图。
• 5 、修改视图，
• 6 、删除视图college_view

  1、在数据库example下创建college表。

  College表内容如下所示

      字段名     字段描述     数据类型      主键      外键     非空      唯一     自增
      number     学号        INT(10)      是       否       是       是        否
      name       姓名      VARCHAR(20)    否       否       是       否        否
      major      专业       VARCHAR(20)   否       否       是       否        否
      age        年龄        INT(5)       否       否       否       否        否
  

  mysql> create database example;
  Query OK, 1 row affected (0.04 sec)
   
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | db_test            |
  | example            |
  | information_schema |
  | mysql              |
  | performance_schema |
  | rele               |
  | sys                |
  | user               |
  +--------------------+
  8 rows in set (0.00 sec)
   
  mysql> use example;
  Database changed
  mysql> CREATE TABLE college(
      ->     number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号',
      ->     name VARCHAR(20) NOT NULL COMMENT '姓名',
      ->     major VARCHAR(20) NOT NULL COMMENT '专业',
      ->     age INT(5) COMMENT '年龄'
      -> );
  Query OK, 0 rows affected, 2 warnings (0.11 sec)
   
  mysql> show tables;
  +-------------------+
  | Tables_in_example |
  +-------------------+
  | college           |
  +-------------------+
  1 row in set (0.00 sec)
   
  mysql> desc college;
  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | number | int         | NO   | PRI | NULL    |       |
  | name   | varchar(20) | NO   |     | NULL    |       |
  | major  | varchar(20) | NO   |     | NULL    |       |
  | age    | int         | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  4 rows in set (0.02 sec)
  

  2、在student表上创建视图college_view。

  视图的字段包括student_num、student_name、student_age和department。ALGORITHM设置为MERGE类型，并且为视图加上WITH LOCAL CHECK

  OPTION条件

  mysql> CREATE  VIEW
     -> college_view(student_num,student_name,student_age,department)
     -> AS SELECT number,name,age,major FROM college
     -> WITH LOCAL CHECK OPTION;
  Query OK, 0 rows affected (0.02 sec)
  

  3、查看视图college_view的详细结构

  mysql> show create view college_view \g
  +--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | View         | Create View                     | character_set_client | collation_connection |
  +--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  | college_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `college_view` (`student_num`,`student_name`,`student_age`,`department`) AS select `college`.`number` AS `number`,`college`.`name` AS `name`,`college`.`age` AS `age`,`college`.`major` AS `major` from `college` WITH LOCAL CHECK OPTION | utf8mb4              | utf8mb4_0900_ai_ci   |
  +--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  1 row in set (0.01 sec)
  

  4、 更新视图。

  向视图中插入3条记录。记录内容如下表所示

  umer name major age

  0901 张三 外语 20

  0902 李四 计算机 22

  0903 王五 计算机 19

  mysql> insert into college_view values(0901,'张三',20,'外语');
  Query OK, 1 row affected (0.02 sec)
   
  mysql> insert into college_view values(0902,'李四',22,'计算机');
  Query OK, 1 row affected (0.01 sec)
   
  mysql> insert into college_view values(0903,'王五',19,'计算机');
  Query OK, 1 row affected (0.21 sec)
   
  mysql> select * from college_view;
  +-------------+--------------+-------------+------------+
  | student_num | student_name | student_age | department |
  +-------------+--------------+-------------+------------+
  |         901 | 张三         |          20 | 外语       |
  |         902 | 李四         |          22 | 计算机     |
  |         903 | 王五         |          19 | 计算机     |
  +-------------+--------------+-------------+------------+
  3 rows in set (0.00 sec)
  

  5 、修改视图，

  使其显示专业为计算机的信息，其他条件不变

  方法一：

  CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW

  college_view(student_num,student_name,student_age,department)

  AS SELECT number,name,age,major

  FROM college WHERE major=’计算机’

  WITH LOCAL CHECK OPTION;

  方法二：

  ALTER ALGORITHM=UNDEFINED VIEW

  college_view(student_num,student_name,student_age,department)

  AS SELECT number,name,age,major

  FROM college WHERE major=’计算机’

  WITH LOCAL CHECK OPTION;

  mysql> ALTER ALGORITHM=UNDEFINED VIEW
     ->     college_view(student_num,student_name,student_age,department)
     ->     AS SELECT number,name,age,major
     ->     FROM college WHERE major='计算机'
     ->     WITH LOCAL CHECK OPTION;
  Query OK, 0 rows affected (0.01 sec)
  

  mysql> select * from college_view;
  +-------------+--------------+-------------+------------+
  | student_num | student_name | student_age | department |
  +-------------+--------------+-------------+------------+
  |         902 | 李四         |          22 | 计算机     |
  |         903 | 王五         |          19 | 计算机     |
  +-------------+--------------+-------------+------------+
  2 rows in set (0.01 sec)
  

  6 、删除视图college_view

  DROP VIEW college_view;

  mysql> drop view college_view;
  Query OK, 0 rows affected (0.01 sec)
   
  mysql> select * from college_view;
  ERROR 1146 (42S02): Table 'rele.college_view' doesn't exist