mysql一些命令记载

(1):查看进程

SELECT * FROM information_schema.processlist WHERE info IS NOT NULL;

show processlist ;

(2):创建索引的方式

CREATE INDEX idx_column1_column2 ON table_name (column1,column1);


ALTER TABLE table_name ADD KEY idx_column1_column2(column1,column2);

(3):查看创建表的sql:

SHOW CREATE TABLE table_name;

(4):查看sql情况：

DESC
SELECT * FROM table_name   WHERE column1 = 'XXX';

(5):查看表的每一列信息

###详细
SHOW FULL FIELDS FROM `db_name`.`table_name `; 

###列
SHOW COLUMNS FROM `db_name`.`table_name `; 

###详细信息
SHOW FULL COLUMNS FROM `db_name`.`table_name `; 

(6):查看表的索引：

SHOW  INDEX FROM `db_name`.`table_name `; 

(7):按照时间段查询

SELECT *  FROM `db_name`.`table_name` WHERE column2 = 'XXX  AND column5  BETWEEN '2017-05-20 00:00:00' AND '2017-05-21 00:00:00';

SELECT * FROM `db_name`.`table_name`  WHERE DATE_FORMAT(column5,'%Y-%m-%d') ='2017-05-20';


SELECT * FROM `db_name`.`table_name`  WHERE STR_TO_DATE(column5,'%Y-%m-%d') ='2017-05-20';

(8)：mysql授权

grant select on db_name.* to user_name@'10.10.10.10' identified by "passwd";
flush privileges;
show grants for user_name@'10.10.10.10';

（9）：分组聚合

SELECT GROUP_CONCAT(id) FROM t_person  GROUP BY type

（10）：修改表字段类型

ALTER TABLE t_table  MODIFY   name VARCHAR(300);
ALTER TABLE t_table  MODIFY   name VARCHAR(400)

（11）修改字段名

ALTER TABLE t_table  CHANGE  name_old  name_new VARCHAR(300)