数据库---1-8个实验

实验1---数据库与数据表的创建与管理

1.在SQL Server中创建数据库，具体要求如下：

（1）数据库名称为Test1。

（2）主要数据文件：逻辑文件名为Test1Data1，物理文件名为Test1Data1.mdf，初始容量为5MB，最大容量为10MB，递增量为1MB。

（3）次要数据文件：逻辑文件名为Test1Data2，物理文件名为Test1Data2.ndf，初始容量为2MB，最大容量为10MB，递增量为1MB，放在testgroup1文件组中。

（4）事务日志文件：逻辑文件名为Test1Log1，物理文件名为Test1Log1.ldf，初始容量为1MB，最大容量为5MB，递增量为20%。

2.在Test1数据库中创建下面5张表，表结构要求如下：

【操作步骤】

1）按Test1数据库要求用SQL语言创建Test1。

create database Test1    

on primary(              

name=Test1Data1,     

filename='d:\db\Test1Data1.mdf',     

size=5MB,            

maxsize=10MB,        

filegrowth=1MB),     

filegroup testgroup1         

(name=Test1Data2,   

filename='d:\db\Test1Data2.ndf',     

size=2MB,           

maxsize=10MB,        

filegrowth=1MB)       

log on(                  

name=Test1Log1,      

filename='d:\db\Test1Log1.ldf',    

size=1MB,         

maxsize=5MB,  

filegrowth=20%)   

2）按Department表的结构要求用SQL语言创建Department表。

Use Test1

create table Department(

DeptID char(3) primary key,

DeptName char(20) not null unique,

Place varchar(30),

ManagerID char(6))

3）按Post表的结构要求用SQL语言创建Post表。

create table Post(

PID char(3) primary key,

Pname varchar(10) not null unique,

Pdesc text,

Pallowance decimal(7,2) default 2000)

4）按Employee表的结构要求用SQL语言创建Employee表。

create table Employee(

EmpID char(6) primary key,

Name varchar(8) not null,

Birth date not null,

Sex char(2) not null default ‘男’,

Phone char(11),

HireDate date not null default getdate(),

PID char(10) references Post (PID),

DeptID char(3) references Department (deptID))

5）按Salary表的结构要求用SQL语言创建Salary表。

create table Salary(

SID int primary key identity,

EmpID char(6) not null references Employee (empID),

Bsalary decimal(7,2) not null,

Psalary decimal(7,2) not null,

Sdate date not null default getdate())

【分析与思考】

1.关于NOT NULL

（1）在定义基本表语句时，NOT NULL约束的作用是什么？

 设置非空约束。

（2）主码列修改成允许NULL能否操作？为什么？ 

 不能，因为主码的取值范围为唯一且非空。

2.关于外码

根据下面设计的表结构，Employee表的外键能否设置成功？思考外码设置需要注意哪些问题？

Department表的结构

字段名	字段描述	数据类型	主键	外键
dno	部门号	INT(4)
dname	部门名	VARCHAR(20)

Employee表的结构

字段名	字段描述	数据类型	主键	外键
eno	员工号	INT(10)
dno	所在部门号	Char(10)		√
name	姓名	VARCHAR(20)

（1）如果主表（父表）无数据，从表（子表）的数据能输入吗？为什么？ 

不能，因为从表中有外码，外码取值非空时必须为父表中的某个主码值。 

（2）先创建从表（子表），再创建主表（父表）是否可以？为什么？ 

不可以，因为子表中的外码要引用父表中的主码。 

3.关于主码和唯一约束

（1）唯一约束列是否允许NULL值？为什么？

可以为空，但是只能有一个值为空。 

（2）一张表可以设置几个主码，可以设置几个唯一约束？ 

一张表只能有一个主码，但是可以有很多个唯一约束。 

实验2---数据插入、修改、删除操作SQL语法格式

【实验内容】

