一、    今天完成的事情

回顾Oracle，整理了笔记。

第一部分

1.sql优化原则(sql的执行计划)

      1.尽量使用列名查询，不用*

      2.sql语句where的解析顺序：右 -> 左

            where condition1 and condition2

            where condition2 and condition1

            这两句在oracle中结果一样，但含义不一样

      3.尽量使用where，而不是having

      4.多表查询优于子查询

      5.尽量使用UNION ALL不用UNION

      6.尽量不适用集合运算(随着参与运算的集合增多，效率降低)

2.sql中的null值

      1.包含null的表达式结果都为null

            解决使用NVL(null,0)函数

      2.判断是null用is不用=，不是null用is not不用!=

3.列名起别名

      1.empno as "员工号"

      2.empno "员工号"

      3.empno 员工号

      2与1没有区别；3与1有区别，当别名中包含特殊字符(或关键字)或数字时需要双引号

4.distinct 作用于其后面所有的列

5.连接符 concat ||

6.dual表 伪表

7.字符串

      日期和字符只能在单引号中出现

8.sql语句与sql*plus命令

      sql                   sql*plus

      一种语言          一种环境

      ANSI标准        Oracle特性

      关键字不缩写   可以缩写

9.spool d:\filename.txt

  spool off

10.字符串大小写敏感 -> 区分大小写

   日期格式敏感 -> 文字与格式字符串匹配

            默认的日期格式：dd-MM-yy

            修改日期格式：alter session set NLS_DATE_FORMAT='DD-MON-RR';

11.模糊查询

      查询含有'_'的字段(需要转义)

      like '%\_%' escape '\'

12.排序

      可通过列号排序，从1开始

      多个列排序：

      order by作用于后面所有列，遵循的排序规则依次按照列的顺序。

      asc和desc只作用一列

      order by desc asc, sal desc

      空值的排序：nulls first，nulls last

      select * from emp order by comm nulls last;

12.单行函数

      字符函数

      LOWER(▲)

      UPPER(▲)

      INITCAP:首字母大写

      SUBSTR(String, index [, number_of_char]):取子串

      length(string):字符数

      lengthb(string):字节数

      lpad:左填充

      rpad:右填充

      trim:去掉前后指定的字符

      replace:替换

      数字函数

      ROUND(number, 2):四舍五入(▲)

      TRUNC(number, 2):截断

      MOD:求余

      日期函数

      to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'):格式化时间(▲)

      MONTHS_BETWEEN:两个日期相差的月数

      NEXT_DAY:

      转换函数

      转换有显式转换和隐式转换

      TO_CHAR(▲)

      TO_NUMBER

      TO_DATE

      通用函数

      NVL(expr1, expr2):expr1为null时返回expr2

      DECODE函数：

      DECODE (列名, 条件, 执行,

                       条件, 执行,

                             默认执行) 自定义该列名

13.多行函数 分组函数(自动滤空)

      AVG

      COUNT

      对于滤空可通过NVL()防止被过滤

      group by a, b; 先按照a分组，再按照b分组

      having：过滤分组(有点像where)

      eg: having avg(sal) > 1500

      where和having的区别：where不能使用多行函数

      group by语句的增强——rollup

14.多表查询

      外连接  使条件不成立的记录仍然包含在结果中

      左外连接，等号左边的表记录被包含

            where a.deptno=b.deptno(+)

      右外连接，等号右边的表记录被包含

            where a.deptno(+)=b.deptno

      自连接

      通过表的别名，将同一张表是为多表

      from emp a, emp b where a.mgr=b.empno

      不适合操作大表(笛卡儿积)

      层次查询

      connect by...

      start with...

第二部分

1.子查询(内查询)

      1.不可以在group by后面使用子查询

      2.加括号

      3.强调from后面的子查询

      4.可以在主查询的where select having from 等关键字后面使用子查询

      5.一般不在子查询排序：但top-n分析问题中，必须对子查询排序

      6.一般先执行子查询，再执行主查询；但相关子查询例外

      7.单行子查询只能使用单行操作符(>/</=)，多行子查询只能使用多行操作符(in/any/all)

      8.子查询中的null

2.重要练习

      1)找到员工表中工资最高的前三名

      补充：rownum 行号

      SELECT rownum, empno, ename, sal

      FROM (

      SELECT * FROM emp

      ORDER BY sal DESC)

      WHERE rownum <= 3;

      2)找到员工表中薪水大于本部门平均薪水的员工

      SELECT empno, ename, sal, avgsal

      FROM emp,

      (SELECT deptno,avg(sal) avgsal

      FROM emp GROUP BY deptno) temp

      WHERE emp.deptno = temp.deptno

      AND emp.sal > temp.avgsal;

      3)统计1988年和所有的入职的员工人数★

      SELECT COUNT(*) total,

      SUM(DECODE(TO_CHAR(hiredate, 'yyyy'),1988,1,0)) "1988"

      FROM emp;

3.关于rownum

      1.rownum永远按照默认的顺序生成

      2.rownum只能使用< <=，不能使用> >=

