MySQL

数据库

1. 简介

MySQL

是一个 DBMS（数据库管理系统），由瑞典 MySQLAB 开发，目前属于 Oracle，MySQL 是最流行的关系型数据库管理系统。

关联数据库

将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。MySQL 使用 SQL 语言进行操作。

• 常见的 SQL 语言类型
  
  • DDL：数据定义语言
  • TPL：事务处理语言
  • DCL：数据控制语言
  • DML：数据操作语言

MySQL 开发技巧着重于 DML：SELECT，INSERT，UPDATE，DELETE。

2. 安装

2.1. Linux 下安装 MySQL

# 安装 MySQL 服务端、核心程序
sudo apt-get install mysql-server
# 安装 MySQL 客户端
sudo apt-get install mysql-client

2.2. 检查是否安装 MySQL

sudo netstat -tap | grep mysql

3. 基础

3.1. 数据类型

• VARCHAR、TEXT、DATE、TIME、ENUM 等类型的数据也需要单引号修饰；
• INT、FLOAT、DOUBLE 等则不需要；
• CHAR 和 VARCHAR 的区别：CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的。
  
  • 比如，存储字符串“abc"，对于 CHAR (10)，表示存储的字符将占 10 个字节(包括 7 个空字符)，而同样的 VARCHAR(12) 则只占用 3 个字节的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。
• ENUM 和 SET 的区别：ENUM 类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而 SET 类型的值则可以多选。

3.2. 约束分类

• 约束分类皆在 CREATE TABLE <表名>(...) 内部声明。

3.2.1. 主键约束

主键约束 (PRIMARY KEY)

用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。行中的主键不能有重复且不能为空。

• 声明方法
  
  • 列名a 数据类型(数据长度) PRIMARY KEY
  • CONSTRAINT <主键名> PRIMARY KEY (<主键列名>)
  • 复合主键：CONSTRAINT <主键名> PRIMARY KEY (<主键列一名,主键列二名>)

3.2.2. 默认约束

默认约束 (DEFAULT)

只会在使用 INSERT 语句时体现出来，INSERT 语句中，如果被 DEFAULT 约束的位置没有值，那么这个位置将会被 DEFAULT 的值填充；

• 声明方法：列名a 数据类型(数据长度) DEFAULT '数据'

3.2.3. 唯一约束

唯一约束 (UNIQUE)

比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

• 声明方法：UNIQUE (列名)

3.2.4. 外键

外键 (FOREIGN KEY)

既能确保数据完整性，也能表现表之间的关系。一个表可以有多个外键，每个外键必须 REFERENCES 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

• 声明方法：CONSTRAINT <外键名> FOREIGN KEY (<外键列名>) REFERENCES <表名>(<列名>)

3.2.5. 非空约束

非空约束 (NOT NULL)

顾名思义，被非空约束的列，在插入值时必须非空。在 MySQL 中违反非空约束，不会报错，只会有警告；当输入为空时，被记为 0。

• 声明方法：列名a 数据类型(数据长度) NOT NULL

3.3. 内置函数

• 其中 COUNT 函数可用于任何数据类型（因为它只是计数），而另 4 个函数都只能对数字类数据类型做计算。

3.4. 索引

一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

3.5. 视图

从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

• 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
• 视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
• 使用视图的时候，可以把它当作一张表。

创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询。

4. 使用

4.1. 打开

# 打开 MySQL 服务
sudo service mysql start
# 使用 root 用户登录
mysql -u root

4.2. 查看库

show databases;

4.3. 选定库操作

use <数据库名>

示例：use information_schema；

4.4. 查看表

show tables;

4.5. 退出

quit

或者

exit

4.6. 新建库

CREATE DATABASE <数据库名>;

示例：CREATE DATABASE mysql_firstdatabase;。

• SQL 语句大小写不敏感，但是出于严谨，而且便于区分保留字和变量名，我们把保留字大写，把变量和数据小写。

保留字 (reserved word)

指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。

4.7. 新建表

CREATE TABLE <表名>
(
<列名a> 数据类型(数据长度),
<列名b> 数据类型(数据长度),
<列名c> 数据类型(数据长度)
);

4.8. 插入值

INSERT INTO 表名(列名a, 列名b, 列名c) VALUES(值1, 值2, 值3);

示例：

INSERT INTO employee(id, name, phone) VALUES(01,'Tom',110110110);
# 或者：
INSERT INTO employee VALUES(02, 'Jack', 119119119);
# 或者：
#表中 Rose 的 phone 为 NULL
INSERT INTO employee(id, name) VALUES(03, 'Rose');

