MySQL

关系型数据库概念

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解。
关系型数据库这一系列的行和列被称为表，一组表组成了数据库。
用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。
关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织

创建表

创建MySQL数据表需要以下信息：

表名
表字段名
定义每个表字段

示例：

//  如果表不存在则创建表
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
  // 字段名(runoob_id) 数据类型(INT) 无符号(UNSIGNED)  自增(AUTO_INCREMENT)
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
  //  非空(NOT NULL)
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
  //  主键(PRIMARY KEY)
   PRIMARY KEY ( `runoob_id` )
  //  引擎  字符格式
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据操作

查询

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

其中，各条子句的含义如下：

{*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
<表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。可以设置多个字段，按照顺序进行排序
[LIMIT[<offset>,]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。
offset:初始位置，row count：记录数。不指定初始位置时，默认从第一条数据开始查询

查询结果去重

1	SELECT DISTINCT <字段名> FROM <表名>;

设置别名

1	``<表名> [AS] <别名>``

模糊查询

1	select * from <表名> where <字段名> [NOT] LIKE '字符串';

LIKE 关键字支持百分号”%”和下划线”_”通配符。

“%”

“%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。例如，a%b表示以字母 a 开头，以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。**中文字符需要使用”%%”**。

“_”

“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。

范围查询

1	select * from <表名> where <字段名> [NOT] BETWEEN 取值1 AND 取值2;

空值查询

1	select * from <表名> where <字段名> IS [NOT] NULL;

Group By 拼接

1	select group_concat(<字段名>) from <表名> group by <字段名>

多表查询

多表查询分为交叉查询，内连接，外连接三种

交叉查询

1
2
3

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
-- 或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句] ;

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

内连接

1	SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。(只返回满足条件的数据)

外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接。

左外连接

1 2	SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>; -- outer可以省略，即:LEFT JOIN

“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。

右外连接

1 2	SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>; -- 同左连接一样可以直接使用：RIGHT JOIN

与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

子查询

1	WHERE <表达式> <操作符> (子查询)

操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字

IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

插入数据

1 2	INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];

语法说明如下：

<表名>：指定被操作的表名。
<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

INSERT INTO <表名>
SET <列名1> = <值1>,
        <列名2> = <值2>,
        …

此语句用于直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，col_name 为指定的列名，等号后面为指定的数据，而对于未指定的列，列值会指定为该列的默认值。

由 INSERT 语句的两种形式可以看出：

使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
使用 INSERT…SET 语句可以指定插入行中每列的值，也可以指定部分列的值；
INSERT…SELECT 语句向表中插入其他表的数据。
采用 INSERT…SET 语句可以向表中插入部分列的值，这种方式更为灵活；
INSERT…VALUES 语句可以一次插入多条数据。

在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。

当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。

复制表值

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中
SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

修改

1 2	UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。

删除

1	DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

ACID原则

ACID原则是数据库事务正常执行的四个原则

A (Atomicity) 原子性
原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。
比如银行转账，从A账户转100元至B账户，分为两个步骤：1）从A账户取100元；2）存入100元至B账户。这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。
C (Consistency) 一致性
一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。
例如现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。
I (Isolation) 隔离性
所谓的隔离性是指并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。
比如现有有个交易是从A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。
D (Durability) 持久性
持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

事务隔离导致的问题

首先了解几个概念：

脏读：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
不可重复读：是指在一个事务内，多次读同一数据。
幻读：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了

隔离级别：

读未提交：read uncommitted
SELECT语句以非锁定方式被执行，所以有可能读到脏数据，隔离级别最低。（读不锁）
读已提交：read committed
只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。（读锁，等写完）
可重复读：repeatable read
在同一个事务内的查询都是事务开始时刻一致的，InnoDB的默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。(写锁，等读完)
串行化：serializable
完全的串行化读，所有SELECT语句都被隐式的转换成SELECT … LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高。(以上3个均为行锁/记录锁，当前为表锁)