Mysql 查询语句大全（有示例）

喜欢ヅ旅行 2023-06-09 15:30 32阅读 0赞

zhanglei 10:28:57  
shanbi   
itw\_zhangqc 10:29:26  
\-- 查询每天的数据  
SELECT COUNT(1) as total,DATE\_FORMAT(create\_time,'%Y-%m-%d') as time FROM op\_endor\_info GROUP BY DATE\_FORMAT(create\_time,'%Y-%m-%d');

\-- 查询每周的数据  
SELECT COUNT(1) as total,WEEK(create\_time) as time FROM op\_endor\_info GROUP BY WEEK(create\_time);

\-- 查询每月的数据  
SELECT COUNT(1) as total,MONTH(create\_time) as time FROM op\_endor\_info GROUP BY MONTH(create\_time);

\-- 查询每年的数据  
SELECT COUNT(1) as total,YEAR(create\_time) as time FROM op\_endor\_info GROUP BY YEAR(create\_time);

\-- 其他：  
\-- DATE\_ADD(,INTERVAL expr unit)  
\-- DATE\_ADD(create\_time,INTERVAL 1 DAY)  得到指定日期后一天的日期/把1改为任意数字就可以得到后n天的日期  
\-- DATE\_SUB(,INTERVAL expr unit)  
\-- DATE\_SUB(create\_time,INTERVAL 1 DAY)  得到指定日期前一天的日期/把1改为任意数字就可以得到前n天的日期  
itw\_zhangqc 13:37:29  
SELECT  
    i.policy\_no  as 保单号,  
    i.create\_time as 创建时间,  
    n.operate\_time as 提交时间  
FROM  
    op\_endor\_info i,  
    op\_endor\_node\_detail n  
WHERE  
    i.policy\_no = n.policy\_no  
AND n.result = '通过' and i.endor\_no = n.endor\_no;

\-- 查询事件录入

UNION  
\-- 新建事件和领取  
SELECT  
    i.create\_time AS 创建时间,  
    n.operate\_time AS 操作时间,  
    avg(n.operate\_time - i.create\_time) 时间差  
FROM  
    op\_endor\_info i  
LEFT JOIN op\_endor\_node\_detail n ON i.policy\_no = n.policy\_no  
where n.operation\_detail='领取' and i.endor\_no = n.endor\_no;

UNION

SELECT  
    i.create\_time AS 创建时间,  
    n.operate\_time AS 操作时间,  
FROM  
    op\_endor\_info i  
LEFT JOIN op\_endor\_node\_detail n ON i.policy\_no = n.policy\_no  
where n.result='通过' and i.endor\_no = n.endor\_no;

SELECT \* FROM op\_endor\_node\_detail;

SELECT operate\_time  FROM op\_endor\_node\_detail where operation\_detail = '提交' AND endor\_no='2019061000015';2019-06-10 14:12:24  
SELECT operate\_time  FROM op\_endor\_node\_detail where operation\_detail = '领取' AND endor\_no='2019061000015';2019-06-10 14:15:03

SELECT \* FROM op\_endor\_node\_detail where result = '通过';

select \* from op\_endor\_info

SELECT  
    t.policy\_no AS 保单号,  
    t.operation\_detail AS 操作步骤,  
    o.create\_time AS 创建时间,  
    t.operate\_time AS 结束时间,  
    t.result as 事件状态,  
    SECOND(t.operate\_time) - SECOND(o.create\_time) AS 时间差  
FROM  
    op\_endor\_node\_detail t,  
    op\_endor\_info o  
WHERE  
    t.policy\_no = o.policy\_no  
AND t.endor\_no = o.endor\_no  
\-- AND t.result = '通过'  
AND t.policy\_no ='600000510132018060120193888054';  
UNION  
SELECT  
    t.policy\_no AS 保单号,  
    t.operation\_detail AS 操作步骤,  
    o.create\_time AS 创建时间,  
    t.operate\_time AS 结束时间,  
    t.result as 事件状态,  
    SECOND(t.operate\_time) - SECOND(o.create\_time) AS 时间差  
FROM  
    op\_endor\_node\_detail t,  
    op\_endor\_info o  
WHERE  
    t.policy\_no = o.policy\_no  
AND t.endor\_no = o.endor\_no  
AND t.result = '通过'  
AND t.policy\_no is not null;

\--查询某一天的数据:  
SELECT \* FROM op\_endor\_info where platform\_source='min-b' and system\_source is null and DATE\_FORMAT(create\_time,'%Y-%m-%d') = '2019-06-18';

\-- 查询当天的数据  
SELECT \* FROM op\_endor\_info where DATEDIFF(create\_time,NOW())= 0;  
\-- 查询一天的 同上  
SELECT \* FROM op\_endor\_info where TO\_DAYS(create\_time) = TO\_DAYS(NOW());  
SELECT \* FROM op\_endor\_info where DATE(create\_time) = CURDATE();

\-- 查询昨天的数据  
SELECT \* FROM op\_endor\_info where TO\_DAYS(NOW())- TO\_DAYS(create\_time) =1;

\--7天  
SELECT \* FROM op\_endor\_info where DATE\_SUB(CURDATE(),INTERVAL 7 DAY) < DATE(create\_time);

\-- 本月  
SELECT \* FROM op\_endor\_info where DATE\_FORMAT(create\_time,'%Y%m') = DATE\_FORMAT(CURDATE(),'%Y%m');

\--获取今天是一周的第几天或者周几  返回值 0是周一 6是周日  
SELECT WEEKDAY(NOW());

时间戳转日期:  
SELECT FROM\_UNIXTIME(1560912691);  
SELECT FROM\_UNIXTIME(1560912691,'%Y-%m-%d')

日期转时间戳: --1560912691  
SELECT UNIX\_TIMESTAMP(NOW());

\-- 查询每天数据汇总  
SELECT  
    DATE\_FORMAT(create\_time, '%Y-%m-%d') AS PerDay,  
    count(0) AS Total  
FROM  
    op\_endor\_info  
WHERE  
    create\_time BETWEEN '2019-06-01 00:00:00'  
AND '2019-06-30 23:59:59'  
GROUP BY  
    PerDay  
ORDER BY  
    create\_time;  
      
或者是:  
\-- 查询每天的数据  
SELECT  
    COUNT(0) AS total  
FROM  
    op\_endor\_info  
GROUP BY DATE\_FORMAT(create\_time,'%Y-%m-%d')  
;  
      
\-- 如何查询某个时间点   
DATE\_FORMAT(create\_time, '%Y-%m-%d %H:%i:%s')

UPDATE  
op\_endor\_info  
SET   
platform\_source =  
CASE  
    WHEN platform\_source = 'APP-泰康在线' THEN    'APP'  
    WHEN platform\_source = '微信-泰康在线保险服务' THEN    'Healthy'  
    WHEN platform\_source = '支付宝-生活号' THEN    'ALIPAY'  
    WHEN platform\_source = '微信-泰康在线保险' THEN    'WeChatSelfHelp'  
END  
where creater\_name = '三端一微';

\-- mysql 根据长度排序

select \* from dict\_info o where o.dict\_type='correctType' and o.parent\_label='0'

order by length(o.dict\_value);

\-- mysql根据权重排序

select \* from dict\_info o where o.dict\_type='bankName' and o.parent\_label='0'

order by fileld (o.dict\_label,'301','302') desc;

\-- mysql根据中文排序

select \* from dict\_info o where o.dict\_type='correctType' and o.parent\_label='0'

order by convert (o.dict\_value using gbk);