| 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
| 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