Note for mysql

单个多库备份 sql 文件中分离单个库

sed -n '/^-- Current Database: bk_nodeman/,/^-- Current Database: `/p' fulldump.sql

nohup gunzip -c < mysql-backup.sql | mysql -h0.0.0.1 -uroot -ppassword --default-character-set=utf8 2>err.log &

解压已经切割的 sql 压缩包

cat ../mysql-backup.sql.split.00* | tar -zx

表记录条数

select count(*) from table_name;

```  


## 清空表

```sql

truncate table table_name;

delete * from table_name;

其中truncate操作中的table可以省略，delete操作中的*可以省略。这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：

truncate是整体删除（速度较快）， delete是逐条删除（速度较慢）。

truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因。

truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。

如果只需删除表中的部分记录，只能使用DELETE语句配合where条件。 DELETE FROM table_name WHERE……

查看该数据库实例下所有库大小

use information_schema;

select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \

as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

查看该实例下各个库大小

select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, \

sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, \

count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;

查看单个库的大小

select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size, \

concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size, \

concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free, \

concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size\

from information_schema.tables where table_schema = 'table_name';

查看单库下所有表的状态

select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024) \

as index_mb, ((data_length+index_length)/1024/1024) as all_mb, table_rows \

from tables where table_schema = 'table_name';

清理relay_log

SET GLOBAL relay_log_purge=1; FLUSH LOGS; SET GLOBAL relay_log_purge=0;

