oracle（表的管理）

创建一个表

create table student (xh number(4),

name varchar2(20),

sex char(2),

birthday date,

salary number(7,2)

);

添加一个字段

alter table student add (classid number(2));

修改字段的长度

alter table student modify (name varchar2(30));

修改字段类型(不能有数据)

alter table student modify (name char(20));

删除一个字段

alter table student drop column salary;

修改表的名字

rename table student to stu;

删除表

drop table student;

插入一条数据

insert into student values('0001', '张三', '男', '12-may-1988', 1000);

修改数据库默认日期格式

alter session set nls_date_format = 'yyyy-mm-dd';

再次插入一条数据

insert into student values('0002', 'bergren', 'm', '1988-05-12', 1000);

插入部分数据

insert into student(xh, name, sex) values (3, 'xbergren', 'm'); 

插入空值

insert into student(xh, name, sex) values ('4', null, 'f');

更新一条数据

update student set sex = 'f', name = 'songxiaoping' where xh = 1;

update student set salary = 1001,birthday='1988-4-19' where xh=1 and sex='f';

更新salary为原来的1/2

update student set salary=salary/2 where xh=1;

查询为空的数据

select *from student where name is null;

select *from student where name is null and salary is null;

查询部为空的数据

select *from student where name is not null;

只查询学号和姓名

select xh,name from student;

保存一个点

savepoint a; 

删除表数据， 写日志  可恢复

delete from student;

delete from student where xh=1;

回滚

rollback to a;

删除表中所有数据，不写日志，不可恢复，速度很快

truncate table student;