数据库实验报告2.doc

昆明理工大学信息工程与自动化学院学生实验报告（ 2011 2012 学年 第 1 学期 ）课程名称：数据库系统教程 开课实验室：信自楼445 2011 年11月 27日年级、专业、班学号姓名成绩实验项目名称SQL的数据查询指导教师丁家满教师评语该同学是否了解实验原理：A.了解B.基本了解C.不了解该同学的实验能力：A.强 B.中等 C.差 该同学的实验是否达到要求：A.达到B.基本达到C.未达到实验报告是否规范：A.规范B.基本规范C.不规范实验过程是否详细记录：A.详细B.一般 C.没有 教师签名： 年 月 日一、上机目的及内容1.上机内容: SQL的数据查询,查询、插入、删除、修改2.上机目的： 掌握数据查询语句，并能熟练应用二、实验原理及基本技术路线图（方框原理图或程序流程图） 在SQL server 2008软件中的查询中，输入SQL代码三、所用仪器、材料（设备名称、型号、规格等或使用软件）1台PC及SQL server 2008软件四、实验方法、步骤（或：程序代码或操作过程）1）select查询单表查询：查询全体学生的学号及姓名：SELECT SNO,SNAME FROM S;查询全体学生的全部信息：SELECT * FROM S;2)查询经过计算值（SELECT子句的为表达式，表达式可以是：算术表达式、字符串常量、函数、列别名等 ）查全体学生的学号、姓名及其出生年份：SELECT SNO,SNAME,2012-AGE FROM S;查询全体学生的姓名、出生年份和所属系：SELECT SNO,SNAME,2012-AGE,SDEPT FROM S;查询选修了课程的学生学号:SELECT SNO FROM SC,C WHERE SC.CNO=C.CNO;为了避免这种不合题意的情况，我们用distinct用了去除重复的元组。所以上例中的执行语句为：SELECT DISTINCT SNO FROM SC;SELECT DISTINCT SNO FROM SC,C WHERE SC.CNO=C.CNO;查询选修课程的各种成绩：SELECT CNO,GRADE FROM SC;SELECT DISTINCT CNO,DISTINCT GRADE FROM SC;SELECT CNO,DISTINCT GRADE FROM SC;SELECT DISTINCT CNO,GRADE FROM SC;SELECT CNO,GRADE FROM SC;对上述情况的分析及总结：distinct作用于其后的列，同一个selsct后面只允许跟一个distinct而且只能放在第一个列的位置上。若此处不同distinct则执行语句按照表中的顺序顺序的执行下来(如执行语句3的结果示)选择满足条件的元组选择满足条件的元组选择满足条件的元组选择3）满足条件的元组比较大小比较大小比较大小比较大小 在WHERE子句的中使用比较运算符=，=，=，!= 或 ，!，!20 and AGE30;SELECT SNAME,AGE FROM S WHERE AGE BETWEEN 20 AND 30;4）使用谓词：IN ,NOT IN 。 ：用逗号分隔的一组取值查询信自、材料和机械学院学生的姓名和性别：SELECT SNAME,SEX FROM S WHERE SDEPT IN(SELECT SDEPT FROM S WHERE SDEPT=信自 or SDEPT=材料 or SDEPT=机械);SELECT SNAME,SEX FROM S WHERE SDEPT IN(信自 ,材料 ,机械);总结：在使用in查询某一个值在某一个范围的时候，要加在范围上加上括号，括号里面的表示范围。如果表示某一个值不属于某一个范围，则用not in5）字符串的匹配操作字符串的匹配操作字符串的匹配操作字符串的匹配操作 条件表达式中字符串的匹配操作符是“LIKE”（可以用 = 运算符取代 LIKE 谓词，用 != 或 运算符取代 NOT LIKE 谓词。）在表达式中可以使用两个通配符： 百分号（%）：与零个或者多个字符组成的字符串匹配。a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串。下划线（ _）：与单个字符匹配。a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。查询2009开头学号的学生的详细情况SELECT * FROM S WHERE SNO LIKE 2009%;查询所有姓张学生的姓名、学号和性别:SELECT SNO,SNAME,SEX FROM S WHERE SNAME LIKE 张%;SELECT SNO,SNAME,SEX FROM S WHERE SNAME LIKE 张_;查找不姓刘的学生的详细情况SELECT SNO,SNAME FROM S WHERE SNAME not LIKE 张;6)使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符 为了使字符串中包含特殊字符（既%和_），SQl允许定义转义字符。转义字符紧靠特殊字符（%和_）并放在它前面，表示该特殊字符将被当成普通字符。在like比较中使用ESCAPE关键字定义转义符。一般使用（）作为转义字符，也可以用其他字符，只要和ESCAPE后面的匹配就可以，如使用（）也可以，但为了不出现错误，尽量不要用%和_作为转义字符。查询语文课程的课程号和课程名：SELECT CNO,CNAME FROM C WHERE CNAME LIKE 语_ ESCAPE ;7) 涉及空值的查询涉及空值的查询涉及空值的查询涉及空值的查询 SQL中允许列值为空，空值用保留字NULL表示。可以使用谓词 IS NULL 或 IS NOT NULL，这里IS NULL 是测试列值是否为空，如果需要测试非空值则用IS NOT NULL，请注意， “IS NULL” 不能用 “= NULL” 代替。 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号SELECT CNO,SNO FROM SC WHERE GRADE IS NULL ;某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询有成绩的学生的学号和相应的课程号SELECT CNO,SNO FROM SC WHERE GRADE IS not NULL ;8）使用聚合函数使用聚合函数使用聚合函数使用聚合函数 SQl提供下列的聚合函数： 计数：COUNT（DISTINCT|ALL *）和COUNT（DISTINCT|ALL ） 计算总和：SUM（DISTINCT|ALL ） 计算平均值：AVG（DISTINCT|ALL ） 求最大值：MAX（DISTINCT|ALL ） 求最小值：MIN（DISTINCT|ALL ） 这里，DISTINCT短语：在计算时要取消指定列中的重复值；ALL短语：不取消重复值；ALL为缺省值查询学生总人数SELECT COUNT(*) AS 学生总人数 FROM S;查询选修了课程的学生人数:SELECT COUNT(DISTINCT SNO) AS 选修了课人数 FROM S;9）SELECT语句完整的句法语句完整的句法语句完整的句法语句完整的句法 语句格式： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ， GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用集函数；HAVING短语：筛选出满足指定条件的组；ORDER BY子句：对查询结果表按指定列值的升序或降序排序。 使用ORDER BY子句，可以按一个或多个属性列排序。升序：ASC；降序：DESC；缺省值为升序，当排序列含空值时，各系统把列为空值的元组集中在最前面或最后面显示。 (1).对查询结果进行排序对查询结果进行排序对查询结果进行排序对查询结果进行排序：查询选修了C001号课程的学生的学号及其成绩，查询结果按分数降序排列：SELECT SNO,GRADE FROM SC WHERE CNO=C001ORDER BY GRADE DESC;求各个课程号及相应的选课人数:select CNO,COUNT(distinct SNO) as 选课人数 from SC group by cno;查询有2门以上（含2门）课程是90分以上（含90分）的学生的学号及（70分以上的）课程:select SNO,COUNT(SNO) AS 课程数 from SC where GRADE IN(SELECT GRADE FROM SC WHERE GRADE=70) group by SNO having COUNT(SNO)=2;连接查询连接查询连接查询连接查询 同时涉及多个表的查询称为连接查询，两张以上的表做笛卡尔积，再做自然连接，把主键连接在一起，主键无需同名查询每个学生及其选修课程的情况:SELECT S.*,SC.* FROM S,SC WHERE SC.SNO=S.SNO;嵌套查询嵌套查询嵌套查询嵌套查询 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询查询与“秦始皇”在同一个系学习的学生：select * from S where sdept in (select sdept from S where sname=秦始皇);谓词语义SOME：某一个值，ALL：所有值。 注：“SOME”操作表示左边那个元组与右边集合中至少一个元素满足运算。“ALL”操作表示左边那个元组与右边集合中每一个元素满足运算。 例1，查询其他系中比信息系其中某一个学生年龄小的学生姓名和年龄select SNAME,AGE,SDEPT from S where AGEsome (select AGE from S where SDEPT=机械) and SDEPT计科;带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值。 (重要)由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。查询所有选修了1号课程的学生姓名:SELECT SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND cno=C001);形式 UNION UNION ALL 参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同，带ALL的操作，返回结果未消除重复元组，不带ALL的操作将在返回结果中消除重复元组。查询计算机科学系的学生及年龄不大于19岁的学生:select * from S where age=19 union select * from S where sdept=机械;形式： INTERSECT INTERSECT ALL 参加INTERSECT操作的各结果表的列数必须相同；对应项的数据类型也必须相同，带ALL的操作，返回结果未消除重复元组，不带ALL的操作将在返回结果中消除重复元组。 查询冶金的学生与年龄不大于30岁的学生的交集：select * from S where age=30 intersect select * from S where sdept=冶金;查询材料的学生与年龄不大于19岁的学生的差集：select * from S where sdept=材料 except select * from S where age=19;五、实验总结1.select后面的表达式可以是算术表达式，字符串表达式等。在进行列名的重命名时，在select后的列名后面加上as后添加或修改所需的列的列名，as也可以用空格来代替。在进行小写输出时，用lower. 2.distinct作用于其后的列，同一个selsct后面只允许跟一个distinct而且只能放在第一个列的位置上。 3.在使用in查询某一个值在某一个范围的时候，要加在范围上加上括号，括号里面