某超市的食品管理的数据库的Food表，Food表的定义如表所示，请完成插入数据、更新数据和删除数据。

Food表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一
foodid	食品编号	Char(6)	是	否	是	是
Name	食品名称	VARCHAR(20)	否	否	是	否
Company	生产厂商	VARCHAR(30)	否	否	是	否
Price	价格（单位：元）	FLOAT	否	否	是	否
Product_time	生产日期	Date	否	否	否	否
Validity_time	保质期（单位：年）	Decimal(3,1)	否	否	否	否
address	厂址	VARCHAR(50)	否	否	否	否

按照下列要求进行操作：

【操作步骤】

1）按Food表的结构要求用SQL语言创建Food表。

create table food

(foodid char(6)primary key,

Name varchar(20) not null,

Company varchar(30) not null,

Price float not null,

Product_time date,

validity_time decimal(3,1),

address varchar(50));

2）采用2种方式，将表的记录插入到Food表中。

方法一：不指定具体的字段，

插入数据： 'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'。

方法二：依次指定food表的字段，

插入数据： 'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北')。

分别写出相应语句。

方法一：

insert into food

values('001','QQ饼干','QQ饼干厂',2.5,'2020-4-9',3,'北京')

方法二：

insert into food(foodid,Name,company,price,product_time,validity_time,address)

values ('002', 'MN牛奶','MN牛奶厂',3.5,'2021-5-5',1,'河北')

3）将“MN牛奶厂”的厂址(address)改为“内蒙古”，并且将价格改为3.2。

update food

set address = '内蒙古',price = 3.2

where company = 'MN牛奶厂';

4）将厂址在北京的公司的保质期(validity_time)都改为5年。

update food

set validity_time = 5

where address = '北京';

5）删除过期食品的记录。若当前年份-生产年份>保质期（validity_time），则视为过期食品。

delete from food

where year(getdate())-year(product_time) > validity_time;

6）删除厂址为“北京”的食品的记录。

delete from food where address = '北京';

【分析与思考】

1）Update和alter的区别？

Update和alter都是修改，但Update属于DML语言，是针对数据进行修改；alter是针对数据库的对象（结构）进行修改。

2）DROP命令和DELETE命令的本质区别是什么？

Drop是删除表结构命令，在删除表结构的同时也会删除表中数据；

Delete是删除表中的数据，不会删除表结构

实验3---简单查询

【实验内容】

数据库XKGL中有7张表，表结构如下：

Department表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
DepartmentID	系号	char(4)	是	否	是	否	否
DepartmentName	系名	varchar(20)	否	否	是	否	否
DepartmentHeader	系主任	varchar(8)	否	否	是	否	否
TeacherNum	教师人数	int	否	否	是	否	否

Class表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
ClassID	班号	char(8)	是	否	是	否	否
ClassName	班名	varchar(20)	否	否	是	否	否
Monitor	班长	char(8)	否	否	是	否	否
StudentNum	学生人数	int	否	否	是	否	否
DepartmentID	系号	char(4)	否	是	否	否	否

Student表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
Student ID	学号	char(11)	是	否	是	否	否
Student name	姓名	char(8)	否	否	是	否	否
Sex	性别	char(2)	否	否	是	否	否
Birth	出生日期	date	否	否	是	否	否
EntranceTime	入学时间	datetime	否	否	是	否	否
HomeAddr	家庭地址	varchar(50)	否	否	否	否	否
ClassID	班号	char(4)	否	是	否	否	否

Course表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
CourseID	课程号	char(8)	是	否	是	否	否
CourseName	课程名	varchar(60)	否	否	是	否	否
BookName	书名	varchar(80)	否	否	是	否	否
credit	学分	int	否	否	是	否	否

grade表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
CourseID	课程号	char(8)	是	是	是	否	否
StudentID	学号	char(11)	是	是	是	否	否
Semester	开课学期	int	否	否	是	否	否
SchoolYear	学年	int	否	否	是	否	否
Grade	成绩	Decimal(4,1)	否	否	否	否	否

