第一讲 数据查询优化.

Presentation on theme: "第一讲 数据查询优化."— Presentation transcript:

1 第一讲 数据查询优化

2 工作目标 1. 培养职业能力目标 2.培养专业知识目标 3.培养职业素质能力 （1）具有数据库查询分析优化的能力；
（2）能对数据表设计连接查询、分组统计查询、嵌套查询等复杂查询； （3）具备根据数据组织设置视图和索引，优化查询速度的能力。 2.培养专业知识目标 （1）掌握排序、连接、分组和嵌套等查询功能应用； （2）掌握聚集函数的应用； （3）掌握视图和索引的概念、作用和创建方法。 3.培养职业素质能力 （1）培养正确的逻辑思维习惯； （2）提高信息搜索、语言表达素质。

3 基于工作过程化的学习情境 工作任务 信息收集 理论学习 讨论方案 制定计划 任务准备 过程实施 质量评价 资 讯 实现商品采购入库管理 1
资 讯 决策与计划 实 施 工作任务 信息收集 理论学习 实现商品采购入库管理 1 实现产品采购退货管理 2 讨论方案 制定计划 按条件查询商品采购数据 3 任务准备 过程实施 统计商品采购数据信息 4 商品采购数据查询优化 5 检查与评价 质量评价

4 通过深入企业调研，我们已经了解了郑州新开普电子股份有限公司的“企业商品管理系统”的需求，并发现在企业中小型数据库系统开发中存在以下问题：
1.数据库在查询上不仅仅是简单查询，还涉及到条件限制，函数计算，分组，排序等等复杂的查询，如何使用复杂查询的知识实现这些操作。 2.查询所需要的数据集合往往是来自于不同的表中，还需要多表联接查询来完成这样操作。 3.对于经常用到的复杂数据查询可以使用合适的方式来简化其操作，并且保证数据的安全性和独立性。 4.为了提高海量数据的查询速度，提高操作效率，如何通过优化数据在数据库中的物理结构来完成。

5 商品采购管理系统的概念模型分析 商品采购管理系统的实现涉及到： 数据的复杂查询； 多表数据的查询。
通过分析该系统的概念模型，能够了解查询所涉及到的实体以及实体和实体之间的联系。 返回目录

6 概念模型分析 商品采购管理系统的E-R模型如图所示： 商品采购管理系统的E-R模型 单位 价格 商品 n n n m 1 1 用户 采购员
商品编号 商品名称 单位 价格 商品 n n n 采购 采购的商品类别 订购 存储 联系电话 m 1 1 用户 采购员 仓库 用户编号 采购员编号 用户名称 仓库名称 负责人 商品采购管理系统的E-R模型

7 通过对商品采购管理系统开发的资讯，将整个开发过程分为6讲进行讲解：
学习情境3 商品采购管理系统数据库开发 通过对商品采购管理系统开发的资讯，将整个开发过程分为6讲进行讲解： 第一讲 数据查询优化 第二讲 数据排序、分组和统计 第三讲 连接查询 第四讲 嵌套查询 第五讲 利用视图进行查询优化 第六讲 利用索引进行查询优化

8 第一讲 数据查询优化

9 商品采购管理系统，具有商品信息查询等基本管理功能。
学习情境引入 商品采购管理系统，具有商品信息查询等基本管理功能。 通过这一系统，企业可以对所有的商品进行快速查询、以及较复杂的查询，能够更快地得到数据，提供系统查询的速度。

10 应用SELECT语句对商品信息进行查询 建立数据库的目的是为了查询数据。因此，可以说数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为： SELECT [ALL | DISTINCT ] <目标列表达式> [, <目标列表达式>…] FROM <表名或视图名 [别名]> [, <表名或视图名>[别名] ]… [WHERE <条件表达式>] [GROUP BY <分组表达式> [HAVING <条件表达式> ] ] [ORDER BY <排序列名> [ASC | DESC ] ]； 其中，SELECT子句指定要显示的属性列。FROM子句指定查询对象（基本表或视图）。WHERE子句指定查询条件。GROUP BY子句用于对查询结果按指定列的值分组，该属性列相等的元组为一组。HAVING子句用于筛选出只有满足指定条件的组。ORDER BY子句用于对查询结果表按指定列值的升序或降序排序。 计算机不可能直接处理现实世界中的具体事物，所以人们必须实现把具体事物转换成计算机能够处理的数据。 在数据中用 数据模型 这个工具来抽象、表示和处理 现实世界的数据和信息。 10

