Excel2010 常用SQL语句解释

Excel常用SQL语句解释

SELECT查询

图C- 1展示了某公司的员工信息数据列表。

图C- 1 公司员工信息数据列表

含义：从指定的表中返回符合条件的指定字段的记录。

语法：

SELECT {谓词} 字段AS 别名 FROM 表

{WHEREE 分组前条件}

{GROUP BY 分组依据}

{HAVING 分组后条件}

{ORDER BY 指定排序}

SELECT查询各部分的说明如表格C- 1所示。

SELECT查询的基本语句

如果希望在如图C- 1所示的“员工信息”数据列表中，查询所有字段的数据记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$]

如果希望在如图C- 1所示的“员工信息”数据列表中，查询每个员工所在的部门及其婚姻状况的数据记录，可以使用以下SQL语句。

SELECT 部门,姓名,婚姻状况 FROM [员工信息$]

WHERE子句

如果希望在如图C- 1所示的“员工信息”数据列表中，查询员工性别为男的数据记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$] WHERE 性别='男'

BETWEEN…AND运算符

用于确定指定字段的记录是否在指定值范围之内。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询基本工资在1500到2000之间（含1500和2000）的数据记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$] WHERE 基本工资BETWEEN 1500 AND 2000

NOT运算符

表示取相反的条件。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询基本工资不在1500到2000之间（即基本工资小于1500或大于2000）的所有记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$] WHERE NOT 基本工资BETWEEN 1500 AND 2000

AND、OR运算符

当查询条件在两个或两个以上，需要使用AND或OR等运算符将不同的条件连接，其中，使用AND运算符表示连接的条件，只有同时成立才返回记录，使用OR运算符表示连接的条件中，只要有一个条件成立，即可返回记录。需要注意的是，AND运算符执行次序比OR运算符优先，如果用户需要更改运算符的运算次序，请用小括号将需要优先执行的条件括起来。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询“财务室”部门员工的基本工资高于2000的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 部门='财务室' AND 基本工资>2000

如果希望在如图C- 1所示的“员工信息”数据列表中，查询“财务室”或“业务部”两个部门的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 部门='财务室' OR 部门='业务部'

IN运算符

确定字段的记录是否在指定的集合之中。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询“陈丰笑”、“孙娇雪”和“刘风权”等3位员工的数据记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$] WHERE 姓名IN ('陈丰笑','孙娇雪','刘风权')

使用NOT IN，可以返回字段记录在指定集合之外的记录。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询除“陈丰笑”、“孙娇雪”和“刘风权”等3位员工外的数据记录，可以使用以下SQL语句。

SELECT * FROM [员工信息$] WHERE 姓名NOT IN ('陈丰笑','孙娇雪','刘风权') LIKE运算符

返回与指定模式匹配的记录，若需要返回与指定模式匹配相反的记录，请使用NOT LIKE，LIKE 运算符支持使用通配符。

LIKE使用的通配符如表格C- 2所示。

表格C- 2 通配符说明

提示：常用的字符列表包括数字字符列表[0-9]、大写字母字符列表[A-Z]和小写字母字符列表[a-z]。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询姓名以“陈”开头的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 姓名LIKE '陈%'

如果希望在如图C- 1所示的“员工信息”数据列表中，查询姓名不以“陈”开头的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 姓名LIKE '[!陈]%'

也使用以下语句。

SELECT * FROM [员工信息$] WHERE 姓名NOT LIKE '陈%'

如果希望在如图C- 1所示的“员工信息”数据列表中，查询姓名以“翠”结尾且姓名长度为2的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 姓名LIKE '_翠'

如果希望在如图C- 1所示的“员工信息”数据列表中，查询姓名包含字母的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 姓名LIKE '%[a-zA-Z]%'

注意：在Excel 2010保存的工作薄中，使用SQL语句返回的记录不区分大小写，但以兼容形式另存为Excel 2010版本以下的工作簿时（如Excel 97-2003版本），记录区分大小写。

常量NULL

表示未知值或结果未知。判断记录是否为空，可以用ISNULL或IS NOT NULL。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询没有领取住房津贴的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 住房津贴IS NULL

如果希望在如图C- 1所示的“员工信息”数据列表中，查询有领取住房津贴的数据记录，可以使用以下语句。

SELECT * FROM [员工信息$] WHERE 住房津贴IS NOT NULL

已知员工的实际收入等于基本工资加上住房津贴，如果希望在如图C- 1所示的“员工信息”数据列表中，统计每个部门的员工的实际收入，可以使用以下SQL语句。

SELECT 部门,姓名,基本工资+IIF(住房津贴IS NULL,0,住房津贴) AS 实际收入 FROM [员工信息$] 提示：NULL表示未知值或结果未知，如何与NULL进行的运算，其结果也是未知的，返回NULL。所以，这里需要使用IIF函数，将住房津贴为NULL的值返回0，否则返回住房津贴，然后再与基本工资相加，从而得到实际收入。

GROUP BY子句

