Oracle创建表语句介绍

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

-- 创建表: student_info 属主: scott (默认当前用户)

create table scott.student_info (

  sno         number(10) constraint pk_si_sno primary key,

  sname       varchar2(10),

  sex         varchar2(2),

  create_date date

);

-- 添加注释

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.sex is '性别';

comment on column scott.student_info.create_date is '创建日期';

-- 语句授权，如：给 hr 用户下列权限

grant select, insert, update, delete on scott.student_info to hr;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

-- 插入数据

insert into scott.student_info (sno, sname, sex, create_date)

values (1, '张三', '男', sysdate);

insert into scott.student_info (sno, sname, sex, create_date)

values (2, '李四', '女', sysdate);

insert into scott.student_info (sno, sname, sex, create_date)

values (3, '王五', '男', sysdate);

-- 修改

update scott.student_info si

   set si.sex = '女'

 where si.sno = 3;

-- 删除

delete scott.student_info si where si.sno = 1;

-- 提交

commit;

-- 查询

select * from scott.student_info;

1

2

alter table scott.student_info add address varchar2(50);

alter table scott.student_info add (id_type varchar2(2), id_no varchar2(10));

1

2

alter table scott.student_info modify address varchar2(100);

alter table scott.student_info modify (id_type varchar(20), id_no varchar2(20));

1

alter table scott.student_info rename column address to new_address;

1

2

alter table scott.student_info rename to new_student_info ;

alter table scott.new_student_info rename to student_info;

1

2

alter table scott.student_info drop column sex;

alter table scott.student_info drop (id_type, id_no);

1

2

-- 删除表结构

drop table scott.student_info;

1

2

-- 清空表数据

truncate table scott.student_info;

1

'dba_xx' > all_xx > user_xx （'dba_xx' DBA 用户才有权限）

1

select * from dba_tables; -- all_tables、user_tables

1

select * from dba_tab_comments;

1

select * from dba_tab_cols t order by t.column_id;

1

select * from dba_col_comments;