MySQL 的一些常用 SQL 集合

简单描述表结构，字段类型

显示表结构，字段类型，主键，是否为空等属性，但不显示外键。

desc tabl_name;

查询数据库的外键关系

你可以通过 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表来查看。

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 't_stu'

查询表中列的注释信息

select * from information_schema.columns
where table_schema = 'db' -- 表所在数据库
and table_name = 'tablename' ; -- 你要查的表

只查询列名和注释

select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename' ;

查看表的注释

select table_name,table_comment from information_schema.tables where table_schema = 'db' and table_name = 'tablename'

查看表生成的 DDL

show create table table_name;

复制数据

insert into table1 select * from table

机器授权

grant select on *.* to 'reader'@'%' identified by '123456' WITH GRANT OPTION
flush privileges

查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询当前月份的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在 6 个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())

select *
from user
where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *
from [user]
where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now())
and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())

select *
from [user]
where pudate between 上月最后一天
and 下月第一天

获取前一天时间的方法

SELECT DATE_SUB(current_date，INTERVAL 1 DAY);

SELECT DATE_SUB(curdate(), INTERVAL 1 DAY);

mysql> select date_sub(curdate(),interval 1 day);
+------------------------------------+
| date_sub(curdate(),interval 1 day) |
+------------------------------------+
| 2006-06-19                         |
+------------------------------------+
1 row in set (0.09 sec)

mysql> select date_sub('2006-06-04',interval 1 day);
+---------------------------------------+
| date_sub('2006-06-04',interval 1 day) |
+---------------------------------------+
| 2006-06-03                            |
+---------------------------------------+
1 row in set (0.00 sec)

替换部分字段内容

UPDATE imicro_messages set ms_content = REPLACE(ms_content, ' ', '</span>')