1 概述
1. 作用: '存储数据'(逻辑结构上)
2 表管理
2.1 命名规范
1. 必须以 '字母' 开头,可由 '字母、数字、_、$、#' 组成
2. 长度不能超过 30 个字符
3. 避免使用 Oracle 的 '关键字'
4. 在创建表、列时,Oracle 会自动转为 '大写'
2.2 设计规范:范式
2.3 信息查询
1. 表、列创建查询
(1) select * from dba_tables;
(2) select * from dba_tab_columns t order by t.column_id;
2. 表、列备注查询
(1) select * from dba_tab_comments;
(2) select * from dba_col_comments;
3 表操作
3.1 创建表
create table scott.student_info (
sno number(10) constraint pk_student_info_sno primary key,
sname varchar2(30) not null,
create_date date not null,
create_user varchar2(50) not null,
update_date date not null,
update_user varchar2(50) not null
);
comment on table scott.student_info is '学生信息表';
comment on column scott.student_info.sno is '学号';
comment on column scott.student_info.sname is '姓名';
comment on column scott.student_info.create_date is '创建日期';
comment on column scott.student_info.create_user is '创建人';
comment on column scott.student_info.update_date is '更新日期';
comment on column scott.student_info.update_user is '更新人';
grant select on scott.student_info to hr;
3.2 修改表
1. '增加'
alter table scott.student_info add address varchar2(50);
alter table scott.student_info add (id_type varchar2(1), id_no varchar2(10));
2. '修改'
alter table scott.student_info modify (address varchar2(100));
alter table scott.student_info modify (id_type varchar(2), id_no varchar2(20));
alter table scott.student_info rename column address to new_address;
alter table scott.student_info rename to new_student_info;
alter table scott.new_student_info rename to student_info;
3. '删除'
alter table scott.student_info drop column new_address;
alter table scott.student_info drop (id_type, id_no);
3.3 删除表
drop table scott.student_info;
truncate table scott.student_info;