《数据库系统原理及应用教程》(苗雪兰等，第五版) 实验三：数据库的简单查询和连接查询实验

最新推荐文章于 2021-07-04 13:56:33 发布

K2SO4钾

最新推荐文章于 2021-07-04 13:56:33 发布

阅读量3.3k

点赞数 4

分类专栏：数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/K2Fe2O7/article/details/118438448

版权

数据库专栏收录该内容

7 篇文章 10 订阅

订阅专栏

阅读前注意：

1. 本实验报告配套《数据库系统原理及应用教程》(苗雪兰等，第五版) 实验三：数据库的简单查询和连接查询实验（书上10.4节以及第六章部分内容），书本中采用Microsoft SQL Server Management Studio 2008，实验报告中采用Microsoft SQL Server Management Studio 2019。

2. 如果你的数据库课程选用的书目是这本书的话，那么大概率这会是你的实验作业。建议先自己独立完成后再作参考，数据库这门课是需要自己动手的。

3. 实验报告中可能增加了一些书中没有的附加内容，读者可以有兴趣自行完成。

4. 实验报告仅供初学者参考。

5. 请谅解实验报告中可能存在的问题或错误，欢迎指出，欢迎交流讨论。

一、实验目的

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

二、实验内容

1、基本操作实验

（1）按照书上10.4.3的要求完成简单查询和连接查询实验内容。
要求:在每个基本表中各输入至少10条记录,并能运用学过的SELECT命令进行相关数据的查询。
（2）将书中第六章的例题P150-153页例6-15至例6-24内容一一在计算机上实现。

2、提高操作实验

（1）向自设计的数据库的各表，输入多条实际数据，并实现数据的增、删、改操作。
（2）在学生数据库与图书数据库中实现其数据的内连接和各种外查询操作（P153页例6-25至例6-27内容）。

三、实验过程

1、基本操作实验

（1）按照书上10.4.3的要求完成简单查询和连接查询实验内容。

上一实验中已经建立好Student_Class数据库。首先检验数据库表中信息是否符合书上10.4.3的要求。分别执行以下语句：

USE Student_Class

SELECT * FROM Student

SELECT * FROM Course

SELECT * FROM Select_Course

执行结果分别为：

学生表：

在这里插入图片描述

课程表：

在这里插入图片描述
学生选课表：

在这里插入图片描述
将结果与书上表格对照，检验一致。
下面按照书本10.4.3要求进行查询实验：

<1>简单查询实验

在学生课程数据库中进行以下操作：

1）求数学系学生的学号和姓名。

代码如下：

SELECT s_number, s_name FROM Student WHERE s_major='数学'

查询结果：

在这里插入图片描述

2）求选修了课程的学生学号。

代码如下：

SELECT DISTINCT s_number FROM Select_Course

查询结果：

在这里插入图片描述
3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

代码如下：

SELECT s_number, c_score FROM Select_Course WHERE c_number='C1'
ORDER BY c_score DESC, s_number ASC

查询结果：

在这里插入图片描述
4）求选修课程C1且成绩在80-90的学生学号和成绩，将成绩乘以系数0.8输出。

代码如下：

SELECT s_number, c_score*0.8 FROM Select_Course
WHERE c_number='C1' AND c_score BETWEEN 80 AND 90

查询结果：

在这里插入图片描述
5）求数学系或计算机系姓张的学生的信息。

代码如下：

SELECT * FROM Student
WHERE s_major IN ('计算机', '数学') AND s_name LIKE '张%'

查询结果：

在这里插入图片描述
6）求缺少了成绩的学生的学号和课程号。

代码如下：

SELECT s_number, c_number FROM Select_Course
WHERE c_score IS NULL

查询结果：

在这里插入图片描述

<2>连接查询实验

1）查询每个学生的情况以及他（她）所选修的课程。

代码如下：

