# 总数
Count(*)

# Count + If 组合
Count(If(DayOfWeek(trav_date) In (1, 7), 1, Null))

# 最小
Min()

# 最大
Max()

# 总数
Sum()

# 平均
Avg()

# 唯一值
Distinct()

# 子群/分组/聚类函数
# 聚类函数会忽略 Null 值
... Group By trav_date

# 子群/分组/聚类函数选择
# Having 的操作是在已经做好分组的基础上进行的筛选
... Group By trav_date Having Count(*) > 3

# 确定数据库是否存在
Select Schema_Name
    From Information_Schema.Schemata
    Where Schema_Name = ?

# 确定数据表是否存在
Select Table_Name
    From Information_Schema.Tables
    Where Schema_Name = ? and Table_Name = ?

# 列信息
Select *
    From Information_Schema.Columns
    Where Table_Schema = ? and Table_Name = ?
        And Column_Name = ?

# 显示创建数据表结构
Show Create Table tbl_name;

# 显示列
Show Columns From tbl_name;

Show Engines;

# 清华源(稳定)
$ yum install https://mirrors.tuna.tsinghua.edu.cn/percona/yum/percona-release-latest.noarch.rpm

# 官方
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 腾讯云
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.cloud.tencent.com\/percona/g' /etc/yum.repos.d/percona-original-release.repo

# 清华
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.tuna.tsinghua.edu.cn/g' /etc/yum.repos.d/percona-original-release.repo

$ yum update
$ yum list | grep percona-toolkit
$ percona-release enable tools

# 腾讯云
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.cloud.tencent.com\/percona/g' /etc/yum.repos.d/percona-tools-release.repo

# 清华
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.tuna.tsinghua.edu.cn\/percona/g' /etc/yum.repos.d/percona-tools-release.repo

$ yum install -y percona-toolkit

percona-xtrabackup-22
percona-xtrabackup-24       # for 5.6/5.7
percona-xtrabackup-80       # for 8.0

CCYY-MM-DD   : 1000-01-01 - 9999-12-31
hh:mm:ss     : -838:59:59 - 838:59:59
DateTime(CCYY-MM-DD hh:mm:ss)
    : 1000-01-01 00:00:00 - 9999-12-31 23:59:59
Timestamp
    : 1970-01-01 00:00:00 - 2037-12-31 23:59:59

# 进行日期的提取
Str_To_Date('May 13, 2007', '%M %D, %Y')

# 日期格式化
Date_Format(date, '%M %D, %Y');

# 时间格式化
Time_Format(time, '%T');

# 转换时区
Convert_Tz()

# 当前日期
CurDate()

# 当前时间
CurTime()

# 当前日期时间
Now()

# Utc
Utc_Date(), Utc_Time(), Utc_Timestamp()

# 设置默认时区
mysqld --default-time-zone

# 根据设定来更新当前时间戳 + 更新当前时间戳
Create Table `t` (
    ts timestamp Not Null Default Current_Timestamp
       On Update Current_Timestamp
)

# 合成时间
MakeTime(10, 30, 30)

# 合成日期
MakeDate(2007, 60)

# 时间 + 秒的转换
Time_To_Sec()
Sec_To_Time()

# 时间 + 日期的转换
To_Days()
From_Days()

# 时间戳转换
Unix_TimeStamp()
From_UnixTime()

# 日期间隔
DateDiff(date1, date2)

# 时间间隔
TimeDiff(time1, time2)

# 时间戳间隔
TimestampDiff(Unit, dt1, dt2)

# 时间加法
AddTime()

# 时间/日期时间 相加
Timestamp()

DateAdd(d, interval)
DateSub(d, interval)

d + Interval val unit
d - Interval val unit

# 计算生日
TimestampDiff(unit, birth, current)

Load Data Local InFile 'file.txt' Into Table tbl_name
    Fields Terminated By ':'
        Enclosed By '"'   # 指定引用
        Escaped By ''     # 指定转义
    Lines Terminated By '\r'
    Ignore 1 Lines;       # 指定忽略
# 默认使用制表符 \t
# 换行代表一行数据
# 需要 File 权限
# 文件位于服务器机器上
# 相对路径的文件位于 数据库存储 位置
# 没有 Local 读取服务器文件
# 存在 Local 读取本地文件
# Replace Into 是对数据进行替代操作, 存在则不进行处理
# 使用 Show Warnings 来显示相关的警告信息
# 表格后可以指定列 tbl_name(col1, col2, col3), 默认全部

