Oracle通过scott用户中的emp练习单表操作

scott用户中的表很适合练习查询操作
要想操作scott用户,先将这个用户密码设置了,并解锁

-- 1. 暴力破解某个用户的密码
ALTER USER scott IDENTIFIED BY orcl;
-- 2. 解锁用户
ALTER USER scott ACCOUNT UNLOCK;

单表查询的话我们主要操纵雇员表emp

一.基础查询

-- 单行注释方式

/*
|| 多行注释1
|| 为啥前面加两个竖线？
|| 因为这样好看。。。
*/

/*
多行注释2
不加竖线的效果
是不是不好看？因为是斜体，所以比较乱，其实tap键也可以。。。
*/

-- 任务1 : 基础查询结构
-- SELECT * 或 列名 FROM 表名;

-- 查询 EMP 表所有列数据 
-- ( SQL语句要大写 , 这样可以提高执行性能 )
-- ( SQL语句要以分号结尾 )
SELECT * FROM EMP;

-- 查询指定列的数据 ( 查询了员工表的编号,姓名,职位三列 )
SELECT EMPNO,ENAME,JOB FROM EMP;

-- 给列起个别名 ( 笑 :-D )
SELECT EMPNO 编号,ENAME 姓名,JOB 职位 FROM EMP;
-- 除了列可以起别名,表也可以起别名( 笑 :-D )
SELECT E.EMPNO 编号,E.ENAME 姓名 FROM EMP E;

-- 消除重复的数据 ( 初级操作 )
-- 实际意义 : 想看一个字段有几种数据 , 除了 group by, 呵呵 ...
SELECT DISTINCT JOB 职位种类 FROM EMP;

-- 四则运算 ( 加减乘除啥的都可以 )
-- 计算员工年薪
SELECT E.ENAME 姓名,E.SAL*12 年薪 FROM EMP E;

-- 空值 NULL 的理解 需要注意
-- 比如福利 , 计算年福利
SELECT COMM*12 FROM EMP;
-- 会发现没有值的地方 并不是 0 或者 空格 
-- 是程序中我们理解的 null 值 不管你怎么计算 都是 NULL

-- 连接符 || 刚才我么见过的两个竖线 当然此竖线非彼竖线
-- 正 + 常 + 理 + 解  但是 万万没想到 ...
SELECT '员工的姓名是:'||ENAME FROM EMP;

二.条件查询和排序

-- 知识点 1 : 查询某列数据是否有 NULL 值
-- 为空 ( IS NULL )
SELECT * FROM EMP E WHERE E.COMM IS NULL;
-- 不为空 ( IS NOT NULL )
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL;

-- 查询工资大于1500 并且有奖金的员工
-- 注意 AND 连接两个查询条件 两个条件必须同时满足
SELECT * FROM EMP E WHERE E.SAL > 1500 AND E.COMM IS NOT NULL;

-- 查询工资大于1500 或者 没有奖金的员工
-- 注意 OR 连接两个查询条件 两个查询条件满足一个即可
SELECT * FROM EMP E WHERE E.SAL > 1500 OR E.COMM IS NULL;

-- 查询工资小于1500 并且 没有奖金的员工
SELECT * FROM EMP E WHERE NOT(E.SAL > 1500) AND E.COMM IS NULL;
-- 其实上面只是想说下 NOT 的用法 其实 可以变成这样
SELECT * FROM EMP E WHERE E.SAL < 1500 AND E.COMM IS NULL;

-- 知识点 2:范围限制
-- 基本工资大于 1500 但是小于 3000
SELECT * FROM EMP E WHERE E.SAL > 1500 AND E.SAL < 3000;

-- 如果包含1500 和 3000 呢 ?
SELECT * FROM EMP E WHERE E.SAL >= 1500 AND E.SAL <= 3000;
-- 是不是太麻烦呢 ? 简化一下吧 使用 between ... and 
SELECT * FROM EMP E WHERE E.SAL BETWEEN 1500 AND 3000;

-- between ... and 还可以查询时间日期
-- 查询1981年入职的员工
SELECT * FROM EMP E WHERE E.HIREDATE BETWEEN '1-1月-1981' AND '31-12月-1981';
-- 时间为什么要这么写 ? 这是默认格式啦 ,能改么? 当然可以 !
SELECT * FROM EMP E WHERE E.HIREDATE BETWEEN DATE'1981-01-01' AND DATE'1981-12-31';
-- 是不是很神奇 ? 
-- 屁啦 Sql Server MySQL 连 DATE 都不用加 ! 哎 !

-- 查询条件中 值 是区分大小写的 ! 
-- 查询叫 scott 的员工
SELECT * FROM EMP E WHERE E.ENAME = 'scott';
-- 查不到 为啥 ? 因为 scott 是区分大小写的 !
SELECT * FROM EMP E WHERE E.ENAME = 'SCOTT';

