MySQL数据库基础详解

文章大纲

一、数据库简介
二、Mysql数据库简介
三、Mysql安装与服务启动（Windows版本）
四、Mysql图形化工具
五、Mysql存储引擎精讲
六、Mysql数据类型介绍
七、Mysql主要专业名称介绍
八、Mysql常见sql语句
九、Mysql设计与语句优化
十、事务介绍
十一、Mysql数据库备份与恢复
十二、Mysql分库分表
十三、Mysql权限管理
十四、Mysql数据库之阿里云
十五、资料下载
十六、参考文章

 

一、数据库简介

1. 数据库是什么

  数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映客观事物间的本质联系。数据库能有效地帮助一个组织或企业科学地管理各类信息资源。
  数据是数据库中存储的基本对象，是按一定顺序排列组合的物理符号。数据有多种表现形式，可以是数字、文字、图像，甚至是音频或视频，它们都可以经过数字化后存入计算机。
  数据库是数据的集合，具有统一的结构形式并存放于统一的存储介质内，是多种应用数据的集成，并可被各个应用程序所共享。
  在日常生活中，人们可以直接用中文、英文等自然语言描述客观事物。在计算机中，则要抽象出对这些事物感兴趣的特征，并组成一个记录来描述。

2. 数据库在开发中的作用

  从数据库系统应用角度来看，数据库系统常见的运行与应用结构有：客户端/服务器结构、浏览器/服务器结构。
  在客户端/服务器（Client/Server，C/S）结构中，数据库的使用者（如 DBA、程序设计者）通过命令行客户端、图形化界面管理工具或应用程序等连接到数据库管理系统，可以通过数据库管理系统查询和处理存储在底层数据库中的各种数据。
  数据库使用者与命令行客户端、图形化界面管理工具或应用程序等直接交互，而不与数据库管理系统直接联系。
  在这种结构中，命令行客户端、图形化界面管理工具或应用程序等称为“客户端”或“前台”，主要完成与数据库使用者的交互任务；而数据库管理系统则称为“服务器”或“后台”，主要负责数据管理。这种结构经常被称为“C/S”结构。
  在客户端/服务器模式中，客户端和服务器可以同时工作在同一台计算机上，这种工作方式称为“单机方式”；也可以“网络方式”运行，即服务器被安装和部署在网络中某一台或多台主机上。
  对于客户端应用程序的开发，目前常用的语言工具主要有 Visual C++、Delphi、.NET 框架、Visual Basic、Python 等。
  数据库能有效存储数据，读取数据、查找数据更是方便，其实那些管理软件就是通过软件的界面向内部的数据库进行数据的增、删、改、查操作。

3.常见数据库比较

3.1 MySQL数据库
定位
开源、多平台、关系型数据库
目前使用最广泛、流行度最高的的开源数据库。

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据，有插件式存储引擎，支持多种存储引擎格式

部署
用编译安装的方式，或者二进制包的方式，按照“安装软件-创建实例-库表用户初始化”，可以很快完成数据库部署

使用
使用标准的SQL语句进行数据库管理，简单SQL语句的并发和性能较好，对视图、存储过程、函数、触发器等支持的不是太好

监控
在命令行界面有一些常用的命令显示状态和性能，在图形界面方面，有比较多的开源监控工具来监控和记录数据库的状态，比如zabbix，nagios，cacti，lepus等

备份
逻辑备份 mysqldump/mysqldumper ，物理备份 用xtrabackup等工具进行备份；

高可用
MySQL高可用有多种方案，官方有基础的master-slave主从复制，新版本的innodb cluster，第三方的有MHA等高可用方案；

扩展
MySQL水平拆分，可以通过水平拆分proxy中间进行逻辑映射和拆分，扩大MySQL数据库的并发能力和吞吐量。

适用场景
默认的innodb存储引擎，支持高并发，简单的绝大部分OLTP场景；
Tokudb存储引擎，使用高并发insert的场景；
Inforbright存储引擎，可以进行列压缩和OLAP统计查询场景；

选择注意
使用MySQL进行OLTP业务时，需要注意数据量级，如果数据量级过大，需要进行水平拆分；
如果有OLAP需求，可以结合其他架构综合考虑。

3.2 SQL Server数据库
定位
商业、Windows平台、关系型数据库
最早接触、与微软体系结合紧密的的商业数据库，属于“微软技术体系”

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据

部署
在Windows平台，用图形界面进行软件安装；

使用
在Windows平台，使用SQL Server Mangement Studio图形界面进行安装；

监控
一般通过Windows资源管理和SQL server图形工具进行系统和数据库性能显示；

备份
通常用第三方备份恢复软件进行备份恢复；

高可用
通过共享存储和双机热备的方式，可以实现SQL Server数据库的高可用；

扩展
SQL Server数据库集群采用共存存储的方式，通过硬件垂直升级来对数据库集群进行扩展；

