[大数据]数据库实验记录&教学

数据库实验

准备：配置环境

1.前往官网下载MySQL

首先，点击前往官网下载MySQL。
下载完成后解压，记住解压的路径。

2.配置my.ini文件

在解压后的根目录下创建一个txt文件，重命名为my.ini
这里需要打开显示文件扩展名，否则改的只是名字（就相当于你改成了一个名为my.ini的txt文件）修改方法：打开“我的电脑（此电脑）”点击上方工具栏的【查看】按钮，在【显示/隐藏】模块中勾选“文件扩展名”
复制下面的代码放进my.ini配置文件：
记得修改两处地址

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录（根目录）需要修改！！！
basedir=D:\Develop\mysql-5.7.19-winx64
# 设置mysql数据库的数据的存放目录（需要在根目录自行创建data文件夹）需要修改！！！
datadir=D:\Develop\mysql-5.7.19-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4（包含中文，一般都用这个）
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

3.命令提示符连接mysql

用windows自带的这个左下角的搜索，搜索：“命令提示符”

右键选择以管理员身份运行。
输入下面的指令进入mysql的bin文件夹

cd D:\Develop\mysql-5.7.19-winx64\bin

接着执行初始化命令

mysqld --initialize --console

复制显示的最后root@localhost:后面的为密码（冒号后面有一个空格，不要复制，复制空格后面的所有字符包括括号之类的符号）这个密码一定要复制下来，记在文本文档或者发在聊天里面，后面要用！
接着执行安装命令

mysqld --install mysql

接着启动mysql服务

net start mysql

连接mysql

mysql -hlocalhost -uroot -p

连接后会提示你输入密码，把刚刚保存的密码输入即可。
登陆成功后使用下面的命令修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '新的密码';

4.配置环境变量

还是直接用系统自带的搜素搜素：“环境变量”

点击这个“环境变量”按键。
在下半部分的系统变量中，添加如下变量：

变量值还是改为安装的根目录。
接着找到一个名为Path的系统变量：

双击编辑它，新建两个如下的变量：

至此，mysql已经配置完毕。

实验一：数据定义

创建和使用表、视图

1.创建数据库，命名为学号后三位

2.在个人学号数据库中创建如下的三张表，并输入记录内容：

教师表（JS）

JS数据

课程表（Course）

Course数据

授课表（SK）

SK数据

3.修改表结构
1)在授课表中添加一个授课类别，列名为Type，类型为char(4)
2)将授课表中的Hours的类型改为Smallint
3)删除课程表的Hours列

4.完成以下功能：
1)定义表students，其中字段有：SNO CHAR(10) 、 SNAME CHAR(8) 、 AGE NUMERIC(3,0)、 SEX CHAR(2)、 BPLACE CHAR(20)、Polity CHAR(20)。要求SNO和SNAME不为空，SNO为主键
2)在教师表JS中增加住址列，字段名为ADDR，类型为 CHAR，长度50
3)根据students表，建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL
4)建立一成绩表，表名为score，其中字段有：SNO CHAR(10)，CNO CHAR(10)，score NUMERIC(6,0)，并输入部分记录，内容自定
5.定义索引
1)在数据库中的新建一个数据表，名为js1，结构与js表相同。为js1表创建一个唯一聚集索引，索引字段为Sid，索引名为I_js_sid。
2)为数据库中的course数据表，创建一个复合索引，索引名为I_cource_xf，使用Cno和Credit字段

操作步骤

1.命令提示符连接mysql

mysql -hlocalhost -uroot -p

连接后会提示你输入密码，输入之前改过后的密码。
连接成功后，根据要求创建命名为学号后三位的数据库：（这里我的数据库名为124）

create database 124;

接着输入指令连接124数据库

use 124;

2.使用sql语句完成各种操作

构建教师表（JS）

create table JS(
    Tno varchar(7),
    Tname varchar(10),
    Tsex varchar(2),
    Birthday datatime,
    Dept varchar(20),
    Sid varchar(18)
);

插入教师表数据

INSERT INTO JS VALUES('T001','刘威','女','1971-3-20','电信','551021197103203121');
INSERT INTO JS VALUES('T002','张琪劲','男','1963-7-13','数理','32010119630713318X');
INSERT INTO JS VALUES('T003','李子文','女','1973-9-15','电信','551021197103203121');
INSERT INTO JS VALUES('T004','江海防','女','1960-2-18','社科','560102196002185623');
INSERT INTO JS VALUES('T005','李铁','男','1977-10-11','数理','230103197710118632');
INSERT INTO JS VALUES('T006','吴天一','男','1962-4-23','电信','320104196204237516');
INSERT INTO JS VALUES('T007','赵志华','男','1968-8-27','社科','321102196808277214');
INSERT INTO JS VALUES('T008','钱进','男','1980-7-10','电信','570102198007103452');
INSERT INTO JS VALUES('T009','孙星南','女','1981-3-2','外语','110102198103024125');

