Skip to content

B-SQL语句-01-单行函数.md

Latest commit

 

History

History
1500 lines (1143 loc) · 33.3 KB

B-SQL语句-01-单行函数.md

File metadata and controls

1500 lines (1143 loc) · 33.3 KB

SQL语句-查询语句-单行函数

查询语句DQL简介

select * | {[distinct] column | expression [alias] , ...}
from table;
  1. select 后面跟 通配符|关键字|表达式 别名 或者一些计算
  2. from 后面跟 表名 或者 结果集 ，这就是oracle对国标扩展的部分，能够对结果集进行二次查询
  3. 必须要有结束符号 ;有结束符，sql才会被运行
  4. 大小写不区分
  5. 可以多行书写

简单的sql查询

简单的sql查询 语句
查看scott用户下有哪些表和视图 SQL> select * from tab;
描述一张表的结构，不看表中的数据 SQL> desc dept
查看雇员表中的所有数据 select * from dept;
描述雇员表的结构 SQL> desc emp
查看emp表中感兴趣的列 SQL> select ename,sal from emp;
在select中使用四则运算:null不能参与四则运算 select ename,(sal+100)*12 from emp;
为列定义别名 select ename AS first_name,sal*12 "Annual Salary" from emp;
连接操作符 select ename,job,ename||' is a '||job detail from emp;
压缩重复行 select distinct deptno,job from emp;
将缓冲区中的命令保存为脚本 SQL> save p1_1.sql
查看sql脚本内容 SQL> get p1_1.sql
运行sql脚本 SQL> @p1_1.sql

sqlplus结果集的显示风格为:

  • 字符串和日期类型左对齐
  • 数字类型右对齐
  • 缺省显示为大写
SQL> SELECT * FROM TAB;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
BONUS			       TABLE
DEPT			       TABLE
EMP			       TABLE
SALGRADE		       TABLE

SQL> desc dept;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO 				   NOT NULL NUMBER(2)
 DNAME						    VARCHAR2(14)
 LOC						    VARCHAR2(13)

SQL> select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

SQL> select ename,sal from emp;

ENAME		  SAL
---------- ----------
SMITH		  800
ALLEN		 1600
WARD		 1250
JONES		 2975
MARTIN		 1250
BLAKE		 2850
CLARK		 2450
SCOTT		 3000
KING		 5000
TURNER		 1500
ADAMS		 1100

ENAME		  SAL
---------- ----------
JAMES		  950
FORD		 3000
MILLER		 1300

14 rows selected.

SQL> select ename,(sal+100)*12 from emp;

ENAME	   (SAL+100)*12
---------- ------------
SMITH		  10800
ALLEN		  20400
WARD		  16200
JONES		  36900
MARTIN		  16200
BLAKE		  35400
CLARK		  30600
SCOTT		  37200
KING		  61200
TURNER		  19200
ADAMS		  14400

ENAME	   (SAL+100)*12
---------- ------------
JAMES		  12600
FORD		  37200
MILLER		  16800

14 rows selected.

-- (SAL+100)*12列在磁盘上并没有保存，我们称其为计算表达式所生成的伪列
-- 空值不能参与运算
SQL> select ename,sal,comm,sal+comm from emp;

ENAME		  SAL	    COMM   SAL+COMM
---------- ---------- ---------- ----------
SMITH		  800
ALLEN		 1600	     300       1900
WARD		 1250	     500       1750
JONES		 2975
MARTIN		 1250	    1400       2650
BLAKE		 2850
CLARK		 2450
SCOTT		 3000
KING		 5000
TURNER		 1500	       0       1500
ADAMS		 1100

ENAME		  SAL	    COMM   SAL+COMM
---------- ---------- ---------- ----------
JAMES		  950
FORD		 3000
MILLER		 1300

14 rows selected.

-- 将工资和奖金求和，结果发现奖金comm列为null的员工不用发工资了！这是错误的

SQL> select ename as first_name , sal*12 "Annual Salary" from emp;    

FIRST_NAME Annual Salary
---------- -------------
SMITH		    9600
ALLEN		   19200
WARD		   15000
JONES		   35700
MARTIN		   15000
BLAKE		   34200
CLARK		   29400
SCOTT		   36000
KING		   60000
TURNER		   18000
ADAMS		   13200

FIRST_NAME Annual Salary
---------- -------------
JAMES		   11400
FORD		   36000
MILLER		   15600

14 rows selected.

-- as可以省略，如果别名加了引号，则显示指定的字符，而不会使用缺省的大写

SQL> select ename,job,ename || ' is a ' || job detail from emp;

