https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/%E4%B8%80%E5%8D%83%E8%A1%8CMySQL%E5%91%BD%E4%BB%A4.md

基本操作

mysql -h 地址 -P 端口 -u 用户名 -p 密码
SHOW PROCESSLIST -- 显示哪些线程正在运行
SHOW VARIABLES -- 显示系统变量信息

数据库操作

-- 查看当前数据库
SELECT DATABASE();
-- 显示当前时间、用户名、数据库版本
SELECT now(), user(), version();
-- 创建库
CREATE DATABASE[ IF NOT EXISTS] 数据库名数据库选项
数据库选项：
CHARACTER SET charset_name
COLLATE collation_name
-- 查看已有库
SHOW DATABASES[ LIKE 'PATTERN']
-- 查看当前库信息
SHOW CREATE DATABASE 数据库名
-- 修改库的选项信息
ALTER DATABASE 库名选项信息
-- 删除库
DROP DATABASE[ IF EXISTS] 数据库名
同时删除该数据库相关的目录及其目录内容

表的操作

创建表

DROP TABLE IF EXISTS banned_status;
CREATE TABLE banned_status (
id varchar(64) COLLATE utf8_bin NOT NULL,
create_time datetime DEFAULT NULL,
delete_time datetime DEFAULT NULL,
deleted bit(1) DEFAULT NULL,
modify_time datetime DEFAULT NULL,
status varchar(255) COLLATE utf8_bin DEFAULT NULL,
is_locked bit(1) DEFAULT NULL,
status varchar(255) COLLATE utf8_bin DEFAULT NULL,
url text COLLATE utf8_bin,
url_id varchar(255) COLLATE utf8_bin DEFAULT NULL,
url_type varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

对于字段的定义

字段名数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

表选项

字符集

CHARSET = charset_name
如果表没有设定，则使用数据库字符集

存储引擎

ENGINE = InnoDB

自增起始数

AUTO_INCREMENT = 行数

数据文件目录

DATA DIRECTORY = '目录'

索引文件目录

INDEX DIRECTORY = '目录'

表注释

COMMENT = 'string'

分区选项

PARTITION BY ... (详细见手册)

查看表结构

SHOW CREATE TABLE 表名
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

修改表

-- 修改表本身的选项
ALTER TABLE 表名表的选项
eg: ALTER TABLE 表名 ENGINE=MYISAM;

-- 对表进行重命名
    RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
    -- RENAME可以交换两个表名
-- 修改表的字段机构（13.1.2. ALTER TABLE语法）
    ALTER TABLE 表名 操作名
    -- 操作名
        ADD[ COLUMN] 字段定义       -- 增加字段
            AFTER 字段名          -- 表示增加在该字段名后面
            FIRST               -- 表示增加在第一个
        ADD PRIMARY KEY(字段名)   -- 创建主键
        ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
        ADD INDEX [索引名] (字段名) -- 创建普通索引
        DROP[ COLUMN] 字段名      -- 删除字段
        MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
        CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
        DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
        DROP INDEX 索引名 -- 删除索引
        DROP FOREIGN KEY 外键    -- 删除外键

删除表

DROP TABLE[ IF EXISTS] 表名 ...

清空表数据

TRUNCATE [TABLE] 表名

复制表结构和数据

CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

复制表结构

CREATE TABLE 表名 LIKE 要复制的表名

-- 检查表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修复表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

数据操作

INSERT [INTO]，SELECT，DELETE FROM，UPDATE

字符集编码

/* 字符集编码 */ ------------------
-- MySQL、数据库、表、字段均可设置编码
-- 数据编码与客户端编码不需一致
SHOW VARIABLES LIKE 'character_set_%' -- 查看所有字符集编码项
character_set_client 客户端向服务器发送数据时使用的编码
character_set_results 服务器端将结果返回给客户端所使用的编码
character_set_connection 连接层编码

SET 变量名 = 变量值
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK; -- 相当于完成以上三个设置

-- 校对集
校对集用以排序
SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 查看所有字符集
SHOW COLLATION [LIKE 'pattern'] 查看所有校对集
CHARSET 字符集编码设置字符集编码
COLLATE 校对集编码设置校对集编码

数据类型(列类型)

1.数值类型

数值类型

2.字符串类型

字符串类型

3.日期和时间类型

日期和时间类型

4. 枚举和集合

CREATE TABLE set_table (
id int(11) NOT NULL AUTO_INCREMENT,
set set('1','2','3','4','5','11') DEFAULT '1',
int int(11) DEFAULT NULL,
enum enum('1','2') DEFAULT '1',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;

列属性(列约束)

1. PRIMARY 主键

- 能唯一标识记录的字段，可以作为主键。
- 一个表只能有一个主键。
- 主键具有唯一性。
- 声明字段时，用 primary key 标识。
    也可以在字段列表之后声明
        例：create table tab ( id int, stu varchar(10), primary key (id));
- 主键字段的值不能为null。
- 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
    例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));

