Oracle 通用表
/*
emp 表 dept 部门表 salgrade 表
empno 员工编号 deptno 部门编号 grade 工资等级
ename 员工姓名 dname 部门名称 losal 工资范围下限
job 职位 loc 工作地点 hisal 工资范围上限
mgr 经理编号
hiredate 入职日期
sal 薪金
comm 奖金
deptno 部门编号
*/
Oracle实例演练
--查询dept表中的部门编号，部门名称，工作地点
SELECT deptno, dname, loc
FROM dept
--查询emp表中的员工编号，员工名称，职位和薪金
SELECT empno, ename, job, sal
FROM emp
--查询salgrade表中的所有数据 *
Select *
from salgrade
--查询表中的数据个数(返回的是表中的记录个数，避免记录数过多)
SELECT COUNT(*)
FROM emp
--查询emp表中的员工工资涨完500，以及这一年的总收入
SELECT sal, sal + 500, sal * 12
FROM emp
--员工转正后，月薪上调20%，请查询出所有员工转正后的月薪工资
SELECT sal, sal*(1 + 0.2)
FROM emp
--员工试用期6个月转正后，月薪上调20%
--请查询出所有员工第一年的年薪所得
SELECT sal, sal * 6 + sal * 6 * (1.2)
FROM emp
--员工试用期后，第一年加上所有奖金所得
--( NULL代表空值，参与运算时，结果为空)
SELECT sal， comm, sal * 6 + sal * 6 * (1.2) + 12 * comm
FROM emp
--解决：
--调用函数NVL
--nvl(参数1，参数2)
--功能：若参数1不为NULL,返回参数1，否则返回参数2
--前提：两个参数类型必须一致
SELECT sal comm, sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0)
FROM emp
--让奖金为空的，显示0
SELECT comm, NVL(comm, 0)
FROM emp
--采用别名方式
--别名方式1 (使用空格)
SELECT sal 工资，comm 奖金，sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) 年薪
FROM emp
--别名方式2 （使用AS）
SELECT sal AS 工资，comm AS 奖金，sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) AS 年薪
FROM emp
--三种特殊情况需要加“”
--1、区分大小写
SELECT sal Sal, comm Comm, sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) total
FROM emp
SELECT sal "Sal", comm "Comm",sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "Total"
FROM emp
--2.包括空格
SELEct sal "工 资", comm "奖 金", sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "年 薪"
FROm emp
--3.包括特殊符号
SELECT sal "工￥资", comm "奖￥金",sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "年薪&"
FROM emp
--连接操作符||
SELECT ename, job, ename||job
FROM emp
--字符类型全部使用单引号''
SELECT ename || '的职位是'|| job
FROM emp
--去重关键字 DISTINCT
SELECT DISTINCT deptno
FROM emp
--对两列完全一样的去重
SELECT DISTINCT ename, deptno
FROM emp
--WHERE 条件查询
--1.数值型作为条件
--查询部分10，员工的编号，姓名，部分编号
SELECT empno, ename,deptno
FROM emp
WHERE deptno = 10
--字符型作为条件
--查询job是CLERK的员工姓名，职位
SELECT ename, job, empno
FROM emp
WHERE job = 'CLERK'
--查询10部门，并且job是CLERK的员工姓名，职位
SELECT ename, job, deptno
FROM emp
WHERE job = 'CLERK' AND deptno = 10
--3.日期型作为条件
--查询入职日期在81年以后的员工信息（DD-MON-RR）即：日-月-年
SELECT *
FROM emp
WHERE hiredate >= '01-1月-1981'
/* number(4) 最大长度为4 （-9999-9999）
varchar2(10)变长字符类型
number(7, 2)最大长度7小数点2位*/
--查询部门不等于10的员工信息
--不等于<> or != 都可以
SELECT *
FROM emp
WHERE deptno <> 10
--BETWEEN AND (在....之间)
--查询工资在2000-3000的员工 姓名以及工资
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 2000 AND 3000
--查询82年1月1日到83年12月31日
SELECT ename, sal, hiredate
FROM emp
WHERE hiredate BETWEEN '01-1月-1982' AND '31-12月-1983'
--IN关键字
--查询部门10-20的员工姓名以及部门编号
SELECT ename, deptno
FROM emp
WHERE deptno IN (10, 20)
--查询工作是CLERK 和SALESMAN的员工姓名
SELECT ename, job
FROM emp
WHERE job IN ('CLERK', 'SALESMAN')
--like模糊查询
--%代表任意多个字符
--_代表一个字符
--查询姓名为以S开头的员工姓名
SELECT ename
FROM emp
WHERE ename LIKE 'S%'
--没有任何返回结果
Select *
from emp
where ename like 'M_%'
and comm = null;
--查询姓名以第二个S开头
SELECT ename
FROM emp
WHERE ename LIKE '_S%'
--查询倒数第二个字符为T的
SELECT ename
FROM emp
WHERE ename LIKE '%T_'
--查询姓名中包括C的
SELECT ename
FROM emp
WHERE ename LIKE '%C%'
--查询S@_中不带@的:S_
SELECT ename
FROM emp
WHERE ename LIKE 'S@_%' ESCAPE '@'
--查询S'
SELECT ename
FROM emp
WHERE ename LIKE 'S''%'
--IS NULL (NOT IN , NOT BETWEEN , IS NOT)
--查询奖金为空
SELECT ename, sal, comm
FROM emp
WHERE comm IS NULL
--AND OR 优先级：AND > OR
SELECT ename, job, sal
FROM emp
WHERE (job = 'SALESMAN' OR job = 'PRESIDENT') AND sal > 1500
SELECT ename, job, sal
FROM emp
WHERE job = 'SALESMAN' OR (job = 'PRESIDENT' AND sal > 1500)
--ORDER BY 排序(默认是升序)
--升序ASC
--降序DESC
--两者的书写和执行都在最后
--工资升序排列
SELECT *
FROM emp
ORDER BY sal ASC
--工资降序排列
SELECT *
FROM emp
ORDER BY sal DESC
--按照列别名排序
SELECT ename, sal 工资
FROM emp
ORDER BY 工资 DESC
--按照列序号排序：对第二列进行排序
SELECT ename, sal
FROM emp
ORDER BY 2 DESC
--进行多列排序：部门升序，工资降序
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno ASC, sal DESC
--查询员工姓名的第三个字母是a的员工姓名
SELECT ename
FROM emp
WHERE ename like '__A%'
--查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ename, hiredate, job
FROM emp
WHERE hiredate >= '07-9月-1982' AND job <> 'SALESMAN'
--查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
SELECT ename, sal, deptno
FROM emp
WHERE sal >= 4500 AND (deptno = 10 OR deptno = 20)
--查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。
SELECT ename, hiredate, job
FROM emp
WHERE hiredate IN('01-1月-1982'， '31-12月-1983') AND (job like 'SALES%' OR job like 'MAN%')
ORDER BY hiredate DESC
--查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。用IN(2000, 3000)的意思是2000 or 3000
SELECT ename, deptno, sal
FROM emp
WHERE (sal BETWEEN 2000 AND 3000) AND deptno <> 10
ORDER BY deptno ASC, sal DESC
--查询员工姓名中第四个字符是R的员工信息
select *
from emp
where ename like '___R%';
法二：
select *
from emp
where instr(ename, 'R', 4, 1) = 4;
法三：
select *
from emp
where substr(ename, 4, 1) = 'R';
--查询员工工资等级大于2的员工姓名，工资，部门名称，工作地点
Select ename, sal, dname, loc
FROM emp e, dept d, salgrade
WHERE e.deptno = d.deptno
AND sal between losal and hisal
AND grade > 2;
-- 在员工表中查询出员工的工资，并计算应交税款：
--如果工资小于1000,税率为0，
--如果工资大于等于1000并小于2000，税率为10％，
--如果工资大于等于2000并小于3000，税率为15％，
--如果工资大于等于3000，税率为20％.
SELECT ename, sal, (case TRUNC(NVL(sal, 0) / 1000, 0)
WHEN 0 THEN 0
WHEN 1 THEN 0.1
WHEN 2 THEN 0.15
ELSE 0.2 end ) 税率
FROM emp;
法二：
SELECT ename 员工姓名, sal 工资, DECODE(TRUNC(NVL(sal, 0) / 1000, 0),
0, 0,
1, 0.1,
2, 0.15,
0.2) 税率
FROM emp;