ENAME	   JOB	     DETAIL
---------- --------- -------------------------
SMITH	   CLERK     SMITH is a CLERK
ALLEN	   SALESMAN  ALLEN is a SALESMAN
WARD	   SALESMAN  WARD is a SALESMAN
JONES	   MANAGER   JONES is a MANAGER
MARTIN	   SALESMAN  MARTIN is a SALESMAN
BLAKE	   MANAGER   BLAKE is a MANAGER
CLARK	   MANAGER   CLARK is a MANAGER
SCOTT	   ANALYST   SCOTT is a ANALYST
KING	   PRESIDENT KING is a PRESIDENT
TURNER	   SALESMAN  TURNER is a SALESMAN
ADAMS	   CLERK     ADAMS is a CLERK

ENAME	   JOB	     DETAIL
---------- --------- -------------------------
JAMES	   CLERK     JAMES is a CLERK
FORD	   ANALYST   FORD is a ANALYST
MILLER	   CLERK     MILLER is a CLERK

14 rows selected.

-- ||是字符连接符 detail是别名

SQL> select distinct deptno,job from emp;

    DEPTNO JOB
---------- ---------
	20 CLERK
	30 SALESMAN
	20 MANAGER
	30 CLERK
	10 PRESIDENT
	30 MANAGER
	10 CLERK
	10 MANAGER
	20 ANALYST

9 rows selected.

-- distinct 去除重复

SQL> save p1_1.sql
Created file p1_1.sql
SQL> get p1_1.sql
  1* select distinct deptno,job from emp
SQL> @p1_1.sql

    DEPTNO JOB
---------- ---------
	20 CLERK
	30 SALESMAN
	20 MANAGER
	30 CLERK
	10 PRESIDENT
	30 MANAGER
	10 CLERK
	10 MANAGER
	20 ANALYST

9 rows selected.

限制和排列数据

  1. 工资高于1500的销售员？
  2. 查询10部门的雇员和20部门工资小与2000的雇员？
  3. 查询有奖金的雇员？
  4. 使用rownum伪列限制查询返回的行的数量
SQL> select ename,sal from emp where sal>1500;

ENAME		  SAL
---------- ----------
ALLEN		 1600
JONES		 2975
BLAKE		 2850
CLARK		 2450
SCOTT		 3000
KING		 5000
FORD		 3000

7 rows selected.

SQL> select deptno,ename,sal from emp where (deptno=10 or deptno=20) and sal < 2000;

    DEPTNO ENAME	     SAL
---------- ---------- ----------
	20 SMITH	     800
	20 ADAMS	    1100
	10 MILLER	    1300

SQL> select deptno,ename,sal from emp where deptno in (10,20) and sal < 2000;

    DEPTNO ENAME	     SAL
---------- ---------- ----------
	20 SMITH	     800
	20 ADAMS	    1100
	10 MILLER	    1300

SQL> select ename,comm from emp where comm is not null;

ENAME		 COMM
---------- ----------
ALLEN		  300
WARD		  500
MARTIN		 1400
TURNER		    0

SQL> select ename from emp where rownum < 6;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN

-- mysql中限制行是用limit，而oracle用rownum

单行函数

五种：字符函数、数值函数、日期函数、转换函数、其他函数

字符函数

字符串函数 函数名 解释
大小写转换 LOWER(column or expression) 小写
UPPER(column or expression) 把字母全部变成大写
INITCAP(column or expression) 首字母大写
字符处理 CONCAT(column1 or expression1,column2|expression2) 连接字符
SUBSTR(column or expression,m[,n]) 截取从索引位m的字符开始的数量为n个的字符，索引从1开始
LENGTH(column or expression) 返回字符串长度
INSTR(column or expression,‘string’, [,m], [n] ) 返回字符串的位置。你可以随机地指定从第 m 个字母开始搜索第 n 个要查找的字符串。m 和 n 缺省为 1, 意思是从第一个字母开始查找第一次出现要查找的字符串的位置。
LPAD(column or expression, n,'string') 右对齐字符串,左面用指定字符填充至 n位;
RPAD(column or expression, n,'string') 左对齐字符串,右边用指定字符填充至 n位
TRIM(leading or trailing or both, trim_character FROM trim_source) 删除头尾字符，默认为空白，类似于python中strim
REPLACE(text,search_string,replacement_string) 查找并替换字符串

字符函数示例

  1. 记录中的字符串是区分大小写，如果想从海量数据中搜索scott用户，该如何去做呢？
select * from emp where lower(ename)='scott';

究竟用lower还是upper，需要规范前端应用程序，如果小写就都小写，要大写就都大写，函数的选择影响索引的创建，代码的书写规则影响后面索引的创建。

  1. instr和substr的区别 instr为指定一个字符获取该字符在目标字符串的位置 substr为截取制定索引位的字符串 将两者结合在一起用，比如打印字符串中第一个a和第二个a之间的字符串，包含第一个a
