数据库实验报告

[TOC]

这个主要是将本学期，的实验报告进行一个汇总

实验二：数据库的创建和使用操作

一、实验目的

1.安装配置MySQL workbench或者sqlyog客户端，并实现服务的连接。

2.学习mysql workbench 的操作。

3.使用mysql workbench创建school表格，并熟悉对简单数据的录入。

二、实验要求

1.成功运行workbench。

2.创建表格并实现对数据的录入。

三、实验过程

1.运行workbench。

新建一个数据库：在左侧的界面中点击schemas，在空白处右键，创建一个新的数据库。

可以命名为school。新建表，点击apply，即为成功创建一个新的表。在左侧菜单内可以找到自己新建的表。打开表，可以在表格中输入自己想要的数据。

其中，数据库名，表名，表头名均不允许用中文。数据库中表格数据的大写选项解释如下：

NN：not null 非空 UQ：unique 唯一索引 不许重复 BIN：binary 二进制数据(比text更大的二进制数据) UN：unsigned 无符号 整数（非负数） ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001 AI：auto increment 自增 G：generated column 生成列

实验三：数据库常见错误的试错与分析

一、【实验目的】

1.学会建立简单的数据库表格，并对常见出现的错误进行试错与分析。

2.学会限制数据的取值范围。

二、【实验要求】

1.进行对表格数据的试错。

2.认识在试错时候出现的错误。（数据类型错误，key键的是否唯一，数据的取值是否在规定的范围内等）

3.学会用外部键处理数据。

三、【实验过程】

一、数据类型错误，key不是唯一，或数据超过规定的长度：都是常见的数据库错误。

二. 限制数据的范围：

用外部键进行数据取值的限制，在sqlyog中实现这一操作。关系外键一般的取名原则为：fk（代表外键）_引用列名_被引用列名。

实验四：数据库的导入和导出

一、实验目的

1. 掌握数据库的导入和导出相关知识
2. 练习并使用cheak约束完成实验

二、实验要求

(1) 使用MySQL创建数据库表

(2) 简单使用Data Export,Data import 练习导入和导出数据库

(3) 尝试使用sqlyog对数据库进行cheak约束。

三、实验过程

1. 建立数据库：创建具有三张表格的school数据库
2. 找到Administration选项卡下的Data Export选项，选中创建好的school数据库，依次使用Export to Dump Project Folder和Export to self-Contained File验证实验结果，在使用Export to self-Contained File时生成一个.sql文件，在使用Export to Dump Project Folder生成一个文件夹。
3. 使用Data import练习导入数据库
   • 选中数据库drop，删库。
   • Data import 分别选中两种导入方式导入数据库，刷新发现数据库被成功导入，名字仍然叫做school。
   • 我们可以看到在导入和导入后的数据库信息不回发生改变。
4. 在SQLyog里，创建一张新表可以对其添加约束条件，我们可以使用sql语句使得，表格中部分选项数据设置一定的范围，在后续的使用中，如果写入数据不满足约束的话，系统会报错而不会执行，这是因为在添加约束时的约束项限制了我们可以导入的数据范围。
5. 另外的，在不同版本的sqlyog中对这一操作的兼容不同，存在不少的Mysql版本并不支持执行约束。
   • 比如MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告，但是MySQL 8.0中可以使用check约束了。

四、实验反思:

在实验的过程中，我使用的sqlyog版本并不支持，check约束，我无法添加check约束，在解决这个问题的过程中，我查找这些问题，在他人的博客上，说check约束，在MySQL中只是作为一般的注释，对数据库没有约束功能。但是在借用同学计算机使用后，发现check语句能够对MySQL数据库进行约束，在通过了查阅很多资料后。

确认了在不同版本的sqlyog中对这一操作的兼容不同，存在不少的Mysql版本并不支持执行约束。

比如MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告，但是MySQL 8.0中可以使用check约束了。

实验五：使用SQL语句创建和使用数据库

一、实验目的

1. 掌握SQL语句实现数据库的创建和使用

二、实验要求

1. 使用MySQL创建数据库表
2. 简单使用Data Export,Data import 练习导入和导出数据库
3. 尝试使用sqlyog对数据库进行cheak约束。

三、实验过程

代码语言：javascript复制

DROP DATABASE `school` ;
-- 删除原有数据库 school
CREATE SCHEMA `school` 
DEFAULT CHARACTER SET utf8 
COLLATE utf8_bin ;
-- 创建新数据库 school
use school;
-- 选择使用school数据库，避免修改默认数据库sys
CREATE TABLE student (
 sno CHAR(10) NOT NULL,
 cno VARCHAR(45) NOT NULL,
 sage INT NULL DEFAULT 18,
 PRIMARY KEY (sno))
COMMENT = '这是一个学生数据库';
-- 创建第一张student表
create table course(
cno char(4) not null primary key,
cname varchar(45) not null,
cpno char(4) references cno,
credit tinyint
);
-- 创建第二张course表
create table sc(
sno char(10),
cno char(4),
score int,
PRIMARY key(sno,cno),
foreign key(sno) references student(sno),
foreign key(sno) references course(cno)
);
-- 创建第三张sc表并设置两个外键

