第8讲 数据表的查询 马秀麟 2012－11－15 2019/5/24.

Presentation on theme: "第8讲 数据表的查询 马秀麟 2012－11－15 2019/5/24."— Presentation transcript:

1 第8讲 数据表的查询 马秀麟 2012－11－15 2019/5/24

2 一、查询的概念 1、查询的用途 2、查询的类型 查询是进行一切数据库操作的基础操作 (1)简单查询： (2)分组计算查询： 单表查询：
多表连接查询： (2)分组计算查询： 思路 分组字段 计算字段 相当于Excel的分类汇总 2019/5/24 马秀麟 2008－12－24

3 一、查询的概念 (3)带有参数的查询： (4)更新查询 (5)SQL查询： 插入查询， 修改查询， 删除查询 2019/5/24
马秀麟 2008－12－24

4 二、创建简单查询 1、单表查询 特点： 注意： 所有数据来源于一个数据表。主要任务是选择数据表，然后选择输出列和查询条件。例如：
输入到字段行中的信息会默认加方括号，因为计算机认为字段名要用方括号括起来。 输入到条件行中的内容会默认添加英文双引号，计算机认为字符串需要加英文双引号。 输入到条件行中的内容如果带有方括号，会被系统理解为参数查询，将提出问题，要求用户回答。 2019/5/24 马秀麟 2008－12－24

5 二、创建简单查询 2、多表连接查询 查询数据或条件来源于多个数据表。核心问题是建立多表之间的关系。如果相关表之间已经有了关系，则添加数据表时自动包含相关关系。如果相关表之间尚没有关系，则可以直接以鼠标拖动方式添加数据表之间的临时关系。 2019/5/24 马秀麟 2008－12－24

6 二、创建简单查询 3、查询条件的撰写 (1)单条件书写 普通比较式：> >= <= <>=
处于二者之间： between 数1 and 数2 字符串的模糊比较： like "*字符串*" 例如：查找姓名中包含“丽”字的同学：姓名 like "*丽*" 其中*为字符的通配符，可以代表任意字符。 2019/5/24 马秀麟 2008－12－24

