数据库原理实验报告(4)实验四 查询设计实验

南京晓庄学院《数据库原理与应用》课程实验报告实验四查询设计实验所在院(系):数学与信息技术学院班级：学号：姓名：1.实验目的了解查询的目的，掌握SELECT语句的基本语法和查询条件的表示方法。掌握数据排序和数据联接查询的方法。掌握SQLServer查询分析器的使用方法。2.实验要求针对“TM”数据库，在SQLServer查询分析器中，用T-SQL语句实现以下单表查询操作，并将将调试成功的T-SQL命令，填入实验报告中。查询所有课程的详细情况。查询来自江苏或山东的学生学号和姓名，并以中文名称显示输出的列名。查询选修了课程的学生学号（一人选了多门课程的话，学号只显示一次）。查询选修课程号为07253001的学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。查询所有学生的学号、姓名和年龄。查询选修课程号为07253001的成绩在85-95之间的学生学号和成绩，并将成绩乘以0.7输出。查询数学与信息技术学院（DEPT_ID为07）或物理与电子工程学院(DEPT_ID为09)姓张的学生的信息。。查询所有核心课程（课程名中带*的）的情况。查询缺少了成绩的学生的学号和课程号，查询结果按课程号升序排列。在SQLServer查询分析器中，用T-SQL语句实现下列数据联接查询操作：查询每个学生的情况以及他（她）所选修的课程。查询学生的学号、姓名、选修的课程名及成绩。查询选修C语言程序设计且成绩为85分以上的学生学号、姓名及成绩。查询和学生柏文楠是同一个民族的学生（用自身联接实现）。分别用等值联接和内联接查询有授课记录的老师的姓名。用外联接查询所有老师的授课情况，输出老师的工号、姓名、职称、院系、担任的课程号和授课的学期，结果按院系和职称升序排列。如果该老师没有授课历史，在课程号和授课的学期中显示空值在SQLServerManagementStudio中新建查询，完成以上查询命令的同时，熟悉SQL编辑器工具栏中各快捷按钮的作用。按要求完成实验报告。3.实验步骤、结果和总结实验步骤/结果将调试成功的T-SQL语句写在下方（标明题号）。1.1查询所有课程的详细情况。SELECT*FROMCourseGO1.2查询来自江苏或山东的学生学号和姓名，并以中文名称显示输出的列名。SELECTsidAS'学号',SnameAS'姓名'FROMStudentWHEREsNativeLIKE'江苏%'ORsNativeLIKE'山东%'GO1.3查询选修了课程的学生学号（一人选了多门课程的话，学号只显示一次）。SELECTDISTINCT(lSId)FROMLearnInfoGO1.4查询选修课程号为07253001的学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT*FROMLearnInfoWHERElCid='07253001'ORDERBYlGradeDESC,lSIdASCGO1.5查询所有学生的学号、姓名和年龄。SELECTsId,sName,YEAR(GETDATE())-YEAR(sBrithday)ASageFROMStudent1.6查询选修课程号为07253001的成绩在85-95之间的学生学号和成绩，并将成绩乘以0.7输出。SELECTlSId,lGrade*0.7ASGradeFROMLearnInfoWHERElCid='07253001'ANDlGradeBETWEEN85AND95GO1.7查询数学与信息技术学院（DEPT_ID为07）或物理与电子工程学院(DEPT_ID为09)姓张的学生的信息。SELECT*FROMStudentWHEREsDepId='07'ORsDepId='09'ANDsNameLIKE'张%'1.8查询所有核心课程（课程名中带*的）的情况。SELECT*FROMCourseWHEREcNameLIKE('%*%')1.9查询缺少了成绩的学生的学号和课程号，查询结果按课程号升序排列。SELECTlSId,lCidFROMLearnInfoWHERElGradeISNULLORDERBYlCidASC在SQLServer查询分析器中，用T-SQL语句实现下列数据联接查询操作：2.1查询每个学生的情况以及他（她）所选修的课程。SELECT*FROMStudent,LearnInfoWHERESID=lSIdGO2.2查询学生的学号、姓名、选修的课程名及成绩。SELECTSID,Sname,cName,lGradeFROMStudent,LearnInfo,CourseWHERESID=lSIdANDlCid=cIdGO2.3查询选修C语言程序设计且成绩为85分以上的学生学号、姓名及成绩。SELECTSID,Sname,cName,lGradeFROMStudent,LearnInfo,CourseWHERESID=lSIdANDlCid=cIdANDcName='C语言程序设计'ANDlGrade>85GO2.4查询和学生柏文楠是同一个民族的学生（用自身联接实现）。SELECT*FROMStudentWHEREsEthnic=(SELECTsEthnicFROMStudentWHEREsname='张三')2.5分别用等值联接和内联接查询有授课记录的老师的姓名。SELECTDISTINCTT_NameFROMTeacher,TCWHERETeacher.T_ID=TC.T_ID2.6用外联接查询所有老师的授课情况，输出老师的工号、姓名、职称、院系、担任的课程号和授课的学期，结果按院系和职称升序排列。如果该老师没有授课历史，在课程号和授课的学期中显示空值SELECTTeacher.T_ID,Teacher.T_Name,Teacher.Academic_Title,Department.DEPT_Name,TC.C_ID,TC.SemesterFROMTeacherRightjoinTCONTeacher.T_ID=TC.T_IDjoinDepartmentONDepartment.DEPT_ID=Teacher.DEPT_IDORDERBYDepartment.DEPT_Name,Teacher.Academic_Title4．实验思考：①联接查询中，输出列名时何时可以忽略列名前的表名，何时不能？答：若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。若选择的字段名在各个表中有相同的，则不可以省略字段名前的表名。②联接查询中，INNERJOIN、LEFTOUTERJOIN、RIGHTOUTERJOIN、FULLOUTERJOIN的结果各有什么不同？假设我们有两张表。TableA是左边的表。TableB是右边的表。其各有四条记录，其中有两条记录name是相同的，如下所示：让我们看看不同JOIN的不同A表idname1Pirate2Monkey3Ninja4SpaghettiB表idname1Rutabaga2Pirate3DarthVade4Ninja1.INNERJOINSELECT*FROMTableA

