Oracle——基本SQL（1）
第 2 章 过滤和排序数据
-
WHERE 子句紧随 FROM 子句
-
查询 last_name 为 'King' 的员工信息
错误1: King 没有加上 单引号
select first_name, last_name from employees where last_name = King错误2: 在单引号中的值区分大小写
select first_name, last_name from employees where last_name = 'king'正确
select first_name, last_name from employees where last_name = 'King' -
查询 1998-4-24 来公司的员工有哪些?
注意: 日期必须要放在单引号中, 且必须是指定的格式
select last_name, hire_date from employees where hire_date = '24-4月-1998' -
查询工资在 5000 -- 10000 之间的员工信息.
1). 使用 AND
select * from employees where salary >= 5000 and salary <= 100002). 使用 BETWEEN .. AND .., 注意: 包含边界!!
select * from employees where salary between 5000 and 10000 -
查询工资等于 6000, 7000, 8000, 9000, 10000 的员工信息
1). 使用 OR
select * from employees where salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary = 100002). 使用 IN
select * from employees where salary in (6000, 7000, 8000, 9000, 10000) -
查询 LAST_NAME 中有 'o' 字符的所有员工信息.
select * from employees where last_name like '%o%' -
查询 LAST_NAME 中第二个字符是 'o' 的所有员工信息.
select * from employees where last_name like '_o%' -
查询 LAST_NAME 中含有 '_' 字符的所有员工信息
1). 准备工作:
update employees set last_name = 'Jones_Tom' where employee_id = 1952). 使用 escape 说明转义字符.
select * from employees where last_name like '%\_%' escape '\' -
查询 COMMISSION_PCT 字段为空的所有员工信息
select last_name, commission_pct from employees where commission_pct is null -
查询 COMMISSION_PCT 字段不为空的所有员工信息
select last_name, commission_pct from employees where commission_pct is not null -
ORDER BY:
1). 若查询中有表达式运算, 一般使用别名排序
2). 按多个列排序: 先按第一列排序, 若第一列中有相同的, 再按第二列排序.
格式： ORDER BY 一级排序列 ASC/DESC,二级排序列 ASC/DESC；
第 3 章 单行函数
-
打印出 "2009年10月14日 9:25:40" 格式的当前系统的日期和时间.
select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS') from dual注意: 使用双引号向日期中添加字符
-
格式化数字: 1234567.89 为 1,234,567.89
select to_char(1234567.89, '999,999,999.99') from dual -
字符串转为数字时
1). 若字符串中没有特殊字符, 可以进行隐式转换: select '1234567.89' + 100 from dual 2). 若字符串中有特殊字符, 例如 '1,234,567.89', 则无法进行隐式转换, 需要使用 to_number() 来完成 select to_number('1,234,567.89', '999,999,999.99') + 100 from dual -
对于把日期作为查询条件的查询, 一般都使用 to_date() 把一个字符串转为日期, 这样可以不必关注日期格式
select last_name, hire_date from employees where hire_date = to_date('1998-5-23', 'yyyy-mm-dd') --where to_char(hire_date,'yyyy-mm-dd') = '1998-5-23'
❤️22. 转换函数: to_char(), to_number(), to_date()
-
查询每个月倒数第 2 天入职的员工的信息.
select last_name, hire_date from employees where hire_date = last_day(hire_date) - 1 -
计算公司员工的年薪
--错误写法: 因为空值计算的结果还是空值 select last_name, salary * 12 * (1 + commission_pct) year_sal from employees --正确写法 select last_name, salary * 12 * (1 + nvl(commission_pct, 0)) year_sal from employees
❤️25. 查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
--使用 case-when-then-else-end
select last_name, department_id, salary, case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
end new_sal
from employees
where department_id in (10, 20, 30)
--使用 decode
select last_name, department_id, salary, decode(department_id, 10, salary * 1.1,
20, salary * 1.2,
30, salary * 1.3
) new_sal
from employees
where department_id in (10, 20, 30)
补充
通用函数
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
NVL 函数
将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
练习1：求公司员工的年薪（含commission_pct)
--求公司员工的年薪（含commission_pct)
select employee_id, last_name, salary*12*(1+nvl(commission_pct,0)) "annual_salary"
from employees
201 Hartstein 156000
202 Fay 72000
203 Mavris 78000
EMPLOYEE_ID LAST_NAME annual_salary
----------- ------------------------- -------------
204 Baer 120000
205 Higgins 144000
206 Gietz 99600
107 rows selected
练习2：输出last_name,department_id，当department_id为null时，显示‘没有部门’。
--输出last_name,department_id，当department_id为null时，显示‘没有部门’
select last_name,nvl(to_char(department_id, '99999'), '没有部门')
from employees
Livingston 80
Grant 没有部门
Johnson 80
Taylor 50
Fleaur 50
Sullivan 50
LAST_NAME NVL(TO_CHAR(DEPARTMENT_ID,'999
------------------------- ------------------------------
Geoni 50
NVL2 函数
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
练习：查询员工的奖金率，若为空，返回0.01,
若不为空，返回实际奖金率+0.015
--练习：查询员工的奖金率，若为空，返回0.01,
--若不为空，返回实际奖金率+0.015
select last_name, commission_pct, nvl2(commission_pct, commission_pct + 0.015, 0.01)
from employees
Taylor 0.20 0.215
Livingston 0.20 0.215
Grant 0.15 0.165
Johnson 0.10 0.115
Taylor 0.01
Fleaur 0.01
Sullivan 0.01
LAST_NAME COMMISSION_PCT NVL2(COMMISSION_PCT,COMMISSION
------------------------- -------------- ------------------------------
条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式
DECODE 函数
CASE 表达式
在需要使用 IF-THEN-ELSE 逻辑时:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
--查询部门号为 10, 20, 30 的员工信息,
--若部门号为 10, 则打印其工资的 1.1 倍,
--20 号部门, 则打印其工资的 1.2 倍,
--30 号部门打印其工资的 1.3 倍数
select employee_id,last_name,first_name,department_id,case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
else salary * 1.3 end new_sal
from employees
where department_id in(10,20,30)
SQL> /
EMPLOYEE_ID LAST_NAME FIRST_NAME DEPARTMENT_ID NEW_SAL
----------- ------------------------- -------------------- ------------- ----------
114 Raphaely Den 30 14300
115 Khoo Alexander 30 4030
116 Baida Shelli 30 3770
117 Tobias Sigal 30 3640
118 Himuro Guy 30 3380
119 Colmenares Karen 30 3250
200 Whalen Jennifer 10 4840
201 Hartstein Michael 20 15600
202 Fay Pat 20 7200
9 rows selected
DECODE 函数
在需要使用 IF-THEN-ELSE 逻辑时:
DECODE(col|expression, search1, result1 ,
[, search2, result2,...,]
[, default])
--查询部门号为 10, 20, 30 的员工信息,
--若部门号为 10, 则打印其工资的 1.1 倍,
--20 号部门, 则打印其工资的 1.2 倍,
--30 号部门打印其工资的 1.3 倍数
select employee_id,last_name,first_name,department_id,decode(department_id, 10, salary * 1.1,
20, salary * 1.2,
salary) new_sal
from employees
where department_id in(10,20,30)
