Fork me on GitHub

JackLin的博客

当前位置:首页 > 标签

SpringBoot 14 Spring 2 SpringMVC 3 MyBatis 2 Linux 4 阿里云 13 宝塔 1 Docker 3 ElasticSearch 2 Redis 4 Shiro 0 Dubbo 0 Swagger 0 Thymeleaf 6 数据库 11 MySQL 11 外键 2 Gradle 1 Test 0 Tomcat 1 JavaWeb 7 Ajax 1 注解 3 css 2 报错 3 多数据源 1 Java基础 1 源码 2 Servlet 1 JSP 1 环境搭建 8 RabbitMQ 1 七牛云 1 Edit.md 1 图像识别 4 英语 2 Zookeeper 1

数据库期末复习

  • 2020-06-22
  • 191
  • 数据库设计
前言:关于数据库的期末复习,侧重点在代码,存储函数,存储过程,触发器等等。 > SQL语句 ``` -- 在语法中:[] 表示可选 <> 表示必选 -- 在进行很多操作时,我们是不知道表名的,所以在书写语句时需要限定一下表名或者列名,而存储函数和存储过程是依赖于数据库的,所以不需要指定表名 -- 所有的代码是伪代码,只是为了方便期末复习查看,并不应能执行成功。 -- 一、第1部分:数据库,表,视图,索引,存储函数,存储过程,约束,触发器 -- 1. 创建删除数据库 create database 数据库名; drop database 数据库名; -- 2. 创建,修改,删除表; 后面 4 行代码是 MySQL 提供的约束,将在第 7 点中详细解释,这是是在创建表的同时增加约束,后面将在创建表结束之后修改约束。 -- 代码都是伪代码,方便记忆,所以将多种情况融在了一起。 create table student ( number varchar(60) primary key, sname varchar(60) not null unique, gender varchar(60), age int(10) default 0, time timestamp default current_timestamp, -- 1. 主键约束 constraint 约束名称 primary key(number, sname), -- 2. 唯一约束 constraint 约束名称 unique(gender), -- 3. 检查约束 constraint 约束名册 check(age > 20 and age < 60), -- 4. 外键约束(创建外键约束时,可以不需要前面的 constraint 约束名称 也可以正常运行) constraint 约束名称 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade ); -- 或者 2.1 create table 表名 select_语句; -- 2.2 修改表结构 add, modify, drop alter table 表名 add 列名 varchar(60) [default 'test']; alter table 表名 modify 列名 varchar(60) [default 'test']; alter table 表名 drop 列名 -- 2.3 截断表 truncate table 表名 -- 2.4 删除表 drop table 表名 -- 3. 创建索引 create index 索引名称 on 表名(属性名); drop index 索引名称 on 表名; -- 4. 创建视图,修改视图,删除视图; -- with check option 表示说插入或修改的数据行必须满足视图所定义的约束条件 create [or replace] view 视图名称 [别名] as sql语句 [with check option]; alter view 视图名称 [别名] as select_语句; drop view 视图名称; -------------------------------------------------- -- 5. 存储函数的创建 create function 函数名([参数名 参数类型]) returns 返回值类型 begin 函数体; return 语句; end; -- 5.1 调用存储函数 select 函数名([参数值]); -- 5.2 删除存储函数,后面不需要带括号 drop function 函数名称; -------------------------------------------------- -- 5. 存储函数举例, 使用 declare 创建变量,使用 set 为变量赋值 -- 5.1 创建存储函数 -- delimiter@@ -- create function name_fn(dno decimal(2), a decimal(5,2), b decimal(5,2)) -- returns varchar(14) -- begin -- declare x decimal(5,2); -- declare y decimal(5,2); -- set x = a, y = b; -- return x+y; -- end@@ delimiter@@ create function name_fn(a decimal(5,2), b decimal(5,2)) returns decimal begin declare x decimal(5,2); declare y decimal(5,2); set x = a, y = b; return x+y; end@@ deleimter; set global log_bin_trust_function_creators=TRUE; -- 5.2 调用存储函数 select name_fno(1, 2); -- 5.3 删除存储函数 drop function name_fn; ----------------------------------------------------- -- 6. 创建存储过程 create procedure 存储过程名( [in | out | inout] 参数名 参数类型, [in | out | inout] 参数名 参数类型 ) begin 过程体; end -- 6.1 调用存储过程 call procedure 存储过程名(); drop procedure 存储过程名; ------------------------------------------------------- -- 6. 存储过程举例, select ... into ... 给变量赋值,结合 out 参数使用 -- 关于 in, out,inout 更详细的例子可以查看课本 P88 delimiter @@ create procedure process_name( in i_1 decimal(2, 0), out o_1 varchar(14), out o_2 varchar(14) ) begin select test_1, test_2 into o_1, o_2 from test_table where test_3 = i_1; end@@ delimiter; call process_name(10, @p_1, @p_2); select @p_1, @p_2; select concat('MySql版本信息:', @@version); --------------------------------------------------------- -- 7. 约束,需要使用 constraint 关键字 -- 这里介绍 4 中约束,在创建表的时候增加约束已经在第 1 点中详细介绍了,这里只介绍在创建表之后如何建立约束 -- 7.1 创建表之后,删除,增加主键约束 alter table 表名 drop primary key; -- 删除主键约束,主键只有一个,所以不需要指定主键名称 alter table 表名 add constraint 主键名称 primary key(列名); -- 增加主键约束 -- 7.2 创建表之后,删除,增加唯一约束 alter table 表名 drop index 约束名称; -- 删除 “唯一约束” , 注意:这里使用的是 index,而且需要指定约束名称; alter table 表名 add constraint 约束名称 unique(列名); -- 增加 “唯一约束” -- 7.3 创建表之后,删除,增加外键约束 alter table 表名 drop foreign kye 外键约束名称; -- 删除外键约束,需要指定约束名称 alter table 表名 add constraint 约束名册 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade; -- 增加外键约束 --------------------------------------------------------- -- 8. 触发器 create trigger 触发器名称 before | after insert | delete | update on 表名 for each row <触发体>; -- 8.1 删除触发器 drop trigger 触发器名册; -- 触发器是数据库级别的,所以这里不需要指定表名 --------------------------------------------------------- -- 8. 触发器举例,配合 new,old 使用 create trigger test_trigger after update on department for each row update employee set deptno = new.deptno where deptno = old.deptno; -- 二、第2部分:用户,权限管理,数据的备份与恢复 -- 1. 用户管理:创建用户,修改密码,修改用户名 -- 用户 == 用户名@主机名 grant user 用户名@主机名 identified by 'password'; set password for 用户 = '新密码'; rename user 旧用户 to 新用户; drop user 用户; ----------------------------------------------------------- -- 2. 权限管理; 后面可以加上 with grant option -- 2.1 授予 "MySql 字段级别" 的权限:由于是 “字段级别”,所以要指定数据库名,表名,列名 grant 权限名称(列名) on table 数据库名.表名 to 用户 [with grant option] -- 2.1 实例 grant select(ename, sal), update(sal) on table scott1.emp to test_usr@localhost with grant option; ----------------------------------------------------------- -- 2.2 授予 “MySql 表级别” 的权限:由于是 “表级别”,所有只需要指定数据库名,表名 grant 权限名称 on table 数据库名.表名 to 用户 [with grant option] -- 2.2 实例 grant alter, select, insert(empno, ename) on table scott1.emp to test_usr@localhost with grant option; ------------------------------------------------------------ -- 2.3 授予 “MySql 存储程序级别” 的权限“由于是存储过程,存储函数级别的权限,所以不需要指定表名,只需要执行数据库名,存储函数名/存储过程名 grant 权限名称 on function | references 数据库名.存储函数名/存储过程名 to 用户 with grant option; -- 2.3 实例 grant execute on procedure scott1.emp_p to test_usr@localhost with grant option; ----------------------------------------------------------- -- 2.4 授予 “MySql 数据库级别” 的权限:因为是数据库级别,所有范围直接写成 数据库名.* grant 权限名称 on 数据库名.* to 用户 with grant option; -- 2.5 授予 “MySql 服务器管理” -------------------------------------------------------------------- grant 权限名册 on *.* to 用户 with grant option; -- 2.6 撤销所有权限 revoke all privileges, grant option from 用户; -- 2.6.1 撤销指定权限 revoke 权限名称 on *.* | 数据库名.* | 数据库名.表名 from 用户; -- 3. 角色管理; 角色格式:'角色名'@'主机名' ;这个角色的格式和用户的格式十分相似 create role 角色; -- 3.1 授予角色权限:语法和授予用户权限的语法一样 -- 3.2 授予角色给用户 grant 角色 to 用户; -- 3.3 撤销用户角色 drop role 角色; -- 4. 数据的备份与恢复:这些命令不能写到查询里面,而是要在控制台的命令行中输入并执行。 -- 如果通过 -p 指定了密码,则可以直接执行;如果没有指定密码,则后面需要输入密码 -- 4.1 备份单个数据库 mysqldump -u 用户名 -h 主机名 -p 密码 数据库名>备份文件名.sql -- 4.2 备份多个数据库 mysqldump -u 用户名 -h 主机名 -p 密码 --database 数据库名 数据库名>备份文件名.sql -- 4.3 备份所有数据库 mysqldump -u 用户名 -h 主机名 -p 密码 --all -database>备份文件名.sql -- 4.4 恢复shuj1 mysql -u 用户名 -p 数据库名<备份文件名.sql -- 之后会要求输入密码 -- 5. 表数据的导入和导出 mysqldump -u root -p -T '目标路径' 数据库名 表名 -- “目标路径” 可以通过下面语句查询得到 select @@secure_file_priv; ```

