传智播客 MySQL 数据库入门 教学设计 课程名称 : MySQL 数据库入门 授课年级 : 2015 年级 授课学期 : 2015 学年第一学期 教师姓名 : 某某老师 2014 年 04 月 09 日
课题名称第 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 是可选参数, 用于限制查询结果
的数量 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 关键字指定集合范围
中的记录 带 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%',
通配字符串中的字符 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 聚合函数 作用 返回某列的行数 返回某列值的和 返回某列的平均值 返回某列的最大值 返回某列的最小值
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; 执行结果如下所示 :
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 +----------+--------+ 1 NULL 2 女 5 男 +----------+--------+ 3 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 +------------+--------+ 90 NULL 154 女 +------------+--------+ 2 rows in set (0.00 sec)
使用 LIMIT 限制查询结果的数量 MySQL 中提供了一个关键字 LIMIT, 可以指定查询结果从哪一条记录开始以及一共查询多少条信息, 其语法格式如下所示 : SELECT 字段名 1, 字段名 2, LIMIT [OFFSET,] 记录数在上面的语法格式中,LIMIT 后面可以跟 2 个参数, 第一个参数 OFFSET 表示偏移量, 如果偏移量为 0 则从查询结果的第一条记录开始, 偏移量为 1 则从查询结果的中第二条记录开始 以此类推 OFFSET 为可选值, 如果不指定其默认值为 0 第二个参数 记录数 表示返回查询记录的条数 函数 ( 列表 ) MySQL 中的函数包括数学函数 字符串函数 日期和时间函数 条件判断函数 加密函数等等 接下来通过 5 张表对其中一些常用函数的作用进行说明
为表取别名 第五课时 ( 为表取别名 为字段取别名 ) 在查询操作时, 如果表名很长使用起来就不太方便, 这时可以为表取一个 别名, 用这个别名来代替表的名称 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 +------------+------------+ 8 rows in set (0.02 sec)
第六课时 ( 总结, 上机练习 ) 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
请按照以下要求编写查询语句 要求如下 : 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 字段值进行分组查询, 计算出每个分组中各有 多少名学生 思考题和 习题 见教材配套的习题 教学后记