数据库实验报告2.docx

数据库实验报告2.docx

《数据库实验报告2.docx》由会员分享，可在线阅读，更多相关《数据库实验报告2.docx（15页珍藏版）》请在冰豆网上搜索。

数据库实验报告2

数据库实验报告2

昆明理工大学信息工程与自动化学院学生实验报告

（2011—2012学年第1学期）

课程名称：

数据库系统教程开课实验室：

信自楼4452011年11月27日

年级、专业、班

学号

姓名

成绩

实验项目名称

SQL的数据查询

指导教师

丁家满

教师评语

该同学是否了解实验原理：

A.了解□B.基本了解□C.不了解□

该同学的实验能力：

A.强□B.中等□C.差□

该同学的实验是否达到要求：

A.达到□B.基本达到□C.未达到□

实验报告是否规范：

A.规范□B.基本规范□C.不规范□

实验过程是否详细记录：

A.详细□B.一般□C.没有□

教师签名：

年月日

一、上机目的及内容

1.上机内容:

SQL的数据查询,查询、插入、删除、修改

2.上机目的：

掌握数据查询语句，并能熟练应用

二、实验原理及基本技术路线图（方框原理图或程序流程图）

在SQLserver2008软件中的查询中，输入SQL代码

三、所用仪器、材料（设备名称、型号、规格等或使用软件）

1台PC及SQLserver2008软件

四、实验方法、步骤（或：

程序代码或操作过程）

1）select查询

单表查询：

查询全体学生的学号及姓名：

SELECTSNO,SNAMEFROMS;

查询全体学生的全部信息：

SELECT*FROMS;

2）查询经过计算值

（SELECT子句的<目标列表达式>为表达式，表达式可以是：

算术表达式、字符串常量、函数、列别名等）

查全体学生的学号、姓名及其出生年份：

SELECTSNO,SNAME,2012-AGEFROMS;

查询全体学生的姓名、出生年份和所属系：

SELECTSNO,SNAME,2012-AGE,SDEPTFROMS;

查询选修了课程的学生学号:

SELECTSNOFROMSC,CWHERESC.CNO=C.CNO;

为了避免这种不合题意的情况，我们用distinct用了去除重复的元组。

所以上例中的执行语句为：

SELECTDISTINCTSNOFROMSC;

SELECTDISTINCTSNOFROMSC,CWHERESC.CNO=C.CNO;

查询选修课程的各种成绩：

SELECTCNO,GRADEFROMSC;

SELECTDISTINCTCNO,DISTINCTGRADEFROMSC;

SELECTCNO,DISTINCTGRADEFROMSC;

SELECTDISTINCTCNO,GRADEFROMSC;

SELECTCNO,GRADEFROMSC;

对上述情况的分析及总结：

distinct作用于其后的列，同一个selsct后面只允许跟一个distinct而且只能放在第一个列的位置上。

若此处不同distinct则执行语句按照表中的顺序顺序的执行下来（如执行语句3的结果示）

选择满足条件的元组选择满足条件的元组选择满足条件的元组选择3）满足条件的元组

比较大小比较大小比较大小比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!

=或<>，!

>，!

<，逻辑运算符NOT+比较运算符。

查询所有年龄在20岁以上,30以下的学生姓名及其年龄：

SELECTSNAME,AGEFROMSWHEREAGE>20andAGE<30;

SELECTSNAME,AGEFROMSWHEREAGEBETWEEN20AND30;

4）使用谓词：

IN<值表>,NOTIN<值表>。

<值表>：

用逗号分隔的一组取值

查询信自、材料和机械学院学生的姓名和性别：

SELECTSNAME,SEXFROMSWHERESDEPTIN（SELECTSDEPTFROMSWHERESDEPT='信自'orSDEPT='材料'orSDEPT='机械'）;

SELECTSNAME,SEXFROMSWHERESDEPTIN（'信自','材料','机械'）;

总结：

在使用in查询某一个值在某一个范围的时候，要加在范围上加上括号，括号里面的表示范围。

