计算机软件Oracle复习知识点整理

表空间

创建表空间：

CREATE TABLESPACE tbs DATAFILE ‘d:\app\administrator\oradata\tbs_1.dbf’ SIZE 50M

EXTENT MANAGEMENT LOCAL                 //可省略 另一种是DICTIONARY

UNIFORM SIZE 512K                          //区定制分配

SEGMENT SPACE MANAGEMENT MANUAL;      //段手动管理

修改表空间：

ALTER TABLESPACE tbs ADD DATAFILE ‘d:\app\administrator\oradata\tbs_2.dbf’ SIZE 10M;                                           //添加数据文件

ALTER DATABASE DATAFILE ‘d:\app\administrator\oradata\tbs_1.dbf’

AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;  //改为自动扩展

ALTER DATABASE DATAFILE ‘d:\app\administrator\oradata\tbs_1.dbf’ RESIZE 8M;  

//手动修改数据文件大小

删除表空间：DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES;

查询表空间:V$tablespace    dba_tablespaces

改变数据文件的名称和位置：

1. alter tablespace tbs offline;
2. host copy

d:\app\administrator\oradata\tbs_1.dbf

d:\app\administrator\tbs_1.dbf

1. alter tablespace tbs rename datafile

‘d:\app\administrator\oradata\tbs_1.dbf’ to

‘d:\app\administrator\tbs_1.dbf’

1. alter tablespace tbs online;

查询数据文件：v$datafile     dba_data_files

控制文件：

控制文件用于记录和维护数据库的物理结构，包括:数据库名称、数据文件和重做日志文

件的名称和位置等。

数据库至少要包含一个控制文件, Oracle建议使用多个控制文件避免因单个控制文件损

坏而导致的数据库无法启动。

控制文件对数据库至关重要，应联机保存多个备份，存储在不同的磁盘上。

多路复用控制文件: 4步。

（1）alter system set control_files=

           'd:\app\administrator\oradata\control01.ctl',

           'd:\app\administrator\oradata\control02.ctl',

           'd:\app\administrator\control03.ctl'    scope=spfile;

（2）shutdown immediate

（3）host copy

d:\app\administrator\oradata\control01.ctl 

     d:\app\administrator\control03.ctl

 （4）startup

查询控制文件: v$controlfile

范围分区：

Create table student_range(

Sno number(6) primary key,

Sname varchar2(10),

Sage int,

Score number)

Partition by range(score)

(

Partition p1 values less than(60) tablespace tbs1,

Partition p2 values less than(80) tablespace tbs2,

Partition p3 values less than(maxvalue) tablespace tbs3

);

列表分区：

Create table student_list(

Sno number(6) primary key,

Sname varchar2(10),

Sex char(2) constraint s_ck1 check(sex in(‘M’,’F’)

)

Partition by list(sex)

(

Partition stu_male values(‘M’) tablespace tbs1,

Partition stu_female values(‘F’) tablespace tbs2

);

用户管理

创建用户：create user user1 identified by user1 default tablespace users temporary tablespace temp quota 5M on users password expire account lock;

修改用户：alter user user1 identified by user1 account unlock;

删除用户：drop user user1 CASCADE;

授权管理

授系统权限：grant create session,create table to user1;

授对象权限：grant update,select on scott.emp to user1;

回收权限：revoke create table from user1;

修改表：

Alter table student ADD(phone varchar2(10));

Alter table student ADD(hiredate DATE default sysdate not null);

Alter table student ADD constraint p_ck1 check(sage between 20 and 30);

Alter table student ADD constraint p_ck2 check(sex=’M’ or sex=’F’) modify sex default ‘M’;

CREATE TABLE employee(

   empno NUMBER(5) PRIMARY KEY,

   ename VARCHAR2(15),

   deptno NUMBER(3) NOT NULL CONSTRAINT fk_emp REFERENCES dept(deptno)

);

模糊查询：

SELECT * FROM employees WHERE last_name LIKE '%S%';  名字中含有S的

SELECT * FROM employees WHERE first_name LIKE '_a%';  第二个字母是a的

SELECT * FROM employees WHERE first_name LIKE '%x_%' ESCAPE 'x'; 名字中含有_的

空值查询：

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

多列排序

SELECT * FROM employees ORDER BY department_id,salary DESC;  ACS升序，默认

统计查询

SELECT count(*),avg(salary),max(salary),min(salary) FROM employees WHERE

department_id=50;

分组查询

SELECT department_id,count(*),avg(salary) FROM employees GROUP BY

department_id HAVING avg(salary)>8000;

连接查询

内连接：

select w.ename,w.empno,w.sal,m.ename,m.empno,m.sal from emp w,emp m where w.mgr=m.empno and w.deptno=10；

左外连接

SELECT dname,empno,ename FROM dept LEFT JOIN emp ON

dept.deptno=emp.deptno AND dept.deptno=10;

右外连接

SELECT empno,ename,dname FROM dept RIGHT JOIN emp ON

dept.deptno=emp.deptno AND dept.deptno=20;

INSERT INTO emp_s SELECT deptno,max(salary), min(salary) FROM emp GROUP BY deptno;

INSERT INTO dept VALUES(departments_seq.nextval, 'Teaching',200,1800);

UPDATE emp SET sal=300+ (SELECT avg(sal) FROM emp WHERE deptno=10)

WHERE deptno=30;  

DELETE FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7900);