-- 关键字 IN 的用法
-- 查询员工编号为 7369 7499 7521 三个员工的姓名
SELECT * FROM EMP E WHERE E.EMPNO = 7369 OR E.EMPNO = 7499 OR E.EMPNO = 7521;
-- 是不是有点麻烦 ? 同一列的 不同值而已嘛 改一下
SELECT * FROM EMP E WHERE E.EMPNO IN (7369,7499,7521);
-- 简单吧 但是要注意用法 同一列 不同值 可以用 IN 圈查
-- 不同值中间要以逗号隔开
-- 例如 , 查询姓名为 SMITH WARD SCOTT 三个员工信息
SELECT * FROM EMP E WHERE E.ENAME IN ('SMITH','WARD','SCOTT');

-- 模糊查询
-- 记不清了 只能根据大概查询 
-- 以 X 开头 ( 例如 找个人叫 张XX 的 )
-- 以 X 结尾 ( 例如 找个人叫 XX明 的 )
-- 中间包含X 的 ( 例如 找下名字中包含 文 的 )
-- LIKE 关键字 % 匹配任意字符 _匹配一个字符
-- 查询员工姓名第二个字母包含 M 的员工
SELECT * FROM EMP E WHERE E.ENAME LIKE '_M%';

-- LIKE 中没有指定关键字 表示查询全部
SELECT * FROM EMP E WHERE E.ENAME LIKE '%%';
-- 恕我直言 , 这么写简直是脑残写法 不管是一个 % 还是两个 %

-- 查询名字中带 M 的员工信息
SELECT * FROM EMP E WHERE E.ENAME LIKE '%M%';
-- 经验 : LIKE 使用不好很有可能导致查询全部数据找你所需要的
-- 这样查询效率会很低哒 比如 LIKE '%X' LIKE '%X%' 都是不可以
-- 哒 这样不行 所以一定要避免使用这样的样式 非要用 ? 不存在哒
-- 如果真的非要用 滚去了解索引去 !

-- Oracle 非等于 可以使用 <> 或者 !=
-- 员工信息不是7369的员工信息
SELECT * FROM EMP E WHERE E.EMPNO <> 7369;
-- 或者
SELECT * FROM EMP E WHERE E.EMPNO != 7369;


-- Oracle 对数据的排序可以使用 ORDER BY 的形式
-- 根据员工的工资高低展示数据 ( 从高到低展示 )
SELECT * FROM EMP E ORDER BY E.SAL DESC;
-- 从低到高呢 ?
SELECT * FROM EMP E ORDER BY E.SAL ASC;
-- ASC 从低到高排序是默认的 你可以不写哈
SELECT * FROM EMP E ORDER BY E.SAL;

-- 那可以一起排序多个字段么 ? 当然可以呀
-- 根据工资和入职时间排序
SELECT * FROM EMP E ORDER BY E.SAL DESC,E.HIREDATE DESC;
-- 根据查询结果可知 先按工资排序 如果工资相同再按时间排序

-- 排序中的 NULL 值问题
-- 比如按照奖金排序
SELECT * FROM EMP E ORDER BY E.COMM DESC;
-- 会出现一堆 NULL 排在前面 什么鬼 ? 怎么办呢 ?
-- 可以使用 NULLS FIRST 和 NULLS LAST 
-- NULL 值放前面 一般不会这么干吧 ?
SELECT * FROM EMP E ORDER BY E.COMM DESC NULLS FIRST;
-- NULL 值放在后面 这样可以哒
SELECT * FROM EMP E ORDER BY E.COMM DESC NULLS LAST;
-- 但是还是一堆 NULL 哦 能去掉不 ? 完全没问题
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL ORDER BY E.COMM DESC;
-- 呵呵呵呵 还有个 0 ? 不存在的
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL AND E.COMM > 0 ORDER BY E.COMM DESC;

三.单行函数

-- 知识点 1 : 字符串函数
-- 字符串全转成大写
SELECT UPPER('hello,Oracle') FROM DUAL;
-- 逗号也大写了 你能看出来么? 屁啦 骗人哒
-- 字符串全转成小写
SELECT LOWER('HELLO,ORACLE') FROM DUAL;
-- 逗号也小..... 啪 .. 已打死

-- 字符串拼接
SELECT CONCAT('Hello','Oracle') FROM DUAL;
-- 拼接多个怎么办
SELECT 'Hello'||','||'Oracle' FROM DUAL;
-- 神 + 奇 + 不 + ? + 
-- 但 + 是 + 我 + 还 + 是 + 喜 + 欢 + 加 + 号 (可惜用不了)

-- 字符串截取
SELECT SUBSTR('Hello,Oracle!',1,3) FROM DUAL;
-- 索引 1 和 0 都一样 Oracle 还真是照顾我们呢 !
SELECT SUBSTR('Hello,Oracle!',0,3) FROM DUAL;

