oracle 10g基础操作表
1创建新表
1.1从查询到的表创建表
create table temp as select stuName,stuNo,stuSex from stuInfo where stuAge>25;
1.2创建新表
/*学生信息表*/
create table stuInfo(
stuName varchar2(10) ,
stuNo varchar2(10),
stuSex varchar2(4),
stuAge number(2),
stuSeat number(10),
stuAddress varchar2(400));
/*学生成绩表*/
create table stuMark(
examNo varchar2(10),
stuNo varchar2(10),
writtenExam number(4),
labExam number(4));
2 修改表
2.1 增加字段
alter table stuInfo add(Zip number(6));
2.2 删除字段
alter table stuInfo drop column Zip
2.3 修改字段类型
alter table stuInfo modify(Zip varchar2(6));
2.4修改字段大小
alter table stuInfo modify(Zip number(4));
2.5 删除表
drop table stuInfo
3约束
3.1添加约束
alter table stuInfo add constraint PK_stuNo primary key(stuNo);
alter table stuInfo add constraint CK_stuSex check(stuSex in('男','女'));
alter table stuInfo add constraint CK_stuAge check(stuAge between 15 and 40);
alter table stuInfo add constraint CK_stuSeat check(stuSeat between 1 and 30);
alter table stuMark add constraint PK_ExamNo_stuMark primary key(examNo);
alter table stuMark add constraint FK_stuNo_stuMark foreign key(stuNo) references stuInfo(stuNo);
select stuName,decode(stuSex,'男','男同志'),
(stuSex,'女','女同志')
from stuInfo;
alter table stuInfo modify(stuSex not null);
3.2删除约束
3.2.1删除普通约束
alter table stuInfo drop constraint CK_stuSex;
3.2.2删除被外键参照的主键约束
alter table stuInfo drop primary key PK_StuNo
4索引
4.1创建索引
create index stuName_index on stuInfo(stuName);
4.2删除索引
drop index stuName_index;
5创建序列
5.1 创建序列
create sequence stuSeat_identity
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 2
5.2触发器实现字段列自增长
相关文档：
This course is aim to train the great DBA with good English speaking.
In recent years, more demands of Oracle DBA, but most of Senior DBAs are required to speak good English.
English has become the great barrier for more peoples in their career development, you must have the deep feeling about it ......
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出� ......
从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。
如果经常在表上执行DML操� ......
一。查看oracle数据库是否为归档模式：
1.select name,log_mode from v$database;
NAME LOG_MODE
------------------ ------------------------
QUERY NOARCHIVELO ......