构建课程表（Course）

create table Course(
    Cno varchar(10),
    Cname varchar(20),
    Credit smallint,
    property varchar(10),
    Hours int
);

插入课程表数据（这里我们的插入语句没有指出属性名，对于Hours列要明确给出空值）

INSERT INTO Course VALUES('01010101','大学英语1',4,'考试',NULL);
INSERT INTO Course VALUES('01010102','普通物理1',4,'考试',NULL);
INSERT INTO Course VALUES('01010103','高等数学1',6,'考试',NULL);
INSERT INTO Course VALUES('01010104','形势政策',2,'考查',NULL);
INSERT INTO Course VALUES('01010105','计算机基础',4,'考查',NULL);

构建授课表（SK）

create table SK(
    Tno varchar(7),
    Cno varchar(10),
    Hours int
);

插入授课表数据

INSERT INTO SK VALUES('T001','01010105',64);
INSERT INTO SK VALUES('T002','01010102',64);
INSERT INTO SK VALUES('T009','01010101',64);
INSERT INTO SK VALUES('T004','01010104',32);
INSERT INTO SK VALUES('T005','01010103',96);
INSERT INTO SK VALUES('T006','01010105',64);
INSERT INTO SK VALUES('T003','01010101',64);

在授课表中添加一个授课类别，列名为Type，类型为char(4)

ALTER TABLE SK ADD COLUMN Type char(4);

将授课表中的Hours的类型改为Smallint

ALTER TABLE SK ALTER COLUMN Hours Smallint;

删除课程表的Hours列

ALTER TABLE Course DROP COLUMN Hours;

定义表students，其中字段有：SNO CHAR(10) 、 SNAME CHAR(8) 、 AGE NUMERIC(3,0)、 SEX CHAR(2)、 BPLACE CHAR(20)、Polity CHAR(20)。要求SNO和SNAME不为空，SNO为主键

create table students(
    SNO CHAR(10) PRIMARY KEY NOT NULL,
    SNAME CHAR(8) NOT NULL,
    AGE NUMERIC(3,0),
    SEX CHAR(2),
    BPLACE CHAR(20),
    Polity CHAR(20)
);

在教师表JS中增加住址列，字段名为ADDR，类型为 CHAR，长度50

ALTER TABLE JS ADD COLUMN ADDR char(50);

根据students表，建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL

create table GRIL(
    SNO CHAR(10) PRIMARY KEY NOT NULL,
    SNAME CHAR(8) NOT NULL,
    AGE NUMERIC(3,0)
);

建立一成绩表，表名为score，其中字段有：SNO CHAR(10)，CNO CHAR(10)，score NUMERIC(6,0)，并输入部分记录，内容自定

create table score(
    SNO CHAR(10),
    CNO CHAR(10),
    score NUMERIC(6,0)
);
insert into score values('1922106','00001',87);
insert into score values('1922107','00001',98);
insert into score values('1922105','00002',66);

在数据库中的新建一个数据表，名为js1，结构与js表相同。为js1表创建一个唯一聚集索引，索引字段为Sid，索引名为I_js_sid

create table js1(
    Tno varchar(7),
    Tname varchar(10),
    Tsex varchar(2),
    Birthday datatime,
    Dept varchar(20),
    Sid varchar(18)
);
create CLUSTER INDEX I_js_sid ON js1(Sid);

为数据库中的course数据表，创建一个复合索引，索引名为I_cource_xf，使用Cno和Credit字段

CREATE INDEX I_cource_xf on Course(Cno, Credit);

3.导出sql文件

新打开一个命令提示符，进入需要导出的位置

比如我这里选择了桌面
接着输入如下指令导出sql文件

mysqldump -u root -p 数据库名 -P 3306 > 数据库名.sql

输入密码后即可导出，后面的导出同理。

思考与实践

1．创建“图书借阅数据库”。
2．修改“图书借阅数据库”，相关参数自行确定。
3．在“图书借阅数据库”中创建以下四个表，并分别为这四个表写入部分数据，内容自定（提醒：各表数据之间的相关性）
借书人表（JS）