《数据库原理及应用》课后实验6_数据库安全管理

  • 2020-05-21
  • 163
  • 数据库设计
前言:这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结,相当于作为**数据库系统**课程的期末复习吧! # 实验6_数据库安全管理 > 实验需要掌握知识点 - 掌握用户账号的创建,查看,修改,删除方法 - 掌握用户权限的设置方法 - 掌握角色的创建,删除方法 > 具体 SQL 代码 ``` -- 1. 在本地主机创建用户账号st_01,密码为123456。 create user st_01@localhost identified by '123456'; -- 2. 查看MySQL下所有用户账号列表。 use mysql; select DATABASE(); ## 调用系统函数,查看当前所使用的数据库 select * from user; -- 3. 修改用户账号st_01的密码为111111。 set password for st_01@localhost = '111111'; -- 4. 使用studentsdb数据库中的student_info表。 -- (1)授予用户账号st_01查询表的权限。 ## 属于 MySQL 表级别的权限 use linkai_studentsdb; GRANT SELECT ON TABLE linkai_studentsdb.student_info TO st_01@localhost; -- (2)授予用户账号st_01更新家庭住址列的权限。 ## 属于 MySQL 字段级别的权限 GRANT UPDATE(s_address) ON TABLE linkai_studentsdb.student_info TO st_01@localhost; -- (3)授予用户账号st_01修改表结构的权限。 GRANT ALTER ON TABLE linkai_studentsdb.student_info TO st_01@localhost; -- 5. 使用studentsdb数据库中的student_info表。 -- (1)创建存储过程cn_proc,统计student_info表中的学生人数。 delimiter@@ create procedure cn_proc() BEGIN SELECT COUNT(s_number) 学生人数 FROM student_info; END@@ delimiter; CALL cn_proc(); -- (2)授予用户账号st_01调用cn_proc存储过程的权限。 GRANT EXECUTE ON PROCEDURE linkai_studentsdb.cn_proc TO st_01@localhost; -- (3)以用户账号st_01连接MySQL服务器,调用cn_proc存储过程查看学生人数。 CALL cn_proc(); -- 6. 使用studentsdb数据库。 -- (1)授予用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。 ## 数据库级别的权限 GRANT CREATE, DROP, SELECT, INSERT ON linkai_studentsdb.* TO st_01@localhost; -- (2)以用户账号st_01连接MySQL服务器,创建新表st_copy,与表student_info完全相同。 create table linkai_studentsdb.st_copy select * from linkai_studentsdb.student_info; -- (3)以用户账号st_01连接MySQL服务器,删除表st_copy。 drop table linkai_studentsdb.st_copy; -- 7. 撤消用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。 REVOKE CREATE, DROP, SELECT, INSERT ON linkai_studentsdb.* from st_01@localhost; -- 8. 撤消用户账号st_01所有权限. REVOKE ALL PRIVILEGES, GRANT OPTION FROM st_01@localhost; -- 9. 使用studentsdb数据库中的student_info表。 -- (1)创建本地机角色student。 (注意:角色管理需要 MySQL8.0以上的版本才支持) create ROLE 'student'@'localhost'; -- (2)授予角色student查询student_info表的权限。 ## 表级权限 GRANT SELECT ON TABLE linkai_studentsdb.student_info TO 'student'@'localhost'; -- (3)创建本地机用户账号st_02,密码为123。 CREATE USER st_02@localhost identified BY '123'; -- (4)授予用户账号st_02角色student的权限。 GRANT 'student'@'localhost' TO st_02@localhost; -- 同时记得激活角色 SET global activate_all_roles_on_login = ON; -- (5)以用户账号st_02连接MySQL服务器,查看student_info表信息。 SELECT * FROM linkai_studentsdb.student_info; -- (6)撤消用户账号st_02角色student的权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'student'@'localhost'; -- (7)删除角色student。 DROP ROLE 'student'@'localhost'; -- 10.删除用户账号st_01、st_02。 DROP USER st_01@localhost, st_02@localhost; ```

