02.MySQL_积累

（1）SHOW相关命令

1.show常用命令：

#返回可用数据库：

SHOW DATABASES;

#返回当前数据库内可用表：

SHOW TABLES;

SHOW TABLES FROM database_name;

#显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间：

SHOW TABLE STATUS

SHOW TABLE STATUS FROM database_name;

#返回表中字段名、数据类型、是否允许NULL、键信息、默认值、其他：

SHOW COLUMNS FROM table_name;

SHOW COLUMNS FROM table_name FROM database_name;

SHOW COLUMNS FROM database_name.table_name;

DESCRIBE table_name;

#显示表的索引

SHOW INDEX FROM table_name;

#显示创建特定数据库的MySQL语句：

SHOW CREATE DATABASE database_name;

#显示创建特定表的MySQL语句：

SHOW CREATE TABLE database_name;

#显示系统中正在运行的所有进程，也就是当前正在执行的查询：

SHOW PROCESSLIST;

SELECT * FROM information_schema.`PROCESSLIST` WHERE db = 'database_name';

SELECT * FROM information_schema.processlist WHERE db = 'database_name' AND USER  = 'user_name' ORDER BY TIME DESC;

#显示系统变量的名称和值：

SHOW VARIABLES;

SHOW VARIABLES LIKE '%max_connections%';

#显示一些系统特定资源的信息，例如，正在运行的线程数量:

SHOW STATUS;

SHOW STATUS LIKE 'Threads%';

#显示最后一个执行的语句所产生的错误、警告和通知。

show warnings;

#只显示最后一个执行语句所产生的错误。

show errors;

#查询当前用户的信息：

SELECT user();

#查询用户的权限：

SELECT * FROM mysql.user WHERE Host = '10.101.84.174' AND User = 'user_name';

SHOW GRANTS FOR 'idb_rnd'; 

#显示服务器所支持的不同权限：

SHOW PRIVILEGES;

2.权限分为以下几种：

1.数据库/数据表/数据列权限：

Alter: 修改已存在的数据表(例如增加/删除列)和索引。

Create: 建立新的数据库或数据表。

Delete: 删除表的记录。

Drop: 删除数据表或数据库。

INDEX: 建立或删除索引。

Insert: 增加表的记录。

Select: 显示/搜索表的记录。

Update: 修改表中已存在的记录。

2.全局管理MySQL用户权限：

file: 在MySQL服务器上读写文件。

PROCESS: 显示或杀死属于其它用户的服务线程。

RELOAD: 重载访问控制表，刷新日志等。

SHUTDOWN: 关闭MySQL服务。

3.特别的权限：

ALL: 允许做任何事(和root一样)。

USAGE: 只允许登录--其它什么也不允许做。

3.show常用命令：

show tables或show tables from database_name或show database_name.tables;

解释：显示当前数据库中所有表的名称

 

show databases;

解释：显示mysql中所有数据库的名称

show processlist;

解释：显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看

他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

show table status;

解释：显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间

show columns from table_name from database_name; 或show columns from database_name.table_name;或show fields;

解释：显示表中列名称（和 desc table_name 命令的效果是一样的）

show grants for user_name@localhost;

解释：显示一个用户的权限，显示结果类似于grant 命令

show index from table_name;或show keys;

解释：显示表的索引

show status;

解释：显示一些系统特定资源的信息，例如，正在运行的线程数量

show variables;

解释：显示系统变量的名称和值

show privileges;

解释：显示服务器所支持的不同权限

show create database database_name;

解释：显示创建指定数据库的SQL语句

show create table table_name;

解释：显示创建指定数据表的SQL语句

show engies;

解释：显示安装以后可用的存储引擎和默认引擎。

show innodb status;

解释：显示innoDB存储引擎的状态

show logs;

解释：显示BDB存储引擎的日志

show warnings;

解释：显示最后一个执行的语句所产生的错误、警告和通知

show errors;

解释：只显示最后一个执行语句所产生的错误

show status 结果说明

列 含义

Name 表名

Type 表的类型 (ISAM，MyISAM或HEAP)

