Microsoft Word - 第4章单表查询—教学设计.doc

Size: px

Start display at page:

Download "Microsoft Word - 第4章单表查询—教学设计.doc"

悦於
5 years ago
Views:

1 传智播客 MySQL 数据库入门教学设计课程名称 : MySQL 数据库入门授课年级 : 2015 年级授课学期 : 2015 学年第一学期教师姓名 : 某某老师 2014 年 04 月 09 日

2 课题名称第 4 章单表查询计划学时 7 课时内容分析教学目标及基本要求重点及措施通过前面章节的学习, 我们知道如何对数据进行添加修改删除等操作数据库还有一项重要的操作就是查询数据, 查询数据分为单表查询和多表查询, 本章将针对单表查询的相关操作进行详细讲解要求学生熟悉 SELECT 语句和一些简单查询, 学会带 IN 关键字的查询带 BETWEEN AND 关键字的查询带 LIKE 关键字的查询以及高级查询中的聚合函数和分组查询教学重点 : 按条件查询带 IN 关键字的查询带 BETWEEN AND 关键字的查询带 LIKE 关键字的查询使用聚合函数的查询和分组查询难点及措施教学难点 : 分组查询教学方式教学采用教师课堂讲授为主, 使用教学 PPT 讲解教学过程第一课时 (SELECT 语句查询所有字段查询指定字段 ) SELECT 语句 ² 什么是 SELECT 语句 MySQL 从数据表中查询数据的基本语句是 SELECT 语句 ² SELECT 语句的基本语法格式 SELECT [DISTINCT] * { 字段名 1, 字段名 2, 字段名 3, } [WHERE 条件表达式 1] [GROUP BY 字段名 [HAVING 条件表达式 2]] [ORDER BY 字段名 [ASC DESC]] [LIMIT [OFFSET] 记录数 ] l SELECT [DISTINCT] * { 字段名 1, 字段名 2, }: 字段 1, 字段 2 表示从表中查询的指定字段, 星号 ( * ) 通配符表示表中所有字段, 两者为互斥关系, 任选其一 DISTINCT 是可选参数, 用于剔除查询结果中重复的数据 l : 表示从指定的表中查询数据 l WHERE 条件表达式 1: WHERE 是可选参数, 用于指定查询条件 l GROUP BY 字段名 [HAVING 条件表达式 2]: GROUP BY 是可选参数, 用于将查询结果按照指定字段进行分组, HAVING 也是可选参数, 用于对分组后的结果进行过滤 l ORDER BY 字段名 [ASC DESC]: ORDER BY 是可选参数, 用于将查询结果按照指定字段进行排序排序方式由参数 ASC 或 DESC 控制, 其中 ASC 表示按升序进行排列,DESC 表示按降序进行排列如果不指定参数, 默认为升序排列 l LIMIT [OFFSET] 记录数 : LIMIT 是可选参数, 用于限制查询结果

