SQL-Cheat-Sheet

Basics

Create Table	create table table1( id varchar(300) primary key, name varchar(200) not null);
Insert	insert into table1 (id,name) values ('aa','bb');
Delete	delete from table1 where id ='cc';
Update	update table1 set id = 'bb' where id='cc';
Query	select id,name (case gender when 0 then 'male' when 1 then ‘femal’ end ) gender from table1
Delete Table	drop table table1;
Change Table Title	alter table table1 rename to table2;
Copy Table Content	insert into table1 (select * from table2);
Copy Table Structure	create table table1 select * from table2 where 1>1;
Copy Table Content andStructure	create table table1 select * from table2;
Copy Selected Field	create table table1 as select id, name from table2 where 1>1;

Math Functions

Absolute value: abs()	select abs(-2) value from dual;	2
Ceiling: ceil()	select ceil(-2.001) value from dual;	-2
Florr: floor()	select floor(-2.001) value from dual;	-3
Trunc: trunc()	select trunc(-2.001) value from dual;	-2
Round-off: round()	select round(1.234564,4) value from dual;	1.2346
N-th Power: power(m,n)	select power(4,2) value from dual;	16
Square Root: SQRT()	select sqrt(16) value from dual;	4
Random Number: dbms_random(minvalue,maxvalue)	select dbms_random.value() from dual; select dbms_random.value(2,4) value from dual;
Sign: Sign()	select sign(-3) value from dual;	-1
Greatest Value: greatest(value)	select greatest(-1,3,5,7,9) value from dual;	9
LeastValue: least(value)	select least(-1,3,5,7,9) value from dual;	-1
Deal with NULL	select nvl(null,10) value from dual;	10

Rownum

Selectfrom top n rows.(Oracle Don’t Support Selecttop)	select * from student where rownum <3;
Select from table but top n rows.	select * from(select rownum rn ,id,name from student) where rn>2; select * from (select rownum rn, student.* from student) where rn >3;
Select from a region	select * from (select rownum rn, student.* from student) where rn >3 and rn<6;
Sort and select from top.	select * from (select rownum rn, t.* from ( select d.* from DJDRUVER d order bydrivernumber)t )p where p.rn<10;
Sort and select fromregion.	select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn<9 and p.rn>6;
Sort and select from region,another way.	select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum<9 )p where p.rn>6;

Paging Query (10 terms a page)

Without sorting

low efficiency	select * from (select rownum rn, d.* from DJDRIVER d )p where p.rn<=20 and p.rn>=10; select * from (select rownum rn, d.* from DJDRIVER d )p where p.rn between 10 and 20;
high efficiency	select * from (select rownum rn, d.* from DJDRIVER d where rownum<=20 )p where p.rn>=10;

With Sorting

Sort and query a region	select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn<=20 and p.rn>=10;
(low efficiency)	select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t )p where p.rn between 10 and 20;
Sort and query a region(high efficiency)	select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum<=20 )p where p.rn>=10;

  ## String Manipulation

Substring(start from 1)	substr('abcdefg',1,5)	Abcde
Search substring	instr('abcdefg','bc')	TRUE
Append strings	'Hello'||'World'	HelloWorld
Deletewhitespace	trim(' Wish ')	Wish
Deletewhitespace before the string	rtrim('Wish ')	Wish
Deletewhitespace after the string	ltrim(' Wish')	wish
Delete prefix	trim(leading 'w' from 'wish')	ish
Delete trailing	trim(trailing 'h' from 'wish')	wis
Delete	trim('w' from 'wish')	ish
Ascii convert	ascii('A')	65
	ascii('a')	97
Character convert	chr(65)	A
	chr(97)	a
length	length('abcdefg')	7
Capitalize	lower('WISH')	wish
	upper('wish')	WISH
	initcap('wish')	Wish
Replace	replace('wish1','1','youhappy')	wishyouhappy
Translate(string,from_str,to_str). Replace every character in string that appeared in from_str to appropriateone in to_str.	translate('wish1','1','y')	wishy
	translate('wish1','sh1','hy')	wihy
Connect	concat('11','22')	1122

Aggregate Function

Term number	count (distinct|all)
Average	avg (distinct|all)
MaximumValue	max (distinct|all)
MinimumValue	min (distinct|all)
Standard Deviation	stddev(distinct|all)
Sum	sum(distinct|all)
Median	median(distinct|all)