数据库第二次试验：数据库的简单查询和连接查询

前言

为了帮助同学们完成痛苦的实验课程设计，本作者将其作出的实验结果及代码贴至CSDN中，供同学们学习参考。如有不足或描述不完善之处，敬请各位指出，欢迎各位的斧正！

一、实验目的

使学生掌握 SQL Server 查询分析器的使用方法，加深对 SQL 语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

二、实验要求

1.简单查询操作
该实验包括投影、选择条件表达式、数据排序、使用临时表等。
2.连接查询操作
该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。

三、实验原理、方法和手段

1.将查询需求用 SQL 语言表示。
2.在 SQL Server 查询分析器的输入区中输入 SQL 查询语句。
3.设置查询分析器结果区为 Standard Execute（标准执行）或 Execute to Grid 方式。
4.发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。
5.查询分析器及使用方法
查询分析器是在开发数据库应用系统时使用最多的工具。查询分析器的主要作用是编辑SQL 语句，将其发送到服务器，并将执行结果及分析显示出来（或进行存储）。查询分析功能主要通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现自动建立索引的功能。
在查询分析器中的左边窗口是对象浏览器，其中按树结构列出了数据库对象；右上方是SQL 代码区域，用于输入 SQL 的查询语句；右下方为结果区，用于显示查询结果和分析结果。对于 SQL 语句的执行结果，在结果区中可以有 4 中不同的输出形式；标准执行将结果直接显示在结果区；网格执行将结果以表格形式显示在结果区；计划执行显示执行计划；索引分析为在结果区中显示查询的索引情况。上述输出形式，可以通过菜单或按钮选择。

四、实验组织运行要求

本实验属于验证型实验，通过实验，加强对课堂讲授知识的理解。开始实验前，必须进行预习，写出实现所有查询要求的 SQL 语句。实验过程中，先集中由老师进行具体要求和注意事项的讲解，然后各自独立在机器上完成实验。实验过程中出现问题，在实验指导老师帮助下解决。

五、实验条件

（1）硬件条件：个人计算机。
（2）软件条件：Windows；MS SQL Server。

六、实验步骤

1.基本操作实验
（1）简单查询实验
1）用SQL语句表示下列操作，在学生选课库中实现其数据查询操作。
①求数学系学生的学号和姓名。
②求选修了课程的学生学号。
③求选修2号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
④求选修2号课程且成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。
⑤求数学系或计算机系姓张的学生的信息。
⑥求缺少了成绩的学生的学号和课程号。
2）在图书读者库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在 2019 年以前的借阅记录存入临时的超期借阅表。
（2）连接查询实验
用SQL语句表示，并在学生选课库中实现下列数据连接查询操作：
①查询每个学生的情况以及他所选修的课程。
②求学生的学号、姓名、选修的课程名及成绩。
③求选修1号课程且成绩为90分以上的学生学号、姓名及成绩。
④查询每一门课的间接先行课（即先行课的先行课）。
2.提高操作实验
（1）按如下表的格式，建立职工部门库和职工表、部门表，并向表中输入数据。

职工表(ZG)

职工号(Eno)	姓名(Ename)	性别(Egender)	年龄(Eage)	所在部门(Edep)
1010	李勇	男	20	11
1011	刘晨	女	19	空
1012	王敏	女	22	12
1014	张立	男	21	13

部门表(BM)

部门号(bmh)	部门名称(bmmc)	电话(dh)
11	生产科	566
12	计划科	578
13	一车间	467
14	科研所	空

（2）用 SQL 语句表示职工和部门之间的内连接、左外部连接和右外部连接，在职工部门库中实现其数据内连接和各种外查询操作。

七、实验过程

1.基本操作实验
（1）简单查询实验
1）用SQL语句表示下列操作，在学生选课库中实现其数据查询操作。
附：dbo.Student、dbo.Course、dbo.SC：



①求数学系学生的学号和姓名。

select [Sno],[Sname] from ST.dbo.Student where Sdep='MA'

②求选修了课程的学生学号。

select distinct Sno from ST.dbo.SC 

③求选修001号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

select [Sno],[Grade] from SC WHERE Cno='001' order by Grade DESC,Sno ASC

④求选修001号课程且成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。

select distinct [Sno],[Grade],[Grade]*0.8 newgrade from ST.dbo.SC where Cno='001' and Grade between 80 and 90

⑤求数学系或计算机系姓张的学生的信息。

select * from Student where (Sdep='CS' or Sdep='MA') and Sname like '张%'

⑥求缺少了成绩的学生的学号和课程号。

select [Sno],[Cno] from SC where Grade is null

2）在图书读者库中实现其查询操作：将C类的书存入永久的计算机图书表中，将借书日期在 2019 年以前的借阅记录存入临时的超期借阅表。
附：dbo.Book、dbo.BORROW：