7 二、创建简单查询 (2)常用函数式 字符串操作函数： 日期操作函数： 集合操作式：in (集合)
取左侧的字符：left(变量名，字符个数） 取右侧的字符：right(变量名,字符个数） 取从x处开始的n个字符：mid(变量名,x,n) 取“字符串1”在“字符串2”中的位置：instr(字符串2，字符串1） 日期操作函数： 取年份：Year(日期型变量） 取月份：month(日期型变量） 取日子：Day(日期型变量） 取当前日期：Date() 集合操作式：in (集合) 例如： 姓名 in ("王虎","崔里","马佳","赵兰") （提示：可以利用Access提供的表达式生成器工具）。 2019/5/24 马秀麟 2008－12－24

8 二、创建简单查询 (3)复合条件式书写 (4)条件式书写规范 not and or
所有函数的函数名后面必须有()，其参数如果是字段名，则需要把字段名用[]括起来。如果不是字段名，字符串常量用""标记，日期常量用##标记，数值型常量不需要标记。 2019/5/24 马秀麟 2008－12－24

9 三、计算查询 1、无条件计算查询 特点 例如 注意： 对表中的所有记录进行计算，没有约束条件。
计算学生表中的所有人的打工收入总和、平均打工收入。 注意： 通过“视图”－“总计”，打开“总计”行。 计算结果只有一条记录，其中有2个数字。 2019/5/24 马秀麟 2008－12－24

10 三、计算查询 2、有条件计算查询 特点 例如 对表中的部分记录进行计算，有约束条件。
计算学生表中的物理系、男生的打工收入总和、平均打工收入。 注意 总计行中的标记：条件，条件，总计，平均值。 计算结果只有一条记录，其中有2个数字。 2019/5/24 马秀麟 2008－12－24

11 三、计算查询 3、分组计算查询 特点 例如 对表中的记录先按照某个字段分组，然后进行计算。
分别求出学生表中每个单位的学生的打工收入总和、平均打工收入。 注意 总计行中的标记：分组，条件，总计，平均值。第一个标记“分组”，表示按照单位分组。强调：分组字段应该标记为显示状态，以便说明该行的统计结果属于哪个单位。 计算结果有多条记录，每行有2个数字，说明其中一个单位的情况。 2019/5/24 马秀麟 2008－12－24

12 三、计算查询 4、查询列带有计算式的计算 特点： 例如 对表中的特定列先计算，然后输入结果。
分别查询学生表中每位学生的总收入（打工收入＋生活补贴）、年龄。 2019/5/24 马秀麟 2008－12－24

13 四、SQL查询语言 1、什么是SQL 语言 含义 价值 Structure Query Language (结构化查询语言）
数据库的通用语言 2019/5/24 马秀麟 2008－12－24

14 四、SQL查询语言 2、SQL查询语句的一般格式 (1)语法 select <列名>,... from <表名>
[where <条件式>] [into table <目的表>] [group by <分组列> ][Having <分组条件>] [Order by <排序列> asc/DESC] 需要大家注意的是：SQL语句的子句顺序基本不允许变化，请大家牢记上面的语句顺序。 2019/5/24 马秀麟 2008－12－24

15 四、SQL查询语言 spxxb.dbf(商品销售表） 售货员号 商品号 件数 单价 金额 001 AAA 10 1.00 002 BBB
23 3.00 003 30 20 2.00 CCC 40 004 DDD 4.00 11 15 12 14 17

16 四、SQL查询语言 示例表2 update spxxb set 金额=单价*件数 shy.dbf(售货员表） 售货员号 售货员名 单位 住址
籍贯 001 王小丽 礼品部 海淀区 山东 002 马小明 五金部 西城区 004 崔大伟 东城区 河北 003 赵秀花 日用品部

17 四、SQL查询语言 (3)案例 列出一个表中的所有信息： 列出表中的部分信息： 对字符串的模糊查询： 唯一性查询：
Select * from spxxb 列出表中的部分信息： Select * from shy where 单位=‘礼品部’ Select 售货员号,姓名 from shy 对字符串的模糊查询： select * from shy where 姓名 like %丽%’ 唯一性查询： Select distinct 列名 from 表名

18 四、SQL查询语言 2、计算查询 (1)统计函数 SQL语句常用的计算函数有五个：sum,count,max,min,avg.

19 四、SQL查询语言 (2)普通计算查询示例 求出所有生意的总金额： SELECT sum(金额) from spxxb
SELECT sum(金额) as 总金额 from spxxb 其中“as 总金额”说明金额以“总金额”作为SUM(金额）的别名。

20 四、SQL查询语言 求出各笔生意的最大销售额： 求出各笔生意的最小销售额： 求出001号售货员经手的生意的总金额：
SELECT max(金额) as 最大金额 from spxxb 求出各笔生意的最小销售额： SELECT min(金额) as 最小金额 from spxxb 求出001号售货员经手的生意的总金额： SELECT sum(金额) as 总金额 from spxxb where 售货员号="001" 求出商品CCC的总经营金额： SELECT sum(金额) as 总金额 from spxxb where 商品号="CCC"

21 四、SQL查询语言 (3)简单分组查询： 求出每个售货员的销售额：
SELECT 售货员号,sum(金额) as 总金额 from spxxb group by 售货员号 求出每种商品的销售额： SELECT 商品号,sum(金额) as 总金额 from spxxb group by 商品号 求出每种商品的最大经营额（一笔生意）： SELECT 商品号,max(金额) as 最大额 from spxxb group by 商品号 求出每种商品被出售的次数： SELECT 商品号,count(*) as 次数 from spxxb group by 商品号 求每种商品被出售的次数，最大的生意额，总销售额： SELECT 商品号,count(*) as 次数,max(金额) as 最大额,sum(金额) as 总销售额 from spxxb group by 商品号

22 四、SQL查询语言 3、嵌套查询 (1)嵌套查询的含义
所谓嵌套查询是指先对一个数据表进行查询，得到一个数据集，然后再让另一个查询依据这个数据集操作。例如：查找礼品部做的各笔生意。我们就可以先找出礼品部的售货员，然后把这些售货员做的生意列出来就可以了。也就是说，这种查询往往由两个或两个以上的子查询组成，先进行的查询我们称为内层查询，后出现的查询是外层查询。由于外层查询依据内层查询实现，因此一般的内层查询结果都只有一个字段。 在嵌套查询中，如果内层查询的值仅有一个，一般可用“=”号，如果有多个，一般用“IN”进行联系，表示“属于”。

23 四、SQL查询语言 (2)简单嵌套查询实例 查找礼品部做的各笔生意： 查找河北人做的各笔生意：
select * from spxxb where 售货员号 in ; (select 售货员号 from shy where 单位="礼品部") 说明：(划横线的为内层查询，因礼品部的售货员不止一人，故 in 不能变成 =） 查找河北人做的各笔生意： (select 售货员号 from shy where 籍贯="河北")

24 四、SQL查询语言 (2)复杂嵌套查询实例 查找销售额在500员以上的售货员的姓名，单位：
select 姓名，单位 from shy where 售货员号 in ; (select 售货员号 from spxxb group by 售货员号 having sum(金额）>=500) 查找作过三笔以上生意而且有一笔生意的金额超过200元的售货员的所有信息： select * from shy where 售货员号 in ; (select 售货员号 from spxxb group by 售货员号 having count(*）>=3 and max(金额)>=200) 查找销售金额最高的那笔生意： select * from spxxb where 金额 = (select max(金额）from spxxb)

25 四、SQL查询语言 4、连接查询 (1)问题的提出：
如果我们想列出礼品部的人员做的各笔生意，要求显示单位、姓名、商品号、件数和金额。由于要显示的字段处在不同的表中，显然使用上面介绍的命令是不能完成的。 引入一个多表连接的命令，即通过一个语句把两个表连接起来。一般说来，两个表的连接不能胡乱联系，必须通过一个特殊的连接字段来联系。 因此，让商品销售表与售货员表通过售货员号连接起来，这样才能保证数据的正确性，不致于把马小明做的生意安到崔大伟身上。 制作人：马秀麟

26 四、SQL查询语言 (2)命令格式： select <列名>，<列名>..... from<表名>，<表名> where <连接条件> and <普通条件> 需要注意的是： 如果使用的列在两个表中都存在，则必须在列名前加“表名.”加以说明。 from 后面的表名可以带盘符、路径，其他的表名信息处是表的别名，不许带盘符和路径。 对于连接后的表，也可以使用排序等功能。 制作人：马秀麟

27 四、SQL查询语言 (3)实例 列出礼品部的人员做的各笔生意，要求显示单位、姓名、商品号、件数和金额。
SELECT 单位，姓名，商品号，件数，金额from spxxb，shy where spxxb.售货员号=shy.售货员号 and 单位="礼品部" 注意：划横线的语句为连接条件。 制作人：马秀麟

28 四、SQL查询语言 列出山东籍的每个售货员的销售总额。要求显示单位，姓名，总金额,结果按单位和姓名升序排列
这个题目有一定的难度。由于在两个表中都没有售货员的销售总金额字段，我们怎么办呢？ 实际上，对于本题，我们可以分两步走： 首先利用分组查询创建一个临时表，存放售货员号和总金额；然后再让售货员表与这个临时表相连接。 select 售货员号,sum(金额) as 总金额 from spxxb into table c:\ttt group by 售货员号 select 单位，姓名，总金额 from ttt,shy where ttt.售货员号=shy.售货员号 and 籍贯="山东" order by 单位,姓名 这种通过几步来完成一个任务的策略希望同志们能够掌握。 制作人：马秀麟