文章标题

基本

–新建表：
create table table1( id varchar(300) primary key, name varchar(200) not null);

–插入数据
insert into table1 (id,name) values (‘aa’,’bb’);

–更新数据
update table1 set id = ‘bb’ where id=’cc’;

–删除数据
delete from table1 where id =’cc’;

–删除表
drop table table1;

–修改表名：
alter table table1 rename to table2;

–表数据复制：
insert into table1 (select * from table2);

–复制表结构：
create table table1 select * from table2 where 1>1;

–复制表结构和数据：
create table table1 select * from table2;

–复制指定字段：
create table table1 as select id, name from table2 where 1>1;

–条件查询：
select id,name (case gender when 0 then ‘男’ when 1 then ‘女’ end ) gender from table1

数学函数

–绝对值：abs()
select abs(-2) value from dual; –(2)

–取整函数（大）：ceil（）
select ceil(-2.001) value from dual; –(-2)

–取整函数（小）：floor（）
select floor(-2.001) value from dual; –(-3)

–取整函数（截取）：trunc（）
select trunc(-2.001) value from dual; – (-2)

–四舍五入：round（）
select round(1.234564,4) value from dual; –(1.2346)

–取平方：Power（m,n）
select power(4,2) value from dual; –(16)

–取平方根:SQRT()
select sqrt(16) value from dual; –(4)

–取随机数:dbms_random(minvalue,maxvalue)
select dbms_random.value() from dual; (默认是0到1之间)
　select dbms_random.value(2,4) value from dual; （2-4之间随机数）

–取符号：Sign()
　　select sign(-3) value from dual; –(-1)
　　select sign(3) value from dual; –(1)

–取集合的最大值:greatest(value)
select greatest(-1,3,5,7,9) value from dual; –(9)

–取集合的最小值:least(value)
select least(-1,3,5,7,9) value from dual; –(-1)

–处理Null值：nvl(空值，代替值)
select nvl(null,10) value from dual; –(10)
　　
select nvl(score,10) score from student;

rownum

rownum小于某个数时可以直接作为查询条件（注意oracle不支持select top）
select * from student where rownum <3;

–查询rownum大于某个数值,需要使用子查询，并且rownum需要有别名
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 (select rownum rn, student.* from student) where rn >3 and rn<6;

–排序+前n条
select * from (select rownum rn, t.* from ( select d.* from DJDRUVER d order by drivernumber)t )p where p.rn<10;

–排序+区间查询1
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;

–排序+区间查询2
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;–效率远高于方式一

分页查询（每页显示10个数据）

1. 不排序
–效率低

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;

–效率高

select * from (select rownum rn, d.* from DJDRIVER d where rownum<=20 )p where p.rn>=10;

2. 排序
–排序+区间查询1（效率低）

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;

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;

–排序+区间查询2（效率高）

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;

**

字符函数

**
–字符函数
select substr(‘abcdefg’,1,5)substr, –字符串截取
instr(‘abcdefg’,’bc’) instr, –查找子串

   'Hello'||'World' concat,                         --连接   trim('  wish  ') trim,                           --去前后空格   rtrim('wish  ') rtrim,                           --去后面空格   ltrim('  wish') ltrim,                           --去前面空格   trim(leading 'w' from 'wish') deleteprefix,      --去前缀   trim(trailing 'h' from 'wish') deletetrailing,   --去后缀   trim('w' from 'wish') trim1,   ascii('A') A1,    ascii('a') A2,                                   --ascii(转换为对应的十进制数)   chr(65) C1,    chr(97) C2,                                      --chr(十进制转对应字符)   length('abcdefg') len,                           --length    lower('WISH')lower,    upper('wish')upper,    initcap('wish')initcap,                            --大小写变换   replace('wish1','1','youhappy') replace,           --替换   translate('wish1','1','y')translate,               --转换，对应一位(前面的位数大于等于后面的位数)   translate('wish1','sh1','hy')translate1,   concat('11','22') concat          　　　　　　　　   --连接

from dual;

to_number

–to_number(expr)
–to_number(expr,format)
–to_number(expr,format,’nls-param’)

select to_number(‘0123’)number1, –converts a string to number
trunc(to_number(‘0123.123’),2) number2,
to_number(‘120.11’,’999.99’) number3,
　　　 to_number(‘0a’,’xx’) number4, –converts a hex number to decimal
to_number(100000,’xxxxxx’) number5

from dual;

聚合函数

student表

1. count
   –count (distinct|all)
   select count(1) as count from student;–效率最高
   select count(*) as count from student;
   select count(distinct score) from student;
   语句结果11

2. avg
   –avg (distinct|all)
   select avg(score) score from student;
   select avg(distinct score) from student;
   select classno,avg(score) score from student group by classno;
   

3. max
   –max (distinct|all)
   select max(score) from student;
   select classno, max(score) score from student group by classno;
4. min
   –min (distinct|all)
   select min(score) from student;
   select classno, min(score) score from student group by classno;
5. stddev（standard deviation）标准差
   –stddev
   select stddev(score) from student;
   select classno, stddev(score) score from student group by classno;
6. sum
   –sum
   select sum(score) from student;
   select classno, sum(score) score from student group by classno;
7. median–中位数
   –median
   select median(score) from student;
   select classno, median(score) score from student group by classno;