java達(dá)內(nèi)課件06oracle編程exday了解事務(wù)及相關(guān)的命令

1234*5*注 練習(xí)1：非等值連接(on后面的條件丌是等值操作 練習(xí)2：insert(插入數(shù)據(jù) 使用方法：丌寫(xiě)列 使用方法：寫(xiě)列 練習(xí)3：update(更新數(shù)據(jù) 練習(xí)4：delete(刪除數(shù)據(jù) 練習(xí)5：多個(gè)會(huì)話操作同一張 練習(xí) 練習(xí)7：drop(刪除結(jié)構(gòu)和全部的表數(shù)據(jù) 練習(xí)8：truncate(截取,截?cái)?練習(xí)9：alter(修改結(jié)構(gòu) 1onEmp_xxxSalgrade_xxx123451SQL>createsalgrade_xxx(gradenumber(2),lowsalnumber(7,2),SQL>createsalgrade_xxx(gradenumber(2),lowsalnumber(7,2),hisalnumber(7,)SQL>insertintosalgrade_ningvalues(1,10001,99999);SQL>insertintosalgrade_ningvalues(2,8001,10000);SQL>insertintosalgrade_ningvalues(3,6001,8000);SQL>insertintosalgrade_ningvalues(4,4001,6000);SQL>insertintosalgrade_ningvalues(5,1,4000);SQL>commitSQL>selectSQL>selecte.empno,e.ename,e.salary,s.gradefromemp_xxxejoinsalgrade_xxxsone.salarybetweens.lowsalands.hisal練習(xí)2：insert(揑入數(shù)據(jù))--如果有數(shù)據(jù)沒(méi)提供,用nullSQL>insertintodept_xxxvalues(66,'market',null)SQL>insertintovalues(1020,'rory','Programmer',6000,null,null,sysdate,10)SQL>SQL>insertintoemp_xxx(empno,salary,ename,hiredate)values(1020,6000,'rory',to_date('2011/10/10','yyyy/mm/dd'))to_dateoracle表【案例4】表：只結(jié)構(gòu),不數(shù)SQL>SQL>createtablesalgrade_yyywhere1<>1【案例5】表：一部分?jǐn)?shù)SQL>createtableemp_yyyselectempnoenamesalary*12 fromemp_xxxwheredeptno=10;【案例6】表：一部分?jǐn)?shù)SQL>createtableemp_count(didemp_num fromemp_xxxgroupbydeptno7emp10如果有一張表emp1第1 創(chuàng)建一個(gè)空SQLSQL>createtableemp_bak1--emp_bak1emp_xxxwhere1=0;SQL>insertinto(SQL>insertinto(select*fromemp_xxxwheredeptno=10)SQL>SQL>deletefromemp_bak1 fromSQL>insertinto(select*fromemp_xxxwheredeptnoin(20,30))rownum98SQL>SQL>deletefromemp_bak1SQL>insertinto(select*fromemp_xxxwhererownum<=8);--rownum3：update(更新數(shù)據(jù))1010123500,工作改為SQL>SQL>updatesetsalary=3500,job='Programmer'whereempno=1012;SQL>SQL>updateemp_xxxsetsalary=salary+1000wheredeptno=10;4：delete(刪除數(shù)據(jù))deleteemp_bak1whereempno=1002deleteemp_bak1wheredeptno=10deleteemp_bak1commit13】distinct33SQL>insertintoemp_bak1(empno,ename,salary)values(1015,'amy',4000);22SQL>insertintoemp_bak1(empno,ename,salary)values(1016,'rory',5000);11SQL>insertintoemp_bak1(empno,ename,salary)values(1017,'river',6000);2emp_bak2SQL>SQL>createtableemp_bak2selectdistinctempno,ename,salaryhiredate,job,bonus,deptno,mgrfromemp_bak1;SQL>SQL>droptableemp_bak1 --刪除表SQL>renameemp_bak2toemp_bak1 emp_bak2SQL>deletefromSQL>deletefromemp_bak13遍,揑入3SQL>insertintoemp_bak1(empno,ename,salary)values(1015,'amy',4000);2遍,揑入2SQL>insertintoemp_bak1(empno,ename,salary)values(1016,'rory',5000);1遍,揑入1SQL>insertintoemp_bak1(empno,ename,salary)values(1017,'river',6000);SQL>deleteSQL>deletefromwherewhererowidnotin(selectmax(rowid)fromgroupbyempno,ename,salary)練習(xí)開(kāi)啟一個(gè)會(huì)話A,創(chuàng)建表并插入1條數(shù)據(jù)(注意：丌commit2個(gè)會(huì)話B,在會(huì)話Acommit之前,會(huì)話BAcommitBAupdatecommitBAcommit)后,會(huì)話BAupdatecommitBdeleteAcommit),會(huì)話B如果提交(commitrollback),則createtablemytemp_xxx(idnumber(4))insertintomytemp_xxxvalues(3)savepointA--設(shè)置保存點(diǎn),名為insertintomytemp_xxxvalues(4)savepoint--設(shè)置保存點(diǎn),名為insertintomytemp_xxxvalues(5)rollbacktoA;--回滾到保存點(diǎn)A,注意：Aselect*frommytemp_xxx;--34、57：drop(191112077-1211SQL>selecta.table_name,fromuser_tablesajoinuser_objectsbona.table_name=b.object_namewhereb.created>'7-DEC-11'; SQL>droptableSQL>SQL>selectfromuser_tablesajoinuser_objectsbona.table_name=b.object_namewhereb.created<add_months(sysdate,-8：truncate(截取,截?cái)?練習(xí)9：alter(修改結(jié)構(gòu))22SQL>SQL>createtablemytemp_xxx(idnumber(4));SQL>altertablemytemp_xxxadd(namechar(10));SQL>altertablemytemp_xxxadd(passwordchar(4));SQL>SQL>altertablemytemp_xxxrenamecolumnpasswordtopwdSQL>SQL>altertablemytemp_xxxmodify(pwdchar(8))SQL>SQL>altertablemytemp_xxxdropcolumnpwdSQL>@在服務(wù)器端運(yùn)行執(zhí)行.sql(文件和sqlplus在同一臺(tái)機(jī)器上SQL>@1：創(chuàng)建一個(gè)ning.sql%提示符 2個(gè)用