Oracle數(shù)據(jù)庫(kù)Sql語(yǔ)句詳解大全 (2)ppt課件

1、OracleSQL第一章SELECT查詢.本章目的寫一條SELECT查詢語(yǔ)句在查詢中運(yùn)用表達(dá)式、運(yùn)算符對(duì)空值的處置對(duì)查詢字段起別名查詢字段的銜接.SELECT查詢根本語(yǔ)法SELECT FROM SQL SELECT * FROM s_emp請(qǐng)查詢出s_emp表中一切的員工信息:.查詢指定列SQL SELECT dept_id , salary FROM s_emp請(qǐng)查詢出s_emp表中一切的員工的部門ID，工資:.運(yùn)用算數(shù)表達(dá)式SQL SELECT salary*12 FROM s_emp請(qǐng)查詢出s_emp表中一切的員工的年薪:.運(yùn)用算數(shù)表達(dá)式括號(hào)可以改動(dòng)運(yùn)算符運(yùn)算的優(yōu)先順序:SQL SELE

2、CT 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 31200.列別名請(qǐng)查詢出s_emp表中一切的員工的姓名:SQL SELECT firname_name | last_name FROM s_empSQL SELECT firname_name | last_name “姓名 FROM s_emp請(qǐng)查詢出s_emp表中一切的員工的姓名:.空

3、值的處置請(qǐng)查詢出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;.去掉反復(fù)行請(qǐng)查詢出s_dept表的部門稱號(hào):SQL SELECTname 2 FROM s_dept;SQL SELECTDISTINCT name 2 FROM s_dept;.去掉多列反復(fù)行SQL SELECT DISTINCT dept_id, title 2

4、FROM s_emp;請(qǐng)查詢出s_emp表中一切的員工的部門ID及職稱:.小結(jié)查詢表的全部記錄查詢指定的列給列起別名NVL函數(shù)及字符串銜接符反復(fù)行的處置DistinctSELECT DISTINCT *,columnalias,.FROM table;.引言.第二章 條件查詢.本章目的WHERE條件查詢?cè)诓樵冎羞\(yùn)用表達(dá)式、運(yùn)算符運(yùn)用LIKE、BETWEEN、IN進(jìn)展模糊查詢.第一章內(nèi)容回想對(duì)員工表中信息進(jìn)展查詢，詳細(xì)要求如下： 1. 查詢s_emp表要求輸出員工姓名(firs_name、last_name)和實(shí)踐工資(根本工資+提成)：.查詢根本語(yǔ)法SELECT FROM WHERE 列稱號(hào)S

5、ELECT *表名FROMS_emp過濾條件WHEREdept_id= 41 請(qǐng)查詢出s_emp表中dept_id為41的員工信息:.WHERE條件查詢請(qǐng)查詢出s_emp表中l(wèi)ast_name為Smith的員工的信息:SELECT * FROM s_emp WHERE last_name = Smith請(qǐng)查詢出s_emp表中部門ID為50并且工資大于1500的員工的信息:SELECT * FROM s_emp WHERE salary1500 and dept_id=50.WHERE條件查詢-BETWEEN&IN請(qǐng)查詢出s_emp表中工資在1500到2000之間的員工信息：SELECT * F

6、ROM s_emp WHERE salary between 1500 and 2000請(qǐng)查詢出s_dept表中region_id為1，3的部門信息：SELECT * FROM s_dept WHERE region_id in (1,3).WHERE條件查詢-like請(qǐng)查詢出s_emp表中姓中含有字母a的員工信息：SELECT * FROM s_emp WHERE last_name like %a%請(qǐng)查詢出當(dāng)前用戶下一切以s_開頭的表： SELECT table_name FROM user_tables WHERE table_name like S_% escape 請(qǐng)查詢出s_emp

