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

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

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

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

4、lect sum(成績(jī)) as 總成績(jī) from stu.成績(jī)表數(shù)據(jù)查詢-排序 2-1升序排列select 學(xué)號(hào), 成績(jī)from stu.成績(jī)表order by 成績(jī)降序排列select 學(xué)號(hào), 成績(jī)from stu.成績(jī)表order by 成績(jī) descOracle常用函數(shù)Oracle中函數(shù)劃分為單行函數(shù)和多行函數(shù)單行函數(shù)作用于數(shù)據(jù)庫(kù)表的某一行并返回一個(gè)值字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)。多行函數(shù)基于數(shù)據(jù)庫(kù)表多行進(jìn)行運(yùn)算，返回一個(gè)值例如對(duì)多行記錄的某個(gè)字段進(jìn)行求和、求最大值運(yùn)算等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)取絕對(duì)值abs(-15)15CEIL(n )向上取整ceil(44.778)45SIN(n)正弦sin(1.571).999999979COS(n)余弦cos(0)1SIGN

7、(n)取符號(hào)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)截?cái)鄑runk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函數(shù)常用的日期函數(shù)函 數(shù)功 能示 例結(jié) 果MONTHS_BETWEEN返回兩個(gè)日期間的月份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返回指定日期后的星期對(duì)應(yīng)的新日期next_day(06-2月-03,星期一)10-2月-03LAST_DAY返回指定日期所在的月的最后一天last_day(06-2月-03)28-2月-03ROUND按指定格式對(duì)日期進(jìn)行四舍五入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對(duì)日期按指定方式進(jìn)行截?cái)鄑runc(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當(dāng)前日期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)绻胫?，某個(gè)人的總成績(jī)或某科目的平均成績(jī)，該如何獲得？分組查詢分組查詢select 課程,avg(成績(jī)) as 單

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

12、m pet where master_id=1;COUNT()計(jì)數(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要求某公司印了一批充值卡，卡的密碼是隨機(jī)生成的，現(xiàn)在出現(xiàn)這個(gè)問題： 卡里面的“O和0”（歐和零），“I和1”（哎和一），用戶反映說看不清楚，公司決定，把存儲(chǔ)在數(shù)據(jù)庫(kù)中的密碼中所有的“哦”都改成“零”，把所有的“I”都改成“1”；請(qǐng)編寫SQL語(yǔ)句實(shí)現(xiàn)以上要求；數(shù)據(jù)庫(kù)表

13、名：Card；密碼字段名：PassWord；案例分析1分析這是更新語(yǔ)句，需要使用UPDATE語(yǔ)句；因?yàn)闋可娴阶址奶鎿Q，需要使用到Oracle中的函數(shù)Replace；案例分析1SQL*Plus兩行SQL語(yǔ)句Update stu.Card Set PassWord = Replace(PassWord ,O,0)Update stu.Card Set PassWord = Replace(PassWord ,i,1)一行SQL語(yǔ)句Update stu.Card Set PassWord = Replace(Replace(PassWord ,O,0),i,1)多表聯(lián)結(jié)查詢問題學(xué)員內(nèi)部測(cè)試成績(jī)查

14、詢的每次顯示的都是學(xué)員的編號(hào)信息，因?yàn)樵摫碇兄淮鎯?chǔ)了學(xué)員的編號(hào)；實(shí)際上最好顯示學(xué)員的姓名，而姓名存儲(chǔ)在學(xué)員信息表；如何同時(shí)從這兩個(gè)表中取得數(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梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)SCode1234查詢結(jié)果SName梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)CourseIDScore00100100200297896776陸乘風(fēng)00381多表連接查詢內(nèi)連接-2select 學(xué)生表.學(xué)號(hào),姓名,年齡,性別,成績(jī) from stu.學(xué)生表 join stu.成績(jī)表 on 學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)多表連接查詢左外連接select 學(xué)生表.姓名,成績(jī)表.課程編號(hào),成績(jī)表.成績(jī) from stu.學(xué)生表 left join stu.成績(jī)表on 學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)多表連接查詢右外連接select 學(xué)生

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

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