實施文件oracle教程課件數(shù)據(jù)查詢

1、Oracle 數(shù)據(jù)查詢目標理解查詢的機制查詢所有列查詢指定列模糊查詢分組查詢多表查詢-內(nèi)連接查詢內(nèi)置函數(shù)數(shù)據(jù)查詢機制Application Logic客戶程序查詢請求查詢結(jié)果集OracleA B C D E F G select name, pay from emp.salary查詢產(chǎn)生一個虛擬表，看到的是以表的形式顯示的結(jié)果，但結(jié)果并不真正存儲，每次執(zhí)行查詢只是現(xiàn)從數(shù)據(jù)表中提取數(shù)據(jù)，并按照表的形式顯示出來數(shù)據(jù)查詢-基礎(chǔ)查詢?nèi)康男泻土衧elect * from stu.學生表 查詢部分行select 學號,成績,課程 from stu.成績表select * from stu.成績表wher

2、e 成績70條件查詢數(shù)據(jù)查詢-列名去除重復行select distinct 學號from stu.成績表模糊查詢like查詢時，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，只要字段中含有這些內(nèi)容select 姓名 from stu.學生表 where 姓名 like 張%姓名張果老張飛張揚出去模糊查詢between and把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來select 學號, 分數(shù) from stu.成績表 where 分數(shù) between 60 and 80學號分數(shù)77786111761764模糊查詢in把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記錄查詢出來Select 姓名, 地址

3、from stu.學生表 where 地址 in (北京,廣州,上海)學員姓名地址李揚廣州于紫電上海李青霜北京司馬弓上海問題成績表中存儲了所有學員的成績，我想知道：學員的總成績、平均成績、有成績的學員總共有多少名怎么辦？聚合函數(shù)-1SUM 求和AVG 求平均值select SUM(成績) from stu.成績表select avg(成績) from stu.成績表聚合函數(shù)-2MAX、MIN 求最大值、最小值COUNT 求有記錄的行數(shù)select max(成績), min (成績)from stu.成績表select count (*) from stu.成績表數(shù)據(jù)查詢-定義別名定義列別名se

4、lect sum(成績) as 總成績 from stu.成績表數(shù)據(jù)查詢-排序 2-1升序排列select 學號, 成績from stu.成績表order by 成績降序排列select 學號, 成績from stu.成績表order by 成績 descOracle常用函數(shù)Oracle中函數(shù)劃分為單行函數(shù)和多行函數(shù)單行函數(shù)作用于數(shù)據(jù)庫表的某一行并返回一個值字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)。多行函數(shù)基于數(shù)據(jù)庫表多行進行運算，返回一個值例如對多行記錄的某個字段進行求和、求最大值運算等Oracle常用函數(shù)常用的字符函數(shù)函 數(shù)功 能示 例結(jié) 果INITCAP (char)首字母大寫initc

5、ap (hello)HelloLOWER (char)轉(zhuǎn)換為小寫lower (FUN)funUPPER (char)轉(zhuǎn)換為大寫upper (sun)SUNLTRIM (char, set)左剪裁ltrim (xyzadams, xyz)adamsRTRIM (char, set)右剪裁rtrim (xyzadams, ams)xyzadTRANSLATE (char, from, to)按字符翻譯translate (jack, abcd, 1234)j13kREPLACE (char, search_str, replace_str)字符串替換replace (jack and jue, j

6、, bl)black and blueINSTR (char, substr, pos)查找子串位置instr (worldwide, d)5SUBSTR (char, pos, len)取子字符串substr (abcdefg,3,2)cdCONCAT (char1, char2)連接字符串concat (Hello, world)HelloworldOracle常用函數(shù)常用的數(shù)字函數(shù)函 數(shù)功 能示 例結(jié) 果ABS(n)取絕對值abs(-15)15CEIL(n )向上取整ceil(44.778)45SIN(n)正弦sin(1.571).999999979COS(n)余弦cos(0)1SIGN