4.9. 删除库

DROP DATABASE <库名>;

4.10. 删除表

DROP TABLE <表名>;

4.11. 重命名表

ENAME TABLE 原名 TO 新名;
# 或者：
ALTER TABLE 原名 RENAME 新名;
# 或者：
ALTER TABLE 原名 RENAME TO 新名;

4.12. 添加列

• 新添加的列，被默认放置在表的最右边；

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
# 或者：
ALTER TABLE 表名 ADD 列名 数据类型 约束;

• 可改变列插入位置；

# 在 AFTER 后的列名右边插入
ALTER TABLE 表名 ADD 列名 数据类型 约束 AFTER 列名;
# 使用 FIRST 使之放在第一列
ALTER TABLE 表名 ADD 列名 数据类型 约束 FIRST;

4.13. 删除列

ALTER TABLE 表名 DROP COLUMN 列名;
# 或者：
ALTER TABLE 表名 DROP 列名;

4.14. 重命名列

ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型 约束;

• 重命名语句后面的“数据类型”不能省略，否则重命名失败；

4.15. 修改列的数据类型

# CHANGE 语句中原列名和新列名相同时，指定新的数据类型或约束即可修改数据类型或约束；
ALTER TABLE 表名字 MODIFY 列名字 新数据类型; 
# 或者：
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

• 修改数据类型可能会导致数据丢失，所以要慎重使用；

4.16. 修改某行

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

• 必须要有 WHERE 条件；

4.17. 删除某行

DELETE FROM 表名字 WHERE 条件;

• 必须要有 WHERE 条件；

4.18. 建立索引

• 对一张表中的某个列建立索引；

ALTER TABLE 表名 ADD INDEX 索引名 (列名);
# 或者：
CREATE INDEX 索引名 ON 表名 (列名);

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。

4.19. 建立视图

CREATE VIEW 视图名(列a, 列b, 列c) AS SELECT 列1, 列2, 列3 FROM 表名字;

4.20. 导入

LOAD DATA INFILE '文件路径' INTO TABLE 表名;

• 可以导入 txt 文件；

4.21. 导出

SELECT 列1, 列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

• 语句中“文件路径”之下不能已经有同名文件；

4.22. 备份

• 备份整个数据库；

mysqldump -u root 库名>备份文件名;

• 备份整个表；

mysqldump -u root 库名 表名>备份文件名;

4.23. 恢复

source <.sql路径>
# 或者：
# 备份的整个数据库
mysqldump -u root 库名<备份文件名;   

5. 语句示例

5.1. 简单语句查询示例

SELECT name, age FROM employee;
SELECT name, age FROM employee WHERE age>25;
SELECT name, age, phone FROM employee WHERE name='Mary';
# 筛选出大于 25，小于 30 的age
SELECT name, age FROM employee WHERE age>25 AND age<30;    
# 筛选出大于等于 25，小于等于 30 的 age
SELECT name, age FROM employee WHERE age BETWEEN 25 AND 30;
# 在 dpt3 或 dpt4 中
SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt IN ('dpt3', 'dpt4'); 
# 既不在 dpt3 也不在 dpt4 中
SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1', 'dpt3'); 
# _ 通配一个未定字符
SELECT name, age, phone FROM employee WHERE phone LIKE '1101__'; 
# %通配不定个未定字符
SELECT name, age, phone FROM employee WHERE name LIKE 'J%';
# 对结果排序
SELECT name, age, salary, phone FROM employee ORDER BY salary DESC; 
# 函数计算
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

5.2. 进阶语句查询示例

5.2.1. 子查询

• 可以扩展到 3 层、4 层或更多层；

SELECT of_dpt, COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

5.2.2. 连接查询

SELECT id, name, people_num
FROM employee, department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

此举等同于 JOIN ON 语句：

SELECT id, name, people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

6. 进阶

6.1. JOIN 从句

6.1.1. 内连接

内连接 (INNER JOIN)

SELECT 列名
FROM 表1 
INNER JOIN 表2
ON 表1.值 = 表2.值;

6.1.2. 左外链接

左外链接 (LEFT OUTER)

SELECT 列名
FROM 表1 
LEFT JOIN 表2
ON 表1.值 = 表2.值
WHERE 表2.值 IS NULL;

6.1.3. 右外链接

右外链接 (RIGHT OUTER)

SELECT 列名
FROM 表1 
RIGHT JOIN 表2
ON 表1.值 = 表2.值
WHERE 表1.值 IS NULL;