11 [INTO new_table_name] FROM table_list [WHERE search_conditions]
SELECT语句 SELECT select_list [INTO new_table_name] FROM table_list [WHERE search_conditions] [GROUP BY group_by_list] [HAVING search_conditions] [ORDER BY order_list[ASC|DESC]] [COMPUTE compute_condition [BY column]] 指定查询结果集的列 指定要查询的表或视图 选取数据源的行信息 对结果集进行分组汇总 对结果集进行排序 对结果集进行明细汇总

12 1. 选择所有字段 2. 选择部分字段 3. 不显示重复记录 4．TOP关键字 5. 使用WHERE子句筛选查询结果

13 1.选择所有字段 使用T-SQL语言分别显示Sales数据库中的员工表employee、商品表goods、销售表sell_order表和部门表department中的所有记录。 SELECT * FROM employee SELECT * FROM goods SELECT * FROM sell_order SELECT * FROM department

14 2.选择部分字段 在很多情形下，用户只对一个表中的部分列的数据感兴趣。这时可以在“查询结果列表”中，按用户需要的顺序指定所要的列来实现。 查询employee表中所有记录的name、sex字段. SQL语句如下： select name , sex from employee

15 3．不显示重复记录 DISTINCT关键字就是用来从查询的结果集中去掉重复的记录。如果用户没有指定DISTINCT关键字，那么系统默认将返回符合条件的所有记录，其中包括重复的记录。 在employee表中，显示没有重复记录的“name”字段的信息，SQL语句如下： Use Sales select distinct name from employee

16 4．TOP关键字 【案例3.4】查询employee表的前三条记录，SQL语句如下： Use Sales select top 3 * from employee 【案例3.5】查询employee表的前50%的记录，SQL语句如下： Use Sales select top 50 percent * from employee

17 使用WHERE子句筛选查询结果 查询满足指定条件的元组可以通过WHERE<条件表达式>子句实现。条件表达式是操作数与运算符的组合，操作数可以包括常数、变量和字段等。常用运算符如表3-1所示。 （1）比较运算： （2）确定范围： （3）集合运算： （4）字符匹配： （5）空值判断： （6）逻辑运算：

18 表1 常用运算符 查询条件 运算符 比较 =，>，>=，<，<=，!=，<> 确定范围
表1 常用运算符 查询条件 运算符 比较 =，>，>=，<，<=，!=，<> 确定范围 BETWEEN…..AND…...，NOT BETWEEN….AND… 集合运算 IN，NOT IN 字符匹配 LIKE，NOT LIKE 空值判断 IS NULL，IS NOT NULL 逻辑运算 AND，OR，NOT

19 【案例3.7】查询订单号为H001，H002,H003的订单信息。
（1）比较运算 【案例3.6】查询库存量少于10的商品编号和商品名称。 分析：库存数量小于10，要用比较运算符<在WHERE子句中。 SELECT 商品编号，商品名称 FROM 商品信息表 WHERE 库存数量<10; （2）确定集合 【案例3.7】查询订单号为H001，H002,H003的订单信息。 分析：只要订单号的值为H001，H002,H003中的任意一个即可，他们属同一属性的值，所以可以用集合IN。 SELECT * FROM 订单信息表 WHERE 订单号 IN （’H001’，’H002’,’H003’）； 概念模型一方面应该具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识，另一方面它还应该简单、清晰，用户易于理解。 19

20 【案例3.8】查询商品售价在3000～4000元的商品名称、商品编号和库存数量。
（3）确定范围 【案例3.8】查询商品售价在3000～4000元的商品名称、商品编号和库存数量。 分析：3000 ～4000是一个范围，可以用BETWEEN … AND或用>=和<=来完成。 SELECT 商品编号，商品名称，库存数量 FROM 商品信息表 WHERE 商品售价 BETWEEN 3000 AND 4000； 或 WHERE 商品售价>=3000 AND 商品售价<= 4000； 概念模型一方面应该具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识，另一方面它还应该简单、清晰，用户易于理解。 20

