数据查询实验二 (2)

实验二SQL语言——数据查询操作SQL (Structured Query Language) 是一种用于管理关系数据库系统的标准语言。

在数据查询操作方面，SQL 可以帮助用户从数据库中检索想要的数据，通过下面的实验，我们将深入了解 SQL 语言在数据查询操作中的应用。

一、SELECT语句SELECT语句是SQL中最常用的语句之一，它用于从数据库中选择需要查询的数据。

SELECT语句的基本语法如下：```SELECT列名1,列名2,...FROM表名;```例如，我们有一个名为 "Students" 的表，其中包含了学生的姓名、年龄和性别等信息。

要查询学生的姓名和年龄，我们可以使用以下SELECT 语句：```SELECT 姓名, 年龄 FROM Students;```二、WHERE子句WHERE子句用于在SELECT语句中添加过滤条件，只返回满足条件的数据。

下面是WHERE子句的基本语法：```SELECT列名1,列名2,...FROM表名WHERE条件;```例如，我们要查询年龄大于等于18岁的学生信息，可以使用以下SELECT语句：```SELECT * FROM Students WHERE 年龄 >= 18;```三、ORDERBY子句ORDERBY子句用于按照指定的列进行排序，可以按照升序或降序排列。

下面是ORDERBY子句的基本语法：```SELECT列名1,列名2,...FROM表名ORDERBY列名[ASC，DESC];```例如，我们要按照学生的年龄从小到大排序，可以使用以下SELECT语句：```SELECT * FROM Students ORDER BY 年龄 ASC;```四、GROUPBY子句GROUPBY子句用于将查询结果按照一个或多个列进行分组，并对每个组进行聚合操作。

下面是GROUPBY子句的基本语法：```SELECT列名1,列名2,...FROM表名GROUPBY列名1,列名2,...;```例如，我们要统计每个性别的学生人数```SELECT 性别, COUNT(*) FROM Students GROUP BY 性别;```五、HAVING子句HAVING子句用于在GROUPBY子句中添加过滤条件，只返回满足条件的分组。

《数据库原理及应用》实验报告实验名称数据库查询实验室502 实验日期求选修了课程的学生学号。

Use 学生课程GoSelect 学号from 学生where 学号in (select 学号from where 课程号 is not null)求选修1102课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

Use 学生课程GoSelect 学号,成绩 from 课程,选课 where 课程.课程号and 课程号=1102 order by 成绩 desc,学号 asc求选修课程1102且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。

Use 学生课程GoSelect 学号,成绩*0.8 as 成绩 from选课 where 成绩 between 80 and求数学系或计算机系姓张的学生的信息。

Use 学生课程GoSelect * from 学生,课程,选课 where 学生.学号=选课.学号 and 课程号=课程.课程号and 姓名like ’张%’and (所在系=’数学’在系=’计算机’)求缺少了成绩的学生的学号和课程号。

Use 学生课程GoSelect 学号,课程号 from 选课 where 成绩 is null连接查询实验语句在“学生选课”数据库中实现下列数据连接查询操作。

查询每个学生的情况以及他(她)所选修的课程。

Use 学生课程GoSelect * from 学生,选课,课程 where 学生.学号=选课.学号课程号=课程.课程号求学生的学号、姓名、选修的课程名及成绩。

Use 学生课程GoSelect 学号,姓名,课程名,成绩 from 学生,选课,课程 where学生求选修1105课程且成绩为90分以上的学生学号、姓名及成绩。

Use 学生课程GoSelect 学号,姓名,成绩 from 学生,选课 where 学生.学号=选课and 课程号=1105 and 成绩>90查询每门课的选课人数。

实验二数据库的查询实验实验目的和要求(1)掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。

(2)熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

(3)熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

实验内容和原理在实验一定义的“学生成绩数据库”中，使用T-SQL 语句完成以下查询：(1 )求计算机系学生的学号和姓名。

(2)求选修了数学的学生学号、姓名和成绩。

(3)求选修01 课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

(4)查找选修课程的平均成绩位于前三名的学生的学号。

(5)查询计算机系的姓刘且单名的学生的信息。

(6)查询至少选修两门课程的学生学号。

(7)查询学生的学号、课程号以及对应成绩与所有学生所有课程的最高成绩的百分比。

(8)查询选修“数据库”课程，且成绩在80 分以上的学生的学号和成绩。

(9)查询所有姓“王”的同学没有选修的课程名。