6.1.4. 全外链接

全外链接 (FULL OUTER)

• MySQL 中不支持 FULL OUTER JOIN，以 LEFT JOIN、RIGHT JOIN、UNION JOIN 结合进行解决；

SELECT 列名
FROM 表1 
LEFT JOIN 表2
ON 表1.值 = 表2.值
UNION JOIN
SELECT 列名
FROM 表2
RIGHT JOIN 表1
ON 表1.值 = 表2.值;

6.1.5. 交叉连接

交叉连接 (CROSS)
： 即两表叉乘（笛卡尔积连接）；

SELECT 列名
FROM 表1 
CROSS JOIN 表2;

6.1.6. 联合更新

• 将表 1 与表 2 中同一 id 的表 1 中的某键的值替换成表 2 中的值。
• 一般思路：思路在 SQL Server 和 Oracle 上可行，但 MySQL 不支持这种机制；

UPDATE 表1
SET 键='值'
WHERE 表1.列名
IN
(SELECT 表2.列名
FROM 表1 
LEFT JOIN 表2 
ON 表1.列名 = 表2.列名);

• 优化方法

UPDATE 表1
JOIN 
(SELECT 表2.列名
FROM 表1
JOIN 表2
ON 表1.列名 = 表2.列名) 表2
ON 表1.列名 = 表2.列名
SET 表1.列名 = '值';

示例：将 user1 表中 user_name 与 user2 表中 user_name 相同的 user1 表的 over 替换成 user2 表中的 over 值；

UPDATE user1 
SET over='齐天大圣'
WHERE user1.user_name
IN
(SELECT b.user_name
FROM user1 a
LEFT JOIN user2 b
ON a.user_name = b.user_name);

↓↓

UPDATE user1 a
JOIN 
(SELECT b.user_name
FROM user1 a
JOIN user2 b
ON a.user_name = b.user_name) b
ON a.user_name = b.user_name
SET a.over = '齐天大圣';

6.1.7. 优化子查询

• 一般写法：若数据量小，没有多大影响，若数据量大，则要消耗大量的查询；

SELECT 列名, 
(
SELECT 列名
FROM 表2
WHERE 表1.列名 = 表2.列名
)
AS 新列名
FROM 表1;

• 优化方法

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;

示例：

SELECT a.user_name, a.over,
(
SELECT over
FROM user2 b
WHERE a.user_name = b.user_name
)
AS over2
FROM user1 a;

↓↓

SELECT a.user_name, a.over, b.over
FROM user1 a
LEFT JOIN user2 b
ON a.user_name = b.user_name;

6.1.8. 优化聚合子查询

• 一般思路：聚合子查询；

示例：查询出四人组中打怪最多的日期；

SELECT a.user_name, b.timestr, b.kills 
FROM user1 a 
JOIN user_kills b 
ON a.id = b.user_id
WHERE b.kills = (SELECT MAX(c.kills) 
FROM user_kills c 
WHERE c.user_id = b.user_id);

↓↓

SELECT a.user_name, b.timestr, MAX(b.kills) 
FROM user1 a 
JOIN user_kill b 
ON a.id=b.user_id 
GROUP BY a.user_name;

或者：

SELECT a.user_name, b.timestr, b.kills 
FROM user1 a
JOIN user_kills b ON a.user_id = b.user_id
JOIN user_kills c ON c.user_id = b.user_id
GROUP BY a.user_name, b.timestr, b.kills
HAVING b.kills = MAX(c.kills);

6.1.9. 实现分组选择

示例：分类聚合方式查询每一个用户某一个字段数据最大的两条数据；

SELECT a.user_name, b.timestr, b.kills
FROM user1 a
JOIN user_kills b
ON a.id = b.user_id
WHERE user_name = '孙悟空'
ORDER BY b.'kills' DESC
LIMIT 2;

↓↓

SELECT d.user_name, c.ctimestr, kills 
FROM
(SELECT user_id, timestr, kills,
(
SELECT count(*) 
FROM user_kills b 
WHERE b.user_id = a.user_id AND a.kills <= b.kills
) 
AS cnt
FRP, user_kills a 
GROUP BY user_id, timestr, kills) c 
JOIN user1 d 
ON c.user_id = d.id 
WHERE cnt <= 2;

6.2. 行列转换

6.3. 成唯一序列号

6.4. 数据查重

6.5. 子查询中匹配两个值

6.6. 同属性多值过滤

6.7. 累积税计算