09數(shù)據(jù)庫編程day01oracle sql database第一天

1、課程安排22:33數(shù)據(jù)庫？數(shù)據(jù)的庫。為什么學(xué)習(xí)數(shù)據(jù)庫？管理數(shù)據(jù)，便于程序開發(fā)。學(xué)習(xí)幾個數(shù)據(jù)庫？oracle，mongodb課程安排：2.5天 oraclesql語句 select from where group by 入門分區(qū)database第一天 的第 1 頁having order by 解決top-N問題insert，update，delete，create 1.5天sql語句，重點是與oracle不同的部分api編程：c語言里調(diào)用sql語句，實現(xiàn)一個的客戶端1天mongodbjson語句，數(shù)據(jù)的增刪改查api編程：c+調(diào)用api，增刪改查1天做一個案例，+mongodb=分布式網(wǎng)盤課

2、程特點：難度不高，但是指令多，要求：指令至少敲一遍。分區(qū)database第一天 的第 2 頁學(xué)習(xí)目標(biāo)22:12oracle的體系結(jié)構(gòu)oracle安裝的注意事項基本的查詢語法條件過濾oracle的函數(shù)分組函數(shù)分組數(shù)據(jù)1.2.3.4.5.6.7.分區(qū)database第一天 的第 3 頁簡介9:17oracle殷墟出土的甲骨文的第一個單詞Oracle公司成立于1977年，總部位于加州收購bea，目的是為了得到weblogic（web服務(wù)器的框架，免費(fèi)的對應(yīng)的tomcat）sun公司收購了oracle收購了suntuxedo c、c+編寫的服務(wù)timesten 內(nèi)存數(shù)據(jù)庫oracle的體系結(jié)構(gòu)：實例+

3、數(shù)據(jù)文件相關(guān)概念：表空間+數(shù)據(jù)文件分區(qū)database第一天 的第 4 頁表空間：邏輯概念數(shù)據(jù)文件：物理概念分區(qū)database第一天 的第 5 頁安裝9:43安裝的注意事項：目錄不能有中文和空格啟動服務(wù)：servi.msc實例主服務(wù) OracleServiceORCLc:appadministratorproduct11.2.0dbhome_1binORACLE.EXE ORCL偵聽服務(wù)OracleOraDb11g_home1TNSListener，主要提供網(wǎng)絡(luò)服務(wù)C:appAdministratorproduct11.2.0dbhome_1BNSLSNR客戶端安裝的注意事項：目錄不能有中文

4、和空格一定要選擇管理員模式分區(qū)database第一天 的第 6 頁登陸方式10:09本機(jī)有服務(wù)器端：在命令行:sqlplus scott/11登錄方式：- scott 用戶，11測試：tnsipip連接命令：sqlplus scott/11/192.168.137.100/orcl在tnsnames.ora里配置了連接別名sqlplus scott/11orcl100退出指令：quit登陸管理員用戶：sqlplus sys/sys as sysdbasys用戶是oracle超級用戶，默認(rèn)在安裝的主機(jī)上登錄可以不輸入的sys用戶與當(dāng)前操作用戶進(jìn)行了綁定）（安裝的時候，oracle修改需要2步SQ

5、L alter user scott account unlock;用戶已更改。SQL alter user scott identified by 11;用戶已更改。任務(wù)：晚上自行修改hr用戶的，作業(yè)是以hr為主分區(qū)database第一天 的第 7 頁-tab 數(shù)據(jù)字典 ，存放的表的類型，名字相關(guān)的信息SQL select * from tab;TNAMETABTYPE CLUSTERIDBONUSTABLESALGRADETABLE表：行和列的集合，是數(shù)據(jù)庫的最基本。scott，方案，用戶清屏：SQLhost cls查看表結(jié)構(gòu)SQL desc dept （部門表）名稱是否為空? 類型-（部

6、門）NOT NULL NUMBER(2)VARCHAR2(14) VARCHAR2(13)（部門名稱）（地址）DNAMELOC員工表SQL desc emp名稱是否為空? 類型員工EMPNOENAME JOB MGRHIREDATESAL COMMNOT NULL NUMBER(4)VARCHAR2(10) VARCHAR2(9)NUMBER(4)DATENUMBER(7,2) NUMBER(7,2)NUMBER(2)工種經(jīng)理入職時間薪水獎金部門設(shè)置行寬分區(qū)database第一天 的第 8 頁DEPTNODEPTNODEPTTABLEEMPTABLE設(shè)置頁寬性設(shè)置 D:appAdministr

