聚合函数

8.1 各种统计函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 总数
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

8.2 With RollUp 进行汇总统计

在分组统计数据的基础上再进行统计汇总,即用来得到 group by 的汇总信息

数据表操作对应信息

Information_Schema 表 Show
Schemata Show Database
Tables Show Tables
Columns Show Columns

9.1 如何正确的使用元数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 确定数据库是否存在
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 = ?

9.2 显示创建数据表结构

1
2
3
4
5
# 显示创建数据表结构
Show Create Table tbl_name;

# 显示列
Show Columns From tbl_name;

9.3 获取服务器元数据

语句 语句生成的信息
Select Version() 服务器版本
Select Database() 默认的数据库名称
Select User() 客户端连接时候给出的当前用户
Select Current_User() 用来检查客户端权限
Show [Global] Status 服务器的全局状态指示器, 没有 Global 显示当前
Show Variables 服务器配置变量

9.4 确定服务器支持的存储引擎

1
Show Engines;

Percona 系列工具介绍以及在 CentOS 安装

下载和安装 percona toolkit 的包

Step1 : 安装 Repo

1
2
3
4
5
# 清华源(稳定)
$ 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

Step2 : 替换 Release 更新地址并更新 yum 的 repo 缓存

1
2
3
4
5
# 腾讯云
$ 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

更新缓存, 启用 tools 工具

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

更新 tools 的源地址

需要启用 tools 之后才能更新源地址

1
2
3
4
5
# 腾讯云
$ 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

Step3 : 安装工具

1
$ yum install -y percona-toolkit

percona-toolkit 介绍

使用文档 Percona Release Usage

percona-toolkit 是一组高级命令行工具的集合,可以查看当前服务的摘要信息,磁盘检测,分析慢查询日志,查找重复索引,实现表同步等等

percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的 工具,!这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的 DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。

命令说明

有的 32 个命令,可以分为 7 大类

工具命令 工具作用 备注
pt-duplicate-key-checker 列出并删除重复的索引和外键
pt-online-schema-change 在线修改表结构
pt-query-advisor 分析查询语句,并给出建议,有 bug 已废弃
pt-show-grants 规范化和打印权限
pt-upgrade 在多个服务器上执行查询,并比较不同
pt-index-usage 分析日志中索引使用情况,并出报告
pt-pmp 为查询结果跟踪,并汇总跟踪结果
pt-visual-explain 格式化执行计划
pt-table-usage 分析日志中查询并分析表使用情况 pt 2.2 新增命令
pt-config-diff 比较配置文件和参数
pt-mysql-summary 对 mysql 配置和 status 进行汇总
pt-variable-advisor 分析参数,并提出建议
pt-deadlock-logger 提取和记录 mysql 死锁信息
pt-fk-error-logger 提取和记录外键信息
pt-mext 并行查看 status 样本信息
pt-query-digest 分析查询日志,并产生报告 常用命令
pt-trend 按照时间段读取 slow 日志信息 已废弃
pt-heartbeat 监控 mysql 复制延迟
pt-slave-delay 设定从落后主的时间
pt-slave-find 查找和打印所有 mysql 复制层级关系
pt-slave-restart 监控 salve 错误,并尝试重启 salve
pt-table-checksum 校验主从复制一致性
pt-table-sync 高效同步表数据
pt-diskstats 查看系统磁盘状态
pt-fifo-split 模拟切割文件并输出
pt-summary 收集和显示系统概况
pt-stalk 出现问题时,收集诊断数据
pt-sift 浏览由 pt-stalk 创建的文件 pt 2.2 新增命令
pt-ioprofile 查询进程 IO 并打印一个 IO 活动表 pt 2.2 新增命令
pt-archiver 将表数据归档到另一个表或文件中
pt-find 查找表并执行命令
pt-kill Kill 掉符合条件的 sql 常用命令
pt-align 对齐其他工具的输出 pt 2.2 新增命令
pt-fingerprint 将查询转成密文 pt 2.2 新增命令

上面是 pt 工具各个命令的基本功能介绍,可以使用 command –help 来查看每个命令的具体作用和使用方法;

有的命令也可以使用 man command 命令查询相关命令详细信息。

Xtrabackup

