数据库实验记录&教学

最新推荐文章于 2023-04-11 15:20:28 发布
最新推荐文章于 2023-04-11 15:20:28 发布
阅读量3.2k 收藏 45
点赞数 13
分类专栏： 数据库 文章标签： 数据库 mysql cmd navicat
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shenhaizpg/article/details/121546930
版权
本文详细指导了如何在Windows上配置MySQL环境，包括下载、配置my.ini文件、连接数据库、环境变量设置，并演示了数据定义、完整性约束、数据操纵和编程实践，涉及创建表、视图、索引、存储过程、自定义函数等内容。
摘要由CSDN通过智能技术生成

江苏科技大学数据库实验

准备：配置环境

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）
列名说明数据类型
Tno教师号字符串，长度为7
Tname姓名字符串，长度为10
Tsex性别字符串，长度为2
Birthday出生日期日期时间型
Dept所在部门字符串，长度为20
Sid身份证号字符串，长度为18
JS数据
TnoTnameTsexBirthdayDeptSid
T001刘薇女1971-3-20电信551021197103203121
T002张骐劲男1963-7-13数理32010119630713318X
T003李子文女1973-9-15外语461031197309153829
T004江海防女1960-2-18社科560102196002185623
T005李铁男1977-10-11数理230103197710118632
T006吴天一男1962-4-23电信320104196204237516
T007赵志华男1968-8-27社科321102196808277214
T008钱进男1980-7-10电信570102198007103452
T009孙星南女1981-3-2外语110102198103024125
课程表（Course）
列名说明数据类型
Cno课程号字符串，长度为10
Cname课程名字符串，长度为20
Credit学分短整型
property课程性质字符串，长度为10
Hours授课时数整数
Course数据
CnoCnameCreditproperty
01010101大学英语14考试
01010102普通物理14考试
01010103高等数学16考试
01010104形势政策2考查
01010105计算机基础4考查
授课表（SK）
列名说明数据类型
Tno教师号字符串，长度为7
Cno课程号字符串，长度为10
Hours授课时数整数
SK数据
TnoCnoHours
T0010101010564
T0020101010264
T0090101010164
T0040101010432
T0050101010396
T0060101010564
T0030101010164

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）

列名说明数据类型键
Jsno借书证号字符串，长度为6*
Jname姓名字符串，长度为10
Jdw单位字符串，长度为20

图书表（TS）

列名说明数据类型键
Tsno书号字符串，长度为15*
Tname书名字符串，长度为20
Tnum数量短整型
Tpos位置字符串，长度为30
Cno出版社号字符串，长度为4

出版社表（CBS）

列名说明数据类型键
Cno出版社号字符串，长度为4*
Cname出版社名字符串，长度为20
Ctel电话字符串，长度为12
Cyb邮编字符串，长度为6
Caddr地址字符串，长度为40

借阅表（JY）

列名说明数据类型键
Jsno借书证号字符串，长度为6*
Tsno书号字符串，长度为15*
Jydate借书日期日期时间
Hdate还书日期日期时间

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）

列名说明约束
Tno教师号主键
Tname姓名非空
Tsex性别取值为“男”、“女”
Birthday出生日期允许空
Dept所在部门允许空
Sid身份证号不重

课程表（course）

列名说明约束
Cno课程号主键
Cname课程名非空
Credit学分大于0
property课程性质默认值为“必修”

授课表（SK）

列名说明约束
Tno教师号主键，引用教师表JS的外码
Cno课程号主键，引用课程表course的外码
Hours授课时数大于0
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）

列名说明数据类型约束
Jsno借书证号字符串，长度为6主键
Jname姓名字符串，长度为10非空
Jdw单位字符串，长度为20

图书表（TS）

列名说明数据类型约束
Tsno书号字符串，长度为15主键
Tname书名字符串，长度为20非空
Tnum数量短整型>0
Tpos位置字符串，长度为30
Cno出版社号字符串，长度为4主键，引用出版社表CBS的外码

出版社表（CBS）

列名说明数据类型约束
Cno出版社号字符串，长度为4主键
Cname出版社名字符串，长度为20非空
Ctel电话字符串，长度为12
Cyb邮编字符串，长度为6由数字组成
Caddr地址字符串，长度为40

借阅表（JY）

列名说明数据类型约束
Jsno借书证号字符串，长度为6主键，引用借书人表JS的外码
Tsno书号字符串，长度为15主键，引用图书表TS的外码
Jydate借书日期日期时间借书日期≤还书日期
Hdate还书日期日期时间
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）

