oracl sql 笔记【未整理】

SELECT ename,sal,deptno
FROM emp
WHERE DEPTNO = 10;


# ASC 升序 DESC 降序 默认为升序，NULL值为最大
SELECT ename,sal,deptno, hiredate, comm
FROM emp
ORDER BY deptno DESC, sal;


#字符形式 大小写敏感
# 字符函数 ：
#  大小写相关：LOWER,UPPER, INTTCAP
#  其他： TRIM, LTRIM, RTRIM, LPAD, RAD, INSTR, SUBSTR, LENGTH, CONCAT, REPLACE
#


SELECT lower('Hello'), upper('Hello'), initcap('hello')
FROM dual;


# 消除字符
SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'), rtrim('HelloHH', 'H')
FROM dual;


# 补齐字符
SELECT lpad(1000, 7, '*'), rpad(1000,7,'*')
FROM dual;


#查找字符位置
SELECT instr('hello tom','tom')
FROM dual;


#截取字符
SELECT substr('hello',3,2)
FROM dual;


#连接字符，求字符长度
SELECT concat('ton','ny'),length('tom')
FROM dual;


#替换字符
SELECT replace('hellh','h','mm')
FROM dual;


#数值函数
# ROUND 四舍五入  TRUNC 去尾  MOD 求模（求余数）


#四舍五入 小数点后保留几位，小数点前保留几位，不保留
SELECT round(123.456, 2), round(145.456, -2), round(123.456, 0), round(123.456)
FROM dual;


#不进行四舍五入,去尾
SELECT trunc(123.456,2), trunc(123.456, -2),trunc(123.456,0), trunc(123.456)
from dual;


#求模
SELECT mod(12,7)
FROM dual;


# 日期函数
# SYSDATE 获取系统当前日期
# MONTHS_BETWEEN 获取两个日期之间的相差月数
# ADD_MONTH 获取几个月后的日期
# LAST_DAY 获取本月最后一天的日期
# EXTRACT 从日期中获取某一部分值


SELECT sysdate + 2/24
FROM dual;


SELECT add_months(sysdate, 3)
FROM dual;


SELECT ename, trunc(months_between(sysdate, hiredate)) da
FROM emp;


SELECT last_day(sysdate)
FROM dual;


SELECT extract(YEAR FROM sysdate)
FROM dual;


#转换函数 
# TO_CHAR 将日期或数值转换为制定格式的字符
# TO_DATE 将指定格式字符转换为日期
# TO_NUMBER 将指定格式字符转换为数值


SELECT to_char(sysdate,'YYYY-MM-DD') date1
FROM dual;


SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MI:SS') date1
FROM dual;


#年 year，yyyy，yy
#月 month，mon，mm
#日 DD
#星期几 DAY（monday），DY（mon），D（1）
#时间 HH24:MI:SS  HH:MI:SS


SELECT to_char(123.45,'L9,999,999.00')
FROM dual;
#L:本地的货币符号, $:直接显示, 9:表示数值, 0:同9，强制占位


SELECT to_date('1980-01-01', 'YYYY-MM-DD')
FROM dual;


SELECT to_number('$1,222.34','$9,999.99')
FROM dual;


# 空值转换函数 要求类型相同
SELECT ename,comm,nvl(comm,0)
FROM emp;


# 为空的话用 sal 代替，不为空的话用 0 代替
SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;


# 两个表达式值相同为空
SELECT nullif('a','a')
FROM dual;


# 条件分支函数 DECODE , CASE
SELECT ename,deptno,sal,
    decode(deptno,
            10, sal + 100,
            20, sal + 200,
            30, sal + 300,
            sal) salary
FROM emp;


SELECT ename, deptno, sal,
      CASE deptno
      WHEN 10 THEN sal + 100
      WHEN 20 THEN sal + 200
      WHEN 30 THEN sal + 300
      ELSE deptno END salery
FROM emp;


SELECT ename,deptno,sal,
      CASE
      WHEN deptno = 10 THEN sal + 100
      WHEN deptno = 20 THEN sal + 200
      WHEN deptno = 30 THEN sal + 300
      ELSE deptno END salery
FROM emp;


#聚合函数与数据分组-常用聚合函数及使用
# MAX 求最大值  可以应用在字符、数值和日期类型数据上
# MIN 求最小值  可以应用在字符、数值和日期类型数据上
# SUM 计算和  可以应用在数值类型数据上
# AVG 计算平均值  可以应用在数值类型数据上
# COUNT 统计行数  统计表达式值不为空的行数


# 求最大至于最小值
SELECT MAX(sal),min(sal)
FROM emp;


# 求行数
SELECT COUNT(comm)
FROM emp;


# 验证行数
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY comm DESC;


# 数据分组
# GROUP BY : 根据指定的列进行分组统计
# HAVING : 限定显示分组后的数据


SELECT deptno, max(sal), min(sal)
from emp
group by deptno
having min(sal)>1000;


# 数据分组 要求
# 在select子句中出现的非分组列，必须出现在 GROUP BY 子句中
select deptno,job,avg(sal)
from emp
group by deptno,job;


select deptno,avg(sal)
FROM emp
group by deptno;


#多表查询
#基于同名列进行连接
select ename,deptno,dname
from emp NATURAL join dept;


select ename,deptno,dname
from emp join dept using(deptno);


# 自连接：将一张表作为两张表看待进行的连接
# FROM table t1 JOIN table t2 ON t1.col=t2.col


select * from emp;


select w.ename, n.ename
from emp w join emp n on w.mgr = n.empno


# 外连接：使用空行匹配非匹配行的连接
SELECT e.ename,d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;


# 连接更多表
# FROM table1 JOIN table2 ON t1.col = t2.col
              JOIN table3 ON t1.col = t3.col
              ...
SELECT ename,dept.deptno,dname,grade
FROM emp JOIN dept ON emp.deptno = dept.deptno
         JOIN salgrade ON sal BETWEEN losal AND hisal;
         
# 子查询
  # 单行子查询：子查询为一行的查询
  # 查询 工资大于 ename 为 ADAMS 的员工信息
  SELECT * 
  FROM emp
  WHERE sal > (SELECT sal
               FROM emp
               WHERE ename = 'ADAMS');
  # 查询 工资大于平均工资的员工的信息
  SELECT * 
  FROM emp
  WHERE sal > (SELECT avg(sal) FROM emp);
  
  # 多行子查询 ： 子查询为多行的查询
  # ANY ALL IN
  
  SELECT * FROM emp
  WHERE sal > ANY (SELECT AVG(sal) FROM emp GROUP BY deptno);
  
  SELECT AVG(sal) FROM emp GROUP BY deptno
  SELECT ename from emp;
  
  select * from emp where ename in ('WARD','ALLEN','SCOTT');
  


  # 多列子查询
  # 非相关子查询
  # 相关子查询 : 子查询中需要使用主查询中行的查询
  #  流程：
  #    1.首先从主查询中获取一行
  #    2.利用主查询中获取行执行子查询
  #    3.执行主查询
  
  # 相关子查询
  SELECT ename,sal,deptno
  FROM emp e
  WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno = e.deptno)
  ORDER BY deptno DESC,sal;
  
  # 使用EXISTS进行相关子查询：在子查询中只判断存在性的查询
  # SELECT ...
    FROM table1 outer
    WHERE [NOT] EXISTS (SELECT ... 
                        FROM table2
                        WHERE col = outer.col);
                        
  SELECT *
  FROM emp e
  WHERE EXISTS (SELECT 0 FROM emp WHERE mgr = e.empno);