关于Oracle的一些基础知识以及注意事项

一.oracle基础 1.1 DDL(Data Definition Language) 数据定义语言 create drop,desc（注意，此操作只能在PL/SQL Developer的命令窗户执行，在sql窗口不能执行）table tablename 1.2 DML(Data Manipulation Language) 数据操作语言 insert, delete,update,… 1.3 TCL(Transaction Control Language) 事务控制语言 begin transaction commit rollback 1.4 DQL(Data Query Language) 数据查询语言 select 1.5 DCL(Data Control Language) 数据控制语言 grant revoke 2.oracle 的一些内部查询语句 2.1 了解Oracle的字符编码 select userenv(‘language‘) from dual; 3. 关于varchar2的中文问题 create table foo(c1 varchar2(2)); insert into foo values(‘你好‘); SQL 错误: ORA-12899: 列 "SCOTT"."FOO"."C1" 的值太大 (实际值: 4, 最大值: 2) 解决此问题的办法是使用nvarchar2 4.字符串的连接 select fname || ‘.‘ || lname from foo_6 用concat函数也可以连接: concat(fname,lname) 5. 一组常用函数 trim 去前后空格, ltrim去左边空格, rtrim去右边空格 lpad 左边填充字符至指定长度 lpad(str, 填充至的长度,[填充的字符,默认空格]) rpad 右边填充字符至指定长度 lower 变成小写 upper 变成大写 initcap 首字母大写 length 取字符串长度 substr 取子字符串 instr 查找字符串 last_day(date)所在月的最后一天 months_between(date1, date2) date1-date2相差的月数 least(date1, date2) date1和date2较早的那一个 greastest(date1,date2) date1和date2较近的那一个 round(date) 对时分秒进行舍入 trunc(date) 把时分秒去掉 select to_char (trunc(sysdate), ‘yyyy-mm-dd hh24:mi:ss‘) from dual; extract extract(year from 日期值) extract(month from 日期值) extract(day from 日期值) extract(hour from 日期值) extract(minute from 日期值) extract(second from 日期值) select extract(year from sysdate) from dual; 6.空值函数 （oracle特有函数） nvl(arg1,arg2) 如果arg1为null,返回arg2 如果arg1不为null,返回arg1 select nvl(c1,‘hahaha‘) from goo_1; nvl2(arg1,arg2,arg3) 如果arg1为null,返回arg3 如果arg1不为null,返回arg2 select nvl2(c1,‘hehehe‘,‘hahaha‘) from goo_1; 7. 主键,数据表中行的唯一性 1) 创建主键 主键也是列(多列-联合主键), 一般没有业务含义(不能发生变更), 唯一标识数据表中的某一行 必须有主键 类型最好是number constraint 约束名 primary key(主键列) create table stu ( stu_id number(11), stu_no number(8), stu_name varchar2(50), constraint stu_pk primary key(stu_id) ) 2)主键约束 主键不能为null 主键不能重复 insert into stu values(100,2013001,‘abc‘); insert into stu values(null,2013002,‘bcd‘); insert into stu values(100,2013003,‘cde‘); 8. drop truncate delete drop table stu; 删除表,释放空间 truncate table stu; 保留表的结构,删除数据,释放空间,不能恢复,速度快 delete from stu 删除数据,可以恢复,速度慢 9.根据其他的表创建新表 create table emp_1 as select id, last_name, first_name, salary from s_emp; 注意：创建的表可以复制结构和数据但是不复制约束 10. decode函数（oracle特有函数） decode(value if1 then1 if2 then2...else) 如果value=if1 返回 then1 否则 如果 value=if2 返回 then2 ... 默认的返回值是else 其他特有函数还包括to_char、to_date、to_number、trim、length、substr（下标从1开始）等 11.使用union,union all, intersect（交集）, minus（差集） 注意： union 和 union all 的区别 在数据库中，union和union all 关键字都是将两个结果集合并成一个，但这两者在使用和效率上来说都有所不同。 union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。 实际上大部分应用不会产生重复的记录，最常见的是过程表和历史表union，例如 Select * from pro_table union select * from his_table 这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量很大的话可能会导致用磁盘进行排序。 而union all 只是简单的将两个结果合并后就返回，这样，如果返回的两个结果集中有重复的数据，那么返回的结果集中就会包含重复的数据。 从效率上说，union all 要比union 快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就是用union all。 总结:union 去重且排序 union all 含重不排序 12.2. connect by 和 start with(Oralce SQL) 遍历树，level相当于树的等级， 提供一个专门的伪列level select level, empno, ename,mgr from emp start with ename=‘KING‘ connect by prior empno=mgr，其中，connect by prior empno=mgr表示本条记录上一条记录的empno是本条记录的mgr 13.高级分组函数 rollup 函数多一行"小计" 对于分组的列为null 对于聚集函数为 求"小计"的结果 select job, sum(sal) from emp group by job select job, sum(sal), round(avg(sal)),count(empno), max(sal) from emp group by rollup(job); select dname,job,sum(sal) from emp inner join dept using(deptno) group by cube(dname, job) order by dname, job对具体某一个dname也求一下聚合函数的“小计” grouping 函数 grouping set 函数 select grouping(dname), grouping(job), dname, job, sum(sal) from emp inner join dept using(deptno) group by rollup(dname, job) order by dname, job; 只查询小计 select dname, job, sum(sal) from emp inner join dept using(deptno) group by grouping sets(dname, job) order by dname, job; 如果希望查询的结果只有小计,用 grouping sets,因为它的效率要高于cube和rollup 14. 排名函数 select rank() over (order by sal desc) r,ename, sal from emp select dense_rank() over (order by sal desc) r,ename, sal from emp 15. 计算行号 select row_number() over (order by sal desc) num , ename from emp; 二. 数据库设计 1. 三范式 1) 列的值唯一,不能有重复的列值(必须遵守) 2) 属性完全依赖于主键(必须遵守) a. 必须满足第一范式 b. 必须有主键 c. 其他列必须完全依赖于主键 3) 属性不依赖于其他非主属性(在特定的场合出于效率的考虑,可以有冗余的设计--违反了第三范式) a. 必须遵守第二范式 b. 去除传递依赖 2. E-R关系(E-R图) 1) 仔细阅读系统的需求规约,研究业务需求 2) 设计并绘制E-R关系图 3. 数据库设计文档 三. Oracle其他对象 1. 序列 Oracle可以通过序列来生成主键 create table Foo ( foo_id number(11), foo_value varchar2(50), constraint FOO_PK primary key(foo_id) ); select max(foo_id) from Foo; ××× 创建序列 DDL create sequence 序列名 [start with 数值] [incremet by 数值] 删除序列 DDL drop sequence 序列名 例如: create sequence SEQ_FOO start with 1000 increment by 2 通过伪列 获取下一个值(序列值会递增) nextval 获取当前值(序列值不会递增) currval select seq_foo.nextval from dual select seq_foo.currval from dual 使用序列值作为主键 insert into foo (foo_id,foo_value) values(seq_foo.nextval,?) 2. 索引 Index 1. 经常要根据某个列进行查询; 2. 选取的列不超过总数的10% 为了提升查询效率可以创建索引. 优势:基于该列的查询效率高 缺点:空间占用,插入时效率低 主键默认创建索引 例如: create index i_account_real_name on account(real_name) 注意: 索引和函数 select id,real_name from account where upper(real_name)=? 基于函数的索引 create index i_account_real_name on account(upper(real_name)) --可以用来查看所有的索引信息 select * from user_indexes --可以用来查看所有的表 select * from user_tables; 引起oracle索引失效的原因很多： 1.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20), 但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn=‘13333333333‘; 2.对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 3.使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引. 错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引， create index test_id_fbi_idx on test(round(id)); 然后 select * from test where round(id)=10; 这时函数索引起作用了 1,<> 2,单独的>,<,(有时会用到，有时不会) 3. 视图(View) 1) 方便权限划分 2) 简化复杂查询 授权 grant create view to scott 创建view create view v_emp_1 as select empno, ename, job from emp; (简单) create view v_emp_sal as select empno,ename,sal from emp where sal>1000; (简单) // 可以检查数据是否可以通过该视图插入(是否符合该视图的查询条件) create view v_emp_sal_1 as select empno,ename,sal from emp where sal>1000 with check option constraint check_v_emp_sal_1; (简单) // 只读视图,只能DQL create view v_emp_sal_2 as select empno,ename,sal from emp where sal>1000 with read only constraint check_v_emp_sal_2; (简单) create view v_emp_dept as select empno,ename,dname from emp inner join dept using(deptno); (复杂) create view v_emp_num as select deptno, count(empno) emp_num from emp where deptno is not null group by deptno (复杂) 对view的操作 DQL (OK) select * from V_EMP_1 DML (对简单视图 OK) 实际上是对BASE Table 的DML insert into V_EMP_1 values(8888,‘hahaha‘,‘clerk‘) 4. 约束 1) 外键约束 create table EMP_1 (-------一个部门可以有多个员工，外键在多的一方 id number(11), name varchar2(20) not null, sal number(12,2) not null, deptId number(4), constraint PK_EMP primary key(id), constraint FK_EMP_DEPT foreign key(deptId) references DEPT_1(id) ) create table DEPT_1 ( id number(4), name varchar2(30) not null, constraint PK_DEPT1 primary key(id) ) alter table SERVICE add constraint FK_SERVICE_ACCOUNT foreign key(ACCOUNT_ID) references ACCOUNT(ID); alter table SERVICE drop constraint FK_SERVICE_ACCOUNT 5.存储过程 1. 运行在数据库内部对数据进行操作的一段程序(Oracle中用PL/SQL语言) 1). PL/SQL块--HelloWorld set serveroutput on declare --变量的声明 a number(5) := 100; b number(5) := 200; c number(5); begin --程序 c := a + b; dbms_output.put_line(‘c=‘||c); end; / 2) if set serveroutput on; declare a1 number(5) := 100; a2 number(5) := 200; a3 number(5); begin if a1>a2 then a3:=1; elsif a1100; end loop; dbms_output.put_line(‘v_sum=‘||v_sum); end; / 4) for set serveroutput on; declare v_sum number(5) := 0; begin -- i 可以不用声明 for i in 1..100 loop v_sum := v_sum + i; end loop; dbms_output.put_line(‘v_sum=‘||v_sum); end; / 5) cursor 游标 set serveroutput on; declare -- 声明变量为数据表列的类型 v_empno emp.empno%type; --通过%type取emp表的empno的类型 v_ename emp.ename%type; --声明一个游标对应一个查询 cursor v_emp_cursor is select empno,ename from emp order by ename; begin -- 从游标中获取数据 open v_emp_cursor; loop --从游标中取出一行存入变量 --取出以后,游标下移 fetch v_emp_cursor into v_empno, v_ename; --退出条件 exit when v_emp_cursor%notfound; --打印输出 dbms_output .put_line(v_empno||‘,‘||v_ename); end loop; close v_emp_cursor; end; / 6) rowtype set serveroutput on; declare v_dept dept%rowtype; --可以如此访问 --v_dept.deptno, v_dept.dname, v_dept.loc cursor v_dept_cursor is select deptno,dname,loc from dept; begin open v_dept_cursor; loop fetch v_dept_cursor into v_dept; exit when v_dept_cursor%notfound; dbms_output .put_line(v_dept.deptno ||‘,‘||v_dept.dname||‘,‘||v_dept.loc); end loop; close v_dept_cursor; end; / 7) 简单的存储过程 procedure create or replace procedure helloworld (a1 in number, a2 in number , sum out number, sub out number) as begin sum := a1+a2; sub := a1-a2; end; /