列名说明
Sno学号
Sname姓名
Sage年龄
Ssex性别
Sbirthady生日

教师表（JS）

列名说明
Tno教师号
Tname姓名
Tsex性别
Birthday生日
Tage年龄

授课表（SK）

列名说明
Tno教师号
Cno课程号

课程表（Course）

列名说明
Cno课程号
Cname课程名
Hours学时

成绩表（Score）

列名说明
Sno学号
Cno课程号
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='某借书人';

总结

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

实验四：数据库编程

构建表

学生表（Student）

列名说明
Sno学号
Sname姓名
Age年龄
Sex性别
Bno班号
Birthady生日
Polity政治面貌

教师表（JS）

列名说明
Tno教师号
Tname姓名

授课表（SK）

列名说明
Tno教师号
Cno课程号

课程表（Course）

列名说明
Cno课程号
Cname课程名
Hours学时

成绩表（Score）

列名说明
Sno学号
Cno课程号
score分数

班级表（Class）

列名说明
Bno班号
Bname班名

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

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

#最高分
select Student.Sno,Student.Sname,Course.Cname,score 
from score,Student,Course
where Student.SNO=Score.Sno and Course.Cno=Score.Cno and Score.Cno='某课程号'
ORDER BY score DESC 
limit 1;
#最低分
select Student.Sno,Student.Sname,Course.Cname,score 
from score,Student,Course
where Student.SNO=Score.Sno and Course.Cno=Score.Cno and Score.Cno='某课程号'
ORDER BY score ASC 
limit 1;

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

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表中考试成绩，查询某班学生某门课程的平均成绩，并根据平均成绩输出相应的提示信息。

CREATE PROCEDURE stu_avgs()
BEGIN
declare s float;
set s=(select avg(score) from score where cno='某门课程号');
select '平均成绩为'+s;
end;

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

select score,(case Score.Cno 
              when 1 then Course.Cname 
              else Course.Cname end),Class.Bname
from Score,student,Course,Class
where Course.Cno=Score.CNO and Class.Bno=student.Bno and Class.Bno='某班级号' and student.Sno=Score.SNO;

（6）根据t_score表中考试成绩，查询某班学生的考试情况，并根据考试分数输出考试等级。

select student.Sname,(case when score between 100 and 90 then '优秀'
                           when score between 89 and 80 then '良好'
                           when score between 79 and 70 then '中等'
                           when score between 69 and 60 then '及格'
                           else '不及格'
                           end),Course.Cname,Class.Bname
from Score,student,Course,Class
where Course.Cno=Score.Cno and Class.Bno=student.Bno 
and Class.Bno='某班级' and student.Sno=Score.SNO;

存储过程的创建与调用

（1）创建一个存储过程stuscoreinfo，完成的功能是在表student、表course和表score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。

DELIMITER //
  CREATE PROCEDURE stuscoreinfo()
    BEGIN
      SELECT Student.Bno,Student.Sno,Student.Sname,Student.Sex,Course.Cno,Score.score
      From Student,Course,Score;
    END
    //
DELIMITER ;

（2）创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student中查询此学生的信息。

DELIMITER //
  CREATE PROCEDURE stu_info(in n varchar)
    BEGIN
      SELECT *
      From Student
      Where Sno=n;
    END
    //
DELIMITER ;

（3）创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student中计算此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。

DELIMITER //
  CREATE PROCEDURE stu_age(in n varchar,out a datetime)
    BEGIN
      DECLARE err int
      set err=0
      set a =now()-(SELECT Birthday
                    From Student
                    Where Sno=n)
      #检查错误，返回错误号
      IF (@@ERROR!=0)
      SET err=@@ERROR
      RETURN err;
    END
    //
DELIMITER ;

（4）执行stuscoreinfo存储过程（无参）。

CALL stuscoreinfo();

（5）执行存储过程stu_info（该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值）。

CALL stu_info('学号值');

（6）执行存储过程stu_age（该存储过程有一个输入参数“学号”和一个输出参数@age。存储过程执行完后应有一个返回的状态值，这个值可以从返回的错误号得到）。

CALL stu_info('学号值',@a);

（7）使用系统存储过程sp_help、sp_helptext、sp_depends、sp_stored_procedures查看用户创建的存储过程。

call sp_help();
call sp_helptext();
call sp_depends();
call sp_stored_procedures();

（8）删除存储过程stuscoreinfo

drop procedure stuscoreinfo;

自定义函数的创建与调用