四、拓展学习：

MySQL 外键(FOREIGN KEY)用法：

表与表的记录之间存在着三种关系：一对多、多对多、一对一的关系。处理表之间关系问题就会利用到FOREIGN KEY

约束1：在创建表时，先建被关联的表dep，才能建关联表emp

约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp

约束3：更新与删除都需要考虑到关联与被关联的关系。

2.MySQL中支持的编码方式：

整形：

tinyint(m)	1个字节表示(-128~127)
smallint(m)	2个字节表示(-32768~32767)
mediumint(m)	3个字节表示(-8388608~8388607)
int(m)	4个字节表示(-2147483648~2147483647)
bigint(m)	8个字节表示( -9.22*10的18次方)

浮点型：

float(m,d)	单精度浮点型，8位精度(4字节),m是十进制数字的总个数， d是小数点后面的数字个数。
double(m,d)	双精度浮点型，16位精度(8字节)

字符型:

char(n)	固定长度的字符串，最多255个字符
varchar(n)	固定长度的字符串，最多65535个字符
tinytext	可变长度字符串，最多255个字符
text	可变长度字符串，最多65535个字符
mediumtext	可变长度字符串，最多2的24次方-1个字符
longtext	可变长度字符串，最多2的32次方-1个字符

char和varchar：

1.均可以经过指定n，来限制存储的最大字符数长度，char(20)和varchar(20)将最多只能存储20个字符，超过的字符将会被截掉。n必须小于该类型容许的最大字符数。

2.char类型指定了n以后，若是存入的字符数小于n，后面将会以空格补齐，查询的时候再将末尾的空格去掉，因此char类型存储的字符串末尾不能有空格，varchar不受此限制。

3. 内部存储的机制不一样。char是固定长度，char(4)无论是存一个字符，2个字符或者4个字符（英文的），都将占用4个字节，varchar是存入的 实际字符数 1个字节（n<=255）或2个字节(n>255)，因此varchar(4),存入一个字符将占用2个字节，2个字符占用3个 字节，4个字符占用5个字节。

4.char类型的字符串检索速度要比varchar类型的快。

varchar和text：

1.都是可变长度的，最多能存储65535个字符。

2.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数 1个字节（n<=255）或2个字节(n>255)，text是实际字符数 2个字节。

3.text类型不能有默认值。

4.varchar可直接建立索引，text建立索引要指定前多少个字符。查询速度varchar要快于text,在都建立了索引的状况下，text的索引好像没起做用，参见这篇文章：http://forums.mysql.com/read.php?24,105964,105964

日期时间类型(date,time,datetime,timestamp)

date	日期’2008-12-2’
time	时间’12:25:36’
datetime	日期时间’2008-12-2 22:06:44’
timestamp	不固定

1. timestamp比较特殊，若是定义一个字段的类型为timestamp，这个字段的时间会在其余字段修改的时候自动刷新。因此这个数据类型的字段能够存放这条记录最后被修改的时间，而不是真正来的存放时间。

二进制数据(xxxBlob)：

1. XXXBLOB和xxxtext是对应的，不过存储方式不一样，xxxTEXT是以文本方式存储的，若是存储英文的话区分大小写，而xxxBlob是以二进制方式存储的，不区分大小写。
2. xxxBlob存储的数据只能总体读出。
3. xxxTEXT能够指定字符集，xxxblob不用指定字符集。

定点数(decimal)：

1. decimal(m,d) 定点类型,浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为065，d小数点右侧数字的个数，范围为030，但不得超过m。
2. 对定点数的计算能精确到65位数字。

除此之外还有，空间数据类型如: Geometry :可以存储所有的几何类型，Point:简单点，LINESTRING : 简单线，POLYGON:简单面，MULTIPOINT ：多点，MULITILINESTRING: 多线，MUILITIPOLYGON： 很多方面，GEOMETRYCOLLECTION:任何几何集合

实验六：MySQL语句实现完整性约束

一、实验目的

1.使用mysql语句实现完整性约束。

二、实验要求

1.学习了解MySQL的各种约束

2.学会添加删除约束。

三、实验过程

默认值=缺省值=default value

id int default 1

使用列级约束：把约束的内容定义在列声明之后

表级约束：把约束定义放在所有列声明之后。

约束种类：

按照约束定义的种类来划分：

表级和列级约束。两者声明的方式不同，在所有列级约束声明完以后，在最后一行声明表级约束。

按照约束的内容来划分：

default: 默认约束, 域完整性 not null: 非空约束，域完整性 unique: 唯一约束，实体完整性 primary key: 主键约束，实体完整性 foreign key: 外键约束，参照完整性 check: 检查约束（MySQL不支持），域完整性 auto_increment: 自增长约束 unsigned: 无符号约束 zerofill: 零填充约束

