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

1、NameDateOracleSQL本章目標(biāo)本章目標(biāo)寫一條SELECT查詢語句在查詢中使用表達(dá)式、運(yùn)算符對空值的處理對查詢字段起別名查詢字段的連接SELECT查詢基本語法查詢基本語法SELECT FROM 請查詢出s_emp表中所有的員工信息:查詢指定列查詢指定列 請查詢出s_emp表中所有的員工的部門ID，工資:運(yùn)用算數(shù)表達(dá)式運(yùn)用算數(shù)表達(dá)式 請查詢出s_emp表中所有的員工的年薪:運(yùn)用算數(shù)表達(dá)式運(yùn)用算數(shù)表達(dá)式 括號可以改變運(yùn)算符運(yùn)算的優(yōu)先順序:列別名列別名 請查詢出s_emp表中所有的員工的姓名: 請查詢出s_emp表中所有的員工的姓名:空值的處理空值的處理 請查詢出s_emp表中所有的員工的

2、工資:去掉重復(fù)行去掉重復(fù)行 請查詢出s_dept表的部門名稱:去掉多列重復(fù)行去掉多列重復(fù)行 請查詢出s_emp表中所有的員工的部門ID及職稱:小結(jié)小結(jié)查詢表的全部記錄查詢指定的列給列起別名NVL函數(shù)及字符串連接符重復(fù)行的處理Distinct引言引言本章目標(biāo)本章目標(biāo)WHERE條件查詢在查詢中使用表達(dá)式、運(yùn)算符使用LIKE、BETWEEN、IN進(jìn)行模糊查詢第一章內(nèi)容回顧第一章內(nèi)容回顧v對員工表中信息進(jìn)行查詢，具體要求如下： 1. 查詢s_emp表表要求輸出員工姓名(firs_name、last_name)和實(shí)際工資(基本工資+提成)：查詢基本語法查詢基本語法SELECT FROM WHERE 列名

3、稱列名稱SELECT *表名表名FROMS_emp過濾條件過濾條件WHEREdept_id= 41 v請查詢出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=50WHERE條件查詢條件查詢-BETWEEN&IN 請查詢出s_emp表中工資在15

4、00到2000之間的員工信息：SELECT * FROM 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

5、_name like S_% escape 請查詢出s_emp表姓中第二個(gè)字母為a的員工信息：SELECT * FROM s_emp WHERE last_name like _a%空值的查詢空值的查詢v查詢出s_emp表中非銷售職位的員工信息： SELECT * FROM s_emp WHERE commission_pct is null 課堂練習(xí)課堂練習(xí)已建立好的S_emp表，對此表中的數(shù)據(jù)進(jìn)行模糊查詢，具體要求如下：查詢一名last_name以“M”開頭的員工，他的dept_id好像是3X查詢工資在1200至1500之間的員工查詢來自部門ID為(41，42，43)的員工查詢結(jié)果排序查詢

6、結(jié)果排序v查詢出s_emp表將部門ID為41的員工的工資按從高到低排列顯示出來： SELECT * FROM s_emp WHERE dept_id=41 ORDER BY salary DESC 小結(jié)小結(jié)Where條件查詢Betweenand & In & Like 模糊查詢對查詢結(jié)果排序本章目標(biāo)本章目標(biāo)熟悉各種類型單行函數(shù)的使用掌握轉(zhuǎn)換函數(shù)的使用兩種兩種SQL函數(shù)函數(shù)單行函數(shù) Character Number Date Conversion多行函數(shù) Group字符函數(shù)字符函數(shù)LOWER將字符串轉(zhuǎn)換成小寫 UPPER將字符串變?yōu)榇髮?INITCAP將字符串的第一個(gè)字母變?yōu)榇髮?/p>

7、 CONCAT拼接兩個(gè)字符串，與 | 相同 SUBSTR取字符串的子串 LENGTH以字符字符給出字符串的長度 NVL以一個(gè)值來替換空值 字符函數(shù)舉例字符函數(shù)舉例LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEINITCAP(SQL Course)Sql Course SELECT * FROM s_emp WHERE last_name=PATEL SELECT * FROM s_emp WHERE UPPER( last_name)=PATEL字符操作函數(shù)字符操作函數(shù)CONCAT(Good, String)GoodString

