Oracle數(shù)據(jù)庫Sql語句詳解大全課件

1、Oracle數(shù)據(jù)庫Sql語句詳解大全Oracle數(shù)據(jù)庫Sql語句詳解大全Oracle數(shù)據(jù)庫Sql語句詳解大全第一章SELECT查詢2021/2/42第一章SELECT查詢2021/2/42本章目標寫一條SELECT查詢語句在查詢中使用表達式、運算符對空值的處理對查詢字段起別名查詢字段的連接2021/2/43SELECT查詢基本語法SELECT FROM SQL SELECT * FROM s_emp請查詢出s_emp表中所有的員工信息:2021/2/44查詢指定列SQL SELECT dept_id , salary FROM s_emp請查詢出s_emp表中所有的員工的部門ID，工資:202

2、1/2/45運用算數(shù)表達式SQL SELECT salary*12 FROM s_emp請查詢出s_emp表中所有的員工的年薪:2021/2/46運用算數(shù)表達式括號可以改變運算符運算的優(yōu)先順序:SQL SELECT last_name, salary, 12 * salary + 100 2 FROM s_emp;.Velasquez 2500 30100SQL SELECT last_name, salary, 12 * (salary + 100) 2 FROM s_emp;.Velasquez 2500 312002021/2/47列別名請查詢出s_emp表中所有的員工的姓名:SQL S

3、ELECT firname_name | last_name FROM s_empSQL SELECT firname_name | last_name “姓名” FROM s_emp請查詢出s_emp表中所有的員工的姓名:2021/2/48空值的處理請查詢出s_emp表中所有的員工的工資:SQL SELECT last_name, salary*commission_pct/100 “工資” 2 FROM s_emp;SQL SELECT last_name , salary+salary*NVL(commission_pct,0)/100 2 FROM s_emp;2021/2/49去掉重

4、復行請查詢出s_dept表的部門名稱:SQL SELECTname 2 FROM s_dept;SQL SELECTDISTINCT name 2 FROM s_dept;2021/2/410去掉多列重復行SQL SELECT DISTINCT dept_id, title 2 FROM s_emp;請查詢出s_emp表中所有的員工的部門ID及職稱:2021/2/411小結(jié)查詢表的全部記錄查詢指定的列給列起別名NVL函數(shù)及字符串連接符重復行的處理DistinctSELECT DISTINCT *,columnalias,.FROM table;2021/2/412引言2021/2/413第二章

5、 條件查詢2021/2/414本章目標WHERE條件查詢在查詢中使用表達式、運算符使用LIKE、BETWEEN、IN進行模糊查詢2021/2/415第一章內(nèi)容回顧對員工表中信息進行查詢，具體要求如下： 1. 查詢s_emp表要求輸出員工姓名(firs_name、last_name)和實際工資(基本工資+提成)：2021/2/416查詢基本語法SELECT FROM WHERE 列名稱SELECT *表名FROMS_emp過濾條件WHEREdept_id= 41 請查詢出s_emp表中dept_id為41的員工信息:2021/2/417WHERE條件查詢請查詢出s_emp表中l(wèi)ast_name為

6、Smith的員工的信息:SELECT * FROM s_emp WHERE last_name = Smith請查詢出s_emp表中部門ID為50并且工資大于1500的員工的信息:SELECT * FROM s_emp WHERE salary1500 and dept_id=502021/2/418WHERE條件查詢-BETWEEN&IN請查詢出s_emp表中工資在1500到2000之間的員工信息：SELECT * FROM s_emp WHERE salary between 1500 and 2000請查詢出s_dept表中region_id為1，3的部門信息：SELECT * FROM

7、 s_dept WHERE region_id in (1,3)2021/2/419WHERE條件查詢-like請查詢出s_emp表中姓中含有字母a的員工信息：SELECT * FROM s_emp WHERE last_name like %a%請查詢出當前用戶下所有以s_開頭的表： SELECT table_name FROM user_tables WHERE table_name like S_% escape 請查詢出s_emp表姓中第二個字母為a的員工信息：SELECT * FROM s_emp WHERE last_name like _a%2021/2/420空值的查詢查詢出s

