常用的sql函数

常用的sql函数
 
（把ename转换成小写，找第二个字母是a）
SQL> select ename from emp where
  2  lower(ename) like '_a%';
 
ENAME
----------
WARD
MARTIN
JAMES
(查询enamel中的第二个字母是不是a)
SQL> select ename from emp where ename like '_A%' or ename
  2  like '_a%';
 
ENAME
----------
WARD
MARTIN
JAMES
(转换成大写)
SQL> select upper(ename) from emp;
 
UPPER(ENAM
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
 
UPPER(ENAM
----------
JAMES
FORD
MILLER
 
已选择14行。
（截取字符串，从第二个字符开始，长度为四）
SQL> select substr(ename, 2,4) from emp;
 
SUBSTR(E
--------
MITH
LLEN
ARD
ONES
ARTI
LAKE
LARK
COTT
ING
URNE
DAMS
 
SUBSTR(E
--------
AMES
ORD
ILLE
 
已选择14行。
（从第二个字符开始，长度是3）
SQL> select substr(ename,2,3)from emp;
 
SUBSTR
------
MIT
LLE
ARD
ONE
ART
LAK
LAR
COT
ING
URN
DAM
 
SUBSTR
------
AME
ORD
ILL
 
已选择14行。
（找到asc是65 的那个字符）
SQL> select chr(65) from dual;
 
C
-
A
（A的ascii值是多少）
SQL> select ascii('A') from dual;
 
ASCII('A')
----------
        65
（四舍五入）
SQL> select round(23,78) from dual;
 
ROUND(23,78)
------------
          23
 
SQL> select round(23.678) from dual;
 
ROUND(23.678)
-------------
           24
（四舍五入小数点后保留两位）
SQL> select round(23.6790,2)from dual;
 
ROUND(23.6790,2)
----------------
           23.68
四舍五入到十位
SQL> select round(23.456,-1)from dual;
 
ROUND(23.456,-1)
----------------
              20
（改变格式，）
SQL> select to_char(sal,'$99,999.9999') from emp;
 
TO_CHAR(SAL,'
-------------
    $800.0000
  $1,600.0000
  $1,250.0000
  $2,975.0000
  $1,250.0000
  $2,850.0000
  $2,450.0000
  $3,000.0000
  $5,000.0000
  $1,500.0000
  $1,100.0000
 
TO_CHAR(SAL,'
-------------
    $950.0000
  $3,000.0000
  $1,300.0000
 
已选择14行。
（转成当地货币的形式）
SQL> select to_char(sal,'L99,999.9999') from emp;
 
TO_CHAR(SAL,'L99,999.9
----------------------
            ￥800.0000
          ￥1,600.0000
          ￥1,250.0000
          ￥2,975.0000
          ￥1,250.0000
          ￥2,850.0000
          ￥2,450.0000
          ￥3,000.0000
          ￥5,000.0000
          ￥1,500.0000
          ￥1,100.0000
 
TO_CHAR(SAL,'L99,999.9
----------------------
            ￥950.0000
          ￥3,000.0000
          ￥1,300.0000
 
已选择14行。
 
SQL> select to_char(sal,'L00000,0000') from emp;
 
TO_CHAR(SAL,'L00000,0
---------------------
         ￥00000,0800
         ￥00000,1600
         ￥00000,1250
         ￥00000,2975
         ￥00000,1250
         ￥00000,2850
         ￥00000,2450
         ￥00000,3000
         ￥00000,5000
         ￥00000,1500
         ￥00000,1100
 
TO_CHAR(SAL,'L00000,0
---------------------
         ￥00000,0950
         ￥00000,3000
         ￥00000,1300
 
已选择14行。
 
SQL> select hiredate from emp;
 
HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
28-9月 -81
01-5月 -81
09-6月 -81
19-4月 -87
17-11月-81
08-9月 -81
23-5月 -87
 
HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82
 
已选择14行。
（改变日期的显示格式：符合我们的习惯）
SQL> select to_char(hiredate,'YYYY-MM--DD HH:MI:SS') from dual;
select to_char(hiredate,'YYYY-MM--DD HH:MI:SS') from dual
               *
第 1 行出现错误:
ORA-00904: "HIREDATE": 标识符无效
 
 
SQL> select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
 
TO_CHAR(HIREDATE,'Y
-------------------
1980-12-17 12:00:00
1981-02-20 12:00:00
1981-02-22 12:00:00
1981-04-02 12:00:00
1981-09-28 12:00:00
1981-05-01 12:00:00
1981-06-09 12:00:00
1987-04-19 12:00:00
1981-11-17 12:00:00
1981-09-08 12:00:00
1987-05-23 12:00:00
 
TO_CHAR(HIREDATE,'Y
-------------------
1981-12-03 12:00:00
1981-12-03 12:00:00
1982-01-23 12:00:00
 
已选择14行。
（改变系统时间的格式）
SQL> select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YY
-------------------
2013-05-07 07:44:13
（把系统格式改成24进制的）
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YY
-------------------
2013-05-07 19:45:14
 
几个转换函数：
(先把日期转化成  一般格式，然后查询比这个日期入职早的员工)
SQL>select ename,hiredate from emp where hiredate >
to_date('1981-1-23 12:00:00','YYYY-MM-DD HH24::MI:SS');
 
（把薪水显示的格式转变）
SQL>select sal from emp where sal>to_number("$1,250.00','$9,999.99');
 
(查询全年的年薪)
SQL>select ename,sal*12 + conn from emp;
 
（对于奖金为0的员工的处理）
SQL>select  ename,sal*12+nvl(comm,0) from emp;
 
 
to_date 转换成日期形式：
 
SQL> select ename,hiredate from emp where hiredate>
  2  to_date('1981-2-20 12:34:33','YYYY-MM-DD HH24:MI:SS');
 
ENAME      HIREDATE
---------- --------------
WARD       22-2月 -81
JONES      02-4月 -81
MARTIN     28-9月 -81
BLAKE      01-5月 -81
CLARK      09-6月 -81
SCOTT      19-4月 -87
KING       17-11月-81
TURNER     08-9月 -81
ADAMS      23-5月 -87
JAMES      03-12月-81
FORD       03-12月-81
 
ENAME      HIREDATE
---------- --------------
MILLER     23-1月 -82
 
已选择12行。
 
to_number的应用：
 
SQL> select sal from emp where sal>to_number
  2  ('$1,250.00','$9,999.99');
 
       SAL
----------
      1600
      2975
      2850
      2450
      3000
      5000
      1500
      3000
      1300
 
已选择9行。
 
SQL> select ename,sal*12+comm from emp;
 
ENAME      SAL*12+COMM
---------- -----------
SMITH
ALLEN            19500
WARD             15500
JONES
MARTIN           16400
BLAKE
CLARK
SCOTT
KING
TURNER           18000
ADAMS
 
ENAME      SAL*12+COMM
---------- -----------
JAMES
FORD
MILLER
 
已选择14行。
 
对于空值的处理：
SQL> select ename,sal*12+nvl(comm,0)from emp;
 
ENAME      SAL*12+NVL(COMM,0)
---------- ------------------
SMITH                    9600
ALLEN                   19500
WARD                    15500
JONES                   35700
MARTIN                  16400
BLAKE                   34200
CLARK                   29400
SCOTT                   36000
KING                    60000
TURNER                  18000
ADAMS                   13200
 
ENAME      SAL*12+NVL(COMM,0)
---------- ------------------
JAMES                   11400
FORD                    36000
MILLER                  15600
 
已选择14行。
 
 
主函数：
 
(查询emp表中的薪水最高的人)
SQL>select max(sal) from emp;
 
（查询emp表总共有多少行）
SQL>select count(*) from emp;
 
求最大值：
SQL> select max(sal) from emp;
 
  MAX(SAL)
----------
      5000
 
求最小值
SQL> select min(sal) from emp;
 
  MIN(SAL)
----------
       800
 
求平均值
SQL> select avg(sal) from emp;
 
  AVG(SAL)
----------
2073.21429
 
先得到平均值，再转换成字符串形式：
SQL> select to_char(avg(sal),'9999999.99') from emp;
 
TO_CHAR(AVG
-----------
    2073.21
 
对平均值进行四舍五入保留两位处理：
SQL> select round(to_char(avg(sal),'999999.99'),2)from emp;
 
ROUND(TO_CHAR(AVG(SAL),'999999.99'),2)
--------------------------------------
                               2073.21
 
求和：
SQL> select sum(sal)from emp;
 
  SUM(SAL)
----------
     29025
 
SQL> select count(*) from emp;
 
  COUNT(*)
----------
        14
 
求有多少行：
SQL> select count(*) from emp where deptno = 10;
 
  COUNT(*)
----------
         3
查询有多少个姓名：
SQL> select count(ename) from emp;
 
COUNT(ENAME)
------------
          14
 
SQL> select count(distinct deptno) from emp;
 
COUNT(DISTINCTDEPTNO)
---------------------
                    3
 
 
分组函数：group by
(查询员工的姓名，薪水，部门号)
SQL>select ename,sal ,deptno from emp;
 
通过分组查一个部门中，平均工资
SQL>select avg(sal) from emp group by deptno;
 
通过对部门分组查询每一个部门的平均工资
SQL>select deptno,avg(sal) from emp group by deptno;
 
通过对部门和工作的分组，查询最高工资
SQL>select max(sal) from emp group by deptno ,job;
 
把部门和工作分组中最高的工资显示出来
SQL>select deptno ,job,max(sal) from emp group by deptno ,job;
 
查询所有员工中的最高工资
SQL>select max(sal) from emp;
 
对deptno分组，查询最高工资（注意：没有在select列表中出现的名称，不能出现在
主函数和 分组列表中）
SQL>select deptno,max(sal) from emp group by deptno;
 
Having（）对分组进行限制：
 
where 是对单条语句进行过滤；Having()可以对分组语句进行过滤；
 
部门的平均工资大于2000的显示：
SQL>select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;
 
最基本的语句的执行顺序，先进行select查询，进行单条语句的过滤，
再进行分组，然后最分组后的进行过滤，再进行排序：
 
eg:对于工资大于1200的按部门进行分组，分组后的平均薪水大于1500，然后按降序进行排列：
SQL>select avg(sal) from emp where sal>1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;