查询基础
SELECT (显示内容)
FROM (哪里查)
WHERE (约束条件)
常用的查询
SELECT [DISTINCT]
t1.column_1 [AS `description`]
,t2.column_2 [AS `description`]
,t1.column_1 *t2.column_12
,nvl(expr1,expr2)
,(CASE
WHEN condition1= '2G69'
THEN 'description1'
WHEN condition2= '2G88' OR condition4= '2G89'
THEN 'description2'
WHEN condition3 IS NULL
THEN 'description3'
ELSE
'description4'
END
) t4
,FUNCTION(...)
.......
FROM table_name1 t1
, table_name2 t2,
,(SELECT column_1 ...
FROM table_name
WHERE exists (...)
..... ) t3
WHERE expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]
AND standard_cost BETWEEN 500 AND 600
AND standard_cost <> condition
OR expression [NOT] IN (v1,v2,...)
OR standard_cost = [ALL|ANY] (SELECT column_1 ...
FROM table_name
..... )
INNER JOIN table_name3
GROUP BY col_name,...
HAVING where_condition
ORDER BY
column [ASC | DESC] [NULLS FIRST | NULLS LAST]
LIMIT offset,row_count
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]；
递归使用
START WITH：用于指定层次查询的起始根行，当然也可以指定末行开始查询
CONNECT BY：用于指定父子行关系，在其后必须使用PRIOR引用父行
--递归层次显示11MP28440ZZZZ001110-004101的BOM结构
SELECT DISTINCT concat(lpad(' ',3*(level-1)),bmb01) father_ima,
concat(lpad(' ',3*(level+1-1)),bmb03) level_ima
FROM bmb_file,bma_file
WHERE bma01=bmb01 AND bma10='2' AND bmb14='0'
START WITH bmb01 = '11MP28440ZZZZ001110-004101'
AND bmb01 IS NOT NULL AND bmb14='0'
AND (bmb05 IS NULL OR bmb05>sysdate)
AND bmb04<=sysdate
CONNECT BY PRIOR bmb03 = bmb01 --表示当前的bmb03是下阶的bmb01
ORDER BY father_ima DESC
INSERT 语句
INSERT INTO table_name VALUES (value1，value2，......);
INSERT INTO table_name(colum1，colum2，......) VALUES (value1，value2，......);
INSERT INTO table_name SELECT * FROM table_name2;
UPDATE语句
UPDATE table_name
SET colum1=value1
,colum2=value2
.....
WHERE condition
