本文最后更新于2020年12月9日,已超过 2 个月没更新!

数据库实验三:数据库查询和数据操纵

实验目的

  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


疏影横斜水清浅,暗香浮动月黄昏