江科大数据库实验
本实验采用SQLite数据库完成。
由于sqlite不完全支持sql标准，因此我采用python3来模拟一部分函数定义和存储过程。
准备过程
单击这里下载sqlite3的压缩包，然后解压到桌面。
像这样。
然后使用win+R 运行cmd 到 shell中
数据定义
cd 到桌面，然后运行（按要求学号后三位+db即可）代码如下:
sqlite3 108.db
运行结果如下
教师表(JS)
复制下面的代码然后粘贴到cmd中
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');
select * from JS;
课程表（Course）
复制下面的代码然后粘贴到cmd中
create table Course(
Cno varchar(10),
Cname varchar(20),
Credit integer,
property varchar(10),
Hours integer
);
插入数据：
INSERT INTO Course VALUES('01010101','大学英语1',4,'考试','');
INSERT INTO Course VALUES('01010102','普通物理1',4,'考试','');
INSERT INTO Course VALUES('01010103','高等数学1',6,'考试','');
INSERT INTO Course VALUES('01010104','形势政策',2,'考查','');
INSERT INTO Course VALUES('01010105','计算机基础',4,'考查','');
授课表
create table SK(
Tno varchar(7),
Cno varchar(10),
Hours integer
);
插入数据：
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);
select * from SK;
修改表结构
1）在授课表中添加一个授课类别，列名为Type，类型为char（4）
ALTER TABLE SK ADD COLUMN Type char(4);
查看更改结果
select * from sqlite_master where type = "table";
更改完毕
2）将授课表中的Hours的类型改为Smallint
因为Sqllite不能修改字段或者删除字段，这里我直接删表重建。如果是mysql就使用alter table news modify column Hours smallint;
drop table SK;
create table SK(
Tno varchar(7),
Cno varchar(10),
Hours Smallint
);
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);
select * from SK;
3)删除课程表的Hours列
和上面一个道理
drop table SK;
create table SK(
Tno varchar(7),
Cno varchar(10)
);
INSERT INTO SK VALUES('T001','01010105');
INSERT INTO SK VALUES('T002','01010102');
INSERT INTO SK VALUES('T009','01010101');
INSERT INTO SK VALUES('T004','01010104');
INSERT INTO SK VALUES('T005','01010103');
INSERT INTO SK VALUES('T006','01010105');
INSERT INTO SK VALUES('T003','01010101');
select * from SK;
完成以下功能：
1) 定义表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)
);
2) 在教师表JS中增加住址列
字段名为ADDR，类型为 CHAR，长度50。
ALTER TABLE JS ADD COLUMN TDDR char(50);
3) 根据students表
建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL。
create table GRIL(
SNO CHAR(10) PRIMARY KEY NOT NULL,
SNAME CHAR(8) NOT NULL,
AGE NUMERIC(3,0)
);
4) 建立一成绩表
表名为score，其中字段有：SNO CHAR（10），CNO CHAR（10），scoreNUMERIC(6,0)，并输入部分记录，内容自定
create table score(
SNO CHAR(10),
CNO CHAR(10),
score NUMERIC(6,0)
);
insert into students values('8101','张三','','','','');
insert into students values('8102','李四','','','','');
insert into score values('8101','01010101',60);
定义索引
在数据库中的新建一个数据表
名为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 INDEX I_js_sid ON JS1 (Sid);
SELECT * FROM sqlite_master WHERE type = 'index';
为数据库中的course数据表
创建一个复合索引，索引名为I_cource_xf，使用Cno和Credit字段
CREATE INDEX I_cource_xf on Course(Cno, Credit);
SELECT * FROM sqlite_master WHERE type = 'index';
完成后输入.exit退出
思考与实践
1．创建“图书借阅数据库”。
2．修改“图书借阅数据库”，相关参数自行确定。
3．在“图书借阅数据库”中创建以下四个表，并分别为这四个表写入部分数据，内容自定（提醒：各表数据之间的相关性）
创建个数据库
sqlite3 图书借阅.db
在cmd中运行如下sql语句
create table JS(
Jsno varchar(6),
Jname varchar(10),
Jdw varchar(20)
);
create table TS(
Tsno varchar(15),
Tname varchar(20),
Tnum smallint,
Tpos varchar(30),
Cno varchar(4)
);
create table CBS(
Cno varchar(4),
Cname varchar(20),
Ctel varchar(12),
Cyb varchar(6),
Caddr varchar(40)
);
create table JY(
Jsno varchar(6),
Tsno varchar(15),
Jydate datetime,
Hdate datetime
);
.table
运行效果如下
插入数据
insert into JS values('000000','张三','江科大');
insert into JS values('000001','李四','江科大');
insert into JS values('000002','王五','江科大');
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','2019-11-18','2019-11-18');
创建视图
TS_view
CREATE VIEW TS_view AS select Tsno,Tname,Cname,Tnum from TS,CBS where TS.Cno=CBS.Cno;
select * from TS_view;
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;
select * from Jy_view;
建立索引
随便建建
create INDEX ts_index ON TS(Tsno);
create INDEX jy_index ON JY(Jsno);
create INDEX cbs_index ON CBS(Cno);
create INDEX js_index ON JS(Jsno);
完整性约束
删表重建吧。
懒得写了。草
数据操纵
1. 在查询分析器中，对“教师授课管理数据库”表中数据进行下列查询操作
（1）查询所有男学生的姓名、出生日期。
select SNAME,BPLACE from students where SEX='男';
运行效果如下：
（2）查询男女教师的人数。
select Tsex,count(Tno) from JS group by Tsex;
运行效果如下:
（3）找出年龄在20～23岁之间的学生的学号、姓名和年龄，并按年龄升序排序。
select * from students where AGE>=20 and AGE<=23;
懒得填数据了，把20改成18就能查出来了
（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,Dept,Sid 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);
2．在查询分析器中，对表中数据进行插入操作
（1）向students表中插入几条记录，内容自定。
insert into students values('8089','张八','女','','','');
（2）把教师李映雪（教师号为1476，其他内容自定）的记录加入到教师表JS中。
insert into JS values('T010','李映雪','女','1971-3-20','电信','551021197103203181','');
（3）根据students表，建立一个只包含学号、姓名、年龄的女学生表，表名为GRIL。
建过了
3．在查询分析器中，对表中数据进行修改操作
（1）把所有学生的年龄增加一岁。
update students set AGE=AGE+1;
（2）将所有选修某一指定课程的学生成绩增加5分。
update score set score=score+5 where Cno='01010101';
（3）将某个学生的所有成绩置0。
update score set score = 0 where Sno='8102';
4．在查询分析器中，对表中数据进行删除操作
（1）从教师表JS中删除年龄已到60岁的退休教师的数据。
drop from JS where datetime(CURRENT_TIMESTAMP,'localtime')-Birthday>59;
（2）将学生表student中的某个学生删除，并删除其他表中与该学生相关的信息。
一个一个删吧。如果用外键的话，直接
drop from student where SNO=?
FOREIGN KEY 学生信息ID
REFERENCES 学生信息 (学生信息ID)
ON DELETE CASCADE
数据库编程
T-SQL的流程控制语句、游标的使用编写程序完成以下功能，在查询分析器中执行程序，并记录结果。
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．
查询某班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌
增加一个班级表 banji 和一个班级学生关系表:
create table banji(
Bno varchar(4),
Bname varchar(20)
);
create table banji_student(
Sno char(10),
Bno varchar(4)
);
向表内添加数据:
insert into students values('8103','张五',18,'男','1999-08-07','团员');
insert into students values('8104','李五',18,'女','1998-09-04','党员');
insert into banji values('100','一班');
insert into banji_student values('8103','100');
insert into banji_student values('8104','100');
查询一班的学生信息：
select banji.Bname, students.SNO,students.SNAME,students.SEX,students.BPLACE,students.Polity
from banji,banji_student,students
where banji.Bno = 100 and banji_student.Sno = students.SNO
3.
在student表中先插入三条新记录，其中的Pollity字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“群众”
drop table students;
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) DEFAULT '群众'
);
insert into students values('8103','张五',18,'男','1999-08-07','团员');
insert into students values('8104','李五',18,'女','1998-09-04','党员');
insert into students values('8101','张三','','','','');
insert into students values('8102','李四','','','','');
select * from students;
insert into students(SNO,SNAME,AGE,SEX,BPLACE) values('8105','王八',18,'男','1999-08-08');
4.
根据score表中考试成绩，查询某班学生某门课程的平均成绩，并根据平均成绩输出相应的提示信息
向score表中添加数据
insert into score values('8102','01010101',50);
SELECT avg(score),Course.Cname,banji.Bname
FROM score,students,Course,banji,banji_student
where Course.Cno=score.CNO and banji.Bno=banji_student.Bno
and banji.Bno=100 and banji_student.Sno = students.SNO and banji_student.Sno=score.SNO;
5．
根据score表中考试成绩，查询某班学生的考试情况，并使用CASE将课程号替换为课程名称
SELECT score,(case score.CNO when 1 then Course.Cname ELSE Course.Cname END),banji.Bname
FROM score,students,Course,banji,banji_student
where Course.Cno=score.CNO and banji.Bno=banji_student.Bno
and banji.Bno=100 and banji_student.Sno = students.SNO and banji_student.Sno=score.SNO;
6.
根据score表中考试成绩，查询某班学生的考试情况，并根据考试分数输出考试等级。
SELECT students.SNAME,(case when score >=60 then '及格' else '不及格' end),Course.Cname,banji.Bname
FROM score,students,Course,banji,banji_student
where Course.Cno=score.CNO and banji.Bno=banji_student.Bno
and banji.Bno=100 and banji_student.Sno = students.SNO and banji_student.Sno=score.SNO;
存储过程的创建与调用
因为sqlite功能有限。这里选择python来模拟逻辑。
1.
创建一个存储过程stuscoreinfo，完成的功能是在表student、表course和表score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
先修改一下张三的性别
update students set SEX='女' where SNAME='张三';
编写sql语句
select banji.Bname,students.SNAME,students.SEX,Course.Cname,score.score
from banji,banji_student,students,Course,score
where banji.Bno=banji_student.Bno and banji_student.Sno=students.SNO and score.Cno=Course.Cno and score.Sno=students.SNO;
使用python进行封装调用。
python 代码如下：
import sqlite3
def stuscoreinfo():
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select banji.Bname,students.SNAME,students.SEX,Course.Cname,score.score from banji,banji_student,students,Course,score where banji.Bno=banji_student.Bno and banji_student.Sno=students.SNO and score.Cno=Course.Cno and score.Sno=students.SNO;")
values = cursor.fetchall()
print (values)
cursor.close()
conn.close()
stuscoreinfo()
运行结果如下所示
2．
创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在student中查询此学生的信息。
编写sql如下所示
select * from students where SNO='8101'
python封装如下
import sqlite3
def stu_info(num):
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select * from students where SNO='"+str(num)+"'")
values = cursor.fetchall()
print (values)
cursor.close()
#conn.commit()
conn.close()
stu_info(8101)
运行结果如下
3.
创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在student中计算此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。
import sqlite3
import time
def stu_age():
try:
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select BPLACE,SNAME from students");
values = cursor.fetchall()
for i in values:
if i[0].split('-')[0]!='':
print (str(int(time.localtime(time.time())[0])-int(i[0].split('-')[0]))+'|'+str(i[1]))
else:
print ('未填写' + '|'+str(i[1]))
cursor.close()
conn.close()
return 0
except BaseException:
return 1
stu_age()
运行结果如下：
4-8
上面均已经python模拟了存储过程的逻辑。
自定义函数的创建与调用
1．
使用自定义函数fun_sumscores。求score表中各班级的各门课程的平均分。主程序调用该函数，显示各班级、各课程的平均分。
这是修改sql之前的信息
编写sql语句如下：
select banji.Bname,avg(score),Course.Cname
from banji,score,banji_student,Course
where banji.Bno=banji_student.Bno and banji_student.Sno=score.Sno and Course.Cno=score.Cno
group by Course.Cname;
python封装如下
import sqlite3
def fun_sumscores():
try:
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select banji.Bname,avg(score),Course.Cname from banji,score,banji_student,Course where banji.Bno=banji_student.Bno and banji_student.Sno=score.Sno and Course.Cno=score.Cno group by Course.Cname;");
values = cursor.fetchall()
print (values)
cursor.close()
conn.close()
return 0
except BaseException:
return 1
fun_sumscores()
运行效果如下：
2．
编写一个用户自定义函数fun_sumscores。要求根据输入的班级号和课程号，求得此班此门课程的总分。主程序调用该函数，查询指定班级的某课程的总分。
编写sql如下
select Cname,sum(score)
from score,banji,banji_student,Course
where banji.Bno=100 and score.Cno='01010101' and Course.Cno=score.Cno and banji.Bno=banji_student.Bno and banji_student.Sno=score.SNO;
python封装如下
import sqlite3
def fun_sumscores(Bno,Cno):
try:
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select Cname,sum(score) from score,banji,banji_student,Course where banji.Bno="+Bno+" and score.Cno='"+Cno+"' and Course.Cno=score.Cno and banji.Bno=banji_student.Bno and banji_student.Sno=score.SNO;");
values = cursor.fetchall()
print (values)
cursor.close()
conn.close()
return 0
except BaseException:
return 1
fun_sumscores('100','01010101')
运行效果如下
3.
编写一自定义函数，用于查询给定姓名的学生，如果没找到，返回0，否则返回满足条件的学生人数。主程序调用该函数，查询“李浩”的学生，并根据函数的返回值进行输出。
编写sql如下
select count(*) from students where SNAME='张三';
python模拟如下
import sqlite3
def fun_select(name):
try:
conn = sqlite3.connect('108.db')
cursor = conn.cursor()
cursor.execute("select count(*) from students where SNAME='"+name+"'")
values = cursor.fetchall()
print (values)
cursor.close()
conn.close()
return 0
except BaseException:
return 1
fun_select('张三')
运行效果如下：
思考与实践
对“图书借阅数据库”中的JS、TS、CBS、JY表进行操作：
查询各借书人的借阅量（若重复借阅一本书，则以一本书计），要求得到：借书证号、姓名、借书量。
编写sql
select JS.Jsno,JS.Jname,count(JY.Jsno)
from JS,JY
where JS.Jsno=JY.Jsno
Group by JS.Jsno;
运行效果如下
微信搜索桔子科研或者扫描二维码，第一时间获取编程有趣的知识和最新科研学术成果。