（1）使用自定义函数fun_sumscores。求score表中各班级的各门课程的平均分。主程序调用该函数，显示各班级、各课程的平均分。

delimiter //
create function fun_sumscores()
returns type [float] 
begin
    select avg(score)
    from Score
    where Sno in(select Sno
    from Student
    where Bno=(select Bno
    from Class)
    )/(select count(Sno) from Student);
end 
//
delimiter ;

（2）编写一个用户自定义函数fun_sumscores。要求根据输入的班级号和课程号，求得此班此门课程的总分。主程序调用该函数，查询指定班级的某课程的总分。

delimiter //
create function fun_sumscores(banji varchar,kecheng varchar)
returns type [int] 
begin
    declare n int
    set n=(select sum(score)
    from Score
    where Cno=kecheng and Sno=(select Sno
    from student
    where Bno=banji));
    return n;
end 
//
delimiter ;

（3）编写一自定义函数，用于查询给定姓名的学生，如果没找到，返回0，否则返回满足条件的学生人数。主程序调用该函数，查询“李浩”的学生，并根据函数的返回值进行输出。

delimiter //
create function fun_findstudent(name varchar)
returns type [int] 
begin
    declare n int
    set n=(select count(Sname)
    from student
    where Sname=name)
    return n;
end 
//
delimiter ;
#调用
select fun_findstudent('李浩');

思考与实践

借书人表（JS）

列名说明数据类型约束
Jsno借书证号字符串，长度为6主键
Jname姓名字符串，长度为10非空
Jdw单位字符串，长度为20

图书表（TS）

列名说明数据类型约束
Tsno书号字符串，长度为15主键
Tname书名字符串，长度为20非空
Tnum数量短整型>0
Tpos位置字符串，长度为30
Cno出版社号字符串，长度为4主键，引用出版社表CBS的外码

出版社表（CBS）

列名说明数据类型约束
Cno出版社号字符串，长度为4主键
Cname出版社名字符串，长度为20非空
Ctel电话字符串，长度为12
Cyb邮编字符串，长度为6由数字组成
Caddr地址字符串，长度为40

借阅表（JY）

列名说明数据类型约束
Jsno借书证号字符串，长度为6主键，引用借书人表JS的外码
Tsno书号字符串，长度为15主键，引用图书表TS的外码
Jydate借书日期日期时间借书日期≤还书日期
Hdate还书日期日期时间

对“图书借阅数据库”中的JS、TS、CBS、JY表进行操作：
查询各借书人的借阅量（若重复借阅一本书，则以一本书计），要求得到：借书证号、姓名、借书量。

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

总结

在进行数据库实验的过程中其实还有不少问题，对于理论的理解加以实践的时候总能遇到奇奇怪怪的问题，好在csdn上可以搜索到大部分问题。本文加以个人的理解整合了很多对于诸如存储过程、函数等书上缺失的知识点，每一题基本都经过了多次考究，但个人水平有限，仅供参考，如有问题可以私聊联系我。

