Oracle中常用SQL语句(一)
来源:互联网 发布:淘宝上复制链接在哪里 编辑:程序博客网 时间:2024/05/22 04:56
1、常用SQL
--新建表: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;
2、数学函数
--绝对值: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; --如果sccore为空,则为10,否则为score
3、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;--排序+区间查询1select * 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;--排序+区间查询2select * from (select rownum rn, t.* from ( select d.* from DJDRIVER d order by DJDRIVER_DRIVERTIMES)t where rownum<9 )p where p.rn>6; --效率远高于方式1
4、分页查询(假设每页显示10条)
4.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;
4.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;
阅读全文
0 0
- Oracle中常用SQL语句(一)
- Oracle常用sql语句(一)
- oracle常用sql语句(不断更新中)
- Oracle中常用SQL语句(二)
- oracle中常用sql语句
- Oracle维护常用SQL语句整理一
- oracle DBA常用sql语句一
- SQL常用语句(一)
- 数据库-oracle常用SQL语句整理(持续更新中)
- Oracle数据库常用Sql语句(整理中)
- oracle中常用的sql语句
- oracle中常用经典sql查询语句。
- oracle中常用的sql查询语句
- oracle 中几个常用sql语句
- oracle中sql语句查询优化(一)
- oracle常用SQL语句
- oracle常用SQL语句
- oracle常用SQL语句
- H.264标准(三)F4V封装格式详解
- Django 实现分页显示
- 普利姆(Prime)算法
- linux java编译 Error: Could not find or load main class hello.java
- java的引用数据类型
- Oracle中常用SQL语句(一)
- 使用Pandas对数据进行筛选和排序
- Java虚拟机发展历程
- wait、join、sleep、yield的区别
- Spring源码分析之SpringMVC的DispatcherServlet是如何处理Http请求的
- HDU-5886-Tower Defence
- 菱形图案输出
- TestLink学习二:Windows搭建TestLink环境
- java集合