7、atorproduct11.2.0cset linesize 140;set pagesize 120;nt_1sqlplusadminglogin.sql-數(shù)值型字段寬度修改col empno for 999999;-字符型字段寬度修改 col ename for a20;分區(qū)database第一天 的第 9 頁基本查詢10:50linux啟動oracle啟動實例：sqlplus / as sysdbaSQLstartup啟動偵聽：在命令行l(wèi)snrctl start寫法：注意分號可以換行寫，sql關(guān)鍵字不能縮寫，不能換行寫基本查詢SELECT *|DISTINCT column|expreF

8、ROM table;alias,.結(jié)論：查詢，可以全部列，部分列，表達(dá)式，別名-查詢員工表信息select * from emp;-查詢員工號，月薪，獎金，年薪select empno as 員工select empno as 員工,sal 薪水,comm 獎金,sal*16 年薪from emp;,sal 薪水,comm 獎 金,sal*16 年薪from emp;,ename ,ename -select * 與 select 列的區(qū)別select列更好？因為實例進(jìn)行轉(zhuǎn)換。-查詢員工號，月薪，獎金，年薪，年收入-null 搗蛋鬼分區(qū)database第一天 的第 10 頁null的第一個結(jié)論

9、，null與任何值運(yùn)算，結(jié)果為nullnvl(a,b) 如果a為null，返回結(jié)果為b，如果a不為null，返回aselect empno as 員工-查看員工表不同的部門,ename ,sal 薪水,comm 獎 金,sal*16 年薪,sal*16+nvl(comm,0) 年收入from emp;distinct 去除重復(fù)行select distinct deptno from emp;-輸出計算表達(dá)式 3+20*5，顯示當(dāng)前日期偽表（dual，數(shù)學(xué)計算，取系統(tǒng)時間，函數(shù)使用）和偽列（sysdate，并不存在某SQL select 3+20*5,sysdate from dual;，但是可

10、以直接使用）3+20*5 SYSDATE103 16-6月-17select 3+20*5,sysdate from emp;sqlplus 與sql命令的區(qū)別，哪個是sqlplus令，哪個是sql令sqlplus是oracle提供的客戶端執(zhí)行程序，本身并不會修改數(shù)據(jù)，只是本地化設(shè)置sql是語言，可以實現(xiàn)對數(shù)據(jù)庫數(shù)據(jù)的修改 set pluscolhost cls select insert sql update delete可以查看sqlplusSQL ? topic令分區(qū)database第一天 的第 11 頁分區(qū)database第一天 的第 12 頁where過濾11:40select 。f

11、rom 。where 。復(fù)雜sql的編寫方法ed（it） 打開記事本，編輯sql，sql不帶 ；/ 執(zhí)行sqlwhere過濾比較運(yùn)算符 = = != () betn and-查詢10號部門的員工信息select * from emp where deptno = 10;-查詢員工名字為king的員工信息SQL select * from EMP where ENAME =KING;-字符串單引號包含，大小寫敏感EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO17-11月-817839 KING500010-查找薪水不等于1250員工的信息select * from

12、 emp where sal != 1250; select * from emp where sal 1250;-查詢?nèi)肼毴掌跒榈膯T工信息說明日期格式敏感分區(qū)database第一天 的第 13 頁SQL select *from emp where hiredate =17-11月-81;EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO17-11月-817839 KING500010SQL select *from emp where hiredate =17-11月-81;EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO17-11

13、月-817839 KING500010-修改會話，日期格式 yyyy-mm-ddSQL alter sesset NLS_DATE_FORMAT=yyyy-mm-dd;會話已更改。SQL select sysdate from dual;SYSDATE2017-06-16select * from emp where hiredate =1981-11-17;-留下一個疑問邏輯運(yùn)算符：寫多個條件and 并且or 或者not 非-查詢10號部門或者20部門的員工信息分區(qū)database第一天 的第 14 頁select * from emp where deptno=10 or deptno=2

