week3.MySQL练习

1、在数据库example下创建college表。College表内容如下所示
    字段名     字段描述     数据类型     主键     外键     非空     唯一     自增
    number     学号         INT(10)     是     否     是     是     否
    name     姓名             VARCHAR(20) 否     否     是     否     否
    major     专业         VARCHAR(20) 否     否     是     否     否
    age         年龄 I        NT(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