########### Basic SQL SELECT ################
select col_name as col_alias from table_name ;
select col_name from table_name where col1 like '_o%'; ----'_'匹配單個(gè)字符
/*使用字符函數(右邊截取,字段中包含某個(gè)字符,左邊填充某字符到固定位數,右邊填充某字符到固定位數)*/
select substr(col1,-3,5),instr(col2,'g'),LPAD(col3,10,'$'),RPAD(col4,10,'%') from table_name;
/*使用數字函數(往右/左幾位四舍五入,取整,取余)*/
select round(col1,-2),trunc(col2),mod(col3) from table_name ;
/*使用日期函數(計算兩個(gè)日期間相差幾個(gè)星期,兩個(gè)日期間相隔幾個(gè)月,在某個(gè)月份上加幾個(gè)月,某個(gè)日期的下一個(gè)日期,
某日期所在月的最后的日期,對某個(gè)日期的月分四舍五入，對某個(gè)日期的月份進(jìn)行取整)*/
select (sysdate-col1)/7 week,months_between(sysdate,col1),add_months(col1,2),next_day(sysdate,'FRIDAY'),last_day(sysdate),
round(sysdate,'MONTH'),trunc(sysdate,'MONTH') from table_name;
/*使用NULL函數(當expr1為空取expr2/當expr1為空取expr2,否則取expr3/當expr1=expr2返回空)*/
select nvl(expr1,expr2),nvl2(expr1,expr2,expr3),nullif(expr1,expr2) from table_name;
select column1,column2,column3, case column2 when '50' then column2*1.1
when '30' then column2*2.1
when '10' then column3/20
else column3
end as ttt
from table_name ; ------使用case函數
select table1.col1,table2.col2 from table1
[CROSS JOIN table2] | -----笛卡兒連接
[NATURAL JOIN table2] | -----用兩個(gè)表中的同名列連接
[JOIN table2 USING (column_name)] | -----用兩個(gè)表中的同名列中的某一列或幾列連接
[JOIN table2
ON (table1.col1=table2.col2)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 ------相當于(+)=,=(+)連接,全外連接
ON (table1.col1=table2.col2)]; ------SQL 1999中的JOIN語(yǔ)法;
##################### CREATE/ALTER TABLE #######################
alter table table_name drop column column_name ;---drop column
alter table table_name set unused (col1,col2,...);----設置列無(wú)效，這個(gè)比較快。
alter table table_name drop unused columns;---刪除被設為無(wú)效的列
rename table_name1 to table_name2; ---重命名表
comment on table table_name is 'comment message';----給表放入注釋信息
create table table_name
(col1 int not null,col2 varchar2(20),col3 varchar2(20),
constraint uk_test2_1 unique(col2,col3))); -----定義表中的約束條件
alter table table_name add constraint pk_test2 primary key(col1,col2,...); ----創(chuàng )建主鍵
/*建立外鍵*/
create table table_name (rid int,name varchar2(20),constraint fk_test3 foreign key(rid) references other_table_name(id));
alter table table_name add constraint ck_test3 check(name like 'K%');
alter table table_name drop constraint constraint_name;
alter table table_name drop primary key cascade;----級聯(lián)刪除主鍵
alter table table_name disable/enable constraint constraint_name;----使約束暫時(shí)無(wú)效
/*刪除列，并級聯(lián)刪除此列下的約束條件*/
alter table table_name drop column column_name cascade constraint;
select * from user_constraints/user_cons_columns;---約束條件相關(guān)視圖
############## Create Views #####################
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]]; ------創(chuàng )建視圖的語(yǔ)法
example: Create or replace view testview as select col1,col2,col3 from table_name; ------創(chuàng )建視圖
/*使用別名*/
Create or replace view testview as select col1,sum(col2) col2_alias from table_name;
/*創(chuàng )建復雜視圖*/
Create view view_name (alias1,alias2,alias3,alias4) as select d.col1,min(e.col1),max(e.col1),avg(e.col1) from table_name1 e,table_name2 d where e.col2=d.col2 group by d.col1;
/*當用update修改數據時(shí)，必須滿(mǎn)足視圖的col1>10的條件，不滿(mǎn)足則不能被改變.*/
Create or replace view view_name as select * from table_name where col1>10 with check option;
/*改變視圖的值.對于簡(jiǎn)單視圖可以用update語(yǔ)法修改表數據，但復雜視圖則不一定能改。如使用了函數，group by ,distinct等的列*/
update view_name set col1=value1;
########################## 增強的 group by 子句 #########################
select [column,] group_function(column)...
from table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column]; -------ROLLUP操作字，對group by子句的各字段從右到左進(jìn)行再聚合
example:
/*其結果看起來(lái)象對col1做小計后，再對col2做小計，最后算總計*/
select col1,col2,sum(col3) from table group by cube(col1,col2);
/*復合rollup表達式*/
select col1,col2,sum(col3) from table group by cube((col1,col2));
/*混合rollup,cube表達式*/
select col1,col2,col3,sum(col4) from table group by col1,rollup(col2),cube(col3);
/*GROUPING(expr)函數，查看select語(yǔ)句種以何字段聚合，其取值為0或1*/
select [column,] group_function(column)...,GROUPING(expr)
from table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column];
聯(lián)系客服
