第04章：基本查询

1、第4章基本查询本章内容本章内容1查询中列的操作2排序3条件查询、模糊查询4BETWEEN 和 IN5聚合函数、分组查询6HAVING讲解时间：20分钟实践时间：30分钟查询结果中列的操作：内容预览查询结果中列的操作：内容预览 查询全部列、部分列、列别名查询全部列、部分列、列别名 结果去重结果去重理论 查询全部列、部分列、列别名查询全部列、部分列、列别名 结果去重结果去重实践SELECT语句lSELECT语句具有强大的查询功能。l是SQL语言中使用频率最高的语句，是SQL语言的灵魂。lSELECT允许从一个表或多个表中选择满足给定条件的一个或多个行或列。SQL概述SELECT FROM WHER

2、E GROUP BY COLUMN1, COLUMN2, COLUMN3. | HAVING ORDER BY ASC 或 DESCSELECT子句和FROM子句是必须的。对查询结果进行排序。ASC和DESC分别表示升序和降序排列，默认为升序。SELECT语法选择列表的含义选择列表在SELECT关键字之后，用于指定需要在查询返回的结果集中所包含的字段（列）。选择列表所有列含义和表示方法 当查询结果集需要返回表中全部列时，可使用“*”代替全部列名。查询所有列SELECT * FROM Employee查询所有员工的全部信息。使用部分列的原因及其使用方法 只需检索表的部分信息。 部分列查询时，各列

3、之间用“,”隔开。查询部分列SELECT RankName FROM RankSELECT RankID, RankName FROM Rank使用的原因及其表示方法 在SELECT查询返回的结果中，表头默认显示数据表的字段名，有时对用户而言不直观。也称为。 方法：“AS 标题名”或者“空格” + “标题名”列标题别名列标题的表示方法 使用AS关键字显示列标题列标题别名SELECT RankID AS 职级编号, RankName 职级名 FROM RankSELECT 字段名1 AS 列标题1，字段名2 别标题2. FROM 表名使用场景 SELECT语句的选择列不仅可以是表中的列，还可以是

4、基于列的表达式。 有商品销售表，其中有两个列为商品单价和售出数量，如果用户查询的商品销售信息中包含：商品单价、售出数量以及该商品售出总价，则选择列中的“商品售出总价”可以使用表达式，表达式表达式为：商品单价*售出数量。选择列作为表达式查询所有员工的姓名、月基本工资以及增长10%之后的月基本工资。选择列作为表达式SELECT Name 姓名, Salary 月基本工资, Salary*1.1 加薪后月基本工资 FROM Employee列为表达式过滤重复行的原因及其使用方法 使用DISTINCT过滤重复行。 SELECT DISTINCT 字段名 FROM 表名使用DISTINCT过滤重复行DI

5、STINCT如何只显示全部员工 不同 的职级号？过滤重复行的原因及其使用方法 使用DISTINCT过滤重复行。 SELECT DISTINCT 字段名 FROM 表名DISTINCTSELECT DISTINCT RankID FROM Employee过滤掉重复的RankIDl 练习巩固上述查询语法l 计算所有员工的年薪（一年14薪）l (KTV数据表)显示中国大陆男歌手的基本信息，信息包括歌手编号、歌手姓名、歌手类型和歌手所在地区。要求采用别名显示。l (KTV数据表)显示歌手所在地区。信息包括歌手编号、歌手姓名、歌手类型和歌手所在地区。要求地区不重复。实践练习实践练习实践时间：30分钟讲

6、解时间：20分钟实践时间：40分钟排序：内容预览排序：内容预览 查询结果排序查询结果排序 TOP NTOP N理论 查询结果排序查询结果排序 TOP NTOP N实践排序l使用关键字：ORDER BYlASC(ascend)：升序，默认值lDESC(descend)：降序，需指定查询结果排序单列排序l指按照某一列值的升序或降序排列l例如：查询Rank表，按RankID升序排序l例如：查询Employee表，按Salary降序排序单列排序单列排序（升序）SELECT RankID, RankName FROM Rank ORDER BY RankID ASC按职级号升序排列。默认为升序，ASC可

7、省略。单列排序（降序）SELECT EmployeeID 员工号, Name 姓名, RankID 职级号, Salary 月基本工资 FROM Employee ORDER BY Salary DESC按照Salary降序排列。可用别名“月基本工资”替换“Salary”。查询结果按多个列进行排序。依照排序列从左至右从左至右的次序，确定排序的优先级。多列排序多列排序SELECT * FROM Employee Order BY RankID, Salary Desc按职级号升序排列，如果职级相同，则按月基本工资降序排列。多列排序TOP N的含义及其使用场景l可以指定查询所返回的行数。l适用于显