3 的数量 LIMIT 后面可以跟 2 个参数, 第一个参数 OFFSET 表示偏移量, 如果偏移量为 0 则从查询结果的第一条记录开始, 偏移量为 1 则从查询结果的中第二条记录开始以此类推 OFFSET 为可选值, 如果不指定其默认值为 0 第二个参数记录数表示返回查询记录的条数查询所有字段 MySQL 中有两种方式, 接下来将针对这两种方式进行详细地讲解 ² 在 SELECT 语句中指定所有字段其语法格式如下 : SELECT 字段名 1, 字段名 2, ² 在 SELECT 语句中使用星号 ( * ) 通配符代替所有字段其语法格式如下所示 : SELECT * ; 查询指定字段查询数据时, 可以在 SELECT 语句的字段列表中指定要查询的字段, 这种方式只针对部分字段进行查询, 不会查询所有字段, 其语法格式如下所示 : SELECT 字段名 1, 字段名 2, 第二课时 ( 带关系运算符的查询带 IN 关键字的查询带 BETWEEN AND 关键字的查询空值查询带 DISTINCT 关键字的查询带 LIKE 关键字的查询带 AND 关键字的多条件查询和带 OR 关键字的多条件查询 ) 带关系运算符的查询在 MySQL 中, 提供了一系列的关系运算符, 在 WHERE 子句中可以使用关系运算符连接操作数作为查询条件对数据进行过滤, 常见的关系运算符如表 4-1 所示 : 关系运算符带 IN 关键字的查询表 4-1 关系运算符说明 = 等于 <> 不等于!= 不等于 < 小于 <= 小于等于 > 大于 >= 大于等于 IN 关键字用于判断某个字段的值是否在指定集合中, 如果字段的值在集合中, 则满足条件, 该字段所在的记录将被查询出来其语法格式如下所示 : SELECT * 字段名 1, 字段名 2, WHERE 字段名 [NOT] IN ( 元素 1, 元素 2, ) 在上面的语法格式中, 元素 1 元素 2 表示集合中的元素, 即指定的条件范围 NOT 是可选参数, 使用 NOT 表示查询不在 IN 关键字指定集合范围

4 中的记录带 BETWEEN AND 关键字的查询 BETWEEN AND 用于判断某个字段的值是否在指定的范围之内, 如果字段的值在指定范围内, 则满足条件, 该字段所在的记录将被查询出来, 反之则不会被查询出来其语法格式如下所示 : SELECT * { 字段名 1, 字段名 2, } WHERE 字段名 [NOT] BETWEEN 值 1 AND 值 2 在上面的语法格式中, 值 1 表示范围条件的起始值, 值 2 表示范围条件的结束值 NOT 是可选参数, 使用 NOT 表示查询指定范围之外的记录空值查询在 MySQL 中, 使用 IS NULL 关键字来判断字段的值是否为空值, 其语法格式如下所示 : SELECT * 字段名 1, 字段名 2, WHERE 字段名 IS [NOT] NULL 在上面的语法格式中, NOT 是可选参数, 使用 NOT 关键字用于判断字段不是空值带 DISTINCT 关键字的查询有时候, 出于对数据的分析需求, 需要去掉查询记录中重复的值, 在 SELECT 语句中, 可以使用 DISTINCT 关键字去掉查询记录中重复值的功能, 其语法格式如下 : SELECT DISTINCT 字段名 ; 在上面的语法格式中, 字段名表示要去除重复记录的字段带 LIKE 关键字的查询有时候我们需要对字符串进行模糊查询,MySQL 中提供了 LIKE 关键字用以判断两个字符串是否相匹配其语法格式如下所示 : SELECT * { 字段名 1, 字段名 2, } WHERE 字段名 [NOT] LIKE ' 匹配字符串 '; 在上面的语法格式中,NOT 是可选参数, 使用 NOT 表示查询与指定字符串不匹配的记录匹配字符串指定用来匹配的字符串, 其值可以是一个普通字符串, 也可以是包含百分号 (%) 和下划线 (_) 的通配字符串百分号和下划线统称为通配符, 它们在通配字符串中有特殊含义, 两者的作用如下所示 : ² 百分号 (%) 通配符 : l 匹配任意长度的字符串, 包括空字符串例如, 通配字符串 c% 匹配以字符 c 开始, 任意长度的字符串, 如 ct cut current 等 l 百分号通配符可以出现在通配字符串的任意位置例如, 通配字符 'w%g', 这时的百分号通配符可以匹配在字符 w 和 g 之间的任意个数的字符, 如 wuyong wusong l 通配字符串中可以出现多个百分号通配符例如, 通配字符 '%y%',