Teacher表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
TeacherID	教师号	char(8)	是	否	是	否	否
Teachername	教师姓名	char(8)	否	否	是	否	否
Sex	性别	char(2)	否	否	是	否	否
Birth	出生日期	date	否	否	是	否	否
Profession	开课学期	char(8)	否	否	是	否	否
Telephone	学年	char(11)	否	否	否	否	否
HomeAddr	家庭地址	varchar(50)	否	否	否	否	否
DepartmentID	系号	char(4)	否	是	否	否	否

Schedule表的结构

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
ID	编号	int	是	否	是	是	是
TeacherID	教师号	char(8)	否	是	是	否	否
CourseID	课程号	char(8)	否	是	是	否	否
ClassID	班级号	char(8)	否	是	是	否	否
Semester	开课学期	int	否	否	是	否	否
SchoolYear	学年	int	否	否	否	否	否

按照下列要求进行操作：

【操作步骤】

1）按Schedule表的结构要求用SQL语言创建Schedule表（此题不运行，只写代码）。

Create table Schedule(

ID int identity primary key,

TeacherID char(8) not null references Teacher(TeacherID),

CourseID char(8) not null references Course(CourseID),

ClassID char(8) not null references Class(ClassID),

Semester int not null,

SchoolYear int)

2）查询teacher表中所有教师的姓名和年龄。

select teachername 姓名,year(getdate())-year(birth) 年龄

from teacher

3）查询所有系的信息。

SELECT * FROM Department

4）查询学分值大于等于4的课程的名称。

select coursename

from Course

where credit>=4

5）查询07010211班的女生信息。

select *

from Student

where ClassID='07010211' and Sex='女'

6）查询学生姓名中第2个字为“丽”的学生信息。

select *

from student

where studentname like '_丽%'

7）查询年龄大于40岁的教授和副教授的姓名和性别。

select teachername,sex

from Teacher

where year(getdate())-year(birth)>40 and profession in ('教授','副教授')

8）查询course表中前5行数据。

select top 5 *

from course

9）查询选修了Dp010001号课程的学生的学号和成绩，将查询结果按成绩降序排序，成绩相同按学号升序排序。

select studentid,grade

from grade

where courseid='Dp010001'

order by grade desc,studentid asc

10）查询course表中的最大学分的课程名。(用order by子句)。

select top 1 with ties coursename

from course

order by credit desc

11）查询不同职称的教师人数。

select profession,count(teacherid)

from teacher

group by profession

12）统计各个班的学生人数。

select classid,count(studentid)

from student

group by classid

13）查询课程Dp030001的最高分、最低分和平均分。

select max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分

from grade

where courseid='Dp030001'

14）查询grade表中选修了3门以上课程的学生学号。

select studentid

from grade

group by studentid

having count(courseid)>3

15）查询不同职称不同性别教师人数。

select profession,sex,count(teacherid) as 人数

from teacher

group by profession,sex

实验4---简单查询

【操作步骤】

1）查询课程名及该课程的得分情况。

select course.courseid,grade

from course join grade

on course.courseid=grade.courseid

2）查询教师姓名及其所教授的课程名。

select distinct(teacher.teachername),course.coursename

from teacher join schedule on teacher.teacherid=schedule.teacherid

 join course on schedule.courseid=course.courseid

3）查询和‘刘芳’老师同职称的教师信息。

select t2.* 

from teacher t1 join teacher t2 on t1.teacherid=t2.teacherid

where t1.profession='讲师'

4）查询比 ‘刘芳’老师年龄小的教师信息。

select t2.* 

from teacher t1 join teacher t2 on t2.brith > t1.brith

where t1.teachername='刘芳'

5）查询全部教师的授课情况,包括没有授课的老师。

select teacher.teachername,courseid,classid

from teacher left join schedule on teacher.teacherid=schedule.teacherid