(请分别用exists和in完成该查询) (10)查询选修了全部课程的学生的姓名。

(请至少写出两种查询语句)(11)求选修了学生“ 95001”所选修的全部课程的学生学号和姓名。

(12)查询每一门课的间接先修课。

(13)列出所有学生所有可能的选课情况。

(14)列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。

(15)输出与“张三”同性别并位于同一个系的所有同学的姓名。

(请至少写出两种查询语句)(16)查询至少被两名男生选修的课程名。

(17)对被两名以上学生所选修的课程统计每门课的选课人数。

要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

(18)列出选修课程超过 3 门的学生姓名及选修门数。

(19)检索至少选修课程号为01 和03 的学生姓名。

(20)检索至少选修课程“数学”和“操作系统”的学生学号。

(21 )查询‘操作系统'课程的最高分的学生的姓名、性别、所在系(22)查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩(23)所有成绩都在70 分以上的学生姓名及所在系。

实验4 数据查询一、实验目的1.掌握SELECT语句的基本语法和查询条件的表示方法；2.掌握连接查询的表示方法；3.掌握嵌套查询的表示方法；4.掌握数据汇总的方法；5.掌握ORDER BY子句的作用和使用方法；6.掌握HAVING子句的作用和使用方法；二、实验内容使用SELECT查询语句，在数据库StudentCourse的Student表、Course 表和StuCourse 表上进行各种查询，包括单表查询、连接查询、嵌套查询，并进行数据汇总，以及使用GROUP BY子句、ORDER BY子句和HAVING子句对结果进行分组、排序和筛选处理。

1.SELECT语句的基本使用；2.连接查询；3.嵌套查询；4.数据汇总；5.使用GROUP BY 子句对结果分组；6.使用ORDER BY子句对结果排序；7.使用HAVING子句对分组结果进行筛选；思考与练习：1.用SELECT语句查询Course表和StuCourse表中的所有记录。

SQL语句：select * from Course;select * from StuCourse;实验结果：2.用SELECT语句查询Course表和StuCourse表中满足指定条件的一列或若干列。

SQL语句：select 课程号,课程名 from Course where 学分=5;select 学号,课程号 from StuCourse where 成绩>80;实验结果：3.查询所有姓名中包含有“红”的学生的学号及姓名。

SQL语句：select 学号,姓名 from Student where 姓名 like '%红%';实验结果：4.用连接查询的方法查找所有选修了“2001” 或“1002”号课程的学生学号和姓名。

SQL语句：selectdistinct Student.学号,姓名from StuCourse,Studentwhere(课程号='1002'or 课程号='2001')and StuCourse.学号=Student.学号;实验结果：5.用子查询的方法查找所有选修了“2001”或“1002”号课程的学生学号和姓名。

课程名称数据库原理实验序号 2实验项目数据查询实验地点实验学时实验类型验证性指导教师实验员专业班级学号姓名年月日成绩：A教师该学生本次实验的内容丰富，完成的操作步骤详细具体，实验结果正确，在实验报告的填写中态度十分严谨，对数据分析有自己的见解。

四、实验过程（实验步骤、记录、数据、分析）实验准备：在实验一的基础上（包括数据库的建立、定义表和添加表内容）进行实验，下面分别为depts表、students表、courses表、reports表。

一、简单的选择与投影查询1、无条件查询1.1查询全体学生的详细记录。

这是一个无条件的选择查询，其命令为：其命令为：select * from students运行结果如右图，显示整张表的内容。

1.2查询全体学生的姓名(Sname)、学号(Sno)、所在系(dno)。

这是一个无条件的投影查询，其命令为：select sname,ssexfrom students运行结果如右图，显示了表中的三列。

1.3查询全体学生的姓名(Sname)、出生年份及学号(Sno)。

其命令为：select sno,sname,2017-sage as birth from students运行结果如右图，显示了三列内容。

1.4查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。

其命令为：select sname,'birth:' title,YEAR(GETDATE())-sagebirthyear,LOWER(sno) lsnofrom students运行结果如右图，显示了四列内容。

1.5查询选修了课程的学生学号。

其命令为：select distinct snofrom reports运行结果如右图，distinct短语是为了消去查询结果中的重复值。

2、条件查询（1）比较条件1.1查询d03系全体学生的学号(Sno)和姓名(Sname)。

其命令为：select sno,snamefrom studentswhere dno='d03'运行结果如右图，显示了d03系的全体学生姓名与学号。

