模糊查询
Like
- - 表示有一个字符
- % 表示0个或者多个字符
- 转义 escape
Escape的使用
查找人名里面有_的职员。
SELECT * FROM emp WHERElower(ename) LIKE'%_%'
SELECT * FROM emp WHERElower(ename) LIKE'%\_%'ESCAPE'\'
这个转义字符是自己定义的,你可以使用自己想用的字符。
Select from [where] [group by [having]] [order by]
SELECT ename,deptno, round(AVG(nvl(sal, 0)), 2) || '$' salary
FROM emp
WHERE mgr isnotnull
GROUP BY deptno, ename
HAVINGAVG(nvl(sal, 0)) > 1500
ORDER BY deptno
Oracle里面的判空 is null | is not null
Oracle 里面的日期函数
伪列 sysdate、locatimestamep、current_timestamp、current_date、dbtimezone、SESSIONTIMEZONE、interval
函数 add_months、mouths_between、next_day、last_day、new_time、extract
例:以年月日的方式显示所有员工的服务年限.
SELECT e.ename,
trunc(months_between(SYSDATE, e.hiredate) / 12) years,
trunc(MOD(months_between(SYSDATE, e.hiredate), 12)) months,
trunc(SYSDATE -
add_months(e.hiredate,
trunc(months_between(SYSDATE, e.hiredate) / 12) * 12 +
trunc(MOD(months_between(SYSDATE, e.hiredate), 12)))) days
FROM emp e
ORDER BY years, months, days
Decode 和 case when then的使用
SELECT emp.*,
salgrade.*,
decode(grade,
1,
'1级',
2,
'2级',
3,
'3级',
4,
'4级',
5,
'5级',
'未知等级') 等级
FROM emp, salgrade
WHERE nvl(emp.sal, 0) + nvl(emp.comm, 0) BETWEEN salgrade.losal AND
salgrade.hisal
SELECT e.*,
sg.*,
CASE sg.grade
WHEN1THEN
'LOW'
WHEN5THEN
'HIGH'
ELSE
'MEDIUM'
ENDAS grade
FROM emp e
INNER JOIN salgrade sg
ON nvl(sal, 0) + nvl(comm, 0) BETWEEN sg.losal AND sg.hisal
再来看一个题目,要求如图所示,按照下面给出的格式输出。
SELECT PK,
(CASE
WHEN RC1 > 0THEN
rc1
ELSE
0
END) AS收入,
(CASE
WHEN RC1 < 0THEN
ABS(rc1)
ELSE
0
END) AS支出
FROM Table1
SELECTNAME,
SUM((CASE score
WHEN N'胜'THEN
1
ELSE
0
END)) AS胜,
SUM((CASE score
WHEN N'负'THEN
1
ELSE
0
END)) AS负
FROM table2
GROUP BYNAME