8、_emp表中非銷售職位的員工信息： SELECT * FROM s_emp WHERE commission_pct is null 2021/2/421課堂練習已建立好的S_emp表，對此表中的數(shù)據(jù)進行模糊查詢，具體要求如下：查詢一名last_name以“M”開頭的員工，他的dept_id好像是3X查詢工資在1200至1500之間的員工查詢來自部門ID為(41，42，43)的員工2021/2/422查詢結(jié)果排序查詢出s_emp表將部門ID為41的員工的工資按從高到低排列顯示出來： SELECT * FROM s_emp WHERE dept_id=41 ORDER BY salary DES

9、C SQL SELECTlast_name, dept_id, salary 2 FROM s_emp 3 ORDER BYdept_id, salary DESC;2021/2/423小結(jié)Where條件查詢Betweenand & In & Like 模糊查詢對查詢結(jié)果排序SELECTDISTINCT *, column alias, .FROM tableWHEREcondition(s)ORDER BYcolumn, expr, alias ASC|DESC;2021/2/424第三章 單行函數(shù)2021/2/425本章目標熟悉各種類型單行函數(shù)的使用掌握轉(zhuǎn)換函數(shù)的使用2021/2/426兩

10、種SQL函數(shù)單行函數(shù)CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函數(shù)GroupMULTI-ROWFUNCTION2021/2/427字符函數(shù)LOWER將字符串轉(zhuǎn)換成小寫 UPPER將字符串變?yōu)榇髮?INITCAP將字符串的第一個字母變?yōu)榇髮?CONCAT拼接兩個字符串，與 | 相同 SUBSTR取字符串的子串 LENGTH以字符給出字符串的長度 NVL以一個值來替換空值 2021/2/428字符函數(shù)舉例LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEINITCAP(SQL Co

11、urse)Sql Course SELECT * FROM s_emp WHERE last_name=PATEL SELECT * FROM s_emp WHERE UPPER( last_name)=PATEL2021/2/429字符操作函數(shù)CONCAT(Good, String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)62021/2/430數(shù)字函數(shù)ROUND(value,precision) 按precision 精度4舍5入TRUNC(value,precision) 按precision 截取valueSQL SELECT round

12、(55.5),round(-55.5),trunc(55.5),trunc(-55.5)FROM dual;round(55.5) round(-55.5) trunc(55.5) trunc(-55.5)- - - - 56 -56 55 -55SQL SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) FROM dual; TRUNC1 TRUNC(124.16666,2)- - 100 124.162021/2/431Round&Trunc函數(shù)ROUND (45.923, 2)45.92ROUND (45.923, 0)46RO

13、UND (45.923, -1)50TRUNC (45.923, 2)45.92TRUNC (45.923)45TRUNC (45.923, -1)402021/2/432日期函數(shù)MONTHS_BETWEEN(date2,date1) 給出 Date2 - date1的月數(shù)ADD_MONTHS增加或減去月份NEXT_DAY ( date,day) 給出日期date之后下一天的日期LAST_DAY(date)返回日期所在月的最后一天 2021/2/433日期函數(shù)MONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194ADD_MONTHS(11-JAN-94,6)1

14、1-JUL-94NEXT_DAY(01-SEP-95,FRIDAY)08-SEP-95LAST_DAY(01-SEP-95)30-SEP-952021/2/434日期函數(shù)ROUND(25-MAY-95,MONTH)01-JUN-95ROUND(25-MAY-95 ,YEAR)01-JAN-95TRUNC(25-MAY-95 ,MONTH)01-MAY-95TRUNC(25-MAY-95 ,YEAR)01-JAN-952021/2/435轉(zhuǎn)換函數(shù)TO_CHAR(date, fmt) 轉(zhuǎn)換日期格式到字符串 用下列格式顯示字符為數(shù)字：9 代表一個數(shù)字.0 顯示前綴零.$ 根據(jù)本地語言環(huán)境顯示貨幣.L

15、 采用當?shù)刎泿欧? 打印一個小數(shù)點., 千位計算法顯示.2021/2/436日期格式2021/2/437To-char舉例SQL SELECT last_name, TO_CHAR(start_date, 2 fmDdspth of Month YYYY fmHH:MI:SS AM) HIREDATE 3 FROMs_emp 4 WHERE start_date LIKE %91;SQL SELECT Order | TO_CHAR(id) | 2 was filled for a total of | TO_CHAR(total,fm$9,999,999) 3 FROM s_ord 4 W