-- 获取字符串的长度
SELECT LENGTH('Hello,Oracle') FROM DUAL;
-- 是不是很简单 但是 SQL Server 是 len() 函数 笑着面对 挺萌的 !

-- 字符替换
SELECT REPLACE('hello,Oracle','l','x') FROM DUAL;



-- 知识点 2 : 数值函数
-- 可以四舍五入的 , 很实用呀 
SELECT ROUND(46.578,2) FROM DUAL;
-- 直接干掉 多少也不要的 土豪的做法
SELECT TRUNC(46.578,2) FROM DUAL;
-- 取余数的 , 很实用呀 , 分页存储过程用这个可以
SELECT MOD(5,2) FROM DUAL;


-- 知识点 3 : 日期函数
-- 计算员工入职的周数
SELECT E.ENAME,ROUND((SYSDATE - HIREDATE)/7) FROM EMP E;

-- 获得两个时间段中的月数
-- 计算员工入职的月数
SELECT E.ENAME,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) FROM EMP E;

-- 获得几个月后的日期
-- 获得一个月后的日期
SELECT ADD_MONTHS(SYSDATE,1) FROM EMP E; 


-- 知识点 4 : 转换函数
-- 日期转字符串
-- 年月日 时分秒
SELECT TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mm:ss') FROM DUAL;

-- 获得星期几
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

-- 数字转字符串
-- 9 属于数字占位符 一定要长 省得位数不够 
SELECT TO_CHAR(10000.00,'L999,999,999,999.00') FROM DUAL;

-- 字符串转时间
SELECT TO_DATE('2017-12-12','yyyy-MM-dd') FROM DUAL;

-- 字符串转数字
SELECT TO_NUMBER('100.00') FROM DUAL;


-- 知识点 5: 通用函数
-- 处理 NULL
-- NVL 如果目标为 NULL 取第二个数值
SELECT NVL(E.COMM,0) FROM EMP E;
-- NVL2 如果目标不为 NULL 取第一个 为 NULL 取第二个
SELECT NVL2(E.COMM,E.COMM,0) FROM EMP E;
-- NULLIF 如果两个值相等 返回一个 NULL 我的天 什么鬼 ?
SELECT NULLIF(E.COMM,E.COMM) FROM EMP E;
-- COALESCE 配置多个表达式 返回一个为空的 如果都为空 则返回空
SELECT COALESCE(NULL,1,NULL,2) FROM DUAL;
-- 计算年薪 基本工资*12 + 奖金
SELECT E.ENAME 员工名称,E.SAL*12+NVL(E.COMM,0) 年薪 FROM EMP E;

-- 知识点6 : 条件表达式
-- 计算员工的工资等级
-- 条件匹配版
SELECT E.ENAME 姓名,E.SAL 薪资,
CASE 
WHEN E.SAL >= 500 AND E.SAL < 1500 THEN 1 
WHEN E.SAL >= 1500 AND E.SAL < 2000 THEN 2 
WHEN E.SAL >= 2000 AND E.SAL < 2500 THEN 3 
WHEN E.SAL >= 3000 AND E.SAL < 3500 THEN 4 
ELSE 5 
END 薪资等级 
FROM EMP E;
-- CASE WHEN THEN END 结构 跟 Java switch 一样
-- 结果匹配版
SELECT E.ENAME 姓名,E.SAL 薪资,
DECODE(
ROUND(E.SAL/1000),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45
) 手续费
FROM EMP E;

四.多行函数

-- 多行函数

-- 计数器 : 统计个数
SELECT COUNT(EMPNO) FROM EMP;
-- COUNT(*) 包含所有列再计数
-- COUNT(1) 包含 N 多行1再计数 相当于占位符
-- COUNT(主键) 包含 N 多行主键ID 再计数 因为主键上有索引,查询是
-- 最快的

-- 最小值 : 最低工资
SELECT MIN(SAL) FROM EMP;

-- 最大值 : 最高工资
SELECT MAX(SAL) FROM EMP;

-- 平均值 : 平均工资
SELECT AVG(SAL) FROM EMP;
--有可能除不开 所以四舍五入
SELECT ROUND(AVG(SAL)) FROM EMP E;

-- 求和 : 看看一个月薪资的支出
SELECT SUM(SAL) FROM EMP;

-- 分组查询 : 看看每个部门有多少个人
SELECT DEPTNO,COUNT(EMPNO) FROM EMP E GROUP BY DEPTNO;
-- 一旦分组 只能查询分组字段和聚合函数 不能查其他字段
-- 不信 ?  不信你试试 select * 

-- 过滤分组 : 查询平均工资大于 2000的部门编号和人员个数
SELECT E.DEPTNO,COUNT(E.EMPNO) FROM EMP E GROUP BY E.DEPTNO HAVING AVG(E.SAL) > 2000;

-- HAVING 最好不用 因为分组都已经很累啦 你居然还要过滤
-- 是不是*  是不是 *