宝塔面板安装MySQL数据库遇到的坑

  • 2020-05-20
  • 145
  • 数据库设计
今天由于在写 MySQL 安全性方面的实验报告,关于角色管理这一个内容,低版本的 MySQL 数据库不支持,为了方便,直接就用宝塔面板安装了一个 MySQL8.0。 原本以为会很顺利,但是中间确遇到了点小问题,特此记录一下。 ## 遇到问题 我安装成功之后,尝试使用 Navicat 连接数据库,但是却报了一个 1045 错误,如下图所示: ![](http://image.linkaiblog.top/宝塔MySQL_2020052015315431SS.png) 网上查了一下说是不允许远程主机访问,实际上也比较容易解决。 ## 解决方案 稍微熟悉 MySQL 的同学应该知道,安装完数据库后,默认是有一个 root 用户的,root 用户有最高的权限,可以创建一些用户并赋予权限。 而记录用户的一些信息是放在 mysql数据库(具体的一个数据库)的 user 表里面的。 我们可以使用 Xshell 连接服务器,使用 root 用户登录 MySQL 服务,查看 mysql 数据库里面的 user 表的具体内容。 ## 连接 MySQL进行操作 ### 使用 root 用户进行登录 ``` mysql -u root -p123456 ``` - -u 后面输入用户名 - -p 后面输入密码(如何使输入明文密码不需要空格) - 如果想输入密码是不显示密码,则在最后直接输入 -p,然后会提示输入密码 `mysql -u root -p` ### 查看 mysql 数据库(是以具体的数据库)中的 user 表中的字段 **为了方便理解,我先给出一张成功修改之后的截图** ![](http://image.linkaiblog.top/宝塔MySQL_2020052016214021SS.png) 下面进行具体的操作 ``` use mysql; ## 切换到 mysql 数据库 select Host, User from user; ## 查询Host,User 字段 ``` ![](http://image.linkaiblog.top/宝塔MySQL_202005201602500SS.png) 发现 root 用户对应的 Host 字段默认值是 localhost,表明只允许本地连接,我们需要对其进行修改 ### 修改 Host 字段,然后刷新一下 ``` update user set Host = '%' where User = 'root'; ## 修改之后记得刷新一下 flush privileges; ``` ![](http://image.linkaiblog.top/宝塔MySQL_2020052016562856SS.png) 这里的 % 是一个通配字符,表示允许任意的 IP 进行连接。 `flush privileges;` 用来刷新MySQL系统权限的相关表 ## 再次连接,连接成功 完成上面的操作之后,再次使用 Navicat 进行连接,连接成功! ![](http://image.linkaiblog.top/宝塔MySQL_202005201663706SS.png)