16、HERE ship_date = 21-SEP-92;2021/2/438To-char舉例查詢員工表中入職日期在7月份的員工信息：SELECT * FROM s_emp WHERE to_char(start_date,mm)=072021/2/439RR 日期格式Current Year1995199520012001Specified Date27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR Format1995201720171995YY Format1995191720172095If the specified two-digit year isIf t

17、wo digits of the current year are0-490-4950-9950-99The return date is in the current century.The return date is in the century after the current one.The return date is in the century before the current one.The return date is in the current century.2021/2/440轉(zhuǎn)換函數(shù)TO_NUMBER(String) 轉(zhuǎn)換字符串到數(shù)字 TO_DATE(Str

18、ing) 轉(zhuǎn)換字符串到日期格式 SELECT to_date(2009-09-22,yyyy-mm-dd) FROM dual 2021/2/441轉(zhuǎn)換函數(shù)的嵌套F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 32021/2/442轉(zhuǎn)換函數(shù)嵌套舉例SQL SELECTlast_name, 2 NVL(TO_CHAR(manager_id),No Manager) 3 FROMs_emp 4 WHEREmanager_id IS NULL;查詢員工表中manager_id為空的員工查詢

19、出來，并將空列的值置為“No Manager”：2021/2/443小結(jié)字符函數(shù)日期函數(shù)數(shù)值函數(shù)轉(zhuǎn)換函數(shù)2021/2/444第四章 關(guān)聯(lián)查詢2021/2/445本章目標在一張或多張表中使用等值或非等值連接使用外連接查詢自連接查詢2021/2/446等值連接的種類等值連接非等值連接外連接自連接2021/2/447S_EMP TableID LAST_NAME DEPT_ID- - - 1 Velasquez50 2 Ngao41 3 Nagayama31 4 Quick-To-See10 5 Ropeburn50 6 Urguhart41 7 Menchu42 8 Biri43 9 Catchp

20、ole4410 Havel4511 Magee3112 Giljum3213 Sedeghi3314 Nguyen3415 Dumas3516 Maduro41表間的關(guān)系S_DEPT TableID NAME REGION_ID- - -30 Finance 131 Sales 132 Sales 243 Operations 350 Administration 1S_REGION TableID NAME- - 1 North America 2 South America 3 Africa / Middle East 4 Asia 5 Europe2021/2/448簡單關(guān)聯(lián)查詢的語法查

21、詢員工表中l(wèi)ast_name為Biri的員工的last_name與部門名稱查詢出來：SELECT table.column, table.columnFROM table1, table2WHERE table1.column1 = table2.column2SQL SELECT e.last_name , 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = Biri2021/2/449非等值連接SQL SELECT e.ename, e.job, e.sal, s.grade2FROM em

22、p e, salgrade s3WHERE e.sal BETWEEN s.losal AND s.hisal;2021/2/450自連接S_EMP (WORKER)S_EMP (MANAGER)LAST_NAME MANAGER_IDIDLAST_NAME-Ngao11VelasquezNagayama11Velasquez Ropeburn11Velasquez Urguhart22NgaoMenchu2 2Ngao Biri2 2Ngao Magee33NagaymaGiljum3 3Nagayma .Server2021/2/451自連接查詢員工表中l(wèi)ast_name為Biri的員工的

23、last_name及其部門經(jīng)理名稱查詢出來：SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = manager.id;2021/2/452外連接SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = manager.id （

24、+）;SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id（+） = manager.id;SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker left outer join s_emp manager 3 on worker.manager_id= manager.id;2021/2/453內(nèi)連接SQL SE

25、LECT e.last_name , 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = BiriSQL SELECT e.last_name , 2 FROM s_emp e inner join s_dept d on e.dept_id = d.id 3 WHERE e.last_name = Biri2021/2/454小結(jié)等值連接非等值連接外連接自連接2021/2/455第五章 組函數(shù)2021/2/456本章目標定義及有效的使用組函數(shù)使用Group By對查詢數(shù)據(jù)分組使用

26、HAVING子句對分組后的數(shù)據(jù)進行過濾2021/2/457使用Group By的查詢語法SELECT column, group_functionFROMtableWHEREconditionGROUP BY group_by_expressionHAVINGgroup_conditionORDER BYcolumn;2021/2/458常用組函數(shù)AVG (DISTINCT|ALL|n)COUNT (DISTINCT|ALL|expr|*)MAX (DISTINCT|ALL|expr)MIN (DISTINCT|ALL|expr)SUM (DISTINCT|ALL|n)2021/2/459應用

