DB笔记(Oracle)
DB笔记(Oracle)
一．Oracle的安装，服务的启动，数据库的建立，用户的建立（略过）。
二．Select语句：
（一）、单行函数：
1. 表达式和null计算，结果为null，即，不作显示（也就是数据失踪了！）。所以一般用函数NVL（列名，替换值），如NVL（sal，0）。
2. 字符串拼接：|| 例如：select ID||’---’||uname from emp;
3. 排序 和过滤（order by 和 where）：
a) select* from emp order by sal asc,comm desc; 此表达式为按薪水升序排，薪水相同的按奖金倒序排。
b) select* from emp order by ( sal + NVL(comm,0) ) desc;此表达式是：按照薪水和奖金之和来排序，注意：如果不用NVL直接用comm，则会丢失一些值。
c) select* from emp where uname > ‘CBA’; 字符串的过滤, 这个具体还没搞懂。
d) select* from emp where sal between 100 and 200;
4. 条件选择：and，or，in(a1,a2,a3)。注意：其中in(a1,a2,a3)表示选择a1,a2,a3三个中的一个，而不是选择三个范围中的一个。
5. Not关键字：not in(a,b,c); is not null; 等等。
6. Like关键字：通配符有两个，如下
a) %: 任意字符 出现 任意次数。
b) _: 任意字符 出现 一次。
注意：查询通配符需要转义，如下：select * from emp where ename like‘%\%%’ escape ‘\’; select * from emp where ename like ‘_H@_%’ escape ‘@’;
7. 事件处理函数： upper(), Lower(), substr(ename,1,4), length(), trim(), ltrim(), rtrim()。
8. 时间格式函数：to_char(sysdate,’yyyy-mm-dd’); to_date(string,’yyyy-mm-dd’)。
两个日期间的天数 select floor( sysdate – to_date(‘2014/12/27’ , ‘yyyy/mm/dd’) );
9. rownum: 行号，表的原本数据排序，只能用于<或<=运算符，不能用>或>=运算符，如果要取后几行数据，需要一个where子查询先倒叙排列。
10. 正则表达式：sql语句支持正则表达式，例子如下：
select* from emp where regexp_like(列名,’正则表达式’);
（二）、组函数：
1. avg() ,min(), max(), sum() 略过不讲。
2．count()：计数函数。返回查询结果的行数，经常配合distinct，去除重复行。
例如： select count(eno) from emp whereeno>200; 或者 select count(distinct eno) from emp;
3. group by : 分组函数。
a). 查询单位不再以行为单位，所有操作均以组为单位.
b). 何为一组？值相同则为一组.
c). having为过滤分组，
c). group by子句 须在where子句之后，order by子句之前。
d). select eno from emp group byeno; ----eno相同的分为一组，查询每组的eno
select depno,sal(sal) from dept group by depno; ----以部门号分组，查询。
select max( avg(sal) ) from dept group by deptno; ----先按部门编号分组，查出每组平均值的最大值。
select * from dept group by sal,comm; ----工资和奖金相同的分为一组，查询*。
三．Insert，update，delete语句（略过）。
四．其他操作：
1. 索引index：可以给某个列增加index，提高其select的效率。创建方法超级简单：create index index_name on tablename(cols); 然后不用管，我们索引时oracle会自动调用index来检索。删除方法也超简单：drop index index_name; 注意： index缺点也不少，Index不适合：数据唯一性不好的列+增删改频繁的列。所以不到必要，不用建立。
2. 视图view：使用方法和表的一模一样，好处：sql重用+安全
create view v$_vname as (select子句); drop view V$_vname;
3. 序列sequence：用法：创建，删除，引用。略过。
五．DB设计范式： （重要）
1. DB范式目前有六种，1NF，2NF，3NF，BCNF，4NF，5NF(完美范式)。依次越优化。
2. 企业级开发，最低满足3NF。
3. 满足范式越高，DB冗余越低，DB越优化。
4. 详解：
a) 1NF：表的每个列都是原子的。
错误示范 -->
b) 2NF：实体属性完全依赖主关键字。即不能只依赖主键的一部分。
错误示范 à （略过）
常见错误 à 联合主键的表。 某些属性依赖主键的一部分。
解决方案 à 拆分主键拆分表，外键关联。
c) 3NF:
错误示范 -->
常见错误 à 表中属性A依赖于B，B依赖于C。数据量大时会大量冗余。
解决方案 à 拆表，外键关联。
d) BCNF：允许出现有主键的一部分被主键另一部分或者其他部分决定。即联合主键相互依赖。
错误示范 à仓库表storehouse（仓库ID，管理员ID，物品ID，数量），一个仓库一个管理员，一个仓库多个物品，则依赖关系如下：
出现了仓库ID和管理员ID相互依赖，符合3NF但不符合BCNF。
解决方案 -->拆分。
编程两个一对多，这种结构也叫：实体表—映射表关联结构。
e) 注意：映射表中两个外键需形成联合主键。
备注：
1. 别名可以用中文，但要加双引号；
2. oracle的某些客户端，如PLsql等，有sql window和command window窗口，用来跑不同的语句，例如desc User; 指令只能在command window中跑；
3. SQL语句优化：Select * from emp; *是通配符，性能较低，程序中尽量不要出现*，最好能具体到列名。
4. SQL不区分大小写，但是script执行的时候会统一转化为大写。 所以：大写执行效率高但不利于阅读，小写脚本执行效率高但不利于阅读，所以一般关键字大写，另外需要注意：建议程序里关键字也大写，有利于增加效率。
5. SQL语句的优化： 检索结果少and 检索结果多， 检索结果多 and 检索结果少。因为sql语句是顺序执行。
6. SQL语句优化：select * from emp where eno=100 or eno=200 oreno=300; 此语句和select * from emp where eno in (100,200,300); 等价，但是用in的语法清晰+效率高，因为in函数内部做了优化。
7.SQL语句优化：用like关键字和通配符来检索数据万不得已再用，它的操作慢的不得了。
8. 组函数的嵌套最多两层，例如 max( avg(sal) )等，不能出现max( min( avg(sal) ) ).
9. Oracle内部执行顺序：where行级过滤 à group by分组 à having组级过滤 à order by排序。所以：group by子句 须在where子句之后，order by子句之前，即where…group by…order by…
10. Transaction事务机制要点：只操作insert，update，delete行级语句。如果系统正常关闭，则事务自动commit；如果执行create，drop等表级操作，事务自动commit；如果非正常关闭如断电等，事务自动rollback。