逻辑运算符以及单行函数

来源：互联网发布：sas数据分析大赛编辑：程序博客网时间：2024/05/22 07:40

逻辑运算符：
AND：逻辑与 SELECT empno,ename,job,sal FROM emp WHERE sal>=1100 AND job='CLERK';
OR：逻辑或

NOT：逻辑非(后边只能跟一个参数)

特殊not: is not null

逻辑非还可以与BETWEEN...AND, LIKE,
IS NULL一起使用

...where deptno not in(10,20)
...where sal not between 3000 and 5000
...where ename not like 'D%'
...where mgr is not null
逻辑表达式的结果为 TRUE， FALSE，NULL

逻辑运算符

• 运算符的优先级

–括号’()’优先于其他操作符。

优先级运算分类运算符举例

1 算术运算符 *, \, +, -

2 连接运算符 ||

3 比较运算符 =, <>, <, >, <=, >=

4 特殊比较运算符 BETWEEN..AND.. ,IN,LIKE,IS NULL

5 逻辑非 NOT

6 逻辑与 AND

7 逻辑或 OR

练习4

• 1.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的
员工姓名、职位、工资

SELECT ename,job,sal FROM emp
WHERE sal>2000 AND job='MANAGER' OR job='SALESMAN'

• 2.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名
、职位、工资。
SELECT ename,job,sal FROM emp
WHERE sal>2000 AND (job='MANAGER' OR job='SALESMAN')

• 3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名
、部门、工资。
SELECT ename,deptno,sal FROM emp
WHERE deptno IN(10,20)AND sal BETWEEN 3000 AND 5000

• 4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、
入职日期、职位。

SELECT ename, hiredate,job FROM emp
WHERE hiredate BETWEEN '01-1月-81' AND '31-12月-81' AND job NOT LIKE('SALES%')

• 5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名
包含A的员工姓名、职位、部门编号。

SELECT ename,job, deptno FROM emp
WHERE job IN('SALESMAN','MANAGER')AND deptno IN(10,20)
AND ename LIKE'%A%'

---------------------------------------------------------------------------------
ORDER BY子句

• 使用ORDER BY子句能对查询结果集进行排序,语法结构如下：

