SQL 精编（3）

2021-10-19 19:42:15 浏览数 (1)

show

代码语言：javascript复制
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.%';

index

代码语言：javascript复制
create index profiles_on_user_id on  profiles(user_id);
create index conversations_on_user_id on conversations (user_id);

grant

代码语言：javascript复制
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

代码语言：javascript复制
select user();
select databases();
select version();
select * from information_schema.processlist where Command="sleep" and Time>86400;

desc

代码语言：javascript复制
desc mysql.user
desc mysql.db

一些函数

时间相关函数

代码语言：javascript复制
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>
select 函数

0 人点赞