-- 创建表: 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;
2.插入数据
-- 插入
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;
3.修改表 alter table
1. '增加' 一列或者多列
alter table scott.student_info add address varchar2(50);
alter table scott.student_info add (id_type varchar2(2), id_no varchar2(10));
2. '修改' 一列或者多列
(1) 数据类型
alter table scott.student_info modify address varchar2(100);
alter table scott.student_info modify (id_type varchar(20), id_no varchar2(20));
(2) 列名
alter table scott.student_info rename column address to new_address;
(3) 表名
alter table scott.student_info rename to new_student_info ;
alter table scott.new_student_info rename to student_info;
3. '删除' 一列或者多列,删除多列时,不需要关键字 column
alter table scott.student_info drop column sex;
alter table scott.student_info drop (id_type, id_no);