SQL 精编（3）

show charset;
show character set;
show char set;
show character set like '%utf8%';
show collation like "%utf8%";
SHOW TABLE STATUS FROM `xxx_qa` LIKE 'abc'\G
show table status like 'conversations'\G
SHOW CREATE TABLE `xxxx_qa`.`abc`\G
SHOW INDEX FROM `xxxx_qa`.`abc`\G
show variables like "%format%";
show databases;
show tables;
show CREATE DATABASE `xxx_qa`
show grants for 'care'@'192.168.1.%';

create index profiles_on_user_id on  profiles(user_id);
create index conversations_on_user_id on conversations (user_id);

grant create, CREATE TEMPORARY tables , CREATE VIEW , index , REFERENCES , drop , select , insert , update , delete , lock tables ,show view on xxx.* to 'xxx'@'192.168.1.%' identified by 'xxx';
grant alter on xxx.* to 'xxx'@'192.168.1.%';
GRANT RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost' IDENTIFIED BY 'xxx';
flush privileges;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON `xxxxxx`.* TO 'xxxxx'@'192.168.1.%'  identified by 'xxxxxx';

select user();
select databases();
select version();
select * from information_schema.processlist where Command="sleep" and Time>86400;

desc mysql.user
desc mysql.db

mysql> select date_sub(now(),interval 30 day);
+---------------------------------+
| date_sub(now(),interval 30 day) |
+---------------------------------+
| 2016-04-17 20:31:42             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(date_sub(now(),interval 30 day));
+-------------------------------------------------+
| unix_timestamp(date_sub(now(),interval 30 day)) |
+-------------------------------------------------+
|                                      1460896325 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select to_days(date_sub(now(),interval 30 day));
+------------------------------------------+
| to_days(date_sub(now(),interval 30 day)) |
+------------------------------------------+
|                                   736436 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select to_days(now());
+----------------+
| to_days(now()) |
+----------------+
|         736466 |
+----------------+
1 row in set (0.00 sec)

mysql>