【MySQL】第三周作业

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