oracle数据库查询2

2.将字符串转为小写

<span style="color: #0000ff;">select</span> lower（ename）<span style="color: #0000ff;">as</span> name <span style="color: #0000ff;">from</span> emp

3.逐值替换

<span style="color: #0000ff;">select</span> decode(deptno,<span style="color: #800000;">‘</span><span style="color: #800000;">10</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">开发部</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">20</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">产品部</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">30</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">维护部</span><span style="color: #800000;">‘</span>)<span style="color: #0000ff;">from</span> emp

4.当前系统日期的年份

<span style="color: #0000ff;">select</span> extract(year <span style="color: #0000ff;">from</span> sysdate) <span style="color: #0000ff;">from</span> dual

5.查询每个员工的工龄

<span style="color: #0000ff;">select</span> extract(year <span style="color: #0000ff;">from</span> sysdate) - extract(year <span style="color: #0000ff;">from</span> hiredate)  <span style="color: #0000ff;">as</span> age <span style="color: #0000ff;">from</span> emp

6.转换函数

<span style="color: #0000ff;">select</span> to_char(<span style="color: #800080;">0.123</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">$0.9999</span><span style="color: #800000;">‘</span>)<span style="color: #0000ff;">from</span> dual

7.将日期对象转成字符串

SELECT TO_CHAR(sysdate,<span style="color: #800000;">‘</span><span style="color: #800000;">YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS</span><span style="color: #800000;">‘</span>)FROM dual;

8.查询员工工资和（工资+奖金）

<span style="color: #0000ff;">select</span> ename,sal+nvl(comm,<span style="color: #800080;">0</span>) <span style="color: #0000ff;">from</span><span style="color: #000000;"> emp
</span><span style="color: #0000ff;">select</span> ename,sal+nvl2(comm,comm,<span style="color: #800080;">0</span>) <span style="color: #0000ff;">from</span> emp

9.如果二个值不一样则结果为第一个值，如果两个值一样则结果为null

<span style="color: #0000ff;">select</span> nullif(<span style="color: #800080;">200</span>,<span style="color: #800080;">200</span>) <span style="color: #0000ff;">from</span> dual

10.分析函数（看后面num‘值）

-- row_number()连续排位

<span style="color: #0000ff;">select</span> emp.*, row_number() over(order by sal desc ) <span style="color: #0000ff;">as</span> num <span style="color: #0000ff;">from</span> emp 

 --rank

<span style="color: #0000ff;">select</span> emp.*, rank() over(order by sal desc ) <span style="color: #0000ff;">as</span> num <span style="color: #0000ff;">from</span> emp 

 -- dense rank

 11.创建一个用户

<span style="color: #000000;">CREATE  USER  test  IDENTIFIED  BY  test;
GRANT  CONNECT , CREATE  SYNONYM TO test;
GRANT  SELECT   ON   SCOTT.EMP  TO test;
GRANT  DELETE  ON   SCOTT.EMP TO test;
GRANT  UPDATE  ON  SCOTT.EMP TO test;</span>

12.创建同义词

<span style="color: #000000;">CREATE  SYNONYM   e   FOR  SCOTT.emp;
</span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> e

13.创建公有同义词

<span style="color: #000000;">CREATE   PUBLIC   SYNONYM  pub_emp FOR  SCOTT.emp;
</span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span>  pub_emp 

14.创建序列

<span style="color: #000000;">CREATE  sequence mysql
start with </span><span style="color: #800080;">1</span><span style="color: #000000;">
increment by </span><span style="color: #800080;">1</span><span style="color: #000000;">
create  table student(
        sid </span><span style="color: #0000ff;">int</span><span style="color: #000000;"> primary key,
        sname varchar(</span><span style="color: #800080;">20</span><span style="color: #000000;">)
) 
insert into student values(mysql.nextval,</span><span style="color: #800000;">‘</span><span style="color: #800000;">张三</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">select</span> mysql.currval <span style="color: #0000ff;">from</span><span style="color: #000000;"> dual
</span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> student

15.授权

grant create view to scott

16.创建视图

<span style="color: #000000;">create  view dept_emp 
</span><span style="color: #0000ff;">as</span> 
<span style="color: #0000ff;">select</span> dept.deptno,dname,loc,empno,ename,job,mgr,hiredate <span style="color: #0000ff;">from</span> emp join dept on emp.deptno=dept.deptno

欢迎各位大神指点和评论；

oracle数据库查询2

标签：start   span   var   结果   comm   sid   dna   loading   函数