SELECT [DISTINCT] { * | | [ ][,...]}
列名表达式别名
FROM 表名
[WHERE 条件]
[ORDER BY { | | |
列名表达式列别名列序号

• 其中：
–可以按照列名、表达式、列别名、结果集的列序号排序
–ASC: 升序，默认值
DESC: 降序

–ORDER BY 子句必须写在SELECT语句的最后

排序规则(以升序为例)

–数字升序排列小值在前，大值在后。即按照数字大小顺序由

小到大排列。

–日期升序排列相对较早的日期在前，较晚的日期在后。例

如：’01-SEP-06’在’01-SEP-07’前。

–字符升序排列按照字母由小到大的顺序排列。即由A-Z排列；

中文升序按照字典顺序排列。

空值在升序排列中排在最后，在降序排列中排在最开始。

还可以按列别名排序，
也可以多列参与排序
ORDER BY子句

• 多列参与排序

SQL> SELECT ename, deptno, sal
2 FROM emp
3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL
---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.

• 参与排序的多列都可以指定升序或者降序

• ORDER BY子句中可以写没在SELECT列表中出现的列

ORDER BY子句

• 按结果集列序号排序
–ORDER BY子句后列名可以用数字来代替，这个数字是SELECT
语句后列的顺序号。

SQL> SELECT ename, deptno, sal
2 FROM emp
3 ORDER BY 2, 3 DESC;

ENAME DEPTNO SAL
---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.

练习5

• 1.查询部门在20或30的员工姓名，部门编号，并
按照工资升序排序。
SELECT ename,deptno FROM emp
WHERE deptno IN(20,30)
ORDER BY sal ASC 升序

• 2.查询工资在2000-3000之间，部门不在10号的
员工姓名，部门编号，工资，并按照部门升序，
工资降序排序。

SELECT ename ,deptno,sal FROM emp
WHERE sal BETWEEN 2000 AND 3000 AND deptno NOT IN(10)
ORDER BY deptno,sal DESC
或者
SELECT ename ,deptno,sal FROM emp
WHERE sal BETWEEN 2000 AND 3000 AND deptno<>10
ORDER BY deptno,sal DESC

• 3.查询入职日期在82年至83年之间，职位以
SALES或者MAN开头的员工姓名，入职日期，职位
，并按照入职日期降序排序。

SELECT ename,hiredate,job FROM emp
WHERE hiredate BETWEEN '01-1月-82'AND'31-12月-83'AND
(job LIKE 'SALES%' OR job LIKE 'MAN%')
ORDER BY hiredate DESC

注意当初写的时候(job LIKE 'SALES%' OR job LIKE 'MAN%')
写成了 job LIKE 'SALES%' OR 'MAN%' 这样是不对的
-------------------------------------------------------------------

SQL函数概述

• 单行函数语法

–语法:

函数名[(参数1，参数2,…)]

–其中的参数可以是以下之一：

•变量

•列名

•表达式

SQL函数概述

• 单行函数特征

–单行函数对单行操作

–每行返回一个结果

–有可能返回值与原参数数据类型不一致

–单行函数可以写在SELECT、WHERE、ORDER BY子句中

–有些函数没有参数，有些函数包括一个或多个参数

–函数可以嵌套

字符函数
• 字符函数
–字符函数：主要指参数类型是字符型，不同函数返回值可能
是字符型或数值型。

字符函数

大小写转换字符处理

CONCAT
LOWER SUBSTR
UPPER LENGTH
INITCAP INSTR
LPAD
RPAD
REPLACE
TRIM

想查找姓名中包含A的
SELECT * FROM emp WHERE ename LIKE '%'||upper('a')||'%'

或者是
SELECT *FROM emp WHERE lower(ename) LIKE'%a%' 讲姓名都转换成小写再like 后边的
--------------------------------------

字符函数

• 字符处理函数

–CONCAT(column1|expression1,column2|expression2)
•连接两个值 ,等同于||

只能有两个参数，若想连多了，只能用函数的嵌套

–SUBSTR (column|expression,n1[,n2]) 下标是从0开始的

• 返回第一个参数中，从第n1位开始，长度为n2的子串。
–如果n2省略,取第n1位开始的所有字符。
–如果n1是负值，表示从第一个参数的后面第abs(n1) （abs表示绝对值）位开始
向右取长度为n2的子串。（通常用来判断后几位，或者以什么什么结尾）

–LENGTH(column | expression) （返回的数值型，即长度，所以说字符型函数不一定返回字符）
• 取字符长度

练习1

• 1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）
SELECT ename,initcap(ename),length(ename) 姓名长度 FROM emp
WHERE ename LIKE 'J%'OR ename LIKE 'A%'OR ename LIKE'M%'
ORDER BY ename ASC
或者

SELECT ename,initcap(ename),length(ename) 姓名长度 FROM emp
WHERE substr(ename,1,1) IN ('J','A','M')
ORDER BY ename ASC

字符函数

• 字符处理函数

–INSTR(s1,s2,[,n1],[n2])
• 返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值
为1

–LPAD(s1,n1,s2)
• 返回s1被s2从左面填充到n1长度后的字符串。

–RPAD(s1,n1,s2)
• 返回s1被s2从右面填充到n1长度后的字符串。

–TRIM:去除字符串头部或尾部（头尾）的字符
格式:TRIM(leading | trailing | both trim_character From
trim_source)

–REPLACE(s1,s2,s3)
•把s1中的s2用s3替换。

•举例字符处理函数

函数结果

CONCAT('Good', 'String') GoodString
SUBSTR('String',1,3) Str
LENGTH('String') 6
INSTR('String', 'r') 3
LPAD(sal,10,'*') ******5000
RPAD(sal,10,'*') 5000******
TRIM('S' FROM 'SSMITH') MITH
REPLACE 'abc','b','d') adc

练习2

• 1.查询员工姓名中中包含大写或小写字母A的员工姓名。

SELECT ename
FROM emp

WHERE instr(ename,'A',1,1)>0 OR instr(ename,'a')>0 （最好是这个）或者

SELECT ename
FROM emp
WHERE upper(ename)LIKE'%A%'

• 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中
包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函
数，不能使用like进行判断)

SELECT ename,length(ename)
FROM emp
WHERE deptno IN(10,20)AND hiredate>'01-5月-81'AND instr(ename,'A')>0
• 3.查询每个职工的编号,姓名，工资
–要求将查询到的数据按照一定的格式合并成一个字符串.
–前10位：编号,不足部分用*填充,左对齐
–中间10位：姓名，不足部分用*填充,左对齐

