自学Oracle的DDL、DML语句

– Oracle
– DDL

– 创建一张学生信息表
create table t_student
(
id int,
name varchar2(20),
age int,
class int,
grade int,
sex varchar(10),
course varchar2(20)
);

comment on table t_student
is ‘学生信息表’;
comment on column t_student.id
is ‘主键’;
comment on column t_student.name
is ‘姓名’;
comment on column t_student.age
is ‘年龄’;
comment on column t_student.class
is ‘班级’;
comment on column t_student.grade
is ‘年级’;
comment on column t_student.sex
is ‘性别’;
comment on column t_student.course
is ‘课程’;

– 表中加入2个字段
alter table t_student add entrance_time date;
comment on column t_student.entrance_time is ‘入学时间’;
alter table t_student add graduation_time date;
comment on column t_student.graduation_time is ‘毕业时间’;

– 修改表中字段
alter table t_student modify (name varchar(30));

– 修改表中字段名
alter table t_student rename column entrance_time to start_time;
alter table t_student rename column graduation_time to end_time;

– 修改表名
rename t_student to student;

– 删除字段
alter table student drop column start_time;
alter table student drop column end_time;

– 删除表
drop table student;

– DML

– t_student表中插入数据（列名可省略顺序固定）（顺序颠倒，字段不全要标好表名和列名）

insert into t_student (id,name,age,class,grade,sex,course) values (1,‘Mary’,9,4,3,‘W’,‘math,chinese,english’);

insert into t_student (id,name,age,class,grade,sex,course) values (2,‘Susan’,9,4,3,‘W’,‘math,chinese,english’);

insert into t_student (id,name,age,class,grade,sex,course) values (3,‘Jackson’,9,4,3,‘M’,‘math,chinese,english’);

insert into student (name,grade,course) values (‘Tom’,5,‘english,chinese,math’);

insert into student (grade,name,course) values (5, ‘Tom’,‘english,chinese,math’);

insert into student （select * from t_student）;

– 复制表数据
create table student as select * from t_student;

–只复制表结构不复制数据
create table student as select * from t_student where 1=2;

–update 更新

update student set age=age + 1 where id in (‘1’,‘2’,‘3’);

–delete 删除

delete from student where id=1;

–清空表 无需提交事务，效率也高，尽量避免使用
truncate table student;