对于 Percona-xtrabackup , 有这几个版本

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

5. 使用日期和时间

6.1 选择合适的日期或者时间变量类型

1
2
3
4
5
6
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

6.2 MySQL 日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 进行日期的提取
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()
格式化字符 含义
%Y 4 位年份
%y 2 位年份
%M 完整的月份名称(January-December)
%b 月份的前三个字母(Jan-Dec)
%m 数字月份, 带有前缀 0 (01-12)
%c 数字月份, 无前缀(1-12)
%d 数字日期, 带有前缀 0 (01-31)
%e 数字日期, 无前缀 (1, 31)
%W 工作日名称 (Sunday - Saturday)
%r 时间, 12 小时, 以 AM - PM 结尾
%T 时间, 24 小时制
%H 小时, 带有前缀 0 (00-23)
%i 分钟, 带有 0(00-59)
%s 秒 (00-59)
%% ‘%’ 文字字符

6.3 客户端

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

6.4 Timestamp 的自动更新

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

6.5 日期分离函数

函数 返回值
Year()
Month()
MonthName() 月完整名称 (January-December)
DayOfMonth() 1…31
DayName() 一周中的天数 (Sunday-Saturday)
DayOfWeek() 1-7, 对应 (Sunday - Saturday)
WeekDay() 0-6, 对应 (Monday - Sunday)
DayOfYear() 一年中的天数值 (1…366)
Hour() 小时数
Minute() 分钟数
Second() 秒数

6.6 日期组成函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 合成时间
MakeTime(10, 30, 30)

# 合成日期
MakeDate(2007, 60)

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

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

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

6.7 计算时间间隔

1
2
3
4
5
6
7
8
# 日期间隔
DateDiff(date1, date2)

# 时间间隔
TimeDiff(time1, time2)

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

6.8 时间计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 时间加法
AddTime()

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

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

d + Interval val unit
d - Interval val unit

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

数据导入导出

10.1 使用 Load Data 和 mysqlimport 导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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), 默认全部
1
2
3
4
5
6
$ mysqlimport --local tbl_name file.txt
--fields-terminated-by=':'
--fields-enclosed-by=':'
--fields-escaped-by=':'
--lines-terminated-by='\r'
--columns=a,b,c

10.2 导入时候对数据进行预处理

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

10.3 mysql 导出

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

10.4 SQLMode

通过设置 sql_mode 变量来开启输入数据接收限制.

11.1 生成和使用序列

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

12.1 在表中找到与另一个表中的行相匹配的行

使用 join

一个完全的连接将产生所有可能的行联合, 即笛卡尔积.正因为如此同时也很少使用两个以上表的联合, 连接通常会包含 On 或 Using 子句以指定如何在表间进行连接

1
2
3
4
5
6
7
8
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)

Inner Join 所编写的连接为内部连接, 这意味着它们只为某个表中与其他表的值相匹配的值产生结果.

外部连接(Left Join/Right Join)同样可以产生这些匹配, 此外还可以为你显示某个表中的哪些值于另外一个表中的值是不相符的.

Left Join 的意思是左侧为基准, 右侧数据可以为空, Right Join 则相反.

4. 与字符串共舞

5.1 字符串属性

字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 看看系统中有哪些字符集
mysql > Show Character Set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
...
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
...

Length 和 Char_Length 差别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 |
+------------+-----------------+

Collation

如果没有指明哪种 Collation, 则 Default 为 Yes 的则为默认的字符集.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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)

名称后缀的说明

  • ci : (case insensitive) 大小写不敏感
  • cs : (case sensitive) 大小写敏感
  • bin: (binary) 二进制

对于不同的语种也有不同的排序方法.

1
2
3
4
5
6
7
8
# 排序 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;

5.2 选择字符串数据类型

根据这几个来进行判定

  • 是否二进制
  • 是否大小写敏感
  • 字串的最大长度是多少
  • 定长值还是变长值
  • 是否需要保留尾部空格
  • 是否有固定的允许值集合

字串长度

二进制数据类型 普通文本 最大长度 说明
Binary Char 255 2^8
VarBinary VarChar 65535 2^16
TinyBlob TinyText 255 2^8
Blob Text 65535 2^16
MediumBlob MediumText 16777215 2^24
LongBlob LongText 4294967295 2^32