create table librarydb.dbo.pBook
(
Bno int primary key,
Bclass nchar(10),
Bpublish nchar(20),
Bauthor nchar(20),
Bname nchar(30),
Bprice float,
foreign key (Bno) references librarydb.dbo.Book(Bno)
);
insert into librarydb.dbo.pBook select * from librarydb.dbo.Book where [Bclass]='C';

create table tempdb.dbo.#eBk
(
Bno int,
Rno int,
BRdate date,
primary key(Bno, Rno),
)
insert into #eBk select * from librarydb.dbo.BORROW where BRdate<'2019-1-1 00:00:00'
select * from #eBk

使用创建视图的方式：

create view ebk as select * from librarydb.dbo.Book where [Bclass]='C';

（2）连接查询实验
用SQL语句表示，并在学生选课库中实现下列数据连接查询操作：
①查询每个学生的情况以及他所选修的课程。

select * from ST.dbo.Student left join ST.dbo.SC on Student.Sno=SC.Sno

②求学生的学号、姓名、选修的课程名及成绩。

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

③求选修001号课程且成绩为90分以上的学生学号、姓名及成绩。

select distinct Student.Sno,Sname,grade from SC,Student where Student.Sno=SC.Sno and Cno='001' and Grade>=90

④查询每一门课的间接先行课（即先行课的先行课）。

select x.Cname Cname,y.Cname Cpno,z.Cname CpnoCpno  from Course x,Course y,Course z where x.Cpno=y.Cno and y.Cpno=z.Cno

2.提高操作实验
（1）按如下表的格式，建立职工部门库和职工表、部门表，并向表中输入数据。

职工表(Employee)

职 工 号（Eno）	姓名(Ename)	性别(Egender)	年龄(Eage)	所 在 部 门(Edep)
1010	李勇	男	20	11
1011	刘晨	女	19	NULL
1012	王敏	女	22	12
1014	张立	男	21	13

部门表(Department)

部门号(Dno)	部门名称(Dname)	电话(Dtel)
11	生产科	566
12	计划科	578
13	一车间	467
14	科研所	NULL

USE [master]
GO
CREATE DATABASE [ED2]
 ON  PRIMARY 
( NAME = N'ED2', FILENAME = N'E:\SQL\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\ED2.mdf')
 LOG ON 
( NAME = N'ED2_log', FILENAME = N'E:\SQL\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\ED2_log.ldf')
GO
USE [ED2]
GO
CREATE TABLE [ED2].[dbo].[Department](
	[Dno] [smallint] primary key,
	[Dname] [nchar](20) NULL,
	[Dtel] [int] NULL,
)
CREATE TABLE [ED2].[dbo].[Employee](
	[Eno] [int] primary key ,
	[Ename] [nchar](40) NULL,
	[Egender] [nchar](6) NULL,
	[Eage] [smallint] NULL,
	[Edep] [smallint] NULL,
)
INSERT [dbo].[Department] ([Dno], [Dname], [Dtel]) VALUES (11, N'生产科', 566)
INSERT [dbo].[Department] ([Dno], [Dname], [Dtel]) VALUES (12, N'计划科', 578)
INSERT [dbo].[Department] ([Dno], [Dname], [Dtel]) VALUES (13, N'一车间', 467)
INSERT [dbo].[Department] ([Dno], [Dname], [Dtel]) VALUES (14, N'科研所', NULL)
INSERT [dbo].[Employee] ([Eno], [Ename], [Egender], [Eage], [Edep]) VALUES (1010, N'李勇', N'男', 20, 11)
INSERT [dbo].[Employee] ([Eno], [Ename], [Egender], [Eage], [Edep]) VALUES (1011, N'刘晨', N'女', 19, NULL)
INSERT [dbo].[Employee] ([Eno], [Ename], [Egender], [Eage], [Edep]) VALUES (1012, N'王敏', N'女', 22, 12)
INSERT [dbo].[Employee] ([Eno], [Ename], [Egender], [Eage], [Edep]) VALUES (1014, N'张立', N'男', 21, 13)
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Department] FOREIGN KEY([Edep]) REFERENCES [dbo].[Department] ([Dno])
GO

（2）用 SQL 语句表示职工和部门之间的内连接、左外部连接和右外部连接，在职工部门库中实现其数据内连接和各种外查询操作。
内连接：

select Employee.*,Department.* from Employee inner join Department on Employee.Edep=Department.Dno

左外连接：

select Employee.*,Department.* from Employee left join Department on Employee.Edep=Department.Dno

右外连接：

select Employee.*,Department.* from Employee right join Department on Employee.Edep=Department.Dno

八、实验总结

在实验过程中，对于新建数据库、新建数据表格并插入数据的操作并不是很熟练，以及在新建临时表时出现了语法错误，在经过查阅书籍以及在CSDN查阅学习SQL语法后进行了多次修改与尝试，最终顺利解决了出现的语法问题
本次实验在宏观上增加了对SQL语句操作的熟练度，同时学习了表达同一目的而使用不同方式的不同SQL语句语法，并对数据库操作有了更深一步的了解。