5 通配字符串中的字符 y 前后各有一个百分号通配符, 它匹配包含字符 y 的字符串, 无论 y 在字符串的什么位置, 如 wuyong yanqing l LIKE 之前可以使用 NOT 关键字, 用来查询与指定通配字符串不匹配的记录 ² 下划线 (_) 通配符 : 下划线通配符只匹配单个字符, 如果要匹配多个字符, 需要使用多个下划线通配符例如, 字符串 cu_ 匹配以字符串 cu 开始, 长度为 3 的字符串, 如 cut cup, 字符串 c l 匹配在字符 c 和 l 之间包含两个字符的字符串, 如 cool coal 等带 AND 关键字的多条件查询在使用 SELECT 语句查询数据时, 有时为了使查询结果更加精确, 可以使用多个查询条件在 MySQL 中, 提供了一个 AND 关键字, 使用 AND 关键字可以连接两个或者多个查询条件, 只有满足所有条件的记录才会被返回其语法格式如下所示 : SELECT * { 字段名 1, 字段名 2, } WHERE 条件表达式 1 AND 条件表达式 2 [ AND 条件表达式 n]; 从上面的语法格式可以看到, 在 WHERE 关键字后面跟了多个条件表达式, 每两个条件表达式之间用 AND 关键字分隔带 OR 关键字的多条件查询在使用 SELECT 语句查询数据时, 可以使用 OR 关键字连接多个查询条件与 AND 关键字不同, 使用 OR 关键字时, 只要记录满足任意一个条件就会被查询出来其语法格式如下所示 : SELECT * { 字段名 1, 字段名 2, } WHERE 条件表达式 1 OR 条件表达式 2 [ OR 条件表达式 n]; 聚合函数 ² 什么是聚合函数第三课时 ( 聚合函数对查询结果排序 ) 实际开发中, 经常需要对某些数据进行统计, 例如统计某个字段的最大值最小值平均值等等, 像这样用于对一组值进行统计, 并返回唯一值, 这样的函数就被称为聚合函数 MySQL 中提供的聚合函数如表 4-2 所示函数名称 COUNT() SUM() AVG() MAX() MIN() ² 聚合函数的用法 l COUNT() 函数表 4-2 聚合函数作用返回某列的行数返回某列值的和返回某列的平均值返回某列的最大值返回某列的最小值

6 COUNT() 函数用来可以求出表中有多少条记录, 其语法格式如下所示 : SELECT COUNT(*) l SUM() 函数 SUM() 是求和函数, 用于求出表中某个字段所有值的总和, 其语法格式如下 : SELECT SUM( 字段名 ) ; l AVG() 函数 AVG() 函数用于求出某个字段所有值的平均值, 其语法格式如下所示 : SELECT AVG( 字段名 ) FROM student; l MAX() 函数 MAX() 函数是求最大值的函数, 用于求出某个字段的最大值, 其语法格式如下所示 : SELECT MAX(grade) FROM student; l MIN() 函数 MIN() 函数是求最小值的函数, 用于求出某个字段的最小值, 其语法格式如下所示 : SELECT MIN(grade) FROM student; 对查询结果排序从表中查询出来的数据可能是无序的, 或者其排列顺序不是用户期望的为了使查询结果满足用户的要求, 可以使用 ORDER BY 对查询结果进行排序, 其语法格式如下所示 : SELECT 字段名 1, 字段名 2, ORDER BY 字段名 1 [ASC DESC], 字段名 2 [ASC DESC] 参数 ASC 表示按照升序进行排序,DESC 表示按照降序进行排序默认情况下, 按照 ASC 方式进行排序第四课时 ( 分组查询使用 LIMIT 限制查询结果的数量函数 ( 列表 )) 分组查询 ² 分组查询的定义在 MySQL 中, 可以使用 GROUP BY 按某个字段或者多个字段中的值进行分组, 字段中值相同的为一组, 其语法格式如下所示 : SELECT 字段名 1, 字段名 2, GROUP BY 字段名 1, 字段名 2, [HAVING 条件表达式 ]; HAVING 关键字指定条件表达式对分组后的内容进行过滤需要注意的是, GROUP BY 一般和聚合函数一起使用, 如果查询的字段出现在 GROUP BY 后, 却没有包含在聚合函数中, 该字段显示的是分组后的第一条记录的值 ² 分组查询的使用 l 单独使用 GROUP BY 分组 : 单独使用 group by 关键字, 查询的是每个分组中的一条记录例 4-31 查询 student 表中的记录, 按照 gender 字段值进行分组,SQL 语句如下所示 : SELECT * FROM student GROUP BY gender; 执行结果如下所示 :

