数据库实验报告
2010211122班李济汉 10210737 20号实验一：用SQL Server实现数据库设计
建立数据库：
CREATE DATABASE teaching
ON PRIMARY
(NAME='teaching_data',
FILENAME='e:\李济汉\teaching.mdf',
SIZE=1MB,
MAXSIZE=5MB,
FILEGROWTH=10%
);
LOG ON
(
NAME='teaching_log',
FILENAME='e:\李济汉\teaching.ldf',
SIZE=1MB,
MAXSIZE=2MB,
FILEGROWTH=10%
);
建立数据类型：
CREATE TYPE student_no
FROM CHAR(10)NOT NULL;
CREATE TYPE member_no
FROM INTEGER;
CREATE TYPE shortstring
FROM VARCHAR(15);
建立表：
班级表
CREATE TABLE class
(
CLname CHAR(10)PRIMARY KEY,
CLmajor CHAR(20),
CLgrade CHAR(4),
CLnumber INTEGER,
CLteacher CHAR(10)
);
学生表
CREATE TABLE student
(
Sno CHAR(8)PRIMARY KEY,
Sname CHAR(20)UNIQUE,
Sclass CHAR(10)NOT NULL,
Sgender CHAR(2)DEFAULT'男',
Sbirthday DATETIME,
Sface CHAR(10),
Splace CHAR(6),
Stime DATETIME,
Sphone CHAR(15)DEFAULT'unknown',
Sexp VARCHAR(200),
FOREIGN KEY(Sclass)REFERENCES class(CLname) );
CREATE DEFAULT Sphone AS'unknown'
课程表
CREATE TABLE lesson
(
Lnum CHAR(10)PRIMARY KEY,
Lname CHAR(20)UNIQUE,
Lkind CHAR(10),
Lmajor CHAR(10),
Lgrade CHAR(4),
Ltime DATETIME,
Lscore CHAR(2),
Lperiod CHAR(3)
);
课程安排表
CREATE TABLE lessonarrange
(
LAnum CHAR(10)PRIMARY KEY,
LAtime DATETIME,
LAclass CHAR(10),
LAteacher CHAR(10),
FOREIGN KEY(LAclass)REFERENCES class(CLname), FOREIGN KEY(LAnum)REFERENCES lesson(Lnum)
);
选课表
CREATE TABLE sele
(
SLnum CHAR(10),
SLno CHAR(8),
SLscore INTEGER,
FOREIGN KEY(SLnum)REFERENCES lesson(Lnum), FOREIGN KEY(SLno)REFERENCES student(Sno), PRIMARY KEY(SLnum,SLno)
);
数据库各表关系图：
执行插入操作：
INSERT INTO class(CLname,CLmajor,CLgrade,CLnumber,CLteacher)
VALUES('2010211127','通信工程','大二',32,'赵一超');
INSERT INTO class(CLname,CLmajor,CLgrade,CLnumber,CLteacher)
VALUES('2010211122','通信工程','大二',28,'韩宇宏');
INSERT INTO student(Sno,Sname,
Sclass,Sgender,Sage,Sface,Splace,Stime,Sphone,Sexp)
VALUES('10210737','李济汉','2010211127','男',1992-11-28,'共青团员','湖南',2010-9-1,'133********','我是一个有梦想的学生。');
执行更新操作：
UPDATE student SET Sclass='2010211122'WHERE Sno='10210737';
执行删除操作：
DELETE FROM student WHERE Sno='10210737';
执行查询操作：
SELECT Sno,Sname,Sgender,Sage INTO stu FROM student;
删除表数据：
TRUNCATE TABLE
创建索引：
CREATE CLUSTERED INDEX SLcluster1 ON sele (SLnum,SLno); CREATE INDEX SLcluster2 ON sele(SLno);
CREATE INDEX SLcluster3 ON sele(SLnum);
删除索引：
DROP INDEX SLcluster3;
实现各种查询：
检索选修了课程号为C1或C2课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。SELECT student.Sname,lesson.Lname,sele.SLscore
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum AND
(lesson.Lnum='C1'OR lesson.Lnum='C2')AND(sele. SLscore>=70);
检索姓“王”的所有学生的姓名和年龄。
SELECT Sname AS name,Sage AS age
FROM student
WHERE Sname LIKE'王%';
检索没有考试成绩的学生姓名和课程名。
SELECT student.Sname,lesson.Lname
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum AND sele.SLscore= NULL;
检索年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT Sname, Sage AS age
FROM student
WHERE Sgender='男'AND Sage>(SELECT AVG(Sage)FROM student WHERE Sgender='女');
创建视图：
创建视图，包含所有通信工程专业的学生的信息；
CREATE VIEW Tel_student
AS SELECT student.*
FROM student,class
WHERE student.Sclass=class.CLname AND class.CLmajor='通信工程' WITH CHECK OPTION;
创建视图，包含所有学生的学号，姓名，选课的课程名和成绩；
CREATE VIEW Is_student
AS SELECT student.Sno,student.Sname,lesson.Lname,sele.SLscore FROM student LEFT OUT JOIN sele ON(student.Sno=sele.SLno),
lesson LEFT OUT JOIN sele ON(lesson.Lnum=sele.SLnum)
WITH ENCRYPTION;
创建视图，包含所有课程的课程号，名，班级名称及每班选课的人数；
CREATE VIEW Is_lesson
AS SELECT lesson.Lnum,lesson.Lname,class.CLname,COUNT(sele.SLno)AS Number
FROM student,lesson,class,sele
WHERE student.Sno=sele.SLno AND student.Sclass=class.CLname AND
lesson.Lnum=sele.SLnum
GROUP BY lesson.Lnum,lesson.Lname,class.CLname;
实验二：SQL Server数据库设计高级内容
统计有学生选修的课程门数。
SELECT COUNT(DISTINCT SLnum)AS coursenumber FROM sele;
求选课在四门以上的学生所选课程的平均成绩(不统计不及格的课程)。最后按降序列出平均成绩名次名单来。
SELECT SLno,AVG(SLscore)AS avggrade
FROM sele
WHERE SLscore>=60
GROUP BY SLno HAVING COUNT(SLnum)>=4
ORDER BY avggrade DESC;
统计每门指选课程的学生选修人数(超过10人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
SELECT SLnum,Lname,COUNT(SLno) SLnumber
FROM sele,lesson
WHERE lesson.Lnum=sele.Slnum
GROUP BY SLnum,Lname HAVING COUNT(SLno)>=4 --由于数据较少，将改为选课人数大于等于人
ORDER BY COUNT(SLno)DESC,SLnum ASC;
检索所学课程包含了学生s3所选的所有课程的学生学号。
SELECT DISTINCT SLno FROM sele sele_1 WHERE
NOT EXISTS(
SELECT*FROM sele sele_2 WHERE SLno='10210737'AND
NOT EXISTS(
SELECT*FROM sele sele_3
WHERE sele_3.SLno=sele_1.SLno AND sele_3.SLnum=sele_2.SLnum
)
);
定义check约束：
ALTER TABLE sele ADD CONSTRAINT checksele CHECK(SLscore>=0 AND SLscore<=100);
ALTER TABLE sele DROP CONSTRAINT checksele;
定义规则：
CREATE RULE coursetype as @coursetype in('必修','选修','必选');
sp_bindrule'coursetype','lesson.Lkind';
sp_unbindrule'lesson.Lkind';
DROP RULE coursetype;
定义存储过程：
定义存储过程，实现学生学号，姓名，课程名和成绩的查询；
CREATE PROC student_query1 AS
SELECT student.Sno,Sname,Lnum,SLscore
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum;
EXEC student_query1;
定义存储过程，实现按某人指定课程的成绩；
CREATE PROC student_query2
@sname varchar(20),
@cname varchar(30)
AS
SELECT student.Sno,Sname,Lnum,SLscore
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum
AND Sname=@sname AND Lnum=@cname;
EXEC student_query2 '李济汉','C2';
定义存储过程，在查询某人所选修的课程和成绩，指定姓名时，可以只给出姓；CREATE PROC student_query3
@sname varchar(20)='%'
AS
SELECT student.Sno,Sname,Lnum,SLscore
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum
AND Sname LIKE @sname;
EXEC student_query3 '周%';
定义存储过程，计算并查看指定学生的总学分。
ALTER TABLE lesson ALTER COLUMN Lscore FLOAT;
CREATE PROC student_query4
@sname varchar(20),
@total integer OUTPUT
AS
SELECT @total=SUM(Lscore)
FROM student,lesson,sele
WHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum
AND Sname=@sname;
DECLARE @total FLOAT
EXEC student_query4 '朱建华',@total OUTPUT;
SELECT @total AS Total;
定义触发器：
使用CREATE TRIGGER命令对学生选课信息表创建插入触发器，实现的功能是：当向学生选课信息表中插入一记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程编号是否在课程表中存在，若有一项为否，则提示“违背数据的一致性”错误信息，并且不允许插入。
CREATE TRIGGER checkin ON [dbo].[sele]
FOR INSERT
AS
IF NOT EXISTS(
SELECT*FROM student,lesson,inserted
WHERE student.Sno=inserted.SLno
AND lesson.Lnum=inserted.SLnum
)
BEGIN
RAISERROR('违背数据的一致性',16,1)
ROLLBACK
END
ELSE
SELECT*FROM sele
INSERT INTO sele VALUES('10310435','C5',94)
使用CREATE TRIGGER 命令对学生信息表创建删除触发器，实现的功能是：当在学生信息表中删除一条记录时，同时删除学生选课信息表中相应的记录。
CREATE TRIGGER [del_trigger] ON [dbo].[student]
FOR DELETE
AS DELETE FROM sele
WHERE sele.SLno IN(SELECT Sno FROM deleted)
实验三：在PB中实现第一个应用程序
d_student学生信息表：
计算并增加年龄（当今年份-出生年份）项：
d_score学生成绩：
学生信息按年龄降序排列，并可查询其各科成绩：
面板：
dw_1.SetTransObject(sqlca)
dw_1.retrieve()
信息显示区：
string s1
int row
row=GetRow(dw_1)
if row<1 then return SelectRow (dw_1,0,False) SelectRow (dw_1,row,True)
s1=dw_1.GetItemString(row,1) dw_2.settransobject(sqlca)
dw_2.retrieve(s1)
按年龄排序：
if cbx_1.checked=TRUE then
if rb_1.checked=TRUE then
dw_1.SetSort("sage A") else
dw_1.SetSort("sage D") end if
dw_1.Sort()
end if
升序：
if cbx_1.checked=TRUE then
dw_1.SetSort("sage A") dw_1.Sort()
end if
降序：
if cbx_1.checked=TRUE then
dw_1.SetSort("sage D")
dw_1.Sort()
end if
退出：
CLOSE(Parent)
学生信息按年龄升序排列，并可查询其各科成绩：
教学管理系统登录界面（teachingsystem）：
设计一个登录窗口，使用用户在窗口的输入信息，作为登录指定的数据库的帐号和密码。创建数据库用户名密码表：
登陆界面：
用户名密码匹配错误：
（功能亮点）
设置用户名全局变量username
Global Variables:string username
与数据库进行匹配用户名与密码：
string suser,spasswd,str="0"
suser=trim(sle_1.text)
spasswd=trim(sle_2.text)
select Suser,Spasswd,Suser
into :suser,:spasswd,:str
from admin
where Suser=:suser and Spasswd=:spasswd;
if str="0" then
messagebox("警告","用户名或密码输入错误！") return
else
username=suser
open(blog)
close(parent)
end if
新功能：
修改密码
验证密码窗口（code）：
错误窗口（code4）：
string str,suser="0"
str=sle_1.text
select Suser
into :suser
from admin
where Spasswd=:str;
if suser=username then open(code2)
close(parent)
else
open(code4)
end if
设置新密码窗口（code2）：