mysql查询,update

1.查询两表唯一记录：
select * from (
select a.订单号,count(*) as 合并数 from (
select order_code as '订单号' from e_order a where a.create_time >date('2016-12-01') and a.create_time union all
select 订单号 from order_status) a group by a.订单号 ) b where 合并数=1
CASE WHEN orderinfo.ORDER_TYPE = 1 THEN '有线路下单'
WHEN orderinfo.ORDER_TYPE = 2 THEN '无线路下单'
when orderinfo.ORDER_TYPE = 3 THEN '即时运输计划'
END AS UserType,

2. 日期查询

a.本月第一日
date_add(curdate(), interval - day(curdate()) + 1 day)

b. 今天

select date_add(curdate(), interval+0 day)

c. 昨天

date_add(curdate(), interval-1 day)
d. 明天

date_add(curdate(), interval+1 day)

e. 上月

select date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y%m')
f. 本月

select date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH), '%Y%m')
select date_format(date_add(curdate(), interval+0 month), '%Y%m')

3. mysql select语句where条件嵌套select

select * from cm_user_info where tenant_id  = (select tenant_id from sys_tenant_def where name like '%广泽%' )

4. 批量更新字段值

UPDATE sys_vehicle_info SET ACTUAL_WEIGHT=ACTUAL_WEIGHT*1000
where ACTUAL_WEIGHT <500 and sts = 1 and AUTH_STATE =1 and ACTUAL_WEIGHT =32

5. 去重复并只显示ID值大的数据
-- select * from sys_tenant_vehicle where id NOT IN (select MIN(id) from sys_tenant_vehicle GROUP BY VEHICLE_ID HAVING COUNT(VEHICLE_ID)>1)

6. 添加序号

select (@i:=@i+1) i,b.* from (select @i:=0) a, abc b

7. 数字转换百分数并取两位小数

select concat(round(0.5*100,2),'%')

8. 一列根据条件变多列

IF(oi.STATE is null, '未上发', null) as 未上发, if(oi.STATE  = 1, '成功', null)as 成功, if(oi.STATE  = 2, '失败', null)as 失败

9.行转列，行变列，两条记录合并查询成一条（去重DISTINCT  只能有一个字段）

SELECT DISTINCT a.order_id,
(SELECT city_id FROM ord_order_route b WHERE a.order_id=b.order_id AND b.WORK_NODE_TYPE='1' ) AS 'Str',
(SELECT city_id FROM ord_order_route b WHERE a.order_id=b.order_id AND b.WORK_NODE_TYPE='3' ) AS 'End'
FROM ord_order_route a
where a.order_id ='460873' or a.order_id ='462535'

10.根据表备注查表名 
SELECT TABLE_NAME AS tableName,TABLE_COMMENT AS tableComment 
FROM information_schema.`TABLES` 
WHERE TABLE_COMMENT like '%条形码%';