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

1、OracleSQL第一章SELECT查詢.本章目的寫一條SELECT查詢語句在查詢中運用表達(dá)式、運算符對空值的處置對查詢字段起別名查詢字段的銜接.SELECT查詢根本語法SELECT FROM SQL SELECT * FROM s_emp請查詢出s_emp表中一切的員工信息:.查詢指定列SQL SELECT dept_id , salary FROM s_emp請查詢出s_emp表中一切的員工的部門ID，工資:.運用算數(shù)表達(dá)式SQL SELECT salary*12 FROM s_emp請查詢出s_emp表中一切的員工的年薪:.運用算數(shù)表達(dá)式括號可以改動運算符運算的優(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.列別名請查詢出s_emp表中一切的員工的姓名:SQL SELECT firname_name | last_name FROM s_empSQL SELECT firname_name | last_name “姓名 FROM s_emp請查詢出s_emp表中一切的員工的姓名:.空

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

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

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

7、表姓中第二個字母為a的員工信息：SELECT * FROM s_emp WHERE last_name like _a%.空值的查詢查詢出s_emp表中非銷售職位的員工信息： SELECT * FROM s_emp WHERE commission_pct is null .課堂練習(xí)已建立好的S_emp表，對此表中的數(shù)據(jù)進(jìn)展模糊查詢，詳細(xì)要求如下：查詢一名last_name以“M開頭的員工，他的dept_id好似是3X查詢工資在1200至1500之間的員工查詢來自部門ID為(41，42，43)的員工.查詢結(jié)果排序查詢出s_emp表將部門ID為41的員工的工資按從高到低陳列顯示出來： 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 模糊查詢對查詢結(jié)果排序SELECTDISTINCT *, column alias, .FROM tableWHEREcondition(s)ORDER BYcolumn, expr, alias ASC|DESC;.第三章 單行函數(shù).本章目的熟習(xí)各種類型單行函

9、數(shù)的運用掌握轉(zhuǎn)換函數(shù)的運用.兩種SQL函數(shù)單行函數(shù)CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函數(shù)GroupMULTI-ROWFUNCTION.字符函數(shù)LOWER將字符串轉(zhuǎn)換成小寫 UPPER將字符串變?yōu)榇髮?INITCAP將字符串的第一個字母變?yōu)榇髮?CONCAT拼接兩個字符串，與 | 一樣 SUBSTR取字符串的子串 LENGTH以字符給出字符串的長度 NVL以一個值來交換空值 .字符函數(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 代表一個數(shù)字.0 顯示前綴零.$ 根據(jù)本地言語環(huán)境顯示貨幣.L 采用當(dāng)?shù)刎泿欧? 打印一個小數(shù)點., 千位計算法顯示.日期格式DY星期幾的縮寫Mon,Tue,.DAY星期幾的全拼Monday,

14、Tuesday,.D一周的星期幾，星期天=1，星期六=71,2,3,4,5,6,7DD一月的第幾天，1311，2，. 31W一個月的第幾周，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小時制，1121,2,3,.12H

15、H2424小時制，0230,1,2,3,.23MI一小時中的第幾分，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為空的員工查詢出來，并將空列的值置為“No Manager：.小結(jié)字

19、符函數(shù)日期函數(shù)數(shù)值函數(shù)轉(zhuǎn)換函數(shù).第四章 關(guān)聯(liá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.簡單關(guān)聯(lián)查詢的語法查詢員工表中l(wèi)ast_name為Biri的員工的last_name與部門稱號查詢出來：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)理稱號查詢出來：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ù).本章目的定義及有效的運用組函數(shù)運用Group By對查詢數(shù)據(jù)分組運用HAVING子句對分組后的數(shù)據(jù)進(jìn)展過濾.運用Group By的查詢語法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).運用舉例查詢s_emp表中一切員工的平均工資：SQL SELECT avgsalary 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 GROU

27、P BY dept_id；.運用舉例查詢s_emp表中31部門一共有多少員工：SQL SELECTCOUNT(*) 2 FROMs_emp 3 WHERE dept_id = 31;查詢s_emp表中銷售人員的數(shù)量(提成率不為空的記錄個數(shù))：SQL SELECTCOUNT(commission_pct) 2 FROMs_emp.運用舉例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表中各個部門員工的平均工資,工資總和及部門稱號并按照工資總和排序：.運用舉例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部門以外的部門員工的平均工資,工資總和及部門稱號并按照工資總和排序：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);.運用舉例求平均工資高于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開頭職位的，各個職位中工資總和大于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 = 最小工資上一條的運轉(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ù)庫設(shè)計.本章目的了解系統(tǒng)開發(fā)的步驟數(shù)據(jù)關(guān)系的定義了解實體關(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ù)庫3范式數(shù)據(jù)庫中的每一列都是不可再分的根本數(shù)據(jù)項，同一列中不能有多個值數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的部分依賴數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的傳送援用.約束類型PK Primary Key

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

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

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

42、 name varchar2(32);.建表運用約束舉例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.級聯(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);.第九章 對數(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插入語法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,?);.更新語法UPDATEtableSETcolumn = value , column = valueWHE

48、REcondition;.刪除語法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ù)原點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列由原來的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)建序列.本章目的掌握如何運用sequences創(chuàng)建sequences修正sequences刪除sequences.創(chuàng)建sequences語法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語法ALTER SEQUENCE nameINCREMENT BY nSTART WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE.刪除sequences語法