8、示排序结果集中的前几条数据，如显示单价最高的五个商品信息、工资最高的前十位员工信息以及市值排名前十位的IT企业信息等。TOP NSELECT TOP N FROM 返回结果集中前N行数据。SELECT TOP 5 * FROM Employee Order BY Salary DESC查询月基本工资最高的五个员工。去掉DESC后，查询语句的执行结果是什么？TOP NTOP N与TOP N比较 按百分比返回数据行。 TOP 20 PERCENT返回前20%的行数据。TOP N PERCENTSELECT TOP 20 PERCENT * FROM Employee Order BY Salary

9、 DESCTOP N PERCENTEmployee表共有19条记录。19*20%=3.8，大于3所以显示4条记录。l 练习巩固上述查询语法l 查询月薪最低的10%的员工的姓名和月薪l 查询Employee表，列出所有信息，按照Address升序、月薪降序排列实践练习实践练习实践时间：40分钟n KTV数据表:l 显示男歌手的全部信息，按歌手出生年月升序排列。l 显示点歌数排行前五名的歌曲信息，信息包括：歌曲编号、曲目、歌曲类型编号、歌手编号和点歌数。要求使用别名显示列名。l 显示歌曲的全部信息，按歌曲名长度升序排列，如果字数相等，则按点歌次数降序排列。l 显示酒水小食信息，按单价升序排列。讲

10、解时间：30分钟实践时间：60分钟条件查询模糊查询：内容预览条件查询模糊查询：内容预览 条件查询条件查询 模糊查询模糊查询理论 条件查询条件查询 模糊查询模糊查询实践条件查询的意义和表示方法 数据库表中包含大量的数据，根据需要，有时仅需显示满足一定条件的数据。 在SELECT语句中，使用WHERE子句表示查询的条件。 在WHERE子句中，经常使用两种运算符：运算符和运算符。条件查询SQL Server运算符比较运算符逻辑运算符条件查询运算符比 较 运 算 符含 义=等于 大于=大于等于=小于等于 不等于!=不等于逻 辑 运 算 符含 义AND并且OR或者NOT取反IS NULL如果列值为NUL

11、L则返回true，否则返回falseWHERE子句后仅有一个查询条件单条件查询SELECT * FROM Rank WHERE RankID=203SELECT * FROM Employee WHERE Salary=20000SELECT * FROM RANK WHERE RankName != 董事会秘书“不等于”，可以用“”代替WHERE子句后有多个查询条件多条件查询SELECT * FROM Employee WHERE Birthday=1983-1-1 AND Salary7000SELECT * FROM Employee WHERE Salary20000 OR Rank=

12、204SELECT * FROM Employee WHERE (Salary20000 AND Salary7000) OR Rank=204模糊查询的作用模糊查询即利用部分参数查找到相关数据的一种搜索方式，如查找张姓员工信息。在SQL Server中，利用运算符和可实现模糊查询。模糊查询还可搜索出满足值在某一之内的数据表的字段数据。模糊查询LIKE运算符的实现方式 在SQL Server 中，通过使用通配符来实现LIKE运算。 通配符是一种在WHERE子句中拥有特殊意义的字符，通配符包括“%”、“_”和“”。LIKE运算符“%”通配符匹配0至多个任意字符 开头、结尾匹配 中间匹配 两端匹配

13、“%”通配符SELECT * FROM Employee WHERE Name LIKE 许%涛SELECT * FROM Employee WHERE Name LIKE 李%SELECT * FROM Employee WHERE Name LIKE %军%“_”通配符匹配任意一个字符“_”通配符SELECT * FROM Employee WHERE Name LIKE 张_星“_”通配符SELECT * FROM Employee WHERE Name LIKE 李_ _“%”和“_”综合应用SELECT * FROM Employee WHERE Name LIKE _晓%“ ”通配