–后10位：工资，不足部分用*填充,右对齐

SELECT lpad(empno,10,'*')||lpad(ename,10,'*')||rpad(sal,10,'*')
FROM emp

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

数值函数

• 数值函数

–ROUND:将列或表达式所表示的数值四舍五入到小数点后的第n位。
格式: ROUND(column| expression, n)
ROUND(45.926, 2) -> 45.93

–TRUNC:将列或表达式所表示的数值截取到小数点后的第n位。
格式：TRUNC(column| expression, n)
TRUNC(45.926, 2) -> 45.92

–MOD:取m除以n后得到的余数
格式：MOD(m,n)
MOD(1600, 300) ->100

• DUAL：是一张虚表，不能保存任何数据，只有一个字段，一行记录。当我们不希望从任何表中读取数据，同时又想利用SQL引擎中的计算表达式的能力帮我们运算时，就可
以使用DUAL表。

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- -------------- -----------------
45.92 46 50

练习3

• 1.写一个查询，分别计算100.456 四舍五入到小
数点后第2位，第1位，整数位的值。
SELECT round(100.456,2),round(100.456,1),round(100.456)
FROM dual

• 2.写一个查询，分别计算100.456 从小数点后第
2位，第1位，整数位截断的值。
SELECT trunc(100.456,2),trunc(100.456,1),trunc(100.456)
FROM dual

----------------------------------------------------------------------------
日期的处理

• Oracle是以一种内部的数值形式存储日期的，
即：世纪、年、月、日、小时、分、秒

• 默认的日期形式是： DD-MON-RR

日期的运算

• 日期类型数学运算

–日期类型可以加减数字，功能是在该日期上加减对应的天数。如：’10-AUG-06’+15结果是’25-AUG-06’

–日期类型之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：’10-AUG-06’- ‘4-AUG-06’结果四舍五入后是6天

–如果需要对一个日期进行加减相应小时操作，可以使用n/24来实现

–SYSDATE：返回系统当前日期

–MONTHS_BETWEEN：返回两个日期类型数据之间间隔的自然月数

–MONTHS_BETWEEN 函数演示——查询所有员工服务的月数。

SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months

FROM emp

ORDER BY months;

–ADD_MONTHS：返回指定日期加上相应的月数后的日期

–ADD_MONTHS 函数演示——查询82年后入职的员工转正日期，

按照3个月试用期考虑

SELECT ename, sal, hiredate, ADD_MONTHS(hiredate,3)

FROM emp

WHERE hiredate>'01-1月 -82';

–NEXT_DAY：返回某一日期的下一个指定日期

–NEXT_DAY 函数演示——返回在02-2月-06之后的下一个周

一是什么日期。

SELECT NEXT_DAY('02-2 月 -06',' 星期一（或者是其他的星期几） ') NEXT_DAY

FROM DUAL;

–LAST_DAY：返回指定日期当月最后一天的日期

–LAST_DAY 函数演示——返回06年2月2日所在月份的最后一

天。

SELECT LAST_DAY('02-2月 -06') "LAST DAY"

FROM DUAL;

–ROUND(date[,‘fmt’])将date按照fmt指定的格式进行四舍五入，

fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最

近的天。

格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS

–ROUND函数演示——查询81年入职的员工姓名，入职日

期按月四舍五入的日期。

SELECT empno, hiredate,

ROUND(hiredate, 'MONTH')

FROM emp

WHERE SUBSTR(hiredate,-2,2)=‘81';

–TRUNC(date[,‘fmt’])将date按照fmt指定的格式进行截断，fmt为

可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的

天。

–TRUNC 函数演示——查询81年入职的员工姓名，入职日

期按月截断的日期。

SELECT empno, hiredate, TRUNC(hiredate, 'MONTH')

FROM emp

WHERE SUBSTR(hiredate,-2,2)=‘81';

–EXTRACT：返回日期类型数据中的年份、月份或者日。

EXTRACT ([YEAR] [MONTH][DAY]

FROM [日期类型表达式])

–部门编号是10的部门中所有员工入职月份。

SELECT ename, hiredate,

EXTRACT (MONTH FROM HIREDATE) MONTH

FROM emp