如果表示某一个值不属于某一个范围，则用notin

5）字符串的匹配操作字符串的匹配操作字符串的匹配操作字符串的匹配操作条件表达式中字符串的匹配操作符是“LIKE”（可以用=运算符取代LIKE谓词，用!

=或<>运算符取代NOTLIKE谓词。

）在表达式中可以使用两个通配符：

百分号（%）：

与零个或者多个字符组成的字符串匹配。

a%b表示以a开头，以b结尾的任意长度的字符串。

如acb，addgb，ab等都满足该匹配串。

下划线（_）：

与单个字符匹配。

a_b表示以a开头，以b结尾的长度为3的任意字符串。

如acb，afb等都满足该匹配串。

查询2009开头学号的学生的详细情况

SELECT*FROMSWHERESNOLIKE'2009%';

查询所有姓张学生的姓名、学号和性别:

SELECTSNO,SNAME,SEXFROMSWHERESNAMELIKE'张%';

SELECTSNO,SNAME,SEXFROMSWHERESNAMELIKE'张_';

查找不姓刘的学生的详细情况

SELECTSNO,SNAMEFROMSWHERESNAMEnotLIKE'张';

6）使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符为了使字符串中包含特殊字符（既%和_），SQl允许定义转义字符。

转义字符紧靠特殊字符（%和_）并放在它前面，表示该特殊字符将被当成普通字符。

在like比较中使用ESCAPE关键字定义转义符。

一般使用（\）作为转义字符，也可以用其他字符，只要和ESCAPE后面的匹配就可以，如使用（[）也可以，但为了不出现错误，尽量不要用%和_作为转义字符。

查询语文课程的课程号和课程名：

SELECTCNO,CNAMEFROMCWHERECNAMELIKE'语_'ESCAPE'\';

7）涉及空值的查询涉及空值的查询涉及空值的查询涉及空值的查询SQL中允许列值为空，空值用保留字NULL表示。

可以使用谓词ISNULL或ISNOTNULL，这里ISNULL是测试列值是否为空，如果需要测试非空值则用ISNOTNULL，请注意，“ISNULL”不能用“=NULL”代替。

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。

查询缺少成绩的学生的学号和相应的课程号

SELECTCNO,SNOFROMSCWHEREGRADEISNULL;

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。

查询有成绩的学生的学号和相应的课程号

SELECTCNO,SNOFROMSCWHEREGRADEISnotNULL;

8）使用聚合函数使用聚合函数使用聚合函数使用聚合函数SQl提供下列的聚合函数：

计数：

COUNT（[DISTINCT|ALL]*）和COUNT（[DISTINCT|ALL]<列名>）计算总和：

SUM（[DISTINCT|ALL]<列名>）计算平均值：

AVG（[DISTINCT|ALL]<列名>）求最大值：

MAX（[DISTINCT|ALL]<列名>）求最小值：

MIN（[DISTINCT|ALL]<列名>）这里，DISTINCT短语：

在计算时要取消指定列中的重复值；ALL短语：

不取消重复值；ALL为缺省值

查询学生总人数

SELECTCOUNT（*）AS学生总人数FROMS;

查询选修了课程的学生人数:

SELECTCOUNT（DISTINCTSNO）AS选修了课人数FROMS;

9）SELECT语句完整的句法语句完整的句法语句完整的句法语句完整的句法

语句格式：

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<行条件表达式>][GROUPBY<列名>[HAVING<组条件表达式>]][ORDERBY<列名>[ASC|DESC]，……]GROUPBY子句：

对查询结果按指定列的值分组，该属性列值相等的元组为一个组。

通常会在每组中使用集函数；HAVING短语：

筛选出满足指定条件的组；ORDERBY子句：

对查询结果表按指定列值的升序或降序排序。

使用ORDERBY子句，可以按一个或多个属性列排序。

升序：

ASC；降序：

DESC；缺省值为升序，当排序列含空值时，各系统把列为空值的元组集中在最前面或最后面显示。