按照作用的范围来划分：

域完整性（对数据表中字段属性的约束） 实体完整性（通过主键约束和候选键位数来实现的） 参照完整性（不仅管理自己表的范围，而且涉及到跟别的表的范围，范围很大，MySQL的外键）

复合约束：两个值不能同时一样。a引用b先创建b，先创建被引用的表。 实验七：SQL语句-数据查询

一、实验目的

掌握利用sql语句进行数据的查询操作

二、实验要求

1. 熟练掌握SELECT 语句来查询数据，实现数据的查询操作
2. 熟练掌握单表查询、分组查询、连接查询、嵌套查询和集合查询的查询方式
3. 记录实验中遇到的问题和心得，写出实验报告。

三、实验过程

#SQL：结构化的查询语言 #交互式SQL，嵌入式SQL #select 目标表达式列表 #from 目标关系列表 #where 目标条件列表 -> 条件子句 #order by 排序子句 #group by 分组子句

代码语言：javascript复制

use school;
select * from student;
select sno,sname from student;
#重命名
select sno as '学号', sname as '姓名' from student;
#as和单引号可省略，单引号使用防止字符有空格
select sno 学号,sname as '姓  名' from student;select 2022-sage '出生年份' from student;
#不加'出生年份'以'2022-sage'作为列名
select 2022-sage  from student;select cno from course;
#可跟函数
select lower(cno) from course;select upper(cno) from course;
#聚集函数/统计查询
select count(*) from student;select count(*) as '班级人数' from student;
#单条件查询where
select sno,sname,ssex from student where ssex='女';select sno,sname,ssex,sage from student where sage<=30 and ssex='男';
select sno,sname,ssex,sage from student where sage<=30 or ssex='男';
#'男'不要加空格,虽然有些软件可以自动去两边空格,仅侥幸
select * from student where sdept='计算机系' or sdept='历史系';
#减短查询：查询集合
inselect * from student where sdept in ('计算机系','音乐系','历史系');select * from student where sage>=23 and sage<25;
#[23,25)
select * from student where not sage>=23 and sage<25;select * from student where sage between 23 and 25;
#并，闭区间[23,25]，多条件无法实现 
#一个for语句遍历所有的行，where为真true才被选中
#数据库中第三种状态：除了真假，还有未知
select * from course;select * from course where not cpno='C001'; 
#为空未被选中
select * from course where cpno!='C001'; 
select * from course where cpno=null;
#无法比较……得到未知
select * from course where cpno is null;
select * from course where cpno is not null;
#方式一select * from course where not (cpno is null);
#方式二#只限制前三条查询：起始下标0,偏移量3（网页分页查询使用）
select * from student where ssex='男' limit 0,3;
#所有的人按系科排序（默认：升序排序asc）-> 字符集utf8 排序规则
utf8_binselect * from student order by sdept desc;
select * from student order by sdept desc, sage asc;
#放最后执行mysql

实验八：分组查询与模糊查询

一、实验目的

1. 使用sql语句实现分组查询与模糊查询

二、实验要求

1. 学会使用group by having条件分组查询
2. 学会使用like模糊查询语句
3. 学会使用% _通配符与escape的使用

三、实验过程

1. Mysql like %通配符有% 下划线_和escape三种
2. Mysql like区分大小写：匹配字符时，默认是不区分大小写，如果需要取区分大小写，则需要加入binary操作符，严格区分大小写，按照二进制进行比较
3. 当like匹配时加上binary操作符后，则会严格区分英文大小写，因此当检索的内容是中英文混合，则许哟啊忽略英文大小写，使用UPPER(),LOWER()或CONCAT()函数:UPPER()函数将引文字符串变成大写，CONCAT()：将多个字符串连接成一个字符串
4. 如果要找出既含有又含有但是不区分顺序的时候可以使用and条件
5. escape通配符：当既需要查%又需要查_时，不能作为通配符使用的时候，使用escape，转义字符后面的%或_就不作为通配符，注意钱买你没有转义字符的%和_仍然可以起到通配符的作用

实验代码：

代码语言：javascript复制

use school;
select * from student order by sdept asc,sage desc;
select * from course where cpno=null;
#null和任何数字比较得到的结果都是unknown。
select * from course where cpno is null;
select upper(cno),cname from course;
#查阅mysql手册查看各种功能函数
#统计查询
select count(*) from student 
group by sdept having 最小年龄>=20;
#having是对分组进行筛选alter，having必须在group by后面。可以使用别名进行筛选，但是应该用变量名
#如果重命名后的变量名有空可以用一对``包括
#模糊查询
select * from student where sname like '刘%';
select * from course where cname like 'C__程序设计%';
select * from course where cname like 'C_ _程序设计%';
#的作业是用来转义的，可以使用escape，规定转义字符，如果在查询内容中有,就应该使用两个\进行转义
select * from course where cname like  'C#__程序设计%'
escape "#";

实验九：多表查询的链接查询

一、实验目的

