邯郸专业做网站地方,可信赖的商城网站建设,大连金州新区规划建设局网站,网站开发团队分工【MySQL】第三周作业 1、在数据库example下创建college表。2、在student表上创建视图college_view。3、查看视图college_view的详细结构4、 更新视图。5 、修改视图#xff0c;6 、删除视图college_view 1、在数据库example下创建college表。
College表内容如下所示 字段名 … 【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 ALGORITHMUNDEFINED DEFINERrootlocalhost 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 ALGORITHMUNDEFINED 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 ALGORITHMUNDEFINED 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 ALGORITHMUNDEFINED 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 doesnt exist