8、SUBSTR(String,1,3)StrLENGTH(String)6數(shù)字函數(shù)數(shù)字函數(shù)ROUND(value,precision) 按precision 精度4舍5入TRUNC(value,precision) 按precision 截取valueSQL SELECT round(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) tr

9、unc1, trunc(124.16666,2) FROM dual; TRUNC1 TRUNC(124.16666,2)- - 100 124.16Round&Trunc函數(shù)函數(shù)ROUND (45.923, 2)45.92ROUND (45.923, 0)46ROUND (45.923, -1)50TRUNC (45.923, 2)45.92TRUNC (45.923)45TRUNC (45.923, -1)40日期函數(shù)日期函數(shù)MONTHS_BETWEEN(date2,date1) 給出 Date2 - date1的月數(shù)ADD_MONTHS增加或減去月份NEXT_DAY ( date

10、,day) 給出日期date之后下一天的日期LAST_DAY(date)返回日期所在月的最后一天 日期函數(shù)日期函數(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_DAY(01-SEP-95)30-SEP-95日期函數(shù)日期函數(shù)ROUND(25-MAY-95,MONTH)01-JUN-95ROUND(25-MAY-95 ,YEAR)01-JAN-95TRUNC(25-MAY-95 ,MONTH)01-MAY-95TR

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

12、,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,.12HH2424小時(shí)制，0230,1,2,3,.23MI一小時(shí)中的第幾分，0590,1,2,3.59SS一分中的第幾秒，0590,1,2,3,.59To-char舉例舉例To-char舉例舉例v查詢員工表中入職日期在7月份的員工信息：RR 日期格式日期格式轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 轉(zhuǎn)換函數(shù)的嵌套轉(zhuǎn)換函數(shù)的

13、嵌套Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3轉(zhuǎn)換函數(shù)嵌套舉例轉(zhuǎn)換函數(shù)嵌套舉例 查詢員工表中為空的員工查詢出來，并將空列的值置為“No Manager”：小結(jié)小結(jié)字符函數(shù)日期函數(shù)數(shù)值函數(shù)轉(zhuǎn)換函數(shù)本章目標(biāo)本章目標(biāo)在一張或多張表中使用等值或非等值連接使用外連接查詢自連接查詢等值連接的種類等值連接的種類等值連接非等值連接外連接自連接表間的關(guān)系表間的關(guān)系簡單關(guān)聯(lián)查詢的語法簡單關(guān)聯(lián)查詢的語法 查詢員工表中為Biri的員工的last_name與部門名稱查詢出來：SELECT FROM WHERE 非等值連接非等值連接自連接自連接自連接自連接 查詢

14、員工表中為Biri的員工的last_name及其部門經(jīng)理名稱查詢出來：外連接外連接內(nèi)連接內(nèi)連接小結(jié)小結(jié)等值連接非等值連接外連接自連接本章目標(biāo)本章目標(biāo)定義及有效的使用組函數(shù)使用Group By對查詢數(shù)據(jù)分組使用HAVING子句對分組后的數(shù)據(jù)進(jìn)行過濾使用使用Group By的查詢語法的查詢語法常用組函數(shù)常用組函數(shù)vAVG (DISTINCT|ALL|n)vCOUNT (DISTINCT|ALL|expr|*)vMAX (DISTINCT|ALL|expr)vMIN (DISTINCT|ALL|expr)vSUM (DISTINCT|ALL|n)應(yīng)用舉例應(yīng)用舉例 查詢s_emp表中所有員工的平均工資

15、： 查詢s_emp表中各個(gè)部門員工的平均工資及部門名稱：應(yīng)用舉例應(yīng)用舉例 查詢s_emp表中31部門一共有多少員工： 查詢s_emp表中銷售人員的數(shù)量(提成率不為空的記錄個(gè)數(shù))：應(yīng)用舉例應(yīng)用舉例 查詢s_emp表中各個(gè)部門員工的平均工資,工資總和及部門名稱并按照工資總和排序：應(yīng)用舉例應(yīng)用舉例 查詢s_emp表中除41部門以外的部門員工的平均工資,工資總和及部門名稱并按照工資總和排序：應(yīng)用舉例應(yīng)用舉例 求平均工資高于1500的部門的工資總和，最高工資，最低工資： 課堂練習(xí)課堂練習(xí) 求不以“VP”開頭職位的，各個(gè)職位中工資總和大于5000的職位及工資總和，并按工資總和排序：小結(jié)小結(jié)子查詢子查詢 查

16、詢s_emp表中工資最低的員工的姓名：子查詢子查詢 查詢s_emp表中平均工資低于32部門的部門ID：子查詢子查詢 查詢s_emp表中平均工資低于32部門的部門ID及名稱：子查詢舉例子查詢舉例小結(jié)小結(jié)本章目標(biāo)本章目標(biāo)了解系統(tǒng)開發(fā)的步驟數(shù)據(jù)關(guān)系的定義理解實(shí)體關(guān)系映射圖（E-R圖）系統(tǒng)開發(fā)步驟系統(tǒng)開發(fā)步驟StrategyandAnalysisDesignBuildandDocumentTransitionProduction數(shù)據(jù)模型數(shù)據(jù)模型Model of systemin clients mindEntity model ofclients modelTable model ofentity m

17、odelTables on diskServerEntity Relationship Modeling Conceptsv Entity A thing of significance about which information needs to be known Examples: customers, sales representatives, ordersv Attribute Something that describes or qualifies an entity Examples: name, phone, identification numberv Relation

18、ship An association between two entities Examples: orders and items, customers and sales representativesEntity Relationship Modelv Create an entity relationship diagram from business specifications or narratives.vScenario. . . Assign one or more customers to a sales representative . . . . . Some sal

19、es representatives do not yet have assigned customers . . .CUSTOMER#* id*nameophoneEMPLOYEE#* id*last nameofirst nameassigned tothe sales rep toRelationship Typesv One-to-one Have a degree of one and only one in both directions. Are rare. Example: Husband and wife.v Many-to-one Have a degree of one

20、or more in one direction and a degree of one and only one in the other direction. Are very common. Example: passengers and plane.v Many-to-many Have a degree of one or more in both directions. Are resolved with an intersection entity. Example: Employees and skills.數(shù)據(jù)庫數(shù)據(jù)庫3范式范式數(shù)據(jù)庫中的每一列都是不可再分的基本數(shù)據(jù)項(xiàng)，同一列

21、中不能有多個(gè)值數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的部分依賴數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任何候選關(guān)鍵字段的傳遞引用約束類型約束類型PK Primary Key 唯一且非空FK Foreign Key 外鍵約束，值引用另一張表 已經(jīng)存在的數(shù)據(jù)UKUnique Key 唯一且可為空NOT NULL 非空約束舉例約束舉例Primary KeyForeign KeyS_EMP TableS_DEPT TablePrimary Key本章目標(biāo)本章目標(biāo)掌握創(chuàng)建表的語法Oracle的數(shù)據(jù)類型使用約束數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)一個(gè)Oracle數(shù)據(jù)庫包含下列幾種數(shù)據(jù)結(jié)構(gòu)： Table 存儲(chǔ)數(shù)據(jù) View 從一個(gè)表

