oracle练习之增删改

--学生表
CREATE TABLE STUDENT(
XH NUMBER(4),--学号
XM VARCHAR2(20),--姓名
SEX CHAR(2),--性别
BRITHDAY DATE,--出生日期
SAL NUMBER(7,2) --奖学金
);
CREATE TABLE CLASSES(
CLASSID NUMBER(2),
CNAME VARCHAR2(40)
);
--添加字段
alter table student add(classid number(2));
--修改字段长度
alter table student modify(xm varchar2(30));
--修改字段的类型/或是名字（不能有数据）
alter table student modify(xm char(30));
--删除一个字段
alter table student drop column sal;
--修改表的名字
rename student to stu;
--删除表
drop table stu;
--插入字段
insert into student values(1,'小明','男','1997-12-12',23333.33,12)
--修改日期的格式
alter session set nls_date_format='yyyy-mm-dd';

insert into student values(2,'小明','男','1997-12-12',23333.33,12)

select * from student

--插入空值
insert into student(xh,xm,sex,brithday) values(3,'aa','女',null)

--查询空值
select * from student where brithday is null
select * from student where brithday is not  null

--工资为原来的一般
update student set sal = sal/2 where sex='男'
update student set sal = sal/2,classid = 3 where sex='男'
--删除数据
--delete 写日志
--设置保存点写日志
insert into student values(1,'小明','男','1997-12-12',23333.33,12);
insert into student values(2,'小明','男','1997-12-12',23333.33,12);
insert into student(xh,xm,sex,brithday) values(3,'aa','女',null);
--之间不能进行commit或是rollback
Savepoint a;
delete from student
rollback to  Savepoint a;
select * from student

--删除不写日志，不能回复
truncate table student;