《数据库原理及应用》课后实验5_存储过程和存储函数

  • 2020-05-20
  • 140
  • 数据库设计
前言:这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结,相当于作为**数据库系统**课程的期末复习吧! ## 实验5_存储过程和存储函数 > 实验需要掌握知识点 - 掌握 CREATE PROCEDURE 创建存储过程的方法 - 掌握 CALL 调用存储过程的方法 - 掌握 ALTER PROCEDURE 修改存储过程的方法 - 掌握 DROP PROCEDURE 删除存储过程的方法 - 掌握 CREATE FUNCTION 创建存储函数的方法 - 掌握 ALTER FUNCTION 修改存储函数的方法 - 掌握 DROP FUNCTION 删除存储函数的方法 > 具体 SQL 代码 ``` # 1. 输入以下代码,创建存储过程stu_info,执行时通过输入姓名,可以查询该姓名的学生的各科成绩。 DELIMITER @@ CREATE PROCEDURE stu_info(IN name CHAR(8)) BEGIN SELECT s.s_number,s_name,g.c_number,g_score FROM student_info s,grade g WHERE s.s_number=g.s_number and s_name=name; END @@ -- 使用CALL命令执行存储过程stu_info,其参数值为'张青平'。 DELIMITER ; CALL stu_info('张青平'); -- 2. 使用studentsdb数据库中的student_info表、curriculum表、grade表。 -- (1)创建一个存储过程stu_grade,查询学号为0001的学生的姓名、课程名称、分数。 delimiter@@ create procedure stu_grade(in number char(4)) BEGIN SELECT s_name, c_name, g_score FROM student_info s, grade g, curriculum c WHERE s.s_number = g.s_number AND g.c_number = c.c_number AND s.s_number = number; END@@ -- (2)调用存储过程stu_grade。 CALL stu_grade('0001'); -- 3. 使用studentsdb数据库中的student_info表、curriculum表、grade表。 -- (1)创建存储过程stu_name,当任意输入一个学生的姓名时,查看其课程的最高分、最低分、平均分。 delimiter@@ create procedure stu_name(in name char(8)) BEGIN SELECT MAX(g_score) AS 最高分, MIN(g_score) AS 最低分 , AVG(g_score) AS 平均分 FROM student_info s, grade g WHERE s.s_number = g.s_number AND s.s_name = name; END@@ -- (2)调用存储过程stu_name。 CALL stu_name('张青平'); -- (3)删除存储过程stu_name。 drop procedure stu_name; -- 4. 使用studentsdb数据库中的grade表。 -- (1)创建一个存储过程stu_g_r,当输入一个学生的学号时,通过返回输出参数获取该学生选修课程的门数。 delimiter@@ create procedure stu_g_r(in number1 char(4), out number2 int) BEGIN select COUNT(c_number) 课程门数 from student_info s, grade g WHERE s.s_number = g.s_number AND s.s_number = number1; END@@ -- (2)执行存储过程stu_g_r,输入学号 CALL stu_g_r('0004', @c_number2); -- (3)显示学生的选课门数。 SELECT @c_number2; drop procedure stu_g_r; -- 5. 使用studentsdb数据库中的curriculum表、grade表。 -- (1)创建一个存储函数num_func,统计指定课程名称的选课人数。 -- (2)执行存储函数num_func,查看“C语言程序设计”选课人数。 delimiter@@ create procedure num_func(in name char(20)) BEGIN SELECT COUNT(s_number) FROM curriculum c, grade g WHERE c.c_number = g.c_number and c.c_name = name; END@@ CALL num_func('C语言程序设计'); DROP procedure num_func; -- 6. 使用studentsdb数据库中的curriculum表、grade表。 -- (1)创建一个存储函数avg_func,通过游标统计指定课程的平均分。 delimiter@@ create function avg_func(name varchar(50)) RETURNS decimal ## 注意:这里是 returns ,后面还有一个 s BEGIN declare v_avg decimal; ## 声明平均分变量 DECLARE avg_cur CURSOR FOR SELECT avg(g_score) from curriculum c, grade g WHERE c.c_number = g.c_number and c.c_name = name; OPEN avg_cur; ## 打开游标 FETCH avg_cur INTO v_avg; ## 提取数据 CLOSE avg_cur; ## 关闭游标 RETURN v_avg; ## 函数返回 END@@ -- (2)执行存储函数avg_func,查看“C语言程序设计”课程平均分。 SELECT avg_func('C语言程序设计') 课程平均分; -- (3)删除存储函数avg_func。 DROP FUNCTION avg_func; ```

