大数据

去重方法： DISTINCT：SELECT DISTINCT DEPTNO FROM EMP;--显示去重 -- DISTINCT 返回唯一不同的值GROUP BY: SELECT DEPTNO FROM EMP GROUP BY DEPTNO; --分组显示去重 。

ORCL BI大数据(续） 2022年

• 3.28
  • 子查询
    • 当某个条件不确定的时候,就需要用到子查询
      • WHERE 后面 接子查询
        • SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT');
      • FROM 后面接子查询
        • SELECT DEPTNO , JOB , AVG(SAL) AVG_SAL FROM (SELECT * FROM EMP WHERE DEPTNO <> 20 AND JOB <> 'CLERK') GROUP BY DEPTNO,JOB
• 3.29
  • 表的基本操作
    • 字段类型
      • VARCHAR2(X) :字符长度
      • NUMBER(X,Y) :数字类型且总长度不超过X,且小数位有Y位长度
      • NUMBER(X) :数字类型且总长度不超过X的整型
      • DATE :年月日 / 年月日 时分秒
    • 创建表 CREATE TABLE
      • CREATE TABLE T_SCORE(SNO NUMBER(3) , ENAME VARCHAR2(20) , CLASS_NAME VARCHAR2(20) , SCORE NUMBER(10),DATE_TIME DATE) ; 建立表格框架
      • 输入表格内容
        • 写入表数据
          • INSERT INTO T_SCORE VALUES (101,'张三','语文',99, TO_DATE('20200101','YYYYMMDD')); 连续执行就会将VALUES里面的内容连续输入进表格里
        • 查询结果集写入到表
          • INSERT INTO T_SCORE_2 -- INSERT 是 DML 语句 需要 COMMIT 否则会 ROLLBACK SELECT * FROM T_SCORE WHERE SCORE >= 60; COMMIT;
      • Oracle的事务
        • 提交 → COMMIT -- 更新update，插入inser into，删除数据 delete，不提交就只能在当前页面看到 -- 回滚 → ROLLBACK --取消当前操作，提交之后就不能回滚
      • 复制表数据 / 备份的功能
        • 复制表结构, 复制数据
          • CREATE TABLE T_SCORE_1 AS SELECT * FROM T_SCORE WHERE SCORE > 60 -- 1=1条件恒成立 -- 复制 T_SOCRE表的 结构以及数据 给 T_SCORE_1
        • 复制表结构,但是不复制数据
          • CREATE TABLE T_SCORE_2 AS SELECT * FROM T_SCORE WHERE 1=2; 1=2（任意值就是不为1） 条件永不相等
    • 修改数据 UPDATE
      • UPDATE T_SCORE_2 SET SCORE = SCORE -10 -- 设置 SCORE这个字段 等于 原始的SCORE 减去10 WHERE ENAME = '张三' AND CLASS_NAME = '语文'; -- UPDATE 也是DML语句 需要提交 或者 回滚 COMMIT;
      • 同时更新多列
        • UPDATE T_SCORE_2 SET SCORE = 80, DATE_TIME = TO_DATE('2020-01-01','YYYY-MM-DD') WHERE ENAME = '张三'; COMMIT;
    • 删除表数据 DELETE / TRUNCATE /DROP
      • 部分删除：DELETE
        • 删除T_SCORE_2表中,姓名等于张三的数据 DELETE FROM T_SCORE_2 WHERE ENAME = '张三'; COMMIT; -- DELETE 也是 DML 语句
      • 全量删除：TRUNCATE(清空表数据)
        • TRUNCATE TABLE T_SCORE_2; -- TRUNCATE 不需要提交/回滚 是 DDL 语句
      • 删除表：DROP
        • 删除表 T_SCORE_2 DROP TABLE T_SCORE_2; -- DROP 不需要提交/回滚 是 DDL 语句
      • 1.DELETE 是 DML语句,需要提交/回滚,还可以部分删除; 2.TRUNCATE 是 DDL语句,不能回滚,而且只能清空表数据; 3.DROP 是DDL语句,不能回滚,而且是删除表。
      • 数据的增删改查
        • 增：INSERT INTO
        • 删：DELETE/TRUNCATE/DROP
        • 改：UPDATE
        • 查：SELECT
  • Oracle的表结构
    • CONSTRAINT
      • KEY 分：主键跟外键
      • PRIMARY KEY 主键
        • 主键：可以在表中的某一列或者多列(组合唯一) 添加主键,用来限定 数据唯一性 主键的分组取COUNT(1) 都是等于1的。主键唯一且非空。
      • FOREIGN KEY 外键
        • 外键：这个字段值在另外一张表的某个字段范围内。 比如：EMP表的DEPTNO字段在DEPT表的DEPTNO字段范围内。
  • 表结构的修改
    • 修改字段长度
      • 修改 EMP_BANK 表的EMPNO= 1改为11111 INSERT INTO EMP_BANK VALUES('1','ZHANGSAN','数据员','', TO_DATE('20200101','YYYYMMDD'),'2000','','') UPDATE EMP_BANK SET EMPNO = 11111 WHERE EMPNO = 1;
      • ALTER TABLE EMP_BANK MODIFY (EMPNO NUMBER(10)); “EMPNO NUMBER(10)“因为要将EMPNO的1改为11111，一个1占两个字节，10个字节就能写五个1
        • MODIFY --修改（列）
    • 修改字段类型
      • 将EMP表的SAL字段转换成 字符型 ----被修改的字段,对应的值应全为NULL ALTER TABLE EMP_BANK MODIFY (SAL VARCHAR2(10));
    • 表的新增字段
      • 需要在EMP表新增一个字段是更新时间(UPDATE_TIME DATE) ALTER TABLE EMP_BANK ADD UPDATE_TIME DATE;
      • 删除EMP表新增的 UPDATE_TIME 字段 ALTER TABLE EMP_BANK DROP COLUMN UPDATE_TIME;
  • ROWID & ROWNUM（伪列）
    • ROWNUM 返回的是行号常用于分页,不能用大于 或者大于等于
      • SELECT ROWNUM,E.* FROM EMP E;
      • rownum作为条件过滤时，只能是一个范围，并且是 < 或者 <= --比如：查出emp表中的6到10行的数据 SELECT E.*,ROWNUM FROM EMP E WHERE ROWNUM >= 6 ; ---错误的。这样查没有任何结果
        • 查出emp表中的6到10行的数据 SELECT F.*, ROWNUM RN2 FROM (SELECT E.*, ROWNUM RN1 FROM EMP E) F ---再根据这张带了之前的rownum的临时表来查出想要的数据 WHERE RN1 BETWEEN 6 AND 10; ROWNUM 不能在WHERE 的后面
    • ROWID 记录表数据的存储位置,唯一,常用于 删除重复数据
      • SELECT ROWID,E.* FROM EMP E;
      • DELETE FROM EMP_BAK WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_BAK GROUP BY EMPNO); COMMIT;
        • 去重，保持ROWID最大的一组数值，其他的都删除
  • --去重方法： DISTINCT：SELECT DISTINCT DEPTNO FROM EMP;--显示去重 -- DISTINCT 返回唯一不同的值 GROUP BY: SELECT DEPTNO FROM EMP GROUP BY DEPTNO; --分组显示去重 ROWID：
