資源描述：
《Oracle_基本建表語句》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、--創(chuàng)建用戶createuserhanidentifiedbyhandefaulttablespaceusersTemporaryTABLESPACETemp;grantconnect,resource,dbatohan;//授予用戶han開發(fā)人員的權(quán)利--------------------對表的操作--------------------------創(chuàng)建表格語法:????createtable表名(??????字段名1字段類型(長度)是否為空,??????字段名2字段類型??????是否為空);-增加主鍵????alterta
2、ble表名addconstraint主鍵名primarykey(字段名1);-增加外鍵:????altertable表名??????addconstraint外鍵名foreignkey(字段名1)???????references關(guān)聯(lián)表(字段名2);在建立表格時就指定主鍵和外鍵???createtableT_STU(?????STU_ID??????????????char(5)????????????????????????notnull,??????STU_NAME????????????varchar2(8)????????
3、????????????notnull,?????constraintPK_T_STUprimarykey(STU_ID));主鍵和外鍵一起建立：????createtableT_SCORE(??????EXAM_SCORE??????????number(5,2),??????EXAM_DATE???????????date,???????AUTOID??????????????number(10)?????????????????????notnull,??????STU_ID??????????????char(5),???
4、???SUB_ID??????????????char(3),??????constraintPK_T_SCOREprimarykey(AUTOID),??????constraintFK_T_SCORE_REFEforeignkey(STU_ID)??????referencesT_STU(STU_ID))--創(chuàng)建表createtableclasses(??????idnumber(9)notnullprimarykey,??????classnamevarchar2(40)notnull)?????--查詢表select*fr
5、omclasses;--刪除表droptablestudents;--修改表的名稱renamealist_table_copytoalist_table;--顯示表結(jié)構(gòu)describetest--不對沒查到-----------------------對字段的操作-------------------------------------增加列altertabletestaddaddressvarchar2(40);--刪除列altertabletestdropcolumnaddress;--修改列的名稱altertabletest
6、modifyaddressaddressesvarchar(40;--修改列的屬性altertabletestmodicreatetabletest1(??????idnumber(9)primarykeynotnull,??????namevarchar2(34)?????)renametest2totest;--創(chuàng)建自增的序列createsequenceclass_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;selectclass_seq.currvalfromd
7、ual--插入數(shù)據(jù)insertintoclassesvalues(class_seq.nextval,'軟件一班')commit;--更新數(shù)據(jù)updatestu_accountsetusername='aaa'wherecount_id=2;commit;--創(chuàng)建唯一索引createuniqueindexusernameonstu_account(username);??--唯一索引不能插入相同的數(shù)據(jù)--行鎖在新打開的對話中不能對此行進行操作select*fromstu_accounttwheret.count_id=2forup
8、date;--行鎖--altertablestuinfomodifysty_idtostu_id;altertablestudentsdropconstraintclass_fk;altertablestudentsaddconstraintcla
