本文最后更新于2020年12月9日，已超过 6 个月没更新！
数据库实验三：数据库查询和数据操纵
实验目的
- 掌握各种查询的使用方法。
- 掌握数据操纵的使用方法。
实验内容
根据实验2中给出的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
1. 使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
① 查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
② 查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
③ 查询选修课程号为K002的学生的学号，姓名（使用连接查询，普通子查询，相关子查询）。
④ 查询没有同时选修K001和M001课程的学号，姓名，课程号和三次成绩。
2. 使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
① 在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05，出生日期为1991-01-01。
② 将所有课程的学分数变为原来的两倍。
③ 删除张乐的信息。
实验二SQL代码
USE [SCE]
GO
/****** Object: Table [dbo].[学生作业表] Script Date: 11/28/2016 14:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[学生作业表](
[课程号] [char](10) NOT NULL,
[学号] [char](10) NOT NULL,
[作业1成绩] [int] NULL,
[作业2成绩] [int] NULL,
[作业3成绩] [int] NULL,
CONSTRAINT [SC_Prim] PRIMARY KEY CLUSTERED
(
[课程号] ASC,
[学号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K001 ', N'0433 ', 60, 75, 75)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K001 ', N'0529 ', 70, 70, 60)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K001 ', N'0531 ', 70, 80, 80)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K001 ', N'0591 ', 80, 90, 90)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K002 ', N'0496 ', 80, 80, 90)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K002 ', N'0529 ', 70, 70, 85)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K002 ', N'0531 ', 80, 80, 80)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K002 ', N'0538 ', 65, 75, 85)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K002 ', N'0592 ', 75, 85, 85)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K006 ', N'0531 ', 80, 80, 90)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'K006 ', N'0591 ', 80, 80, 80)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'M001 ', N'0496 ', 70, 70, 80)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'M001 ', N'0591 ', 65, 75, 75)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'S001 ', N'0531 ', 80, 80, 80)
INSERT [dbo].[学生作业表] ([课程号], [学号], [作业1成绩], [作业2成绩], [作业3成绩]) VALUES (N'S001 ', N'0538 ', 60, NULL, 80)
/****** Object: Table [dbo].[学生表] Script Date: 11/28/2016 14:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[学生表](
[学号] [char](10) NOT NULL,
[姓名] [char](20) NOT NULL,
[性别] [char](2) NOT NULL,
[专业班级] [char](50) NOT NULL,
[出生日期] [date] NOT NULL,
[联系电话] [char](15) NULL,
PRIMARY KEY CLUSTERED
(
[学号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0433 ', N'张艳 ', N'女', N'生物04 ', CAST(0x0D110B00 AS Date), N' ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0496 ', N'李越 ', N'男', N'电子04 ', CAST(0x680D0B00 AS Date), N'13812902331 ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0529 ', N'赵欣 ', N'男', N'会计05 ', CAST(0x4D0D0B00 AS Date), N'13502222908 ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0531 ', N'张志国 ', N'男', N'生物05 ', CAST(0x0A110B00 AS Date), N'13312567890 ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0538 ', N'于兰兰 ', N'女', N'生物05 ', CAST(0x650D0B00 AS Date), N'13312004030 ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0591 ', N'王丽丽 ', N'女', N'电子04 ', CAST(0x820D0B00 AS Date), N'13320809766 ')
INSERT [dbo].[学生表] ([学号], [姓名], [性别], [专业班级], [出生日期], [联系电话]) VALUES (N'0592 ', N'王海强 ', N'男', N'电子04 ', CAST(0x3E110B00 AS Date), N' ')
/****** Object: Table [dbo].[课程表] Script Date: 11/28/2016 14:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[课程表](
[课程号] [char](10) NOT NULL,
[课程名] [char](50) NOT NULL,
[学分数] [float] NOT NULL,
[学时数] [int] NOT NULL,
[任课老师] [char](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[课程号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[课程表] ([课程号], [课程名], [学分数], [学时数], [任课老师]) VALUES (N'K001 ', N'计算机图形学 ', 2.5, 40, N'胡晶晶 ')
INSERT [dbo].[课程表] ([课程号], [课程名], [学分数], [学时数], [任课老师]) VALUES (N'K002 ', N'计算机应用基础 ', 3, 48, N'任泉 ')
INSERT [dbo].[课程表] ([课程号], [课程名], [学分数], [学时数], [任课老师]) VALUES (N'K006 ', N'数据结构 ', 4, 64, N'马跃先 ')
INSERT [dbo].[课程表] ([课程号], [课程名], [学分数], [学时数], [任课老师]) VALUES (N'M001 ', N'政治经济学 ', 4, 64, N'孔繁新 ')
INSERT [dbo].[课程表] ([课程号], [课程名], [学分数], [学时数], [任课老师]) VALUES (N'S001 ', N'高等数学 ', 3, 48, N'赵晓尘 ')
/****** Object: Default [DF__学生表__性别__0519C6AF] Script Date: 11/28/2016 14:04:33 ******/
ALTER TABLE [dbo].[学生表] ADD DEFAULT ('男') FOR [性别]
GO
/****** Object: Check [Score_Chk1] Script Date: 11/28/2016 14:04:33 ******/
ALTER TABLE [dbo].[学生作业表] WITH CHECK ADD CONSTRAINT [Score_Chk1] CHECK (([作业1成绩]>=(0) AND [作业1成绩]<=(100)))
GO
ALTER TABLE [dbo].[学生作业表] CHECK CONSTRAINT [Score_Chk1]
GO
/****** Object: Check [Score_Chk2] Script Date: 11/28/2016 14:04:33 ******/
ALTER TABLE [dbo].[学生作业表] WITH CHECK ADD CONSTRAINT [Score_Chk2] CHECK (([作业2成绩]>=(0) AND [作业2成绩]<=(100)))
GO
ALTER TABLE [dbo].[学生作业表] CHECK CONSTRAINT [Score_Chk2]
GO
/****** Object: Check [Score_Chk3] Script Date: 11/28/2016 14:04:33 ******/
ALTER TABLE [dbo].[学生作业表] WITH CHECK ADD CONSTRAINT [Score_Chk3] CHECK (([作业3成绩]>=(0) AND [作业3成绩]<=(100)))
GO
ALTER TABLE [dbo].[学生作业表] CHECK CONSTRAINT [Score_Chk3]
GO
实验结果
1. 使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
① 查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
连接查询：
SELECT 学生表1.*
FROM 学生表 AS 学生表1,学生表 AS 学生表2
WHERE 学生表2.姓名='张志国' AND 学生表1.专业班级=学生表2.专业班级;
子查询：
SELECT *
FROM 学生表
WHERE 专业班级 =
(
SELECT 专业班级
FROM 学生表
WHERE 姓名 = '张志国'
);
② 查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
连接查询：
SELECT 课程表1.*
FROM 课程表 AS 课程表1,课程表 AS 课程表2
WHERE 课程表2.课程名 = '计算机应用基础' AND 课程表1.学时数>课程表2.学时数;
子查询：
SELECT *
FROM 课程表
WHERE 学时数 >
(
SELECT 学时数
FROM 课程表
WHERE 课程名 = '计算机应用基础'
);
③ 查询选修课程号为K002的学生的学号，姓名（使用连接查询，普通子查询，相关子查询）。
连接查询：
SELECT 学生表.学号,学生表.姓名
FROM 学生作业表,学生表
WHERE 学生作业表.课程号='K002'AND 学生作业表.学号 = 学生表.学号;
普通子查询：
SELECT DISTINCT 学生表.学号,学生表.姓名
FROM 学生作业表,学生表
WHERE 学生表.学号 IN
(
SELECT 学生表.学号
FROM 学生作业表
WHERE 学生作业表.课程号 = 'K002' AND 学生作业表.学号 = 学生表.学号
)
相关子查询：
SELECT DISTINCT 学生表.学号,学生表.姓名
FROM 学生表,学生作业表
WHERE 学生表.学号 IN
(
SELECT 学生作业表.学号
FROM 学生作业表
WHERE 学生作业表.课程号='K002'
)
④ 查询没有同时选修K001和M001课程的学号，姓名，课程号和三次成绩。
SELECT DISTINCT 学号,课程号,作业1成绩,作业2成绩,作业3成绩
FROM 学生作业表
WHERE 学号 NOT IN
(
SELECT 学号
FROM 学生作业表
WHERE 课程号='K001' OR 课程号='M001'
)
2. 使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
① 在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05，出生日期为1991-01-01。
insert into 学生表(学号,姓名,性别,专业班级,出生日期) values (0593,'张乐','男','电子05','1991-01-01');
② 将所有课程的学分数变为原来的两倍。
update 课程表 SET 学分数=学分数*2;
③ 删除张乐的信息。
delete from 学生表 where 姓名='张乐';
Comments | NOTHING