WHERE deptno= 10;

练习5

• 1.查询服务器当前时间
SELECT SYSDATE FROM dual

• 2.查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用 months_between,extract)

SELECT ename,hiredate,extract(MONTH FROM hiredate)MONTH months_between(‘01-1月-01’，hiredate) （一定不要忘记日期要用引号括起来）
FROM emp
WHERE deptno IN(10,20)

• 3.如果员工试用期6个月，查询职位不是MANAGER的员工姓

名，入职日期，转正日期，入职日期后的第一个星期一,

入职当月的最后一天日期。（提示：使用

add_months,next_day,last_day)

SELECT ename, hiredate,ADD_MONTHS(hiredate,6),next_day(hiredate,'星期一')next_day,
last_day(hiredate)last_day
FROM emp
WHERE job NOT IN('MANAGER')

或者

SELECT ename,hiredate,add_months(hiredate,6),next_day(hiredate,'星期一'),
last_day(hiredate)
FROM emp
WHERE job<>'MANAGER'

转换函数：

显式数据类型转换

–通常是在字符类型、日期类型、数值类型之间进行显式转

换。主要有3个显式转换函数：

•TO_CHAR

•TO_NUMBER

•TO_DATE

显式数据类型转换

DATE

TO_CHAR TO_DATE

CHARACTER

TO_CHAR TO_NUMBER

NUMBER

TO_CHAR 用于日期型

TO_CHAR(date, 'fmt')

• 日期格式模型：

− 必须用单引号引起来并且是大小写敏感的

− 可以包含任何有效的日期元素

− 使用逗号将日期型数据与日期型格式模型分隔开

SELECT ename,TO_CHAR(hiredate,'MM/YY')

month_hired

FROM emp;

• 日期格式模型的元素

YYYY 完整的年份数字表示

YEAR 年份的英文表示

MM 用两位数字来表示月份

MONTH 月份的全名

DAY 星期几

DY 用个英文字符缩写来表示星期几

•用时间元素格式化日期的时间部分

HH24:MI:SS AM 15:45:32 PM

•通过使用双引号可以添加字符串

DD "of" MONTH 12 of OCTOBER

TO_CHAR 用于数值型

TO_CHAR(number, 'fmt')

• 通过在TO_CHAR中使用以下形式可以把数值型

数据转化成变长的字符串：

9 一位数字

0 显示前导零

$ 显示美元符号

L 显示本地货币符号

. 显示小数点

, 显示千位符

TO_CHAR 用于数值型

SQL> SELECT TO_CHAR(sal,'$99,999') SALARY

2 FROM emp

3 WHERE ename = 'SCOTT';

SALARY

--------

$3,000

注意：进行数字类型到字符型转换时，格式中的

宽度一定要超过实际列宽度，否则会显示为###。

TO_NUMBER 和 TO_DATE 函数

• 使用TO_NUMBER将一个字符串转换成数值型数据

TO_NUMBER(char[, 'fmt'])

注意：要转换的char类型数据必须是由数字组成的字符

串，格式码中相应的格式必须要和char中的格式匹配。

• 使用TO_DATE将一个字符串转换成日期型数据。

TO_DATE(char[, 'fmt'])

注意：要转换的char类型数据必须是可以转换成日期的

字符，格式码的格式必须要和char中的格式匹配。

练习6

• 1.显示服务器系统当前时间，格式为2007-10-12

17:11:11(提示：使用to_char函数)

SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
FROM dual

• 2.显示ename、hiredate 和雇员开始工作日是星期几，列

标签DAY(提示：使用to_char函数)

SELECT ename,hiredate,to_char(hiredate,'day') AS DAY FROM emp

• 3.查询员工姓名，工资，格式化的工资（￥999,999.99）

(提示：使用to_char函数)

SELECT ename,sal, to_char(sal,'L999,999.99') FROM emp

• 4.把字符串2015-3月-18 13:13:13 转换成日期格式，并计

算和系统当前时间间隔多少天。 (提示：使用to_date函数

SELECT to_date('2015-3月-18 13:13:13','yyyy-mon-dd hh24:mi:ss'),
round(SYSDATE-to_date('2015-3月-18 13:13:13','yyyy-mon-dd hh24:mi:ss'))
FROM dual

)

通用函数

• 通用函数

–与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操

作。主要包括以下函数：