如果希望在如图C- 1所示的“员工信息”数据列表中，统计每个部门的员工人数，可以使用以下SQL语句。

SELECT 部门,COUNT(姓名) AS 员工人数FROM [员工信息$] GROUP BY 部门

HAVING子句

如果希望在如图C- 1所示的“员工信息”数据列表中，查询员工人数超过7人（含7人）的部门记录，可以使用以下SQL语句。

SELECT 部门 FROM [员工信息$]GROUP BY 部门HAVING COUNT(姓名)>=7

提示：HAVING子句通常需要结合GROUP BY子句使用。

聚合函数

聚合函数的说明如表格C- 3所示。

表格C- 3 聚合函数

提示：使用如表格C- 3所示的聚合函数中，除FIRST和LAST函数外，其余函数均忽略空值（NULL）。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询每个部门最高可领取的住房津贴的数据记录，可以使用以下SQL语句。

SELECT 部门,MAX(住房津贴) AS 最高住房津贴FROM [员工信息$] GROUP BY 部门

DISTINCT谓词

使用DISTINCT谓词，将忽略指定字段返回的重复记录，即重复的记录只保留其中一条。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询部门的不重复记录，可以使用以下SQL语句。

SELECT DISTINCT部门FROM [员工信息$]

ORDER BY子句

使用ORDER BY子句，可以使结果根据一个或多个字段的指定排序方式进行排序。如果指定的字段没有指定排序模式，则默认为按此字段升序排序。

提示：在数据透视表中，字段的排序结果最终取决于数据透视表的字段排序方式。

TOP谓词

使用TOP谓词，可以返回位于ORDER BY 子句所指定范围内靠前或靠后的某些记录。

如果不指定排序方式，则返回此TOP谓词所对应表或查询的靠前的指定记录。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询前10条记录，可以使用以下SQL 语句。

SELECT TOP 10 * FROM [员工信息$]

如果希望在如图C- 1所示的“员工信息”数据列表中，查询基本工资在前10位的数据记录，可以使用以下SQL语句。

SELECT TOP 10 * FROM [员工信息$] ORDER BY 基本工资DESC

结合使用PERCENT 保留字可以返回位于ORDER BY 子句所指定范围内靠前或靠后的一定百分比的记录。

如果希望在如图C- 1所示的“员工信息”数据列表中，查询基本工资前30%的数据记录，可以使用以下语句。

SELECT TOP 30 PERCENT * FROM [员工信息$] ORDER BY 基本工资DESC

提示：如果使用ORDER BY子句，那么假如在指定范围内最后一条记录有多个相同的值，那么这些值对应的记录也会被返回。如果没有OREDR BY子句，那么在指定范围内最后一条记录即使有多个相同的值，也只会返回在指定范围内靠前的记录。

联合查询

图C- 2展示了某连锁集团“三角头”、“江南”和“东山”三间分店的销售数据列表。

图C- 2 分店销售数据列表

含义：合并多个查询的结果集，这些查询具有相同的字段数目且包含相同或可以兼容的数据类型。语法：

SELECT 字段 FROM 表1UNION {ALL}

……

SELECT 字段 FROM 表x

联合查询的特点：

使用联合查询，需要确保查询的字段数目相同，且包含相同或兼容的数据类型。

在联合查询中，最终返回的记录的字段名称以第一个查询的字段名称为准，其余进行联合查询的查询，使用的字段别名将被忽略。

UNION和UNION ALL的区别在于，UNION会将所有进行联合查询的表的记录进行汇总，并返回不重复记录（即重复记录只返回其中一条记录），同时对记录进行升序排序，而UNION ALL则只将所有进行联合查询的表的记录进行汇总，不管记录是否重复，也不对记录进行排序。

提示：“数字”和“文本”在联合查询中，是可以兼容的数据类型

如果希望查询如图C- 2所示的“三角头”、“江南”和“东山”三间分店销售数据列表中，各分店所有产品不重复个数，可以使用以下SQL语句。

SELECT '三角头' AS 分店,产品 FROM [三角头$]UNION

SELECT '江南',产品FROM [江南$]UNION

SELECT '东山',产品FROM [东山$]

如果希望将如图C- 2所示的“三角头”、“江南”和“东山”三间分店销售数据列表进行汇总，可以使用以下SQL语句。

SELECT '三角头' AS 分店,* FROM [三角头$] UNION ALL

SELECT '江南',* FROM [江南$] UNION ALL

SELECT '东山',* FROM [东山$]

多表查询

图C- 3展示了某班级“学生信息”、“科目”和“成绩表”三张数据列表。

图C- 3 班级成绩数据列表

含义：根据约束条件，返回查询指定字段记录所有可能的组合。

语法：

SELECT{表名称}.字段FROM 表1,表2,……表x {WHERE 约束条件}

多表查询的特点：

在同一语句中，若需要查询的字段名称存在于多张表中，那么，此字段名称需要声明来源表，否则该字段可省略声明来源表。

当查询涉及多张表关联时，需要注意使用约束条件，没有约束条件或约束条件设置不当，将可能出现笛卡尔积，从而导致数据虚增。

