1234*5*注 練習1：非等值連接(on后面的條件丌是等值操作 練習2：insert(插入數據 使用方法：丌寫列 使用方法：寫列 練習3：update(更新數據 練習4：delete(刪除數據 練習5：多個會話操作同一張 練習 練習7：drop(刪除結構和全部的表數據 練習8：truncate(截取,截斷 練習9：alter(修改結構 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練習2：insert(揑入數據)--如果有數據沒提供,用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】表：只結構,不數SQL>SQL>createtablesalgrade_yyywhere1<>1【案例5】表：一部分數SQL>createtableemp_yyyselectempnoenamesalary*12 fromemp_xxxwheredeptno=10;【案例6】表：一部分數SQL>createtableemp_count(didemp_num fromemp_xxxgroupbydeptno7emp10如果有一張表emp1第1 創建一個空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(更新數據)1010123500,工作改為SQL>SQL>updatesetsalary=3500,job='Programmer'whereempno=1012;SQL>SQL>updateemp_xxxsetsalary=salary+1000wheredeptno=10;4：delete(刪除數據)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)練習開啟一個會話A,創建表并插入1條數據(注意：丌commit2個會話B,在會話Acommit之前,會話BAcommitBAupdatecommitBAcommit)后,會話BAupdatecommitBdeleteAcommit),會話B如果提交(commitrollback),則createtablemytemp_xxx(idnumber(4))insertintomytemp_xxxvalues(3)savepointA--設置保存點,名為insertintomytemp_xxxvalues(4)savepoint--設置保存點,名為insertintomytemp_xxxvalues(5)rollbacktoA;--回滾到保存點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(截取,截斷)練習9：alter(修改結構)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>@在服務器端運行執行.sql(文件和sqlplus在同一臺機器上SQL>@1：創建一個ning.sql%提示符 2個用