4.相关子查询：

      将主查询中的值作为参数传递给子查询

5.集合运算★

      UNION/UNION ALL 并集

      INTERSECT 交集

      MINUS 差集

6.UNION与UNION ALL

      UNION交集部分取一次，UNION ALL交集部分取两次。(没交集两者一样)

      两者等同时，尽量使用UNION ALL，因为union=union all + distinct

7.关于集合运算

      1.参与运算的各个集合必须列数相同，且类型一致

      2.采用第一个集合作为最后的表头

      3.order by永远在最后

      4.括号可改变执行顺序

8.SQL类型

      1.DML(data manipulation language 数据操作语言):insert,update,delete, select

      2.DDL(data definition language 数据定义语言):create table,alter table,drop table,truncate table,create/drop view,sequence,index,synonym

      3.DCL(data control language 数据控制语言):grant(授权) revoke(撤权)

9.地址符 &

10.海量插入数据

      1.数据泵(PLSQL程序: dbms_datapump)

      2.SQL*Loader工具

      3.外部表

11.什么叫数据的完整性？

      建立在一张表上的约束(主键/外键/非空/唯一)

12.delete和truncate的区别

      1.delete逐条删除；truncate先摧毁表，再重建

      2.★delete是DML；truncate是DDL

            ->DML可回滚，DDL不能回滚

      3.delete不会释放空间；truncate会

      4.delete可闪回(flashback),truncate不能

      5.delete会产生碎片，truncate不会

13.去掉碎片

      1.alter table 表名 move;

      2.数据的导出和导入

            exp      imp

            expdp  impdp

14.undo数据(还原数据)

15.Oracle中事务的标志

      1.起始标志：事务中第一条DML语句

      2.结束标志：提交 显式 commit

                              隐式 正常退出exit, DDL, DCL

            如DDL：truncate = 清空表+commit

                       回滚 显式 rollback

                              隐式 非正常退出

/*************五个数据库对象：表/视图/序列/索引/同义词*************/

16.建表

      使用As sub_query，将建表和子查询结合

17.管理表

      ALTER TABLE：

      追加新的列；修改现有的列；删除一个列

      如：

      ALTER TABLE ... ADD

      ALTER TABLE ... MODIFY

      ALTER TABLE ... DROP

      ALTER TABLE ... RENAME COLUMN ... TO ...

      DROP TABLE:

      删除表，不能回滚，可以闪回

      进入了oracle的回收站 recyclebin

      RENAME ... TO ...:

      改变表、视图、序列或同义词的名称

      TRUNCATE TABLE:

      删除表，不能回滚

18.约束

      约束是表一级的限制

      约束类型：

      NOT NULL

      UNIQUE

      PRIMARY KEY

      FOREIGN KEY

      CHECK(自定义约束，定义每一行记录必须满足的条件)

      格式：CONSTRAINT 约束名 前四种约束/CHECK (条件)

      约束名规范：列名_约束类型

19.视图

      是虚表

      视图建立在已有表的基础上，这些表称为基表

      向视图提供数据内容的语句为SELECT语句，可以认为视图是在存储SELECT语句的结果。

      简化复杂查询

      注意视图主要用来查询！不用来CUD

      记住：视图不能提高性能！！！

20.视图操作

      创建视图

      CREATE [OR REPLACE] VIEW ... [(字段,...)]

      AS 子查询

      [WITH CHECK OPTION [CONSTRAINT ...]]

      [WITH READ ONLY [CONSTRAINT]]

21.rowid

22.序列

      序列：可供多个用户用来产生唯一数值的数据库对象

      将序列值装入内存可以提高访问效率

      相当mysql中的auto_increment

      序列 -> 数组 -> 内存

                 [1,2,...,20][21,22,...,40]

23.定义序列

      CREATE SEQUENCE sequence

            [INCREMENT BY n]     --步长

            [START WITH n]          --起始

            [MAXVALUE n]      --最大值

            [MINVALUE n]        --最小值

            [{CYCLE | NOCYCLE}] --循环

            [{CACHE n | NOCACHE}]--默认20

24.NEXTVAL和CURRVAL伪列

      NEXTVAL应在CURRVAL之前指定，两者应同时有效。

      因为初始指针不是指向第一个值。

       [1,2,...,20]

      ↑

      SELECT mysequence.nextval FROM DUAL;--此时指针才指向第一个值

      简单的说，通过nextval移动指针

25.序列出现裂缝：

      回滚/系统异常关闭/多表公用

26.索引

      查询数据时:

      没有索引:先进行全表扫描(TABLE ACCESS FULL)

      有索引:存放rowid

      Oracle中索引的类型

      1.BalanceTree索引(默认)

      2.位图索引(矩阵)

27.同义词synonym

      起别名，提高安全性。

      CREATE [PUBLIC] SYNONYM 别名 FOR object; --默认是私有别名

二、    明天计划的事情

三、    遇到的问题

四、    收获

1）昨天完成了面试项目，今天完成了项目说明文档后就把任务提交了。

2）回顾了Oracle，整理了一下笔记。

3）回顾了计网的知识。