如果希望在如图C- 3所示的“科目”和“成绩表”数据列表中，查询各科目的平均成绩及各科目任课老师的数据记录，可以使用以下SQL语句。

SELECT A.科目名称,A.任课老师,AVG(B.分数) AS 平均分 FROM [科目$]A,[成绩表$]B WHERE A.

科目名称=B.科目 GROUP BY A.科目名称,A.任课老师

设置“平均分”字段的数字格式为【数值】，【小数位数】为0，最终生成的数据透视表如图C- 4所示。

图C- 4 科目任课老师和科目平均分数据列表

内部联接

含义：对于不同结构的表或查询，如果这些表或查询具有关联的字段，那么将这些表或查询指定字段的记录按关联的字段整合在一起。

使用单个内部联接的语法：

SELECT {表名称.}字段 FROM 表1 INNER JOIN 表2 ON 关联字段

如果希望在如图C- 3所示的“学生信息”和“成绩表”数据列表中，查询参加考试学生的各科目成绩及其担任职务的数据记录，可以使用以下SQL语句。

SELECT A.*,B.职务FROM [成绩表$]A INNER JOIN [学生信息$]B ON A.学生=B.学生 WHERE B.

职务IS NOT NULL

最终生成的数据透视表如图C- 5所示。

图C- 5 担任职务的学生成绩数据列表

使用多个内部联接的语法：

SELECT {表名称.}字段FROM (……(表1 INNER JOIN 表2 ON 关联字段) INNER JOIN 表3 ON 关联字段……)INNER JOIN 表x ON 关联字段

如果希望在如图C- 3所示的“学生信息”、“科目”和“成绩表”数据列表中，查询参加考试的学生担任的职务、各科目的成绩和各科目任课老师的数据记录，可以使用以下SQL语句。SELECT A.学生,A.科目,A.分数,B.职务,C.任课老师 FROM ([成绩表$]A INNER JOIN [学生信息$]B ON A.学生=B.学生) INNER JOIN [科目$]C ON A.科目=C.科目名称

最终生成的数据透视表如图C- 6所示。

图C- 6 学生职务、科目成绩和科目任课老师数据列表

左外部联接和右外部联接

含义：左外部联接返回左表所有记录和右表符合关联条件的部分记录，右外部联接刚好与左外部联接相反，右外部联接返回的是右表所有记录和左表符合关联条件的部分记录。

单个左外部联接/右外部联接：

SELECT {表名称.}字段 FROM 表1 LEFT JOIN/RIGHT JION 表2 ON 关联条件

如果希望在如图C- 3所示的“学生信息”和“成绩表”数据列表中，查询所有科目都缺考学生的学生信息，可以使用以下SQL语句。

SELECT A.* FROM [学生信息$]A LEFT JOIN [成绩表$]B ON A.学生=B.学生 WHERE B.分数 IS NULL 或使用以下SQL语句。

SELECT B.* FROM [成绩表$]A RIGHT JOIN [学生信息$]B ON A.学生=B.学生WHERE A.分数IS NULL

最终生成的数据透视表如图C- 7所示。

图C- 7 所有科目都缺考的学生信息数据列表

提示：多个左外部联接/右外部联接的语法请参考多个内部联接。

子查询

三种常用子查询语法：

SELECT (子查询) {AS 字段} FROM 表

SELECT 字段 FROM 表 WHERE 字段运算符{谓词} (子查询)

SELECT 字段 FROM 表 WHERE {NOT} EXISTS (子查询)

如果希望在如图C- 3所示的“成绩表”数据列表中，对参加考试的学生的总成绩进行排名，可以使用以下SQL语句。

SELECT *,(SELECT COUNT(学生) FROM (SELECT 学生,SUM(分数) AS 总分 FROM [成绩表$] GROUP BY 学生)A WHERE A.总分>B.总分)+1 AS 排名 FROM (SELECT 学生,SUM(分数) AS 总分 FROM [成绩表$] GROUP BY 学生)B

最终生成的数据透视表如图C- 8所示。

图C- 8 学生总分排名数据列表

如果希望在如图C- 3所示的“成绩表”数据列表中，查询各科目分数最高的学生成绩数据记录，可以使用以下SQL语句。

SELECT * FROM [成绩表$]A WHERE 分数=(SELECT MAX(分数) FROM [成绩表$]B WHERE A.科目

=B.科目 GROUP BY B.科目)

也使用以下SQL语句。

SELECT * FROM [成绩表$]A WHERE 分数IN (SELECT MAX(分数) FROM [成绩表$]B WHERE A.科目=B.科目 GROUP BY B.科目)

还可以使用以下SQL语句。

SELECT * FROM [成绩表$]A WHERE EXISTS (SELECT 最高分FROM (SELECT 科目,MAX(分数) AS 最高分 FROM [成绩表$] GROUP BY 科目)B WHERE A.科目=B.科目AND A.分数=B.最高分)

最终生成的数据透视表如图C- 9所示。

图C- 9 各科目分数最高的学生数据列表