创建一个以员工号为参数，返回该员工所在部门平均工资的函数。编写PL/SQL块，输出7788号员工所在部门的平均工资。

create or replace function func_avgsal(

    p_empno emp.empno%type)

return emp.sal%type

as

    v_deptno emp.deptno%type;

    v_avgsal emp.sal%type;

begin

select deptno into v_deptno from emp where empno=p_empno;

select avg(sal) into v_avgsal from emp where deptno=v_deptno;

return v_avgsal;

end func_avgsal;

创建一个函数，以员工号为参数，返回该员工的工资。并编写PL/SQL块，调用该函数求出所有员工的员工号，员工姓名和工资。

create or replace function func_sal(

p_empno emp.empno%type,

p_ename out emp.ename%type)

return emp.sal%type

as

     v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=p_empno;

select ename into p_ename from emp where empno=p_empno;

return v_sal;

EXCEPTION

    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,'There is not such an employee!');

end func_sal;

declare

v_sal emp.sal%type;

p_ename emp.ename%type;

begin

for v_emp in (select distinct empno from emp where empno is not null) loop

v_sal:=func_sal(v_emp.empno,p_ename);

dbms_output.put_line(v_emp.empno||' '||p_ename||' '||v_sal);

end loop;

end;

创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工姓名、工资及所在员工号。编写PL/SQL块，调用此过程求出20号部门平均工资以及20号部门中比平均工资高的员工号、员工名和工资。

create or replace procedure proc_show(

p_deptno emp.deptno%type)

as

v_sal emp.sal%type;

begin

select avg(sal) into v_sal from emp where deptno=p_deptno;

dbms_output.put_line(p_deptno||' '||'avgsal is:'||v_sal);

for v_emp in (select * from emp where deptno=p_deptno and sal>v_sal)loop

dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);

end loop;

end proc_show;

创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资和最低工资。编写PL/SQL块求出所有部门的人数、最高工资和最低工资。

create or replace procedure proc_show3(

p_deptno emp.deptno%type,

p_count out number,

p_maxsal out emp.sal%type,

p_minsal out emp.sal%type)

as

v_sal emp.sal%type;

begin

select count(*),max(sal),min(sal) into p_count,p_maxsal,p_minsal from emp where deptno=p_deptno;

exception

when no_data_found then

dbms_output.put_line('the department doesn't exists');

end proc_show3;

declare

v_count number;

v_maxsal emp.sal%type;

v_minsal emp.sal%type;

begin

for v_emp in (select distinct deptno from emp)loop

proc_show3(v_emp.deptno,v_count,v_maxsal,v_minsal);

dbms_output.put_line(v_emp.deptno||' '||v_count||' '||v_maxsal||' '||v_minsal);

end loop;

end;

create or replace procedure pro_up(

p_empno emp.empno%type)

as

v_deptno emp.deptno%type;

v_inc number(4);

begin

select deptno into v_deptno from emp where empno=p_empno;

case v_deptno

when 10 then v_inc:=140;

when 20 then v_inc:=200;

when 30 then v_inc:=250;

else v_inc:=300;

end case;

update emp set sal=sal+v_inc where empno=p_empno;  end pro_up;

create or replace procedure pro_up(

p_empno emp.empno%type)

as

v_deptno emp.deptno%type;

v_inc number(4);

begin

select deptno into v_deptno from emp where empno=p_empno;

if v_deptno=10 then v_inc:=140;

elsif v_deptno=20 then v_inc:=200;

elsif v_deptnp=30 then v_inc:=250;

else v_inc:=300;

end if;

update emp set sal=sal+v_inc where empno=p_empno;  end pro_up;

begin

for v_emp in (select distinct empno from emp where empno is not null) loop

pro_up(v_emp.empno);

end loop;

end;