mysql

1.什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database

2.RDBMS 术语

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

数据库: 数据库是一些关联表的集合。

数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。

行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

外键：外键用于关联两个表。

复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

表头(header): 每一列的名称;

列(col): 具有相同数据类型的数据的集合;

行(row): 每一行用来描述某条记录的具体信息;

值(value): 行的具体信息, 每个值必须与该列的数据类型相同;

键(key): 键的值在当前列中具有唯一性。

3.MySQL数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL 是开源的，目前隶属于 Oracle 旗下产品。

MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL 使用标准的 SQL 数据语言形式。

MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。

MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。

MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。

MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

4.MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（AICD）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

总结

假如有这样一款游戏，游戏分为一个个关卡，单个关卡内需要打败很多Boss才能闯关成功，每个关卡闯关成功游戏会自动进行记录游戏进度，并将游戏进度上传远程服务器，除非重置游戏，否则游戏进度不可回退。

原子性：最小操作逻辑，不可再进行分割；就像游戏的一个关卡一样，关卡内不管闯到什么地方，只要失败了就会回到闯关卡前。

事务，将原本是一系列的数据库操作包裹成一个关卡（一个原子操作），要么闯过关卡，要么回到关卡前。

持久性：当事务执行完毕（事务提交），该操作将永久的改变了数据库中的数据。即关卡闯关完成，游戏进度永久保存在了服务器中。

事务，能保证AID，即原子性，隔离性，持久性。但是一致性无法通过事务来保证，一致性依赖于应用层，开发者。

5.MySQL数据引擎

MySQL 有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

MEMORY存储引擎提供"内存中"表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。

注释：MEMORY存储引擎正式地被确定为HEAP引擎。

InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所 有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。

EXAMPLE存储引擎是一个"存根"引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在 MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。

NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。

ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。

CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。

BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。

FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。

常用的是 MyISAM 和 InnoBD

6.MySQL隔离级别

MySQL支持四种隔离级别，分别是：

1. 读未提交（Read Uncommitted）：最低级别的隔离级别，事务可以读取其他事务未提交的数据，可能会导致脏读、不可重复读和幻读的问题。

2. 读已提交（Read Committed）：在一个事务中，只能读取其他事务已经提交的数据，可以避免脏读问题，但仍可能出现不可重复读和幻读的问题。

3. 可重复读（Repeatable Read）：一个事务中，多次读取同一数据时，所读取的数据始终保持一致，保证了可重复读的一致性，但仍可能出现幻读问题。

4. 串行化（Serializable）：最高级别的隔离级别，通过对事务进行串行化执行，避免了并发操作导致的各种问题，包括脏读、不可重复读和幻读等。

可以使用以下语句设置MySQL的隔离级别：

SET TRANSACTION ISOLATION LEVEL <隔离级别>

其中，<隔离级别>可以替换为上述四种隔离级别之一。需要注意的是，设置隔离级别会影响到事务的并发性能和数据的一致性，需要根据具体的业务需求进行选择配置。

7.什么是脏读，幻读，不可重复读？

1. 脏读（Dirty Read）：

脏读发生在一个事务读取了另一个事务未提交的数据。可以想象一下，有两个事务A和B，事务A正在对某个数据进行修改，但还没有提交事务。此时，事务B读取了这个还未提交的数据，导致事务B读取到了被事务A修改但还未生效的脏数据。这种读取到未提交数据的行为就是脏读。

2. 幻读（Phantom Read）：

幻读是指一个事务在相同的查询条件下，多次执行同样的查询，但每次返回的结果集却不相同。可以想象一下，有两个事务A和B，事务A开启了一个查询操作，获取了一部分数据。而此时事务B插入了一条新的数据或删除了一些数据。当事务A再次执行相同的查询时，会发现结果集中出现了新增或删除的数据，好像产生了幻觉一样，这种现象就是幻读。  

3. 不可重复读（Non-Repeatable Read）：