2.UNIQUE 唯一索引（唯一约束）

使得某字段的值也不能重复。

3.NULL 约束

null不是数据类型，是列的一个属性。
表示当前列是否可以为null，表示什么都没有。
null, 允许为空。默认。
not null, 不允许为空。
insert into tab values (null, 'val');
    -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null

4.DEFAULT 默认值属性

insert into tab values (default, 'val');    -- 此时表示强制使用默认值。
create table tab ( add_time timestamp default current_timestamp );
    -- 表示将当前时间的时间戳设为默认值。
    current_date, current_time

5.AUTO_INCREMENT 自动增长约束

自动增长必须为索引（主键或unique）
只能存在一个字段为自动增长。
默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;

6.COMMENT 注释

例：create table tab ( id int ) comment '注释内容';

7. FOREIGN KEY 外键约束

用于限制主表与从表数据完整性。
alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
    -- 将表t1的t1_id外键关联到表t2的id字段。
    -- 每个外键都有一个名字，可以通过 constraint 指定
存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
MySQL中，可以对InnoDB引擎使用外键约束：
语法：
foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。
可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。
如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：
1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
3. restrict，拒绝父表删除和更新。
注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

数据库设计三大范式

第一范式：确保每列保持原子性
第二范：确保表中的每列都和主键相关
第三范式：确保每列都和主键列直接相关,而不是间接相关

SELECT

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
A.SELECT
B.FROM
C.WHERE
D.GROUP BY 子句, 分组子句

GROUP BY 字段/别名 [排序方式]
分组后会进行排序。升序：ASC，降序：DESC
以下[合计函数]需配合 GROUP BY 使用：
count 返回不同的非NULL值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。

E.HAVING

与 where 功能、用法相同，执行时机不同。
where 在开始时执行检测数据，对原数据进行过滤。
having 对筛选出的结果再次进行过滤。
having 字段必须是查询出来的，where 字段必须是数据表存在的。
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
where 不可以使用合计函数。一般需用合计函数才会用 having

SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。

F.ORDER BY 子句，排序子句

order by 排序字段/别名排序方式 [,排序字段/别名排序方式]...
升序：ASC，降序：DESC
支持多个字段的排序。

G.LIMIT 子句，限制结果数量子句

仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
limit 起始位置, 获取条数
省略第一个参数，表示从索引0开始。limit 获取条数

H.DISTINCT

distinct 去除重复记录
默认为 all, 全部记录

UNION

1.UNION必须由两条或两条以上的SELECT语句组成;
2.UNION中的每个查询必须包含相同的列、表达式或聚集函数
--在使用UNION时，重复的行被自动合并
--UNION ALL ，返回所有的行，不自动合并

连接查询(join)

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN ON

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

INNER JOIN

LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

LEFT JOIN

RIGHT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

RIGHT JOIN

TRUNCATE

/* TRUNCATE */ ------------------
TRUNCATE [TABLE] tbl_name
清空数据
删除重建表
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。
4，当被用于带分区的表时，truncate 会保留分区