SELECT Student.*, Select_Course.* FROM Student, Select_Course
WHERE Student.s_number=Select_Course.s_number

查询结果：

在这里插入图片描述
2）求学生的学号、姓名、选修的课程名以及成绩。

代码如下：

SELECT Student.s_number, s_name, c_name, c_score
FROM Student, Course, Select_Course
WHERE Student.s_number=Select_Course.s_number 
AND Course.c_number=Select_Course.c_number

查询结果：

在这里插入图片描述
3）求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

代码如下：

SELECT Student.s_number, s_name, c_score
FROM Student, Select_Course
WHERE Student.s_number=Select_Course.s_number 
    AND c_number='C1' AND c_score>90

查询结果：

在这里插入图片描述
4）查询每一门课的间接先行课（即先行课的先行课）

代码如下：

SELECT A.c_number, A.c_name, B.c_precourse
FROM Course A, Course B
WHERE A.c_precourse=B.c_number

查询结果：

在这里插入图片描述

（2）将书中第六章的例题P150-153页例6-15至例6-24内容一一在计算机上实现。

【例6.15】求数学系学生的学号和姓名。

代码如下：

SELECT s_number, s_name
FROM Student
WHERE s_major='数学'

查询结果：

在这里插入图片描述

【例6.16】求选修了课程的学生学号。

代码如下：

SELECT DISTINCT s_number FROM Select_Course

查询结果: 在这里插入图片描述

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

代码如下：

SELECT s_number, c_score FROM Select_Course WHERE c_number='C1'
ORDER BY c_score DESC, s_number ASC

查询结果：
在这里插入图片描述

【例6.18】求选修课程C1且成绩在80-90分之间的学生学号和成绩，并将成绩乘以0.8输出。

代码如下：

SELECT s_number, c_score*0.8 FROM Select_Course
WHERE c_number='C1' AND c_score BETWEEN 80 AND 90

查询结果：
在这里插入图片描述

【例6.19】求数学系或计算机系姓张的学生的信息。

代码如下：

SELECT * FROM Student
WHERE s_major IN ('计算机', '数学') AND s_name LIKE '张%'

查询结果：
在这里插入图片描述

【例6.20】求缺少了成绩的学生的学号和课程号。

代码如下：

SELECT s_number, c_number FROM Select_Course
WHERE c_score IS NULL

查询结果：
在这里插入图片描述

【例6.21】将计算机类的图书存入永久的计算机图书表，将借书日期在1999年以前的借阅记录存入临时的超期借阅表。

代码如下：

USE Library_Reader

SELECT * INTO 计算机图书
FROM Book
WHERE b_type='计算机'

SELECT* INTO #超期借阅
FROM Loan
WHERE l_date<'1999-01-01'

SELECT * FROM 计算机图书

执行结果：
在这里插入图片描述

【例6.22】查询每个学生的情况及他（她）所选修的课程。

代码如下：

SELECT Student.*, Select_Course.* FROM Student, Select_Course
WHERE Student.s_number=Select_Course.s_number

查询结果：
在这里插入图片描述

【例6.23】求学生的学号、姓名、选修的课程名及成绩。

代码如下：

SELECT Student.s_number, s_name, c_name, c_score
FROM Student, Course, Select_Course
WHERE Student.s_number=Select_Course.s_number 
AND Course.c_number=Select_Course.c_number

查询结果：
在这里插入图片描述

【例6.24】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

代码如下：

SELECT Student.s_number, s_name, c_score
FROM Student, Select_Course
WHERE Student.s_number=Select_Course.s_number AND c_number='C1' AND c_score>90

查询结果：
在这里插入图片描述

【例6.25】求学生的学号、姓名、选修的课程名及成绩。

代码如下：

SELECT Student.s_number, s_name, c_name, c_score
FROM Student INNER JOIN Select_Course ON Student.s_number=Select_Course.s_number
INNER JOIN Course ON Course.c_number=Select_Course.c_number

