目
录
第1章
数据库入门
.
...............................................1
1.1 数据管理技术的发展 .................................1
1.2 数据库技术的基本术语 .............................3
1.3 数据模型 .....................................................4
1.3.1 初识数据模型 .................................4
1.3.2 E-R图 ...............................................6
1.3.3 关系模型 .........................................7
1.4 常见的数据库产品 ...................................10
1.5 SQL简介 ....................................................11
1.6 MySQL安装与配置 ..................................12
1.6.1 获取MySQL ..................................12
1.6.2 安装MySQL ..................................15
1.6.3 配置MySQL ..................................17
1.6.4 管理MySQL服务 ..........................18
1.6.5 登录MySQL与密码设置 ..............19
1.6.6 配置环境变量 ...............................23
1.7 常用图形化管理工具 ...............................24
1.7.1 SQLyog ..........................................25
1.7.2 Navicat ...........................................26
1.8 上机实践：图书管理系统的E-R图
设计 ...........................................................28
1.9 本章小结 ...................................................31
1.10 课后习题 .................................................31
第2章
数据库和数据表的基本操作.................33
2.1 数据库的基本操作 ...................................33
2.1.1 创建数据库 ...................................33
2.1.2 查看数据库 ...................................35
2.1.3 选择数据库 ...................................37
2.1.4 修改数据库特征 ...........................37
MySQL数据库入门(第2版)
IV
2.1.5 删除数据库 ..................................................................................................38
2.2 数据类型 ..................................................................................................................39
2.2.1 数值类型 ......................................................................................................39
2.2.2 日期和时间类型 ..........................................................................................41
2.2.3 字符串类型 ..................................................................................................43
2.3 数据表的基本操作 ..................................................................................................46
2.3.1 创建数据表 ..................................................................................................46
2.3.2 查看数据表 ..................................................................................................47
2.3.3 修改数据表 ..................................................................................................50
2.3.4 删除数据表 ..................................................................................................57
2.4 表的约束 ..................................................................................................................58
2.4.1 非空约束 ......................................................................................................58
2.4.2 唯一约束 ......................................................................................................61
2.4.3 主键约束 ......................................................................................................64
2.4.4 默认值约束 ..................................................................................................67
2.5 自动增长 ..................................................................................................................70
2.6 上机实践：图书管理系统的数据库及相关数据表的创建 .................................71
2.7 本章小结 ..................................................................................................................74
2.8 课后习题 ..................................................................................................................74
第3章 数据操作.....................................................................................................................76
3.1 插入数据 ..................................................................................................................76
3.1.1 一次插入单条数据 ......................................................................................76
3.1.2 一次插入多条数据 ......................................................................................81
3.2 更新数据 ..................................................................................................................83
3.3 删除数据 ..................................................................................................................86
3.4 上机实践：图书表的数据操作 .............................................................................92
3.5 本章小结 ..................................................................................................................95
3.6 课后习题 ..................................................................................................................95
第4章
单表查询.....................................................................................................................97
4.1 SELECT语句 ............................................................................................................98
4.2 简单查询 ..................................................................................................................82
4.2.1 查询所有字段 ..............................................................................................82
4.2.2 查询指定字段 ............................................................................................101
4.2.3 查询去重数据 ............................................................................................102
4.3 条件查询 ................................................................................................................104
目
录
V
4.3.1 带比较运算符的查询 ................................................................................104
4.3.2 带逻辑运算符的查询 ................................................................................110
4.4 高级查询 ................................................................................................................114
4.4.1 聚合函数 ....................................................................................................114
4.4.2 分组查询 ....................................................................................................118
4.4.3 排序查询 ....................................................................................................119
4.4.4 限量查询 ....................................................................................................121
4.4.5 内置函数 ....................................................................................................123
4.5 设置别名 ................................................................................................................126
4.5.1 为数据表设置别名 ....................................................................................126
4.5.2 为字段设置别名 ........................................................................................127
4.6 上机实践：图书管理系统的单表查询 ...............................................................128
4.7 本章小结 ................................................................................................................131
4.8 课后习题 ................................................................................................................131
第5章
多表操作...................................................................................................................133
5.1 多表查询 ................................................................................................................133
5.1.1 交叉连接查询 ............................................................................................133
5.1.2 内连接查询 ................................................................................................135
5.1.3 外连接查询 ................................................................................................136
5.1.4 复合条件连接查询 ....................................................................................138
5.2 子查询 ....................................................................................................................139
5.2.1 IN关键字结合子查询 ................................................................................139
5.2.2 EXISTS关键字结合子查询 .......................................................................140
5.2.3 ANY关键字结合子查询 ............................................................................141
5.2.4 ALL关键字结合子查询 .............................................................................142
5.2.5 比较运算符结合子查询 ............................................................................142
5.3 外键约束 ................................................................................................................143
5.3.1 添加外键约束 ............................................................................................143
5.3.2 操作关联表 ................................................................................................145
5.3.3 删除外键约束 ............................................................................................148
5.4 上机实践：图书管理系统的多表操作 ...............................................................149
5.5 本章小结 ................................................................................................................151
5.6 课后习题 ................................................................................................................152
第6章
索引和视图...............................................................................................................154
6.1 索引 ........................................................................................................................154
6.1.1 索引概述 ....................................................................................................155
6.1.2 索引的创建 ................................................................................................156
6.1.3 索引的查看 ................................................................................................162
6.1.4 索引的删除 ................................................................................................165
6.2 视图 ........................................................................................................................167
6.2.1 视图概述 ....................................................................................................168
6.2.2 视图管理 ....................................................................................................168
6.2.3 视图数据操作 ............................................................................................178
6.3 上机实践：图书管理系统中索引和视图的应用 ...............................................180
6.4 本章小结 ................................................................................................................182
6.5 课后习题 ................................................................................................................182
第7章
事务...........................................................................................................................184
7.1 事务处理 ................................................................................................................184
7.1.1 事务概述 ....................................................................................................184
7.1.2 事务的基本操作 ........................................................................................185
7.1.3 事务的保存点 ............................................................................................188
7.2 事务的隔离级别 ....................................................................................................191
7.2.1 READ UNCOMMITTED...........................................................................191
7.2.2 READ COMMITTED ................................................................................195
7.2.3 REPEATABLE READ ................................................................................197
7.2.4 SERIALIZABLE ........................................................................................200
7.3 上机实践：图书管理系统中事务的应用 ...........................................................201
7.4 本章小结 ................................................................................................................203
7.5 课后习题 ................................................................................................................203
第8章
数据库编程...............................................................................................................205
8.1 存储过程 ................................................................................................................205
8.1.1 创建存储过程 ............................................................................................206
8.1.2 查看存储过程 ............................................................................................207
8.1.3 调用存储过程 ............................................................................................209
8.1.4 修改存储过程 ............................................................................................209
8.1.5 删除存储过程 ............................................................................................211
8.2 存储函数 ................................................................................................................211
8.2.1 创建存储函数 ............................................................................................211
8.2.2 查看存储函数 ............................................................................................213
8.2.3 调用存储函数 ............................................................................................214
8.2.4 删除存储函数 ............................................................................................215
8.3 变量 ........................................................................................................................215
8.3.1 系统变量 ....................................................................................................215
8.3.2 用户变量 ....................................................................................................218
8.3.3 局部变量 ....................................................................................................219
8.4 流程控制 ................................................................................................................220
8.4.1 判断语句 ....................................................................................................220
8.4.2 循环语句 ....................................................................................................223
8.4.3 跳转语句 ....................................................................................................227
8.5 错误处理 ................................................................................................................228
8.5.1 自定义错误名称 ........................................................................................228
8.5.2 自定义错误处理程序 ................................................................................229
8.6 游标 ........................................................................................................................231
8.6.1 游标的操作流程 ........................................................................................231
8.6.2 使用游标检索数据 ....................................................................................232
8.7 触发器 ....................................................................................................................235
8.7.1 触发器概述 ................................................................................................235
8.7.2 触发器的基本操作 ....................................................................................235
8.8 上机实践：数据库编程实战 ...............................................................................240
8.9 本章小结 ................................................................................................................245
8.10 课后习题 ..............................................................................................................246
第9章
数据库的管理和维护...............................................................................................247
9.1 数据备份与还原 ....................................................................................................247
9.1.1 数据的备份 ................................................................................................247
9.1.2 数据的还原 ................................................................................................250
9.2 用户管理 ................................................................................................................253
9.2.1 user表 ..........................................................................................................253
9.2.2 创建用户 ....................................................................................................256
9.2.3 删除用户 ....................................................................................................258
9.2.4 修改用户密码 ............................................................................................259
9.3 权限管理 ................................................................................................................263
9.3.1 MySQL的权限 ...........................................................................................263
9.3.2 授予权限 ....................................................................................................265
9.3.3 查看权限 ....................................................................................................266
9.3.4 收回权限 ....................................................................................................267
9.4 上机实践：图书管理系统数据库的管理 ...........................................................268
9.5 本章小结 ................................................................................................................269
9.6 课后习题 ................................................................................................................269
第10章
综合开发案例—图书借阅系统.............................................................................271
10.1 系统分析 ..............................................................................................................271
10.1.1 系统功能需求 ........................................................................................271
10.1.2 系统功能结构 ........................................................................................272
10.1.3 系统预览 ................................................................................................272
10.2 数据库设计 ..........................................................................................................274
10.2.1 实体设计 ................................................................................................274
10.2.2 E-R图设计 ..............................................................................................276
10.2.3 数据库逻辑结构设计 ............................................................................276
10.3 系统开发准备 ......................................................................................................277
10.3.1 预备知识 ................................................................................................278
10.3.2 开发环境搭建 ........................................................................................280
10.4 用户登录模块开发 ..............................................................................................282
10.4.1 用户登录 ................................................................................................282
10.4.2 注销登录 ................................................................................................285
10.5 新书推荐模块开发 ..............................................................................................287
10.5.1 查询新书 ................................................................................................287
10.5.2 借阅新书 ................................................................................................291
10.6 图书借阅模块开发 ..............................................................................................297
10.6.1 查询图书 ................................................................................................297
10.6.2 新增图书 ................................................................................................303
10.6.3 编辑图书 ................................................................................................306
10.7 当前借阅模块开发 ..............................................................................................308
10.7.1 查询当前借阅 ........................................................................................308
10.7.2 归还图书 ................................................................................................313
10.7.3 归还确认 ................................................................................................316
10.8 借阅记录模块开发 ..............................................................................................320
10.8.1 新增借阅记录 ........................................................................................320
10.8.2 查询借阅记录 ........................................................................................323
10.9 本章小结 ..............................................................................................................329
