经典SQL语句

第四章SQL语句本章要点：SQL的语句类型SQL的单表数据查询SQL多表连接查询数据操纵4.1SQL的语句类型（1）查询语言（QL）查询语言用来对已存在的数据库中的数据按照指定的组合、条件表达式或排序进行检索。它的基本结构是由SELECT子句、FROM子句、WHERE子句组成的查询块。（2）数据操纵语言（DML）DML的命令用来改变数据库中的数据，它有3个基本语句：INSERT（插入）、UPDATE（修改）、DELETE（删除）。(3）数据定义语言（DDL）DDL用来创建数据库中的各种对象，包括数据库模式、表、视图、索引、同义词、聚簇等，它的基本语句有：CREATEDATABASE、CREATETABLE、CREATEVIEW、CREATEINDEX等。（4）数据控制语言（DCL）DCL用来授予或回收访问数据库的某种特权，控制数据操纵事务的发生时间及效果、对数据库进行监视等。数据控制基本语句有：GRANT、REVOKE、COMMIT、ROLLBACK、LOCK、UNLOCK等。SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE，如表4-1所示。而且SQL语言语法简单，接近英语口语，因此易学易用。表4-1SQL语言的动词SQL功能动词数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE4.2SQL的单表数据查询SQL的SELECT语句用于检索数据。“SELECT-FROM-WHERE”的基本结构块：SELECTA１，A２，…，AnFROMR１，R２，…，RmWHEREP4.2.1单表查询一、选择表中若干列1．查询指定的列例1、查询全体学生的学号和姓名。SELECTSno，SnameFROMStudent例2、查询全体学生的姓名、学号和所在系SELECTSname，Sno，SdeptFROMStudent注：目标列的选择顺序可以与表中定义的列的顺序不一致2．查询全部列例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent等价于：SELECT*FROMStudent3、查询经过计算的列SELECT子句中的目标列名序列可以是表中存在的属性列，也可以是表达式的结果。例4查询全体学生的姓名及出生年份。SELECTSname，2008-SageFROMStudent例5查询全体学生的姓名、出生年份和所在系，要求去掉所在系名右边的无用空格。SELECTSname，2008-Sage，RTRIM（Sdept）FROMStudent注：RTRIM（）函数的作用是去掉右边的无用空格。改变列标题的语法格式为：列名|表达式[AS]列标题或：列标题=列名|表达式例如，对于例5可写成：SELECTSname姓名，2008-Sage出生年份，LTRIM（Sdept）所在系FROMStudent二、选择表中的若干元组1、消除取值相同的行如果要去掉结果表中的重复行，可通过使用DISTINCT实现，DISTINCT要写在SELECT关键字的后边，其他修饰符和查询列标的前边。例6在修课表中查询有哪些学生修了课程，要求列出学生的学号，去掉重复列。SELECTDISTINCTSnoFROMSC2、查询满足条件的元组查询满足条件的元组是通过WHERE子句实现的。WHERE子句的格式为：WHERE搜索条件搜索条件：：={[NOT]谓词|（搜索条件）}[{AND|OR}[NOT]{谓词|（搜索条件）}]}[，…]常用的谓词如下表所示谓词种类谓词比较=,,=,=,,,!=,!,!,NOT确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件（逻辑谓词）AND，OR1）比较大小例7、查询计算机系全体学生的姓名SELECTSnameFROMStudentWHERESdept=‘计算机系’例8、查询所有年龄在20岁以下的学生的姓名和年龄。SELECTSname，SageFROMStudentWHERESage20或SELECTSname，SageFROMStudentWHERENOTSage=20注：通过使用NOT对条件取反进行的查询效率总是比较低的，因此，能用正的条件实现的查询尽量不要用反的条件实现（用NOT实现）例9、查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade60注：当一个学生有多门不及格课程时，只需列出一个学号，因此这里使用DISTINCT2）确定范围确定范围的谓词为：BETWEEN…AND格式为：列名|表达式[NOT]BETWEEN下限值AND上限值BETWEEN…AND一般用于对数值型数据和日期型进行比较。列名或表达式的类型要与下限值或上限值的类型相同。NOTBETWEEN…AND含义正好相反例10查询年龄在20-23岁之间的学生的学生姓名、所在系和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23等价于：SELECTSname，Sdept，SageFROMStudentWHERESage=20ANDSage=23例11查询年龄不在20-23岁之间的学生姓名、所在系和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23等价于：SELECTSname，Sdept，SageFROMStudentWHERESage20ORSage233）确定集合确定集合的运算符为：IN，IN可以用来查找属性值属于指定集合的元组。使用IN的格式为：列名[NOT]IN（常量1，常量2，…，常量n）NOTIN的含义正好相反例12查询信息系、数学系和计算机系学生的姓名、性别。SELECTSname，SsexFROMStudentWHERESdeptIN（‘信息系’，‘数学系’，‘计算机系’）等价于：SELECTSname，SsexFROMStudentWHERESdept=‘信息系’ORSdept=‘数学系’ORSdept=‘计算机系’例13查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN（‘信息系’，‘数学系’，‘计算机系’）等价于：SELECTSname，SsexFROMStudentWHERESdept！=‘信息系’ANDSdept！=‘数学系’ANDSdept！=‘计算机系’4）字符串匹配LIKE用于查找指定列名与匹配串常量匹配的元组。LIKE运算符的一般形式为：列名|字符串表达式[NOT]LIKE匹配串SQLServer2000支持如下四种通配符：＿（下划线）：匹配任意一个字符；％（百分号）：匹配０个或多个字符；［］：匹配［］中的任意一个字符。中可用连字符－［^］：不匹配［］中的任意一个字符。例14查询姓‘张’的学生的详细信息。SELECT*FROMStudentWHERESnameLIKE‘张％’例15查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。SELECT*FROMStudentWHERESnameLIKE‘[张李刘]%’例16查询名字中第２个字为‘小’或‘大’字的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE‘_[小大]％’例17查询所有不姓‘刘’的学生。SELECT*FROMStudentWHERESnameNOTLIKE‘刘％’例18从学生表中查询学号的最后一位不在2-6范围内的学生情况。SELECT*FROMStudentWHERESnoLIKE‘%[^2-6]’例19查询所有姓李的并且第二个字是‘%’的学生的信息。SELECT*FROMstudentWHEREsnamelike'李[%]%'5）涉及空值的查询例20查询没有考试成绩的学生的学号和相应的课程号SELECTSno，CnoFROMSCWHEREGradeISNULL例21查询所有有考试成绩的学生的学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL6）多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询使用AND谓词的语法格式为：布尔表达式１AND布尔表达式２AND…AND布尔表达式ｎ使用OR谓词的语法格式为：布尔表达式１OR布尔表达式２OR…OR布尔表达式ｎ例22查询计算机系年龄在２０岁以下的学生的姓名。SELECTSnameFROMStudentWHERESdept=‘计算机系’ANDSage20例23查询计算机系和信息系学生中年龄在18-20岁之间的学生的学号、姓名、所在系和年龄。SELECTSno，Sname，Sdept，SageFROMStudentWHERE（Sdept=‘计算机系’ORSdept=‘信息系’）ANDSagebetween18and20注：OR运算符的优先级小于AND，要改变运算的顺序可以通过加括号的方式实现。3、使用CASE子句对查询结果进行分析CASE表达式的语法格式：CASE测试表达式WHEN简单表达式1THEN结果表达式1WHEN简单表达式2THEN结果表达式2……WHEN简单表达式nTHEN结果表达式n[ELSE结果表达式n+1]END例24查询‘C01’课程的考试情况，列出学号和成绩，同时对成绩做如下处理：当成绩大于等于90分时，在结果中显示‘优’；当成绩在80到89分之间时，在结果中显示‘良’；当成绩在70到79分之间时，在结果中显示‘中’；当成绩在60到69分之间时，在结果中显示‘及格’；当成绩小于60分时，在结果中显示‘不及格’SELECTSno，CASEWHENGrade=90THEN‘优’WHENGradebetween80and89THEN‘良’WHENGradebetween70and79THEN‘中’WHENGradebetween60and69THEN‘及格’WHENGrade60THEN‘不及格’ENDAS成绩FROMSCWHERECno=‘C01’ANDGradeISNOTNULL例25使用CASE子句对pubs数据库中的titles表中的数据更改图书分类（Category）的显示，以使其更易于理解。USEpubsSELECTCategory=CASEtypeWHEN‘popular_comp’THEN‘流行计算类’WHEN‘mod_cook’THEN‘现代烹饪类’WHEN‘business’THEN‘商业类’WHEN‘psychology’THEN‘心理学类’WHEN‘trad_cook’THEN‘传统烹饪类’ELSE‘未分类’END，title,priceFROMtitlesWHEREpriceISNOTNULL（三）、对查询结果进行排序可将查询结果按照某个列或某几个列的顺序进行排序，排序可以是从小到大（升序），也可以是从大到小（降序）。排序子句的格式为：ORDERBY列名[ASC|DESC][，…n]例26查所有学