7 mysql> SELECT * FROM student GROUP BY gender; id name grade gender 8 yanqing 90 NULL 4 husanniang 88 女 1 songjiang 40 男 3 rows in set (0.00 sec) l GROUP BY 和聚合函数一起使用可以统计出某个或者某些字段在一个分组中的最大值最小值平均值等等例 4-32 将 student 表按照 gender 字段值进行分组查询, 计算出每个分组中各有多少名学生,SQL 语句如下所示 : SELECT COUNT(*),gender FROM student GROUP BY gender; 执行结果如下所示 : mysql> SELECT COUNT(*),gender FROM student GROUP BY gender; COUNT(*) gender NULL 2 女 5 男 rows in set (0.00 sec) l GROUP BY 和 HAVING 关键字一起使用 HAVING 关键字和 WHERE 关键字的作用相同, 都用于设置条件表达式对查询结果进行过滤, 两者的区别在于,HAVING 关键字后可以跟聚合函数, 而 WHERE 关键字不能通常情况下 HAVING 关键字都和 GROUP BY 一起使用, 用于对分组后的结果进行过滤例 4-33 将 student 表按照 gender 字段进行分组查询, 查询出 grade 字段值之和小于 300 的分组,SQL 语句如下所示 : SELECT sum(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300; 执行结果如下所示 : mysql> SELECT sum(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300; sum(grade) gender NULL 154 女 rows in set (0.00 sec)

8 使用 LIMIT 限制查询结果的数量 MySQL 中提供了一个关键字 LIMIT, 可以指定查询结果从哪一条记录开始以及一共查询多少条信息, 其语法格式如下所示 : SELECT 字段名 1, 字段名 2, LIMIT [OFFSET,] 记录数在上面的语法格式中,LIMIT 后面可以跟 2 个参数, 第一个参数 OFFSET 表示偏移量, 如果偏移量为 0 则从查询结果的第一条记录开始, 偏移量为 1 则从查询结果的中第二条记录开始以此类推 OFFSET 为可选值, 如果不指定其默认值为 0 第二个参数记录数表示返回查询记录的条数函数 ( 列表 ) MySQL 中的函数包括数学函数字符串函数日期和时间函数条件判断函数加密函数等等接下来通过 5 张表对其中一些常用函数的作用进行说明

9 为表取别名第五课时 ( 为表取别名为字段取别名 ) 在查询操作时, 如果表名很长使用起来就不太方便, 这时可以为表取一个别名, 用这个别名来代替表的名称 MySQL 中为表起别名的格式如下所示 : SELECT * [AS] 别名 ; 在上面的语法格式中,AS 关键字用于指定表名的别名, 它可以省略不写例 4-38 为 student 表起一个别名 s, 并查询 student 表中 gender 字段值为女的记录,SQL 语句如下所示 : SELECT * FROM student AS s WHERE s.gender=' 女 '; 在上面的执行语句中, student AS s 表示 student 表的别名为 s,s.gender 表示 student 表的 gender 字段, 执行结果如下所示 : mysql> SELECT * FROM student AS s WHERE s.gender=' 女 '; id name grade gender 4 husanniang 88 女 5 sunerniang 66 女 2 rows in set (0.01 sec) 为字段取别名在前面的查询操作中, 每条记录中的列名都是定义表时的字段名, 有时为了显示查询结果更加直观, 可以为字段取一个别名,MySQL 中为字段起别名的格式如下所示 : SELECT 字段名 [AS] 别名 [, 字段名 [AS] 别名, ] ; 在上面的语法格式中, 为字段名指定别名的 AS 关键字也可以省略不写例 4-39 查询 student 表中的所有记录的 name 和 gender 字段值, 并为这两个字段其别名 stu_name 和 stu_gender,sql 语句如下所示 : SELECT name AS stu_name,gender stu_gender FROM student; 执行结果如下所示 : mysql> SELECT name AS stu_name,gender stu_gender FROM student; stu_name stu_gender songjiang 男 wuyong 男 qinming 男 husanniang 女 sunerniang 女 wusong 男 linchong 男 yanqing NULL rows in set (0.02 sec)

10 第六课时 ( 总结, 上机练习 ) 1 总结本章内容 2 通过题库发放相关测试题, 检查学生掌握情况 3 上机练习主要针对本章中需要重点掌握的知识点, 以及在程序中容易出错的内容进行练习, 通过上机练习可以考察同学对知识点的掌握情况, 对代码的熟练程度上机一 :( 考察知识点为简单查询 ) 针对 student 表进行查询操作 : id name grade gender 1 songjiang 40 男 2 wuyong 100 男 3 qinming 90 男 4 husanniang 88 女 5 sunerniang 66 女 6 wusong 86 男 7 linchong 92 男 8 yanqing 90 NULL 请按照以下要求编写查询语句要求如下 : 1) 查询 student 表中的所有记录 2) 在 SELECT 语句中使用星号 ( * ) 通配符查询 student 表中的所有字段 3) 使用 SELECT 语句查询 student 表中 name 字段和 gender 字段的数据第七课时 ( 上机练习 ) 上机一 :( 考察知识点为条件查询 ) 针对 student 表进行查询操作 : id name grade gender 1 songjiang 40 男 2 wuyong 100 男 3 qinming 90 男 4 husanniang 88 女 5 sunerniang 66 女 6 wusong 86 男 7 linchong 92 男 8 yanqing 90 NULL

