第6单元 数据查询ppt课件.ppt
第6单元 数据查询数据库原理及应用内蒙古师范大学青年政治学院第6单元 数据查询 本单元主要目标：（1）掌握数据查询和数据更新的语法格式。（2）能运用所学知识对数据进行相关操作。（3）能根据测试结果写出检验报告。数据库原理及应用内蒙古师范大学青年政治学院6.1 SELECT语句的语法格式 对SQL Server 数据库进行查询主要使用SELECT语句，SELECT语句从数据库中检索出来的数据，以一条或多条记录集的形式返回给用户，其完整的语法格式为：SELECT DISTINCT | ALL select_listINTO new_tableFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 数据库原理及应用内蒙古师范大学青年政治学院参数说明：（1）Select_list：查询列表，指明要查询的字段名。各个字段名用逗号分开。（2）INTO new_table：指定用查询得到的记录集来创建一个新表，new_table为新表名。（3）FROM table_source ：指出所查询各表的表名以及将各表之间的逻辑关系。（4）WHERE search_condition：指明查询条件，定义由引用表向结果集中返回数据所满足的要求。（5）GROUP BY group_by_expression：根据group_by_expression参数指定的字段对结果集进行分组。（6）HAVING search_condition：根据search_condition所指定的条件，对已经得到的结果集进行附加筛选。（7）ORDER BY order_expression ASC | DESC ：定义查询得到的结果集的排列顺序。数据库原理及应用内蒙古师范大学青年政治学院6.2 单 表 查 询 SELECT语句不但可以完成简单的单表查询，也可以完成复杂的多表查询。本小节我们主要介绍单表的查询操作。1、基本的SELECT语句SELECT语句最基本的语法格式。SELECT * FROM table_source参数说明：l SELECT 关键字后的“*”，代表查询引用表中的所有字段的内容。也可以指定要查询的字段名列表。l FROM关键字后的table_source，指定要查询的数据表。l 所有SELECT语句必须有SELECT子句和FROM子句组成，书写时可以将两个子句写在一行中。数据库原理及应用内蒙古师范大学青年政治学院 （1）查询所有字段 选择查询表中所有字段有两种方法：一是在SELECT语句后面的关键字用“*”，二是在SELECT后面列出所有要查询的字段。【例6.1】查询student表中所有记录。在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT * FROM student （2）查询指定字段 一般情况下，一个数据表包含了一个实体的所有内容，而用户在查询过程中，只希望得到一部分内容，对其他内容不感兴趣，此时，用户在查询过程中可以指定要显示的字段名，各字段名用逗号隔开。数据库原理及应用内蒙古师范大学青年政治学院【例6.2】显示student表中学生姓名，性别和所在系别。 在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT sname,ssex,sdepartment FROM student（3）设置字段别名 在显示查询的结果集时，有时候为了方便用户理解，常常为字段取一个别名来代替原来的字段名。【例6.3】为例题6.2中显示的三个字段设置别名。USE studentdbGOSELECT sname AS 姓名,ssex AS 性别,sdepartment AS 所在系别 FROM student数据库原理及应用内蒙古师范大学青年政治学院（4）限制返回行数 如果用SELECT语句查询得到结果集的行数太多，用户可以使用TOP n选项返回记录集中的前n条记录。【例6.4】返回例题6.3结果集中的前5条记录。 在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT TOP 5 sname AS 姓名,ssex AS 性别,sdepartment AS 所在系别 FROM student（5）消除重复的记录行在用SELECT选择语句查询时，使用DISTINCT关键字可以将重复记录只显示第一次出现的记录，而后面重复的记录不再显示。数据库原理及应用内蒙古师范大学青年政治学院（6）查询结果集中包括导出列 查询列表中可包含表达式，这样在结果集中就包含了源表（或视图）中并不存在、而是通过计算得到的列，即导出列。导出列可通过函数、运算符、数据类型转换或者子查询等获得。【例6.7】使用运算符导出列。USE studentdbSELECT sno,cno,score,score+20,round(score*0.7),2)FROM choice（7）显示常数列 对表（或视图）进行查询返回的结果集中还可以指定某些列显示为常数，以增加可读性。数据库原理及应用内蒙古师范大学青年政治学院2、条件查询（1）比较 比较查询是由比较运算符和相应的表达式构成查询条件，查询结果由查询条件的真假来决定，需要注意的是text、ntext和image类型的数据是不能作为比较查询的条件。【例6.9】查询choice表中成绩大于80分的记录。在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT * FROM choice WHERE score80数据库原理及应用内蒙古师范大学青年政治学院（2）逻辑运算 WHERE子句中可以利用逻辑运算符（AND、OR和NOT）连接查询条件。NOT用于对搜索条件取相反的返回结果；AND用于两个条件表达式的“与”连接，即当这两个条件表达式都成立（逻辑表达式的值为真）时，返回结果才成立；OR用于两个条件表达式的“或”连接，即当这两个条件表达式中有一个成立（逻辑表达式的值为真），返回结果就成立。【例6.9】查询choice表中成绩大于90或小于60，并且课程号等于2001的记录。 USE studentdbSELECT sno,cno,scoreFROM choiceWHERE (score90 or score60) and cno=2001数据库原理及应用内蒙古师范大学青年政治学院（3）确定范围 当需要返回某一字段的值介于两个指定值之间的记录，就可以使用范围查询条件BETWEENEND来实现。需要注意的是用BETWEENEND作为范围查询的时候是包含上下边界值的。【例6.9】查询choice表中成绩介于80到90分的记录。USE studentdbGOSELECT * FROM choice WHERE score BETWEEN 80 AND 90 另外还可以在BETWEENAND前面加一个逻辑否NOT，表示返回界定范围以外（不包含上下界）的所有值。数据库原理及应用内蒙古师范大学青年政治学院（4）模式匹配 在WHERE子句中使用LIKE关键字可以查询并返回于指定字符串表达式匹配的数据行，还可以使用通配符进行模糊匹配。需要注意的是LIKE关键字后面的表达式必须用单引号（）括起来。SQL Server 2012提供的通配符有：l %：代表任意长度的字符串。例如：a%b表示以a开头，以b结尾的任意长度的字符串。l _：代表任意单个字符。例如：a_b表示以a开头，以b结尾的长度为3的任意字符串。l ：用于指定一个范围。例如：a-g表示从a到g范围内的任意单个字符。l ：用于指定一个范围。例如：ag表示从a到g范围以外的任何单个字符。数据库原理及应用内蒙古师范大学青年政治学院【例6.14】查询student表中所有姓李的学生信息。 在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT * FROM student WHERE sname LIKE 李%【例6.15】查询student表中不是汉文系的学生信息。USE studentdbGOSELECT * FROM student WHERE sdepartment NOT LIKE 汉文数据库原理及应用内蒙古师范大学青年政治学院（5）确定集合 当检索的集合如果是一组分散的值，可以使用IN关键字构成列表。IN关键字允许用户选择与确定集合中的值相匹配的行，指定的集合必须用圆括号（）括起来，集合中的各项用逗号隔开。【例6.16】查询student表中专业为计算机和外语的学生信息。 在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT * FROM student WHERE sdepartment IN (计算机,外语)数据库原理及应用内蒙古师范大学青年政治学院（6）空值判断 在SQL Server系统中，空值（NULL）并不代表空格或0，而是表示数据的值未知或不可用。所有的空值都是相等的，它与任何数据进行比较运算或算术运算的结果都是NULL。 空值无法用上述几种方法判断，只能用空值判断符ISNOT NULL来判断表达式的值是否为空。【例6.18】查询显示teacher表中没有指定职称的教师信息。在SQL Server Management Studio“查询”窗口中输入下面的语句：USE studentdbGOSELECT * FROM teacherWHERE tduty is NULL数据库原理及应用内蒙古师范大学青年政治学院3、 排序 为了用户方便查看程序的查询结果，有时候需要对结果集进行排序操作。ORDER BY子句是根据查询结果中的一个字段或多个字段对查询结果集内容进行排序。其语法格式如下： ORDER BY order_expression ASC | DESC 其中，order_expression用于指定排序的字段，它可以是字段名、别名或表达式。ASC 和 DESC指定排序的方向，ASC指定字段值按升序排列，DESC指定字段值按降序排列，默认为升序排列。【例6.19】将choice表中信息按学生成绩降序排列。USE studentdbGOSELECT * FROM choiceORDER BY score DESC数据库原理及应用内蒙古师范大学青年政治学院6.3 数 据 统 计1、聚合函数 聚合函数又称字段函数，聚合函数的作用是在查询结果集中产生和、平均值、记录数目、最大值和最小值等。（1）SUM函数（2）AVG函数（3）COUNT函数（4）MAX和MIN函数2、GROUP BY子句的应用 GROUP BY子句的作用是将记录一句设置的条件分成不同的组。在前面的例题中我们发现，只有使用了GROUP BY子句，SELECT语句中的聚合函数才能发挥作用。数据库原理及应用内蒙古师范大学青年政治学院（1）基本GROUP BY子句的使用 GROUP BY子句的语法格式如下： GROUP BY ALL 分组表达式,N 分组表达式是进行分组时所执行的表达式，当含有多个表达式时，表达式列表决定了查询结果集分组的依据和顺序。需要注意的是，在字段列表中指定的别名是不可以作为分组表达式来使用的。【例6.26】显示choice表中学号小于1005的学生的总成绩。USE student dbSELECT sno,SUM(score)AS总成绩FROM choiceWHERE SN01005GR0UP BY ALL sno数据库原理及应用内蒙古师范大学青年政治学院（2）HAVING子句的使用 在使用GROUP BY子句时，还可以使用HAVING子句为分组统计结果进一步设置筛选条件。HAVING 子句用于制定一组或一个集合的搜索条件，如果含有多个筛选条件可以通过逻辑运算符连接起来。其语法格式为： HAVING 搜索条件【例6.27】在例题6.26的基础上，筛选出总成绩大于140的记录。在SQL Server Management Studio 查询窗口中输入下面的语句：USE studentdbSELECT sno,SUM(score) AS 总成绩FROM choiceWHERE SNO140数据库原理及应用内蒙古师范大学青年政治学院6.4 多 表 查 询 相对于前面讲过的单表查询来说，本节介绍的多表查询可以把一个数据库中相互关联的表连接起来，从而获得更多的信息。连接可以实现从两个或多个表中查询数据，连接条件是通过指定每个表中要用于连接的列值及指定比较各列时使用的运算符和表达式来定义。用户可以通过连接用一个表中的数据查询其他表的数据，也可以把多个表中的数据一起显示，增加了查询的灵活性。 SELECT语句中的连接既可以在FROM子句定义，也可以在WHERE子句中定义。但为了与WHERE子句中的检索条件区分，建议在FROM子句中定义。数据库原理及应用内蒙古师范大学青年政治学院1在FROM子句中定义连接其语法格式如下：FROM,n其中：=表名AS表别名WITH(,n)|视图名AS别名|INNER|LEFT|RIGHT|FULLOUTERJOINON|CROSS JOIN【例6.28】查询所有学生的选课成绩及格的成绩情况，显示学号、课程名和课程成绩。USE studentdbSELECT sno,cname,scoreFROM course join choice on o=oWHERE score60数据库原理及应用内蒙古师范大学青年政治学院2在WHERE子句中定义连接 其语法格式如下： FROM 表1，表2 WHERE 其中： （1）：=表1.列名表2.列名 （2）连接符有：=（内连接）、=*（右外连接）、*=（左外连接）等【例6.29】使用WHERE子句定义连接完成例6.28的查询。USE studentdbSELECT sno,cname,scoreFROM course,choice WHERE o=o and score60数据库原理及应用内蒙古师范大学青年政治学院3交叉连接 其语法格式如下： FROM 表1，表2 WHERE 其中： （1）：=表1.列名表2.列名 （2）连接符有：=（内连接）、=*（右外连接）、*=（左外连接）等【例6.29】使用WHERE子句定义连接完成例6.28的查询。USE studentdbSELECT sno,cname,scoreFROM course,choice WHERE o=o and score60数据库原理及应用内蒙古师范大学青年政治学院4内连接 内连接是用比较运算符比较表中的列值，返回符号连接条件的数据行，使两个表连接成一个数据集。在数据集中没有不满足连接条件的数据行。（1）等值连接（2）不等值连接5、外连接 在外连接的两个表中，我们把两个表分别称作主表和从表。连接时，主表的每一行数据去匹配从表，如果从表的数据行满足与主表中该行的连接条件，则将相关数据返回数据集中；如果从表的所有数据行都不满足与主表某行的连接条件，主表该行的数据仍会在数据集中，但在数据集中该行涉及的从表列的数据用NULL填充。 我们通常把外连接分为以下三种：左外连接、右外连接和全外连接数据库原理及应用内蒙古师范大学青年政治学院6自连接 自连接就是一个表同其自身进行内连接，用这样的方式来完成某些特殊的查询。自连接是将一个表看做是两张完全相同的表进行连接查询。【例6.34】查询“李敏”所在院系所有学生的信息。USE studentdbSELECT st1.*FROM student as st1,student as st2WHERE st1.sdepartment=st2.sdepartment and st2.sname=李敏 注意：在使用自连接时因为是将一个表看成不同的两个表进行连接，所以需要对表设定别名。7、合并结果集 使用UNION关键字可以将多个查询结果合并到一个数据集中。通常这些查询分别是针对不同的表进行的，因此使用UNION关键字可以实现多表查询。数据库原理及应用内蒙古师范大学青年政治学院 使用UNION对表查询不是将各个表的相关列并排显示出来，而是各表中的查询数据行顺序排列在同一个数据集中。要求对合并的表分别选择相同数目的列，对应顺序列的数据类型也必须一致或者可以相互兼容。【例6.35】将student表和teacher表的信息合并在一个数据集中显示。第一列显示学号或者职工号，第二列显示学生姓名或者教师姓名，第三列显示学生所在系或者教师职称。其程序如下：USE studentdbSELECT sno,sname,sdepartmentFROM studentUNIONSELECT tno,tname,tdutyFROM teacher数据库原理及应用内蒙古师范大学青年政治学院6.5 嵌 套 查 询 在一个查询语句中包含另外一个或者多个查询语句称为嵌套查询。其中，外层的查询语句为主查询语句，内层的查询语句为子查询语句。嵌套查询的执行过程是：首先执行子查询语句，将得到的查询结果集传递到外层查询中，作为外层查询语句的查询项或查询条件使用。1、带比较运算符的子查询 如果子查询的SELECT子句中只有一项，并且根据检索限定条件只有一个值相匹配。那么，子查询将只返回一个单值。这种情况我们可以直接使用比较运算符进行匹配。2、带有IN关键字的子查询 如果子查询的SELECT子句中只有一项，但可能返回多个值，这时就不能使用比较运算符来进行匹配筛选，我们可以使用IN或NOT IN关键字来进行匹配。数据库原理及应用内蒙古师范大学青年政治学院3、带有ANY或ALL关键字的子查询 另外，我们还可以使用ANY关键字引入子查询列表。“=ANY”可以代替IN关键字来使用，除了“=”还可以使用其他比较运算符与ANY组合。比较运算符和ANY的组合功能更加强大，使用更加灵活。【例6.40】创建与choice表结构相同的表choice1表，并输入如图6-43所示数据。分析以下程序的结果。USE studentdbSELECT sno,cno,scoreFROM choice1WHERE scoreANY(SELECT score FROM choice WHERE score60) 说明：关键字ANY也可以用SOME替代，与ANY含义相同。数据库原理及应用内蒙古师范大学青年政治学院4、带EXISTS关键字的子查询 如果子查询的结果集是一个多行多列的表，那么需要使用关键字EXISTS或NOT EXISTS引入该子查询的结果集。EXISTS或NOT EXISTS用于测试是否存在满足或不满足子查询条件的数据行，如果子查询至少返回一行数据记录，则EXISTS成立，NOT EXISTS不成立；反之，如果子查询没有一行数据记录返回，则NOT EXISTS成立，EXISTS不成立。 其语法格式为： WHERE NOT EXISTS（子查询）5、UPDATE、DELETE和INSERT语句中的子查询 我们还可以利用UPDATE、DELETE和INSERT语句使用查询改变数据表中的数据。数据库原理及应用内蒙古师范大学青年政治学院 6.6 使用INTO子句创建表 SELECT语句中的INTO子句可以创建一个新表，然后将查询返回的结果集插入到新表中。 其语法格式如下： INTO new_table name INTO子句创建的新表中，表的结构由查询语句中的查询项决定。在新表中列的名称取决于查询语句中的查询列表。如果某列加了别名，那么新表中该列的名称就是该别名，否则与原列名相同。需要注意的是，因为COMPUTE语句产生单独的行，所以INTO子句不能与COMPUTE子句一起使用。数据库原理及应用内蒙古师范大学青年政治学院【6.46】查询score大于平均分学生的姓名、性别、选修课程编号和成绩。并将查询结果集组成一个名为table1的新数据表，表中各字段的名称分别为上面所述的名称。USE studentdbSELECT s.sname as 姓名,s.ssex as 性别,o as 选修课程号,c.score as 成绩INTO table1FROM student as s,choice as cWHERE s.sno=c.sno and c.score(SELECT AVG(score) FROM choice)