字串空格保留
如果你想保留出现在储存的原始字符串尾部的填充值, 那就应该使用一个没有截除动作发生的数据类型, 例如, 如果你正存储可能以空格结尾的字串(普通文本), 并且想保留这个空格, 就应该使用 VarChar 或者 Text 数据类型之一.

5.3 正确设置连接字符集

配置文件

1
2
[mysql]
default-character-set=utf8

应用程序中使用

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

连接时候指定

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

5.4 查询字符串

字串放在单引号或者双引号内

启用 ANSI_QUOTES SQL 模式时候不得使用双引号, 一般来讲使用单引号会比较保险

16 进制字符的表示

1
2
3
0x61625ad43
x'61625ad43'
X'61625ad43'
1
2
3
4
5
6
mysql> select 0x61;
+------+
| 0x61 |
+------+
| a |
+------+

使用由字符集加下划线(‘_‘) 前缀组成的引入器

1
2
_latin1 'abcd'
_ucs2 'abcd'

查询过程中使用转义

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

5.5 检查字符集和字符排序

使用 Charset()Collation() 函数来检查字符集和排序

使用 Convert() 转换字符串的字符集

1
2
3
4
5
6
7
8
9
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)

使用 Collate 操作符改变字符串的 Collation

1
mysql> Set @s2 = @s1 Collate latin1_spanish_ci;

使用 Binary 操作符来进行二进制转换, 同 Convert(@s1 Using binary) 相同.

5.6 字串操作函数

Upper() : 更改为大写
Lower() : 更改为小写
Left(str, 2) : 左侧截取
Right(str, 2) : 右侧截取
Mid(str, 3, 1) : 中间截取
SubString(str, 2, 3) : 字符随意截取
SubString_Index(str, c, n) : 左侧查找 c 在第 n 次出现的位置
Concat() : 字符串拼接
Locate(search, subject) : 确定一个字串中是否含有另一个字串

5.7 使用 SQL 模式进行匹配

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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'

5.8 使用正则表达式进行匹配

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

使用方法, 使用 Regexp 关键词进行匹配

1
... Regexp '^co';

支持 Posix 类

对应说明
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Whitespace characters
[:cntrl:] Control characters
[:digit:] Digit characters
[:graph:] Graphic characters
[:lower:] Lowercase alphabetic characters
[:print:] Graphic or space characters
[:punct:] Punctuation characters
[:space:] Space, tab, newline, and carriage return
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexadecimal digit characters

正则表达式匹配不要求目标串与模式串完全匹配

5.9 使用 FullText 查询

标准语法

1
2
3
4
5
6
7
8
# 单列
... Match (Column) Against ('str');

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

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

配置最小单词

1
2
[mysqld]
ft_min_word_len=3

禁止搜索的单词

这种方法可以匹配多词, 单词, 移除部分单词

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

特殊字符匹配

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

词组查询

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

2. 从表中查询数据

3.1 列查询

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

3.2 指定行查询(条件筛选)

1
2
3
4
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';

3.3 格式化显示查询结果

这里利用各种的函数来格式化相应的位置

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

使用 as 对列使用别名
列别名在 where 中不可用
使用 distinct 进行唯一性处理

3.4 调试比较表达式

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

3.5 null 值的比较

对于 null 值使用 null 的比较操作符

is null, is not null, <=>

这里有两个函数需要注意

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

这两个函数等价.

3.6 排序/视图/多表查询

1
2
3
... order by a;
... order by a, b;
... order by a asc, b;

多表查询

1
2
3
4
5
... from profile 
inner join
profile_contact
on
profile.id = profile_contact.profile_id

子查询

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

查询头尾取出数据

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

结果集再次排序

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

7.1 使用 Order By

1
2
3
4
5
6
7
8
9
10
11
# 标准
... Order By name [Desc][, title [Asc]];

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

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

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

7.2 查询条件

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

7.3 按照用户自定义排序

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

7.4 Enum 排序

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

4. 表管理

4.1 克隆表结构和数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 创建表结构
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;

4.2 使用临时表

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

不同的数据库连接可以创建同名的临时表, 这些表之间不互相影响. 临时表对数据库连接生效
如果临时表没有删除则在下次使用的时候会报错, 所以使用前最好先对数据表是否存在做相应的判定.

