数据库 实验二 数据库的单表查询和连接查询

实验目的

1. 掌握无条件查询的使用方法。

2. 掌握条件查询的使用方法。

3. 掌握库函数及汇总查询的使用方法。

4. 掌握分组查询的使用方法。

5. 掌握查询的排序方法。

6. 掌握连接查询的使用方法。

题目:

对这个数据库中的表进行以下的查询操作(每一个查询都要给出SQL语句,列出查询结果(可以截图))。

(1)查询各位学生的学号、班级和姓名。

(2)查询课程的全部信息。

(3)查询数据库中有哪些专业班级。

(4)查询学时数大于60的课程信息。

(5)查询在1986年出生的学生的学号、姓名和出生日期。

(6)查询三次作业的成绩都在80分(不包括)以上的学号、课程号。

(7)查询姓张的学生的学号、姓名和专业班级。

(8)查询05级的男生信息。

(9)查询没有作业成绩(只要有一门没有成绩就算)的学号和课程号。

(10)查询学号为0538的学生的作业1总分。

(11)查询选修了K001课程的学生人数。

(12)查询数据库中共有多少个班级。

(13)查询选修三门以上(含三门)课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。

(14)将学生作业表中的信息按照学号升序排列,如果学号相同,按作业1降序排列。

(15)查询于兰兰的选课信息,列出学号、姓名、课程名。

解析:

(1)查询各位学生的学号、班级和姓名。

SELECT Sno,DeptandClass,Sname
FROM Student

(2)查询课程的全部信息。

SELECT *
FROM Course

(3)查询数据库中有哪些专业班级。

SELECT DeptandClass
FROM Student

(4)查询学时数大于60的课程信息。

SELECT *
FROM Course
WHERE STime>60

(5)查询在1986年出生的学生的学号、姓名和出生日期。

SELECT Sno,Sname,Birthday
FROM Student
WHERE Birthday like '%1986%'

(6)查询三次作业的成绩都在80分(不包括)以上的学号、课程号。

SELECT Sno,Cno
FROM Homework
WHERE Score1>80 and Score2>80 and Score3>80

  (无)

(7)查询姓张的学生的学号、姓名和专业班级。

SELECT Sno,Sname,DeptandClass
FROM Student
WHERE Sname like '张%'

(8)查询05级的男生信息。

SELECT *
FROM Student
WHERE DeptandClass like '%05' and Sex='男'

(9)查询没有作业成绩(只要有一门没有成绩就算)的学号和课程号。

错误写法:
WHERE Score1 = NULL OR Score2 = NULL OR Score3 = NULL

空值的谓词的一般形式是:列名IS[NOT] NULL;注意:不能写成:列名=NULL;或列名=NOTNULL。

SELECT *
FROM Homework
WHERE Score1 is NULL OR Score2 is NULL OR Score3 is NULL

(10)查询学号为0538的学生的作业1总分。

SELECT Sum(Score1) as Sum
FROM Homework
WHERE Sno = 538

(11)查询选修了K001课程的学生人数。

SELECT Count(*)as Num
FROM Homework
WHERE Cno = 'K001'

(12)查询数据库中共有多少个班级。

SELECT Count(Distinct DeptandClass) as Num
FROM Student

(13)查询选修三门以上(含三门)课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。

在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。

"聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。"

HAVING 子句可以让我们筛选分组后的各组数据。

SELECT Sno,AVG(Score1)as AS1,AVG(Score2)as AS2,AVG(Score3)as AS3
FROM Homework
GROUP BY Sno
Having COUNT(Cno)>=3

(14)将学生作业表中的信息按照学号升序排列,如果学号相同,按作业1降序排列。

SELECT *
FROM Homework
ORDER BY Sno,Score1 DESC

升序:默认 (或ASC)

降序:DESC

(15)查询于兰兰的选课信息,列出学号、姓名、课程名。

INNER JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

列名 'Sno' 不明确。

把语句里相关的列名都加上前缀,可以避免这个问题

报错的主要原因是“显示状态”属于哪个表或哪个结果集不明确

SELECT Student.Sno,Sname,Cname
FROM Homework
INNER JOIN Student
ON Student.Sno=Homework.Sno
INNER JOIN Course
ON Homework.Cno=Course.Cno
WHERE Sname='于兰兰'

有两个INNER JOIN时,FROM后应是连接其他表格的桥梁

 

附:数据信息表.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

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 点我我会动 设计师:上身试试 返回首页