资源描述：
《oracle 基本建表语句》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、--创建用户createuserhanidentifiedbyhandefaulttablespaceusersTemporaryTABLESPACETemp;grantconnect,resource,dbatohan;//授予用户han开发人员的权利--------------------对表的操作----------------------------创建表createtableclasses( idnumber(9)notnullprimarykey, classnamevarchar2(4
2、0)notnull) --查询表select*fromclasses;--删除表droptablestudents;--修改表的名称renamealist_table_copytoalist_table;--显示表结构describetest--不对没查到-----------------------对字段的操作-------------------------------------增加列altertabletestaddaddressvarchar2(40);--删除列altertabletestdropc
3、olumnaddress;--修改列的名称altertabletestmodifyaddressaddressesvarchar(40;--修改列的属性altertabletestmodicreatetabletest1( idnumber(9)primarykeynotnull, namevarchar2(34) )renametest2totest;--创建自增的序列createsequenceclass_seqincrementby1startwith1MAXVALUE999999NO
4、CYCLENOCACHE;selectclass_seq.currvalfromdual--插入数据insertintoclassesvalues(class_seq.nextval,'软件一班')commit;--更新数据updatestu_accountsetusername='aaa'wherecount_id=2;commit;--创建唯一索引createuniqueindexusernameonstu_account(username); --唯一索引不能插入相同的数据--行锁在新打开的对话中不能对此行进行
5、操作select*fromstu_accounttwheret.count_id=2forupdate;--行锁--altertablestuinfomodifysty_idtostu_id;altertablestudentsdropconstraintclass_fk;altertablestudentsaddconstraintclass_fkforeignkey(class_id)referencesclasses(id);--外键约束altertablestuinfoaddconstraintstu_fkfo
6、reignkey(stu_id)referencesstudents(id)ONDELETECASCADE;--外键约束,级联删除altertablestuinfodropconstantstu_fk; insertintostudentsvalues(stu_seq.nextval,'张三',1,sysdate);insertintostuinfovalues(stu_seq.currval,'威海');select*fromstuinfo;createtablezhuce( zc_idnumber(9
7、)notnullprimarykey, stu_idnumber(9)notnull, zhucetimedatedefaultsysdate)createtablefeiyong( fy_idnumber(9)notnullprimarykey, stu_idnumber(9)notnull, mx_idnumber(9)notnull, yijiaonumber(7,2)notnulldefault0, qianfeinumber(7,2)not
8、null )createtalbefymingxi( mx_idnumber(9)notnullprimarykey, feiyongnumber(7,2)notnull, //共7位数字，小数后有两位 class_idnumber(9)notnull}createtablecard(