《数据库原理及应用》课后实验4_数据完整性

  • 2020-05-18
  • 140
  • 数据库设计
前言:这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结,相当于作为**数据库系统**课程的期末复习吧! ## 实验4_数据完整性 > 实验需要掌握知识点 - 使用 CREATE TABLE 定义约束的方法 - 使用 ALTER TABLE 增加或删除约束的方法 - 了解约束的各种类型 - CREATE TRIGGER 创建触发器的方法 - 引发触发器的方法 - 使用 DROP TRIGGER 删除触发器的方法 > 实验中要求创建的 3 张表分别如下图所示 ![表1](http://image.linkaiblog.top/数据库原理_2020051815591059SS.png) ---- ![](http://image.linkaiblog.top/数据库原理_2020051815501150SS.png) ----- ![](http://image.linkaiblog.top/数据库原理_2020051815141214SS.png) > 具体 SQL 代码 ``` -- 1. 创建 stu 表 create table stu( s_number char(4) not null unique primary key, s_name char(8), s_gender char(2), s_birthday date ); -- 2. 注意:在 MySQL 下 CHECK 约束可以设置但是不起作用 create table sc ( s_number char(4) not null, c_number char(4) not null, grade decimal(5,2) check (grade >= 0 and grade <= 100), -- 外键定义写在后面 primary key (s_number, c_number) constraint fk_sno foreign key (s_number) references stu(s_number), ); -- 3. 创建 course 表 create table course ( c_number char(4) not null, c_name char(20), c_score int, constraint uq_cname unique(c_name) ); -- 4. 在course表的课号列建立主键约束 alter table course add primary key(c_number); -- 5.在sc表的课号列建立外键约束fk_cno,参照course表的课号列的取值,要求实现级联更新。 ALTER TABLE sc ADD CONSTRAINT fk_cno FOREIGN KEY (c_number) REFERENCES course (c_number) ON DELETE CASCADE ON UPDATE CASCADE; -- 6. 在stu表的姓名列建立唯一约束名uq_sname。 alter table stu add constraint uq_sname unique(s_name); -- 7. 在course表的学分列建立检查约束ck_xf,检查条件为学分>0。 (在 MySQL 中检查约束不会生效) alter table course add constraint ck_xf check(grade > 0); -- 8. 删除sc表的外键约束fk_cno,fk_sno。 alter table sc drop foreign key fk_sno; alter table sc drop foreign key fk_cno; -- 9. 删除stu表的主键约束。 alter table stu drop primary key; -- 10.删除course表的唯一约束uq_cname。 (注意:删除唯一性约束使用的是 drop index 有一点不一样的地方) alter table course drop index uq_cname; -- 11.创建测试表test,包含一个字段date_time,字段类型varchar(50); -- 创建触发器test_trig,实现在stu表中每插入一条学生记录后,则自动在test表中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。 -- 为stu表插入一条记录引发触发器,查看test表的内容。 create table test( date_time varchar(50) ); CREATE TRIGGER test_trig AFTER INSERT ON stu FOR EACH ROW INSERT INTO test VALUES (SYSDATE()); insert into stu values('666', 'JackLin', 'M', '2020-04-09'); -- 12.在course表上创建触发器del_trig,当course表上删除一门课程时,级联删除sc表该课程的记录。 -- 删除course表的一条记录,查看sc表相应记录是否被自动删除。 CREATE TRIGGER del_trig AFTER DELETE ON course FOR EACH ROW DELETE FROM sc WHERE course.c_number = sc.c_number; delete from course where c_number = '111'; SELECT * FROM sc ; ```