14、0;-查詢10號部門員工工資為1300的員工信息 select * from emp where deptno=10 and sal = 1300;-查找工資介于1000-2000之間的員工信息select * from emp where sal =1000 and sal if(deptno != 10 & deptno != 20 & deptno != null),最后一個表達(dá)式不會成立，所以整個表達(dá)式為假like：模糊查詢知識點： %匹配任意多個字符。_匹配一個字符-查詢員工首字母是S的員工信息select * from emp where ename like S%;-查詢母的員工

15、信息select * from emp where ename like ;-查詢帶有下劃線的inserto emp(empno,ename,job,sal,deptno) values(1001,Tom_abc,SALESMAN,2000,10);esc指定轉(zhuǎn)義字符select * from emp where ename like %_% escselect * from emp where ename like %/_% esc;/;select 取出的內(nèi)容from 從哪取where 有哪些條件分區(qū)database第一天 的第 16 頁排序14:53如何設(shè)計排序？按列?表達(dá)式按什么排序如

16、何識別按什么排序？ 設(shè)計關(guān)鍵字order by按多列排序？排序順序升序還是降序？asc 升序（默認(rèn)），desc 降序oracle支持：order by column|expr|alias|number(序號)-員工信息按入職日期排序select * from emp order by hiredate;-員工薪水按從大到小排序（序號）select * from emp order by sal desc;-員工信息按部門、薪水排列如果要按照多列進(jìn)行排序，則規(guī)則是先按照第一列排序，如果相同，則按照第二列排序；以此類推select * from emp order by deptno desc,s

17、al desc;-asc和desc作用的它之前的最近的字段分區(qū)database第一天 的第 17 頁-員工信息按獎金逆序 nulls last-nullnull的又一個結(jié)論：null既不是空，也不是某個數(shù)，無窮大，默認(rèn)排序在最后。select * from emp order by comm desc nulls last; select * from emp order by nvl(comm,-1) desc ;按序號排序-序號是查詢結(jié)果集的列的序號，并非是表的序號select empno,ename,sal from emp order by 3 ;按照別名來排序select empno

18、,ename,sal sal1 from emp order by sal1 ;-回滾操作rollback;分區(qū)database第一天 的第 18 頁單行函數(shù)15:39定義：對一行進(jìn)行變換，返回一個結(jié)果字符函數(shù)-lower 小寫，upper 大寫，initcap首字母大寫select lower(o wOrld) 一,upper(o wOrld) 二,initcap(o wOrld) 三from dual;-concat(連接符|)，substr，length，lengthb，instrSQL select concat(o,world) from dual;- 連接字符串，支持2個參數(shù)CON