实验二. 数据库查询Student表：列名含义数据类型约束Sno 学号字符串，长度为7 主码Sname 姓名字符串，长度为10 非空Ssex 性别字符串，长度为2Sage 年龄整形Sdept 所在系字符串，长度为20Course表：列名含义数据类型约束Cno 课程号字符串，长度为10 主码Cname 课程名字符串，长度为20 非空Ccredit 学分字节Semester 学期字节SC表：列名含义数据类型约束Sno 学号字符串，长度为7 主码引用Student的Sno作为外码Cno 课程号字符串，长度为10 主码引用Course的Cno作为外码Grade 成绩小整形XKLB 修课类别字符串，长度为4 请写明创建以上三张基本表的语句。

3．将如下数据插入建好的三张表中。

Student表学号姓名性别年龄所在系'9512101','李勇','男',19,'计算机系''9512102','刘晨','男',20,'计算机系''9512103','王敏','女',20,'计算机系''9521101','张立','男',22,'信息系''9521102','吴宾','女',21,'信息系''9521103','张海','男',20,'信息系''9531101','钱小平','女',18,'数学系''9531102','张大力','男',19,'数学系'Course表课程号课程名学分学期'c01','计算机文化学',3,1'c02','VB',2,3'c03','计算机网络',4,7'c04','数据库基础',6,6'c05','高等数学',8,2'c06','数据结构',5,4SC表学号课程号成绩修课类别'9512101','c01',90,'必修''9512101','c02',86,'选修''9512101','c06',null,'必修''9512102','c02',78,'选修''9512102','c04',66,'必修''9521102','c01',82,'选修''9521102','c02',75,'选修''9521102','c04',92,'必修''9521102','c05',50,'必修''9521103','c02',68,'选修''9521103','c06',null,'必修''9531101','c01',80,'选修''9531101','c05',95,'必修''9531102','c05',85,'必修'请写明使用的数据插入语句。

数据库基础与实践实验报告实验二数据查询班级：惠普测试142学号：**********姓名：***日期：2016-11-061 实验目的：1)掌握针对单张基本表的数据查询方法；2)掌握SQL常用库函数及统计汇总查询方法；3)掌握分组查询方法和结果排序的方法；4)掌握连接查询和嵌套查询的方法。

2 实验平台：操作系统：Windows xp。

实验环境：SQL Server 2000以上版本。

3 实验内容与步骤利用实验一创建的sch_id数据库完成下列查询，并对查询语句的功能进行测试。

1.查询学号s5的同学的姓名和所在系，将查询结果的列名显示为中文。

代码：SELECT sn AS姓名,dn AS所在系FROM S,D WHERE S.sno='S5'AND S.dno=D.dno测试记录：S：D：运行结果截图：2.查询教过课程号为C2或C5的课程的教师的编号（写两个查询语句完成同样的功能）代码：：SELECT tno FROM TC WHERE cno='C2'OR cno='C5'SELECT tno FROM TC WHERE cno IN('C2','C5')测试记录：运行结果截图：3.查询学号为S5的同学所学课程的平均成绩。

代码：SELECT A VG(score)AS'平均分'FROM SC WHERE sno='S5'测试记录：运行结果截图：4.查询选过课程号为C1的课程的人数。

代码：SELECT COUNT(*)FROM SC WHERE cno='C1'测试记录：运行结果截图：5.查询计算机系学生的最大年龄，显示系名和最大年龄（查询结果的每列都要有列名且为中文）。

代码：SELECT dn AS系名,MAX(age)AS'最大年龄'FROM S,D WHERE S.dno=D.dno AND D.dn='计算机'GROUP BY dn测试记录：运行结果截图：6.查询所有教过课的老师的人数（教过多门课的同一位老师只计数1次）。

实验(二): 熟练掌握SQL语言1．求选修了课程的学生的学号，要求：(1) 不使用distinct语句，消除重复元组，写出其SQL语句select SC.Sno from SC;（2）使用distinct语句，消除重复元组，写出其SQL语句selectdistinct(SC.Sno)from SC;（3）如果该查询写成：select Student.Sno from Student, SC where Student.Sno=SC.Sno 请问该查询结果和上面哪个结果是相同的？select Student.Sno from Student,SCwhere Student.Sno=SC.Sno;查询结果和第一个结果相同。

2.求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。

select Cno '课程号',Cname '课程名',Ctime '学时',Ccredit '学分'from Course;3.求计算机系和数学系的学生学号、姓名和年龄。