SQL> select instr('abca','a') from dual;

INSTR('ABCA','A')
-----------------
		1

SQL> select instr('abca','a',2) from dual;

INSTR('ABCA','A',2)
-------------------
		  4

SQL> select substr('abca',1,4-1) from dual;

SUB
---
abc

SQL> select instr('superman batman wonderwoman','batman') from dual;

INSTR('SUPERMANBATMANWONDERWOMAN','BATMAN')
-------------------------------------------
					 10
-- 截取字符串'superman batman wonderwoman'中从batman开始到最后

SQL> select substr('superman batman wonderwoman',instr('superman batman wonderwoman','batman')) from dual;

SUBSTR('SUPERMANBA
------------------
batman wonderwoman
  1. 填充，打印固定字符，实现左右对齐
SQL> select lpad('abc',6,'*') from dual;

LPAD('
------
***abc

SQL> select rpad('abc',6,'*') from dual;

RPAD('
------
abc***
  1. 删除字符串 ab 前后空白
SQL> select trim('  abc  ') from dual;

TRI
---
abc

SQL> select '  abc  ' from dual;

'ABC'
-------
  abc

一般空格在左边容易发现，空格在右边不容易发现，默认取出的半角的，所以最好再来一次全角的空格。

例如，从emp中查找雇员名为scott的详细信息

SQL> insert into emp values (1111,'SCoTT ','ANALYST',7566,'19-APR-87',3000,20,NULL);

1 row created.

SQL> select * from emp where lower(ename)='scott';

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20

SQL> select * from emp where trim(lower(ename))='scott';

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      1111 SCoTT      ANALYST	      7566 19-APR-87	   3000 	20
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20

SQL> select * from emp where trim(' ' from trim(lower(ename)))='scott';

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      1111 SCoTT      ANALYST	      7566 19-APR-87	   3000 	20
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20

trim只能带一个字符

  1. 替换字符串
SQL> select replace('superman batman wonderwoman','batman','booboo') from dual;

REPLACE('SUPERMANBATMANWOND
---------------------------
superman booboo wonderwoman
  1. 显示姓氏的最后一个字母是n的那些雇员的相应资料:
SQL> select ename from emp where substr(lower(ename),instr(lower(ename),'n',-1)) = 'n' ;

ENAME
----------
ALLEN
MARTIN

SQL> select ename from emp where lower(ename) like '%n';

ENAME
----------
ALLEN
MARTIN
  1. 执行以下sql并解释的含义
select concat('Hello','World') from dual;
select concat(ename,job) from emp;
select substr('Helloworld',1,2) from dual;
select substr('Helloworld',5) from dual;
select substr('Helloworld',-5,2) from dual;
select length('Helloworld') from dual;
select instr('Helloworld','l') from dual;
select instr('Helloworld','l',1,2) from dual;
select instr('Helloworld','l',-1,2) from dual;
select instr('Helloworld','l',-1) from dual;
select lpad('Hello',10,'*') from dual;
select rpad('Hello',10,'*') from dual;
select trim('H' from 'HelloHhHH') from dual;
select * from emp where trim(' ' from UPPER(ename))='SCOTT';
select replace('Helloworld','owo','xxoo') from dual;

数值函数

  • ROUND :将值舍入到指定的小数位
  • TRUNC :将值截断到指定的小数位
  • MOD :返回除法运算的余数

DUAL 是可用于查看函数和计算结果的公用表。

执行以下sql并解释的含义

select round(45.926,2) from dual;
select round(45.926,0) from dual;
select round(45.926) from dual;
select round(45.926,-1) from dual;

select trunc(45.926,2) from dual;
select trunc(45.926,0) from dual;
select trunc(45.926) from dual;
select trunc(45.926,-1) from dual;

select mod(15,4) from dual;
select power(3,3) from dual;
select ceil(1.00001) from dual;
select abs(-190) from dual;

日期函数

日期是以数字保存的，可以进行加减运算

函数 结果
MONTHS_BETWEEN 两个日期之间的月数
ADD_MONTHS 将指定的月数添加到日期
NEXT_DAY 指定日期之后的下一个日期
LAST_DAY 当月最后一天
ROUND 舍入日期
TRUNC 截断日期
MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 19.6774194
ADD_MONTHS ('31-JAN-96',1) '29-FEB-96'
NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95'
LAST_DAY ('01-FEB-95') '28-FEB-95'
  1. 计算每一个雇员工作的时间（天）
  • sysdate返回当前时间
  • hiredate雇员入职时间
SQL> select sysdate from dual;

SYSDATE
---------
29-JUN-17

SQL> select sysdate - hiredate from emp;

SYSDATE-HIREDATE
----------------
      11029.7991
      13343.7991
      13278.7991
      13276.7991
      13237.7991
      13058.7991
      13208.7991
      13169.7991
      11029.7991
      13008.7991
      13078.7991
      10995.7991
      12992.7991
      12992.7991
      12941.7991

15 rows selected.

这里计算的结果为天数

  1. 计算每个雇员工作的时间，单位为月
  • months_between() 计算两个时间点间的月数
SQL> select ename,months_between(sysdate,hiredate) from emp;

ENAME	   MONTHS_BETWEEN(SYSDATE,HIREDATE)
---------- --------------------------------
SCoTT				 362.348474
SMITH				  438.41299
ALLEN				 436.316216
WARD				   436.2517
JONES				 434.896861
MARTIN				 429.058152
BLAKE				 433.929119
CLARK				 432.671055
SCOTT				 362.348474
KING				  427.41299
TURNER				 429.703313
ADAMS				 361.219442
JAMES				 426.864603
FORD				 426.864603
MILLER				 425.219442

15 rows selected.
  1. 假设员工使用期都是三个月，查看员工专正日期
  • add_months() 在制定时间点上加多少个月份
SQL> select ename,hiredate,add_months(hiredate,3)  from emp;

ENAME	   HIREDATE  ADD_MONTH
---------- --------- ---------
SCoTT	   19-APR-87 19-JUL-87
SMITH	   17-DEC-80 17-MAR-81
ALLEN	   20-FEB-81 20-MAY-81
WARD	   22-FEB-81 22-MAY-81
JONES	   02-APR-81 02-JUL-81
MARTIN	   28-SEP-81 28-DEC-81
BLAKE	   01-MAY-81 01-AUG-81
CLARK	   09-JUN-81 09-SEP-81
SCOTT	   19-APR-87 19-JUL-87
KING	   17-NOV-81 17-FEB-82
TURNER	   08-SEP-81 08-DEC-81
ADAMS	   23-MAY-87 23-AUG-87
JAMES	   03-DEC-81 03-MAR-82
FORD	   03-DEC-81 03-MAR-82
MILLER	   23-JAN-82 23-APR-82

15 rows selected.
  1. 这周六是哪一天？本月最后一天是那一天？
  • next_day() 用周几的方式来表示将来的某一天
  • last_day() 指定时间点所在月份的最后一天，返回自然月的最后一天

Last_day()快速定位自然月最后一天，还有什么用？可以计算期末量。

例如：每个月月底库存还剩下多少？月底销售员的销售额是多少？

同比：今年二月份比去年二月份有什么变化？

环比：今年二月份和今年一月份有什么变化？

分析出淡季旺季

同期比：

SQL> select sysdate from dual;

SYSDATE
---------
29-JUN-17

SQL> select next_day(sysdate,'sun') as SUN ,last_day(sysdate) Last from dual;

SUN	  LAST
--------- ---------
02-JUL-17 30-JUN-17
  1. 生日所在那个月的最后一天
SQL> select last_day(to_date('1990-04-15','yyyy-mm-dd')) from dual;

LAST_DAY(
---------
30-APR-90

SQL> select last_day(to_date('1900-02-15','yyyy-mm-dd')) from dual;

LAST_DAY(
---------
28-FEB-00

每四年会多23小时48分钟xx秒，差了十几分钟，这样累计400年我们就丢了一天，这一天算到哪里去呢？

就在1900年就不算闰年，（能被100整除，还能被400整除）

还可以用来计算自然月的月底销售人员的销售额

期末量、期初量、同比、环比

  1. 日期只显示年或者月

round() 四舍五入 trunc() 截断日期

'year': <=6月 舍弃，> 6月 进

'month': <=15天 舍弃，> 15天 进

SQL> select sysdate from dual;

SYSDATE
---------
29-JUN-17

SQL> select round(sysdate,'year') from dual;

ROUND(SYS
---------
01-JAN-17

SQL> select round(sysdate+30,'year') from dual;

ROUND(SYS
---------
01-JAN-18

SQL> select round(sysdate,'month') from dual;

ROUND(SYS
---------
01-JUL-17

SQL> select round(sysdate-15,'month') from dual;

ROUND(SYS
---------
01-JUN-17


SQL> select round(sysdate-15,'month') from dual;

ROUND(SYS
---------
01-JUN-17

SQL> select to_date('2017-07-16','yyyy-mm-dd') from dual;

TO_DATE(
---------
16-JUL-17

SQL> select round(to_date('2017-07-16','yyyy-mm-dd'),'year') from dual;

ROUND(TO_
---------
01-JAN-18

SQL> select trunc(to_date('2017-07-16','yyyy-mm-dd'),'year') from dual;

TRUNC(TO_
---------
16-JAN-17

SQL> select round(to_date('2017-07-16','yyyy-mm-dd'),'month') from dual;

ROUND(TO_
---------
01-AUG-17

SQL> select trunc(to_date('2017-07-16','yyyy-mm-dd'),'month') from dual;

TRUNC(TO_
---------
01-JUL-17
  1. 执行以下sql并解释的含义
select sysdate from dual;
select sysdate,sysdate+1/1440 from dual;
select months_between(sysdate,hiredate),ename from emp;
select sysdate,add_months(sysdate,6) from dual;
select sysdate,next_day(sysdate,'wed') from dual;
select sysdate,last_day(to_date('01-feb-1900','dd-mon-yyyy')) from dual;
select round(sysdate,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;

转换函数

数据类型转化分为两种：

  1. 隐式数据类型转换 系统自己能作的比较简单的，例如数字变字符串之类的
  2. 显示数据类型转换 必须通过函数，你来制定规则

隐式数据类型转换

Oracle服务器可以自动转换下面的数据类型

  • number<--->varchar2|char<---->date

显式数据类型转换

num----------------------------->char---------------------------->date

			to_char(num,'$9.00')		to_date(char,'YYYY-MM-DD')

num<-----------------------------char<-----------------------------date

to_number(char,'L99.00') to_char(date,'YYYY-MM-DD')

  1. date to_char

TO_CHAR(date, 'format_model')

日期格式样式的元素

元素 结果
YYYY 用数字表示的完整年份
YEAR 拼写出的年份(用英文表示)
MM 月份的两位数值
MONTH 月份的完整名称
MON 月份的三个字母缩写
DY 一周中某日的三个字母缩写
DAY 一周中某日的完整名称
DD 用数字表示的月份中某日
W 星期几
HH 小时
MI 分钟
SS 秒 
SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;

TO_CHAR(SY
----------
2017-07-03

select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual; 

SQL> select to_char(sysdate,'year-month-day') from dual;

TO_CHAR(SYSDATE,'YEAR-MONTH-DAY')
--------------------------------------------------------------
twenty seventeen-july	  -monday

SQL> select to_char(sysdate,'year-month-dy') from dual;

TO_CHAR(SYSDATE,'YEAR-MONTH-DY')
--------------------------------------------------------
twenty seventeen-july	  -mon

SQL> select to_char(sysdate,'dd-mon-rr') from dual;

TO_CHAR(S
---------
03-jul-17

SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;

TO_CHAR(HI
----------
1987-04-19
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
1981-06-09
1987-04-19
1981-11-17
1981-09-08
1987-05-23
1981-12-03
1981-12-03
1982-01-23

15 rows selected.

SQL> select to_char(hiredate,'dd-mm-yy') from emp;

TO_CHAR(
--------
19-04-87
17-12-80
20-02-81
22-02-81
02-04-81
28-09-81
01-05-81
09-06-81
19-04-87
17-11-81
08-09-81
23-05-87
03-12-81
03-12-81
23-01-82

15 rows selected.

SQL> select hiredate from emp;

HIREDATE
---------
19-APR-87
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82

15 rows selected.

日期的后台保存是，前置0+世纪+年+月+日

  1. number to_char

TO_CHAR(number, 'format_model')

下面列出了一些格式元素,可以将其与 TO_CHAR 函数配合使用,以便将数字值显示为字符:

元素 结果
9 代表一个数字
0 强制显示零
$ 放置一个浮动的美元符号
L 使用浮动的本地货币符号
. 显示小数点
, 显示作为千位指示符的逗号 
-- 将数字以指定格式打印出来

SQL> select ename,to_char(sal,'L99,999.99') as sal from emp;

ENAME	   SAL
---------- --------------------
SCoTT		      $3,000.00
SMITH			$800.00
ALLEN		      $1,600.00
WARD		      $1,250.00
JONES		      $2,975.00
MARTIN		      $1,250.00
BLAKE		      $2,850.00
CLARK		      $2,450.00
SCOTT		      $3,000.00
KING		      $5,000.00
TURNER		      $1,500.00
ADAMS		      $1,100.00
JAMES			$950.00
FORD		      $3,000.00
MILLER		      $1,300.00

15 rows selected.

-- 00和99的区别在整数会前置0补满指定位数

SQL> select ename,to_char(sal,'L00,000.00') as sal from emp;

ENAME	   SAL
---------- --------------------
SCoTT		     $03,000.00
SMITH		     $00,800.00
ALLEN		     $01,600.00
WARD		     $01,250.00
JONES		     $02,975.00
MARTIN		     $01,250.00
BLAKE		     $02,850.00
CLARK		     $02,450.00
SCOTT		     $03,000.00
KING		     $05,000.00
TURNER		     $01,500.00
ADAMS		     $01,100.00
JAMES		     $00,950.00
FORD		     $03,000.00
MILLER		     $01,300.00

15 rows selected.
  1. char to_number

TO_NUMBER(char[, 'format_model'])

SQL> select to_number(' $00,800.00','L99999.00') from dual;

TO_NUMBER('$00,800.00','L99999.00')
-----------------------------------
				800

SQL> select to_number(' $00,800.00','$99999.00') from dual;

TO_NUMBER('$00,800.00','$99999.00')
-----------------------------------
				800

SQL> select to_number(' $00,800.00','$9999.00') from dual;
select to_number(' $00,800.00','$9999.00') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

第二参数指定的数字位数不能小于第一个参数的位数，否则会报错。

  1. char to_date

TO_DATE(char[, 'format_model'])

SQL> select to_date('1997-10-1','yyyy-mm-dd') from dual;

TO_DATE('
---------
01-OCT-97

SQL> select to_char(to_date('1997-10-1','yyyy-mm-dd'),'yyyy-mm-dd') from dual;

TO_CHAR(TO
----------
1997-10-01

RR和YY年份

yy年份表示法，当前系统时间所在的世纪

场景重现

1997年10月1日 记录在数据库中用dd-mon-yy表示为 01-OCT-97

  1. 1998年，用户读取该数据，读 yyyy-mm-dd 为 1997-10-01
  2. 2008年，用户读取该数据，读 yyyy-mm-dd 为 2007-10-01

此时就出错了，因为数据库内部使用yy的方式记录年，读取世纪时是根据当前世纪来取的，于是无法跨越千年。

rr年份表示法，以50年为分界，已经取代了yy方式

当前日期 日期范围 记录时间 日期范围 显示世纪
now 0-49 date 0-49 本世纪
now 50-99 date 50-99 本世纪
now 0-49 date 50-99 上个世纪
now 50-99 date 0-49 下个世纪 
SQL> select
  2  to_char(sysdate,'yyyy') curr_year,
  3  to_char(to_date('07','yy'),'yyyy') yy07,
  4  to_char(to_date('97','yy'),'yyyy') yy97,
  5  to_char(to_date('07','rr'),'yyyy') rr07,
  6  to_char(to_date('97','rr'),'yyyy') rr97
  7  from dual;

CURR YY07 YY97 RR07 RR97
---- ---- ---- ---- ----
2017 2007 2097 2007 1997

-- 将字符串97转换为日期时，一定注意rr和yy，建议不要用两位表示年份。

SQL> select to_char(to_date('97-2-1','rr-mm-dd'),'yyyy-mm-dd') from dual;

TO_CHAR(TO
----------
1997-02-01

SQL> select to_char(to_date('97-2-1','yy-mm-dd'),'yyyy-mm-dd') from dual;

TO_CHAR(TO
----------
2097-02-01

练习以下sql：

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select ename,sal,to_char(sal,'L999,999.99') from emp;
select to_number('$800.00','L999.99') from dual;
select to_date('2016-09-15','yyyy-mm-dd') from dual;
select
to_char(sysdate,'yyyy') curr_year,
to_char(to_date('05','yy'),'yyyy') yy05,
to_char(to_date('99','yy'),'yyyy') yy99,
to_char(to_date('05','rr'),'yyyy') rr05,
to_char(to_date('99','rr'),'yyyy') rr99
from dual;

嵌套函数

  • 单行函数可以嵌套到任意层。
  • 嵌套函数的计算顺序是从最内层到最外层。
-- 打印出大写的雇员名字从第一位开始的8个字符，并加上'_china'
SQL> select ename,upper(concat(substr(ename,1,8),'_china')) from emp;

ENAME	   UPPER(CONCAT(S
---------- --------------
SCoTT	   SCOTT _CHINA
SMITH	   SMITH_CHINA
ALLEN	   ALLEN_CHINA
WARD	   WARD_CHINA
JONES	   JONES_CHINA
MARTIN	   MARTIN_CHINA
BLAKE	   BLAKE_CHINA
CLARK	   CLARK_CHINA
SCOTT	   SCOTT_CHINA
KING	   KING_CHINA
TURNER	   TURNER_CHINA
ADAMS	   ADAMS_CHINA
JAMES	   JAMES_CHINA
FORD	   FORD_CHINA
MILLER	   MILLER_CHINA

15 rows selected.

-- 打印大写雇员的姓名，去除头尾空白并加上'_china'

SQL> select ename,upper(concat(trim(' ' from ename),'_china')) from emp;

ENAME	   UPPER(CONCAT(TRI
---------- ----------------
SCoTT	   SCOTT_CHINA
SMITH	   SMITH_CHINA
ALLEN	   ALLEN_CHINA
WARD	   WARD_CHINA
JONES	   JONES_CHINA
MARTIN	   MARTIN_CHINA
BLAKE	   BLAKE_CHINA
CLARK	   CLARK_CHINA
SCOTT	   SCOTT_CHINA
KING	   KING_CHINA
TURNER	   TURNER_CHINA
ADAMS	   ADAMS_CHINA
JAMES	   JAMES_CHINA
FORD	   FORD_CHINA
MILLER	   MILLER_CHINA

15 rows selected.
SQL> select to_char(round((sal/7),2),'9G99D99') from emp;

TO_CHAR(
--------
 4,28.57
 1,14.29
 2,28.57
 1,78.57
 4,25.00
 1,78.57
 4,07.14
 3,50.00
 4,28.57
 7,14.29
 2,14.29
 1,57.14
 1,35.71
 4,28.57
 1,85.71

15 rows selected.

SQL> select to_char(round((sal/7),2),'999D99') from emp;

TO_CHAR
-------
 428.57
 114.29
 228.57
 178.57
 425.00
 178.57
 407.14
 350.00
 428.57
 714.29
 214.29
 157.14
 135.71
 428.57
 185.71

15 rows selected.

常规函数

主要是用来修改空值的；可用于任何数据类型

函数名 解释
NVL (expr1, expr2) 1空为2，1不空则1
NVL2 (expr1, expr2, expr3) 1空为3，1不空为2
NULLIF (expr1, expr2) 1和2相等则输出null，否则输出1
COALESCE (expr1, expr2, ..., exprn) 返回枚举中的第一个非空表达式 
-- 计算员工工资和奖金的和

SQL> select ename,sal,comm,sal+comm from emp;

ENAME		  SAL	    COMM   SAL+COMM
---------- ---------- ---------- ----------
SCoTT		 3000	      20       3020
SMITH		  800
ALLEN		 1600	     300       1900
WARD		 1250	     500       1750
JONES		 2975
MARTIN		 1250	    1400       2650
BLAKE		 2850
CLARK		 2450
SCOTT		 3000
KING		 5000
TURNER		 1500	       0       1500
ADAMS		 1100
JAMES		  950
FORD		 3000
MILLER		 1300

15 rows selected.

SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;

ENAME		  SAL	    COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SCoTT		 3000	      20	    3020
SMITH		  800			     800
ALLEN		 1600	     300	    1900
WARD		 1250	     500	    1750
JONES		 2975			    2975
MARTIN		 1250	    1400	    2650
BLAKE		 2850			    2850
CLARK		 2450			    2450
SCOTT		 3000			    3000
KING		 5000			    5000
TURNER		 1500	       0	    1500
ADAMS		 1100			    1100
JAMES		  950			     950
FORD		 3000			    3000
MILLER		 1300			    1300

15 rows selected.

SQL> select nvl2(null,1,2) from dual;

NVL2(NULL,1,2)
--------------
	     2

SQL> select nvl2('a',1,2) from dual;

NVL2('A',1,2)
-------------
	    1

SQL> select nullif(1,1) from dual;

NULLIF(1,1)
-----------


SQL> select nullif(1,2) from dual;

NULLIF(1,2)
-----------
	  1

SQL> select nullif(3,2) from dual;

NULLIF(3,2)
-----------
	  3

-- 有奖金返回奖金，没有奖金返回工资
SQL> select coalesce(comm,sal)from emp;

COALESCE(COMM,SAL)
------------------
		20
	       800
	       300
	       500
	      2975
	      1400
	      2850
	      2450
	      3000
	      5000
		 0
	      1100
	       950
	      3000
	      1300

15 rows selected.

条件表达式

可以在 SQL 语句中使用 IF-THEN-ELSE 逻辑。

条件判断 语法
CASE表达式 case exp when 1 then e1 when 2 then e2 end
DECODE函数 decode(exp,1,e1,2,e2,defaulte3) 
-- 职员工资上涨百分之10，销售工资上涨百分之15，其他人不变

SQL> select ename,job,sal,
  2  case job when 'CLERK' then sal*1.1
  3  when 'SALESMAN' then sal*1.15
  4  else sal end rev_sal
  5  from emp;

ENAME	   JOB		    SAL    REV_SAL
---------- --------- ---------- ----------
SCoTT	   ANALYST	   3000       3000
SMITH	   CLERK	    800        880
ALLEN	   SALESMAN	   1600       1840
WARD	   SALESMAN	   1250     1437.5
JONES	   MANAGER	   2975       2975
MARTIN	   SALESMAN	   1250     1437.5
BLAKE	   MANAGER	   2850       2850
CLARK	   MANAGER	   2450       2450
SCOTT	   ANALYST	   3000       3000
KING	   PRESIDENT	   5000       5000
TURNER	   SALESMAN	   1500       1725
ADAMS	   CLERK	   1100       1210
JAMES	   CLERK	    950       1045
FORD	   ANALYST	   3000       3000
MILLER	   CLERK	   1300       1430

15 rows selected.

SQL> select ename,job,sal,decode(sal,'CLERK',sal*1.1,'SALESMAN',sal*1.1,sal) rev_sal from emp;

ENAME	   JOB		    SAL    REV_SAL
---------- --------- ---------- ----------
SCoTT	   ANALYST	   3000       3000
SMITH	   CLERK	    800        800
ALLEN	   SALESMAN	   1600       1600
WARD	   SALESMAN	   1250       1250
JONES	   MANAGER	   2975       2975
MARTIN	   SALESMAN	   1250       1250
BLAKE	   MANAGER	   2850       2850
CLARK	   MANAGER	   2450       2450
SCOTT	   ANALYST	   3000       3000
KING	   PRESIDENT	   5000       5000
TURNER	   SALESMAN	   1500       1500
ADAMS	   CLERK	   1100       1100
JAMES	   CLERK	    950        950
FORD	   ANALYST	   3000       3000
MILLER	   CLERK	   1300       1300

15 rows selected.

-- 工资低于1000并且job为雇员的员工薪资涨百分之15，其他人不涨
-- 非标准的case when，不能转化为decode()
-- 任何条件满足则break

SQL> select ename,job,sal,case when sal>1000 then sal when job='CLERK' then sal*1.15  else sal end as rev_sal from emp;

ENAME	   JOB		    SAL    REV_SAL
---------- --------- ---------- ----------
SCoTT	   ANALYST	   3000       3000
SMITH	   CLERK	    800        920
ALLEN	   SALESMAN	   1600       1600
WARD	   SALESMAN	   1250       1250
JONES	   MANAGER	   2975       2975
MARTIN	   SALESMAN	   1250       1250
BLAKE	   MANAGER	   2850       2850
CLARK	   MANAGER	   2450       2450
SCOTT	   ANALYST	   3000       3000
KING	   PRESIDENT	   5000       5000
TURNER	   SALESMAN	   1500       1500
ADAMS	   CLERK	   1100       1100
JAMES	   CLERK	    950     1092.5
FORD	   ANALYST	   3000       3000
MILLER	   CLERK	   1300       1300

15 rows selected.

练习以下语句

select ename,sal,comm,sal+nvl(comm,0) from emp;
select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;
select ename,sal,comm,coalesce(comm,sal,0) from emp;

select ename,
       job,
       sal,
case job when 'CLERK' then sal*1.1
         when 'ANALYST' then sal*1.20
else sal end raise_sal
from emp
order by job;

select ename,
       job,
       sal,
decode(job,
      'CLERK',sal*1.1,
      'ANALYST',sal*1.2,
      sal) raise_sal
from emp order by job;

与MySQL的区别

sql mysql oracle
查看用户的表 use dbname;show tables; conn user/password;select * from tab;
限制行数 select * from emp limit 5; select * from emp where rownum < 6;

MySQL数据库名、表名、列名、别名大小写规则

lower_case_table_names = 0 其中

  • 0：区分大小写
  • 1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

  • 数据库名与表名是严格区分大小写的；
  • 表的别名是严格区分大小写的；
  • 列名与列的别名在所有的情况下均是忽略大小写的；
  • 变量名也是严格区分大小写的；
root@SH_MySQL-01 17:02:  [(none)]> select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

root@SH_MySQL-01 17:03:  [(none)]> use test;
Database changed
root@SH_MySQL-01 17:03:  [test]> select * from T1;
ERROR 1146 (42S02): Table 'test.T1' doesn't exist
root@SH_MySQL-01 17:03:  [test]> select * from t1;
+----+------+
| id | num  |
+----+------+
|  1 |  100 |
|  2 |  200 |
+----+------+
2 rows in set (0.00 sec)

MySQL字符串大小写

校对规则以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 。

比如 utf8字符集，

  • utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的校对规则；
  • utf8_general_cs表示区分大小写；
  • utf8_bin表示二进制比较，同样也区分大小写。