11 请按照以下要求编写查询语句要求如下 : 1) 查询 student 表中 id 值在 2 和 5 之间的学生姓名 2) 查询 student 表中 gender 字段的值, 查询记录不能重复 3) 查询 student 表中 name 字段值以字符 w 开始, 以字符 g 结束的学生 id 4) 查询 student 表中 name 字段值包含 7 个字符, 并且以字符串 ing 结束的记录上机二 :( 考察知识点为条件查询 ) 针对 student 表进行查询操作 : id name grade gender 1 songjiang 40 男 2 wuyong 100 男 3 qinming 90 男 4 husanniang 88 女 5 sunerniang 66 女 6 wusong 86 男 7 linchong 92 男 8 yanqing 90 NULL 请按照以下要求编写查询语句要求如下 : 1) 查询 student 表中一共有多少条记录 2) 求出 student 表中 grade 字段的平均值 3) 将 student 表按照 gender 字段值进行分组查询, 计算出每个分组中各有多少名学生思考题和习题见教材配套的习题教学后记

Microsoft Word - 第4章单表查询—样章.doc

Microsoft Word - 第4章单表查询—样章.doc 第 4 章单表查询学习目标 u 掌握简单查询, 会使用 SELECT 语句查询所有字段和指定的字段 u 掌握按条件查询, 会使用运算符以及不同的关键字进行查询 u 掌握高级查询, 会使用聚合函数查询分组查询等 u 学会为表和字段起别名通过前面章节的学习, 我们知道如何对数据进行添加修改删除等操作, 在数据库中还有一个更重要的操作就是查询数据, 查询数据是指从数据库中获取所需要的数据, 用户可以根据自己对数据的需求来查询不同的数据

Microsoft Word - 第4章 单表查询—教学设计.doc

Microsoft Word - 第4章单表查询—教学设计.doc