1.多表查询的连接查询

2.多表查询的嵌套查询:不相关子查询

二、实验要求

学习多表查询的用法，并自主拓展学习

三、实验过程

代码语言：javascript复制

#一、单表查询：单表查询，条件查询， 
#3.is null，is not null，
#4.between and ， 
#5.count(*),min(*)
#6.目标表达式：重命名，列名、运算、函数;
#7.分组查询 group by  与having子句
#8.模糊查询  like escape ""
#9.排序 order by someone asc/desc
#select 目标表达式列表
#from 目标关系列表
#where 目标条件列表
#group by 分组列列表
#order by 排序列列表

#多表查询 #1.连接查询 #最简单链接：笛卡尔积(交叉连接)cross join #内连接inner join…on:一般连接，等值连接，自然连接(公共属性上的等值连接)nature join #外连接：左外连接left join…on，右外连接right join…on，全外连接full join #2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询) #不相关子查询(干完就跑路)in(…) #一个值 ‘=’；多个值 ‘in’或’=ANY’ #sname<>”刘备”:*不包括刘备 #”> ALL” == > max 大于所有的 #”> ANY” == >min 大于某一个 P92

#3.集合查询

待续

代码语言：javascript复制

use school;
#单表查询 9'：
#简单查询、条件查询、空值查询(is null/is not null)、
#between and、count(*) min(*)，目标表达式(列名、重名、运算、函数)、
#分组查询(group by...having)、模糊查询(like，escape，"\")、排序字句(order by...asc/desc)
#select 目标表达式列表 from 目标关系列表 where 目标条件列表(and,or,not分割) group by 分组列 order by 排序依据列 排序方式
select * from sc;
select sno from sc where cno="C003";
#找出选修C001也选修C003的同学学号
select sno,cno from sc where cno="C001" and cno="C003";#错的
(select sno,cno from sc where cno="C001") union (select sno,cno from sc where cno="C003");
select sno from sc where cno="C001" and 
sno in (select sno from sc where cno="C003");
select sc.sno,sname,sc.cno,cname,grade from student,course,sc where student.sno=sc.sno and sc.cno=course.cno and sc.cno="C003";
#多表查询
#1.连接查询
select * from student,sc where student.sno=sc.sno;
select sno,sname from student,sc where student.sno=sc.sno and cno="C003";#Error Code: 1052. Column 'sno' in field list is ambiguous
select student.sno,sname from student,sc where student.sno=sc.sno and cno="C003";
select sc.sno,sname from (student inner join sc on student.sno=sc.sno) where cno="C003";
#最简单链接：笛卡尔积(交叉连接)cross join
select * from student,sc;
select * from student cross join sc;
#内连接inner join...on:一般连接，等值连接，自然连接(公共属性上的等值连接)nature join,
select * from student inner join sc;
select sc.sno,sname from (student inner join sc on student.sno=sc.sno) where cno="C003";
select * from student natural join sc;#只剩一行sno
select * from student natural join student;#Error Code: 1066. Not unique table/alias: 'student'
select * from student A natural join student B;#自己与自己自然连接，先相等再去掉一份

#外连接：左外连接left join...on，右外连接right join...on，全外连接full join
select * from student left join sc on student.sno=sc.sno;
select * from sc right join student on student.sno=sc.sno;
select * from student full join sc on student.sno=sc.sno;#Error Code: 1054. Unknown column 'student.sno' in 'on clause'
select * from student full join sc ;
#2.嵌套查询:不相关子查询(干完就跑路)、相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询)
#不相关子查询(干完就跑路)
select sno,sname from student where
sno in (select cno from sc where cno="C003");
select sname from student where sdept=(select sdept from student where sname="刘备");#一个值 ‘=’；多个值 ‘in'或'=ANY'
select sname from student where sname<>"刘备" and sdept=(select sdept from student where sname="刘备");#sname<>"刘备" 不包括刘备
select * from student where sdept<>"计算机系" and sage>ANY (select sage from student where sdept="计算机系");
#"> ALL" == > max 大于所有的
#"> ANY" == >min 大于某一个 P92
select * from student where sage>=(select max(sage) from student where sdept ='计算机系');
#等价于
select * from student where sage>=all(select sage from student where sdept ='计算机系');
select * from student where sage<any(select sage from student where sdept ='计算机系');
#小于any等价于小于最大值
#3.集合查询
(select sno,cno from sc where cno="C001") union (select sno,cno from sc where cno="C003");

实验十：相关子查询

一、实验目的

1.多表查询的嵌套查询:相关子查询

二、实验要求

学习多表查询的用法，并自主拓展学习

三、实验过程

#嵌套查询

#1.不相关子查询

#2.相关子查询(本节课任务)

相关子查询(交错互通，内层查询都要看一下外层查询，外层查询都要看一下内层查询)，相关子查询的时间复杂度远大于不相关子查询。

代码语言：javascript复制

