标准SQL语句详细讲解.pptx

SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；下面列举的查询例子都是在某个学生-成绩数据库中进行的。该数据库中有以下三张表：学生表：Student(Sno，Sname，Ssex，Birthday，Sdept)课程表：Course(Cno，Cname，Ccredit，Period)学生成绩表：SC(Sno，Cno，Score)1.单表查询（1）选择表中的若干列例2-8在Student表中查询全体学生的学号和姓名。SELECTSno，SnameFROMStudent；例2-9查询Student表中的所有属性。SELECTSno，Sname，Ssex，SdeptFROMStudent；或SELECT*FROMStudent；1.单表查询例2-10在Student表中查询全体学生的姓名和年龄。SELECTSname，2012-year(birthday)asSageFROMStudent；输出结果：SnameSage-金小巧191920王大海张丰丰胡丽19杨建国19李凯2019吴伟蒋立朋19（2）选择表中的若干元组使用短语ALL与DISTINCT例2-11在SC表中查询有成绩的学生的学号。SELECTSnoFROMSC;查询结果与使用DISTINCT短语的区别？SELECTDISTINCTSnoFROMSC;正确的SELECTSnoFROMSCwherescore0;（2）选择表中的若干元组查询满足条件的元组WHERE子句（实现查询满足指定条件的元组）常用的查询条件有：1.比较大小WHERE子句中使用的比较运算符有：=，=，=，!=，!，!，（2）选择表中的若干元组例2-12查询所有不及格的学生学号和成绩。SELECTSno，ScoreFROMSCWHEREScore=60；2.确定范围确定查询范围使用谓词BETWEENAND或NOTBETWEENAND例2-13查询有成绩在90至100（包括90分和100分）之间的学生的学号。SELECTSnoFROMSCWHEREScoreBETWEEN90AND100；3.确定集合使用谓词IN和NOTIN可以查找属性值属于指定集合的元组。其中：是用逗号分隔的一组值。例2-14号。查询化学系和计算机系学生的姓名和学SELECTSno,，SnameFROMStudentWHERESdeptIN(化学系，计算机系);例2-15学生的姓名和学号。查询既不是化学系，也不是计算机系的SELECTSname，SnoFROMStudentWHERESdeptNOTIN(化学系，计算机系);4.字符串匹配:字符串匹配用谓词LIKE实现格式：NOTLIKEESCAPE当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或运算符取代NOTLIKE谓词串。4.字符串匹配字符串匹配用谓词LIKE实现通配符是可以代替任何一个字符或字符串的符号，其中：%：代表任意长度（长度可以为0）的字符串。例如，m%n表示以m开头，以n结尾的任意长度的字符串。_(下横线)：代表任意单个字符。例如，m_n表示以m开头，以n结尾的长度为3的任意字符串。如min，man都满足该匹配串，但是main和mn不满足该匹配串。注意，Access中使用的通配符与这里介绍的标准SQL有所不同。在Access中，*和?为通配符，其中*代表任意长度的字符串，?代表任意单个字符。例2-16查询学号为20120106的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE20120106；或SELECT*FROMStudentWHERESno=20120106；例2-17查询所有姓金学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE金%；例2-18查询姓金且全名为三个汉字的学生姓名。SELECTSnameFROMStudentWHERESnameLIKE金_；例2-19查询所有不姓金的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE金%；例2-20假设Course表中有一课程为DB_Design，查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_Design查询空值时使用谓词ISNULL或ISNOTNULL，这里“ISNULL”不能用“=NULL”代替。例2-21查询缺少成绩的学生的学号和相应的课程号。某些学生没有参加考试，所以没有考试成绩，要用空值查询。SELECTSno，CnoFROMSCWHEREScoreISNULL；多重条件查询当查询条件有多个时，可以用逻辑运算符AND和高于OR，括号中运算符的优先级最高。OR来联结多个查询条件，AND的优先级例2-13中查询有成绩在90至100（包括90分和100分）之间的学生的学号。SELECTSnoFROMSCWHEREScore=90ANDScore=100；（3）查询结果排序例2-22查询选修了280010号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，ScoreFROMSCWHERECno=280010查询结果SnoGrade-ORDERBYScoreDESC；-2012010392201201078820120101862012010661（4）使用函数常用的有：计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）统计元组个数统计一列中值的个数计算总和SUM（DISTINCT|ALL）计算一列数值型数值的总和计算平均值AVG（DISTINCT|ALL）计算一列数值型数值的平均数(4）使用函数求最大值MAX（DISTINCT|ALL）求最大值求最小值MIN（DISTINCT|ALL）求一最小值其中，DISTINCT短语：计算时要取消指定列中重复值ALL短语：不取消重复值,ALL为缺省值例2-23查询学生总人数。SELECTCOUNT(*)FROMStudent；例2-24查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；用DISTINCT以避免重复计算学生人数例2-25计算280010号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=280010；例2-26查询选修280010号课程的学生最高分数。SELECTMAX(Grade)FROMSC（5）对查询结果分组例2-27求各个系相应的学生人数。SELECTSdeptas系名，COUNT(Sno)as学生人数FROMStudentGROUPBYSdept；（5）对查询结果分组例2-28查询系学生人数在3人以上的系。SELECTSdeptas系名FROMStudentGROUPBYSdeptHAVINGCOUNT(*)3；2连接查询例2-29查询每个学生信息和各门课程的成绩。SELECTbirthday，Sdept，Cno，ScoreStudent.Sno，Sname，Ssex，FROMStudent，SCWHEREStudent.Sno=SC.Sno；SnoSnameSsexbirthdaySdeptCnoScore20120101金小巧女1986.7英语系2800108620120103张丰丰女1985.11英语系2800109220120112胡丽女1986.8化学系2700108320120112胡丽女1986.8化学系2900105620120105杨建国男1986.3化学系2700108420120105杨建国男1986.3化学系2900107820120106李凯男1985.12英语系2800106120120107吴伟男1986.4英语系2800108820120108蒋立朋男1986.2计算机系2900106820120108蒋立朋男1986.2计算机系3000107820120108蒋立朋男1986.2计算机系3000118720120108蒋立朋男1986.2计算机系30001291如果某个学生没有课程成绩，只输出基本情况信息其成绩信息为空值，这时就需要将上述查询语句改为：SELECTStudent.Sno，Sname，Ssex，birthday，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；SnoSnameSsexbirthdaySdeptCnoScore20120101金小巧女1986.7英语系2800108620120102王大海男1986.1英语系20120103张丰丰女1985.11英语系2800109220120112胡丽女1986.8化学系2700108320120112胡丽女1986.8化学系2900105620120105杨建国男1986.3化学系2700108420120105杨建国男1986.3化学系2900107820120106李凯男1985.12英语系2800106120120107吴伟男1986.4英语系2800108820120108蒋立朋男1986.2计算机系2900106820120108蒋立朋男1986.2计算机系3000107820120108蒋立朋男1986.2计算机系3000118720120108蒋立朋男1986.2计算机系30001291例2-30查询课程290010成绩不及格的所有学生的学号、姓名。SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=290010ANDSC.Grade60；例2-31查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno，Sname，Cname，ScoreFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；2.2.4数据更改功能数据更改功能包括插入数据、修改数据和删除数据。1.插入数据插入数据的功能是将新元组插入到指定的表中。格式:INSERTINTO(，)VALUES(，)；2.2.4数据更改功能注意：（1）属性列的顺序可与表定义中的顺序不一致；（2）没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致，值的个数一致，值的数据类型一致；（3）插入的元组在没有指定的属性列上取空值。例2-32该学生的学号是20120109；姓名：刘刚；性别：男；所在系：计算机系；出身年月：1985年10月。将一个新学生记录插入到Student表中。INSERTINTOStudentVALUES(20120109，刘刚，男，2012.10，计算机系)；例2-33在SC表插入一条学生课程记录：学号为20120109的学生选修的课程号为300010，成绩为空。INSERTINTOSC(Sno，Cno)VALUES(20120109，300010)；2.2.4数据更改功能2.修改数据修改指定表中满足WHERE子句条件的元组。格式：UPDATESET=，=WHERE；其中：SET子句要修改的列和修改后取值。WHERE子句指定要修改的元组，缺省表示要修改表中的所有元组。例2-34将学生20120109的名字改为刘钢。UPDATEStudentSETSname=刘钢WHERESno=20120109；例2-35将所有计算机系学生的系别改为“计算机科学系”。UPDATEStudentSETSdept=计算机系WHERESdept=计算机科学系；2.2.4数据更改功能3.删除数据删除指定表中满足WHERE子句条件的元组。格式：DELETEFROMWHERE；其中：WHERE子句指定要删除的元组，缺省表示要修改表中的所有元组。例2-36删除学号为20120109的学生记录。DELETEFROMStudentWHERESno=20120109；例2-37删除课程号为290010的所有选课记录。DELETEFROMSC；WHERECno=290010；例2-38删除所有的学生选课记录。DELETEFROMSC；2.2.5视图1 视图的概念视图是从一个或几个表（或视图）中导出的虚表.表1学号姓名性别单位表2学号课程分数学号姓名课程分数视图在关系数据库中，视图有着重要的作用：（1）简化用户的操作。（2）使用户能从多种角度看待同一数据。（3）对重构数据库提供了一定程度的逻辑独立（4）能够提高数据的安全性。2.2.5视图2.建立视图建立视图的语句格式如下：CREATEVIEW(，)ASWITHCHECKOPTION；例2-39建立计算机系学生的视图。CREATEVIEWCS_StudentASSELECTSno，Sname，Ssex，BirthdayFROMStudentWHERESdept=计算机系；例2-40建立计算机系学生的视图，并要求透过该视图进行的更新操作只涉及计算机系学生。CREATEVIEWCS_StudentASSELECTSno，Sname，Ssex，BirthdayFROMStudentWHERESdept=计算机系WITHCHECKOPTION；对CS_Student视图的更新操作有：（1）修改操作：DBMS自动加上Sdept=计算机系条件。（2）删除操作：DBMS自动加上Sdept=计算机系条件。（3）插入操作：DBMS自动检查Sdept字段值是否为计算机系。如果不是，则拒绝该插入操作。如果没有提供Sdept字段值，则自动定义Sdept为计算机系。例2-41建立计算机系选修了300011号课程且成绩在60分以上的学生视图。CREATEVIEWCS_S1ASSELECTSno，Sname，ScoreWHEREStudent.Sno=SC.SnoANDSdept=计算机系ANDCno=300011ANDScore=60;FROMStudent,SC2.2.5视图3.删除视图删除视图是从数据字典中删除指定的视图定义。语句格式如下：DROPVIEW；例2-42删除视图CS_Student。DROPVIEWCS_Student；2.2.5视图4.查询视图建立了视图后，用户查询视图的方法与查询表是相同的。例2-43在计算机系学生的视图CS_S1中查询成绩为90分以上的学生。SELECTSno，SnameFROMCS_S1WHEREScore=90；例2-44在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMWHERESavg=90；S_G由于S_G视图定义为：CREATEVIEWS_G(Sno，Savg)ASSELECTSno，AVG(Score)FROMSC例2-44在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMS_GWHERESavg=90；实际，查询被转换为：SELECTSno，AVG(Score)FROMGROUPBYSnoHAVINGAVG(Score)=90；SC例2-45将计算机系学生视图CS_Student中学号为20120109的学生姓名改为“刘辰”。UPDATECS_StudentSETSname=刘辰WHERESno=20120109；转换后的语句为：UPDATEStudentSETSname=刘辰WHERESno=20120109ANDSdept=计算机系；例2-46向计算机系学生视图CS_Student中插入一条新的学生记录：赵新，学号为20120201，性别为男，生日为1993年7月3日。INSERTINTOCS_StudentVALUES(20120201,赵新,男,#1993-7-3#)；例2-47删除视图CS_Student中学号为20120201的记录。DELETEFROMCS_StudentWHERESno=20120201；转换为对基本表数据的删除：DELETEFROMStudentWHERESno=20120201ANDSdept=计算机系；