select Sno,Sname,Sagefrom Studentwhere Sdept='计算机系'or Sdept='数学系';4.求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名。

select*from Studentwhere Sdept!='计算机系'and Sdept!='数学系';5.求全体学生的信息，要求按年龄升序排列。

select*from Studentorderby Sage;6.求计算机系年龄在18~20岁之间的学生姓名和年龄。

select Sname,Sagefrom Studentwhere Sdept='计算机系'and Sage>=18 and Sage<=20;7.求姓名是以”李”开头的计算机系学生。

实验训练2：数据查询操作实验目的：基于实验1创建的汽车用品网上商城数据库Shopping，理解MySQL运算符、函数、谓词，练习Select语句的操作方法。

实验内容：1.单表查询【实验2.1】字段查询（1）查询商品名称为“挡风玻璃”的商品信息。

分析：商品信息存在于商品表，而且商品表中包含商品名称此被查询信息，因此这是只需要涉及一个表就可以完成简单单表查询。

（2）查询ID为1的订单。

分析：所有的订单信息存在于订单表中，而且订单用户ID也存在于此表中，因此这是只需要查询订单表就可以完成的查询。

【实验2.2】多条件查询查询所有促销的价格小于1000的商品信息。

分析：此查询过程包含两个条件，第一个是是否促销，第二个是价格，在商品表中均有此信息，因此这是一个多重条件的查询。

【实验2.3】DISTINCT（1）查询所有对商品ID为1的商品发表过评论的用户ID。

分析：条件和查询对象存在于评论表中，对此商品发表过评论的用户不止一个，而且一个用户可以对此商品发表多个评论，因此，结果需要进行去重，这里使用DISTINCT实现。

（2）查询此汽车用品网上商城会员的创建时间段，1年为一段。

分析：通过用户表可以完成查询，每年可能包含多个会员，如果把此表中的创建年份都列出来会有重复，因此使用DISTINCT去重。

【实验2.4】ORDER BY（1）查询类别ID为1的所有商品，结果按照商品ID降序排列。

分析：从商品表中可以查询出所有类别ID为1的商品信息，结果按照商品ID的降序排列，因此使用ORDER BY语句，降序使用DESC关键字。

（2）查询今年新增的所有会员，结果按照用户名字排序。

分析：在用户表中可以完成查询，创建日期条件设置为今年，此处使用语句ORDER BY。

【实验2.5】GROUP BY（1）查询每个用户的消费总金额（所有订单）。

分析：订单表中包含每个订单的订单总价和用户ID。

现在需要将每个用户的所有订单提取出来分为一类，通过SUM()函数取得总金额。

实验二一、实验目的了解SQL Server 查询分析器中执行T-SQL语句的方法；使用T-SQL语句创建数据表；掌握数据库查询语句SELECT语句的基本语法及使用方法；二、实验内容1．利用企业管理器中提供的“生成SQL脚本”命令生成实验一中创建的数据库中数据表对象的创建的脚本文件，作为实验结果文件保存在实验文件夹下。

2．在查询分析器中完成数据库的创建。

(可另外取名创建数据库文件)3．使用SELECT语句建立以下查询1）显示bookinfo表中所有字段的值；2）查询teacher表中所有记录的teachercode、teachername、enterdate、title字段信息；3）查询男教师的姓名（teachername），显示时将字段名改为“姓名”；4）查询2007年以后出版的教材名称（bookname）、出版日期（publishdate）和库存量（stocknum），显示时字段名称分别为教材名称、出版日期、库存量；显示结果如图1所示。

图15）查询所有教材的出版社代码（publishcode），不包括重复的记录；显示结果如图2所示。

图2 图36）查询学生订书量（stubooknum）最大的3个记录，显示教材代号（bookcode）和学生订书量（stubooknum）；显示结果如图3所示。

7）查询学生订书量（stubooknum）在150~250之间的教材信息；显示结果如图4所示。

图48）查询每一种教材的学生订书总量；显示结果如图5所示。

图5 图69）查询书名为“VB程序设计”的教材的学生总订量、教师总订量及总订量；显示结果如图6所示。

10）显示教材的最高价、最低价、平均价、最高价与最低价之间的差值；显示结果如图7所示。

图711）查询出版社代号（publishcode）为10、02、31的出版社所出版的教材信息。

要求显示教材名称（bookname）、作者（author）、价格（price）、库存和出版社名称（publisher）；显示结果如图8所示。