21 （4）字符匹配 在实际应用中，用户有时候不能给出精确的查询条件，力图，用户要检索以字母C开始的订单号，找姓王的学生信息等。检索与给定的某些字符串相匹配的数据可以用LIKE关键字。LIKE关键字是一个匹配运算符，它与字符串表达式相匹配，字符创表达式被定义为字符串和通配符。其一般语法格式如下： [NOT] LIKE ‘<匹配串>’[ESCAPE’<换码字符>’] 其中， <匹配串>可以是一个完整的字符串，也可以含有通配符“%”和“_”。 %：代表任意长度的字符串； _：代表任意单个字符。

22 “%”通配符 “%”通配符能匹配0个或任意长度的字符串。 【案例3.10】在employee表中，查找姓“刘”的客户信息，SQL语句如下： Use sales select * from employee where name like '刘%‘ “_”通配符 “_”号表示任意多个字符，该符号只能匹配单个任意字符。 【案例3.11】在employee表中，查找姓“刘”且名字为两个字的客户信息，SQL语句如下： where name like '刘_'

23 “[ ]”通配符 在模糊查找中可以使用“[ ]”来查询一定范围之内的数据。“[ ]”符号用于表示一定范围内的单个字符，它包含两端数据。 【案例3.12】在employee表中，查询用户编号以’01’结尾并且开头数字位于0-2之间的客户信息。SQL语句如下： Use sales select * from employee where num like ‘E%[0-2]'

24 （5）空值查询 【案例3.13】查询“备注”为空的商品信息。 分析：空值判断要用IS NULL或IS NOT NULL。因本题是找备注为空的商品信息，所以要用IS NULL。 SELECT * FROM 商品信息表 WHERE 备注 IS NULL； 注意：IS是不能用“=”来代替的。

25 （6）逻辑运算 逻辑运算要用AND,OR和NOT。 【案例3.14】查询库存数量少于3并且售价低于4000元或成本价格低于3500元的商品编码和商品名称。 SELECT 商品编号，商品名称 FROM 商品信息表 WHERE 库存数量<3 and 商品售价<4000 or 成本价格<3500;

26 用OR进行查询 【案例3.16】在商品表goods中，查询商品编号是“01001”或“01002”的商品信息。SQL语句如下： Use sales select * from goods where g_num='01001' or g_num='01002' 用AND进行查询。

27 用AND进行查询 【案例3.17】在employee表中，查询性别是“女”且在姓名为“李四”的客户信息。SQL语句如下： Use sales select * from employee where e_sex='女' and name=‘李四’

28 使用IS NULL关键字查询没有赋值的行 【案例3.18】查询employee表中没有姓名的员工编号。 USE sales GO SELECT * FROM employee WHERE employee_name is NULL

29 总结 1．选取全部字段(*) 2．选取指定字段 3．设置字段别名：列表达式 [as] 别名 （三种方式）
4．过滤重复记录(DISTINCT) 5．返回全部记录(ALL 默认) 6．仅返回前面若干条记录(TOP) SELECT [TOP n [PERCENT]] 列名1[,…列名n] FROM 表名

30 总结 1．比较表达式作查询条件 表达式1 比较运算符 表达式2 2．逻辑表达式作查询条件 条件表达式 1 AND或OR 条件表达式 2
表达式1 比较运算符 表达式2 2．逻辑表达式作查询条件 条件表达式 1 AND或OR 条件表达式 2 或：NOT 条件表达式 3．使用BETWEEN关键字 表达式 [NOT] BETWEEN 表达式1 AND 表达式2 4．使用IN关键字 表达式 [NOT] IN (表达式1, 表达式2[,…表达式n]) 5．使用LIKE关键字(模糊查询) 表达式 [NOT] LIKE '格式串' 通配符： % _(下划线) [ ](封闭方括号) [^]