| 作者 |
| 梁晶 |
| 丛书名 |
| 出版社 |
| 机械工业出版社 |
| ISBN |
| 9787111611066 |
| 简要 |
| 简介 |
| 内容简介书籍计算机书籍 本书系统介绍MySQL 5.7数据库管理系统的基础知识与开发工具。全书共分17章，内容包括数据库概述、安装和配置MySQL、操作MySQL数据库、MySQL存储引擎、数据类型、操作MySQL数据表、约束、函数、基本查询语句、进阶查询语句、视图、自定义函数、存储过程、触发器、索引、用户与权限、数据库恢复备份，最后介绍基于PHP语言和MySQL开发的企业项目开发案例。本书从初学者的角度，以丰富的实例、通俗易懂的语言、简单的图示，详细介绍MySQL使用和开发中重点用到的多种技术，可以让读者快速掌握MySQL数据库的使用方法。 |
| 目录 |
| 前 言 第1章 数据库概述 ·····································1 1.1 数据库基本概念 ·······························1 1.1.1 数据存储方式 ·······························1 1.1.2 什么是数据库 ············································2 1.1.3 数据库的存储结构 ····································2 1.1.4 数据库在开发中的作用 ···········································4 1.2 数据库技术 ··············································4 1.2.1 数据库系统 ·······························4 1.2.2 了解SQL语言 ·······································6 1.2.3 数据库访问技术 ···············································6 1.3 认识MySQL ···············································7 1.3.1 MySQL的优势 ······································7 1.3.2 MySQL的版本 ······································8 1.3.3 如何获取MySQL ···········································8 1.4 数据库的学习方法 ···········································10 1.5 小结 ·········································11 1.6 练习题 ·····································11 第2章 安装和配置MySQL ·········································12 2.1 MySQL 5.7的新特性 ·······································12 2.2 在不同平台上安装MySQL ·····································13 2.2.1 安装条件 ··············································13 2.2.2 在Windows系统上安装MySQL ········································13 2.2.3 在Linux系统上安装MySQL ································17 2.2.4 打开MySQL数据库 ··············································20 2.3 验证MySQL是否安装成功 ············································21 2.3.1 获取服务器状态 ············································22 2.3.2 执行简单的SQL 命令 ············································22 2.4 启动服务和登录MySQL 数据库 ·······································22 2.4.1 启动MySQL 服务 ··············································22 2.4.2 登录MySQL 数据库 ··············································23 2.4.3 配置Path 变量 ·····································25 2.5 配置MySQL 数据库 ········································26 2.5.1 使用配置向导 ······································26 2.5.2 手工更改配置 ······································30 2.6 使用MySQL Workbench 图形管理工具·····································30 2.7 使用免安装的MySQL ·····································31 2.8 小结 ·········································33 2.9 练习题 ·····································33 第3 章 操作MySQL 数据库 ········································ 34 3.1 创建数据库 ································34 3.1.1 基本语法 ··············································34 3.1.2 创建并查看数据库 ·······································35 3.1.3 创建指定字符集的数据库 ·····································36 3.2 修改数据库 ··········································37 3.2.1 基本语法 ··············································37 3.2.2 修改数据库的字符集 ·············································38 3.3 删除数据库 ·············································38 3.3.1 基本语法 ··············································38 3.3.2 删除数据库 ··········································39 3.4 选择数据库 ···········································40 3.5 使用图形界面管理数据库 ·····································40 3.6 综合案例 ·······························43 3.6.1 案例目的 ··············································43 3.6.2 案例演示过程 ······································44 3.7 小结 ·········································46 3.8 练习题 ·····································46 第4 章 MySQL 存储引擎与数据类型 ········································· 47 4.1 存储引擎 ·······························47 4.1.1 什么是存储引擎 ·········································47 4.1.2 MySQL 5.7 支持的存储引擎 ·································48 4.1.3 如何选择存储引擎 ··············································48 4.1.4 操作默认存储引擎 ·············································49 4.2 数据类型 ·······························49 4.2.1 整数类型 ··············································50 4.2.2 浮点数类型和定点数类型 ·····································51 4.2.3 日期与时间类型 ········································52 4.2.4 字符串和二进制类型 ··········································55 4.3 综合案例 ·······························59 4.3.1 案例目的 ··············································59 4.3.2 案例演示过程 ······································59 4.4 小结 ·········································60 4.5 练习题 ·····································60 第5 章 操 作 表 ········································ 62 5.1 表的基本概念 ··································62 5.2 创建表 ·····································62 5.2.1 基本语法 ··············································62 5.2.2 在指定的数据库中创建表 ·····································63 5.2.3 查看表结构 ··········································64 5.3 修改表 ·····································65 5.3.1 基本语法 ··············································66 5.3.2 添加字段 ··············································66 5.3.3 修改字段数据类型 ································67 5.3.4 删除字段 ··············································68 5.3.5 修改字段名称 ······································69 5.3.6 修改表名 ··············································69 5.4 删除表 ·····································70 5.4.1 基本语法 ··············································70 5.4.2 删除表 ·································70 5.5 使用图形界面操作数据表 ·································71 5.6 综合案例 ·······························75 5.6.1 案例目的 ··············································75 5.6.2 案例演示过程 ······································75 5.7 小结 ········································79 5.8 练习题 ·····································79 第6 章 约 束 ··········································· 80 6.1 约束的作用 ······································80 6.2 主键约束 ·······························80 6.2.1 选取设置主键约束的字段 ·····································80 6.2.2 在创建表时设置主键约束 ·····································81 6.2.3 在创建表时设置复合主键 ·····································82 6.2.4 在修改表时添加主键约束 ·····································83 6.2.5 使用图形界面操作主键约束 ·································83 6.3 外键约束 ·······························84 6.3.1 选取设置外键约束的字段 ·····································84 6.3.2 在创建表时设置外键约束 ·····································85 6.3.3 在修改表时添加外键约束 ·····································86 6.3.4 删除外键约束 ······································87 6.3.5 使用图形界面操作外键约束 ·································88 6.4 唯一约束 ·······························89 6.4.1 选取设置唯一约束的字段 ·····································89 6.4.2 在创建表时设置唯一约束 ·····································89 6.4.3 在修改表时添加唯一约束 ·····································90 6.4.4 删除唯一约束 ······································90 6.4.5 使用图形界面操作唯一约束 ·································91 6.5 检查约束 ·······························91 6.5.1 选取设置检查约束的字段 ·····································91 6.5.2 在创建表时设置检查约束 ·····································92 6.5.3 在修改表时添加检查约束 ·····································92 6.5.4 删除检查约束 ······································93 6.5.5 使用图形界面操作检查约束 ·································93 6.6 默认值约束 ······································93 6.6.1 选取设置默认值约束的字段 ·································93 6.6.2 在创建表时设置默认值约束 ·································93 6.6.3 在修改表时添加默认值约束 ·································94 6.6.4 删除默认值约束 ····································94 6.6.5 使用图形界面操作默认值约束 ········································95 6.7 非空约束 ·······························95 6.7.1 选取设置非空约束的字段 ·····································95 6.7.2 在创建表时设置非空约束 ·····································95 6.7.3 在修改表时添加非空约束 ·····································96 6.7.4 删除非空约束 ······································97 6.7.5 使用图形界面操作非空约束 ·································97 6.8 查看表中的约束 ···············································98 6.9 综合案例 ·······························98 6.9.1 案例目的 ··············································98 6.9.2 案例演示过程 ······································99 6.10 小结 ·····································100 6.11 练习题 ·······························100 第7 章 函 数 ········································· 102 7.1 数值型函数 ····································102 7.1.1 ABS 函数 ············································102 7.1.2 MOD 函数 ··········································103 7.1.3 SQRT 函数 ·········································103 7.1.4 SIGN 函数 ··········································103 7.1.5 CEIL 与CELING 函数 ·········································104 7.1.6 三角函数 ············································104 7.2 字符串函数 ····································106 7.2.1 LENGTH 函数 ·····································106 7.2.2 UPPER 与LOWER 函数 ······································107 7.2.3 LEFT 与RIGHT 函数 ···········································107 7.2.4 CONCAT 函数 ·····································108 7.2.5 TRIM 函数 ·········································108 7.2.6 INSERT 函数 ······································109 7.2.7 SUBSTRING 函数 ·······························109 7.2.8 REVERSE 函数 ····································110 7.2.9 REPLACE 函数 ····································110 7.3 日期与时间函数 ·············································111 7.3.1 NOW 和SYSDATE 函数 ·····································111 7.3.2 CURDATE 函数和CURRENT_DATE 函数 ·································111 7.3.3 CURTIME 函数和CURRENT_TIME 函数 ··································112 7.3.4 DAYOFWEEK 函数和WEEKDAY 函数 ·······································112 7.3.5 DAYOFMONTH 函数和DAYOFYEAR 函数 ······································113 7.3.6 MONTH 函数和MONTHNAME 函数 ········································113 7.3.7 DATEDIFF 函数···································114 7.3.8 ADDDATE 函数 ···································114 7.3.9 DATE_FORMAT 函数 ··········································115 7.4 聚合函数 ········································116 7.4.1 MAX 函数 ··········································116 7.4.2 MIN 函数 ············································117 7.4.3 COUNT 函数 ······································118 7.4.4 SUM 函数 ···········································118 7.4.5 AVG 函数 ···········································119 7.5 综合案例 ········································119 7.5.1 案例目的 ············································119 7.5.2 案例演示过程 ······································120 7.6 小结 ·······································121 7.7 练习题 ·································121 第8 章 使用基本的查询语句 ····································· 122 8.1 查询语句基础 ································122 8.1.1 基本语法 ············································122 8.1.2 使用“*”查询表中的全部内容 ····································123 8.1.3 查询表中指定的字段 ···········································124 8.1.4 使用DISTINCT 关键字去除结果中的重复行 ································126 8.1.5 在查询时给表和字段设置别名 ······································127 8.1.6 使用LIMIT 关键字指定查询结果的行数 ····································128 8.1.7 使用ORDER BY 对查询结果进行排序 ······································130 8.2 带WHERE 关键字的条件查询 ········································132 8.2.1 常用的运算符 ······································132 8.2.2 单一条件的查询语句 ···········································138 8.2.3 多条件的查询语句 ··············································138 8.2.4 使用LIKE 的模糊查询 ········································139 8.2.5 日期字段作为条件的查询语句 ······································141 8.3 综合案例 ········································141 8.3.1 案例目的 ············································141 8.3.2 案例演示过程 ······································142 8.4 小结 ·······································145 8.5 练习题 ·································145 第9 章 高级查询 ······································ 146 9.1 多表查询 ········································146 9.1.1 内连接查询 ········································147 9.1.2 外连接查询 ········································149 9.1.3 自连接查询 ········································151 9.2 子查询 ·································151 9.2.1 使用子查询的时机 ··············································151 9.2.2 子查询中常用的运算符 ·······································152 9.2.3 子查询的应用 ······································152 9.3 分组查询 ········································156 9.3.1 使用分组查询的时机 ···········································156 9.3.2 聚合函数在分组查询中的应用 ······································157 9.3.3 使用HAVING 关键字设置条件 ·····································158 9.4 使用正则表达式的查询 ···································159 9.4.1 查询以特定字符或字符串开头的记录 ·····························160 9.4.2 查询以特定字符或字符串结尾的记录 ·····································160 9.4.3 用符号“.”代替字符串中的任意一个字符 ······························161 9.4.4 使用“*”和“+”来匹配多个字符 ····························161 9.4.5 匹配指定字符串 ··································162 9.4.6 匹配指定字符串中的任意一个 ······································163 9.4.7 匹配指定字符以外的字符 ···································164 9.5 综合案例 ········································164 9.5.1 案例目的 ············································164 9.5.2 案例演示过程 ······································165 9.6 小结 ·······································168 9.7 练习题 ·································169 第10 章 操作表中的数据 ·········································· 170 10.1 使用INSERT 语句添加数据 ··········································170 10.1.1 基本语法 ··········································170 10.1.2 向表中的全部字段添加值 ·································172 10.1.3 向表中指定字段添加值 ·····································174 10.1.4 使用INSERT INTO … FROM 语句复制表数据 ···································174 10.2 使用UPDATE 语句修改数据 ········································175 10.2.1 UPDATE 语句的基本语法 ·································175 10.2.2 修改表中的数据 ································176 10.2.3 根据条件修改表中的数据 ·································176 10.3 使用DELETE 语句删除数据 ·········································177 10.3.1 删除单个表中的数据 ·········································177 10.3.2 删除表中的全部数据 ·········································177 10.3.3 根据条件删除表中的数据 ·································177 10.4 使用图形界面操作表中的数据 ······································178 10.5 综合案例 ······································179 10.5.1 案例目的 ·········································180 10.5.2 案例演示过程 ···································180 10.6 小结 ·····································184 10.7 练习题 ·······························184 第11 章 视 图 ······································ 185 11.1 认识视图 ······································185 11.2 创建视图 ······································187 11.2.1 基本语法 ···········································187 11.2.2 创建基于单表的视图 ··········································187 11.2.3 创建基于多表的视图 ··········································189 11.2.4 查询视图 ···········································190 11.3 修改视图 ······································191 11.3.1 基本语法 ···········································191 11.3.2 修改视图内容 ·····································191 11.3.3 修改视图名称 ·····································194 11.4 删除视图 ······································194 11.4.1 基本语法 ···········································194 11.4.2 删除视图 ···········································194 11.5 使用图形界面操作视图 ·································194 11.6 综合案例 ······································196 11.6.1 案例目的 ··········································196 11.6.2 案例演示过程 ····································197 11.7 小结 ·····································201 11.8 练习题 ·······························201 第12 章 自定义函数和存储过程 ································ 202 12.1 为什么要使用自定义函数 ··········································202 12.1.1 创建并使用自定义函数 ·····································202 12.1.2 修改自定义函数 ································204 12.1.3 删除自定义函数 ································204 12.1.4 使用图形界面操作自定义函数 ····································204 12.2 为什么要使用存储过程 ·································205 12.3 结构控制语句 ···············································206 12.3.1 声明变量 ··········································206 12.3.2 条件控制语句 ····································207 12.3.3 循环控制语句 ····································208 12.4 创建存储过程 ···············································210 12.4.1 基本语法 ··········································210 12.4.2 创建不带参数的存储过程 ·································211 12.4.3 创建带参数的存储过程 ·····································212 12.5 修改存储过程 ···············································212 12.5.1 基本语法 ··········································212 12.5.2 修改存储过程的内容和名称 ········································213 12.6 删除存储过程 ···············································213 12.6.1 基本语法 ··········································213 12.6.2 删除存储过程 ····································213 12.7 使用图形界面操作存储过程 ·········································213 12.8 综合案例 ······································215 12.8.1 案例目的 ·········································215 12.8.2 案例演示过程 ····································216 12.9 小结 ·····································217 12.10 练习题 ········································217 第13 章 触 发 器 ······································ 219 13.1 为什么要使用触发器 ·····································219 13.2 创建触发器 ··································221 13.2.1 基本语法 ··········································221 13.2.2 创建BEFORE 类型触发器 ································222 13.2.3 创建AFTER 类型触发器 ···································223 13.3 修改和删除触发器 ·······································224 13.3.1 基本语法 ··········································224 13.3.2 删除触发器 ······································225 13.4 使用图形界面操作触发器 ·········································226 13.5 综合案例 ······································227 13.5.1 案例目的 ·········································227 13.5.2 案例演示过程 ···································227 13.6 小结 ·····································229 13.7 练习题 ·······························229 第14 章 索 引 ······································· 231 14.1 为什么要使用索引 ·······································231 14.1.1 索引的分类 ······································232 14.1.2 索引的使用原则和注意事项 ········································234 14.2 创建索引 ······································235 14.2.1 基本语法 ··········································235 14.2.2 创建一般索引 ····································237 14.2.3 创建唯一索引 ····································237 14.2.4 查看索引 ··········································238 14.3 修改和删除索引 ···········································239 14.3.1 基本语法 ··········································239 14.3.2 删除索引 ··········································240 14.4 综合案例 ······································241 14.4.1 案例目的 ·········································241 14.4.2 案例演示过程 ····································242 14.5 小结 ·····································243 14.6 练习题 ·······························244 第15 章 用户与权限 ································· 245 15.1 用户管理 ······································245 15.1.1 创建用户 ··········································246 15.1.2 修改用户 ··········································247 15.1.3 删除用户 ··········································249 15.2 权限管理 ······································250 15.2.1 授予用户权限 ····································250 15.2.2 撤销用户权限 ····································253 15.3 使用图形工具操作用户和权限 ······································254 15.4 综合案例 ······································256 15.4.1 案例目的 ·········································256 15.4.2 案例演示过程 ···································256 15.5 小结 ·····································260 15.6 练习题 ·······························260 第16 章 事务与数据库的备份恢复 ····································· 261 16.1 事务 ·····································261 16.1.1 为什么要使用事务 ·············································261 16.1.2 开始事务 ··········································262 16.1.3 提交事务 ··········································262 16.1.4 撤销事务 ··········································262 16.2 数据库的备份恢复 ·······································263 16.2.1 数据库备份 ······································264 16.2.2 恢复数据库 ······································264 16.3 综合案例 ······································265 16.3.1 案例目的 ·········································266 16.3.2 案例演示过程 ···································266 16.4 小结 ·····································269 16.5 练习题 ·······························269 第17 章 企业项目开发案例 ······································· 271 17.1 项目的导入 ··································271 17.2 创建数据库与设计表结构 ···········································273 17.2.1 系统功能描述和功能模块划分 ····································273 17.2.2 系统流程分析 ····································274 17.2.3 系统所用文件 ····································274 17.2.4 创建数据库 ······································275 17.2.5 设计表结构 ······································275 17.3 设计新闻发布模块 ·······································276 17.3.1 新闻首页 ··········································276 17.3.2 公司新闻详细信息页面 ·····································285 17.3.3 社会新闻详细信息页面 ·····································288 17.3.4 员工动态详细信息页面 ·····································291 17.3.5 公司所有新闻页面 ·············································294 17.3.6 社会所有新闻页面 ·············································296 17.3.7 员工动态所有信息页面 ·····································299 17.4 设计与开发新闻管理模块 ··········································302 17.4.1 新闻管理首页 ····································302 17.4.2 新闻插入页面 ····································310 17.4.3 新闻修改页面 ····································315 17.4.4 新闻删除确认页面 ············································321 17.4.5 新闻删除页面 ····································325 17.5 练习题 ·······························326 |