Row_format 行存储格式 (固定, 动态, 或压缩）

Rows 行数量

Avg_row_length 平均行长度

Data_length 数据文件的长度

Max_data_length 数据文件的最大长度

Index_length 索引文件的长度

Data_free 已分配但未使用了字节数

Auto_increment 下一个 autoincrement(自动加1）值

Create_time 表被创造的时间

Update_time 数据文件最后更新的时间

Check_time 最后对表运行一个检查的时间

Create_options 与CREATE TABLE一起使用的额外选项

Comment 当创造表时，使用的注释 (或为什么MySQL不能存取表信息的一些信息)。

show index 结果说明：

列 含义

Table 表名

Non_unique 0，如果索引不能包含重复。

Key_name 索引名

Seq_in_index 索引中的列顺序号, 从 1 开始。

Column_name 列名。

Collation 列怎样在索引中被排序。在MySQL中，这可以有值A（升序) 或NULL（不排序)。

Cardinality 索引中唯一值的数量。这可通过运行isamchk -a更改.

Sub_part 如果列只是部分被索引，索引字符的数量。NULL，如果整个键被索引。

show variables 结果说明：

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。

Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。

Connections 试图连接MySQL服务器的次数。

Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。

Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。

Delayed_writes 用INSERT DELAYED写入的行数。

Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。

Flush_commands 执行FLUSH命令的次数。

Handler_delete 请求从一张表中删除行的次数。

Handler_read_first 请求读入表中第一行的次数。

Handler_read_key 请求数字基于键读行。

Handler_read_next 请求读入基于一个键的一行的次数。

Handler_read_rnd 请求读入基于一个固定位置的一行的次数。

Handler_update 请求更新表中一行的次数。

Handler_write 请求向表中插入一行的次数。

Key_blocks_used 用于关键字缓存的块的数量。

Key_read_requests 请求从缓存读入一个键值的次数。

Key_reads 从磁盘物理读入一个键值的次数。

Key_write_requests 请求将一个关键字块写入缓存次数。

Key_writes 将一个键值块物理写入磁盘的次数。

Max_used_connections 同时使用的连接的最大数目。

Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。

Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。

Open_tables 打开表的数量。

Open_files 打开文件的数量。

