1 范例数据库表的建立

students表结构：

CREATE TABLE students (
  student_id NUMBER(5)
    CONSTRAINT student_pk PRIMARY KEY,
  monitor_id NUMBER(5),
  name VARCHAR2(10) NOT NULL,
  sex VARCHAR2(6)
  CONSTRAINT sex_chk CHECK(sex IN ('男','女')),
  dob DATE,
  specialty VARCHAR2(10)
);

主键可以直接写 primary key 系统自动为其命名

 select constraint_name from user_constraints where table_name = 'TSA';

这样建表也可以：constraion constraion_name 可省

CREATE TABLE students (
  student_id NUMBER(5) PRIMARY KEY,
  monitor_id NUMBER(5),
  name VARCHAR2(10) NOT NULL,
  sex VARCHAR2(6) CHECK(sex IN ('男','女')),
  dob DATE,
  specialty VARCHAR2(10)
);

departments表结构

CREATE TABLE departments(
  department_id NUMBER(3)
    CONSTRAINT department_pk PRIMARY KEY,
  department_name VARCHAR2(8) NOT NULL,
  address VARCHAR2(40)
);

teachers表结构

CREATE TABLE teachers (
  teacher_id NUMBER(5)
    CONSTRAINT teacher_pk PRIMARY KEY,
  name VARCHAR2(8) NOT NULL,
  title  VARCHAR2(6),
  hire_date DATE DEFAULT SYSDATE,
  bonus NUMBER(7,2),
  wage NUMBER(7,2),
  department_id NUMBER(3)
    CONSTRAINT teachers_fk_departments
    REFERENCES departments(department_id )
);

同理外键的约束名也可以不写。