7、表姓中第二個(gè)字母為a的員工信息：SELECT * FROM s_emp WHERE last_name like _a%.空值的查詢查詢出s_emp表中非銷售職位的員工信息： SELECT * FROM s_emp WHERE commission_pct is null .課堂練習(xí)已建立好的S_emp表，對(duì)此表中的數(shù)據(jù)進(jìn)展模糊查詢，詳細(xì)要求如下：查詢一名last_name以“M開頭的員工，他的dept_id好似是3X查詢工資在1200至1500之間的員工查詢來(lái)自部門ID為(41，42，43)的員工.查詢結(jié)果排序查詢出s_emp表將部門ID為41的員工的工資按從高到低陳列顯示出來(lái)： SELEC

8、T * FROM s_emp WHERE dept_id=41 ORDER BY salary DESC SQL SELECTlast_name, dept_id, salary 2 FROM s_emp 3 ORDER BYdept_id, salary DESC;.小結(jié)Where條件查詢Betweenand & In & Like 模糊查詢對(duì)查詢結(jié)果排序SELECTDISTINCT *, column alias, .FROM tableWHEREcondition(s)ORDER BYcolumn, expr, alias ASC|DESC;.第三章 單行函數(shù).本章目的熟習(xí)各種類型單行函

9、數(shù)的運(yùn)用掌握轉(zhuǎn)換函數(shù)的運(yùn)用.兩種SQL函數(shù)單行函數(shù)CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函數(shù)GroupMULTI-ROWFUNCTION.字符函數(shù)LOWER將字符串轉(zhuǎn)換成小寫 UPPER將字符串變?yōu)榇髮?INITCAP將字符串的第一個(gè)字母變?yōu)榇髮?CONCAT拼接兩個(gè)字符串，與 | 一樣 SUBSTR取字符串的子串 LENGTH以字符給出字符串的長(zhǎng)度 NVL以一個(gè)值來(lái)交換空值 .字符函數(shù)舉例LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEINITCAP(SQL Cours

10、e)Sql Course SELECT * FROM s_emp WHERE last_name=PATEL SELECT * FROM s_emp WHERE UPPER( last_name)=PATEL.字符操作函數(shù)CONCAT(Good, String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)6.數(shù)字函數(shù)ROUND(value,precision) 按precision 精度4舍5入TRUNC(value,precision) 按precision 截取valueSQL SELECT round(55.5),round(-55.5),t

11、runc(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.16.Round&Trunc函數(shù)ROUND (45.923, 2)45.92ROUND (45.923, 0)46ROUND (45.923, -1)50TRUNC (45.92

12、3, 2)45.92TRUNC (45.923)45TRUNC (45.923, -1)40.日期函數(shù)MONTHS_BETWEEN(date2,date1) 給出 Date2 - date1的月數(shù)ADD_MONTHS添加或減去月份NEXT_DAY ( date,day) 給出日期date之后下一天的日期LAST_DAY(date)前往日期所在月的最后一天 .日期函數(shù)MONTHS_BETWEEN(01-SEP-95,11-JAN-94)19.774194ADD_MONTHS(11-JAN-94,6)11-JUL-94NEXT_DAY(01-SEP-95,FRIDAY)08-SEP-95LAST_

13、DAY(01-SEP-95)30-SEP-95.日期函數(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-95.轉(zhuǎn)換函數(shù)TO_CHAR(date, fmt) 轉(zhuǎn)換日期格式到字符串 用以下格式顯示字符為數(shù)字：9 代表一個(gè)數(shù)字.0 顯示前綴零.$ 根據(jù)本地言語(yǔ)環(huán)境顯示貨幣.L 采用當(dāng)?shù)刎泿欧?hào). 打印一個(gè)小數(shù)點(diǎn)., 千位計(jì)算法顯示.日期格式DY星期幾的縮寫Mon,Tue,.DAY星期幾的全拼Monday,