14、符用于指定一个字符集合 “ ”指定一个字符、字符串或范围，要求所匹配的对象为它们中的任一个。 取值同 ，但其要求所匹配对象为指定字符以外的任意一个字符。 “ ”中的字符不能是通配符。如LIKE 5%，其中，“ ”内的“%”不能理解为“%”通配符，它仅表示“%”字符。“”通配符“”通配符查询条件表达式匹配数据LIKE 5a5aLIKE 5%5%，“%”在中不能作为通配符使用LIKE 5_5_，“_”在中不能作为通配符使用LIKE a,b,cdefadef或bdef或cdefLIKE abcdefadef或bdef或cdefLIKE a-f,M-Ta、b、c、d、e、f、M、N、O、P、Q、R、S

15、、TLIKE abc_d%abc_dLIKE aad%不能为aad.，a表示不能取al 练习巩固上述查询语法l 查询Address在武汉的男性员工l 查询Address在南京的月薪超过8k的女性员工l 查询姓名中带“晓”的员工的姓名和地址l 查询姓名为两个字的员工姓名l 查询1980年到1990年出生的、月薪在7k8k之间的员工信息实践练习实践练习实践时间：60分钟实践练习实践练习n KTV数据表:l 显示出生年月晚于1980-1-1的台湾女歌手的全部信息。l 显示欧美歌手和香港女歌手的信息。l 显示类型为“影视金曲”的歌曲全部信息。l 显示大陆女歌手演唱的歌曲全部信息。l 显示大陆和香港男歌

16、手演唱的类型为“热门流行”的歌曲全部信息。l 显示2013-5-30日，酒水小食消费量前三名的消费信息。l 显示李姓歌手信息。l 显示姓名长度为3的蔡姓歌手信息。l 显示“一”开头的歌曲信息。l 显示歌曲名长度为4的歌曲信息。l 显示经典老歌和影视金曲信息。l 显示点击次数在30和60之间的歌曲信息。讲解时间：10分钟实践时间：30分钟查询结果中列的操作：内容预览查询结果中列的操作：内容预览 ININ运算符运算符 BETWEEN.AND运算符运算符理论 ININ运算符运算符 BETWEEN.AND运算符运算符实践IN运算符定义 用于判断一个值是否在一个指定的数据集合之内，也称为“成员条件运算符

17、”。 使用OR运算符可以替换IN运算符。IN运算符IN运算符查找工作地点在武汉或南京的职员信息SELECT * FROM Employee WHERE Address IN运算符SELECT * FROM Employee WHERE Address=南京 Address=武汉 当选择条件很多时，OR运算符运行效率较IN运算符低。BETWEEN.AND运算符的使用方式 在WHERE子句中，可以采用BETWEEN.AND运算符选取介于两个值之间的数据，值可以是数字、字符和日期类型。 取值范围边界。BETWEEN.AND运算符比较值为数字类型数据BETWEEN.AND运算符取值范围包括边界SELE

18、CT * FROM Employee WHERE Salary 20000 50000比较值为日期类型数据BETWEEN.AND运算符SELECT * FROM Employee WHERE Birthday 1970-01-01 1980-01-01l 练习巩固上述查询语法l 查询1980年到1990年出生的、月薪在7k8k之间的员工信息l 查询在武汉、南京、上海工作的员工的月薪，按降序排列n KTV数据表:l 显示点击次数在30和60之间的歌曲信息。l 显示经典老歌和影视金曲信息。l 显示地区为欧美和日韩的歌手信息。l 显示年龄在30至40（含30和40）之间的歌手信息。实践练习实践练习实

19、践时间：30分钟讲解时间：30分钟实践时间：60分钟内容预览内容预览 聚合函数聚合函数 分组查询分组查询理论 聚合函数聚合函数 分组查询分组查询实践聚合函数的定义和作用聚合函数能对集合中的一组数据进行计算，并返回单个计算结果。例如，计算满足各种条件的行数，找出某些字段的最小值和最大值，求某个字段的总和、平均值等。除COUNT(*)外，聚合函数均。聚合函数SUM函数返回选取的某列值的总和。SUM函数SELECT SUM(Salary) AS 月基本工资总额 FROM EmployeeMAX函数返回选取的某列的最大值。MIN函数返回选取的某列的最小值。MAX/MIN函数SELECT MAX(Sal

20、ary) AS 最高月基本工资 FROM EmployeeSELECT MIN(Salary) AS 最低月基本工资 FROM EmployeeSELECT MIN(Birthday) AS 最高龄员工出生年月 FROM EmployeeAVG函数返回选取的某列的平均值。MAX/MIN函数SELECT AVG(Salary) AS 月基本工资平均值 FROM EmployeeCOUNT函数返回选取的某列或记录的行数。COUNT(*)获取表行数。COUNT(column)获取表中column列不为空的行数。COUNT函数SELECT COUNT(*) AS 员工人数 FROM EmployeeS

