数据库查询操作详解

一、数据查询-----单表查询(1)查询语句格式Select[all|distinct]目标列表达式[,目标列表达式]……From表名或视图[,表名或视图]……[where条件表达式][groupby列名1[having条件表达式]][orderby列名2[asc|desc]];注：[all|distinct]中all为缺省值，取消结果中的重复列则用distinct；[asc|desc]中asc为缺省值，表示按照升序排列。对于空值，若按照升序排，则含空值的元组显示在最后面；若按降序排，则空值的元组最先显示。(2)查询指定列a)查询部门表dept中所有部门的详细信息，并且列名用汉字表示。selectDNO,DNAME,ADDRfromdept;b)查询部门表dept中人力资源部的部门编号。selectDNOfromdeptwhereDNAME='人力资源部';目标列表达式中各个列的先后顺序可以与表中的顺序不一致.(3)查询全部列查询全体学生的详细记录Select*FromStudent;(4)将查询结果的列名用别名显示查询部门表dept中所有部门的详细信息，并且列名用汉字表示。selectDNO部门编号,DNAME部门名称,ADDR部门地址fromdept;(5)在查询的结果中插入新的一列用来显示指定的内容SelectSnameNAME,’YearofBirth:’BIRTH,SbirthBIRTHDAY,SdeptDEPARTMENTFromStuent;则显示的结果中，每个元组的第二列均为”YearofBirth:”，此列在原数据库中是不存在的.(6)查询经过计算的值Select子句的目标列表达式不仅可以是表中的属性列，也可以是表达式。例：查询全体学生的姓名及其出生年月SelectSname,2004–Sage/*当时年份减去年龄为出生年月FromStufent;(7)设置查询显示的字母全为大写(或小写)SelectSname,’YearofBirth:’,2004–Sage,LOWER(Sdept)FromStudent;此时Sdept显示的结果全为小写SelectSname,’YearofBirth:’,2004–Sage,UPPER(Sdept)FromStudent;注：要设置查询表中的属性列名的大小写可以用LOWER,UPPER。(8)消除取值重复的行a)查询雇员表empl中出现的所有部门编号，要求无重复。selectdistinctDNOfromEMPL;b)查询项目表proj中所有项目名称。selectPNAMEfromPROJ等价于selectallPNAMEfromPROJ(9)查询满足条件的元组常用的查询条件查询条件谓词比较=,,,=,=,!=,,!,!,NOT+上述比较运算符确定范围Betweenand,notbetweenand确定集合In,notin字符匹配Like,notlike空值NULL,NOTNULL多重条件(逻辑运算)And,or,not注：Between后是范围的下限，and后是范围的上限.查询结果中包含上下限的结果.a)查询成绩不及格的学生的学号SelectdistinctSnoFromSCWhereGrade60;b)查询年龄在20到30(包含20及30)之间的学生的姓名、系别和年龄SelectSname,Sdept,SageFromStudentWhereSagebetween20and30;c)查询计算科学系(CS),数学系(MA),信息系(IS)学生的姓名和性别SelectSname,SsexFromStudentWhereSdeptin(‘CS’,’MA’,’IS’);(10)含通配符”%”的查询%(百分号)：代表任意长度(长度可以为0)的字符串，例如a%b代表以a开头且以b结尾的任意长度的字符串，acb,afdsagasdsab,ab都满足。例：查询雇员表empl中姓名以“伟“字结尾的员工信息。select*fromEMPLwhereENAMElike'%伟';(11)含通配符”_”的查询_(下划线)：代表任意单个字符，例a_b代表以a开头且以b结尾的长度为3的字符串。例：查询姓“欧阳”且全名3个汉字的学生的姓名SelectSnameFromStudentWhereSnamelike‘欧阳__’;注：一个汉字占两个字符的位置(12)字符串本身含通配符的查询使用使用escape‘换码字符’。查询以”DB_”开头，且倒数第三个字符为i的课程的详细情况。Select*FromStudentWhereCnamelike‘DB\_%i__’escape‘\’;注：escape‘\’表示”\”为换码字符，则第一个”_”是普通的字符，后两个”_”表示通配符。(13)涉及空值的查询例：查询所有有成绩的学生的学号SelectSnoFromSCWhereGradeISNULL;注：此处“IS”不能用”=”替代(14)带排序的查询查询工作表job中的工作信息，结果按工作天数升序排列。select*fromJOBorderbyDAYSasc;等价于select*fromJOBorderbyDAYSasc;查询雇员表empl中所有员工的详细信息，结果按员工姓名降序排列。select*fromEMPLorderbyENAMEdesc;(15)含聚集函数的查询聚集函数主要有：Count([distinct|all]*)//统计元组个数Count([distinct|all]列名)//统计一列中值的个数Sum([distinct|all]列名)//计算一列值的总和(此列必须是数值型)Avg([distinct|all]列名)//计算一列值的平均值(此列必须是数值型)Max([distinct|all]列名)//求一列值中的最大值Min([distinct|all]列名)//求一列值中的最小值注：[distinct|all]缺省时为all.在聚集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值.例：查询1号课程的学生的最高分数Selectmax(Grade)FromSCWhereCno=‘1’;(16)到GROUPBY子句的查询a)求各个课程号及相应的选课人数selectCno,count(Sno)fromSCgroupbyCno;b)查询选修了3们以以上的课程的学生的学号selectSnofromSCcroupbySnohavingcount(*)3;注：where子句与having子句短语的区别在于作用对象不同。Where子句作用于基本表或视图，从中选择满足条件的元组；having子句作用与组，从中选择满足条件的组。二、数据查询-----连接查询若一个查询同时设计两个以上的表，则称之为连接查询。(1)等值与非等值连接查询格式如下：[表名1.]列名1比较运算符[表名2.]列名2其中主要的比较运算符有：=,,,=,=,!=(或)等.此外连接谓词还可以有如下的形式：[表名1.]列名1between[表名2.]列名2and[表名2.]列名2当连接运算符为=时，称为等值连接，否则称为非等值连接。注：当属性列在查询的所有表中是唯一的时候则可以去掉前面的表名，否则必须加上表名。例：查询每个学生及其选修课程的情况.SelectStudent.*,SC.*FromStudent,SCWhereStudent.Sno=SC.Sno;(2)自身连接例：查询每一门课程的间接先修课(即先修课的先修课)分析：此时为Course表的自身连接，故要为Course表去两个别名，一个是first,一个是second。Selectfirst.Cno,second.CnoFromCoursefirst,CoursesecondWherefirst.Cpno=second.Cno;(3)外连接在上例中，没有显示200215123和200215125两个学生的信息，原因在于他们没有选课。有时想以Student表为主体列出每个学生的基本情况及其选课情况，则需要使用外连接。用外连接做上面的例题：SelectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,CgradeFromStudentLEFTJOINSCON(Student.Sno=Sc.Sno);/*也可以用USING来去掉上面结果中的重复值：FromStudentLEFTJOINSCUSING(Sno);*/注：做链接列出左边关系(如本例)中的所有元组，右外连接列出右边关系中的所有元组。(4)复合条件连接在上面的例子中，where子句中只有一个条件，即连接谓词。Where也可以有多个连接条件，称为符合条件连接。例：查询选修2号课程且成绩在90分以上的所有学生SelectStudent.Sno,SnameFromStudent,SCWhereStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=‘2’ANDSC.Grade90;/*其它限制条件*/例：查询每个学生的学号、姓名、选修课程名及成绩SelectStudent.Sno,Sname,Cname,GradeFromStudent,SC,CourseWhereStudent.Sno=SC.SnoandSC.Cno=Course.Cno;三、数据查询-----集合查询集合查询操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。注：多个集合操作的个查询结果的列数必须相同，对应项的数据类型也必须相同。(1)并操作UNION使用UNION将多个查询结果并起来时，系统会自动的去掉重复元组；如果要保留重复元组，则可以使用UNIONALL操作符。例：查询计算机科学系的学生及年龄不大于19岁的学生.Select*FromStudentWhereSdept=‘CS’UnionSelect*FromStudentWhereSage=19;例：查询选修了1号课程或者2号课程的学生SelectSnoFromSCWhereCno=‘1’UnionSelectSnoFromSCWhereCno=‘2’;(2)交操作(INTERSECT)例：查询计算机科学系的学生与年龄不大于19岁的学生的交集Select*FromStudentWhereSdept=‘CS’IntersectSelect*FromStudentWhereSage=19;等价于Select*FromStudentWhereSdept=‘CS’andSage=19;(3)差操作(EXCEPT)例：查询计算机科学系的学生与年龄不大于19岁的学生的差集Select*FromStudentWhereSdept=‘CS’ExceptSelect*FromStudentWhereSage=19;四、数据查询-----嵌套查询一个select-from-where语句称为一个查询块。注：子查询的select语句中不能使用orderby子句，orderby子句只能对最终的查询结果排序。(1)带IN谓词的子查询例：查询与“刘晨”在同一个系学习的学生SelectSno,Sname,SdeptFromStudentWhereSdeptin(selectSdeptFromStudentWhereSname=‘刘晨’);(2)带有比较运算符的子查询例：找出每个学生超过他选修课平均成绩的课程号SelectSno,CnoFromSCxWhereGrade=(selectAVG(Grade)/*某个学生的平均成绩*/FromSCyWherey.Sno=x.Sno);(3)带有ANY(SOME)或ALL谓词的子查询子查询返回单值可以用比较运算符，但是但会多值要用NAY或ALL谓词修饰。而使用ANY或ALL谓词时则必须同时使用比较运算符。例：查询其他系中比计算机科学系某一学生年龄小的学生的姓名