INNERJOIN

TableBONTableA.name=TableB.name结果集(TableA.)(TableB.)idnameidname1Pirate2Pirate3Ninja4NinjaInnerjoin产生的结果集中，是A和B的交集。2.FULL[OUTER]JOIN

(1)SELECT*FROMTableA

FULLOUTERJOIN

TableBONTableA.name=TableB.name

结果集(TableA.)(TableB.)idnameidname1Pirate2Pirate2Monkeynullnull3Ninja4Ninja4Spaghettinullnullnullnull1Rutabaganullnull3DarthVadeFullouterjoin产生A和B的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。可以使用IFNULL判断。(2)SELECT*FROMTableA

FULLOUTERJOIN

TableBONTableA.name=TableB.name

WHERETableA.idISnullORTableB.idISnull结果集(TableA.)(TableB.)idnameidname2Monkeynullnull4Spaghettinullnullnullnull1Rutabaganullnull3DarthVade

产生A表和B表没有交集的数据集。

3.LEFT[OUTER]JOIN(1)SELECT*FROMTableA

LEFTOUTERJOIN

TableBONTableA.name=TableB.name结果集(TableA.)(TableB.)idnameidname1Pirate2Pirate2Monkeynullnull3Ninja4Ninja4SpaghettinullnullLeftouterjoin产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。(2)SELECT*FROMTableA

LEFTOUTERJOIN

TableBONTableA.name=TableB.nameWHERETableB.idISnull结果集(TableA.)(TableB.)idnameidname2Monkeynullnull4Spaghettinullnull产生在A表中有而在B表中没有的集合。4.RIGHT[OUTER]JOINRIGHTOUTER

JOIN是后面的表为基础，与LEFTOUTERJOIN用法类似。5.UNION

与

UNIONALLUNION操作符用于合并两个或多个SELECT语句的结果集。

请注意，UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。UNION只选取记录，而UNIONALL会列出所有记录。(1)SELECT

nameFROM

TableA

UNION

SELECTnameFROMTableB新结果集namePirateMonkeyNinjaSpaghettiRutabagaDarthVade选取不同值(2)SELECT

nameFROM

TableA

UNIONALL

SELECTnameFROMTableB新结果集namePirateMonkeyNinjaSpaghettiRutabagaPirateDarthVadeNinja全部列出来(3)注意:SELECT

*FROM

TableA

UNION

SELECT*FROMTableB新结果集idname1Pirate2Monkey3Ninja4Spaghetti1Rutabaga2Pirate3DarthVade4Ninja由于id1Pirate

与id2Pirate并不相同，不合并

还需要注册的是我们还有一个是“交差集”crossjoin,这种Join没有办法用文式图表示，因为其就是把表A和表B的数据进行一个N*M的组合，即笛卡尔积。表达式如下：SELECT*FROMTableACROSSJOINTableB这个笛卡尔乘积会产生4x4=16条记录，一般来说，我们很少用到这个语法。但是我们得小心，如果不是使用嵌套的select语句，一般系统都会产生笛卡尔乘积然再做过滤。这是对于性能来说是非常危险的，尤其是表很大的时候。③LIKE匹配字符有几种？如果要检索的字符中包含匹配字符，该如何处理？1,select*fromstudentwhere学号like'%gg%':学号中任意位置含有'gg'的

2.select*fromstudentwhere学号like'gg%':学号