 |
常用的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;
| |