不可重复读发生在一个事务内多次读取同一数据，在事务执行期间，其他事务修改或删除了该数据，导致每次读取的结果不一致。可以想象一下，事务A在读取某个数据的过程中，事务B修改或删除了这个数据。当事务A再次读取相同的数据时，得到的结果与之前不同，因为数据已经发生了变化，这种情况被称为不可重复读。

8.默认数据库介绍

MySQL 默认数据库包括以下几个：

information_schema：这是一个包含关于所有数据库、表、列和其他元数据信息的数据库。它提供了一种查询数据库结构和统计信息的方法。

2. mysql：这是存储 MySQL 系统和用户权限信息的数据库。在 mysql 数据库中，包含了用户、角色、权限等相关表，用于管理用户的身份验证和授权。

3. performance_schema：这个数据库包含有关 MySQL 数据库性能的统计信息，例如查询执行时间、等待事件、锁等。通过 performance_schema 可以对 MySQL 数据库的性能进行监控和调优。

4. sys：sys 是从 MySQL 5.7 版本开始引入的系统性能监控和管理工具，用于提供简化的系统状态和性能指标的查看和分析。

这些默认数据库在 MySQL 安装时自动创建，它们存储了关于数据库、权限、性能等重要的系统和元数据信息。用户可以通过连接到这些数据库并执行相应的查询语句来获取和管理这些信息。

需要注意的是，默认数据库中的表和数据存储在磁盘上的特定目录中，其位置取决于操作系统和 MySQL 的安装配置。

9.建库

在MySQL中，可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

[]中的内容是可选的。语法说明如下：

<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。

IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。

[DEFAULT] COLLATE：指定此数据库的默认排序规则。示例

CREATE DATABASE IF NOT EXISTS test_db 
DEFAULT CHARACTER SET utf8 
DEFAULT COLLATE utf8_general_ci;

10.命令行操作数据库

创建数据库 :

create database [if not exists] westos

删除数据库 :

drop database [if exists] 数据库名;

查看数据库 :

show databases;

使用数据库 :

use 数据库名;

11.账号管理

1.创建用户

MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。

MySQL 提供了以下 3 种方法创建用户。

使用 CREATE USER 语句创建用户

在 mysql.user 表中添加用户

使用 GRANT 语句创建用户

2.使用 CREATE USER 语句创建用户

可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：

CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] 
[ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

参数说明如下：

用户：指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。

IDENTIFIED BY子句：用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。

PASSWORD 'password'：PASSWORD 表示使用哈希值设置密码，该参数可选。

示例：

CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';

修改密码

SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。使用 SET 语句修改密码的语法结构如下：

SET PASSWORD = PASSWORD ("rootpwd");

3.查看用户

切换数据库

use mysql;

查询用户信息

select host,user,authentication_string from user;

MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。

host参数值说明：

host列值    含义

%    匹配所有主机

localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1    会通过TCP/IP协议连接，并且只能在本机访问

::1    ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.删除用户

使用 DROP USER 语句删除用户的语法格式如下：

DROP USER <用户1> [ , <用户2> ]…
注意：用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。

5.用户授权

在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

参数说明：

priv_type 参数表示权限类型；

columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；

database.table 用于指定权限的级别；

user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；

IDENTIFIED BY 参数用来为用户设置密码；

password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；

MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；

MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；

MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;

MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

示例：

GRANT SELECT,INSERT ON *.* TO 'zking'@'localhost' IDENTIFIED BY '1234'
 WITH GRANT OPTION;

6.用户撤权

删除用户某些特定的权限，语法格式如下：

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：

priv_type 参数表示权限的类型；

column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上；

user 参数由用户名和主机名构成，格式为“username'@'hostname'”。示例1：取消用户的某些权限

REVOKE INSERT ON *.* FROM 'zking'@'localhost'

示例2：取消用户的所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'zking'@'localhost'

7.查看用户权限

使用 SHOW GRANTS FOR 语句查看权限。其语法格式如下：

SHOW GRANTS FOR 'username'@'hostname';

其中，username 表示用户名，hostname 表示主机名或主机 IP。

示例：

SHOW GRANTS FOR 'zking'@'localhost';

12.MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。