程序员sql查询教程PPT课件

5.2 SELECT查询语句 5.2.1 查询分析器 5.2.2 查询语句格式 5.2.3关于演示数据库的说明第1页/共101页5.2.1 查询分析器在SQL查询分析器中，用户既可在全文窗口中输入Transact-SQL语句，也可以打开包含Transact-SQL语句的文本文件，执行语句并在结果窗口中，查看结果。启动SQL查询分析器可采用两种方法： 在“开始”菜单中，通过Microsoft SQL Server下拉菜单的“查询分析器”命令来打开查询分析器。 在SQL Server企业管理器中通过工具菜单启动。 第2页/共101页 启动SQL查询分析器并执行相应语句的操作步骤如下：(1)在【开始】菜单中，点击【程序】级联菜单，选择【Microsoft SQL Server】程序组中的【查询分析器】选项，即可启动SQL查询分析器。或在企业管理器中，点击【工具】，选择【SQL查询分析器】选项，也可启动SQL查询分析器。(2)在启动SQL查询分析器时，系统首先打开【连接到SQL Server】对话框。使用该对话框可以指定连接到哪个SQL Server服务器。如图5-1所示。第3页/共101页图5-1 连接到SQL Server对话框第4页/共101页 (3)单击SQL Server文本框后面的按钮，可打开【选择服务器】对话框。可以在此对话框中选择要连接到的SQL Server服务器。如图5-2所示。 (4)在【连接到SQL Server】对话框中设置正确的验证模式，即可连接到SQL Server服务器，并打开SQL查询分析器。如图5-3所示。 (5) 进入【SQL 查询分析器】界面，在工具栏中，单击 master所在的下拉框，即可选择待查询的数据库，例如选择【学生信息数据库】。(6)在SQL语句录入框中录入SQL语句，然后单击工具栏中按钮【 】，执行该【SELECT】查询语句，即可得到操作结果。第5页/共101页图5-2 选择服务器对话框第6页/共101页图5-3 选择服务器对话框第7页/共101页5.2.2 查询语句格式在SQL语言中，使用SELECT语句进行数据库的查询时，应用灵活、功能强大。1、基本格式SELECT ALL | DISTICT ， ， FROM ， ，. WHERE GROUP BY HAVING ORDER BY ASC | DESC 2、语句说明SELECT语句的基本格式是由SELECT子句、FROM子句和WHERE子句组成的查询块。第8页/共101页整个SELECT语句的含义是：根据WHERE子句的筛选条件表达式，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的字段次序，筛选出记录中的字段值构造一个显示结果表。如果有GROUP子句，则将结果按的值进行分组，该值相等的记录为一个组。如果GROUP子句带HAVING短语，则只有满足指定条件的组才会显示输出。提示：SELECT语句操作的是记录（数据）集合（一个表或多个表），而不是单独的一条记录。语句返回的也是记录集合（满足Where条件的），即结果表。第9页/共101页5.2.3关于演示数据库的说明为了说明【SELECT】语句的各种用法，下面我们以【学生课程数据库】为操作对象进行说明。 在【student】数据库中包含了4个表： 学生信息表；反映了学生个人信息，具体的记录如图5-4所示。 课程信息表：反映了学校的课程信息，具体的记录如图5-5所示。 成绩信息表：反映了学生考试成绩信息，具体的记录如图5-6所示。 班级信息表：反映了学生所在班级的信息，具体的记录如图5-7所示。第10页/共101页图5-4 学生信息表第11页/共101页图5-5 课程信息表第12页/共101页图5-6 成绩信息表第13页/共101页图5-7 班级信息表第14页/共101页图5-8 学生信息表结构第15页/共101页图5-9 课程信息表结构第16页/共101页图5-10 成绩信息表结构 第17页/共101页图5-11 班级信息表结构第18页/共101页5.3 基于单表的查询 问题：我们需要对数据进行哪些维护操作？如何对数据进行查询、统计? 结构化查询语言（Structured Query Language）。这种语言的语法结构类似于英语，易学易用，书写随意。第19页/共101页查询表中指定的字段SELECT子句后的各个字段的先后顺序可以与原表中的顺序不一致，但在结果表中，字段是按照SELECT子句后的各个字段的顺序显示。【例5-1】显示student_info表中学生的姓名、性别以及家庭住址。SELECT student_name, student_sex, address FROM student_info 第20页/共101页 通配符“*”的使用在SELECT语句中，可以使用通配符“*”显示所有的字段。SELECT * FROM student_info【例5-2】 列出student_info表所有的字段。第21页/共101页 使用单引号加入字符串在SELECT语句中，可以在一个字段的前面加上一个单引号字符串，对后面的字段起说明作用。【例5-3】显示student_info表中学生的姓名、家庭住址。SELECT student_name,家庭住址, address FROM student_info第22页/共101页使用别名在显示结果时，可以指定以别名代替原来的字段名称，总共有3种方法： 采用“字段名称 AS 别名”的格式。 采用“字段名称 别名” 的格式。 采用“别名=字段名称” 的格式。【例5-4】 显示student_info表中学生的姓名、家庭住址，并在标题栏种显示“姓名”和“家庭住址”字样，而不是显示student_name和address。SELECT student_name AS 姓名,address AS 家庭住址 FROM student_info第23页/共101页显示表达式的值 在SELECT语句后面可以是字段表达式，字段表达式不仅可以是算术表达式，还可以是字符串常量、函数等。【例5-5】 显示student_info表中所有学生姓名和年龄。 SELECT student_name, year(getdate()-year(born_date) as 年龄 FROM student_info第24页/共101页使用DISTINCT短语消除重复的记录 DISTINCT短语能够从结果表中去掉重复的记录。【例5-6】查询所有学生所属班级的班号。SELECT DISTINCT class_no FROM student_info第25页/共101页用WHERE子句查询特定的记录 SQL是一种集合处理语言，所以数据修改及数据检索语句会对表中的所有记录（行）起作用，除非使用WHERE子句来限定查询的范围。 注意WHERE子句必须紧跟在FROM子句之后。SELECT FROM WHERE 这里条件表达式可以是关系表达式、逻辑表达式、特殊表达式。1、关系表达式用关系运算符将两个表达式连接在一起的式子即为关系表达式，关系表达式的返回值为逻辑值（TRUE、 FALSE），关系表达式的格式为： 第26页/共101页WHERE子句中关系表达式常用的关系运算符如表5-1所示。注意： 在关系表达式字符型数据之间的比较是对字符的ASCII码值进行比较。所有字符都有一个ASCII码值与之对应。例如，字母“A”，字母“B” 字母“C”，它们的ASCII码值分别是65、66、67。 字符串的比较是从左向右依次进行。 在SQL Server 2000中，日期字符串可以按照“年月日”的格式书写。第27页/共101页2、逻辑表达式用逻辑运算符将两个表达式连接在一起的式子即为逻辑表达式，逻辑表达式的返回值为逻辑值（TRUE、 FALSE），逻辑表达式的格式为： 3、特殊表达式特殊表达式在比较运算中有一些特殊的用途，具体的格式在使用时给出。 第28页/共101页有关关系运算符的使用 【例5-7】列出性别为女的学生姓名、家庭住址。SELECT * FROM student_info WHERE born_date 1980-12-31SELECT student_name, address FROM student_info WHERE student_sex=女【例5-8】 列出1980年以后出生的学生的基本情况。第29页/共101页有关逻辑运算符的使用【例5-9】列出所有家住“武汉市”的男学生姓名、电话号码和家庭住址。SELECT student_name,tele_number,student_sex,address FROM student_info WHERE substring(address,1,3)=武汉市 AND student_sex=男【例5-10】列出200001班女同学的名单。SELECT * FROM student_info WHERE class_no=200001 AND student_sex=女第30页/共101页【例5-11】列出200001班或电话号码以87开头同学姓名、性别和电话号码。SELECT student_name,student_sex,tele_number FROM student_info WHERE class_no=200001 OR SUBSTRING(tele_number,1,2)=87【例5-12】列出所有年龄不是19岁的学生姓名、性别和出生日期。SELECT student_name,student_sex, born_date FROM student_info WHERE ( year(getdate()-year(born_date) !=23第31页/共101页有关特殊运算符的使用 【例5-13】列出年龄不在18-20岁之间的所有学生名单。本例主要学习有关“BETWEENAND”的使用格式，使用“BETWEENAND”的作用是定义表达式在两数之间。格式为：表达式 NOT BETWEEN 表达式1 AND 表达式2 SELECT * FROM student_info WHERE ( year(getdate()-year(born_date) BETWEEN 18 AND 20第32页/共101页【例5-14】列出所有注释内容为空的学生姓名。当需要判断一个表达式的值是否为空值时，可使用关键字“IS NULL”。本例主要学习有关“IS NULL”短语的书写格式。使用“IS NULL”短语的格式为：表达式 IS NOT NULL当使用NOT 关键字，表示不为空。 SELECT student_name, tele_number FROM student_info WHERE comment IS NULL第33页/共101页【例5-15】列出200001班和200002班学生的学号、姓名和性别。使用IN关键字可以指定一个值表，值表中指定列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回 TRUE,否则返回FALSE。使用IN关键字指定值表的格式为： 表达式 IN （表达式1，n）本例主要学习“IN 短语”的书写格式。SELECT student_id,student_name, student_sex FROM student_info WHERE class_no IN (200001,200002)“IN 短语”的作用类似于逻辑“或”，有时用IN比OR更方便。例如上述语句也可以写为：SELECT student_id,student_name, student_sex FROM student_info WHERE class_no =200001 OR class_no =200002第34页/共101页【例5-16】列出既不是200001班，也不是200002班学生的学号、姓名和性别。本例主要学习有关“NOT IN 短语”的书写格式。 SELECT student_id,student_name, student_sex FROM student_info WHERE class_no NOT IN (200001,200002)第35页/共101页【例5-17】列出所有“刘” 姓且为双名的学生。本例主要学习LIKE与通配符“_、%”的使用及模式匹配表达式书写格式。LIKE关键字用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、text、 datetime、 smalldatetime等类型的数据，返回逻辑值TRUE或FALSE。LIKE关键字表达式的格式为： 字符表达式1 NOT LIKE 字符表达式2 SELECT student_id,student_name FROM student_info WHERE student_name LIKE 刘_第36页/共101页【例5-18】列出所有有体育特长的学生的姓名。SELECT student_id,student_name FROM student_info WHERE comment LIKE %体育特长%第37页/共101页使用ORDER BY子句对查询结果排序在SELECT语句中，使用“ORDER BY”子句可以对查询结果进行升序或降序的排列。1、“ORDER BY”子句的基本格式基本格式：SELECT FROM WHERE ORDER BY ASC|DESC ，上述格式中，“子句表达式1”可以是一个列名、列的别名、表达式或非零的整数值，而非零的整数值则表示字段、别名或表达式在选择列表中的位置。ASC表示升序，为默认值；DESC表示降序，排序时空值（NULL）被认为是最小值。第38页/共101页“ORDER BY”子句应用实例【例5-21】将student_info表中的学生按出生时间先后顺序。 “ORDER BY”子句的输出结果仅仅是影响表的逻辑顺序（排序顺序），并没影响表的真正物理顺序（实际顺序）。SELECT student_id,student_name, born_dateFROM student_info ORDER BY born_date第39页/共101页这种多能排序叫做能联排序。只要在“ORDER BY”子句后面，按照顺序列出字段的清单，字段之间用逗号分隔，即可实现能联排序的功能。【例5-23】列出2000年元旦前入学的学生名单。要求查询结果按入学时间降序显示。本例主要学习“ORDER BY”子句和“WHERE”子句一起使用。SELECT student_id,student_name, ru_date FROM student_info WHERE ru_date 19 AND student_sex=女第43页/共101页MAX和MIN函数的应用MAX和MIN函数分别用于求表达式中所有值项的最大值与最小值，语句格式为： MAX / MIN （ALL|DISTINCT 表达式）其中表达式是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值、默认为ALL。忽略NULL值。【例5-26】查找年龄最大和最小的学生出生日期。 SELECT MIN (born_date) 年龄最大 , MAX (born_date) 年龄最小 FROM student_info第44页/共101页AVG函数的应用AVG函数的表达式格式与MAX和MIN的表达式格式一样。求表达式中所有项的平均值。其格式为： AVG（ALL|DISTINCT 表达式）其中表达式是常量、列、函数或表达式，其数据类型只能是int、smallint 、 tinyint 、 bigint 、 decimal、numeric、float、real 、money、smallmoney 。ALL表示对所有值进行运算，DISTINCT表示去除重复值、默认为ALL。AVG忽略NULL值第45页/共101页【例5-28】求出第一学期所开课程的平均课时和平均学分。 SELECT AVG (course_time) 平均课时, AVG (course_score) 平均学分 FROM course_info WHERE course_start =1第46页/共101页SUM函数的应用SUM函数用于求表达式中所有项的总和。其格式为： SUM（ALL|DISTINCT 表达式）其中表达式是常量、列、函数或表达式，其数据类型只能是int、smallint 、 tinyint 、 bigint 、 decimal、numeric、float、real 、money、smallmoney 。ALL表示对所有值进行运算， DISTINCT表示去除重复值、默认为ALL。 SUM 忽略NULL值第47页/共101页【例5-29】求出第一学期所开课程的总课时和总学分。 SELECT SUM (course_time) 总课程, SUM (course_score) 总学分 FROM course_info WHERE course_start =1第48页/共101页利用SQL 的GROUP BY子句，能够快速而简便地将查询结果表按照指定的字段进行分组，值相等的记录分为一组。GROUP BY子句一般和SQL的聚合函数一起使用。【基本格式】: SELECT FROM GROUP BY 使用GROUP BY对查询结果进行分组第49页/共101页【例5-30】统计各班学生的总人数。 本例主要学习有关GROUP BY子句的表达式书写格式。 SELECT class_no,COUNT( student_id) 学生人数 FROM student_info GROUP BY class_no第50页/共101页【例5-31】统计男、女学生各自的总人数和平均年龄。 SELECT COUNT(student_id) 总人数, AVG(year(getdate()-year(born_date) 平均年龄 FROM student_info GROUP BY student_sex第51页/共101页利用HAVING筛选结果表在实际使用中，往往还要对分组后的结果按某种条件再进行筛选，而只输出满足用户指定条件的记录。在SQL中，HAVING子句能完成此功能。“WHERE”子句与“HAVING”子句的区别在于作用对象不同：1、WHERE子句的作用对象是表，是从表中选择出满足筛选条件的记录。2、HAVING子句的作用对象是组，是从组中选 择出满足筛选条件的记录。第52页/共101页【例5-32】将总人数大于2的班级学生总人数显示出来。 本例主要学习有关GROUP BY子句的表达式书写格式。 SELECT class_no 班级名, COUNT(student_id) 总人数 FROM student_info GROUP BY class_no HAVING COUNT(student_id)2第53页/共101页5.4 基于多表的联接查询 前面的查询只涉及一个表。在多数情况下，一个SQL查询语句一次往往牵扯到多个表。 在关系型数据库中，将一个查询同时涉及两个或两个以上的表，称为连接查询。 基于多表的查询是通过所谓的连接查询来完成的。 例如在student学生数据库中需要查找选修了汇编语言课程的学生的姓名和成绩，就需要将学生信息表student_info、课程信息表course_info和成绩信息表result_info三个表进行连接，才能得到查询结果。 第54页/共101页在T_SQL语言中，连接查询有两大类表示形式，一是符合SQL标准连接谓词表示形式，一是T_SQL扩展的使用关键字JOIN的表示形式。连接条件可通过以下方法定义两个表在查询中的关联方式： 指定每个表中要用于连接的列。典型的连接条件在一个表中指定外键，在另一个表中指定与其关联的键。 指定比较各列的值时要使用的逻辑运算符，如“=、”等。第55页/共101页5.4.1 连接谓词 可以在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，对这种表示形式称为连接谓词表示形式。其基本格式为： 连接谓词中的两个列（即字段）称为连接字段，他们必须是可比的，连接谓词中的比较符可以是、=、!=、等。当比较符为“=”时，就是等值连接；若在等值连接中去除结果表中相同的字段名，则为自然连接；若有多个连接条件，则为复合条件连接。若一个表与自身进行连接，称为自连接。 第56页/共101页【例5-33】采用等值连接的方法，列出每个学生及其班级的详细情况。 本例主要学习有关等值连接的表达式书写格式。 SELECT student_info.*,class_info.* FROM student_info, class_info WHERE student_info.class_no= class_info.class_no第57页/共101页【例5-34】采用自然连接的方法，列出每个学生及其班级的详细情况。 在上例中，“class_no”学号字段有两个。如果去掉其中一个“class_no”字段，则成为了自然连接。在自然连接的条件表达式中，往往是将各表的主键和外键进行等值连接。本例主要学习有关自然连接的表达式书写格式。 SELECT student_id, student_name,student_sex,born_date, student_info.class_no, tele_number,ru_date,address, comment,class_name,director,profession FROM student_info, class_info WHERE student_info.class_no= class_info.class_no第58页/共101页 注意：当单个查询引用多个表时，所有列引用都必须明确。在查询所引用的两个或多个表之间，任何重复的列名都必须用表名限定。如果某个列名在查询用到的两个或多个表中不重复，则对这一列的引用不必用表名限定。但是，如果所有的列都用表名限定，则能提高查询的可读性。如果使用表的别名，则会进一步提高可读性，特别是在表名自身必须由数据库和所有者名称、限定时。第59页/共101页【例5-35】采用自身连接的方法，查找不同课程成绩相同的学生的学号、课程号和成绩。 若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。本例主要学习有关自身连接的表达式书写格式。 SELECT a.student_id,a.course_no,b.course_no,a.result FROM result_info a,result_info b WHERE a.result=b.result AND a.student_id=b.student_id AND a.course_nob.course_no第60页/共101页【例5-36】采用复合条件连接方法，查找选修了“汇编语言”课程且成绩在70分以上的学生学号、姓名、课程名及成绩。本例主要学习有关复合条件连接的表达式书写格式。在“WHERE”子句中，若有多个连接条件，则称为复合条件连接。在多表操作中，复合条件连接的使用最为广泛。SELECT student_info.student_id,student_name, course_info.course_name,result_info.resultFROM student_info,course_info,result_infoWHERE student_info.student_id= result_info.student_id AND result_info.course_no=course_info.course_no AND course_info.course_name=汇编语言 AND result_info.result70第61页/共101页5.4.2 以JOIN关键字指定的连接T-SQL 扩展了以JOIN关键字连接的表示方式，使表的连接运算能力有了增强，可以将多个表连接起来，FOR关键字后面的连接格式为： ON | CROSS JOIN 其中first_table 、 second_table为需连接的表，join_type表示连接类型，ON用于指定连接条件Join_type的格式为：INNER| LEFT | RIGHT | FULL OUTER JOIN 其中INNER表示内连接，OUTER表示外连接， join_hint是连接提示。 第62页/共101页CROSS JOIN表示交叉连接。因此，以JOIN关键字指定的连接有三种类型：1、 内连接。内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。【例5-37】查找student数据库每个学生的基本情况以及班级情况。SELECT * FROM student_info INNER JOIN class_infoON student_info.class_no= class_info.class_no第63页/共101页结果表将包含student_info表和class_info表的所有字段（重复字段为班级编号class_no），若要去除重复的班级编号，可将语句改为： SELECT student_info.*,class_name,director,profession FROM student_info INNER JOIN class_info ON student_info.class_no= class_info.class_no内连接是系统默认的，可以省略INNER关键字，使用内连接后仍可使用WHERE子句指定条件。第64页/共101页【例5-38】查找选修了课程编号为“2”的课程且成绩在60分以上的学生姓名和成绩。SELECT student_name,result FROM student_info JOIN result_info ON student_info.student_id= result_info.student_id WHERE result_info.course_no=2 AND result_info.result60第65页/共101页【例5-39】查找选修了“汇编语言”课程且成绩在70分以上的学生学号、姓名、课程名及成绩。 SELECT student_info.student_id,student_name, course_info.course_name,result_info.result FROM student_info JOIN result_info ON student_info.student_id= result_info.student_id JOIN course_info ON result_info.course_no=course_info.course_no WHERE course_info.course_name=汇编语言 AND result_info.result70第66页/共101页【例5-40】查找不同课程成绩相同的学生的学号、课程号和成绩。 SELECT a.student_id,a.course_no,b.course_no,a.result FROM result_info a JOIN result_info b ON a.result=b.result AND a.student_id=b.student_id AND a.course_nob.course_no第67页/共101页外联接 外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外联接包括三种： 左向外联接（LEFT OUTER JOIN）或（LEFT JOIN） 结果表中除了满足连接条件的行，还包括左表中的所有行。包括第一个命名表(“左”表，出现在JOIN子句的最左边)中的所有行。不包括右表中的不匹配行。 右向外联接 (RIGHT OUTER JOIN) 或（RIGHT JOIN） 结果表中除了满足连接条件的行，还包括右表中的所有行。包括第二个命名表(“右”表，出现在JOIN子句的最右边)中的所有行。不包括左表中的不匹配行。 完整外部联接 （FULL OUTER JOIN）或（FULL JOIN）结果表中除了满足连接条件的行，还包括两表中的所有行。若要通过在联接结果中包括不匹配的行保留不匹配信息，可以使用完整外部联接。SQL Server 2000提供完整外部联接运算符FULL OUTER JOIN，不管另一 个表是否有匹配的值，此运算符都包括两个表中的所有行。第68页/共101页【例5-41】采用左向外联接。查找200001班所有学生的学号、姓名及他们选修课程号，同时也列出该班没选课的学生的学号、姓名。 SELECT student_info.student_id, student_name,result_info.course_no FROM student_info LEFT OUTER JOIN result_info ON student_info.student_id= result_info.student_idWHERE class_no=200001第69页/共101页【例5-42】采用右向外联接。查找所有学生的学号、姓名及他们选修课程号，同时也列出没选课的学生的学号、姓名。将上面的示例中，将FROM子句中student_info表 和 result_info表交换一下位置，然后使用RIGHT OUTER JOIN：SELECT student_info.student_id, student_name, result_info.course_noFROM result_info RIGHT OUTER JOIN student_infoON student_info.student_id= result_info.student_id第70页/共101页交叉联结在这类联接的结果表内，是由第一个表的每行与第二个表的每行拼接后形成的表。交叉联接不使用WHERE子句。在数学上，就是表的笛卡儿积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。【例5-43】 列出学生所有可能的选课情况。SELECT student_id,student_name,course_no,course_name FROM student_info CROSS JOIN course_info第71页/共101页5.5子查询子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。 任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。 子查询能够将比较复杂的查询分解为几个简单的查询，而且子查询可以嵌套，嵌套查询的过程是：首先执行内部查询，它查询出来的数据并不被显示出来，而是传递给外层语句，并作为外层语句的查询条件来使用。第72页/共101页【例5-44】使用子查询来查询学生“张小强”所在的班级。 SELECT class_name FROM class_info WHERE class_no = (SELECT class_no FROM student_info WHERE student_name=张小强)第73页/共101页使用下面的连接方式也能完成此功能：SELECT class_name FROM class_info JOIN student_info ON (class_info.class_no = student_info.class_no) WHERE student_name=张小强第74页/共101页 连接和子查询可能都要涉及两个或多个表，要注意连接与子查询的区别：连接可以合并两个或多个表中数据，而带子查询的SELECT语句的结果只能来自一个表，子查询的结果是用来作为选择结果数据时进行参照的。 有的查询既可以使用子查询来表达，也可以使用连接表达，例如上面的例子。通常使用子查询表示时可以将一个复杂的查询分解为一系列的逻辑步骤，条理清晰，而使用连接表示有执行速度快的优点。具体使用哪一种要根据具体情况而定。第75页/共101页可以在许多地方指定子查询。例如： 使用别名时。 使用IN或NOT IN时。 在UPDATE、DELETE和INSERT语句中。 使用比较运算符时。 使用ANY、SOME或ALL时。 使用EXISTS或NOT EXISTS时。 在有表达式的地方。第76页/共101页使用IN或NOT IN的子查询IN 子查询用于进行一个给定值是否在子查询结果集中的判断，格式为： 表达式 NOT IN (子查询)当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE，若使用了NOT，则返回的值刚好相反。【例5-45】查找选修了课程号为13的课程的学生情况。 SELECT * FROM student_info WHERE student_id IN (SELECT student_id FROM result_info WHERE course_no=13)第77页/共101页【例5-46】查找200001班没选修课程号为13的课程的学生情况。 SELECT * FROM student_info WHERE class_no=200001 AND student_id NOT IN (SELECT student_id FROM result_info WHERE course_no=13)第78页/共101页【例5-47】 使用多层嵌套子查询来查找选修了“数据库原理”课程的学生名单。SELECT student_name FROM student_info WHERE student_id IN (SELECT student_id FROM result_info WHERE course_no= (SELECT course_no FROM course_info WHERE course_name=数据库原理) )第79页/共101页【例5-48】查找没选修“数据库原理”课程的学生情况。SELECT * FROM student_info WHERE student_id NOT IN (SELECT student_id FROM result_info WHERE course_no IN (SELECT course_no FROM course_info WHERE course_name=数据库原理)第80页/共101页INSERT、DELETE和UPDATE语句中的子查询子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。1、插入子查询结果前面介绍的插入操作，一次只能插入一条记录，其实，INSERT语句和SELECT语句结合起来，就可以插入批量的记录了。基本格式：INSERT INTO （ ， ）SELECT （ ， ）FROM WHERE 第81页/共101页【例5-49 】创建一个新学生表stu，要求：包含4个字段 “学号”、“姓名”、“性别”和“家庭住址”，然后将学生信息表中相应字段信息全部插入到新学生表中，最后显示插入的记录。CREATE TABLE stu(学号 int NOT NULL PRIMARY KEY, 姓名 char(10),性别 char(2),家庭住址 char(50)goINSERT INTO stu (学号,姓名,性别,家庭住址)SELECT student_id,student_name,student_sex,address FROM student_infogoSELECT * FROM stuGo第82页/共101页需要注意的问题是： 新学生表stu的字段名可以与学生信息表不同。例如，stu的第1个字段名是“学号”，而不是原来的student_id。 新学生表的列数也不一定与原表的相同。但是要求对应列的类型必须保持一致因为不能把字符型拷贝成数值型。第83页/共101页2、带子查询的修改语句与INSERT相同，在SQL中，子查询也可以嵌套在UPDATE语句中，用来指定修改的条件。【例5-50】将200001班的全体学生的成绩加10分。UPDATE result_info SET result=result+10 WHERE student_id IN (SELECT student_id FROM student_info WHERE class_no=200001)第84页/共101页3、带子查询的删除语句在SQL 中，同样子查询也可以嵌套在【DELETE】语句中，用来指定删除的条件。【例5-51】删除没选修任何一门课的学生信息。 DELETE student_info WHERE student_id IN (SELECT DISTINCT student_id FROM result_info)第85页/共101页使用比较运算符的子查询 子查询可由一个比较运算符(=、=、，!或=)引入。这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为： 表达式 | | = | != | | ! ALL | ANY (子查询) 其中ALL和ANY说明对比较运算符的限制。 ALL指定表达式要与子查询的结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE。第86页/共101页 ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE。 例如，ALL(1,2,3)表示表达式的值要大于3，才返回TRUE。ANY(1,2,3)表示表达式至少大于一个值，也就是表达式只要大于1或2或3，就返回TRUE。 因此，要使带有ALL的子查询中的某行满足外部查询中指定的条件，引入子查询的列中的值必须大于由子查询返回的值的列表中每个值。同样，ANY表示要使某一行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于由子查询返回的值的列表中的一个值。第87页/共101页【例5-52】查找比所有200002班的学生年龄都大的学生。SELECT student_id, student_name,class_no FROM student_info WHERE born_date ALL (SELECT born_date FROM student_info WHERE class_no=200002) AND class_no200002第88页/共101页【例5-53】查找比200002班某个学生年龄大的学生。SELECT student_id, student_name,class_no FROM student_info WHERE born_date ANY (SELECT born_date FROM student_info WHERE class_no=200002) AND class_no200002第89页/共101页【例5-54】查找课程号为13且成绩不低于课程号为14的最低成绩的学生的学号。SELECT student_id FROM result_info WHERE course_no=13 AND result !ANY (SELECT result FROM result_info WHERE course_no=14)第90页/共101页【例5-55】查找课程号为13且成绩不低于课程号为14的最低成绩的学生的学号。SELECT student_id FROM result_info WHERE course_no=13 AND result !ANY (SELECT result FROM result_info WHERE course_no=14)第91页/共101页使用EXISTS的子查询使用EXISTS用于测试子查询的结果是否为空表。若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXISTS刚好相反。格式为： NOT EXISTS (子查询)【例5-56】查找选修了13号课程的学生名单。 SELECT student_name FROM student_info WHERE EXISTS (SELECT * FROM result_info WHERE course_no=13 AND student_id=student_info.student_id)第92页/共101页【例5-57】查找选修了全部课程的学生名单。SELECT student_name FROM student_info WHERE NOT EXISTS (SELECT * FROM course_info WHERE NOT EXISTS (SELECT * FROM result_info WHERE student_id=student_info.student_id AND course_no=course_info.course_no) )第93页/共101页相关子查询 如果子查询的WHERE子句引用外部查询表，则该查询称为相关子查询，也称为重复查询。例如，在【例5-56】查找选修了13号课程的学生名单的例题即为相关子查询。该例在子查询的条件中使用了限定形式的列名引用“student_info.student_id”，表示这里的学号列出自表student_info,与前面的子查询不同的是：前面的子查询内层查询只处理一次，得到一个结果集，再依次处理外层查询；而该例的内层查询要处理多次，因为内层查询与“student_info.student_id”有关，外层查询中student_info表的不同行有不同的学号值。对于相关子查询，子查询的条件依赖于外层查询中的某些值，其执行过程如下： (1)子查询为外部查询的每一行执行一次，即外部查询将相关的列值传给内部查询。 (2)如果子查询的任何行与其匹配，外部查询就返回结果行。 (3)再回到第一步，直到处理完外部表的所有行。第94页/共101页【例5-58】查找“计算机应用001班”的学生名单。SELECT student_name FROM student_info WHERE 计算机应用001班 IN (SELECT class_name FROM class_info WHERE class_no=student_info.class_no)第95页/共101页使用UNION运算符组合多个结果使用UNION运算符可以将两个或多个SELECT语句的结果组合成个结果集。使用UNION组合的结果集都必须具有相同的结构。而且它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。【例5-59】显示“200001班”以及“200002班”的学生编号和学生姓名。SELECT student_id,student_name FROM student_info WHERE class_no=200001 UNIONSELECT student_id,student_name FROM student_info WHERE class_no=200002第96页/共101页在查询的基础上创建新表 在SQL Server中，提供了INTO子句，使用INTO关键字可以创建新表并将结果行从查询插入新表中，新表可以是一个永久或临时表。基本格式：SELECT INTO FROM WHERE说明： 这是一种常用的创建临时表的方式，临时表以在表名前加（#或#）表示。 新创建表的结构由SELECT列表本身定义，新表的列顺序 与SELECT列表中的顺序也相同，列名及数据类型也由 SELECT列表确定。第97页/共101页【例5-60】将查询得到的学生名和班级名，插入到新建的表student_class中。SELECT student_name,class_name INTO student_class FROM student_info,class_info WHERE student_info.class_no=class_info.class_no第98页/共101页【例5-61】创建“200001班”的学生表“student01”。SELECT * INTO student01 FROM student_info WHERE class_no=200001第99页/共101页第100页/共101页感谢您的观看！第101页/共101页