14、Tuesday,.D一周的星期幾，星期天=1，星期六=71,2,3,4,5,6,7DD一月的第幾天，1311，2，. 31W一個(gè)月的第幾周，1 51,2,3,4,5WW,IW一年的第幾周，一年的ISO的第幾周1,2,3,4,. 52MM兩為數(shù)的月01,02,03,.12MON月份的縮寫Jan,Feb,Mar ,.DecMONTH月份的全拼January,February,.YYYY,YYY,YY,Y四位數(shù)的年，三位數(shù)的年1999,999,99,9YEAR年的全拼Nineteen Ninety-nineRR當(dāng)前年份的后兩位數(shù)字01代表2001年HH,HH1212小時(shí)制，1121,2,3,.12H

15、H2424小時(shí)制，0230,1,2,3,.23MI一小時(shí)中的第幾分，0590,1,2,3.59SS一分中的第幾秒，0590,1,2,3,.59.To-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,99

16、9) 3 FROM s_ord 4 WHERE ship_date = 21-SEP-92;.To-char舉例查詢員工表中入職日期在7月份的員工信息：SELECT * FROM s_emp WHERE to_char(start_date,mm)=07.RR 日期格式Current Year1995199520012001Specified Date27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR Format1995202120211995YY Format1995191720212095If the specified two-digit year isIf

17、two 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.轉(zhuǎn)換函數(shù)TO_NUMBER(String) 轉(zhuǎn)換字符串到數(shù)字 TO_DATE(String) 轉(zhuǎn)換字符

18、串到日期格式 SELECT to_date(2021-09-22,yyyy-mm-dd) FROM dual .轉(zhuǎn)換函數(shù)的嵌套F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3.轉(zhuǎn)換函數(shù)嵌套舉例SQL SELECTlast_name, 2 NVL(TO_CHAR(manager_id),No Manager) 3 FROMs_emp 4 WHEREmanager_id IS NULL;查詢員工表中manager_id為空的員工查詢出來(lái)，并將空列的值置為“No Manager：.小結(jié)字

19、符函數(shù)日期函數(shù)數(shù)值函數(shù)轉(zhuǎn)換函數(shù).第四章 關(guān)聯(lián)查詢.本章目的在一張或多張表中運(yùn)用等值或非等值銜接運(yùn)用外銜接查詢自銜接查詢.等值銜接的種類等值銜接非等值銜接外銜接自銜接.S_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 Catchpole4410 Havel4511 Magee3112 Giljum3213 Sedeghi3314 Nguyen3415 Dumas3516 M