select sname from student where sname="刘备";
select * from student where sdept=(select sdept from student where sname="刘备") and sname !="刘备";
select * from student a ,student b where a.sname="刘备" and b.sname<>"刘备" and a.sdept=b.sdept;
select b.sname from student a ,student b where a.sname="刘备" and b.sname<>"刘备" and a.sdept=b.sdept;

select * from student X where sname<>"刘备" and exists(select * from student Y where sname ="刘备" and Y.sdept=X.sdept);
-- 以这一查询语句为例使用相关子查询的时间复杂度为n2 n(可以认为为n2的时间复杂度)，而实现相同工能的不相关子查询子需要n的时间复杂度
#EXISTS() 存在谓词,存在为真,反之为假
select sname as "姓名" from student where exists (select * from sc where sno=student.sno and cno ="C003");
#查询选修了全部课程的学生的信息
select * from student where not exists(
select * from course where not exists(
select * from sc where student.sno =sc.sno and sc.cno=course.cno)
);
#查询选修了全部学分为2的课程的学生的信息
select * from student where not exists(
select * from course where credit =2 and not exists(
select * from sc where student.sno =sc.sno and sc.cno=course.cno)
);
#查询选修了学号为202006001选修的全部课程的学生的信息
select sno from sc X where not exists(
select * from sc Y where Y.sno="202006001" and not exists(
se	lect * from sc Z where X.sno =Z.sno and Y.cno=Z.cno)
);
#关系代数在除法的运算在SQL语句中没有对应运算，通过存在谓词表示

实验十一：使用MySQL插入，删除，更新语句对数据库进行操作

一、实验目的

1. 了解和学会插入删除与更新语句:

二、实验要求

1. 正确使用语句，查找实验中的问题并解决

三、实验过程

代码语言：javascript复制

INSERT INTO `school`.`student` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006018', '司马懿', '男', '22', '数学系')

第一次执行该程序，该条数据被正确的传入数据库，而在已经插入一次数据之后再次执行会显示

Error Code: 1062. Duplicate entry ‘202006018’ for key ‘student.PRIMARY’

这里的显示数据其实是，因为主码约束的结果，主码发生冲突。

代码语言：javascript复制

DELETE FROM `school`.`student` WHERE (`sno` = '202006018');

将刚刚输入的数据进行删除，第一次执行删除语句，没有出现问题，继续执行该语句删除操作的返回时间发生改变，返回时间为0，但是删除语句成功执行完成

在SC表中插入司马懿的关联数据

代码语言：javascript复制

INSERT INTO `school`.`sc` (`sno`, `cno`, `grade`) VALUES ('202006018', 'C007', '90'),('202006018', 'C006', '86'),('202006018', 'C005', '89');

再次执行删除语句

代码语言：javascript复制

反馈为Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))

因为有外键约束的存在，sc表中的数据sno依赖于student表中的sno，在这种情况下，数据没有成功被删除

代码语言：javascript复制

DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C005');
DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C006');
DELETE FROM `school`.`sc` WHERE (`sno` = '202006018') and (`cno` = 'C007');

删除表中关于202006018号学生的信息，再次执行删除语句，数据被成功删除

在将学生表在的202006018号学生信息删除后，再次执行

代码语言：javascript复制

INSERT INTO `school`.`sc` (`sno`, `cno`, `grade`) VALUES ('202006018', 'C007', '90'),('202006018', 'C006', '86'),('202006018', 'C005', '89');

代码语言：javascript复制

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))

因为外键约束的存在，student表中没有该学号存在，DBMS会拒绝插入语句的执行。

数据更新相关

代码语言：javascript复制

update sc set grade=grade 10;

发生MySQL发生报错，错误信息为Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

根据报错信息上网搜索相关原因

报错原因：现在是在mysql的safe-updates模式中，如果where后跟的条件不是主键，就会出现这种错误。

知道问题，解决方法就简单了，要么where后面加上主键的条件，要么就更改模式：

代码语言：javascript复制

SET SQL_SAFE_UPDATES = 0;

在语句前面增加 SET SQL_SAFE_UPDATES = 0;语句，增加该语句之后之前的语句可以正常执行了，多次执行之后发现数据超过100，估计是一开始数据库建立的时候没有进行约束。

实验十二：数据库视图的创建与使用

一、实验目的

1. 数据库视图的创建和使用

二、实验要求

1. 了解MySQL数据库视图的创建
2. 使用视图功能及使用

三、实验过程

代码语言：javascript复制

select * from student where sdept="计算机系" and sage > 20;
-- 为其定义视图，as作为关键字必不可少，select语句作为视图的数据来源
create view cs_view as select * from student where sdept="计算机系" and sage > 20;
-- 查询视图，对视图的查询实际上是执行定义中基本表的查询
select * from cs_view;
-- 一个基本表可定义多个视图
create or replace view cs_view as select * from student where sdept ="计算机系";
-- Select 语句中引用了不可更新的视图Where子句中的子查询，引用from子句中的表；视图与表的区别视图是从一个或多个表（或视图）导出的虚拟表。而表有时也称为基本表——base table。即视图所对应的数据不进行实际存储，数据库中只存储视图的的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关的基本表。在information_schema数据库的tables表中有记录。