7、(n)取符號sign(-32)-1FLOOR(n)向下取整floor(100.2)100POWER(m,n )m的n次冪power(4,2)16MOD(m,n)取余數(shù)mod(10,3)1ROUND(m,n)四舍五入round(100.256,2)100.26TRUNC(m,n)截斷trunk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函數(shù)常用的日期函數(shù)函 數(shù)功 能示 例結(jié) 果MONTHS_BETWEEN返回兩個日期間的月份months_between (04-11月-05,11-1月-01)57.7741935ADD_MONTHS返回把月份數(shù)加到日期上

8、的新日期add_months(06-2月-03,1)add_months(06-2月-03,-1)06-3月-0306-1月-03NEXT_DAY返回指定日期后的星期對應的新日期next_day(06-2月-03,星期一)10-2月-03LAST_DAY返回指定日期所在的月的最后一天last_day(06-2月-03)28-2月-03ROUND按指定格式對日期進行四舍五入round(to_date(13-2月-03),YEAR) round(to_date(13-2月-03),MONTH)round(to_date(13-2月-03),DAY)01-1月-0301-2月-0316-2月-03

9、TRUNC對日期按指定方式進行截斷trunc(to_date(06-2月-03),YEAR)trunc(to_date(06-2月-03),MONTH)trunc(to_date(06-2月-03),DAY)01-1月-0301-2月-0302-2月-03CURRENT_DATE當前日期Oracle常用函數(shù)常用的轉(zhuǎn)換函數(shù)函 數(shù)功 能示 例結(jié) 果TO_CHAR轉(zhuǎn)換成字符串類型to_char(1234.5, $9999.9)$1234.5TO_DATE轉(zhuǎn)換成日期類型to_date(1980-01-01, yyyy-mm-dd)01-1月-80TO_NUMBER轉(zhuǎn)換成數(shù)值類型to_number(12

10、34.5)1234.5Oracle常用函數(shù)常用的其他函數(shù)函 數(shù)功 能NVL(EXP1, EXP2)如果exp1的值為null，則返回exp2的值，否則返回exp1的值NVL2(EXP1, EXP2, EXP3)如果exp1的值為null，則返回exp2的值，否則返回exp3的值DECODE(VALUE,IF1,THEN1,IF2,THEN2,ELSE) 如果value的值為if1，則返回then1的值，如果value的值為if2，則返回then2的值，否則返回else值分組查詢?nèi)绻胫?，某個人的總成績或某科目的平均成績，該如何獲得？分組查詢分組查詢select 課程,avg(成績) as 單

11、科平均成績 from stu.成績表group by 課程2.分組函數(shù)分組函數(shù)對一組查詢行返回一個結(jié)果值除count(*)外，分組函數(shù)均忽略空值如要包括空值，須使用空值轉(zhuǎn)換函數(shù)分組函數(shù)不可以用在WHERE子句中用以限定查詢的結(jié)果，對分組查詢結(jié)果的限定應使用HAVING子句在分組函數(shù)中可有下面兩個選項(1) DISTINCT選項：使分組函數(shù)只考慮列表達式中的不同值(2) ALL選項：使分組函數(shù)考慮全部值，其中包含重復值Oracle常用函數(shù)常用的分組函數(shù)函 數(shù)功 能示 例SUM()求和select sum(love) from pet;AVG()求平均值select avg(health) fro

12、m pet where master_id=1;COUNT()計數(shù)select count(type_id) from pet;MAX()求最大值select max(adopt_time) from pet;MIN()求最小值select min(adopt_time) from pet where type_id=1;案例分析1要求某公司印了一批充值卡，卡的密碼是隨機生成的，現(xiàn)在出現(xiàn)這個問題： 卡里面的“O和0”（歐和零），“I和1”（哎和一），用戶反映說看不清楚，公司決定，把存儲在數(shù)據(jù)庫中的密碼中所有的“哦”都改成“零”，把所有的“I”都改成“1”；請編寫SQL語句實現(xiàn)以上要求；數(shù)據(jù)庫表