20、aduro41表間的關(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 Europe.簡(jiǎn)單關(guān)聯(lián)查詢的語(yǔ)法查詢員工表中l(wèi)ast_name為Biri的員工的last_name與部門稱號(hào)查詢出來(lái)：SELECT table.column, table.columnFROM ta

21、ble1, 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 = Biri.非等值銜接SQL SELECT e.ename, e.job, e.sal, s.grade2FROM emp e, salgrade s3WHERE e.sal BETWEEN s.losal AND s.hisal;.自銜接S_EMP (WORKER)S_EMP (MANAGER)LA

22、ST_NAME MANAGER_IDIDLAST_NAME-Ngao11VelasquezNagayama11Velasquez Ropeburn11Velasquez Urguhart22NgaoMenchu2 2Ngao Biri2 2Ngao Magee33NagaymaGiljum3 3Nagayma .Server.自銜接查詢員工表中l(wèi)ast_name為Biri的員工的last_name及其部門經(jīng)理稱號(hào)查詢出來(lái)：SQL SELECT worker.last_name| works for |manager.last_name 2 FROM s_emp worker, s_emp ma

23、nager 3 WHERE worker.manager_id = manager.id;.外銜接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, s_emp manager 3 WHERE worker.manager_i

24、d+ = 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;.內(nèi)銜接SQL SELECT 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 ,

25、2 FROM s_emp e inner join s_dept d on e.dept_id = d.id 3 WHERE e.last_name = Biri.小結(jié)等值銜接非等值銜接外銜接自銜接.第五章 組函數(shù).本章目的定義及有效的運(yùn)用組函數(shù)運(yùn)用Group By對(duì)查詢數(shù)據(jù)分組運(yùn)用HAVING子句對(duì)分組后的數(shù)據(jù)進(jìn)展過濾.運(yùn)用Group By的查詢語(yǔ)法SELECT column, group_functionFROMtableWHEREconditionGROUP BY group_by_expressionHAVINGgroup_conditionORDER BYcolumn;.常用組函數(shù)A

26、VG (DISTINCT|ALL|n)COUNT (DISTINCT|ALL|expr|*)MAX (DISTINCT|ALL|expr)MIN (DISTINCT|ALL|expr)SUM (DISTINCT|ALL|n).運(yùn)用舉例查詢s_emp表中一切員工的平均工資：SQL SELECT avgsalary 2 FROM s_emp查詢s_emp表中各個(gè)部門員工的平均工資及部門稱號(hào)：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 GROU

27、P BY dept_id；.運(yùn)用舉例查詢s_emp表中31部門一共有多少員工：SQL SELECTCOUNT(*) 2 FROMs_emp 3 WHERE dept_id = 31;查詢s_emp表中銷售人員的數(shù)量(提成率不為空的記錄個(gè)數(shù))：SQL SELECTCOUNT(commission_pct) 2 FROMs_emp.運(yùn)用舉例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

28、 ORDER BY sum(salary);查詢s_emp表中各個(gè)部門員工的平均工資,工資總和及部門稱號(hào)并按照工資總和排序：.運(yùn)用舉例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 and e.dept_id !=41 4 GROUP BY dept_id 5 ORDER BY sum(salary);查詢s_emp表中除41部門以外的部門員工的平均工資,工資總和及部門稱號(hào)并按照工資總和排序：SQL SELECT e.de

29、pt_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 HAVING e.dept_id !=41 6 ORDER BY sum(salary);.運(yùn)用舉例求平均工資高于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_

30、id = d.id 4 GROUP BY dept_id 5 HAVING avg(e.salary)1500 6 ORDER BY avg (e.salary);.課堂練習(xí)求不以“VP開頭職位的，各個(gè)職位中工資總和大于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);.小結(jié)SELECT column, group_

31、functionFROMtableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BYcolumn;.第六章 子查詢.子查詢查詢s_emp表中工資最低的員工的姓名：SQL SELECT minsalary 2 FROM s_empSQL SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工資上一條的運(yùn)轉(zhuǎn)結(jié)果SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM

32、 s_emp).子查詢查詢s_emp表中平均工資低于32部門的部門ID：SQL SELECT avgsalary 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,

33、s_dept 3 WHERE s_emp.dept_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 row.小結(jié)SELECTselect_listFROMtab

34、leWHEREexpr operator(SELECTselect_list FROMtable);.第七章 數(shù)據(jù)建模及數(shù)據(jù)庫(kù)設(shè)計(jì).本章目的了解系統(tǒng)開發(fā)的步驟數(shù)據(jù)關(guān)系的定義了解實(shí)體關(guān)系映射圖E-R圖.系統(tǒng)開發(fā)步驟StrategyandAnalysisDesignBuildandDocumentTransitionProduction.數(shù)據(jù)模型Model of systemin clients mindEntity model ofclients modelTable model ofentity modelTables on diskServer.Entity Relationship Mod

35、eling 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 two entitiesExamples: orders a

36、nd items, customers and sales representatives.Entity 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 not yet have assigned customers . . .CU

37、STOMER#* id*nameophoneEMPLOYEE#* id*last nameofirst nameassigned tothe sales rep to.Relationship 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 direction and a degree of one and only one in the oth

