首页 学习

# 数据库实验三：数据库查询和数据操纵

# 实验目的

  1. 掌握各种查询的使用方法。
  2. 掌握数据操纵的使用方法。

# 实验内容

根据实验 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.专业班级;

image-20201209114208412

子查询：

SELECT *
FROM 学生表  
WHERE 专业班级 = 
(
			SELECT 专业班级
			FROM 学生表
			WHERE 姓名 = '张志国'
);

image-20201209114325300

# ② 查询比 “计算机应用基础” 学时多的课程信息（使用连接查询和子查询方式）。

连接查询：

SELECT 课程表1.*
FROM 课程表 AS 课程表1,课程表 AS 课程表2  
WHERE 课程表2.课程名 = '计算机应用基础' AND 课程表1.学时数>课程表2.学时数;

image-20201209114410484

子查询：

SELECT *
FROM 课程表
WHERE 学时数 > 
(
			SELECT 学时数
			FROM 课程表
			WHERE 课程名 = '计算机应用基础'
);

image-20201209114506633

# ③ 查询选修课程号为 K002 的学生的学号，姓名（使用连接查询，普通子查询，相关子查询）。

连接查询：

SELECT 学生表.学号,学生表.姓名
FROM 学生作业表,学生表
WHERE 学生作业表.课程号='K002'AND 学生作业表.学号 = 学生表.学号;

image-20201209114539154

普通子查询：

SELECT DISTINCT 学生表.学号,学生表.姓名
FROM 学生作业表,学生表
WHERE 学生表.学号 IN
(
			SELECT 学生表.学号
			FROM 学生作业表      
			WHERE 学生作业表.课程号 = 'K002' AND 学生作业表.学号 = 学生表.学号
)

image-20201209114622680

相关子查询：

SELECT DISTINCT 学生表.学号,学生表.姓名 
FROM 学生表,学生作业表 
WHERE 学生表.学号 IN
(  
			SELECT 学生作业表.学号    
			FROM 学生作业表   
			WHERE 学生作业表.课程号='K002'
)

image-20201209114700833

# ④ 查询没有同时选修 K001 和 M001 课程的学号，姓名，课程号和三次成绩。

SELECT DISTINCT 学号,课程号,作业1成绩,作业2成绩,作业3成绩
FROM 学生作业表 
WHERE 学号 NOT IN
(
			SELECT 学号
			FROM 学生作业表
			WHERE 课程号='K001' OR 课程号='M001'
)

image-20201209114729520

# 2. 使用数据操纵完成以下任务（每一个任务都要给出 SQL 语句，并且列出查询结果）。

# ① 在学生表中添加一条学生记录，其中，学号为 0593，姓名为张乐，性别为男，专业班级为电子 05，出生日期为 1991-01-01。

insert into 学生表(学号,姓名,性别,专业班级,出生日期) values (0593,'张乐','男','电子05','1991-01-01');

image-20201209114818248

# ② 将所有课程的学分数变为原来的两倍。

update 课程表 SET  学分数=学分数*2;

# ③ 删除张乐的信息。

delete  from  学生表 where 姓名='张乐';

image-20201209114921474

更新于 阅读次数 次

请我喝[茶]~(￣▽￣)~*

Lavender 微信支付

微信支付

Lavender 支付宝

支付宝