删除relay log（rm –f  /path/to/archive_dir/*）

SQL 速查表

1. 查找数据的查询

SELECT: 用于从数据库中选择数据

SELECT FROM table_name;

DISTINCT: 用于过滤掉重复的值并返回指定列的行

SELECT DISTINCT column_name;

WHERE: 用于过滤记录/行

SELECT column1, column2 FROM table_name WHERE condition;

SELECT FROM table_name WHERE condition1 AND condition2;

SELECT FROM table_name WHERE condition1 OR condition2;

SELECT FROM table_name WHERE NOT condition;

SELECT FROM table_name WHERE condition1 AND (condition2 OR condition3);

SELECT FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

ORDER BY: 用于结果集的排序，升序（ASC）或者降序（DESC）

SELECT FROM table_name ORDER BY column;

SELECT FROM table_name ORDER BY column DESC;

SELECT FROM table_name ORDER BY column1 ASC, column2 DESC;

SELECT TOP: 用于指定从表顶部返回的记录数

SELECT TOP number columns_names FROM table_name WHERE condition;

SELECT TOP percent columns_names FROM table_name WHERE condition;

# 并非所有数据库系统都支持SELECT TOP。 MySQL 中是LIMIT子句

SELECT column_names FROM table_name LIMIT offset, count;

LIKE: 用于搜索列中的特定模式，WHERE 子句中使用的运算符

% (percent sign) 是一个表示零个，一个或多个字符的通配符

_ (underscore) 是一个表示单个字符通配符

SELECT column_names FROM table_name WHERE column_name LIKE pattern;

LIKE ‘a%’    （查找任何以“a”开头的值）

LIKE ‘%a’    （查找任何以“a”结尾的值）

LIKE ‘%or%’  （查找任何包含“or”的值）

LIKE ‘_r%’   （查找任何第二位是“r”的值）

LIKE ‘a_%_%’ （查找任何以“a”开头且长度至少为3的值）

LIKE ‘[a-c]%’（查找任何以“a”或“b”或“c”开头的值）
``


### **IN**: 用于在 WHERE 子句中指定多个值的运算符

本质上，IN运算符是多个OR条件的简写
```sql
SELECT column_names FROM table_name WHERE column_name IN (value1, value2, …);

SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);

BETWEEN: 用于过滤给定范围的值的运算符

SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;

SELECT FROM Products WHERE (column_name BETWEEN value1 AND value2) AND NOT column_name2 IN (value3, value4);

SELECT FROM Products WHERE column_name BETWEEN #01/07/1999# AND #03/12/1999#;

NULL: 代表一个字段没有值

SELECT FROM table_name WHERE column_name IS NULL;

SELECT FROM table_name WHERE column_name IS NOT NULL;

AS: 用于给表或者列分配别名

SELECT column_name AS alias_name FROM table_name;

SELECT column_name FROM table_name AS alias_name;

SELECT column_name AS alias_name1, column_name2 AS alias_name2;

SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;

UNION: 用于组合两个或者多个 SELECT 语句的结果集的运算符

每个 SELECT 语句必须拥有相同的列数

列必须拥有相似的数据类型

每个 SELECT 语句中的列也必须具有相同的顺序

SELECT columns_names FROM table1 UNION SELECT column_name FROM table2;

UNION 仅允许选择不同的值, UNION ALL 允许重复

ANY|ALL: 用于检查 WHERE 或 HAVING 子句中使用的子查询条件的运算符

ANY 如果任何子查询值满足条件，则返回 true。

ALL 如果任何子查询值满足条件，则返回 true。

SELECT columns_names FROM table1 WHERE column_name operator (ANY|ALL) (SELECT column_name FROM table_name WHERE condition);

GROUP BY: 通常与聚合函数（COUNT，MAX，MIN，SUM，AVG）一起使用，用于将结果集分组为一列或多列

SELECT column_name1, COUNT(column_name2) FROM table_name WHERE condition GROUP BY column_name1 ORDER BY COUNT(column_name2) DESC;

HAVING: HAVING 子句指定 SELECT 语句应仅返回聚合值满足指定条件的行。它被添加到 SQL 语言中，因为WHERE关键字不能与聚合函数一起使用。

SELECT COUNT(column_name1), column_name2 FROM table GROUP BY column_name2 HAVING COUNT(column_name1) > 5;

2. 修改数据的查询

INSERT INTO: 用于在表中插入新记录/行

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

INSERT INTO table_name VALUES (value1, value2 …);

UPDATE: 用于修改表中的现有记录/行

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

UPDATE table_name SET column_name = value;

DELETE: 用于删除表中的现有记录/行

DELETE FROM table_name WHERE condition;

DELETE FROM table_name;

3. 聚合查询

COUNT: 返回出现次数

SELECT COUNT (DISTINCT column_name);

MIN() and MAX(): 返回所选列的最小/最大值

SELECT MIN (column_names) FROM table_name WHERE condition;

SELECT MAX (column_names) FROM table_name WHERE condition;

AVG(): 返回数字列的平均值

SELECT AVG (column_name) FROM table_name WHERE condition;

SUM(): 返回数值列的总和

SELECT SUM (column_name) FROM table_name WHERE condition;

4. 连接查询

INNER JOIN: 内连接，返回在两张表中具有匹配值的记录

SELECT column_names FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

SELECT table1.column_name1, table2.column_name2, table3.column_name3 FROM ((table1 INNER JOIN table2 ON relationship) INNER JOIN table3 ON relationship);

LEFT (OUTER) JOIN: 左外连接，返回左表（table1）中的所有记录，以及右表中的匹配记录（table2）

SELECT column_names FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

RIGHT (OUTER) JOIN: 右外连接，返回右表（table2）中的所有记录，以及左表（table1）中匹配的记录

SELECT column_names FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

FULL (OUTER) JOIN: 全外连接，全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 以 NULL 填充。

SELECT column_names FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Self JOIN: 自连接，表自身连接

SELECT column_names FROM table1 T1, table1 T2 WHERE condition;

5. 视图查询

CREATE: 创建视图

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

SELECT: 检索视图

SELECT FROM view_name;

DROP: 删除视图

DROP VIEW view_name;

6. 修改表的查询

ADD: 添加字段

ALTER TABLE table_name ADD column_name column_definition;

MODIFY: 修改字段数据类型

ALTER TABLE table_name MODIFY column_name column_type;

DROP: 删除字段

ALTER TABLE table_name DROP COLUMN column_name;