数据库系统概论实验课（本文使用SQL SEVER2019）

实验1、数据定义

使用SQL Sever、MySQL等数据库软件，创建“学生-课程”数据库、创建如下关系： Student(Sno,Sname,Ssex,Sage,Sdept); 其中，Sno是主码。 Course(Cno,Cname,Cpno,Ccredit);其中，Cno是主码。 SC(Sno,Cno,Grade);其中（Sno，Cno）是主码，Sno、Cno是外码。 具体数据完整性约束参考3.3数据定义章节中的例子。

任务1 构建

1、创建数据库S-C

点击数据库，右击，创建数据库，输入数据库名称

 2、创建表

根据要求，右击数据库S-C，新建查询，执行如下命令

USE [S-C]
GO
CREATE TABLE [dbo].[Student] (Sno   CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/                  Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/Ssex    CHAR(2),Sage   SMALLINT,Sdept  CHAR(20)); 
CREATE TABLE  Course(Cno       CHAR(4) PRIMARY KEY,Cname  CHAR(40),            Cpno     CHAR(4),               	                      Ccredit  SMALLINT,FOREIGN KEY (Cpno) REFERENCES  Course(Cno) ); 
CREATE TABLE  SC(Sno  CHAR(9), Cno  CHAR(4),  Grade  SMALLINT,PRIMARY KEY (Sno,Cno),  /* 主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGN KEY (Sno) REFERENCES Student(Sno),/* 表级完整性约束条件，Sno是外码，被参照表是Student */FOREIGN KEY (Cno)REFERENCES Course(Cno)/* 表级完整性约束条件， Cno是外码，被参照表是Course*/); 

任务2、为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1

在数据库-》安全性-》用户-》创建用户zhang，-》架构，创建新架构test，然后为其创建表tab1

CREATE TABLE TAB1 (COL1 SMALLINT,COL2 INT,COL3 CHAR(20),COL4 NUMERIC(10,3),COL5 DECIMAL(5,2));

实验2、插入数据与查询

3.2数据库学生-课程S-T，包含了以下三个表。

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

1、基于上节课创建的 学生-课程数据库，参照图3.2(P79)，进行数据插入。

方法1、（适合数据少的表）右击数据库“S-C”-》新建查询，执行如下代码（以course表为例）

之所以调整顺序，是因为cpno为参考外键，必须先存在，具体内容可以参考其他文章

----前面为注释，记得去掉，后面一样，不在赘述
--insert into Course values(2,'Math',null,2);
--insert into Course values(6,'Data Processing',null,2);
--insert into Course values(7,'PASCAL',6,4);
--insert into Course values(4,'Operating System',6,3);
--insert into Course values(5,'Data Structure',7,4);
--insert into Course values(1,'Database',5,4);
--insert into Course values(3,'Information System',1,4);

方法2、（适合数据多的表）

将数据复制到txt文档中，修改至正确格式，如“2;Math;;2”，注：需要带表头“Cno;Cname;Cpno;Ccredit”保存，命名为“course.txt”，然后右击数据库“s-c”-》任务-》导入数据-》数据源选择“course.txt”，预览没问题后选择下一步，目标选择本地Client

接着继续，编辑映射，没问题的话就继续下一步，如图 

然后完成，添加记录。

2、基于 学生-课程数据库，进行如下查询：

----a)查询计算机科学系（Sdept=IS）全体学生的名单； 
--	select Sname from Student
--	where Sdept='IS'
----b)查询计算机科学系年龄在20岁以下的学生姓名； 
--	select Sname from Student
--	where Sdept='IS' and Sage<20
----c）查询选修1号课程的学生的平均成绩。 
--	select AVG(Grade) 平均成绩 from SC
--	where Cno=1

实验三、连接查询

基于学生-课程数据库，进行如下连接查询：

--a）等值连接：查询每个学生及其选修课程的情况； 

    select * from Student,SCwhere Student.Sno=Sc.Sno

--b）自然连接：查询每个学生及其选修课程的情况； 

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;

--c）自身连接：查询每一门课的间接先修课（即先修课的先修课）（例3.52）； 

    select C1.Cno,C2.Cpnofrom Course C1,Course C2where C1.Cpno=C2.Cno;

--d）多表连接：查询每个学生的学号、姓名、选修的课程名及成绩； 

    select distinct Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and SC.Cno=Course.Cno

--e）选择和连接查询：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

    select distinct Student.Sno,Sname from Student,SCwhere Student.Sno=Sc.Sno and Cno=2 and Grade>=90

实验四、嵌套查询

基于 “学生-课程”数据库完成如下嵌套查询（不可使用等价的连接查询）：

--a）查询选择课程号“2”的课程的学生姓名； 

    select Sname from Studentwhere Sno in(select Sno from SCwhere Cno=2)

--b）查询选择“信息系统”课程的学生姓名； 

    select Sname from Studentwhere Sno in(select Sno from SCwhere Cno=(select Cno from Coursewhere Cname='Information System'))

--c）查询与“刘晨”同学同系的学生学号、姓名； 

    select S1.Sname,S1.Sno from Student S1where S1.Sdept =(select S2.Sdept from Student S2where S2.Sname='Liu Chen')

--d）查询选择“数据处理”为先修课的学生学号；

    select distinct Sno from  SCwhere Cno in(select Cno from Coursewhere Cpno in(select Cno from Coursewhere Cname='Data Processing'))

--e）查询选择与“李勇”同学选择相同课程的学生学号及其成绩。

    select Sno,Grade from SCwhere Cno in(select Cno from SCwhere Sno in(select Sno from Studentwhere Sname='Li Yong'        ))

数据库原理及应用-实验5-存储过程

1、创建一个存储过程，求l+2+3+…+n，并打印结果。 

GO
IF EXISTS (
SELECT * FROM SYSOBJECTS 
WHERE NAME='SumResult'
)
DROP PROCEDURE SumResult
CREATE PROCEDURE SumResult
ASDECLARE @i int,@n int,@result intSELECT @i=1,@result=0WHILE(@i<=@n)BEGINSET @result+=@iSET @i+=1ENDPRINT('1+2+3+...+n的结果是:')PRINT @result

2、创建一存储过程Proc_Student，用于显示学号为“201215121”的学生基本信息（包括学号、姓名、性别、年龄和所在系）； 

USE [S-C]
GO
IF EXISTS (
SELECT * FROM SYSOBJECTS 
WHERE NAME='Proc_Student'
)
DROP PROCEDURE Proc_Student
CREATE PROCEDURE Proc_Student
ASBEGINSELECT *FROM StudentWHERE Sno='201215121'END

3、创建一存储过程Stu_grade，通过读取某门课的编号，求出选课学生的学号。（参考例8.9）

USE [S-C]
GO
IF EXISTS (
SELECT * FROM SYSOBJECTS 
WHERE NAME='Stu_grade'
)
DROP PROCEDURE Stu_grade
GOCREATE PROCEDURE Stu_grade
@CNO char(10)
ASBEGINSELECT DISTINCT SnoFROM SCWHERE Cno=CNOEND
GO

4、调用上面的存储过程Stu_grade，求出课程编号为“2”的选课学生学号。
EXEC Stu_grade 2

注：一年多没接触了，数据比较少，也没法验证其准确性，只是对着所给数据，简单实现下任务，只做简单分享