图书表（TS）

出版社表（CBS）

借阅表（JY）

4．在“图书借阅数据库”中创建一个视图Ts_view，要求输出图书的相关信息，包括：书号，书名，出版社名，数量。
5．在“图书借阅数据库”中创建一个视图Jy_view，要求输出图书的相关信息，包括：借书证号，姓名，书名，出版社名，借书日期和还书日期。
6．为“图书借阅数据库”中的四个表分别建立相关索引。

操作步骤

第一步还是连接mysql，不多赘述了。
创建“图书借阅数据库”

create database 图书借阅数据库;

在“图书借阅数据库”中创建以下四个表，并分别为这四个表写入部分数据，内容自定

create table JS(
    Jsno varchar(6) PRIMARY KEY,
    Jname varchar(10),
    Jdw varchar(20)
);
create table TS(
    Tsno varchar(15) PRIMARY KEY,
    Tname varchar(20),
    Tnum smallint,
    Tpos varchar(30),
    Cno varchar(4)
);
create table CBS(
    Cno varchar(4) PRIMARY KEY,
    Cname varchar(20),
    Ctel varchar(12),
    Cyb varchar(6),
    Caddr varchar(40)
);
create table JY(
    Jsno varchar(6) PRIMARY KEY,
    Tsno varchar(15) PRIMARY KEY,
    Jydate datetime,
    Hdate datetime
);
insert into JS values('000001','小明','江苏科技大学');
insert into JS values('000002','小红','江苏科技大学');
insert into JS values('000003','小王','江苏科技大学');
insert into TS values('0000000001','第一本书',10,'江苏镇江','0001');
insert into TS values('0000000002','第二本书',10,'江苏镇江','0001');
insert into TS values('0000000003','第三本书',10,'江苏镇江','0001');
insert into TS values('0000000004','第四本书',10,'江苏镇江','0001');
insert into CBS values('0001','江科大出版社','12345678910','111111','江科大');
insert into JY values('000000','0000000001','2021-10-25','2019-11-26');

在“图书借阅数据库”中创建一个视图Ts_view，要求输出图书的相关信息，包括：书号，书名，出版社名，数量

CREATE VIEW TS_view AS select Tsno,Tname,Cname,Tnum from TS,CBS where TS.Cno=CBS.Cno;

在“图书借阅数据库”中创建一个视图Jy_view，要求输出图书的相关信息，包括：借书证号，姓名，书名，出版社名，借书日期和还书日期

CREATE VIEW Jy_view AS select JS.Jsno,Jname,TS.Tname,TS.Cno,Jydate,Cname,Hdate from JS,TS,CBS,JY where JS.Jsno = JY.Jsno and TS.Tsno=JY.Tsno;

为“图书借阅数据库”中的四个表分别建立相关索引

create INDEX ts_index ON TS(Tsno);
create INDEX jy_index ON JY(Jsno,Tsno);
create INDEX cbs_index ON CBS(Cno);
create INDEX js_index ON JS(Jsno);

实验二：完整性约束

设置完整性约束

1．实验目的与要求
掌握使用企业管理器和查询分析器对数据库进行完整性约束设置
2．实验内容
用企业管理器和查询分析器对数据库进行完整性约束设置
3．实验步骤
数据库的完整性约束设置
对教师表JS、课程表course和授课表SK进行完整性约束设置，要求如下：
教师表（JS）

课程表（course）

授课表（SK）

create table JS(
Tno varchar(7) primary key,
Tname varchar(10) not null,
Tsex varchar(2) check(Tsex in('男','女')),
Birthday date,
Dept varchar(20),
Sid varchar(18) unique
);
create table course(
Cno varchar(10) primary key,
Cname varchar(20) not null,
Credit smallint check(Credit > 0),
property varchar(10) default '必修'
);
create table SK(
Tno varchar(7),
Cno varchar(10),
Hours int check(Hours > 0),
primary key(Tno,Cno),
foreign key (Tno) references JS(Tno),
foreign key (Cno) references course(Cno)
);

思考与实践

对“图书借阅数据库”中的JS、TS、CBS、JY表设置完整性约束。
借书人表（JS）

图书表（TS）

出版社表（CBS）

借阅表（JY）