（1）.对查询结果进行排序对查询结果进行排序对查询结果进行排序对查询结果进行排序：

：

：

：

查询选修了C001号课程的学生的学号及其成绩，查询结果按分数降序排列：

SELECTSNO,GRADEFROMSCWHERECNO='C001'

ORDERBYGRADEDESC;

求各个课程号及相应的选课人数:

selectCNO,COUNT（distinctSNO）as选课人数fromSCgroupbycno;

查询有2门以上（含2门）课程是90分以上（含90分）的学生的学号及（70分以上的）课程:

selectSNO,COUNT（SNO）AS课程数fromSCwhereGRADEIN（SELECTGRADEFROMSCWHEREGRADE>=70）groupbySNOhavingCOUNT（SNO）>=2;

连接查询连接查询连接查询连接查询同时涉及多个表的查询称为连接查询，两张以上的表做笛卡尔积，再做自然连接，把主键连接在一起，主键无需同名

查询每个学生及其选修课程的情况:

SELECTS.*,SC.*FROMS,SCWHERESC.SNO=S.SNO;

嵌套查询嵌套查询嵌套查询嵌套查询将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

查询与“秦始皇”在同一个系学习的学生：

select*fromSwheresdeptin（selectsdeptfromSwheresname='秦始皇'）;

谓词语义SOME：

某一个值，ALL：

所有值。

注：

“θSOME”操作表示左边那个元组与右边集合中至少一个元素满足θ运算。

“θALL”操作表示左边那个元组与右边集合中每一个元素满足θ运算。

例1，查询其他系中比信息系其中某一个学生年龄小的学生姓名和年龄

selectSNAME,AGE,SDEPTfromSwhereAGE'计科';

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值。

（重要）由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

查询所有选修了1号课程的学生姓名:

SELECTSNAMEFROMSWHEREEXISTS（SELECT*FROMSCWHERESno=S.SnoANDcno='C001'）;

形式<查询块><查询块>UNIONUNIONALL<查询块><查询块>参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同，带ALL的操作，返回结果未消除重复元组，不带ALL的操作将在返回结果中消除重复元组。

查询计算机科学系的学生及年龄不大于19岁的学生:

select*fromSwhereage<=19unionselect*fromSwheresdept='机械';

形式：

<查询块><查询块>INTERSECTINTERSECTALL<查询块><查询块>参加INTERSECT操作的各结果表的列数必须相同；对应项的数据类型也必须相同，带ALL的操作，返回结果未消除重复元组，不带ALL的操作将在返回结果中消除重复元组。

查询冶金的学生与年龄不大于30岁的学生的交集：

select*fromSwhereage<=30intersectselect*fromSwheresdept='冶金';

查询材料的学生与年龄不大于19岁的学生的差集：

select*fromSwheresdept='材料'exceptselect*fromSwhereage<=19;

五、实验总结

1.select后面的表达式可以是算术表达式，字符串表达式等。

在进行列名的重命名时，在select后的列名后面加上as后添加或修改所需的列的列名，as也可以用空格来代替。

在进行小写输出时，用lower.

2.distinct作用于其后的列，同一个selsct后面只允许跟一个distinct而且只能放在第一个列的位置上。

3.在使用in查询某一个值在某一个范围的时候，要加在范围上加上括号，括号里面的表示范围。

如果表示某一个值不属于某一个范围，则用notin。

4.（转义字符用法格式）用单引号将所有查询的字符串引起来，并在特殊字符前加上转义字符，在后面加上ESCAPE'\'例如：

like'asdf\_afafas'ESCAPE'\'。

5.使用between…and…时，要包含两个端点值才可以用。

6.在进行SUM，AVG后面的列名要必须是数值型的。

7.在使用升序降序时候的格式为：

orderby列名DESC/ASC。

8.嵌套查询实际上是分步来做的，第一步先执行括号里面的内容，然后再把第一步的查询结果做为已知带入到上面的查询中去。