Oracle数据库整理

发布于 2020-12-05  725 次阅读


Oracle数据库整理

范式

  1. 无重复的列
  2. 属性完全依赖与主键
  3. 属性不依赖于其他非主属性

数据表完整性约束(重点)

准确性和唯一性

主键约束 primary key

非空 唯一

追加关联条件 on

利用 关键字on 指定行为类型
* on delete cascade 父表删除时 子表也被删除
* set null父表删除 子表设置为null
* no cation 父表执行删除 子表有数据时禁止删除

创建表
列名 类型 primary key
----------或者----------
constraint 约束名 primary key(列名)
修改表
alter table 表名 add constraint 约束名 primary key(列名)
alter table 表名 drop constraint 约束名

外键约束 foreign key

创建表
列名 类型 constraint 约束名 unique
修改表
alter table 表名 add constraint 约束名 unique(列名)
alter table 表名 drop constraint 约束名

案例

//创建一个新表 emp_temp1 并将部门编号为30的员工记录插入   
create table emp_temp1 as select * from emp where deptno=30;
//创建一个新表dept_temp1 
create table dept_temp1 as select * from dept; 
//添加主键约束 
alter table dept_temp1 add constraint pk_2 primary key(deptno); 
//为emp_temp1 添加外键dept_temp1(deptno) 
alter table emp_temp1 add constraint emp_dept_fk2 foreign key(deptno) references dept_temp1(deptno);

唯一约束unique

创建表
列名 类型 constraint 约束名 unique
修改表
alter table 表名 add constraint 约束名 unique(列名)
alter table 表名 drop constraint 约束名

非空约束not null

创建表
列名 类型 not null
修改表
alter table 表名 modify 列名 not null
alter table 表名 modify 列名 null

检测约束check

创建一个学习信息表(Student) 并年龄设置约束

create table stuinfo 
( 
    stu_id varchar2(4) primary key, 
    stu_name varchar2(10) not null, 
    age int constraint ag_ck check(age>0 and age<120) 
)

禁用/激活/删除约束

函数

创建

create or replace function get_avg_pay (num_deptno number) return number is 
begin 
    select avg(sal) into num_avg_pay from emp where deptno=num_deptno; 
    return(round(num_avg_play,2)); 
exception 
    where no_data_fount then 
    dbms_output_put_line('该部门编号不存在'); 
    return(0); 
end; 
/

调用

set serveroutput on 
declare 
 avg_pay number; 
begin 
    avg_pay:=get_avg_pay(10); 
    dbms_output_put_line('平均工资时'||avg_pay); 
end; 
/

删除

drop function fun_name;

视图

创建视图
create view emp_view as select * from emp where deptno=20;

可选[只读] with read only;

查询视图
select * from emp_view;
删除视图
drop view 视图名
游标

set serveroutput on 
declare cursor cur_emp(var_job in varchar2:='SALESMAN') 
    is select empno,ename,sal 
    from emp 
    where job=var_job; 
    type record_emp is record --创建一个数据类型 
    ( 
        var_empno emp.empno%type, 
        var_ename emp.ename%type, 
        var_sal emp.sal%type); 
    emp_row record_emp; 
begin 
    open cur_emp('MANAGER');    --打开游标 
    fetch cur_emp into emp_row; --指向第一行 
    while cur_emp%found loop    --循环遍历 
        dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||', 工资是'||emp_row.var_sal); 
        fetch cur_emp into emp_row; --指向下一行 
    end loop; 
        close cur_emp; --关闭游标 
end; 
/

SQL语句查询(重点)

描述 语句
查询工资大于1500的员工信息 select * from emp where sal>1500;
查询销售员工资大于1500的员工信息 select * from emp where job=‘SALESMAN’ and sal>1500;
查询以字母S开头的员工信息 select * from emp where ename like ‘S%’;
查询职务是SALESMAN的员工信息 select * from emp where job=‘SALESMAN’;
查询出员工编号是7369 7788 7566的任意一个员工信息 select * from emp where empno in(7669);
查询工资在2000到3500之间的员工信息 select * from emp where sal between 2000 and 3500;
列出所有不领取奖金,同时工资大于2000元的全部员工信息 select * from emp where comm is null null and sal>2000;
计算平均工资,所有工资和总和,以及最高工资和各组的行数 select job,avg(sal),sum(sal),max(sal),count(job) from emp group by job;
显示每个部门每种职务的平均工资和最高工资 select deptno,job avg(sal),max(sal) from emp group by deptno,job;
统计出每个部门的平均工资大于2000的记录信息 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
按照工资由高到低的排序显示员工信息 select * from emp order by sal desc;
按照工资由高到底排序,如果工资相等则按照入职时间由早到晚进行排序 select * from emp order by sal desc,hiredate asc;
查询经理所在部门名称 select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门 from emp e,dept d where e.deptno=d.deptno and e.job=‘MANAGER’;
通过deptno字段内连接emp表和dept表 select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门 from emp e innner join dept d on e.deptno=d.deptno;
对员工表和部门表进行左外连接 select * from emp left join dept on emp.deptno=dept.deptno;
对员工表和部门表进行右外连接 select * from emp right join dept on emp.deptno=dept.deptno;
对员工表和部门表进行完全外连接 select * from emp full join dept on emp.deptno=dept.deptno;
在emp表中检索工资大于2000的记录,并实现emp表与dept表的自然连接 select empno,ename,job,dname from emp natural join dept where sal>2000;
查询所有管理者所管理的下属员工信息 select em1.empno 员工编号,em1.ename 员工姓名,em2.empno 领导编号,em2.ename 领导姓名 from emp em1 left join emp em2 on em1.mgr=em2.empno;
通过交叉连接dept表和emp表,计算出查询结果的行数 select count(*) from dept cross join emp;

