Posted on 2006-11-10 20:16
黑夜ちつ獨行者 閱讀(345)
評論(0) 編輯 收藏
3.1、創建表
格式：create table 表名；
3.2、刪除表
格式：drop table 表名；
3.3、創建臨時表
格式：create gkoabl temporary table temp
AS
select Name,PhoneNo from friend;
3.4、復制表
格式：create table My_friend AS select*from friend;
3.5 、創建非空約束 ( not null )
3.6、指定默認值 ( default '默認值內容' )
3.7、設定主鍵 ( primary key )
3.8、設置唯一約束 ( 語句后面加 unipue )
3.9、設置檢查約束
格式：age int check(age between 20 and 50);
或 sex varchar check(sex in ('男','女'));
3.10 、使用序列 ( sequences)
格式：
create sequences friendidseq;
(序列的初始值為1，增量為1)
create sequences friendidseq increment by 1
start with 1000;
(序列的初始值為1000，增量為1)
顯示最后一個序列值
select friendidseq.currval from dual;
3.11、使用自動編號字段
格式：create sequences seq;
create or replace trigger autonumber
before insert on friend
for each row
begin
select seq.nextval
into :new.friendid from dual;
end;
3.12、外鍵約束
關鍵字：constraint?? foreign key()
?????????????????references?
舉例
?????????create???table???student(
?????????studentid?? int???not?? null???primary?? key,
?????????name???varchar(20)?? not?? null
?????????);
?????????
?????????create???table???phone(
?????????phoneid???int???not???null???primary?? key,
?????????studentid?? int???not???null,
?????????phone?? varchar(14),
?????????constraint?? fid_fk???foreign???key(srudentid)
?????????????references????student(studentid)????????
?????????)?;
3.13、創建索引
?????????格式：create???index???nameindex?? on?? student (name);???(創建非唯一索引)
?????????????????????create???unique???index???nameindex???on???student(name);???(創建唯一索引)??
?????????????????????
????????查詢索引:
??????????????????????select???student.name???from???student???where???name = '但是';????
??
??????????????????????select???student.name???from???student???where???name =??'%電'?;
??????? 刪除索引
????????????????????? drop???index???nameindex;???
???????????????