Open_streams 打开流的数量(主要用于日志记载）

Opened_tables 已经打开的表的数量。

Questions 发往服务器的查询的数量。

Slow_queries 要花超过long_query_time时间的查询数量。

Threads_connected 当前打开的连接的数量。

Threads_running 不在睡眠的线程数量。

Uptime 服务器工作了多少秒。

（2）SELECT语句

1.LIMIT 3,5，从第3行以后取5行：

SELECT * FROM table_name WHERE is_deleted = 'n' ORDER BY id LIMIT 3,5;

SELECT * FROM table_name WHERE is_deleted = 'n' ORDER BY id LIMIT 5 OFFSET 3;

2.表名和列名均可以完全限定：

SELECT column_name FROM Table_name WHERE is_deleted = 'n';

SELECT table_name.column_name FROM dataBase_name.table_name WHERE is_deleted = 'n';

3.WHERE子句操作符：

= 等于   <> 不等于   != 不等于   < 小于   <= 小于等于   > 大于   >= 大于等于   BETWEEN 在指定的两个值之间

4.AND操作符优先级高于OR；即在处理OR操作符前，会有限处理AND操作符；

5.通配符： 通配符搜索的处理比其他更慢，因此尽量不用，或者放在最后；

% 匹配0个、1个或多个字符

_ 匹配单个字符

（3）正则表达式： REGEXP

1. 默认：

SELECT COUNT(*) FROM table_name WHERE is_deleted = 'n' AND title LIKE '%HF%';

SELECT COUNT(*) FROM table_name WHERE is_deleted = 'n' AND title REGEXP 'HF'; 

2.| 相当于 OR

SELECT COUNT(*) FROM table_name WHERE is_deleted = 'n' AND (title LIKE '%HF%' OR title LIKE '%LC%');

SELECT COUNT(*) FROM table_name WHERE is_deleted = 'n' AND title REGEXP 'HF|LC'; 

3.[] 匹配几个字符之一

SELECT * FROM table_name WHERE is_deleted = 'n' AND (address LIKE '%杭区%' OR court_name LIKE '%湖区%' OR court_name LIKE '%江区%');

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '杭区|湖区|江区';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[杭湖江]区';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[杭|湖|江]区';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '杭|湖|江';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[杭湖江]'; #结果错的为什么？

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[AF]';#这个是对的

4.[0-9a-zA-Z] 匹配范围(不区分大小写)

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[0-9a-z]';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[AF]0';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP 'A0|F0';

5.\\ 匹配特殊字符

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '\\[';

6.\\ 引用元字符

\\f 换页   \\n 换行   \\r 回车   \\t 制表   \\v 纵向制表

7.匹配字符类

# [:alnum:] 任意字母和数字 (同[a-zA-Z0-9])

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[[:alnum:]]0';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[0-9a-z]0';

# [:alnum:] 任意字符 (同[a-zA-Z])

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[[:alpha:]]0';

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '[a-zA-Z]0';

# [:blank:] 空格和制表符 (同[\\t])

# [:cntrl:] ASCII控制字符 (ASCII 0到31和127)

# [:digit:] 任意数字 (同[0-9])

# [:graph:] 与[:print:]相同，但不包括空格

# [:lower:] 任意大小写字母 (同[a-z])

# [:print:] 任意可打印字符

# [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符

# [:space:] 包括空格在内的任意空白字符 (同[\\f\\n\\r\\t\\v])

# [:upper:] 任意大写字母 (同[A-Z])

# [:xdigit:] 任意十六进制数字 (同[a-fA-F0-9])

8.匹配多个实例

*       0个或多个匹配

+       1个或多个匹配 (等于{1,})

?       0个或1个匹配 (等于{0,1})

{n}     指定数目的匹配

{n,}    不少于指定书目的匹配

{n,m}   匹配数目的范围 (m不超过255)

9.定位符

^          文本的开始

$          文本的结尾

[[:<:]]    词的开始

[[:<:]]    词的结尾

#以两个字母开头的法院名称：

SELECT * FROM table_name WHERE is_deleted = 'n' AND address REGEXP '^[a-z]{2,}';

（4）HEX函数

HEX函数: 可以把字段转为16进制，然后正则匹配 对应中文的code码

utf8汉字编码16进制的正则匹配格式是： e[4-9][0-9a-f]{4}

#使用正则匹配出中文字符：

SELECT * FROM table_name WHERE is_deleted = 'n' AND HEX(address) REGEXP 'e[4-9][0-9a-f]{4}';

#使用正则匹配出非中文字符：

SELECT * FROM table_name WHERE is_deleted = 'n' AND HEX(address) NOT REGEXP 'e[4-9][0-9a-f]{4}';

---------------------------------------------------------------------------------

in 和 exists

in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比not in 要快。

大数据量，表连接占优；中数据量，exists比in要好，也更容易用上索引；小数据量，in就OK了，逻辑更清晰点

（5）时间日期

create_time= '2017-11-02 17:30:19'

date(create_time)  #2017-11-02

adddate(date(create_time),1) #2017-11-03

adddate(create_time,1) #2017-11-03 17:30:19

SELECT                                       #030734

,filing_time                                            #2017-11-13 17:30:19

,date(filing_time)                                      #2017-11-13

,adddate(date(create_time),8)                           #2017-11-21

,curdate()                                              #2017-11-14

,datediff(adddate(date(create_time),8),curdate()) AS 天数  #7

,CASE                                                   #剩余7天

WHEN datediff(adddate(date(create_time),8),curdate()) >= 7 THEN '剩余7天' 

WHEN datediff(adddate(date(create_time),8),curdate()) <=0 THEN '超过7天' 

ELSE datediff(adddate(date(create_time),8),curdate()) END AS 剩余天数

FROM table_name WHERE is_deleted = 'n' AND id = 030734;