1、char
存儲(chǔ)固定長(zhǎng)度字符串 最大長(zhǎng)度為2000 如果沒(méi)有說(shuō)明，默認(rèn)長(zhǎng)度為1 如果賦值長(zhǎng)度小于規(guī)定的固定長(zhǎng)度，oracle自動(dòng)用空格填充。
2、varchar2
存儲(chǔ)可變長(zhǎng)度的字符串，最大長(zhǎng)度4000 不需要用空格
3、date

4、oracle 簡(jiǎn)單操作語(yǔ)句
create table student_tab( --創(chuàng)建學(xué)生信息表
stuid varchar2(20) primary key,
stuname varchar2(100) not null,
stusex varchar2(20) not null,
classnum varchar2(50) not null,
stuaddress varchar2(200) default '地址未錄入',
age number(20) not null,
grade varchar2(100) not null,
idnumber varchar2(100) not null
);

create table student_lesson( --創(chuàng)建課程表
lessid varchar2(11) primary key,
lessname varchar2(30) not null
);
create table student_score( --創(chuàng)建成績(jī)表
scoid varchar2(2000) primary key,
stuid varchar2(2000) references student_tab(stuid) not null , --外鍵
lessscore varchar2(2000) not null
);
-- 簡(jiǎn)單查表
select * from student_tab;
select * from student_lesson;
select * from student_score;

--增加多個(gè)字段
alter table student_score add (A21 number(10) null,A22 varchar2(20) null,A23 varchar2(20) null);

--刪除多個(gè)字段
alter table student_score drop(A21,A22,A23);

--刪除表
drop table student_tab;
drop table student_score;

--插入數(shù)據(jù)
insert into student_tab values('001','小明','女','002','甘肅省',8,'8','123456789');
insert into student_tab values('002','農(nóng)夫山','男','003','四川省',5,'7','2485937');
insert into student_tab values('003','未央','女','092','甘肅省',78,'9','1345789');
insert into student_tab values('054','未花','女','012','上海市',55,'5','6789');
insert into student_tab values('285','樹(shù)兒','女','102','北京市',33,'6','16789');

insert into student_lesson values('002','數(shù)據(jù)結(jié)構(gòu)與算法');
insert into student_lesson values('003','操作系統(tǒng)');
insert into student_lesson values('022','數(shù)據(jù)庫(kù)');
insert into student_lesson values('402','J2EE');
insert into student_lesson values('102','計(jì)算機(jī)體系結(jié)構(gòu)');

insert into student_score values('001','001','98');
insert into student_score values('003','004','88');
insert into student_score values('051','003','100');
insert into student_score values('401','002','95');

--更新數(shù)據(jù)（插入某一字段值）
update student_tab set stubirthday=to_date('2012-01-05','yyyy-mm-dd') where stuid='001';

--更新數(shù)據(jù)
update student_tab set stuname='小小' where stuname='未央';

--刪除數(shù)據(jù)
delete from student_tab where stuname='小小';

--修改表名
alter table student_tab rename to student_info;
alter table student_tab rename to student_tab;

--復(fù)制表內(nèi)容
create table new_student_lesson(
);
insert into new_student_lesson (select * from student_lesson);

--簡(jiǎn)單兩表連接查詢
select a.stuid,a.stuname,a.stusex,b.lessscore from student_tab a,student_score b where a.stuid=b.stuid;
select a.stuid,a.stuname,b.lessname,s.lessscore from student_tab a,student_lesson b,student_score s where
a.stuid=s.stuid and(a.stuname='未%'or stusex='女');

--查詢結(jié)果消除重復(fù)行
select distinct a.stuid,a.stuname,b.lessname,s.lessscore from student_tab a,student_lesson b,student_score s where
a.stuid=s.stuid and(a.stuname='未%'or stusex='女');

--升序order by asc(默認(rèn)) 降序order by desc
select * from student_score ls order by stuid DESC;
select * from student_score ls order by stuid ; --默認(rèn)升序

--模糊查詢
select stuname from student_tab where stuname like '%央';

--給表增加字段
alter table student_tab add (stubirthday date);

--修改字段類(lèi)型
alter table student_tab modify(stusex char(200));