Oracle之SQL语法基础

[SQL三类语言]
DDL(Data Define Language): CREATE ALTER DROP
CREATE TABLE  table_name (field list...);
ALTER TABLE  table_name ADD/DROP/MODIFY (field list...);
DROP TABLE table_name;

 

DCL(Data Control Language): GRANT REVOKE
授权 GRANT ddl_opion ON table_name TO user_name;
解除授权 REVOKE ddl_opion ON table_name TO user_name;

 

DML(Data Manage Language): SELECT INSERT DELETE UPDATE

 

[SQL 子句(Clause)]
WHERE, FROM, ALL, DISTINCT, AS, ORDER BY, GROUP BY, HAVING, IN, EXISTS,

LIKE, NOT, ANY

 

[Oracle常用系统函数]
字符：LTRIM  TRIM REPLACE SUBSTR LENGTH LENGTHB
数字：ABS MOD POWER BIN_TO_NUM ROUND TRUNC
日期：SYSDATA CURRENT_DATE NEXT_DAY
转换：TO_CHAR TO_DATE TO_NUMBER
聚集：COUNT SUM AVG MAX MIN (聚集函数可出现在HAVING子句，但不能出现在WHERE子句)
其它：DECODE

 

[以表建表]

INSERT INTO table1(id, name) SELECT id, name FROM table2;
CREATE TABLE table3 AS (SELECT id, name FROM table2);

 

[PL/SQL]
数据类型：
BEALEAN, NUMBER(m,n), CHAR(n), VARCHAR2(n), DATE, LONG, RAW(n), LONG RAW,

BLOB, CLOB, BFILE

块结构:
DECLARE
...
BEGIN
...
EXCEPTION
...
END;

例子:
SET SERVEROUPUT ON
DECLARE
 X VARCHAR2(10);
BEGIN
 X:='HELLO';
 DBMS_OUTPUT.PUT_LINE(X || ', WORLD!');
END;

IF分支：
IF ... THEN
...
ELSIF ... THEN
...
ELSE
...
END IF;

循环语句：
LOOP
...
END LOOP;

WHILE EXPRESSION LOOP
...
END LOOP;

FOR counter IN/REVERSE begin..end LOOP
...
END LOOP;

[表的连接和子查询]
http://blog.chinaunix.net/u1/49865/showart_471961.html

 

 

[例子参考]