数据控制语言DCL和数据定义语言DDL

数据控制语言DCL 

系统权限：

create session,create sequence,create synonym;create table,create any table,drop table,drop any table;create procedure,create any procedure;create user,drop user;create view

  对象权限：

select,insert,update,delete,execute

  创建用户：

  create user wang identified by office;

  create user xiaoli identified by finance

default tablespace users

temporary tablespace temp;

  修改用户:

alert user wang identified by gold;

  删除用户:

drop user wang [cascade]

  授权用户:=>user_sys_privs

grant 系统权限列表 to 用户名 [with admin option]

grant 对象权限列表 on 表名 to 用户名 [with admin option]

  收取权限：=>user_tab_privs_recd

revoke 系统权限列表 from 用户名

revoke 对象权限列表 on 表名 from 用户名

数据定义语言DDL

数据基本类型：char,varchar2,long;number,float;date;raw,long raw;rowid;blob,clob,bfile

完整性约束:Primary key,Not null,Check,Unique,foReign key

表的定义：=>user_tables,=>user_tab_columns,=>user_constraints

 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) 
);

 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 )
);

 courses表结构

CREATE TABLE courses(
  course_id NUMBER(5)
    CONSTRAINT course_pk PRIMARY KEY,
  course_name VARCHAR2(30) NOT NULL,
  credit_hour NUMBER(2)
);

 
students_grade表结构

CREATE TABLE students_grade(
  student_id NUMBER(5)
    CONSTRAINT students_grade_fk_students 
    REFERENCES students(student_id), 
  course_id NUMBER(5)
    CONSTRAINT students_grade_fk_courses
    REFERENCES courses(course_id),
  score NUMBER(4,1)
);

 

  描述表:describe  表名

  修改表名：rename deparment to dep;

  删除表: drop table teacher;

  增加表列： alert table students add sex varchar2(6);

  删除表列： alert table students drop column sex;

  修改表列： alert table students modify wage number(7,2);

  增加约束： alert table students add constraint s_pk primary key(student_id);

  删除约束： alert table students drop constraint s_pk;

  有效约束： alert table students enable constraint s_pk;

  失效约束： alert table students disable constraint s_pk;