27、舉例查詢s_emp表中所有員工的平均工資：SQL SELECT avg（salary） 2 FROM s_emp查詢s_emp表中各個部門員工的平均工資及部門名稱：SQL SELECT e.dept_id, max(),avg(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id；2021/2/460應用舉例查詢s_emp表中31部門一共有多少員工：SQL SELECTCOUNT(*) 2 FROMs_emp 3 WHERE dept_id = 31;查詢s_emp表中銷售人員

28、的數(shù)量(提成率不為空的記錄個數(shù))：SQL SELECTCOUNT(commission_pct) 2 FROMs_emp2021/2/461應用舉例SQL SELECT e.dept_id, max(),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 ORDER BY sum(salary);查詢s_emp表中各個部門員工的平均工資,工資總和及部門名稱并按照工資總和排序：2021/2/462應用舉例SQL SELECT e.dept_i

29、d, max(),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.dept_id !=41 4 GROUP BY dept_id 5 ORDER BY sum(salary);查詢s_emp表中除41部門以外的部門員工的平均工資,工資總和及部門名稱并按照工資總和排序：SQL SELECT e.dept_id, max(),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_

30、id = d.id 4 GROUP BY dept_id 5 HAVING e.dept_id !=41 6 ORDER BY sum(salary);2021/2/463應用舉例求平均工資高于1500的部門的工資總和，最高工資，最低工資：SQL SELECT e.dept_id, max(),avg(e.salary),sum(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING avg(e.salary)1500 6 ORDER BY avg (e.sal

31、ary);2021/2/464課堂練習求不以“VP”開頭職位的，各個職位中工資總和大于5000的職位及工資總和，并按工資總和排序：SQL SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE VP% 4 GROUP BY title 5 HAVING SUM(salary) 5000 6 ORDER BY SUM(salary);2021/2/465小結(jié)SELECT column, group_functionFROMtableWHERE conditionGROUP BY group_by_expressi

32、onHAVING group_conditionORDER BYcolumn;2021/2/466第六章 子查詢2021/2/467子查詢查詢s_emp表中工資最低的員工的姓名：SQL SELECT min（salary） 2 FROM s_empSQL SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工資（上一條的運行結(jié)果）SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM s_emp)2021/2/468子查詢查詢s_emp表中平均工資低于3

33、2部門的部門ID：SQL SELECT avg（salary） 2 FROM s_emp 3 WHERE dept_id = 32;SQL SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary) SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary) SELECT dept_id,avg(salary),name 2 FROM s_emp,s_dept 3 WHERE s_emp.dept

34、_id = s_dept.id 4 GROUP BY dept_id,name 5 HAVING avg(salary) SELECTlast_name, first_name, title 2 FROMs_emp 3 WHEREdept_in = 4 (SELECT ID 5FROM s_dept 6WHERE name = Finance 7 OR region_id = 2);ORA-01427:single-row subquery returns more than one row2021/2/471小結(jié)SELECTselect_listFROMtableWHEREexpr oper

35、ator(SELECTselect_list FROMtable);2021/2/472第七章 數(shù)據(jù)建模及數(shù)據(jù)庫設(shè)計2021/2/473本章目標了解系統(tǒng)開發(fā)的步驟數(shù)據(jù)關(guān)系的定義理解實體關(guān)系映射圖（E-R圖）2021/2/474系統(tǒng)開發(fā)步驟StrategyandAnalysisDesignBuildandDocumentTransitionProduction2021/2/475數(shù)據(jù)模型Model of systemin clients mindEntity model ofclients modelTable model ofentity modelTables on diskServer20

36、21/2/476Entity Relationship Modeling ConceptsEntityA thing of significance about which information needs to be knownExamples: customers, sales representatives, ordersAttributeSomething that describes or qualifies an entityExamples: name, phone, identification numberRelationshipAn association between

37、 two entitiesExamples: orders and items, customers and sales representatives2021/2/477Entity Relationship ModelCreate an entity relationship diagram from business specifications or narratives.Scenario. . . Assign one or more customers to a sales representative . . . . . Some sales representatives do

38、 not yet have assigned customers . . .CUSTOMER#* id*nameophoneEMPLOYEE#* id*last nameofirst nameassigned tothe sales rep to2021/2/478Relationship TypesOne-to-oneHave a degree of one and only one in both directions.Are rare.Example: Husband and wife.Many-to-oneHave a degree of one or more in one dire

