MYSQL第三周作业。

最新推荐文章于 2024-10-08 11:31:42 发布

年少与梦^

最新推荐文章于 2024-10-08 11:31:42 发布

阅读量306

点赞数 2

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_52173320/article/details/139186961

版权

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

mysql> create database example;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| college            |
| example            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.02 sec)

mysql> use example;
Database changed

创建 college 表：

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.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| college           |
+-------------------+
1 row in set (0.03 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.00 sec)

创建 college_view 视图：

查看 college_view 视图的结构：

SHOW CREATE VIEW college_view \G

mysql> CREATE ALGORITHM=MERGE 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)

mysql> SHOW CREATE VIEW college_view \g
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View         | Create View                                                                                                                                                                                                                                                                                                           | character_set_client | collation_connection |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| college_view | CREATE ALGORITHM=MERGE 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 | utf8mb3              | utf8mb3_general_ci   |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

向视图中插入记录,更新视图：

0901, '张三', 20, '外语'

0902, '李四', 22, '计算机'

0903, '王五', 19, '计算机'

mysql> INSERT INTO college_view VALUES(0901, '张三', 20, '外语');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO college_view VALUES(0902, '李四', 22, '计算机');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO college_view VALUES(0903, '王五', 19, '计算机');
Query OK, 1 row affected (0.00 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)

修改视图，仅显示专业为计算机的信息：

mysql> 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;
Query OK, 0 rows affected (0.03 sec)

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

删除视图：

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 'example.college_view' doesn't exist