1
2
# 删除
Drop Temporary Table If Exists tbl_name
1
2
# 唯一性临时表
Select Connection_Id();

4.3 检查或者改变某个表的搜索引擎

1
2
3
4
5
6
7
8
9
10
# 显示引擎
# 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;
1
2
# 修改引擎
Alt Table cookers Engine = InnoDb

1. 使用 Mysql 客户端

1.1 建立 mysql 用户账号

1
2
3
4
5
6
7
8
# 创建用户
CREATE USER 'cbuser'@'%' IDENTIFIED WITH mysql_native_password AS 'cbpass';
GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `1dailian\_v2`.* TO 'remote'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# 创建用户并授权
GRANT ALL ON daniu.* TO 'cbuser' @'localhost' IDENTIFIED BY 'cbpass';

1.2 创建数据库和样表

1
2
3
4
5
CREATE DATABASE cookbook;
USE cookbook;
CREATE TABLE limbs ( thing VARCHAR ( 20 ), legs INT, arms INT );
INSERT INTO limbs VALUES('human', 2, 2);
SELECT * FROM limbs;

1.3 启动和停止 MySQL

1
2
3
4
5
6
7
8
# 启动
$ mysql -h host -p -u cbuser

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

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

1.4 使用可选项文件来指定链接

my.ini / my.cnf 可以进行相应的配置

  • 使用组的方式来组织数据
  • 只允许使用选项的长格式
  • = 前后允许存在空格
  • 选项没有值, 可以略去 =value 部分
  • 如果选项值中包含空格以及其他特殊符号, 可以使用 引号(单引号/双引号) 将数值包裹起来
  • 不需要某行则绿区即可
  • 文件中可以定义多个组
  • 多次出现, 最后出现的拥有最高优先级
  • #, ; 开头的数据会被忽略
1
2
3
4
5
6
[client]
host=localhost
;user=root

[mysql]
skip-auto-rehash

1.5 保护选项文件不被其他用户读取

1
$ chmod 600 my.cnf

1.6 混合使用命令行参数和选项文件参数

命令行参数的优先级比选项文件中参数优先级高

1.7 找不到 mysql 应该怎么做

将 mysql 安装目录放到环境变量中.

1.8 发起 sql 语句

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

1.9 从文件中读取语句

1
2
3
4
$ mysql cookbook < filename.sql
$ mysql -uroot -p
$ use cookbook
$ source filename.sql

1.10 打印错误

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

1.11 让 mysql 读取其他程序的 数据

1
2
3
4
5
6
7
8
9
10
11
# 从文件读取sql
$ mysql cookbook < limbs.sql

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

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

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

1.12 一行输入所有 sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ 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 |
+---------------------+

1.13 预防查询输出超出屏幕范围

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 指定 --pager 使其每次都显示一行输出
$ mysql -uroot --pager=/usr/bin/less

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

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

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

# 恢复基本输出
mysql> \n

1.14 查询结果输出

交互模式
交互模式下输出的是表格

非交互模式
输出的是以制表符分隔的数据体

-t or --table
如果输出格式强制使用 table 表格输出可以携带参数
-B or --batch
如果输出格式强制使用批量(定界符)输出, 可以使用
-H or --html
生成 html 格式
-X or --xml
使用 XML 格式输出
-ss or --skip-column-names
不在查询中输出包含列头部

1.15 查询结果二次转换

如果查询结果并不能按照我们的期望输出, 可以使用 sed, tr 来进行二次转换

1.16 使长输出行更具可读性

1
mysql> show full columns from limbs\G

-E or --vertical
进行垂直输出

1.17 冗长输出和简介输出

-v or -vv or -vvv
冗长输出
-s or -ss or -sss
进行简洁输出

1.18 记录操作步骤

-tee or \T or \t
进行脚本记录的输出, 这些输出可供记录并且可以用作批处理文件

1.19 用户自定义变量

1
2
3
4
5
6
# 从执行过程中获取值
mysql > select @id:=id from libms;
mysql > select * from abcolumsn where id = @id;

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

1.20 mysql 数学函数

1
select 20/sqrt(64);

1.21 shell 脚本中使用 mysql

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