适用场景
大多数OLTP场景（与微软体系配合）

选择注意
SQL Server与微软技术体系结合比较紧密，绝大多数工作，都是通过图形界面完成，对于习惯使用命令行的DBA可能会有不习惯；
SQL server对双引号，大小写，元信息的管理和处理方式，与其他数据库很不相同，需要注意；
使用SQL Server满足OLTP业务，会有比较好的效果，但对于大数据量的OLAP业务，最好还是选用专门的OLAP架构，不要在同一个SQL Server实例上混用OLTP和OLAP业务；
SQL server属于商业软件，需要注意版权和licence授权费用；

3.3 Oracle数据库
定位
商业、多平台、关系型数据库
功能最强大、最复杂、市场占比最高的商业数据库

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据

部署
Oracle单实例数据库部署相对容易，但Oracle RAC集群环境，部署的步骤和依赖条件都比较多；

使用
通常使用命令行工具，进行各种数据库的管理，通常也可以用shell脚本和python脚本提高Oracle数据库管理效率；各种管理功能，都比较强大；

监控
Oracle官方有比较全面的监控工具，常用的第三方监控平台，如zabbix,cacti,lepus等都有对Oracle数据库的各项指标的完善监控；

备份
支持冷备份和热备份，可以用 exp/imp , expdp/impdp等进行逻辑备份和恢复，可以使用强大的RMAN工具进行专业的物理热备份和恢复；

高可用
Oracle数据库的高可用架构，可以用第三方双机热备软件，结合Oracle单实例实现；可以使用Oracle Dataguard，实现master和standby的备份；可以使用 Oracle RAC集群实现实例级别的高可用和负载均衡，使用ASM实现存储级别的高可用；

扩展
由于Oracle集群采用共享存储的方式，一般只能通过垂直硬件升级进行升级；

适用场景
绝大多数OLTP场景，部分OLAP

选择注意
Oracle从架构到运维，可以说是最难的数据库，学习和使用难度较高。

3.4 Hbase数据库
定位
开源、Linux平台、列存储nosql数据库
可用于海量数据存储、与Hadoop生态圈结合、定位于“大”的列存储nosql数据库

功能
命令执行速度非常看，读写性能可达10万/秒；数据结构是key-value类似字典的功能，可以键过期-缓存，发布订阅-消息系统，简单的事物功能；

部署
相对其他数据库，hbase的部署比较复杂，依赖Hadoop，zookeeper等组件，Hbase集群包括一个mater节点，多个regionServer，zookeeper管理所有regionServer，需要依次部署Hadoop、zookeeper之后，再部署HBASE集群；

使用
用redis-cli客户端连接，一般用简单的 set ,get,del 进行数据管理； 在单实例redis的基础上，进行可以数据持久化，主从复制，高可用和分布式等功能；

监控
在命令行界面有一些常用的命令显示状态和性能，在图形界面方面，有开源监控工具来监控和记录数据库的状态，比如cachecloud；

备份
Hbase一般用作海量数据的仓库，本身通过多层副本来保证数据安全性，不用进行专门的备份

高可用
HBASE集群基于Hadoop，需要依次部署Hadoop单机模式、集群模式、HA模式，通过Hadoop HA实现高可用；

扩展
HBASE以集群形式，依次是单机模式，伪分布模式，完全分布模式，底层基于HDFS，zookeeper可以很好地进行扩展；

适用场景
两大用途：
用于简单数据写入和海量、结构简单数据查询的业务场景；
用于成为其他数据库备份和下沉的数据库；

选择注意
Hbase不适合的场景：对数据分析需求高，需要能够用sql或者简单的MapReduce实现分析需求的业务场景，不适合用Hbase；
单表数据量，不超过千万时，使用Hbase，体现不出Hbase的优势，而且会比较慢，不适合用Hbase。
通过对上面数据库“七种”武器的描述，也可以看到目前常用数据库的使用脉络和选择顺序，对应一个业务，可以优先选择最流行的开源数据库——MySQL；如果出于稳定和商业版考虑，可以选择Oracle数据库，或者SQL Server数据库（与Windows体系结合）；如果想用开源，有想要有足够的功能来应对各种场景，可以使用 postgresql数据库。这四种数据库，都是关系型数据库，可以很好地满足大多数业务场景，解决通用性问题。
对于一些特殊性问题，尤其是想要在扩展性方面有比较高的要求，可以考虑nosql数据库。Mongodb数据库，介于关系型数据库和非关系型数据库之间，兼具两者的特点，是非常流行的文档型nosql数据库；redis定位于内存型键值nosql数据库；hbase是海量文件存储的列式nosql数据库。根据合适的业务场景，选择适合的nosql数据库，可以对某一类，或某几类业务问题有很好的解决，可以作为关系型数据库的一种补充。
换个角度，MySQL，Oracle，SQL Server，Postgresql，mongodb这五种数据库，也是DB-Engines排行榜上最流行的排名前五的五种数据库，从使用量和受欢迎程度，也可以看出这些数据库使用的广泛性。