19、CAT(HEoworldSQL select o|world|haha|444 from dual;O|WORLD|oworldhaha444-substr（str,select substr(,len） 截取字符串，從位置開始，截取len長度，如果len未填寫，代表直接到末尾oworld,1,3) 一,substr(oworld,1) 二,substr(oworld,-3) 三from dual;-instr(str,str2),判斷str2是否在str中存在，如果存在，返回位置，如果不存在返回0select instr(oworld,owo) 一,instr(oworld,owow) 二f

20、rom dual;-lpad，rpad-l(r)pad(str，len，ch) 返回len長度的字符串，如果str不夠len的話，在左（右）填充ch這個字符-trim，replaceselect aaa|trim(o world )|bbb from dual;-去首尾空格select trim(H from HHHHoHHHworldHHHHH) from dual;-trim(C from str) 去掉str首尾為C的字符select replace(select replace(oworld,llo,kk) from dual;oworld,llo,) from dual;數(shù)值函數(shù)-r

21、ound 四舍五入 trunc截斷 mod 取模select round(45.926, 2) 一, round(45.926, 1) 二, round(45.926, 0) 三, round(45.926, -1) 四, round(45.926, -2)五from dual;select trunc(45.926, 2) 一, trunc(45.926, 1) 二, trunc(45.926, 0) 三, trunc(45.926, -1) 四, trunc(45.926, -2) 五from dual;分區(qū) database第一天 的第 19 頁ceil函數(shù)-向上取整，floor函數(shù)-向

22、下取整SQL select ceil(181/60),floor(181/60) from dual;CEIL(181/60) FLOOR(181/60)43轉(zhuǎn)換函數(shù)-to_char,to_numer,to_date-to_char 與to_number互相轉(zhuǎn)換-把薪水轉(zhuǎn)換為本地貨幣字符型select empno,sal,to_char(sal,L9,999) from emp;-把上述某個結(jié)果轉(zhuǎn)回數(shù)值型select to_number(￥2,975,L9,999) from dual;-to_char 與to_date-顯示yyyy-mm-dd hh24:mi:ss 今天是幾select t

23、o_char(sysdate,yyyy-mm-dd hh24:mi:ss 今天是 day) from dual;-將上述輸出字符串反轉(zhuǎn)回日期select to_date(2017-06-16 16:10:20 今天是五,yyyy-mm-dd hh24:mi:ss 今天是 day) from dual;上午遺留：select * from emp where hiredate = to_date(1981-11-17,yyyy-mm-dd);select * from emp where to_char(hiredate,yyyy-mm-dd) = 1981-11-17;隱式轉(zhuǎn)換：當(dāng)你沒有明確轉(zhuǎn)

24、換函數(shù)的時候，系統(tǒng)如果判斷類型不一致，會進(jìn)行隱式轉(zhuǎn)換，隱式有一個前提，它必須能轉(zhuǎn)換。盡量避免隱式轉(zhuǎn)換。分區(qū) database第一天 的第 20 頁日期函數(shù)-顯示昨天，今天，明天-oracle日期型+1代表加一天SQL select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;昨天今天明天2017-06-15 2017-06-16 2017-06-17-計算員工工齡 可以按日，周，月，年 日期差減方法SQL select empno,ename,sysdate-hiredate 日,(sysdate-hiredate)/7 周,(sysdate-

25、hiredate)/30 月,(sysdate- hiredate)/365 年from emp;上述結(jié)果不夠準(zhǔn)確，計算月份和年的時候-日期函數(shù) months_betn add_months last_day next_day-months_betn 計算月份差 精確值select months_bet-這樣更準(zhǔn)確n(sysdate,to_date(2017-02-17,yyyy-mm-dd) from dual;select empno,ename,months_betn(sysdate,hiredate),(sysdate-hiredate)/30 月from emp;add_months

26、 增加月份select add_months(sysdate,2) from dual;求明年的今天？select add_months(sysdate,12) from dual;last_day 最后一天-指定日期所在月份的最后一天分區(qū) database第一天 的第 21 頁next_day 求指定日期的下一個幾分區(qū) database第一天 的第 22 頁條件表達(dá)式16:50-打算給漲工資，1000，經(jīng)理800，普通員工400，將漲前、漲后薪水列出if(job = ) sal;else if(job =MANAGER) sal+800;else sal+400;語法：CASE expr W

27、HEN comparison_expr1 THEN return_expr1arison_expr2 THEN return_expr2WHEN comparison_exprn THEN return_exprnELSE else_exprENDsql99標(biāo)準(zhǔn)select empno,ename,sal 漲前薪水,case job when when MANAGER then sal+200else sal end 漲后薪水from emp; then sal+0oracle特有的decode函數(shù)decode(expr|column,search1,res1,search2,res2,def

28、alut)select empno,ename,sal 漲前薪水,decode(job,from emp;漲后薪水,sal,MANAGER ,sal+800,sal+400)分區(qū)database第一天 的第 23 頁分組函數(shù)和分組數(shù)據(jù)17:08分組函數(shù)（組函數(shù)，聚合函數(shù)），對多行進(jìn)行處理。oracle提供的分組函數(shù)： avg，sum，max，min，count統(tǒng)計平均工資？-求員工的工資總額select sum(sal) from emp;-員工人數(shù)select count(empno),count(*) from emp;-求emp表中的工種數(shù)SQL select count(distinc

29、t job) from emp;COUNT(DISTINCTJOB)5-求員工的平均工資（兩種方式）SQL select avg(sal),sum(sal)/count(*) from emp;AVG(SAL) SUM(SAL)/COUNT(*)2073.214292073.21429-求員工的平均獎金（三種方式）SQL select avg(comm),sum(comm)/count(*),sum(comm)/count(comm) from emp;AVG(COMM) SUM(COMM)/COUNT(*) SUM(COMM)/COUNT(COMM)550157.142857550上述結(jié)果不一致的原因是分母不一樣導(dǎo)致分區(qū)database第一天 的第 24 頁究竟哪個對