basic operations in sql

CREATE TABLE TABNAME{
COL1 NUMBER(8) CONSTRAINT CON1 PRIMARY KEY [ON DELETE RESTRICT|SET NULL|SET DEFAULT|SET CASCADE],
COL2 VARCHAR(20) CONSTRAINT CON2 NOT NULL,
COL3 VARCHAR(20) CONSTRAINT CON3 UNIQUE,
COL4 NUMVER(2) CONSTARIN CON4 CHECK(COL>4),
COL5 NUMVER(3) DEFAULT 'SUN',
COL6 VARCHAR(5) CONSTRAINT COL6 REFERENCES MYTABLE(PK)
}

ALTER TABLE TANNAME
ADD CONSTRAINT CON1 FOREIGN KEY(KEYNAME) REFERENCES TABNAME(COLNAME);

ALTER TABLE TABNAME
ADD CONSTRAINT CON2 PRIMARY KEY(PK);

ALTER TABLE TANNAME
MODIFY COLNAME VARCHAR(2);

ALTER TABLE TABNAME
DROP(COLNAME);

ALTER TABLE TABNAME
DROP PRIMARY KEY;
DROP CONSTRAINT CONNAME;

DROP TABLE TABNAME;
TRUNCATE TABLE TABNMAE;

CREATE UNIQUE INDEX IDXNAME ON TABLE(COLNAME);
DROP INDEX IDXNAME;

INSERT INTO TABNAME(COLNAME...) VALUES(...);
INSERT INTO TABNAME VALUES(...);
INSERT INTO TABNAME(COLNAME...) VALUES(NULL,...);
INSERT INTO TABNAME(COLNAME...) WHERE SELECT COLNAME... FROM TABNAME WHERE...

DELETE FROM TABNAME WHERE...
DELETE FROM TABNAME;

UPDATE TABNAME SET COLNAME=XXX,COLNAME2=XXX WHERE...;

SELECT * FROM TABNAME;
SELECT (ALL) COLNAME FROM TABNAME;
SELECT DISTICT COLNAME FROM TABNAME;
SELECT COL1NAME AS "CUSTOMER1",COL2NAME AS "CUSTOMER2" FROM TABNAME;

SELECT COLNAME FROM TABLENAME WHERE COLNAME = XXX AND COLNAME2 = XXX;
SELECT COLNAME FROM TABLENAME WHERE NOT COLNAME = XXX AND NOT COLNAME = XXX;

SELECT COLNAME FROM TABLENAME WHERE COLNAME (NOT) BETWEEN X AND Y;(包含边界)

SELECT COLNAME FROM TABLENAME WHERE COLNAME (NOT) IN(VALUES);

SELECT COLNAME FROM TABLENAME WHERE COLNAME LIKE PARTTEN.(通配符：％任意多个字符，_一个字符)

SELECT COLNAME FROM TABLENAME WHERE COLNAME IS (NOT) NULL;

SELECT COLNAME FROM TABLENAME ORDER BY (COLNAME|COL_NUMVER) DESC/ASC;

SELECT MIN(COLNAME),MAX(COLNAME),AVG([DISTINCT]COLNAME),SUM([DISTINCT]COLNAME),COUNT(*|[DISTICT] COLNAME) FROM TABLENAME WHERE...
COUNT(*) =  NO.OF ROWS
COUNT(COLNAME) = NO. OF ROWS DO NOT HAVE NULL VALUE

GROUP BY必须在聚合函数使用的情况下使用，并且在select list中出现在的colname不在聚合函数中，就一定要出现在GROUP BY中。
HAVING COLNAME中的colname必须是出现在select list中的。也可以是selct list中的聚合函数
SELECT COLNAME,SUM(COLANME2) FROM TABNAME WHERE... GROUP BY COLNMAE HAVING SUM(COLNAME2) > XXX;

集合运算
并集UNION [ALL]:没有all时，结果消除重复项，有all则保留重复项。任何一个选择自己不能使用ORDER BY，但是UNION结果可以排序    
SELECT COLNAME...COLNAME FROM TAB1
  UNION [ALL]
SELECT COLNAME...COLNAME FROM TAB2

交集INTERSECT
SELECT COLNAME FROM TABNAME1
   INTERSECT
SELECT COLNAME FROM TABNAME2;

补集MINUS
SELECT COLNAME FROM TABNAME1
   MINUS
SELECT COLNAME FROM TABNAME2;

RESTRICTION就是水平投影，选出部分行，所有列，用WHERE实现
PROJECTION就是垂直投影，选出所有行，部分列。

连接运算JOIN
笛卡尔连接，内连接，等值连接，外连接其实质都是笛卡尔连接。
笛卡尔连接：SELECT a.colname1,b.colname2 from TAB1 a,TAB2 b;
            SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2;

内连接（等值连接）：SELECT a.colname1,b.colname2 from TAB1 a,TAB2 b WHERE a.colnamex = b.colnamey;
SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX = TAB2.COLNAMEY;

外连接
左外连接：SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX = TAB2.COLNAMEY（＋）;
右外连接：SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX（＋） = TAB2.COLNAMEY;

自连接（一定要定义表的别名和列的别名）：
SELECT COL1 AS "CL1",COL2 AS "CL2"... FROM TAB a,TAB b WHERE a.colname = b.colname;

独立子查询和相关查询
对于独立自查询而言，内查询先独立于外查询而执行，暂时存储查询结果，然后再独立进行外查询
相关查询：即内查询中要用到外查询的表。对外查询表的每一行，内查询都要全部执行一次。

EXIST
SELECT COLNAME...FROM TAB1 WHERE [NOT] EXIST (SELECT COLNAME... FROM TAB2 WHERE TAB1.COL = TAB2.COL) (WITH CHECK OPTION);

VIEW
CREATE VIEW VEIW_NAME(COL1,COL2...) AS SELECT ... FROM ...;
INSERT INTO VIEW_NAME(COL1...) VALUES(...);
DELETE VIEW_NAME WHERE...;
UPDATE VIEW_NAME SET...;
DROP VIEW VIEW_NAME;

GRANT
GRANT ALL [PRIVILEGES]|UPDATE(COLNAMES..)|SELECT|... ON TABNAME|VIWE_NAME TO USERNAME|PUBLIC [WITH GRANT OPTION];

REMOVE ALL [PRIVILEGES]|UPDATE(COLNAMES..)|SELECT|... ON TABNAME|VIWE_NAME FROM USERNAME|PUBLIC;