DML, Data Manipulation Language, 数据操作语言
用于对数据的操作
- Insert
- Delete
- Update
- Select
1. 算时间差
1
| select timestampdiff(second, now(), '2011-01-27 15:52:11');
|
2. 横向排列
1
2
3
4
5
6
7
8
9
10
11
12
| select
date_format(idi.`callin_time`, '%y-%m-%d') as `day`,
count(*) as `total`,
coalesce(sum((case idi.`ivr_selectedoption` when '1' then 1 else 0 end)), 0) as `line1`,
coalesce(sum((case idi.`ivr_selectedoption` when '2' then 1 else 0 end)), 0) as `line2`,
coalesce(sum((case idi.`ivr_selectedoption` when '3' then 1 else 0 end)), 0) as `line3`,
coalesce(sum((case idi.`ivr_selectedoption` when '4' then 1 else 0 end)), 0) as `line4`
from vcc.`tcc_ivrdetailinfo` idi
where 1=1
and idi.`callin_time` between '2015-1-1' and '2015-1-29'
and idi.`ivr_selectedoption` != ''
group by date_format(idi.`callin_time`, '%y-%m-%d');
|
3. join
假如 A 表 join B 表
1
2
3
| select * from a
inner join b
on a.name = b.name
|
1
2
3
| select * from a
full outer join b
on a.name = b.name
|
- left outer join
产生表 A 的完全集, 而 B 表中匹配的则有值, 没匹配的以 null 值取代.
1
2
3
| select * from a
left outer join b
on a.name = b.name
|
- left outer join on where
产生在 A 表中有而在 B 表中没有的集合.
1
2
3
4
| select * from a
left outer join b
on a.name = b.name
where b.name is null
|
- right outer join
产生表 B 的完全集, 而 A 表中匹配的则有值, 没匹配的以null值取代.
1
2
3
| select * from a
right outer join b
on a.name = b.name
|
- right outer join on where
产生在 B 表中有而在 A 表中没有的集合.
1
2
3
4
| select * from a
right outer join b
on a.name = b.name
where a.name is null
|
- full outer join on where
产生 ( A 表中有但 B 表没有) 和 ( B 表中有但 A 表中没有) 的数据集.
1
2
3
4
5
| select * from a
full outer join b
on a.name = b.name
where a.name is null
or b.name is null
|
- cross join
交差集, A * B, 性能较差, 不建议使用