查询结果：
在这里插入图片描述

【例6.26】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

代码如下：

SELECT Student.s_number, s_name, s_name, c_score
FROM Student INNER JOIN Select_Course ON Student.s_number=Select_Course.s_number
WHERE c_number='C1' AND c_score>90

查询结果：
在这里插入图片描述

2、提高操作实验

1）向自设计的数据库的各表，输入多条实际数据，并实现数据的增、删、改操作。

以下以笔者设计的一个房屋租赁数据库进行演示。
首先设计该数据库：

USE [Houses Loan]
 
 --房屋表（房屋号，全称，房屋位置，联系电话）
CREATE TABLE Houses
  (
     h_number          CHAR(20) NOT NULL, --房屋号
     h_name            VARCHAR(30),--全称
     h_location        VARCHAR(30),--房屋位置
     h_tel             CHAR(20),--联系电话
     PRIMARY KEY(h_number),
  )
 
--房东表（身份证号，姓名，地址，性别，电话）
CREATE TABLE Staff
  (
     s_id    CHAR(20) NOT NULL,
     s_name  CHAR(20) NOT NULL,
     s_place CHAR(20),
     s_sex   CHAR(10),
     s_tel   CHAR(20),
     PRIMARY KEY (s_id),
     CHECK(s_sex IN('F', 'M'))
  )
 
--租房表（租房，所属租赁公司，房屋面积，房屋状态，价格）
CREATE TABLE Room
  (
     r_number CHAR(10) NOT NULL,
     h_number CHAR(20) NOT NULL,
     r_size   CHAR(20),
     r_status CHAR(10),
     r_price  NUMERIC NOT NULL,
     PRIMARY KEY(r_number, h_number),
     CHECK (r_status IN('空闲', '有人已租', '售出')),
  )
 
--顾客表（顾客身份证号，姓名，年龄，电话）
CREATE TABLE Customer
  (
     c_id         CHAR(20) NOT NULL,
     c_name       CHAR(20) NOT NULL,
     c_age        SMALLINT,
     c_tel        CHAR(20),
     PRIMARY KEY(c_id),
  )
 
--租房登记表（顾客身份证号，房屋号，租赁公司，租房时间，退房时间）
CREATE TABLE Check_in
  (
     c_id            CHAR(20) NOT NULL,
     r_number        CHAR(10) NOT NULL,
     h_number        CHAR(20) NOT NULL,
     c_checkin_time  DATETIME,
     c_checkout_time DATETIME,
     PRIMARY KEY(c_id, r_number, h_number))

向其中录入少量信息：

USE [Houses Loan]
 
INSERT INTO Houses
VALUES ('1A32D',
        'xxx精品房',
		'武汉市洪山区',
		'13300000000'),
		('22Q12D',
        'xxx精品房',
		'成都市金牛区',
		'13311111111')

SELECT * FROM Houses

查询结果如下：

在这里插入图片描述
修改信息：

USE [Houses Loan]
 
UPDATE Houses
SET    h_tel = '12345678999'
WHERE  h_number = '1A32D'
 
SELECT *
FROM   Houses

修改结果如下：

在这里插入图片描述
删除信息：

USE [Houses Loan]
 
DELETE FROM Houses
WHERE       h_tel = '12345678999'
 
SELECT *
FROM   Houses

删除结果如下：

在这里插入图片描述

2）在学生数据库与图书数据库中实现其数据的内连接和各种外查询操作（P153页例6-25至例6-27内容，书上有操作代码，这里以笔者自建的数据库进行操作）。

重新录入一些信息：

USE [Houses Loan]

-- 清除原有的信息
TRUNCATE TABLE Check_in
TRUNCATE TABLE Customer
TRUNCATE TABLE Houses
TRUNCATE TABLE Room
TRUNCATE TABLE Staff