13、名：Card；密碼字段名：PassWord；案例分析1分析這是更新語句，需要使用UPDATE語句；因為牽涉到字符串的替換，需要使用到Oracle中的函數(shù)Replace；案例分析1SQL*Plus兩行SQL語句Update stu.Card Set PassWord = Replace(PassWord ,O,0)Update stu.Card Set PassWord = Replace(PassWord ,i,1)一行SQL語句Update stu.Card Set PassWord = Replace(Replace(PassWord ,O,0),i,1)多表聯(lián)結(jié)查詢問題學員內(nèi)部測試成績查

14、詢的每次顯示的都是學員的編號信息，因為該表中只存儲了學員的編號；實際上最好顯示學員的姓名，而姓名存儲在學員信息表；如何同時從這兩個表中取得數(shù)據(jù)？多表連接查詢分類內(nèi)連接(inner join)外連接左外連接(left join)右外連接(right join)完整外連接(full join)多表連接查詢內(nèi)連接-1SELECT S.SName,C.CourseID,C.Score From stu.Score AS CINNER JOIN stu.Students AS SON C.StudentID = S.SCodeScoreStudentsIDCourseIDScore12230010010

15、0200297896776300381StundentsSName梅超風陳玄風陸乘風曲靈風SCode1234查詢結(jié)果SName梅超風陳玄風陳玄風陸乘風CourseIDScore00100100200297896776陸乘風00381多表連接查詢內(nèi)連接-2select 學生表.學號,姓名,年齡,性別,成績 from stu.學生表 join stu.成績表 on 學生表.學號=成績表.學號多表連接查詢左外連接select 學生表.姓名,成績表.課程編號,成績表.成績 from stu.學生表 left join stu.成績表on 學生表.學號=成績表.學號多表連接查詢右外連接select 學生

16、表.學號,姓名,年齡,性別,成績 from stu.學生表 right join stu.成績表 on 學生表.學號=成績表.學號數(shù)據(jù)查詢-限制行數(shù)限制固定行數(shù)select 學號, 姓名,Rownumfrom (select 學號, 姓名 from stu.學生表Order by 學號, 姓名)Where Rownum=5;案例分析2要求一家銀行發(fā)行了新的信用卡，剛開始的時候推廣得很好，但是逐漸廢卡也越來越多（卡上的余額少于2元，并且用戶長時間不使用該卡），因此銀行在二月份把這些少于2元的卡從都數(shù)據(jù)庫表中刪除了，但是很快問題就來了，用戶發(fā)現(xiàn)他的卡再也不能使用而投訴，因此只能再把這些卡恢復。編寫

17、SQL，把CardID為23、30等在子表中沒有的記錄插入到子表中，插入的數(shù)據(jù)Score項值為2案例分析2分析這是一個數(shù)據(jù)插入的操作，因此要使用INSERT語句來進行這種方式的數(shù)據(jù)插入，不是固定的數(shù)據(jù)項，而是從其它表中篩選數(shù)據(jù)再插入，因此要使用到INSERT INTOSELECT結(jié)構(gòu)插入的數(shù)據(jù)項是子表中沒有的，需要找到子表中沒有的這些數(shù)據(jù)，我們可以把整個語句的INSERT（前半部分）不考慮，先只考慮SELECT（后半部分），SELECT的任務就是找出兩個表中不同的項在前面的聯(lián)結(jié)查詢中，使用 joinon可以找出相同的項，編寫以下T-SQL：select M.CardID,2 from M join S on S.CardIDM.CardID以上把“=”簡單地改為“”，不能找出不同的項，所找到的項比原來的要多很多，因為兩個表之間是進行“交叉”對比的，而不是一一對比，所以會對比出很多“不同”的項來；因此，這種方法不可行考慮我們在上面的左外連接查詢，能夠查詢出左表中存在而相關(guān)表不存在的數(shù)據(jù)項