•NVL （要求两个参数的类型必须一致）

–语法:NVL (expr1,expr2)

–如果expr1不是null,返回expr1,否则返回expr2

•NVL2

–语法:NVL2(expr1,expr2,expr3)

–如果expr1不是null,返回expr2,否则返回expr3

•NULLIF

–语法:NULLIF(expr1,expr2)

–比较两个表达式,如果相等，返回null,否则，返回第

一个表达式

•COALESCE

–COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL

函数的扩展。COALESCE函数的功能是返回第一个不为空的

参数，参数个数不受限制。

SELECT ename, COALESCE(comm, 0) comm , deptno

FROM emp;

–条件处理函数：

•CASE表达式

–CASE语法

CASE expr

WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

– CASE示例

SELECT ename, deptno,

(CASE deptno

WHEN 10 THEN '销售部'

WHEN 20 THEN '技术部'

WHEN 30 THEN '管理部'

ELSE '无' END)

deptname （这是case语句的列别名）

FROM emp;

注意：when后边只能跟具体数值，若不想则前边case后边的参数不能有，这样可以是表达式例如：

课后习题7
SELECT sal,
sal*(CASE WHEN sal<1000 THEN 0
WHEN sal<2000 THEN 0.1
WHEN sal<3000 THEN 0.15
ELSE 0.2 END) tax

FROM emp

或者这样

SELECT ename,sal,
(CASE trunc(sal/1000)
WHEN 0 THEN 0*sal
WHEN 1 THEN 0.1*sal
WHEN 2 THEN 0.15*sal
ELSE 0.2*sal END) tax
FROM emp

•DECODE

–DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…，][,

缺省值])

SELECT ename, deptno,

decode(deptno,

10,'销售部',

20,'技术部',

30 , '管理部',

' 无 ') deptname

FROM emp;

函数的嵌套

• 单行函数可以嵌套于任何层。

• 嵌套的函数是从最里层向最外层的顺序计算的。

F3(F2(F1(col,arg1),arg2),arg3)

Step 1 = Result 1

Step 2 = Result 2

Step 3 = Result 3

课后作业

1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

SELECT months_between(SYSDATE,'01-1月-00') months,round((SYSDATE-to_date
('01-1月-00','dd-mon-rr'))/7) weeks
FROM dual

2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函

数)。

SELECT ename
FROM emp
WHERE ename LIKE('__A%') （这不是函数）

或者

SELECT ename
FROM emp
WHERE substr(ename,3,1)='A'

或者

SELECT ename
FROM emp
WHERE instr(ename,'A',3,1)=3

3.使用trim函数将字符串‘hello’、‘ Hello ’、

‘bllb’、‘ hello ’分别处理得到下列字符串ello、

Hello、ll、hello。

SELECT TRIM('h'FROM'hello'),TRIM(' Hello '),TRIM('b'FROM'bllb')，TRIM(' hello ')
FROM dual

4.将员工工资按如下格式显示：123,234.00 RMB 。

SELECT to_char(sal,'999,999.00l')FROM emp

5.查询员工的姓名及其经理编号，要求对于没有经理的显示

“No Manager”字符串。

SELECT ename,
nvl(to_char(mgr),'no manager')FROM emp
WHERE mgr IS NULL

6.将员工的参加工作日期按如下格式显示：月份/年份。

SELECT to_char(hiredate,'mm/rr')FROM emp

7.在员工表中查询出员工的工资，并计算应交税款：如果工

资小于1000,税率为0，如果工资大于等于1000并小于2000，

税率为10％，如果工资大于等于2000并小于3000，税率为

15％，如果工资大于等于3000，税率为20％。

SELECT sal,
sal*(CASE WHEN sal<1000 THEN 0
WHEN sal<2000 THEN 0.1
WHEN sal<3000 THEN 0.15
ELSE 0.2 END) tax

FROM emp

或者这样

SELECT ename,sal,
(CASE trunc(sal/1000)
WHEN 0 THEN 0*sal
WHEN 1 THEN 0.1*sal
WHEN 2 THEN 0.15*sal
ELSE 0.2*sal END) tax
FROM emp

8.创建一个查询显示所有雇员的 ename和 sal。格式化sal

为 15 个字符长度，用 $ 左填充，列标签 SALARY。

SELECT ename,lpad(sal,15,'$')salary FROM emp

0 0