INSERT INTO Check_in
VALUES ('C1','R1','01','2010-02-01','2021-02-03'),
       ('C1','R2','02','2010-02-01','2021-02-03'),
	   ('C2','R3','03','2010-02-05','2021-02-06'),
	   ('C3','R4','04','2010-02-07','2021-02-08'),
	   ('C3','R5','05','2010-02-11','2021-02-13'),
	   ('C3','R6','06','2010-02-12','2021-02-18'),
	   ('C4','R7','07','2010-02-17','2021-02-28')

INSERT INTO Customer
VALUES ('C1','邓**',20,'19107121493'),
       ('C2','汪**',19,'19119382910'),
	   ('C3','赵**',20,'19123345623'),
	   ('C4','巩**',18,'19119373988')

INSERT INTO Houses
VALUES ('H1','精装房','成都市金牛区','19137581111'),
       ('H2','精装房','武汉市洪山区','19137175311'),
	   ('H3','毛坯房','北京市朝阳区','13028489923')

INSERT INTO Room
VALUES ('R1','H1','108','空闲',10800),
       ('R2','H1','108','空闲',13200),
	   ('R3','H2','122','空闲',11100),
	   ('R4','H2','122','空闲',16780),
	   ('R5','H2','100','空闲',9800),
	   ('R6','H3','114','空闲',20800),
	   ('R7','H3','118','空闲',20800)

INSERT INTO Staff
VALUES ('0001','张三','武汉','M','13302948575')

SELECT * FROM Check_in
SELECT * FROM Customer
SELECT * FROM Houses
SELECT * FROM Room
SELECT * FROM Staff

录入结果：

在这里插入图片描述

【例6.25】求所有租赁房屋租客的信息。

代码如下：

SELECT Customer.c_id, c_name, Check_in.r_number, Check_in.h_number
FROM Customer INNER JOIN Check_in ON Customer.c_id=Check_in.c_id
INNER JOIN Room ON Room.r_number=Check_in.r_number

查询结果：
在这里插入图片描述
【例6.26】求租赁了房屋R3-R6且在2010年2月10日之后租赁房屋租客的信息。
代码如下：

SELECT Customer.c_id, c_name, Check_in.r_number, Check_in.h_number, Check_in.c_checkin_time
FROM Customer INNER JOIN Check_in ON Customer.c_id=Check_in.c_id
INNER JOIN Room ON Room.r_number=Check_in.r_number
WHERE Check_in.r_number IN('R3','R4','R5','R6') AND c_checkin_time>'2010-02-10'

查询结果：
在这里插入图片描述
【例6.27】内连接、左外部连接、右外部连接。

代码如下：

--内连接
SELECT Check_in.*, Check_in.r_number, Check_in.h_number
FROM Check_in INNER JOIN Room ON Check_in.r_number=Room.r_number

查询结果：
在这里插入图片描述
再录入一些信息后，进行内连接、左外部连接、右外部连接查询：

--再插入一些信息
INSERT INTO Check_in
VALUES ('C4','R8','08',NULL,'2021-02-03'),
       ('C4','R9','09',NULL,'2021-02-03'),
	   ('C4','R10','10',NULL,'2021-02-06')

INSERT INTO Room
VALUES ('R8','H3','108',NULL,13000),
       ('R9','H3','108',NULL,19000)

--内连接
SELECT Check_in.*, Check_in.r_number, Check_in.h_number, Check_in.c_checkin_time, Room.r_status
FROM Check_in INNER JOIN Room ON Check_in.r_number=Room.r_number

--左外部连接
SELECT Check_in.*, Check_in.r_number, Check_in.h_number, Check_in.c_checkin_time, Room.r_status
FROM Check_in LEFT JOIN Room ON Check_in.r_number=Room.r_number

--右外部连接
SELECT Check_in.*, Check_in.r_number, Check_in.h_number, Check_in.c_checkin_time, Room.r_status
FROM Check_in RIGHT JOIN Room ON Check_in.r_number=Room.r_number