记录
1、添加记录
<wiz_code_mirror>
xxxxxxxxxx
15
1
insert into
2
table_name1 t
3
(
4
t.a,
5
t.b
6
)
7
values
8
(
9
a,
10
b
11
),
12
(
13
c,
14
d,
15
)
2、删除记录
<wiz_code_mirror>
xxxxxxxxxx
6
1
delete
2
t.b
3
from
4
table_name t
5
where
6
t.a = ?
3、修改记录
<wiz_code_mirror>
xxxxxxxxxx
6
1
update
2
tabel_name t
3
set
4
t.a = ?
5
where
6
t.b = ?
4、查询记录
<wiz_code_mirror>
xxxxxxxxxx
6
1
select
2
*
3
from
4
table_name t
5
where
6
t.a = ?
（1）查询的条件
比较运算符：
= != <> > < >= <= !< !>
逻辑运算符：
ALL AND ANY BETWEEN(a,b) LIMIT(a,b) EXISTS IN LIKE NOT OR IS NULL UNIQUE
（2）结果分组
GROUP BY
分组函数 COUNT AVG SUM MAX MIN
GROUP BY子句： ROLLUP CUBE
（3）分组后的结果集添加条件
HAVING 条件
（4）结果排序
ORADER BY 字段 (DESC|ASC);
5、关联查询
<wiz_code_mirror>
xxxxxxxxxx
12
1
select
2
count(*)
3
from
4
table1_name t1
5
(left/right) join
6
table2_name t2
7
on
8
t1.a = t2.a
9
where
10
t1.b = ?
11
group by
12
t1.c
6、融合数据
<wiz_code_mirror>
xxxxxxxxxx
17
1
merge into
2
table_name t1
3
using
4
table_name t2
5
on
6
(ti.id = t2.id)
7
when matched then
8
update
9
t1
10
set
11
t1.column1 = t2.column1,
12
t1.column2 = t2.column2
13
when not matched then
14
insert
15
(t1.column1, t1.column2 )
16
values
17
(t2.column1， t2.column2)
7、递归查询
<wiz_code_mirror>
xxxxxxxxxx
9
1
select
2
t.subid,
3
t.parentid
4
from
5
table_name t
6
start with
7
t.subid = '1'
8
connect by prior
9
t.subid = t.parentid
实例
<wiz_code_mirror>
1
查询时间节点中间的数据
2
select
3
*
4
from
5
tabel_name t
6
where
7
t.a
8
between
9
to_date('2017-07-25 00:00:00', 'yyyy-MM-dd HH24:MI:SS')
10
and
11
to_date('2017-07-25 00:00:00', 'yyyy-MM-dd HH24:MI:SS')