$ mysqlimport --local tbl_name file.txt
    --fields-terminated-by=':'
    --fields-enclosed-by=':'
    --fields-escaped-by=':'
    --lines-terminated-by='\r'
    --columns=a,b,c

Load Data Local InFile 'file.txt' Into Table tbl_name
    (@cola, @colb, @colc)
    Set
        dt = concat(@cola, ' ', $colc),
        df = substring_index(@colb, ' ', 1)
        ...;

$ mysql -e "select ...." --skip-column-names tbl_name
    >> data.txt

Auto_Increment # 列
id Int Unsigned Not Null Auto_Increment,
Primary Key(id)

Select * From artist, painting
    where artist.a_id = painting.a_id

Select * From artist Inner Join painting
    on artist.a_id = painting.a_id

Select * From artist Inner Join painting
    on using(a_id)

mysql> Set @s = Convert('abc' Using ucs2);
mysql> Select Length(@s), Char_Length(@s);
+------------+-----------------+
| Length(@s) | Char_Length(@s) |
+------------+-----------------+
|          6 |               3 |
+------------+-----------------+

mysql> Set @s = Convert('abc' Using utf8);
mysql> Select Length(@s), Char_Length(@s);
+------------+-----------------+
| Length(@s) | Char_Length(@s) |
+------------+-----------------+
|          3 |               3 |
+------------+-----------------+

mysql> Show Collation Like 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.01 sec)

# 排序 A, a, b, B (大小写不敏感, a 在 b 前边即可)
Select c From tb_name Order By c Collate latin_swedish_ci;

# A, a, B, b (大小写敏感, A 在 a 前, B 在 b 前)
.... Collate latin_swedish_cs;

# A, B, a, b
.... Collate latin_swedish_bin;

mysql> Set Names 'utf8';
mysql> Set Names 'utf8' Collate 'utf8-general_ci'

jdbc:mysql://{host}/{db}?characterEncoding=UTF-8

0x61625ad43
x'61625ad43'
X'61625ad43'

mysql> select 0x61;
+------+
| 0x61 |
+------+
| a    |
+------+

_latin1 'abcd'
_ucs2 'abcd' 

Select "He said, \"Boo!\"";

mysql> Set @s1 = 'my';
mysql> Set @s2 = Convert(@s1 Using latin1);
mysql> Select Charset(@s1), Charset(@s2);
+--------------+--------------+
| Charset(@s1) | Charset(@s2) |
+--------------+--------------+
| utf8         | latin1       |
+--------------+--------------+
1 row in set (0.00 sec)

mysql> Set @s2 = @s1 Collate latin1_spanish_ci;

# Like
... Like 'co%';     # 开头
... Like '%er';     # 结尾
... Like '%er%';    # 包含

# _
... Like '___per';  # 完全匹配

# 模式匹配和字串函数的使用比较
Year(d) = 1976       d Like '1976-%'
Month(d) = 4         d Like '%-04-%'
DayOfMonth(d) = 1    d Like '%-01'

str Like 'abc%'      Left(str, 3) = 'abc'
str Like '%abc'      Right(str, 3) = 'abc'

^         : 开始
$         : 结束
.         : 匹配任何字符
[...]     : 匹配括号内任意字符
[^...]    : 不匹配括号内任意字符
p1|p2|p3  : 多选1
*         : 0+
+         : 1+
{n}       : 匹配 n 次
{m, n}    : 最少 m, 最多 n 个

... Regexp '^co';

# 单列
... Match (Column) Against ('str');

# 多单词查询, 并不是单词越多越精确
... Match (Column) Against ('str1 str2 str3')

# 多列, 多词
... Match (Column, Column2) Against ('str1 str2 str3')

[mysqld]
ft_min_word_len=3

... Match(Column) Against('+David -Mark' In Boolean Mode)

... Match(Column) Against('David*' In Boolean Mode)

... Match(Column) Against('"Disk Path"' In Boolean Mode)

mysql > select * from mail;
mysql > select mail, name from mail;

mysql > select mail, name from mail where name='zhao';
mysql > select mail, name from mail where name like 'zh%';

mysql > select mail, name from mail where name='zhao' and area ='beijing';

CONCAT()     : 链接字符串
MONTHNAME()  : 月份名称
DayOfMonth() : 天
Year()      : 年份
DateFormat   : 日期格式化