21、ELECT COUNT(Birthday) AS 有出生记录的员工数 FROM Employee仅统计Birthday不为空的记录的数量。聚合函数综合应用 (*) 员工人数, (Salary) AS 平均月基本工资, (Salary) AS 最高月基本工资, (Salary) AS 最低月基本工资 Employee RankID=204GROUP BY的含义和作用 GROUP BY意为“根据（by）一定的规则进行分组（Group）”。 其作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对若干个小区域进行统计汇总。GROUP BYGROUP BY 字段列 HAVING 分组所依据的字

22、段名称列表，可以为多个字段。指定GROUP BY分组显示时需要满足的限定条件。GROUP BYGROUP BY子句的功能和使用场景 用于对查询结果分组统计。 常与聚合函数联合使用。GROUP BY按职级分组计算员工数、月基本工资平均值和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均工资升序排列。GROUP BYSELECT , , , FROM Employee RankID Order BY COUNT(*) , AVG(Salary)根据RankID分组可以使用别名“员工数”和“月基本工资”替换。空值参与分组GROUP BY查询每个职级的员工的最高月基本工资，按职级号升序排列

23、。GROUP BYSELECT RankID 职级号, MAX(Salary) 最高月基本工资 FROM Employee GROUP BY RankID ORDER BY RankIDGROUP BYGROUP BYSELECT Name 员工姓名 , RankID 职级号, MAX(Salary) 最高月基本工资 FROM Employee GROUP BY RankID ORDER BY RankID出错原因：Name是非聚合函数列，且其在GROUP BY子句中并未出现。使用GROUP BY分组查询时，出现在SELECT子句中的一定要出现在GROUP BY子句中。GROUP BY多字段分

24、组规则l 使用可以根据进行分组。l 分组层次，即先按第一个字段分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组依次类推。多字段分组获取不同职级的员工在不同城市工作的人数，按员工职级升序排列。多字段分组SELECT RankID 员工职级, Address 员工工作地, COUNT(Address) 员工人数 FROM Employee GROUP BY RankID, Address ORDER BY RankID先按照RankID进行分组，然后在RankID值相同的记录中，再根据Address进行分组。多字段分组获取在不同城市工作的男女员工人数，按性别升序排列，如果性别相同

25、，则按员工人数升序排列。多字段分组SELECT Gender 员工性别, Address 员工工作地, COUNT(Address) 员工人数 FROM Employee ORDER BY 员工性别, 员工人数多字段分组l 练习巩固上述查询语法l 显示员工信息按年代、地址分组信息l 查询“工程师”的最低工资、最低工资、平均工资l 查询各级别工程师的最低工资、最低工资、平均工资、工资总和l 查询平均工资大于10k的级别n KTV数据表:l 查询香港和台湾歌手中年龄最小和最大的女歌手生日。l 计算大陆男歌手平均年龄。l 统计香港和台湾女歌手数量。 l 汇总歌曲点歌次数。（注意：使用SUM函数而不是

26、COUNT函数）。l 查询演唱者编号为12的最受欢迎歌曲的点歌次数。l 查询类型名为“热门流行”的最不受欢迎歌曲的点歌次数。实践练习实践练习实践时间：60分钟l 计算所有歌曲的平均点歌次数。l 计算歌曲类型名为“经典老歌”的歌曲数量。l 按歌手地区和歌手类型统计歌手人数l 根据歌手地区分组，统计歌手人数，要求显示歌手地区和歌手人数。l 根据歌手地区和歌手类型分组，统计歌手人数，要求显示歌手地区、歌手类型和歌手人数。l 根据歌曲类型，统计歌曲数。l 根据歌曲类型和歌手，统计歌曲数。实践练习实践练习讲解时间：10分钟实践时间：30分钟内容预览内容预览 使用使用HAVINGHAVING对分组结果进行过滤对分组结果进行过滤理论 分组结果过滤分组结果过滤实践HAVING的含义和作用l HAVING子句的作用是筛选满足条件的组，即在分组之后过滤数据。l 使用 HAVING子句中的条件用于显示特定的组，条件中经常包含聚合函数。l HAVING子句在GROUP BY子句之后。使用HAVING对分组结果进行过滤查询每个职级的员工最高月基本工资大于10000的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列。使用HAVING对分组结果进行过滤SELECT RankID 职级号 , MAX(Sal