MySQL 课程教案
版 本 号 :MySQL1.0
密 级 : 受 控 文 档
课
程
标
准
化
2016 年 12 月 8 日
MySQL 课程教案
1.文档属性
文档属性
项目/任务名称:
项目/任务编号:
文档名称:
文档编号:
文档状态:
作 者:
文档评审通过日期:
评审负责人签字:
参考模板:
2.文档变更过程
修正日期
版本
内容
实训部开发组
修正人
描述
第 2 页 共 239 页
MySQL 课程教案
目 录
第 1 章
总述....................................................................................................................................... 11
1.1 编写目的....................................................................................................................................... 11
1.2 总体课程安排............................................................................................................................... 11
1.3 参考资料....................................................................................................................................... 11
第 2 章
数据库系统........................................................................................................................... 12
2.1 章节目标....................................................................................................................................... 12
2.2 基础概念....................................................................................................................................... 12
2.3 数据库技术构成........................................................................................................................... 12
2.3.1 数据库系统........................................................................................................................... 12
2.3.2
SQL 语言 .............................................................................................................................. 13
2.3.3 数据库访问技术................................................................................................................... 13
2.4 关系数据库基本概念................................................................................................................... 13
2.5 常见的数据库系统....................................................................................................................... 14
2.6 作业 1:简答................................................................................................................................ 15
第 3 章
WINDOWS 平台下安装与配置 MYSQL ......................................................................... 16
3.1 章节目标....................................................................................................................................... 16
3.2 WINDOWS 下 MYSQL 的安装 ...................................................................................................... 16
3.3 MYSQL 服务的开启与停止 ........................................................................................................ 17
3.4 命令行登录 MYSQL .................................................................................................................... 18
3.5
NAVICAT 连接数据库 ................................................................................................................... 19
3.6 本章作业....................................................................................................................................... 20
3.6.1 作业1:简答........................................................................................................................ 20
第 4 章
管理数据库........................................................................................................................... 21
4.1 章节目标....................................................................................................................................... 21
4.2 创建数据库................................................................................................................................... 21
4.3 删除数据库................................................................................................................................... 22
4.4 本章作业....................................................................................................................................... 24
4.4.1 作业1 简答........................................................................................................................... 24
4.4.2 作业2 课堂上机................................................................................................................... 24
4.4.3 课后作业............................................................................................................................... 24
第 5 章
创建、修改和删除表........................................................................................................... 25
5.1 章节目标....................................................................................................................................... 25
5.2 基本概念....................................................................................................................................... 25
5.3 创建表........................................................................................................................................... 25
5.3.1 创建表的语法结构............................................................................................................... 26
5.3.2 设置表的主键....................................................................................................................... 28
5.3.3 设置表的外键....................................................................................................................... 29
第 3 页 共 239 页
MySQL 课程教案
5.3.4 设置表的非空约束............................................................................................................... 30
5.3.5 设置表的属性值自动增加................................................................................................... 31
5.3.6 设置表的属性的默认值....................................................................................................... 31
5.4 查看表结构................................................................................................................................... 31
5.4.1 查看表基本结构语句DESCRIBE ....................................................................................... 32
5.4.2 查看表详细结构语句SHOW CREATE TABLE .................................................................. 33
5.5 修改表........................................................................................................................................... 34
1,修改表名......................................................................................................................... 34
2,修改字段的数据类型..................................................................................................... 35
3,修改字段名..................................................................................................................... 36
4,增加字段......................................................................................................................... 38
5,删除字段......................................................................................................................... 38
5.6 删除表........................................................................................................................................... 41
1,删除没有被关联的普通表............................................................................................. 41
2,删除被其他表关联的父表............................................................................................. 41
5.7 本章作业....................................................................................................................................... 42
5.5.1
5.5.2
5.5.3
5.5.4
5.5.5
5.6.1
5.6.2
第 6 章
插入、更新与删除数据....................................................................................................... 44
6.1 章节目标....................................................................................................................................... 44
6.2 插入数据....................................................................................................................................... 44
6.2.1 为表的所有字段插入数据................................................................................................... 44
6.2.2 为表的指定字段插入数据................................................................................................... 47
6.2.3 同时插入多条记录............................................................................................................... 48
6.2.4 将查询结果插入到表中....................................................................................................... 48
6.3 更新数据....................................................................................................................................... 49
6.4 删除数据....................................................................................................................................... 50
DELETE 语句可以同时删除多条记录。 ........................................................................... 51
6.5 本章作业....................................................................................................................................... 53
6.4.1
第 7 章
单表查询............................................................................................................................... 54
7.1 本章目标....................................................................................................................................... 54
7.2 基本查询语句............................................................................................................................... 54
7.3 查询所有字段............................................................................................................................... 56
7.4 查询指定字段............................................................................................................................... 59
7.5 查询指定记录............................................................................................................................... 60
7.6 带 IN 关键字的查询 .................................................................................................................... 61
7.7 带 BETWEEN AND 的范围 ........................................................................................................ 62
7.8 带 LIKE 的字符匹配查询 ........................................................................................................... 64
7.9 查询空值....................................................................................................................................... 66
7.10
多条件查询........................................................................................................................... 67
7.11 对查询结果排序........................................................................................................................... 68
用 LIMIT 限制查询结果的数量 ......................................................................................... 70
7.12
7.13
本章作业............................................................................................................................... 71
7.13.1 作业1:单表查询................................................................................................................ 71
第 4 页 共 239 页
MySQL 课程教案
第 8 章
分组查询............................................................................................................................... 74
8.2.1
8.2.2
8.2.3
8.2.4
8.1 章节目标....................................................................................................................................... 74
8.2 聚合函数....................................................................................................................................... 74
SUM 求和 ............................................................................................................................. 74
AVG 求平均值 ..................................................................................................................... 75
MAX 和MIN 求最大值/最小值 .......................................................................................... 76
COUNT 计数 ....................................................................................................................... 77
8.3 分组统计....................................................................................................................................... 78
8.4 使用 HAVING 对分组过滤 ......................................................................................................... 82
8.5 小结............................................................................................................................................... 83
8.6 本章作业....................................................................................................................................... 83
8.6.1 作业1:聚合函数................................................................................................................ 83
8.6.2 作业2:分组统计................................................................................................................ 84
8.6.3 作业3:分组过滤................................................................................................................ 84
第 9 章
多表查询............................................................................................................................... 85
9.1 章节目标....................................................................................................................................... 85
9.2 多表关联查询............................................................................................................................... 85
9.3 交叉连接(CROSS JOIN) ........................................................................................................ 85
9.4 内连接........................................................................................................................................... 89
9.5 外连接........................................................................................................................................... 90
9.5.1 左外连接............................................................................................................................... 90
9.5.2 右外连接............................................................................................................................... 92
9.5.3 全连接................................................................................................................................... 94
9.6
AS-表和字段的别名 .................................................................................................................... 94
9.7 小结............................................................................................................................................... 95
9.8 本章作业....................................................................................................................................... 95
9.8.1 作业1:多表关联查询........................................................................................................ 95
第 10 章
子查询................................................................................................................................... 97
10.1
10.2
10.3
章节目标............................................................................................................................... 97
子查询概念........................................................................................................................... 97
比较子查询........................................................................................................................... 99
10.3.1 单值比较子查询................................................................................................................... 99
10.3.2 批量比较查询..................................................................................................................... 100
10.4
IN 和 NOT IN ............................................................................................................................. 102
10.5 EXISTS 和 NOT EXISTS .......................................................................................................... 104
小结..................................................................................................................................... 106
10.6
10.7
作业..................................................................................................................................... 106
10.7.1 作业1:子查询.................................................................................................................. 106
第 11 章
常用函数............................................................................................................................. 109
11.1
11.2
章节目标............................................................................................................................. 109
为什么要使用函数............................................................................................................. 109
第 5 页 共 239 页
MySQL 课程教案
11.3
11.4
11.5
数字函数............................................................................................................................. 109
绝对值函数ABS(x)和返回圆周率的函数PI()....................................................... 109
11.3.1
平方根函数SQRT(x)和求余函数MOD(x,y)............................................................. 110
11.3.2
获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)............................................. 110
11.3.3
获取随机数的函数RAND()和RAND(x)..................................................................... 111
11.3.4
四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)..................................... 111
11.3.5
符号函数SIGN(x)...................................................................................................... 112
11.3.6
幂运算函数POW(x,y)、POWER(x,y)和EXP(x)....................................................... 113
11.3.7
对数运算函数LOG(x)和LOG10(x)函数................................................................... 113
11.3.8
角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)....................................... 114
11.3.9
11.3.10 正弦函数ASIN(x)和反正弦函数ASIN(x)............................................................... 115
11.3.11 余弦函数COS(x)和反余弦函数ACOS(x)................................................................. 115
11.3.12 正切函数、反正切函数和余切函数......................................................................... 116
字符串函数......................................................................................................................... 116
计算字符串字符数的函数和字符串长度的函数 (常用)....................................... 116
11.4.1
合并字符串的函数CONCAT(s1,s2,…)和CONCAT_WS(x,s1,s2,…)(常用)......... 117
11.4.2
替换字符串的函数INSERT(s1,x,len,s2).............................................................. 118
11.4.3
字母大小写转换函数 (常用)................................................................................... 118
11.4.4
获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)(常用)......................... 119
11.4.5
填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)................................... 120
11.4.6
删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)(常用)..................................... 120
11.4.7
删除指定字符串的函数TRIM(s1 FROM s)(重点).................................................. 121
11.4.8
重复生成字符串的函数REPEAT(s,n) (重点)........................................................ 122
11.4.9
11.4.10 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)............................................... 122
11.4.11 比较字符串大小的函数STRCMP(s1,s2).................................................................. 123
11.4.12 获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)(重点)........................... 123
11.4.13 匹配子串开始位置的函数......................................................................................... 124
11.4.14 字符串逆序的函数REVERSE(s)................................................................................ 124
11.4.15 返回指定位置的字符串的函数................................................................................. 125
11.4.16 返回指定字符串位置的函数FIELD(s,s1,s2,…).................................................. 125
11.4.17 返回子串位置的函数FIND_IN_SET(s1,s2)............................................................ 125
11.4.18 选取字符串的函数MAKE_SET(x,s1,s2,…)函数.................................................... 126
日期和时间函数................................................................................................................. 126
获取当前日期的函数和获取当前时间的函数(重点)............................................. 127
获取当前日期和时间的函数(重点)......................................................................... 127
获取月份的函数(重点)............................................................................................. 128
获取星期的函数(重点)............................................................................................. 128
获取天数函数(重点)................................................................................................. 129
获取年份、季度、月、小时、分钟和秒钟的函数(重点)..................................... 130
获取日期的指定值函数EXTRACT(type FROM date)(重点).............................. 131
时间和秒钟转换的函数............................................................................................. 131
将日期和时间格式化的函数(重点)......................................................................... 132
条件判断函数..................................................................................................................... 136
IF(expr,v1,v2)函数(重点)..................................................................................... 136
11.5.1
11.5.2
11.5.3
11.5.4
11.5.5
11.5.6
11.5.7
11.5.8
11.5.9
11.6
11.6.1
第 6 页 共 239 页
MySQL 课程教案
11.6.2
11.6.3
11.7
11.7.1
11.7.2
11.7.3
11.7.4
11.8
11.8.1
11.8.2
11.8.3
11.8.4
11.9
11.9.1
11.9.2
11.9.3
11.9.4
11.9.5
11.9.6
11.9.7
11.10
11.11
IFNULL(v1,v2)函数................................................................................................... 137
CASE函数.................................................................................................................... 137
系统信息函数..................................................................................................................... 137
获取MySQL版本号、连接数、数据库名的函数(可讲)......................................... 137
获取用户名的函数(可讲)......................................................................................... 138
获取字符串的字符集和排序方式的函数................................................................. 139
获取最后一个自动生成的ID值的函数................................................................... 139
加密函数............................................................................................................................. 140
加密函数PASSWORD(str)(重点).............................................................................. 140
加密函数MD5(str)(重点)........................................................................................ 140
加密函数ENCODE(str,pswd_str)(重点)................................................................ 141
解密函数DECODE(crypt_str,pswd_str)(重点).................................................... 141
其他函数............................................................................................................................. 141
格式化函数FORMAT(x,n).......................................................................................... 142
不同进制的数字进行转换的函数............................................................................. 142
IP地址与数字相互转换的函数................................................................................ 142
加锁函数和解锁函数................................................................................................. 143
重复执行指定操作的函数......................................................................................... 144
改变字符集的函数..................................................................................................... 145
改变字段数据类型的函数(重点)............................................................................. 145
课上小练习......................................................................................................................... 146
课后作业............................................................................................................................. 147
第 12 章
视图..................................................................................................................................... 149
12.1
12.2
12.3
12.4
12.5
12.6
12.6.1
12.6.2
12.6.3
12.6.4
12.7
12.8
章节目标............................................................................................................................. 149
为什么要使用视图............................................................................................................. 149
什么是视图......................................................................................................................... 149
使用视图的好处................................................................................................................. 150
如何创建视图..................................................................................................................... 151
管理视图............................................................................................................................. 151
删除视图..................................................................................................................... 151
查看视图定义............................................................................................................. 152
修改视图..................................................................................................................... 152
课上小练习................................................................................................................. 155
总结..................................................................................................................................... 156
本章作业............................................................................................................................. 156
第 13 章
索引..................................................................................................................................... 158
13.1
13.2
13.3
13.4
13.5
13.6
章节目标............................................................................................................................. 158
为什么要使用索引............................................................................................................. 158
索引的概念......................................................................................................................... 158
索引的好处、坏处及使用原则......................................................................................... 159
索引的分类......................................................................................................................... 160
索引的创建语法................................................................................................................. 161
第 7 页 共 239 页
MySQL 课程教案
13.7
13.7.1
13.7.2
13.7.3
13.7.4
13.8
13.9
管理索引............................................................................................................................. 162
删除索引..................................................................................................................... 162
查看索引..................................................................................................................... 162
课上案例..................................................................................................................... 163
上机练习..................................................................................................................... 164
总结..................................................................................................................................... 165
本章作业............................................................................................................................. 165
第 14 章
触发器................................................................................................................................. 167
14.1
14.2
14.3
14.4
14.5
14.6
14.6.1
14.6.2
14.7
14.8
14.9
章节目标............................................................................................................................. 167
为什么要使用触发器......................................................................................................... 167
触发器的定义..................................................................................................................... 167
触发器 4 个要素................................................................................................................. 169
触发器的管理..................................................................................................................... 169
触发引用行变量................................................................................................................. 170
课程案例..................................................................................................................... 173
上机练习..................................................................................................................... 175
触发器的应用场合............................................................................................................. 176
总结..................................................................................................................................... 176
课后作业............................................................................................................................. 177
第 15 章
存储过程............................................................................................................................. 178
15.1
15.2
15.3
15.4
15.5
15.6.1
15.6.2
章节目标............................................................................................................................. 178
为什么使用存储过程......................................................................................................... 178
什么是存储过程................................................................................................................. 179
使用存储过程的优点......................................................................................................... 179
创建存储过程的语法格式................................................................................................. 179
15.5.1 in参数................................................................................................................................ 180
15.5.2 out参数.............................................................................................................................. 182
15.5.3 inout参数.......................................................................................................................... 184
存储过程的变量................................................................................................................. 185
变量之间的区别......................................................................................................... 185
变量赋值..................................................................................................................... 185
查看存储过程定义............................................................................................................. 186
调用存储过程..................................................................................................................... 186
删除存储过程..................................................................................................................... 186
流程控制语句..................................................................................................................... 186
BEGIN…END关键字.................................................................................................... 187
15.10.1
15.10.2 流程控制的使用-IF-ELSE......................................................................................... 187
15.10.3 流程控制的使用-CASE语法...................................................................................... 190
15.10.4 流程控制的使用WHILE语句..................................................................................... 193
存储过程与函数的比较..................................................................................................... 195
15.11.1 创建函数的语法......................................................................................................... 195
15.11.2 调用存储函数............................................................................................................. 196
15.6
15.7
15.8
15.9
15.10
15.11
第 8 页 共 239 页