39、ction and a degree of one and only one in the other direction.Are very common.Example: passengers and plane.Many-to-manyHave a degree of one or more in both directions.Are resolved with an intersection entity.Example: Employees and skills.2021/2/479數(shù)據(jù)庫3范式數(shù)據(jù)庫中的每一列都是不可再分的基本數(shù)據(jù)項，同一列中不能有多個值數(shù)據(jù)庫表中不存在非關(guān)鍵字段對

40、任何候選關(guān)鍵字段的部分依賴數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的傳遞引用2021/2/480約束類型PK Primary Key 唯一且非空FK Foreign Key 外鍵約束，值引用另一張表 已經(jīng)存在的數(shù)據(jù)UKUnique Key 唯一且可為空NOT NULL 非空2021/2/481約束舉例IDLAST_NAMEFIRST_NAME.DEPT_ID.1VelasquezCarmen502NgaoLaDoris413NagayamaMidori314Quick-To-SeeMark105RopeburnAudry50IDNAMEREGION_ID10Finance131Sales1

41、41Operations150Administration1Primary KeyForeign KeyS_EMP TableS_DEPT TablePrimary Key2021/2/482第八章 創(chuàng)建表2021/2/483本章目標掌握創(chuàng)建表的語法Oracle的數(shù)據(jù)類型使用約束2021/2/484數(shù)據(jù)結(jié)構(gòu)一個Oracle數(shù)據(jù)庫包含下列幾種數(shù)據(jù)結(jié)構(gòu)： Table 存儲數(shù)據(jù) View 從一個表或多個表的數(shù)句中得到的子集 Sequence 生成主鍵值 Index 提高查詢性能2021/2/485建表語法CREATE TABLE schema.table(column datatype DEFAUL

42、T exprcolumn_constraint, . table_constraint);2021/2/486Oracle的數(shù)據(jù)類型Char(size) 定長字符型，字符長度不夠自動在右邊加空格符號 Varchar2(size) 可變長字符型，大小必須指定 Number(m,n) 數(shù)字型，可存放實數(shù)和整數(shù) Date 日期類型Blob 2進制大對象其最大大小為4 GB 此數(shù)據(jù)類型映射到 Byte 類型的 Array。 Clob 2進制大對象其最大大小為4 GB 此數(shù)據(jù)類型映射到 String 2021/2/487命名規(guī)范必須以字母開頭1-30個字符長度只允許包含AZ, az, 09, _, $,

43、 and #在一個數(shù)據(jù)庫保證命名的唯一不能使用Oracle內(nèi)部的關(guān)鍵字2021/2/488建表舉例CREATE TABLE Mytest( id number， name varchar2(32);CREATE TABLE Mytest( id number deault 11， name varchar2(32);2021/2/489建表使用約束舉例CREATE TABLE Mytest( id number check(id10), name varchar2(32);CREATE TABLE Mytest( id number check(id10), name varchar2(32)

