oracle笔记4-简单sql查询

一、常用的sql语句

 

 1、使用算术表达式  +  -  *  /

         找出每个员工的奖金和工资的总和

         select sal+comm,ename from emp;

 

2、连接运算符||

3、使用字段别名as

4、空值 is null

5、去除重复行  distinct

6、查询结果排序order by asc （desc）

7、比较运算符><(!= or <>)  between and

8、in  not in

9、模糊查询  %----表示零或多个字符

                      _  表示一个字符

对于特殊符号可以用ESCAPE标识符来查找

select * from emp where ename like '%*_%'  escape  '*'

上面的escape表示  * 后面的那个符号不当成特殊字符处理，就是查找普通的_符号

 

10、逻辑运算符   or and not

 

练习：

1、找出部门10中得经理（MANAGER）和部门20中得普通员工（CLERK）

select * from emp where (deptno=10 and job='MANAGER') OR (deptno = 20 and job='clerk');

2、找出部门10中既不是经理也不是普通员工而且工资大于等于2000的员工

select * from emp where deptno=10 and job not in('Manager','Clerk') and sal >=2000

3、找出有奖金的员工的不同工作

select distinct job from emp where commis not null and comm >0

4 、找出没有奖金或者奖金低于500的员工

select * from emp where comm is null or comm<500

 

 

二、sql函数

1、单行函数

1）字符函数

UPPDER  转换为大写

select UPPER('abcde') from dual;     ---ABCDE

select * from emp where ename = UPPER('smith');

 

LOWER  转换为小写

select  lower('ABCDE') from dual;

 

INITCAP--使第一个字母大写

select inicap(ename) from emp;

 

concat  --连接两个字段

select concat('a','b') from dual;    

select 'a'||'b' from dual;

结果是：  ab

select concat(empno,ename) from emp; 

结果是： 7369Smith

                 ....

 

substr

select substr('abcde', length('abcde')-2) from dual;

结果：cde

select substr('abcde',-3,3) from dual;

结果：cde

length

select length(ename) from emp;

 

replace  将’A‘替换掉’a‘

select replace (ename,'a','A') from emp;

 

instr  定义字符的位置索引

select instr('Hello World', 'or') from dual;   

结果：8 === equals (index of   in 'java')

 

Lpad

lpad('Smith',10,'*')  左侧填充*   （*****Smith）

Rpad

rpad('Smith',10,'*')  右侧填充*  （Smith*****）

注意：10---代表总共十个字符，不足十个字符用’*‘填充，lpad-向左侧填充； rpad--向右侧填充

 

Trim

trim('        Mr Smith           ') 作用：过滤掉首尾的空格，相当于java里面的trim（）方法  （Mr Smith）

 

2）数值函数

round

select round(412,-2) from dual;          -----------400

select round (412.313,2) from dual;  ------------412.31

 

mod

 

trunc 截取

select trunc(412.13,-2) from dual;  ---400

3）日期函数

MONTHS_BETWEEN()返回两个日期之间的月份数。
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual;

    MONTHS
----------
        12

select months_between(sysdate,hiredate) from emp;

 

 ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。

 ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

 例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99)   返回  28－Feb-95

注：上例中29调整为28，是因为96年二月份最后一天是29号，而95年二月份最后一天是28号。

 add_months(to_date('15-Nov-1961','d-mon-yyyy'),1)  返回   15-Dec-1961

 add_months(to_date('30-Nov-1961','d-mon-yyyy'),1)  返回   31-Dec-1961

注：从30调整为31，为了保持都是对应最后一天。

 add_months(to_date('31-Jan-1999','d-mon-yyyy'),1)  返回   28-Feb-1999

注：函数将31日调为28日，以使结果对应新一月的最后一天，因1999年2月只有28天。

select add_months(sysdate,1) from dual;

 

next_day();

NEXT_DAT函数返回输入日期开始，紧随其后的指定星期对应的日期，weekday可以用全称，也可以用缩写（如'monday','tuesday','wed')

   

例：next_day(to_date('1999.11.24','yyyy.mm.dd')，'friday')       返回   1999年11月26日

例：next_day(to_date('1999.11.24','yyyy.mm.dd')，'wed')         返回   1999年11月01日

注：1999年11月24日是星期三，第二个参数是星期五，是两天后。第二个例子由于日期正好是星期三，只能用下一个星期三日期。

 

last_day（d）： 時間點d當月份最後一天

select last_day(sysdate) from dual;--2011.12.31

 

NEW_TIME(d,c1,c2)  

NEW_TIME(d,c1,c2)  

 --轉換新時區 

 ex.  

  select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') 台北,   

         to_char(NEW_TIME(sysdate,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') 格林威治   

  from dual;  

  台北                 格林威治  

  -------------------- ------------------- 

  2007/09/21 14:36:53  2007/09/21 19:36:53  

4)转换函数

to_char

select to_char(sysdate,'yyyy') from dual;

select to_char(sysdate,'fmyyyy-mm-dd') from dual;

select to_char(sal,'L999,999,999') from emp;

select to_char(sysdate,'D') from dual;//返回星期

 

to_number

select to_number('13') + to_number('14') from dual;

 

to_date

select to_date('20111205','yyyyMMdd') from dual;

select to_date('20111205','yyyy-MM-dd') from dual;

 

5)通用函数

nvl(a,b)函数 如果a不为null 则返回a,如果a为null则返回b;

select nvl(comm,0) from emp;---如果comm 为null ，就为0 （默认）

 NULLIF()函数

如果表达式exp1 与 exp2 值相等则返回null，否则返回exp1的值

select NULLIF(2,2) from dual;

结果：---null

select NULLIF(2,3) from dual;

结果：---2

 

nvl2(a,b,c)函数,如果a不为null 则返回b,如果a为null则返回c;

select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp;

 注意：如果comm（奖金）不为空，就获得奖金+工资的和，否则就获得工资的金额

 

COALESCE()函数  依次考察各个参数表达式，遇到非null的值即停止并返回该值

 

***********************

CASE表达式

select empno,ename,sal

     case deptno

               when 10 then '财务部'

               when  20 then '研发部'

               when 30 then '销售部'

               else '未知部门'

               end 部门

  from emp;

 

decode()函数， 和case表达式类似，decode（）

函数也用于实现多路分支结构

 

例子：

select empno,ename,sal,decode(deptno,10,'财务部'

                                                                            20，’研发部‘

                                                                             30，’销售部‘

                                                      ’未知部门‘)

部门

from emp;