38、er 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.數(shù)據(jù)庫(kù)3范式數(shù)據(jù)庫(kù)中的每一列都是不可再分的根本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值數(shù)據(jù)庫(kù)表中不存在非關(guān)鍵字段對(duì)任何候選關(guān)鍵字段的部分依賴數(shù)據(jù)庫(kù)表中不存在非關(guān)鍵字段對(duì)任何候選關(guān)鍵字段的傳送援用.約束類型PK Primary Key

39、 獨(dú)一且非空FK Foreign Key 外鍵約束，值援用另一張表 曾經(jīng)存在的數(shù)據(jù)UKUnique Key 獨(dú)一且可為空NOT NULL 非空.約束舉例IDLAST_NAMEFIRST_NAME.DEPT_ID.1VelasquezCarmen502NgaoLaDoris413NagayamaMidori314Quick-To-SeeMark105RopeburnAudry50IDNAMEREGION_ID10Finance131Sales141Operations150Administration1Primary KeyForeign KeyS_EMP TableS_DEPT TablePr

40、imary Key.第八章 創(chuàng)建表.本章目的掌握創(chuàng)建表的語(yǔ)法Oracle的數(shù)據(jù)類型運(yùn)用約束.數(shù)據(jù)構(gòu)造一個(gè)Oracle數(shù)據(jù)庫(kù)包含以下幾種數(shù)據(jù)構(gòu)造： Table 存儲(chǔ)數(shù)據(jù) View 從一個(gè)表或多個(gè)表的數(shù)句中得到的子集 Sequence 生成主鍵值 Index 提高查詢性能.建表語(yǔ)法CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint, . table_constraint);.Oracle的數(shù)據(jù)類型Char(size) 定長(zhǎng)字符型，字符長(zhǎng)度不夠自動(dòng)在右邊加空格符號(hào) Varchar2(size) 可變長(zhǎng)字符型，

41、大小必需指定 Number(m,n) 數(shù)字型，可存放實(shí)數(shù)和整數(shù) Date 日期類型Blob 2進(jìn)制大對(duì)象其最大大小為4 GB 此數(shù)據(jù)類型映射到 Byte 類型的 Array。 Clob 2進(jìn)制大對(duì)象其最大大小為4 GB 此數(shù)據(jù)類型映射到 String .命名規(guī)范必需以字母開頭1-30個(gè)字符長(zhǎng)度只允許包含AZ, az, 09, _, $, and #在一個(gè)數(shù)據(jù)庫(kù)保證命名的獨(dú)一不能運(yùn)用Oracle內(nèi)部的關(guān)鍵字.建表舉例CREATE TABLE Mytest( id number， name varchar2(32);CREATE TABLE Mytest( id number deault 11，