备份与还原

/* 备份与还原 */ ------------------
备份，将数据的结构与表内数据保存起来。
利用 mysqldump 指令完成。
-- 导出
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database

导出一张表
　　mysqldump -u用户名 -p密码库名表名 > 文件名(D:/a.sql)
导出多张表
　　mysqldump -u用户名 -p密码库名表1 表2 表3 > 文件名(D:/a.sql)
导出所有表
　　mysqldump -u用户名 -p密码库名 > 文件名(D:/a.sql)
导出一个库
　　mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件
-- 导入
在登录mysql的情况下：
　　source 备份文件
在不登录的情况下
　　mysql -u用户名 -p密码库名 < 备份文件

视图

什么是视图：

1.视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
2. 视图具有表结构文件，但不存在数据文件。
3. 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很

视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

-- 创建视图

CREATE VIEW V_Customer AS select node.pop_sn,node.pop_id, failinfo.batch_id, failinfo.fail_msg from node_info node INNER JOIN batch_fail_info failinfo ON node.pop_id = failinfo.pop_id ;

-- 查看结构

SHOW CREATE VIEW V_Customer;

-- 查看视图
SELECT * FROM V_Customer;

-- 删除视图

删除视图后，数据依然存在。

可同时删除多个视图。
DROP VIEW [IF EXISTS] view_name ...

-- 视图作用

简化业务逻辑

对客户端隐藏真实的表结构

事务(transaction)

DQL、DML、DDL、DCL的概念与区别

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1.DQL 数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2.DML 数据修改（操作）语言

数据操纵语言DML主要有三种形式：

插入：INSERT
更新：UPDATE
删除：DELETE

3.DDL 数据定义语言

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
表视图索引同义词簇

4.DCL 数据控制语言

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

GRANT：授权。
ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;
COMMIT [WORK]：提交。

-- 保存点

SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

锁表

MyISAM 支持表锁，InnoDB 支持行锁
-- 锁定
LOCK TABLES tbl_name [AS alias]
-- 解锁
UNLOCK TABLES

触发器

--触发器

--触发器
--触发器
drop trigger update_trigger;
use cm;
DELIMITER $
create  trigger update_trigger after update on task_url for each row
begin
    if old.task_type = 11 then
        if (old.status =4 or old.status=5) and  new.status = 1 then
            update history_task set  succeed = case status when  'task_inprocess' then succeed + 1 else succeed end ,processing = case status when  'task_inprocess' then processing -1 else processing end, status = case when succeed + failed= total then "task_done" else status end where create_time = new.create_time and id = new.task_id;
        end if;
        
        if (old.status =4 or old.status=5)  and new.status = 2 then
            update history_task set  failed = case status when  'task_inprocess' then failed + 1 else failed end ,processing = case status when  'task_inprocess' then processing -1 else processing end, status = case when  succeed + failed = total then "task_done" else status end where create_time = new.create_time and id = new.task_id;
        end if;
        
        else
        if old.status = 0 and  new.status = 1 then
            update history_task set  succeed = case status when  'task_inprocess' then succeed + 1 else succeed end ,processing = case status when  'task_inprocess' then processing -1 else processing end, status = case when succeed + failed= total then "task_done" else status end where create_time = new.create_time and id = new.task_id;
        end if;
        
        if old.status = 0  and new.status = 2 then
            update history_task set  failed = case status when  'task_inprocess' then failed + 1 else failed end ,processing = case status when  'task_inprocess' then processing -1 else processing end, status = case when  succeed + failed = total then "task_done" else status end where create_time = new.create_time and id = new.task_id;
        end if;
    end if;
end$
DELIMITER ;

存储过程

存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。

create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end

调用存储过程

存储过程可以在三种环境下被调用：

command命令下，基本语法为：exec sp_name [参数名]；
SQL环境下，基本语法为：call sp_name [参数名]；
PL/SQL环境下，基本语法为：begin sp_name [参数名] end；

MySQL命令