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;

      

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