Oracle数据库整理

范式

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

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

准确性和唯一性

主键约束 `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;
/

咸闲贤鱼

Oracle数据库整理

Oracle数据库整理

范式

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

主键约束 `primary key`

追加关联条件 `on`

外键约束 `foreign key`

唯一约束`unique`

非空约束`not null`

检测约束`check`

禁用/激活/删除约束

函数

视图

SQL语句查询(重点)

存储过程(重点)

创建存储过程

调用存储过程

删除存储过程

流程控制语句

if…then…elsif…语句

case语句

goto语句

loop语句(重点)

for语句

数字图像处理–复习题

ASP.NET连接Sql Server数据库[部分]

limexc

Comments | NOTHING

取消回复

咸闲贤鱼

咸闲贤鱼

Oracle数据库整理

范式

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

主键约束 primary key

追加关联条件 on

外键约束 foreign key

唯一约束unique

非空约束not null

检测约束check

禁用/激活/删除约束

函数

视图

SQL语句查询(重点)

存储过程(重点)

创建存储过程

调用存储过程

删除存储过程

流程控制语句

if…then…elsif…语句

case语句

goto语句

loop语句(重点)

for语句

数字图像处理–复习题

ASP.NET连接Sql Server数据库[部分]

limexc

Comments | NOTHING

取消回复

咸闲贤鱼

主键约束 `primary key`

追加关联条件 `on`

外键约束 `foreign key`

唯一约束`unique`

非空约束`not null`

检测约束`check`