資源描述：
《oracle 基本建表語句》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、--創(chuàng)建用戶createuserhanidentifiedbyhandefaulttablespaceusersTemporaryTABLESPACETemp;grantconnect,resource,dbatohan;//授予用戶han開發(fā)人員的權(quán)利--------------------對(duì)表的操作----------------------------創(chuàng)建表createtableclasses(??????idnumber(9)notnullprimarykey,??????classnamevarchar2(40)notnu
2、ll)?????--查詢表select*fromclasses;--刪除表droptablestudents;--修改表的名稱renamealist_table_copytoalist_table;--顯示表結(jié)構(gòu)describetest--不對(duì)沒查到-----------------------對(duì)字段的操作-------------------------------------增加列altertabletestaddaddressvarchar2(40);--刪除列altertabletestdropcolumnaddress;-
3、-修改列的名稱altertabletestmodifyaddressaddressesvarchar(40;--修改列的屬性altertabletestmodicreatetabletest1(??????idnumber(9)primarykeynotnull,??????namevarchar2(34)?????)renametest2totest;--創(chuàng)建自增的序列createsequenceclass_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;selectcl
4、ass_seq.currvalfromdual--插入數(shù)據(jù)insertintoclassesvalues(class_seq.nextval,'軟件一班')commit;--更新數(shù)據(jù)updatestu_accountsetusername='aaa'wherecount_id=2;commit;--創(chuàng)建唯一索引createuniqueindexusernameonstu_account(username);??--唯一索引不能插入相同的數(shù)據(jù)--行鎖在新打開的對(duì)話中不能對(duì)此行進(jìn)行操作select*fromstu_accounttwhe
5、ret.count_id=2forupdate;--行鎖--altertablestuinfomodifysty_idtostu_id;altertablestudentsdropconstraintclass_fk;altertablestudentsaddconstraintclass_fkforeignkey(class_id)referencesclasses(id);--外鍵約束altertablestuinfoaddconstraintstu_fkforeignkey(stu_id)referencesstudents(
6、id)ONDELETECASCADE;--外鍵約束,級(jí)聯(lián)刪除altertablestuinfodropconstantstu_fk;???insertintostudentsvalues(stu_seq.nextval,'張三',1,sysdate);insertintostuinfovalues(stu_seq.currval,'威海');select*fromstuinfo;createtablezhuce(??????zc_idnumber(9)notnullprimarykey,??????stu_idnumber(9)no
7、tnull,??????zhucetimedatedefaultsysdate)createtablefeiyong(??????fy_idnumber(9)notnullprimarykey,??????stu_idnumber(9)notnull,??????mx_idnumber(9)notnull,??????yijiaonumber(7,2)notnulldefault0,??????qianfeinumber(7,2)notnull??????)createtalbefymingxi(??????mx_idnumber(
8、9)notnullprimarykey,??????feiyongnumber(7,2)notnull,????//共7位數(shù)字，小數(shù)后有兩位??????class_idnumber(9)notnull}createtablecard(