6）查询既选修了Dp010001又选修了Dp010004号课程的学生的学号，姓名。

select student.studentid,studentname

from student join grade on student.studentid=grade.studentid

             join course on grade.courseid=course.courseid

where course.courseid='Dp010001' and course.courseid='Dp010004'

7）“大学英语（一）”成绩不及格的学生人数是多少？

select count(student.studentid) as 不及格人数

from student join grade on student.studentid=grade.studentid

             join course on grade.courseid=course.courseid

where course.coursename='大学英语（一）' and grade.grade < 60

8）计算机系的平均成绩为多少？

select avg(grade) as 平均成绩

from grade join schedule on grade.courseid=schedule.courseid

           join class on schedule.classid=class.classid

   join department on class.departmentid=department.departmentid

where departmentname='计算机系'

9）查询全部教师、全部课程的课程安排。

select *

from teacher full join schedule on teacher.teacherid=schedule.teacherid

             full join course on schedule.courseid=course.courseid

10）查询“计算机应用基础”课程前三名的学生学号、姓名和成绩。

select top 3 student.studentid,student.studentname,grade

from student join grade on student.studentid=grade.studentid

             join course on grade.courseid=course.courseid

where coursename='计算机应用基础'

order by grade desc

【分析与思考】

1. 内连接与外连接有什么区别？

内连接（inner join）：取出两张表中匹配到的数据，匹配不到的不保留

外连接（outer join）：取出连接表中匹配到的数据，匹配不到的也会保留，值为NULL

内连接：也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。由于内连接是从结果表中删除与其他连接表中没有匹配的所有行，所以内连接可能会造成信息的丢失。内连接是保证两个表中所有行都满足连接条件。

外连接：不仅包含符合连接条件的行，还包含左表(左连接时)、右表(右连接时)或两个边接表(全外连接)中的所有数据行。SQL外连接共有三种类型：左外连接(关键字为LEFT OUTER JOIN)、右外连接(关键字为RIGHT OUTER JOIN)和全外连接(关键字为FULL OUTER JOIN)。外连接的用法和内连接一样，只是将INNER JOIN关键字替换为相应的外连接关键字即可。

内连接只显示符合连接条件的记录，外连接除了显示符合条件的记录外，还显示表中的记录

实验5---嵌套查询

【操作步骤】

1）嵌套查询 “计算机系”的班级信息。

select * 

from Class

where DepartmentID=

(select DepartmentID

from Department

where DepartmentName='计算机系')

2）嵌套查询“计算机系”的全部学生信息。

SELECT *

FROM student

WHERE classid IN

(SELECT ClassID

FROM class

WHERE DepartmentID =

(SELECT DepartmentID

FROM department

WHERE DepartmentName='计算机系'))

3）嵌套查询Dp010001课程中成绩未达到该门课程平均分的选课信息。

SELECT *

FROM grade

WHERE CourseID = 'Dp010001'

AND grade <

(SELECT AVG(grade)

FROM grade

WHERE CourseID='Dp010001')

4）嵌套查询Dp010001课程中最低分的学生信息。

SELECT *

FROM student

WHERE StudentID IN

(SELECT StudentID

FROM grade

WHERE CourseID = 'Dp010001'

AND grade =

(SELECT MIN(grade)

FROM grade

WHERE CourseID = 'Dp010001'))

5）嵌套查询Cs010901班比Cs010902班年龄都大的学生信息。

SELECT *

FROM student

WHERE ClassID='Cs010901'

AND Birth < ALL

(SELECT Birth

FROM student

WHERE ClassID='Cs010902')

6）用带EXISTS子查询选修了Dp010001的学生学号和姓名。

SELECT StudentID,StudentName

FROM student

WHERE EXISTS

(SELECT *

FROM grade

WHERE CourseID='Dp010001'

AND student.StudentID=grade.StudentID)

7）查询选修了Dp010001课程而没有选修Dp010002号课程的学生学号。