44、 not null);CREATE TABLE Mytest( id number UNIQUE, name varchar2(32) not null);2021/2/490建表主鍵約束舉例CREATE TABLE Mytest( id number primary key, name varchar2(32) not null);CREATE TABLE Mytest( id number, name varchar2(32) not null, primary key (id);CREATE TABLE Mytest( m number, n number, primary key (m

45、,n);2021/2/491建表外鍵約束舉例CREATE TABLE parent( id number primary key, name varchar2(32);CREATE TABLE child( id number primary key, p_id number references parent(id);CREATE TABLE child( id number primary key, p_id number , foreign key(p_id) references parent(id);2021/2/492約束命名CREATE TABLE child( id numbe

46、r constraint mytest_pk primary key, p_id number , foreign key(p_id) references parent(id);2021/2/493查看表的約束SELECT * FROM user_constraints WHERE table_name =CHILD CONSTRAINT_TYPE C -check P -primary key R -forgien key U -unique2021/2/494級聯(lián)刪除CREATE TABLE child( id number primary key, p_id number refere

47、nces parent(id) on delete cascade);CREATE TABLE child( id number primary key, p_id number references parent(id) on delete set null);2021/2/495子查詢創(chuàng)建表CREATE TABLE emp_41 as ( select * from s_emp where dept_id =41);2021/2/496第九章 對數(shù)據(jù)的操作2021/2/497本章目標在已創(chuàng)建表中插入新的數(shù)據(jù)修改已經(jīng)存在的數(shù)據(jù)刪除表中的數(shù)據(jù)理解事物控制及其重要性2021/2/498DML命令

48、DescriptionAdds a new row to the table.Modifies existing rows in the table.Removes existing rows from the table.Makes all pending changes permanent.Allows a rollback to that savepoint marker.Discards all pending data changes.CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACK2021/2/499Insert插入語法INSERT

49、INTOtable (column , column.)VALUES(value , value.);2021/2/4100插入舉例CREATE TABLE Mytest( id number primary key, name varchar2(32), birth Date);INSERT INTOmytest (id,name,brith)VALUES(1,BluesWang,?);2021/2/4101更新語法UPDATEtableSETcolumn = value , column = valueWHEREcondition;2021/2/4102刪除語法DELETE FROMtab

50、leWHEREcondition;2021/2/4103Database TransactionsContain one of the following statements:DML commands that make up one consistent change to the dataOne DDL commandOne DCL commandBegin when the first executable SQL command is executed.End with one of the following events:COMMIT or ROLLBACKDDL or DCL

51、command executes (automatic commit)Errors, exit, or system crash2021/2/4104還原點SQL UPDATE.SQL SAVEPOINT update_done;Savepoint created.SQL INSERT.SQL ROLLBACK TO update_done;Rollback complete.2021/2/4105Controlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROL

52、LBACKROLLBACK to AROLLBACK to B2021/2/4106第十章 修改表結(jié)構(gòu)及添加約束2021/2/4107本章目標增加或者修改列添加刪除約束刪除表刪除表所有的數(shù)據(jù)2021/2/4108添加列ALTER TABLE tableADD(column datatype DEFAULT exprNOT NULL, column datatype.);2021/2/4109添加列舉例向下表mytest添加名為age，類型為number的一列：ALTER TABLE mytest ADD (age number);再向下表mytest添加名為salary,類型為number(9

53、,1)缺省值為8888的一列：ALTER TABLE mytest ADD (salary number(9,1) default 8888);2021/2/4110刪除列ALTER TABLE tableDROPcolumn , column .;2021/2/4111刪除列舉例將表mytest的salary列刪除：ALTER TABLE mytest drop column salary ;2021/2/4112修改列ALTER TABLEtableMODIFY(column datatype DEFAULT exprNOT NULL, column datatype.);2021/2/4

54、113修改列舉例將表mytest的age列由原來的number類型更改為number（4，2）類型：ALTER TABLE mytest modify (age number(4,2) ;2021/2/4114添加約束SQL ALTER TABLEtable 2 ADD CONSTRAINT constraint type (column);2021/2/4115添加約束舉例將表mytest的id列添加主鍵約束：ALTER TABLE mytest ADD constraints mytest_pk primary key(id);將表child的p_id列添加外鍵約束：ALTER TABLE

55、 child ADD constraints c_fk foreign key(p_id) references parent(id);2021/2/4116刪除約束舉例SQL ALTER TABLEchild 2 DROP CONSTRAINTc_fk;2021/2/4117刪除表及表的重命名DROP TABLE table CASCADE CONSTRAINTS;SQL RENAME 表名 TO 新名;2021/2/4118刪除表數(shù)據(jù)SQL TRUNCATE TABLE 表名;2021/2/4119小結(jié)CommandCREATE TABLEALTER TABLEDROP TABLERENA

56、METRUNCATEDescriptionCreates a table and indicated constraints.Modifies table structures and constraints.Removes the rows and table structure.Changes the name of a table, view, sequence, or synonym.Removes all rows from a table and releases the storage space.2021/2/4120第十一章 創(chuàng)建序列2021/2/4121本章目標掌握如何使用

57、sequences創(chuàng)建sequences修改sequences刪除sequences2021/2/4122創(chuàng)建sequences語法CREATE SEQUENCE nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE2021/2/4123創(chuàng)建序列舉例SQL CREATE SEQUENCE s_dept_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6

58、 NOCYCLE;Sequence created.2021/2/4124序列舉例利用創(chuàng)建好的Sequence向mytest表中添加數(shù)據(jù)：insert into mytest values(s.nextval,a)查看當前序列的值：SELECT s.currval FROM dual2021/2/4125修改sequences語法ALTER SEQUENCE nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE2021/2/4126刪除sequences語法DROP SEQUENCE name2021/2