create table JS(
Jsno varchar(6) primary key,
Jname varchar(10) not NULL,
Jdw varchar(20)
);
create table CBS(
Cno varchar(4) primary key,
Cname varchar(20) not NULL,
Ctel varchar(12),
Cyb varchar(6) check(Cyb like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
Caddr varchar(40)
);
create table TS(
Tsno varchar(15),
Tname varchar(20) not NULL,
Tnum smallint check(Tnum > 0),
Tpos varchar(30),
Cno varchar(4),
primary key(Tsno,Cno),
foreign key (Cno) references CBS(Cno)
);
create table JY(
Jsno varchar(6),
Tsno varchar(15),
Jydate datetime check(Jydate < Hdate or Jydate = Hdate),
Hdate datetime,
primary key(Jsno,Tsno),
foreign key (Jsno) references JS(Jsno),
foreign key (Tsno) references TS(Tsno)
);

实验三：数据操纵

构建表

学生表（Students）

教师表（JS）

授课表（SK）

课程表（Course）

成绩表（Score）

对“教师授课管理数据库”表中数据进行下列查询操作

（1）查询所有男学生的姓名、出生日期。

select Sname,Sbirthday
from Students
where Ssex='男';

（2）查询男女教师的人数。

select Tsex,count(Tno) 
from JS 
group by Tsex;

（3）找出年龄在20～23岁之间的学生的学号、姓名和年龄，并按年龄升序排序。

select Sno,Sname,Sage 
from Students 
where Sage>=20 and Sage<=23
order by Sage ASC;

（4）找出年龄超过平均年龄的学生姓名。

select Sname
from Students 
where AGE>(select avg(age) 
           from Students);

（5）查询成绩不及格的学生信息，包括姓名、学号、课程名和成绩。

select Sname,Students.Sno,Course.Cname,Score.score
from Students,Course,Score
where Score.score<60 and Students.Sno=Score.sno and Score.Cno=Course.Cno;

（6）查询所有讲授“01010105”课程的教师信息。

SELECT JS.Tno,Tname,Tsex,Birthday
from JS,SK 
where JS.Tno=SK.Tno and SK.Cno='01010105';

（7）查询1971年以前（含1971年）出生的所有教师的任课信息，包括教师姓名、出生日期、所授课程名、学时数。

select Tname,Birthday,Course.Cname,Course.Hours 
from JS,Course,SK 
where JS.Birthday<'1972-1-1' and JS.Tno=SK.Tno and Course.Cno=SK.Cno;

（8）查询所有未授课的教师信息。

select * 
from JS 
where JS.Tno not in (select JS.Tno 
                     from JS,SK 
                     where JS.Tno = SK.Tno);

对表中数据进行插入操作

（1）向students表中插入几条记录，内容自定。

insert into students values('00001','张三',20,'女','2001-4-3');
insert into students values('00002','李四',21,'男','2000-11-23');
insert into students values('00003','王五',20,'男','2001-12-1');

（2）把教师李映雪（教师号为1476，其他内容自定）的记录加入到教师表JS中。

insert into JS values('1476','李映雪','女','1976-7-26');

（3）根据students表，建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL。

create table GRIL(
    SNO CHAR(10) PRIMARY KEY NOT NULL,
    SNAME CHAR(8) NOT NULL,
    AGE NUMERIC(3,0)
);

对表中数据进行修改操作

（1）把所有学生的年龄增加一岁。

update Students set Sage=Sage+1;

（2）将所有选修某一指定课程的学生成绩增加5分。

update Score set score=score+5 where Cno='00001';

（3）将某个学生的所有成绩置0。

update Score set score = 0 where Sno='00002';

对表中数据进行删除操作

（1）从教师表JS中删除年龄已到60岁的退休教师的数据。

delete from JS where Tage>59;

（2）将学生表student中的某个学生删除，并删除其他表中与该学生相关的信息。

delete from Students where Tno=‘00003’;
delete from Score where Tno=‘00003’;

思考与实践

（1）查询数量在5本以上的图书信息。

select *
from TS
where Tnum>5;

（2）查询图书总量。

select sum(Tnum)
from TS;

（3）查询图书总量在前5名的出版社信息。

select Cno,Cname,Ctel,Cyb,Caddr
from CBS,TS
where CBS.Cno=TS.Cno
order by sum(Tnum) DESC
limit 5;

（4）查询所有“清华大学出版社”的图书信息。

select *
from TS
where Cno=(select Cno
             from CBS
             where Cname='清华大学出版社'
);

（5）查询所有还书日期已到（假设图书借阅期限为1个月），但仍未还书的借书人信息。

select *
from JS
where Jsno=(select Jsno
            from JY
            where DATE(CURDATE())-Jydate>30
);

这里对于时间的相减，我进行了几次试验，如果数据是年月日就用DATE(CURDATE())去获取当前年月日，相减得到的结果为：

比如说对于如下的数据库进行当前时间和生日相减时得到的数字就是几个不同的六位数，十万位和万位是年份差，千位百位是月份差，十位个位是日差。


同理如果有时分秒的相减就是把时分秒对应数字都置于年月日后面

所以切不可用带有时分秒的时间去减只有年月日的时间，否则会出现下面的情况

比如2021-11-27-01:50:50减去2021-11-27的计算方式为
20211127015050-20211127=202111106803923是个没有实际意义的数。
（6）查询某借书人的所有借阅信息。

select *
from JY
where Jsno=(select Jsno
            from JS
            where Jname='某借书人'
            );

（7）根据图书表TS，建立一个书名中包含有“数据库”的新表DB。

create table DB(
Tsno varchar(15),
Tname varchar(20) not NULL check(Tname like '%数据库%'),
Tnum smallint check(Tnum > 0),
Tpos varchar(30),
Cno varchar(4),
primary key(Tsno,Cno)
);

（8）将某借书人的某书的还书日期设置为当前系统日期。

update JY
set Hdate=now()
where Jsno=(select Jsno
            from JS
            where Jname='某借书人'
            );

（9）查询本月内借书的所有借书人相关信息，包括借书证号、姓名、单位、书名、借书日期，并将查询结果放到lend表中。

insert
into lend
select Jno,Jname,Jdw,Tname,Jydate
from JS,JY
where JS.Jno=JY.Jno;

（10）查询各借书人的借阅量，要求得到：借书证号、姓名、借书量。

select Jsno,Jname,count(Tsno)
from JS,JY
where JS.Jsno=JY.Jsno;

（11）列出所有有借阅记录的读者信息（不能重复）。

select distinct *
from js
where Jsno=(select Jsno
            from JY
            );

（12）将某指定图书（给定图书号）的数量减少2本。

update TS
set Tnum=Tnum-2
where Tsno='指定图书';

（13）用当前系统日期修改某本书（给定书号）的借书日期。

update JY
set Jydate=now()
where Tsno='某本书';

（14）将某书的信息添加到图书表TS中。

insert into TS values('信息');

（15）删除某一借书人及其相关信息。

delete
from JS,JY
where Tsno='某借书人';

总结

实验三的代码没有具体给出数值，不能直接使用，根据要求自行将“某借书人”、“某本书”之类的字符替换为要求的字符。

实验四：数据库编程

构建表

这个实验和实验三共用同样的表。
学生表（Students）

教师表（JS）

授课表（SK）

课程表（Course）

成绩表（Score）

T-SQL的流程控制语句、游标的使用

（1）在score表中求某班学生某门课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。

#最高分
select Students.Sno,Students.Sname,Course.Cname,score 
from score,Students,Course
where Students.SNO=Score.Sno and Course.Cno=Score.Cno 
ORDER BY score DESC 
limit 1;
#最低分
select Students.Sno,Students.Sname,Course.Cname,score 
from score,Students,Course
where Students.SNO=Score.Sno and Course.Cno=Score.Cno 
ORDER BY score ASC 
limit 1;

（2）查询某班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌。

#建表
create table Class(
    Bno varchar(4),
    Bname varchar(20)
);
create table Student(
    Sno varchar(10),
    Sname varchar(15),
    Bno varchar(4),
    Sex varchar(2),
    Birthday datetime,
    Polity varchar(10)
);
#查询
select Class.Bname,Student.Sno,Student.Sname,Sex,Birthday,Polity
from  Class,Student
where Class.Bno = '某班' and Student.Bno = Class.Bno;

（3）在student表中先插入三条新记录，其中的Pollity字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“群众”。

create table student(
    Sno CHAR(10) PRIMARY KEY NOT NULL,
    Sname CHAR(8) NOT NULL,
    Age NUMERIC(3,0),
    Sex CHAR(2),
    Birthday CHAR(20),
    Polity CHAR(20) DEFAULT '群众'
);
insert into student values('0001','张三',20,'男','2001-12-12',NULL);
insert into student values('0002','李四',21,'女','2000-01-07',NULL);
insert into student values('0003','王五',20,'男','2000-10-31',NULL);

（4）根据score表中考试成绩，查询某班学生某门课程的平均成绩，并根据平均成绩输出相应的提示信息。

declare @s float
set @s=(select avg(score) from score)
print '平均成绩为'+@s

（5）根据score表中考试成绩，查询某班学生的考试情况，并使用CASE将课程号替换为课程名称。