# 这里的 name='good' 便是表达式
mysql > select mail, name = 'good' from name

If(expr1 is not null, expr1, expr2);
IfNull(expr1, expr2)

... order by a;
... order by a, b;
... order by a asc, b;

... from profile 
    inner join
        profile_contact
    on 
        profile.id = profile_contact.profile_id

... from profile_contact 
    where
        profile_id in (select id from profile where area='beijing')

... Limit 1
... Limit 2, 1

select * from 
   (select name from profile order by birth desc limit 4) as t
   order by birth asc;

# 标准
... Order By name [Desc][, title [Asc]];

# 表达式排序
... Order By Floor((size + 1023) / 1024)

# 时间排序
... Order By date;

# 可以根据字串的某部分进行排序
... Order By SubString_Index(bsn, '-', -3)

# If
... Order By If(a=b, 0, 1);

# Field
... Order By Field(name, 'Zhang', 'Li', 'Wang')

# 使用 Cast 进行转换为字串
# 默认 Enum 存储的是数值
... Order By Cast(day as Char);

# 创建表结构
Create Table new_table Like ori_table

# 表数据插入
Insert Into new_table Select * From ori_table Where ...

# 表数据指定字段插入
Insert Into new_table(col_a, col_b) Select col_c,col_d From ori_table Where ...

# 查询并且新建
Create Table new_table Select col_a, col_b From ori_table

# 新建表并使用索引
Create Table new_table(Primary Key (id), Index (state, city)) Select id, state, city From ori_table

# 创建数据表并修改结构
Create Table new_table(Primary Key (id), Index (state, city)) Select id, state, city From ori_table;
Alt Table new_table Modify id Int Unsigned Not Null Auto_Increment;

# 修改表的自增数据
alter table tb_name AUTO_INCREMENT = 100000;

# 创建临时表
Create Temporary Table tbl_name (...col define ...)

# 删除
Drop Temporary Table If Exists tbl_name

# 唯一性临时表
Select Connection_Id();

# 显示引擎
# From Information_Schema
Select Engine From Information_Schema.tables
   Where Table_Schema = 'cookbook' And Table_Name ='cookers';

# Status
Show Table Status Like 'cookers';

# Create Table
Show Create Table cookers;

# 修改引擎
Alt Table cookers Engine = InnoDb

# 启动
$ mysql -h host -p -u cbuser

# 导出
$ mysqldump -h localhost -p -u cbuser cookbook > cookbook.sql

# 服务器维护
$ mysqladmin -p -uroot shutdown

;     # 作为末尾输入
\g    # 作为结束替代输入  '\go'
\c    # 作为取消输入     '\cancel'
">    # 需要输入 双引号 来结束
'>    # 需要输入 单引号 来结束
/*>   # 需要输入 */ 来结束

$ mysql cookbook < filename.sql
$ mysql -uroot -p
$ use cookbook
$ source filename.sql

$ perror 24
OS error code  24:  Too many open files

# 从文件读取sql
$ mysql cookbook < limbs.sql

# 另外一种方法的文件读取
$ cat limbs.sql | mysql cookbook

# 跨境传输数据
$ mysqldump cookbook | mysql -h other.host.com cookbook

# 生成数据
$ gen-test-data.sh | mysql cookbook

$ mysql -uroot -e "select count(*) from mk_pam_account" daniu

# 多条数据
$ mysql -uroot -e "select count(*) from mk_pam_account;select now()" daniu
+----------+
| count(*) |
+----------+
|     2342 |
+----------+
+---------------------+
| now()               |
+---------------------+
| 2019-04-10 23:44:49 |
+---------------------+

# 指定 --pager 使其每次都显示一行输出
$ mysql -uroot --pager=/usr/bin/less

# 无参数代表使用系统默认分页
$ mysql -uroot --pager

# mysql 中使用分页
# 设置默认分页
mysql> \P

# 设置指定分页
mysql> \P /usr/bin/less

# 恢复基本输出
mysql> \n

mysql> show full columns from limbs\G

# 从执行过程中获取值
mysql > select @id:=id from libms;
mysql > select * from abcolumsn where id = @id;

# 设置值使用 set
mysql > set @sum = 4+7;

select 20/sqrt(64);

# 多行文本的输入并且支持变量
mysql cookbook <<< INPUT
    select * from $1
INPUT