• 3.30
  • Oracle的场景判断
    • DECODE;
      • EMP表需要将英文的工作转换成中文 SALESMAN → 销售 MANAGER → 经理 PRESIDENT → 老板 其它的 → 职员 SELECT JOB, DECODE(JOB,'SALESMAN','销售','MANAGER','经理','PRESIDENT','老板','职员') AS 中文工种 FROM EMP;
        • 语法 DECODE(字段,值1,返回值1,值2,返回值2,返回值3)
          • -- 条件映射 --DECODE 只能用等值判断
        • AS 别名 --可加AS也可以空格表示
        • -- 判断 JOB 等于 SALESMAN 则返回销售,等于 MANAGER 则返回经理,等于PRESIDENT 则返回老板,否则返回职员
      • 判断员工姓名是A开头的则是管理层,B开头的是基层员工其它的置空 SELECT ENAME, DECODE(SUBSTR(ENAME,1,1),'A','管理层','B','基层员工') AS 员工级别 FROM EMP;
        • -- 最后的 ELSE 返回场景可以不写,默认返回为空
    • CASE WHEN END;
      • 输出EMP表的部门名称 10 → 销售部 20 → 经理部 30 → 市场部 40 → 运营部 SELECT DEPTNO, CASE WHEN DEPTNO = 10 THEN '销售部' WHEN DEPTNO = 20 THEN '经理部' WHEN DEPTNO = 30 THEN '市场部' WHEN DEPTNO = 40 THEN '运营部' END AS 中文部门 FROM EMP;
        • CASE -- 事件 ---- 定义一个事件来判断 WHEN 条件 -- 当 .. 时候 THEN 输出X --- 则 WHEN 条件 THEN 输出Y .. END AS 别名 -- 结束这个事件
      • 判断员工姓名是A开头的则是管理层,其它的是基层员工 SELECT ENAME, CASE WHEN SUBSTR(ENAME,1,1) = 'A' THEN '管理层' ELSE '基层员工' --- 其它情况 END AS 员工级别 FROM EMP;
    • 1.DECODE(字段,值1,返回值1,值2,返回值2[,返回值3]) 默认返回空; 2.CASE WHEN 场景1 (判断条件) THEN 返回值1 WHEN 场景2 THEN 返回值2 ELSE 返回值3 END AS 别名 3.DECODE只能用于等值判断 CASE WHEN 可以用多条件 以及 不等值判断 ;
  • Oracle常用函数
    • 数学函数
      • ABS 取绝对值
      • ROUND 四舍五入
      • TRUNC 截断
      • FLOOR 向下取整
      • CEIL 向上取整
      • MOD 取余数
      • TRUNC 取商
      • POWER 取幂
    • 日期函数
    • 字符函数