深海重苹果
关注
专栏目录
数据库实验记录（一）
weixin_45180685的博客
05-07 274
为了后面数据库实验需要达到的能力要求，第一次实验课在老师的讲解后，我结合老师的课上内容和课后操作，将简单的C#可视化编程加以练习，做成一个较为简单的计算器程序。 该计算器界面如下： 接触visual studio进行C#界面设置的直观印象就是操作简便（不用一句一句的去敲代码真的太贴心了 o(￣▽￣)ブ）。 在设计界面时，我们需要做的只是从工具栏挑选需要的组件，然后进行拖拽到右面的界面预览上即...
数据库实验
qq_40141141的博客
09-26 1789
数据库实验 1、T-SQL编程1：分别用CASE语句和IF语句实现 声明整数变量@var，使用CASE流程控制语句判断@var值等于1，等于2或者两者都不等。当@var值为1时，输出字符串”var is 1”； 当@var值为2时，输出字符串”var is 2”；否则输出字符串”var is not 1 or 2”。 DECLARE @VAR INT SELECT CASE WHEN @VAR=1...
江苏科技大学数据库实验报告
06-08
这个是江苏科技大学数据库概论课程的实验课的实验报告，仅供参考，如有错误，敬请原谅。
江科大SQL课程实验报告
11-26
江苏科技大学数据库课程实验报告，包括数据定义、数据操纵、完整性约束、数据库设计，数据库编程，共计5个实验的代码
江苏科技大学2022数据结构实验预习报告
qq_61578947的博客
05-17 547
2022年春学期江苏科技大学实验预习报告
江科大 数据库
01-01
江苏科技大学 新型数据库 关系模型 PPT课件（全英文）
数据库实验 数据的操纵操作
01-04
1、 利用企业管理器向供应商表和零件表中输入教材P28中的数据； 2、 用SQL语句向工程项目表和供应情况表中输入教材P28中的数据； 3、 完成输入后可向各个表中输入一下带有个人信息的数据。
《数据库原理综合实验》教学大纲.docx
12-18
《数据库原理综合实验》教学大纲主要涵盖了Oracle数据库的学习与实践，旨在深化学生对数据库理论的理解，提升实际操作技能。这门课程是针对信息工程专业高年级学生的必修课，通常在第五学期开设，总共1周的时间，计...
python+sql sever 数据库系统大作业实验 教学信息管理系统
06-25
在本实验项目“python+sql sever 数据库系统大作业实验 教学信息管理系统”中，我们将探讨如何使用Python编程语言与SQL Server数据库进行交互，实现一个教学信息管理系统的各项功能。这个项目包括了数据库设计、...
数据库应用(VF)实验教学大纲.docx
12-18
**数据库应用(VF)实验教学大纲** 在物理学和电子信息工程专业的课程设置中，数据库应用(VF)是一门关键课程，旨在通过16课时的教学帮助本科学生深入理解数据库语言编程，特别是通过使用Visual FoxPro（VF）这一工具...
江苏科技大学计算机网络实验报告
12-21
这个是计算机网络课的上机实验的实验报告，如有不清晰可联系我
江苏科技大学数据库题库
03-19
学长只能帮你到这里啦，加油！100分哦
数据库实验一 基于Sakila的数据库操作
03-29
在这个数据库实验中，我们主要涉及了MySQL数据库管理系统以及Sakila数据库的使用。Sakila是一个示例数据库，通常用于教学和演示目的。以下是对实验中提到的知识点的详细解释： 1. **数据库表字段标记**： - 主键...
深圳大学数据库实验二DW+PHP
01-02
【标题】"深圳大学数据库实验二DW+PHP"是一个针对大学生的实践教学项目，旨在让学生通过实际操作掌握Dreamweaver（DW）与PHP相结合的Web应用开发技术，同时结合SQL数据库管理，提升对项目架构的理解。 【描述】该...
数据库系统实验二：数据操纵与数据查询
weixin_64234293的博客
11-05 3441
数据库系统实验二：数据操纵与数据查询
实验三 数据操纵
安生一场梦
09-22 777
Student(stuno,stuname,stusex,sbirthday,dno,classno,tel,native,address) Course(couno,couname,semester,credit,lecture,experiment) Sc(stuno,couno,grade) Dept(dno,dname,dchair,daddress,tel) 目的 掌握如何向表中添加、修改、删除数据。 内容 向student表添加1条记录（‘101’，‘张三’ ，‘男’，2000-9-9，‘计
江科大nosql作业1
qq_53741629的博客
03-07 311
江科大nosql作业1
嵌套查询及视图的创建和使用
最新发布
weixin_52767679的博客
04-11 1989
（4）创建平均成绩大于80分的学生选课视图，用于查看学生的学号和平均成绩；(14) 求至少有2名讲师（Lecturer）的所在系的系主任姓名。（2）利用女生视图，将“LiuChen”的年龄改成18岁；（1）创建女生视图，包括学生的学号，姓名，年龄和所在系；（3）创建学生选修视图，用于查看学生选修的学号和总成绩；（3）利用副教授视图，删除教师“ChenShu”的记录。（4）查询平均工资小于3300的教师的系名和平均工资。（5）创建视图，用于查看学分大于2的课程信息；（2）创建计算机系选修了课程学生的视图；
dev gridview删除选中行_DevExpress GridControl控件行内新增、编辑、删除添加选择框（转）...
weixin_39689394的博客
12-21 352
http://blog.csdn.net/m1654399928/article/details/219515191.首先到GridControl控件设计里设置属性Repository (In-place EditorRepository) 如下图(CheckEdit可以不添加,这是本人根据自己需求添加的)：主要设置两个两个属性 NullText,Name2.绑定数据关键代码如下:Data...
SQLSERVER数据库创建与管理实验——SC教学管理系统
“广工数据库实验一，实验内容包括使用SQL语句和企业管理器创建数据库及表，以实现教学管理数据库SC，包含student、course、teacher等表。” 在本实验“广工数据库实验一”中，学生将学习和掌握与数据库相关的基础...
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

深海重苹果

谢谢您对我技术的肯定！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值