首页 > 代码库 > 关于Oracle的一些基础知识以及注意事项

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

2024-10-31 09:37:02   208人阅读

一.oracle基础1.1 DDL(Data Definition Language) 数据定义语言 create drop,desc（注意，此操作只能在PL/SQL Developer的命令窗户执行，在sql窗口不能执行）table tablename1.2 DML(Data Manipulation Language) 数据操作语言 insert, delete,update,…1.3 TCL(Transaction Control Language) 事务控制语言 begin transaction commit rollback1.4 DQL(Data Query Language) 数据查询语言 select1.5 DCL(Data Control Language) 数据控制语言 grant revoke2.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)解决此问题的办法是使用nvarchar24.字符串的连接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,返回arg1select nvl(c1,‘hahaha‘) from goo_1;nvl2(arg1,arg2,arg3)如果arg1为null,返回arg3如果arg1不为null,返回arg2select nvl2(c1,‘hehehe‘,‘hahaha‘) from goo_1;7. 主键,数据表中行的唯一性1) 创建主键主键也是列(多列-联合主键),一般没有业务含义(不能发生变更),唯一标识数据表中的某一行必须有主键类型最好是numberconstraint 约束名 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 deletedrop 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=http://www.mamicode.com/if1 返回 then1"小计" 对于分组的列为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和rollup14. 排名函数select rank() over (order by sal desc) r,ename, sal from empselect dense_rank() over (order by sal desc) r,ename, sal from emp15. 计算行号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; ×××创建序列 DDLcreate sequence 序列名 [start with 数值] [incremet by 数值]删除序列 DDLdrop sequence 序列名例如:create sequence SEQ_FOO start with 1000 increment by 2通过伪列获取下一个值(序列值会递增)nextval获取当前值(序列值不会递增)currvalselect seq_foo.nextval from dualselect seq_foo.currval from dual使用序列值作为主键insert into foo (foo_id,foo_value) values(seq_foo.nextval,?)2. 索引 Index1. 经常要根据某个列进行查询;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创建viewcreate view v_emp_1 as select empno, ename, job from emp; (简单)create view v_emp_sal as select empno,ename,sal fromemp where sal>1000; (简单)// 可以检查数据是否可以通过该视图插入(是否符合该视图的查询条件)create view v_emp_sal_1 as select empno,ename,sal fromemp where sal>1000 with check option constraint check_v_emp_sal_1; (简单)// 只读视图,只能DQLcreate view v_emp_sal_2 as select empno,ename,sal fromemp 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_1DML (对简单视图 OK) 实际上是对BASE Table 的DMLinsert 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_ACCOUNT5.存储过程1. 运行在数据库内部对数据进行操作的一段程序(Oracle中用PL/SQL语言)1). PL/SQL块--HelloWorldset 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) ifset serveroutput on;declarea1 number(5) := 100;a2 number(5) := 200;a3 number(5);beginif a1>a2 then a3:=1;elsif a1100;end loop;dbms_output.put_line(‘v_sum=‘||v_sum);end;/4) forset serveroutput on;declarev_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) rowtypeset 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) 简单的存储过程 procedurecreate 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;/

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


联系
我们