SELECT StudentID

FROM  grade

WHERE CourseID='Dp010001' 

AND StudentID NOT IN

(SELECT StudentID

FROM grade

WHERE CourseID='Dp010002')

8）合并显示教师中的男性教师和有教授职称的教师。

SELECT *

FROM Teacher

WHERE Sex='男'

UNION

SELECT *

FROM Teacher

WHERE Profession='教授'

【分析与思考】

1.  “=”与IN在什么情况下作用相同？

当in的值表中只有一个值的时候，“=”与IN作用相同。

实验7---函数创建与管理

【操作步骤】

1）定义一个根据学生姓名查询该生选修课程门数的函数stu_count。

或者

2）分别用select和print调用函数stu_count，查询张宏选修的课程门数。

3）定义一个根据系名和课程名，查询该系学生在该门课程上程的平均分的函数sdept_avggrade。

或者

4）分别用select和print调用函数sdept_avggrade,查询“计算机系”学生在“数据库原理与应用”这门课程上的平均成绩。

5）创建一个函数f1，根据学生的学号和选修的课程号查询该生在该门课程上的成绩，然后返回这个成绩所对应的等级（90及以上：优秀；80及以上：良好；70及以上：中等；60及以上：及格；60以下：不及格）。

或者

6）创建一个自定义函数f2，根据学生的学号查询该学生的姓名、选修课程名称和对应课程的成绩。（表值函数）

或者

7）调用函数f2,查询“St0109010001”号学生的姓名、选修课程名称和对应课程的成绩。

8）使用DROP FUNCTION语句来删除f1和f2函数。

【分析与思考】

1）变量有哪些类型，用什么符号标识？

变量分为全局变量与局部变量，全局变量由系统定义和维护，以@@开头；局部变量由用户定义和使用，以@开头，局部变量用declare定义。

2）用户自定义函数有哪些类型？

SQL Server中，用户自定义函数可分为三种类型：标量函数、内联表值函数和多语句表值函数。

标量型函数只能返回单个值，内联表值函数和多语句表值函数返回的结果是一张数据表。

实验8---存储过程的创建与管理

【操作步骤】

1）创建存储过程p_AvgGrade1，查询出每门课程的平均成绩。

 

2）创建存储过程p_AvgGrade2，实现根据指定课程（课程名）查询出该课程的平均成绩。

3）调用存储过程p_AvgGrade2，查询“数据库原理与应用”课程的平均成绩。

 

4）创建存储过程p_AvgGrade3，通过输入教师号及课程号查询某个老师所授的某门课程的平均成绩和最高成绩，并通过输出参数返回。 

5）调用存储过程p_AvgGrade3，查询dep01001号教师所授的Dp010003号课程的平均成绩和最高成绩。

 

6）创建存储过程p_SumProf，通过输入职称查询该职称的教师数（若没有指定职称，默认为教授）。

 

 7）调用存储过程p_SumProf，查询教师中教授的人数。

8）创建存储过程p_UpdateStuNum，通过输入班级号、人数，更改该班级的人数。

 

9）调用存储过程p_UpdateStuNum，将“Cs010901”的人数改为60。

 

 10）创建存储过程p_DeleteSche，通过输入教师号删除该教师的排课信息。

11）调用存储过程p_DeleteSche，删除“dep03003”号教师的排课信息。 

12）删除存储过程p_AvgGrade1和p_AvgGrade2。

 

【分析与思考】

1）存储过程的作用什么？为什么利用存储过程可以提高数据库的操作效率？

存储过程将多次重复执行的实现特定功能的代码段以一个存储单元的形式存在服务器上，并可以通过其过程名来进行反复调用。

执行速度更快：在数据库中保存的存储过程都是编译过的；减少网络流通量：调用时只需传输存储过程的名称。所以可以提高数据库的操作效率。

2）存储过程的参数有几种形式？

输入参数、输出参数两种。