oracle备考复习

1、建表 字段 数据类型(常用的)

create table students(sid number(5)constraint stu_pk primary key,name varchar2(10) not null,sex varchar2(6) constraint sex_chk_s check(sex in ('m','w')),dob date ,specialty varchar2(10));

create table departments(did number(3) constraint dept_pk primary key,dname varchar(8) not null,address varchar(30));

create table teacher(tid number(5) constraints ther_pk primary key,did number(3)constraints tr_fk_d references departments(did));

主键：constraint constraint_name primary key,

非空：not null,

检查约束：constraint constraint_name check(sex in ('m','w')),

默认值：default sysdate,

外键：constraint con_name references dept(dno),

2、添加约束：表与表之间的关系（主外键）、字段取值范围

alter table table_name add constraint c_name c_expression;alter table stu add constraint s_pk primary key(id);alter table stu add constraint t_fk_s foreign key(id) references d(id);alter table stu modify name not null;alter table stu add constraint s_c check(sex in ('w','m'));

3、会写DML（增删改的语句）例如 插入一些测试数据 修改 删除

INSERT INTO students   VALUES(10101,NULL,'王晓芳', '女', '07-5月-1988','计算机');INSERT INTO students  VALUES(10205,NULL,'李秋枫', '男', '25-11月-1990','自动化');INSERT INTO students  VALUES(10102,10101,'刘春苹', '女', '12-8月-1991','计算机');

4、会有几条查询的语句 涉及到 多表、排序 分组 子查询(难点)

SELECT * FROM teachers t1  WHERE wage >    (SELECT AVG(wage) FROM teachers t2       WHERE t2.department_id = t1.department_id);

5、写一到两个PL/SQL程序包含过程

CREATE OR REPLACE PROCEDURE display_teacher(      v_no teachers.department_id%TYPE)    AS    v_wage teachers.wage%TYPE;    v_maxwage teachers.wage%TYPE;    v_minwage teachers.wage%TYPE;    BEGIN      SELECT AVG(wage) INTO v_wage        FROM teachers WHERE department_id = v_no;      SELECT MAX(wage) INTO v_maxwage        FROM teachers WHERE department_id = v_no;      SELECT MIN(wage) INTO v_minwage        FROM teachers WHERE department_id = v_no;      DBMS_OUTPUT.PUT_LINE        ('该系平均工资为：'||v_wage);      DBMS_OUTPUT.PUT_LINE        ('该系最高工资为：'||v_maxwage);      DBMS_OUTPUT.PUT_LINE        ('该系最低工资为：'||v_minwage);        EXCEPTION      WHEN NO_DATA_FOUND THEN        DBMS_OUTPUT.PUT_LINE('该系不存在。');    END display_teacher;

      

加分题：写一个程序 能够读取指定目录下的文件(可以是多级目录) 然后可以对指定的文件名进行过滤