存储过程(重点)

创建存储过程

set serveroutput on
create or replace procedure 过程名 is
begin
 insert into 表名 value(xxx);
 dbms_output.put_line('插入新纪录成功');
end;
/
create or replace procedure select_dept
    (num_deptno in number,
    var_dname out dept.dname%type,
     var_loc out dept.loc%type
    ) is
begin
    select dname,loc
    into var_dname,var_loc
    form dept
    where deptno=num_deptno
exception
    where on_data_fount then
    dbms_output.put_line('该部门编号不存在')
end;
/

调用存储过程

set serveroutput on
declare
 var_dname dept.dname%type 
 var_loc dept.loc%type
begin
    select_dept(10,var_dname,var_loc);
    dbms_output.put_line(var_dname||'位于'||var_loc);
end;
/

删除存储过程

drop procedure 过程名;
PL/SQL
根据员工编号得到对应的员工姓名
set serveroutput on
declare
    eno emp.empno%type;
    ena emp.ename%type;
begin
    eno:=&empno;
    select ename into ena from emp where empno=eno;
    dbms_output.put_line('编号: '||eno||'ena');
end;
/

使用record记录类型变量, 查询员工编号为7369的员工信息

set serveroutput on 
declare
    type emp_type is record
    (
        var_name varchar2(20),
        var_job varchar2(20),
        var_sal number
    );
    empinfo emp_type;
begin
    select ename,job,sal
    into empinfo
    from emp
    where empno=7369;
    dbms_output.put_line('员工'||empinfo.var_ename||'的职务是'||empinfo.var_job||',工资是'||empinfo.var_sal);
end;
/

流程控制语句

if…then…elsif…语句

set serveroutput on
declare
    month int;
begin
    month:=&inputmonth;
    if month >=1 and month <=3 then
        dbms_output.put_line('这是春季');
    elsif month >=4 and month <=6 then
        dbms_output.put_line('这是夏季');
    elsif month >=7 and month <=9 then
        dbms_output.put_line('这是秋季');
    elsif month >=10 and month <=12 then
        dbms_output.put_line('这是冬季');
    else
        dbms_output.put_line('对不起,月份不合法');
    end if;
end;
/

case语句

set serveroutput on
declare
    season int:=&inputseason;
    aboutinfo varchar2(50);
begin
    case season
    when 1 then
        aboutinfo:=season||'季度包括1,2,3月份';
    when 2 then
        aboutinfo:=season||'季度包括4,5,6月份';
    when 3 then
        aboutinfo:=season||'季度包括7,8,9月份';
    when 4 then
        aboutinfo:=season||'季度包括10,11,12月份';
    else
        aboutinfo:=season||'季度不合法';
    end case;
        DBMS_OUTPUT.put_line(aboutinfo);
end;
/

goto语句

set serveroutput on
declare
    x number;
begin
    x:=9;
    <<repeat_loop>>
    x:=x-1;
    dbms_output.put_line(x);
    if x>0 then
    goto repeat_loop;
    end if;
end;
/

loop语句(重点)

set serveroutput on
declare
   sum_i int:=0;
    i int:=0;
begin
   loop
    i:=i+1;
    sum_i:=sum_i+i;
    exit when i=100;
    end loop;
    dbms_output.put_line('前100个自然数的和是:'||sum_i);
end;
/

for语句

set serveroutput on
declare
   sum_i int:=0;
begin
   for i in reverse 1..100 loop
    if mod(i,2)=0 then
        sum_i:=sum_i+i;
    end if;
    end loop;
    dbms_output.put_line('前100个自然数中偶像之和是:'||sum_i);
end;
/

我们都要做生活的高手。