select * from cs_view;
-- create view cs_course_view as select sc.sno,sname,sc.cno,  from student,sc,course 
create view sage_sdept_view as select avg(sage) ,sdept from student group by sdept;
drop view sage_sdept_view;
select * from sage_sdept_view;
select avg(sage) ,sdept from student group by sdept;
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006011', '赵云', '男', '20', '计算机系');
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006019', '黄忠', '男', '20', '物理系');
select * from cs_view;

create or replace view cs_view as select * from student where sdept ="计算机系" with check option;
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006019', '黄忠', '男', '20', '军事系');
INSERT INTO `school`.`cs_view` (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES ('202006011', '赵云', '男', '20', '计算机系');

可更新视图要通过视图更新基本表的数据，必须保证视图是可更新的，既可以在insert update等语句中当中使用它们。对于可更新的视图，在视图中的行为和基本表中的行之间具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。

使用视图有以下优点：

1为用户集中数据，简化用户的数据查询、修改、删除和更新，2屏蔽数据库的复杂性3简化用户的权限管理，4便于数据共享，5可以重新组织数据以便输出到其他应用程序中；

实验十三：数据库索引的创建和使用

一、实验目的

二、实验要求

1. 了解MySQL数据库索引的创建
2. 使用索引功能及使用

三、实验过程

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

代码语言：javascript复制

CREATE INDEX indexName ON student (ssex);
-- 创建索引
DROP INDEX indexName ON student; 
-- 删除索引
CREATE TABLE mytable(  
ID INT NOT NULL, username VARCHAR(16) NOT NULL,  INDEX indexName (username)  
 );  
-- 创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
-- 唯一索引 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
-- CREATE UNIQUE INDEX indexName ON table_name(column_name) ;
-- 可以使用 SHOW INDEX 命令来列出表中的相关的索引信息
SHOW INDEX FROM student;
-- 使用ALTER 命令添加和删除索引
-- 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 
# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): #添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
# 该语句指定了索引为 FULLTEXT ，用于全文索引。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 G 来格式化输出信息。

实验十四：存储过程及函数的使用实验

一、实验目的

1. 存储过程及函数的使用实验

二、实验要求

1. 了解MySQL数据库存储过程及函数的异同
2. 使用存储过程实现相关操作

三、实验过程

知识导入：

存储过程，函数和触发器之间的区别：

1. 存储过程实现的功能要复杂一些，而函数实现的功能针对性比较强。
2. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用；
3. 触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。

三者的特点：

1. 存储过程的特点
   • 存储过程只在创建时进行编译，以后执行存储过程都不需要再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。
   • 当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete），可将此复杂操作用存储过程封装起来。
   • 可以在过程中调用另一个存储过程。可以再存储过程中调用函数，可以简化一系列复杂语句。
   • 安全性高，可设定只有某用户才具有对指定存储过程的使用权。
   • 参数有三种（In、Out、In Out），可返回多个参数值。
   • 在Oracle中，若干个有联系的过程可以组合在一起构成程序包。
   • 存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
2. 函数的特点
   • 函数只有一种参数（In）,只有一条Return语句，只能返回单一值。
   • 可在SQL语句（DML或Select）中调用函数。由于函数可以返回一个表象，因此它可以在查询语句中位于From关键字的后面。
3. 触发器的特点
   • 触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行

代码语言：javascript复制

USE school ;
DELIMITER $$
CREATE PROCEDURE insert_student (
  p_sno CHAR(10),
  p_sname VARCHAR (50),
  p_sex VARCHAR (1),
  p_age INT,
  p_dept VARCHAR (20)
  -- 在存储过程中定义的变量跟表中数据没有直接联系
  -- 在数据定义时声明长度可以大于原数据库的数据长度，不会报错，但是在数据输入时弱数据大于数据库要求会造成截断。
) 
BEGIN
  # 声明变量info，以表明插入是否成功。
  DECLARE info VARCHAR (20) DEFAULT '插入成功' ;
  # 异常处理
  DECLARE CONTINUE HANDLER FOR 1062 SET info = '插入失败，不能插入重复的数据' ;
  INSERT INTO student 
  VALUES
    (p_sno, p_sname, p_sex, p_age, p_dept) ;
  SELECT 
    info ;
END $$

DELIMITER ;
SET @sno = '202006020' ;
SET @sname = '魏延' ;
SET @ssex = '男' ;
SET @sage = 25 ;
SET @sdept = '计算机系' ;

CALL insert_student (@sno, @sname, @ssex, @sage, @sdept) ;

SELECT * FROM student ;
-- select @@version 查询版本 系统变量 
-- 查询存储过程 触发器 函数 游标的区别与联系
DELIMITER $$
CREATE PROCEDURE search_sc(
p_sno CHAR(10),
p_cno CHAR(10),
OUT p_grade INT
)
BEGIN
	DECLARE info VARCHAR(30) DEFAULT '查找成功';
	DECLARE  CONTINUE  HANDLER  FOR  NOT  FOUND  SET  info='查找失败';
	SELECT  grade  INTO  p_grade  FROM  sc  WHERE  sno=p_sno AND cno=p_cno;
    SELECT info;
END $$
DELIMITER ;
SET @vsno='202006001';
SET @vcno='C001';
CALL search_sc(@vsno,@vcno,@p_grade);
SELECT @p_grade;

# 函数：
SHOW VARIABLES LIKE '%func%';
SET GLOBAL log_bin_trust_function_creators = 1;
-- 第一次使用MySQL的函数功能发生报错1418解决方法如下
-- 1.先查看函数功能是否开启：show variables like '%func%';
-- 2.开启：SET GLOBAL log_bin_trust_function_creators = 1;
-- 3.关闭：SET GLOBAL log_bin_trust_function_creators = 0;
USE school;
DELIMITER $$  
DROP FUNCTION IF EXISTS `get_avgscore`;
-- 在MySQL中在已有定于函数之后，我们无法直接在进行操作，我们需要把原有函数删除。
CREATE  FUNCTION  get_avgscore(f_sno CHAR(10))
RETURNS  FLOAT
BEGIN
      RETURN (SELECT  ROUND(AVG(grade), 2)   
                      FROM  SC  WHERE  sno=f_sno); 
END $$
DELIMITER ; 
SELECT get_avgscore('202006001');

实验十五：触发器与游标的学习与使用

一、实验目的

1. 学习触发器和游标的使用
2. 掌握MySQL数据库对用户与权限的相关操作的原理及方法

二 、实验要求

1. 了解MySQL数据库存储过程及函数的异同
2. 使用存储过程实现相关操作
3. 练习了解MySQL数据库用户权限设置

三、实验过程

知识导入：

触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行。

代码内容：

触发器与游标相关：

代码语言：javascript复制

delimiter $$
create function get_avgscore(f_sno int)
returns float
begin
return(select round(avg(score),2)
from sc
where sno = f_sno);
end $$
delimiter ;
use school;
delimiter $$
create trigger trg_student_delete2
before delete on student
for each row
begin
  delete from sc where sno = old.sno;
end $$
delimiter ;
show triggers from school;
select * from student;
delete from student
where sno = '202006002';
select * from sc;
select * from student;
delimiter $$
create function get_avgscore(f_sno char(2))
returns float
begin
	return(select round(avg(score),2)
          from sc where sno=f_sno);
          -- return后面括号内部的语句最后一条不加分号，类似于select后面的元素，return后面整体的加分号
end $$
delimiter ;
delimiter $$
create function get_avgscore(f_sno char(3))
returns float
begin
	return(select round(avg(score),2)
			from sc where sno=f_sno
      );
end $$
delimiter ;
drop function if exists get_avgscore; -- 删除时函数名后面不加括号
drop function if exists get_avgscore;
drop function if exists get_avgscore;
drop procedure if exists get_avgscore;
drop procedure if exists get_avgscore;
delimiter $$
create procedure sum()
begin
  declare sums3 int;
  declare sums5 int;
  if(sums3 > sums5) then
    	select 's3的总成绩大于s5的总成绩';
	elseif(sums3 < sums5) then
		select 's3的总成绩小于s5的总成绩';
	else
       select 's3的总成绩等于s5的总成绩';
end $$
delimiter ;
use school;
create table if not exists `store` (
    'id' int(11) not null auto_increment,
    'name' varchar(20) not null,
    'count' int(11) not null default '1',
  primary key ('id')
) engine=innodb default charset=latin1 auto_increment=7;

二、用户操作

代码语言：javascript复制

# 1.查看所有用户信息。
SELECT USER,HOST FROM mysql.user;
# 2.创建用户user1,user3，user4密码自定，主机形式分别为localhost、%和你的主机IP地址，并再次查看用户是否创建成功。。
 CREATE USER 'user1'@'localhost' IDENTIFIED BY '1';
 CREATE USER 'user3'@'%' IDENTIFIED BY '3';
 CREATE USER 'user4'@'192.168.82.41' IDENTIFIED BY '4';
 SELECT USER,HOST FROM mysql.user;
# 3.修改用户user4的名字为user5，并修改其密码（自定），其他不变。
RENAME USER 'user4'@'192.168.82.41' TO 'user5'@'192.168.82.41';
ALTER USER 'user5'@'192.168.82.41' IDENTIFIED WITH mysql_native_password BY '5';
RENAME USER 'user5'@'localhost' TO 'user5'@'192.168.82.41';  -- 主机名也可以修改，但本题没要求
SELECT USER,HOST FROM mysql.user;
# 4.删除用户user5。
DROP USER user5@192.168.82.41;
SELECT USER,HOST FROM mysql.user;

– 二、权限操作

1. 在root用户的连接下，授予user1创建数据库，创建用户的权限，创建完成后查看用户user1的权限。

代码语言：javascript复制

SELECT HOST,USER,create_priv,create_user_priv ,grant_priv FROM mysql.user;
GRANT CREATE ON *.* TO user1@localhost;
GRANT CREATE USER ON *.* TO user1@localhost;
SELECT HOST,USER,create_priv,create_user_priv,grant_priv FROM mysql.user; 

1. 建立user1与MySQL数据库的连接。
2. 在user1的连接下创建用户user2（其他自定），创建数据库db1,并查看是否创建成功。

在db1数据库中创建t1和t2表，结构和内容自定。测试能否向表t1中插入数据和查询数据，为什么？

– 以下代码在user1的连接下书写并执行：

代码语言：javascript复制

CREATE DATABASE db1;
  CREATE TABLE t1
  ( tid SMALLINT PRIMARY KEY,
   tname VARCHAR(10));
  CREATE TABLE t2
  ( tid2 SMALLINT PRIMARY KEY,
   tname2 VARCHAR(10));
  INSERT INTO t1 VALUES(1,'Tom');  -- 没有该权限
  SELECT * FROM t1;  -- 没有该权限
  CREATE USER 'user2'@'localhost' IDENTIFIED BY '2';

– 以下代码在root的连接下书写并执行，去验证是否有user2用户。

代码语言：javascript复制

SELECT USER,HOST FROM mysql.user;  -- 查看是否有user2用户 

1. 在root用户的连接下，授予user1对表t1和t2的查询与插入数据的权限，并允许user1传播对t1表和t2表的查询权限。

查看user1的相关权限

代码语言：javascript复制

SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
GRANT INSERT ON db1.* TO user1@localhost;
GRANT SELECT ON db1.* TO user1@localhost WITH GRANT OPTION;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;  -- 查看是否授权成功

1. 在user1的连接下.测试其是否能查询t1和t2表的内容。

代码语言：javascript复制

SELECT * FROM t1;
SELECT * FROM t2; 

1. 在root用户的连接下，授予user1可以授权的权限（grant）

代码语言：javascript复制

GRANT GRANT OPTION ON db1.* TO user1@localhost;

1. 在user1的连接下，将查询t1表的权限授予user2。

代码语言：javascript复制

GRANT SELECT ON db1.t1 TO user2@localhost;

1. 在root用户的连接下，查看user2的权限。

代码语言：javascript复制

SELECT * FROM mysql.tables_priv;

1. 建立user2与MySQL数据库的连接.并测试其是否能查询t1表和t2表的内容，对查询结果进行解释。

代码语言：javascript复制

SELECT * FROM t1;
SELECT * FROM t2;

回收权限

代码语言：javascript复制

#1在root的连接下，回收user1查询db1数据库中所有表的权限授，回收完成后再次查看user1的权限。
REVOKE SELECT ON db1.* FROM user1@localhost;
SELECT HOST,USER,select_priv,insert_priv FROM mysql.db;
#2在user1的连接下，测试其是否还能查询t1和t2表的内容。
SELECT * FROM t1;
SELECT * FROM t2;
#3在user2的连接下，测试其是否还能查询t1表的内容。这说明什么问题？
SELECT * FROM t1;

三、角色管理

1. 在root的连接下，创建角色 role1，主机自定，并在mysql.user中查看创建的角色。

代码语言：javascript复制

CREATE ROLE role1;
SELECT USER,HOST FROM mysql.user;

1. 在root的连接下，给角色 role1授予更新t1表的权限，并查看角色的权限。

代码语言：javascript复制

GRANT UPDATE ON db1.t1 TO role1;
SELECT * FROM mysql.tables_priv;

1. 在root的连接下，将角色role1授予用户user2。

代码语言：javascript复制

GRANT role1 TO user2@localhost;
SELECT * FROM mysql.tables_priv; -- user2还没有这个权限

1. 在user2的连接下，查看自己拥有的角色。

代码语言：javascript复制

SELECT CURRENT_ROLE(); -- 结果为空

1. 在user2的连接下，启用user2上的role1角色，再次查看user2拥有的角色。

代码语言：javascript复制

SET ROLE role1;
SELECT CURRENT_ROLE(); -- 结果有了role1角色

1. 在user2的连接下，修改t1表。

代码语言：javascript复制

SELECT * FROM t1;
UPDATE t1 SET tname='Marry' WHERE tid=1; -- 修改成功
SELECT * FROM t1;

1. 在root连接下，撤销user2的role1角色，并再次修改t1表，测试是否成功，为什么？

代码语言：javascript复制

REVOKE role1 FROM user2@localhost;
-- 在user2的连接下，测试是否还能修改t1表
UPDATE t1 SET tname='Marry2' WHERE tid=1; -- 不成功

1. 在root的连接下，删除角色role1。

代码语言：javascript复制

DROP ROLE role1;

索引 数据库 存储过程 连接 数据

0 人点赞