逍遥山人的MySQL使用笔记，持续更新中
新建表以及添加表和字段的注释
create table t_user(
ID INT(11) primary key auto_increment comment '主键',
NAME VARCHAR(255) comment '姓名',
CREATE_TIME date comment '创建时间'
)comment = '用户信息表';修改字段类型
alter table tablename modify column column_name varchar(30);查看表结构
查看表的字段、字段类型、索引等
DESC tablename;或
SHOW COLUMNS FROM tablename;或包含字符集、字段注释等
SHOW FULL FIELDS from table_name;查看表生成的DDL
注意表名不加单引号
show create table 表名;查看分区表的分区情况
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA()
AND table_name = 'table_name';分区操作参考：http://blog.csdn.net/youzhouliu/article/details/52757043
修改表/字段的注释
alter table t_user comment = '修改后的表注释信息(用户信息表)';修改字段注释
alter table t_user modify column id int comment '主键ID';查看所有表的注释
SELECT table_name 表名, table_comment 表说明
FROM information_schema.TABLES
WHERE table_schema = '数据库名'
ORDER BY table_name;查询所有表及字段的注释
SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM information_schema.TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE a.table_schema = '数据库名'
ORDER BY a.table_name;查询某表的所有字段的注释
select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束
from information_schema.columns
where table_schema = '数据库名' and table_name = '表名';或者
show full columns from 表名;ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);CREATE INDEX可对表增加普通索引或UNIQUE索引
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义。
另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY前两条相同，第三条在主键时用；
查看表的索引
查看表所有索引的具体情况
SHOW KEYS FROM tablename;
SHOW INDEX FROM tablename;注释：
Table 表的名称。
Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name 索引的名称。
Seq_in_index 索引中的列序列号，从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
查看某一个库下某一张表的索引
SELECT DISTINCT
lower(index_name) index_name,
lower(index_type) type
FROM
information_schema.statistics
WHERE
table_schema = 'employees' AND table_name = 'employees';查看某一个库下某一张表的某一个索引
SELECT
lower(column_name) column_name,
seq_in_index column_position
FROM
information_schema.statistics
WHERE
table_schema = 'employees' AND table_name = 'employees' AND index_name = 'primary';交叉连接（CROSS JOIN）
使用交叉连接的时候，结果集由第一个表的所有行与第二个表的所有行的结合，表之间不需要关系；
可用于在统计中构造连续的数据，比如统计每天/周/月的(交易等)数据，为使每天/周/月都有数据，可用交叉连接的方式构造数据；
使用形式如下：
SELECT *
FROM T1
CROSS JOIN T2;格式化(date_format)
定义
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
语法
DATE_FORMAT(date,format)date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有：
| 模式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
示例：
SELECT date_format(CURDATE(), '%Y-%m-%d %h:%m:%s');
SELECT date_format(CURDATE(), '%Y/%m/%d');
SELECT STR_TO_DATE('2016-08-08', '%Y-%m-%d %h:%m:%s');
SELECT DATE_FORMAT(NOW(),'%Y%m%d');毫秒数与日期转化
-- 毫秒数转化为日期
select FROM_UNIXTIME(1364176514656/1000,'%Y-%m-%d %h:%i:%s') as dt;
-- 日期转化为毫秒数
select UNIX_TIMESTAMP('2011-05-31 23:59:59');操作符
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]‘ 可以匹配 "plain" 中的 ‘a‘ |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]‘ 可以匹配 "plain" 中的‘p‘ |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，‘z|food‘ 能匹配 "z" 或 "food"。‘(z|f)ood‘ 则匹配 "zood" 或 "food" |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,} |
| + | 匹配前面的子表达式一次或多次。例如，‘zo+‘ 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,} |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}‘ 不能匹配 "Bob" 中的 ‘o‘，但是能匹配 "food" 中的两个 o |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次 |
匹配连续数字
SELECT * FROM ofchatlog a WHERE a.`sender` REGEXP 'jf[0-9]{5}' LIMIT 10;匹配三个字符串(或的关系)
SELECT *
FROM ofchatlog a
WHERE a.sender REGEXP 'xx1|xx2|xx3';不匹配(包含)
用not regexp ‘xxx‘
类似Oracle和SqlServer的 函数名(列) OVER(选项)
分组排序功能
类似格式row_number() over(partition by )
示例1：
create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) );
select empid,deptid,salary,rank
from (
select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1,
if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=heyf_tmp.deptid
from (
select empid,deptid,salary
from heyf_t10
order by deptid asc ,salary desc
) heyf_tmp ,(select @rownum := 0 , @pdept := null ,@rank := 0) a
) result;示例2：
SELECT
-- rownum 判断@sp_id 是否与当前的sp_id一致，一致加1，否则重置为1
IF(@sp_id = a.sp_id,@i := @i + 1,@i := 1) rownum
,a.*
-- 设置@sp_id 等于上一个 sp_id
,(@sp_id := a.sp_id)
FROM jf_sp_contact a,(SELECT @i := 0,@sp_id := 0) AS b
WHERE a.sp_id = 3334注意：示例1生效，而示例2无效，原因是该逻辑是针对按规则进行排序的数据进行标识；
查询作业
SELECT a.name,b.command
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobsteps b ON a.job_id = b.job_id
WHERE b.command LIKE '%P_GZ_HeavyPaste_UpOrder%';查询过程
SELECT a.name,b.text
FROM sys.objects a
INNER JOIN sys.syscomments b ON a.object_id = b.id
WHERE a.type = 'P' AND b.text LIKE '%T_SEM_PostSnap%';查询触发器
SELECT * FROM sys.objects WHERE TYPE = 'tr';查询视图
SELECT * FROM sysobjects WHERE xtype='V';查看所有的库
SHOW DATABASES;或者使用系统表，除库名外还能得到数据库配置信息
SELECT
lower(schema_name) schema_name
FROM
information_schema.schemata
WHERE
schema_name NOT IN (
'mysql',
'information_schema',
'test',
'search',
'tbsearch',
'sbtest',
'dev_ddl'
);创建或删除库
创建库
-- 按照默认的字符集和排序规则创建数据库
CREATE DATABASE test1;
-- 指定字符集和排序规则
CREATE DATABASE test1 CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
-- 创建数据库时如果已存在会报错，避免报错可以先判断是否存在
CCREATE DATABASE IF NOT EXISTS test1;删除库
DROP DATABASE test;查看某一个库中的所有表
SELECT
table_name,
create_time updated_at,
table_type,
ENGINE,
table_rows num_rows,
table_comment,
ceil(data_length / 1024 / 1024) store_capacity
FROM
information_schema.TABLES
WHERE
table_schema = 'employees' AND table_name NOT LIKE 'tmp#_%' ESCAPE '#';查看某一个库下某一个表的所有字段
SELECT
lower(column_name) column_name,
ordinal_position position,
column_default dafault_value,
substring(is_nullable, 1, 1) nullable,
column_type data_type,
column_comment,
character_maximum_length data_length,
numeric_precision data_precision,
numeric_scale data_scale
FROM
information_schema.COLUMNS
WHERE
table_schema = 'employees' AND table_name = 'employees';判断库中是否存在包含status的表
SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE '%status%';判断库中是否存在包含order的存储过程
SELECT * FROM information_schema.ROUTINES a WHERE a.SPECIFIC_NAME LIKE '%order%';查看服务器版本；
show variables;EXPLAIN
SELECT * FROM tablename LIMIT 10;select column from table_name procedure analyse();强制使用某索引
FORCE INDEX(索引名)查询是否有表被锁
show OPEN TABLES where In_use > 0;原文：https://www.cnblogs.com/xyshanren/p/12244798.html