22、或多個(gè)表的數(shù)句中得到的子集 Sequence 生成主鍵值 Index 提高查詢性能建表語法建表語法Oracle的數(shù)據(jù)類型的數(shù)據(jù)類型Char(size) 定長字符型，字符長度不夠自動(dòng)在右邊右邊加空格符號 Varchar2(size) 可變長字符型，大小必須指定 Number(m,n) 數(shù)字型，可存放實(shí)數(shù)和整數(shù) Date 日期類型Blob 2進(jìn)制大對象其最大大小為4 GB 此數(shù)據(jù)類型映射到 Byte 類型的 Array。 Clob 2進(jìn)制大對象其最大大小為4 GB 此數(shù)據(jù)類型映射到 String 命名規(guī)范命名規(guī)范必須以字母開頭1-30個(gè)字符長度只允許包含AZ, az, 09, _, $, and

23、#在一個(gè)數(shù)據(jù)庫保證命名的唯一不能使用Oracle內(nèi)部的關(guān)鍵字建表舉例建表舉例建表使用約束舉例建表使用約束舉例建表主鍵約束舉例建表主鍵約束舉例建表外鍵約束舉例建表外鍵約束舉例約束命名約束命名查看表的約束查看表的約束 CONSTRAINT_TYPE C -check P -primary key R -forgien key U -unique級聯(lián)刪除級聯(lián)刪除子查詢創(chuàng)建表子查詢創(chuàng)建表本章目標(biāo)本章目標(biāo)在已創(chuàng)建表中插入新的數(shù)據(jù)修改已經(jīng)存在的數(shù)據(jù)刪除表中的數(shù)據(jù)理解事物控制及其重要性DML命令命令CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACKInsert插

