mysql 时间查询相关

##### 今天
select * from 表名 where to_days(时间字段名) = to_days(now());

##### 昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

##### 7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

##### 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

##### 本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

##### 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

##### 查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

#####查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

##### 查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

##### 查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

##### 查询当前这周的数据
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 下月第一天

where   date(regdate)   =   curdate();

select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())

SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30

查询某个数据库有多少张表
SELECT COUNT(*) TABLES, table_schema
FROM information_schema.TABLES
WHERE table_schema = 'database_name';

　　

1、创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username:用户名；host：指定在哪个主机上可以登录，本机可用localhost，%通配所有远程主机；password：用户登录密码；
2、授权：GRANT ALL PRIVILEGES ON  *.* TO 'duyuan'@'%' IDENTIFIED BY 'duyuan';
3、刷新权限：FLUSH PRIVILEGES;
4、格式：grant 权限 on 数据库名.表名 to 用户@登录主机 identified by "用户密码"；*.*代表所有权；
5、@ 后面是访问MySQL的客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）。

SQL:
-- update aa_zhuanti_product set product_class_id = (select product_class_id from aa_product where aa_product.pro_id = aa_zhuanti_product.pro_id);
-- select product_class_id from aa_product where pro_id =489998;
--select count(*) from aa_zhuanti_product ;
//随机查询一条数据
$res = Db::query("SELECT *
                                    FROM
                                        `meizi`
                                   WHERE
                                                id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `meizi`)))
                                           AND  is_caiji =1
                                           AND  is_show = 1
                                   ORDER BY id
                                    LIMIT 1;
                          ");