4. 数据库常见功能

 

二、Mysql数据库简介

1. MySQL的优势

  MySQL 使用的 SQL 语言是用于访问数据库的最常用的标准化语言。
  由于 MySQL 数据库体积小、速度快、总体拥有成本低、开放源代码，其有着广泛的应用，一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越，因此搭配 PHP 和 Apache 服务器可组成良好的开发环境。
  MySQL 数据库管理系统具有以下系统特性：
（1） 使用 C 和 C++ 编写，并使用多种编译器进行测试，保证源代码的可移植性。
（2）支持 AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows 等多种操作系统。
（3）为多种编程语言提供了 API。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
（4）支持多线程，充分利用 CPU 资源。
（5）优化的 SQL 查询算法，有效地提高查询速度。
（6）既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入其他的软件中。
（7）提供多语言支持，常见的编码如中文的 GB 2312、BIG 5，日文的 Shift_JIS 等都可以用作数据表名和数据列名。
（8）提供 TCP/IP、ODBC 和 JDBC 等多种数据库连接途径。
（9）提供用于管理、检查、优化数据库操作的管理工具。
（10）支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
（11）支持多种存储引擎。

2. MySQL的版本以及版本号

针对不同的用户，MySQL 分为两个版本：
（1）MySQL Community Server（社区版）：该版本完全免费，但是官方不提供技术支持。
（2）MySQL Enterprise Server（企业版）：该版本能够以很高的性价比为企业提供数据仓库应用，支持 ACID 事物处理，提供完整的提交、回滚、崩溃恢复和行级锁定功能，但是该版本需要付费使用，官方提供电话技术支持。
温馨提示：MySQL Cluster 主要用于架设群服务器，需要在社区服务或企业版的基础上使用。

MySQL 的命名机制由 3 个数字和 1 个后缀组成，例如 mysql-5.7.20：
第 1 个数字“5”是主版本号，用于描述文件的格式，所有版本 5 的发行版都有相同的文件夹格式。
第 2 个数字“7”是发行级别，主版本号和发行级别组合在一起便构成了发行序列号。
第 3 个数字“20”是在此发行系列的版本号，随每次新发行的版本递增。通常选择已经发行的最新版本。

在 MySQL 开发过程中，同时存在多个发布系列，每个发布系列的成熟度处在不同阶段。
MySQL 5.7 是最新开发的稳定（GA）发布系列，是将执行新功能的系列，目前已经可以正常使用。
MySQL 5.6 是比较稳定的（GA）发布系列，只针对漏洞修复重新发布，不增加会影响稳定性的新功能。
MySQL 5.1 是一个稳定的（产品质量）发布系列，只针对严重漏洞修复和安全修复重新发布，不增加影响该系列稳定性的重要功能。
对于 MySQL 4.1 等低于 5.0 的老版本，官方将不再提供支持

3. MySQL 5.7的新特性

与 MySQL 5.6 相比，MySQL 5.7 具有以下几个方面的新功能。
（1）随机 root 密码
MySQL 5.7 数据库初始化完成后，会自动生成一个 root@localhost 用户，root 用户的密码不为空，而是随机产生一个密码。
（2）自定义 test 数据库
MySQL 5.7 默认安装完成后没有 test 数据库。用户可以自行创建 test 数据库并对其进行权限控制。
（3）默认 SSL 加密
MySQL 5.7 采用了更加简单的 SSL 安全访问机制，默认连接使用 SSL 的加密方式。
（4）密码过期策略
MySQL 5.7 支持用户设置密码过期策略，要求用户在一定时间过后必须修改密码。
（5）用户锁
MySQL 5.7 为管理员提供了暂时禁用某个用户的功能，使被锁定的用户无法访问和使用数据库。
（6）全面支持JSON
MySQL 5.7在服务器端提供了一组便于操作 JSON 的函数。存储的方法是将 JSON 编码成 BLOB 后再由存储引擎进行处理。这样，MySQL 就同时拥有了关系型数据库和非关系型数据库的优点，并且可以提供完整的事务支持。
（7）支持两类生成列（generated column）
生成列是通过数据库中的其他列计算得到的一列。当为生成列创建索引时，可以便捷地加快查询速度。MySQL 5.7 支持虚拟生成列和存储生成列。虚拟生成列仅将数据保存在表的元数据中，作为缺省的生成列类型；存储生成列则是将数据永久保存在磁盘上，需要更多的磁盘空间。
（8）引入系统库（sys schema）
系统库中包含一系列视图、函数和存储过程，通过多线程、多进程、组合事务提交和基于行的优化方式将复制功能提高 5 倍以上，用户向外扩充其跨商品系统的工作负载时，得以大幅提升复制的效能和效率。
与 MySQL 5.6 相比，MySQL 5.7 具