24、入語法插入語法插入舉例插入舉例更新語法更新語法刪除語法刪除語法Database Transactionsv Contain one of the following statements: DML commands that make up one consistent change to the data One DDL command One DCL commandv Begin when the first executable SQL command is executed.v End with one of the following events: COMMIT or ROLLBA

25、CK DDL or DCL command executes (automatic commit) Errors, exit, or system crash還原點(diǎn)還原點(diǎn)Controlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarker ASavepointMarker BROLLBACKROLLBACK to AROLLBACK to B本章目標(biāo)本章目標(biāo)增加或者修改列添加刪除約束刪除表刪除表所有的數(shù)據(jù)添加列添加列添加列舉例添加列舉例 向下表mytest添加名為age，類型為number的一列： 再向下表m

26、ytest添加名為salary,類型為number(9,1)缺省值為8888的一列：刪除列刪除列刪除列舉例刪除列舉例 將表mytest的salary列刪除：修改列修改列修改列舉例修改列舉例 將表mytest的age列由原來的number類型更改為number（4，2）類型：添加約束添加約束添加約束舉例添加約束舉例 將表mytest的id列添加主鍵約束： 將表child的p_id列添加外鍵約束：刪除約束舉例刪除約束舉例刪除表及表的重命名刪除表及表的重命名刪除表數(shù)據(jù)刪除表數(shù)據(jù)小結(jié)小結(jié)CommandCREATE TABLEALTER TABLEDROP TABLERENAMETRUNCATE本章目標(biāo)

27、本章目標(biāo)掌握如何使用sequences創(chuàng)建sequences修改sequences刪除sequences創(chuàng)建創(chuàng)建sequences語法語法創(chuàng)建序列舉例創(chuàng)建序列舉例序列舉例序列舉例 利用創(chuàng)建好的Sequence向mytest表中添加數(shù)據(jù)： 查看當(dāng)前序列的值：修改修改sequences語法語法刪除刪除sequences語法語法本章目標(biāo)本章目標(biāo)掌握如何使用視圖、索引創(chuàng)建視圖、索引修改視圖、索引刪除視圖、索引ID LAST_NAME FIRST_NAME TITLE DEPT_ID- - - - - 1 Velasquez Carmen President 50 2 Ngao LaDoris VP,

28、Operations 41 3 Nagayama Midori VP, Sales 31 4 Quick-To-See Mark VP, Finance 10 5 Ropeburn Audry VP, Administration 50 6 Urguhart Molly Warehouse Manager 41 7 Menchu Roberta Warehouse Manager 42 8 Biri Ben Warehouse Manager 43 9 Catchpole Antoinette Warehouse Manager 4410 Havel Marta Warehouse Manager 4511 Magee Colin Sales Representative 3112 Giljum Henry Sales Representative 3213 Sedeghi Yasm