《数据库原理及应用》课后实验3_索引与视图

  • 2020-05-18
  • 141
  • 数据库设计
前言:这个系列的的文章主要是关于 **《数据库原理及应用》(MySQL版)-清华大学出版社** 的课后实验报告的总结,相当于作为**数据库系统**课程的期末复习吧! ## 实验3_索引与视图 > 实验需要掌握知识点 - 使用 CREATE INDEX 创建索引 - 使用 DROP INDEX 删除索引 - 使用 CREATE VIEW 创建视图 - 使用 ALTER VIEW 修改视图 - 了解删除视图的 SQL 语句 DROP VIEW 的用法 > 具体实验代码 ``` -- 1. 使用SQL语句ALTER TABLE分别删除studentsdb数据库的student_info表、grade表、curriculum表的主键索引。 alter table student_info drop primary key; alter table curriculum drop primary key; alter table grade drop primary key; -- 2. 使用SQL语句为curriculum表的课程编号创建唯一性索引,命名为cno_idx。 create unique index cno_idx on curriculum(c_number); -- 3. 使用SQL语句为grade表的“分数”字段创建一个普通索引,命名为grade_idx。 create index grade_idx on grade(g_score); -- 4. 使用SQL语句为grade表的“学号”和“课程编号”字段创建一个复合唯一索引,命名为grade_sid_cid_idx。 create unique index grade_sid_cid_idx on grade(s_number, c_number); -- 5. 查看grade表上的索引信息。 show index from grade; -- 6. 使用SQL语句删除索引grade_idx。再次查看grade表上的索引信息。 alter table grade drop index grade_idx; show index from grade; -- 7. 使用SQL语句CREATE VIEW建立一个名为v_stu_c的视图,显示学生的学号、姓名、所学课程的课程编号,并利用视图查询学号为0003的学生情况。 CREATE VIEW v_stu_c AS SELECT s.s_number, s.s_name, g.c_number FROM student_info s, grade g WHERE s.s_number = g.s_number; select * from v_stu_c where s_number = '0003'; -- 8. 基于student_info表、curriculum表和grade表,建立一个名为v_stu_g的视图,视图包括所有学生的学号、姓名、课程名称、分数。使用视图v_stu_g查询学号为0001的学生的课程平均分。 CREATE VIEW v_stu_g AS SELECT s.s_number, s.s_name, c.c_name, g.g_score FROM student_info s, curriculum c, grade g WHERE s.s_number = g.s_number AND g.c_number = c.c_number; SELECT AVG(g_score) AS 'averageScore' FROM v_stu_g WHERE s_number = '0001'; -- 9. 使用SQL语句修改视图v_stu_g,显示学生的学号、姓名、性别。 ALTER VIEW v_stu_g AS SELECT s_number, s_name, s_gender FROM student_info; -- 10.利用视图v_stu_g为student_info表添加一行数据:学号为0010、姓名为陈婷婷、性别为女。 INSERT INTO v_stu_g (s_number, s_name, s_gender) VALUES ('0010', '陈婷婷', '女'); select * from v_stu_g where s_number = '0010'; -- 11.利用视图v_stu_g删除学号为0010的学生记录。 delete from v_stu_g where s_number = '0010'; -- 12.利用视图v_stu_g修改姓名为张青平的学生的高等数学的分数为87。 UPDATE grade SET g_score = 87 WHERE s_number = ( SELECT s_number FROM v_stu_g WHERE s_name = '张青平' ) AND c_number = ( SELECT c_number FROM curriculum WHERE c_name = '高等数学' ); -- 13.使用SQL语句删除视图v_stu_c和v_stu_g。 drop view v_stu_c, v_stu_g; ```