42、 name varchar2(32);.建表運(yùn)用約束舉例CREATE TABLE Mytest( id number check(id10), name varchar2(32);CREATE TABLE Mytest( id number check(id10), name varchar2(32) not null);CREATE TABLE Mytest( id number UNIQUE, name varchar2(32) not null);.建表主鍵約束舉例CREATE TABLE Mytest( id number primary key, name varchar2(32)

43、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,n);.建表外鍵約束舉例CREATE TABLE parent( id number primary key, name varchar2(32);CREATE TABLE child( id number primary key, p_id number references parent(id);CREATE

44、 TABLE child( id number primary key, p_id number , foreign key(p_id) references parent(id);.約束命名CREATE TABLE child( id number constraint mytest_pk primary key, p_id number , foreign key(p_id) references parent(id);.查看表的約束SELECT * FROM user_constraints WHERE table_name =CHILD CONSTRAINT_TYPE C -check

45、 P -primary key R -forgien key U -unique.級(jí)聯(lián)刪除CREATE TABLE child( id number primary key, p_id number references parent(id) on delete cascade);CREATE TABLE child( id number primary key, p_id number references parent(id) on delete set null);.子查詢創(chuàng)建表CREATE TABLE emp_41 as ( select * from s_emp where dept

46、_id =41);.第九章 對(duì)數(shù)據(jù)的操作.本章目的在已創(chuàng)建表中插入新的數(shù)據(jù)修正曾經(jīng)存在的數(shù)據(jù)刪除表中的數(shù)據(jù)了解事物控制及其重要性.DML命令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.Comma

47、ndINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACK.Insert插入語(yǔ)法INSERT INTOtable (column , column.)VALUES(value , value.);.插入舉例CREATE TABLE Mytest( id number primary key, name varchar2(32), birth Date);INSERT INTOmytest (id,name,brith)VALUES(1,BluesWang,?);.更新語(yǔ)法UPDATEtableSETcolumn = value , column = valueWHE

48、REcondition;.刪除語(yǔ)法DELETE FROMtableWHEREcondition;.Database 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 o

49、r ROLLBACKDDL or DCL command executes (automatic commit)Errors, exit, or system crash.復(fù)原點(diǎn)SQL UPDATE.SQL SAVEPOINT update_done;Savepoint created.SQL INSERT.SQL ROLLBACK TO update_done;Rollback complete.Controlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROL

50、LBACKROLLBACK to AROLLBACK to B.第十章 修正表構(gòu)造及添加約束.本章目的添加或者修正列添加刪除約束刪除表刪除表一切的數(shù)據(jù).添加列ALTER TABLE tableADD(column datatype DEFAULT exprNOT NULL, column datatype.);.添加列舉例向下表mytest添加名為age，類型為number的一列：ALTER TABLE mytest ADD (age number);再向下表mytest添加名為salary,類型為number(9,1)缺省值為8888的一列：ALTER TABLE mytest ADD (s

51、alary number(9,1) default 8888);.刪除列ALTER TABLE tableDROPcolumn , column .;.刪除列舉例將表mytest的salary列刪除：ALTER TABLE mytest drop column salary ;.修正列ALTER TABLEtableMODIFY(column datatype DEFAULT exprNOT NULL, column datatype.);.修正列舉例將表mytest的age列由原來(lái)的number類型更改為number4，2類型：ALTER TABLE mytest modify (age n

52、umber(4,2) ;.添加約束SQL ALTER TABLEtable 2 ADD CONSTRAINT constraint type (column);.添加約束舉例將表mytest的id列添加主鍵約束：ALTER TABLE mytest ADD constraints mytest_pk primary key(id);將表child的p_id列添加外鍵約束：ALTER TABLE child ADD constraints c_fk foreign key(p_id) references parent(id);.刪除約束舉例SQL ALTER TABLEchild 2 DROP

53、 CONSTRAINTc_fk;.刪除表及表的重命名DROP TABLE table CASCADE CONSTRAINTS;SQL RENAME 表名 TO 新名;.刪除表數(shù)據(jù)SQL TRUNCATE TABLE 表名;.小結(jié)CommandCREATE TABLEALTER TABLEDROP TABLERENAMETRUNCATEDescriptionCreates a table and indicated constraints.Modifies table structures and constraints.Removes the rows and table structure.

54、Changes the name of a table, view, sequence, or synonym.Removes all rows from a table and releases the storage space.第十一章 創(chuàng)建序列.本章目的掌握如何運(yùn)用sequences創(chuàng)建sequences修正sequences刪除sequences.創(chuàng)建sequences語(yǔ)法CREATE SEQUENCE nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLE

55、CACHE n | NOCACHE.創(chuàng)建序列舉例SQL CREATE SEQUENCE s_dept_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6 NOCYCLE;Sequence created.序列舉例利用創(chuàng)建好的Sequence向mytest表中添加數(shù)據(jù)：insert into mytest values(s.nextval,a)查看當(dāng)前序列的值：SELECT s.currval FROM dual.修正sequences語(yǔ)法ALTER SEQUENCE nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE.刪除sequences語(yǔ)法