OracleSQL培訓講義_RYAN1.ppt

1、Oracle SQL 培訓講義一期,ORACLE9i 9.0.1 空間1.76G ORACLE9i 9.0.2 空間2.86G 事務處理 - 增 刪除 修改頻繁 數(shù)據(jù)倉庫 - 查詢 頻繁 GBK -多200個字左右(偏僻字) GB2312 SETUP.exe 雙擊 啟動ORACLE數(shù)據(jù)庫 OracleServiceACCP 服務 多啟動幾次 OracleOraHome90TNSListener 服務 如果啟動不了Listener(監(jiān)聽器) 改注冊表 ImagePath D:oracleora90BINTNSLSNR 用命令行啟動 cmd - tnslsnr 最小化 tNSlsnter 監(jiān)聽器

2、如果無法啟動 運行cmd 切換到DOS界面 敲tnslsnr 窗口不要關閉 D:oracleora90networkADMIN listener.ora 文本文件 HOST=localhost或機器名 存盤 用工具 ORACLE SQL*PLUS驗證數(shù)據(jù)庫是否啟動 用戶名system 密碼manager 出現(xiàn) 連接到 和 SQL符號 說明數(shù)據(jù)庫已經可用了 默認用戶: 超級用戶: 用戶名system/密碼manager 用戶名sys/密碼change_on_install 普通的用戶: 用戶名scott/密碼tiger 注意的問題:安裝的源程序放在什么目錄下? 目錄中不要出現(xiàn)中文名字的目錄 數(shù)據(jù)

3、庫的名字不要是中文,8個字符以內 字符集用默認的ZHS16GBK 刪除: 在硬盤上的一個隱含安裝的目錄Programm Files下的ORACLE,ORACLE安裝和刪除,ORACLE SQL*PLUS 字符界面 ORACLE自帶 sqlplusw.exe windows窗口下的sqlplus sqlplus.exe DOS下的sqlplus 用戶名scott 密碼tiger 普通用戶 system manager 超級用戶 sys change_on_install 超級用戶 比system權限還要大 SQLPLUS下的常用命令 show user 察看用戶 connect 用戶名/密碼 連

4、接到指定用戶 connect scott/tiger select * from tab where tabtype=TABLE; 察看當前用戶下的表 select count(*) from dept; 查詢表dept中記錄的行數(shù) desc dept 察看表dept的結構 quit/exit 退出 clear screen 清除屏幕 set linesize 200 設置一行顯示200個字符 set pagesize 20 設置每頁顯示20行 spool 文件名 (spool c:abc.txt) 作日志文件命令 spool off 修改D:oracleora90sqlplusadmingl

5、ogin.sql文件 可以設置SQLPLUS的環(huán)境 set linesize 200 set pagesize 20,操作ORACLE數(shù)據(jù)庫的工具,ORACLE 企業(yè)管理器 (OEM) 圖形界面 PL/SQL Developer 第三方軟件 ORACLE不帶的 QUSET 公司 TOAD SQLPLUS 瀏覽器使用oracle (isqlplus) D:oracleora90ApacheApacheconfhttpd.conf 修改80端口 - 8001 http:/localhost:8001/isqlplus,操作ORACLE數(shù)據(jù)庫的工具1,字符型 char 范圍 最大2000個字節(jié) 定長

6、 char(10) 張三 后添空格6個把10個字節(jié)補滿 張三 性別 char(2) 男 varchar2 范圍 最大4000個字節(jié) 變長 varchar2(10) 張三 在數(shù)據(jù)庫中張三 大對象 字符型大對象 4000字節(jié) 最大4G CLOB (Character Large OBject) 數(shù)字 number 范圍 10的-38次方 到10的38次方 可以表示小數(shù) 也可以表示整數(shù) number(4) 最大表示4位整數(shù) -9999 到 9999 number(5,2) 表示5位有效數(shù)字 2位小數(shù)的 一個小數(shù) -999.99 到 999.99 日期 date 包含年月日和時分秒 7個字節(jié) 圖片 b

7、lob 二進制大對象 圖像/聲音 4G,ORACLE中字段的數(shù)據(jù)類型,學生表student create table student( -學生表 xh number(4), -學號 xm varchar2(10), -姓名 sex char(2), -性別 birthday date, -日期 sal number(7,2) -獎學金 ); 班級class create table class( -班級表 classid number(2), -班級編號 cname varchar2(20) -班級名字 ); 添加字段(學生所在班級classid) alter table student ad

8、d (classid number(2); 修改字段的長度 alter table student modify (xm varchar2(12) ; 修改字段的類型(不能有記錄的) alter table student modify (xh varchar2(5); 刪除一個字段 alter table student drop column sal; 刪除表 drop table student; 表的名字修改 rename student to stu; 字段如何改名字 -先刪除 a)alter table student drop column sal; -再添加 b)alter t

9、able student add (salary number(7,2);,如何建表,插入數(shù)據(jù) insert語句 所有字段都插入 insert into student values (A001,張三,男,01-5月-05,10); ORACLE中默認的日期格式DD-MON-YY dd 日子(天) mon 月份 yy 2位的年 09-6月-99 1999年6月9號 改日期的默認格式 alter session set nls_date_format = yyyy-mm-dd; insert into student values (A002,MIKE,男,1905-05-06,10); 恢復O

10、RACLE默認格式 alter session set nls_date_format = dd-mon-yy; 察看日期的格式 set linesize 1000 select * from nls_session_parameters where parameter=NLS_DATE_FORMAT; 永久設置日期格式 改注冊表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd 部分字段插入 insert into student(xh,xm,sex) values (A003,JOHN,女); 插入空值 insert into student(xh,x

11、m,sex,birthday) values (A004,MARTIN,男,null);,如何插入數(shù)據(jù),改一個字段 update student set sex=女 where xh=A001; 改多個字段 update student set sex=男, birthday=1980-04-01 where xh=A001; 改為空值 (修改為空時=null) update student set birthday=null where xh=A001; 把生日為空的人的班級編號改為20(條件中的空是is null / is not null) update student set clas

12、sid=20 where birthday is null; 錯誤的沒有達到要求 update student set classid=20 where birthday=null; 不表示空值 表示xm是null的字符串 update student set classid=20 where xm=null;,修改 update,delete from student; 刪除所有記錄,表結構還在,寫日志,可以恢復的,速度慢 drop table student; 刪除表的結構和數(shù)據(jù) delete from student where xh=A001; 刪除一條記錄 truncate tabl

13、e student; 刪除表中的所有記錄,表結構還在,不寫日志,無法找回刪除的記錄,速度快,刪除 delete,select * from student; select xh,xm,sex from student; select * from student where xh like A%1; %任意多個字符 select * from student where xh like A_1; _1個字符 select * from student where xh like %A%; select * from student where xh like A%; select * from

14、 student where xh like %A; select * from student where xh = A%;,查詢 select 1,select * from student order by birthday ; 升序 (order by birthday asc;) select * from student order by birthday desc; -降序 select * from student order by birthday desc,xh asc; -按birthday 降序 按xh升序(asc/默認) select * from student w

15、here sex=女 or birthday=1999-02-01; select * from student where sex=女 and birthday=1999-02-01; select * from student where salary 20 and xh B002; (!=),查詢 select 2,單行函數(shù) 返回值只有一個 分組函數(shù) 返回值是多條記錄 group by sum avg,ORALCE的函數(shù),字符函數(shù) concat 連接 | 顯示dname和loc中間用-分隔 select deptno,dname|-|loc from dept; dual啞元表 沒有表需

16、要查詢的時候 可以用它 select Hello World from dual; select 1+1 from dual; 查詢系統(tǒng)時間 select sysdate from dual; initcap 首字母大寫 select ename,initcap(ename) from emp; lower 轉換為小寫字符 select ename,lower(ename) from emp; upper 轉換為大寫 update dept set loc=lower(loc); update dept set loc=upper(loc); LPAD 左填充 select deptno,lp

17、ad(dname,10, ),loc from dept; RPAD 右填充 LTRIM 去除左邊的空格 RTRIM 去除右邊的空格 ALLTRIM 去除兩邊的空格 replace 替換 translate 轉換 select ename,replace(ename,S,s) from emp; 用s去替換ename中的S select ename,translate(ename,S,a) from emp;,單行函數(shù)1, ASCII 求ASC碼 chr asc碼變字符 select ascii(A) from dual; select chr(97) from dual; select He

18、llo|chr(9)|World from dual; t ascii碼是 9 n ascii碼是 10 select Hello|t|World from dual; substr 字符截取函數(shù) select ename,substr(ename,1,3) from emp; 從第1個位置開始 顯示3個字符 select ename,substr(ename,4) from emp; 從第4個位置開始顯示后面所有的字符 instr 測試字符串出現(xiàn)的位置 select ename,instr(ename,S) from emp; S第1次出現(xiàn)的位置 select ename,instr(ena

19、me,T,1,2) from emp; 從第1個位置開始 測試T第2次出現(xiàn)的位置 length 字符串的長度 select ename,length(ename) from emp;,單行函數(shù)2, sysdate 系統(tǒng)時間 select sysdate from dual; select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual; select to_char(sysdate,DDD) from dual select to_char(sysdate,D) from dual select to_char(sysdate,DAY) fro

20、m dual select to_char(sysdate,yyyy-mm-dd) from dual; select to_char(sysdate,yyyy年mm月dd日 hh24:mi:ss) from dual; select from dual; select to_char(sysdate,SSSSS) from dual; -從今天零點以后的秒數(shù),日期和 時間函數(shù), ADD_MONTHS 添加月份 得到一個新的日期 select add_months(sysdate,1) from dual; select add_months(sysdate,-1) from dual; se

21、lect trunc(sysdate)-to_date(20050101,yyyymmdd) from dual; select add_months(sysdate,12) from dual; 一年以后的今天 select add_months(sysdate,-12) from dual; 一年以前的今天 trunc(sysdate) 截取年月日 select sysdate+2 from dual; 數(shù)字代表的是天數(shù) 兩個日期之間的差值代表天數(shù), last_day 某月的最后一天 select last_day(sysdate) from dual; select add_months

22、(last_day(sysdate)+3,-1) from dual; 本月第3天的日期 months_between 兩個日期之間的月數(shù) select months_between(sysdate,2005-02-01) from dual; 方向 sysdate - 2005-02-01 select months_between(2005-02-01,sysdate) from dual;,to_char 把日期或數(shù)字類型變?yōu)樽址?select to_char(sysdate,hh24:mi:ss) from dual; select to_char(sysdate,yyyymmdd

23、hh24:mi:ss) from dual; select sal,to_char(sal,L9,999) from emp; L本地貨幣 to_number 把字符串變成數(shù)字 select to_number(19990801) from dual; to_date 把字符串變成日期 select to_date(19800101,yyyymmdd) from dual; select to_char(to_date(19800101,yyyymmdd), yyyy年mm月dd日) from dual;,轉換函數(shù),ceil(x) 不小于x的最小整數(shù) ceil（12.4) 13 ceil(-1

24、2.4) -12 floor(x) 不大于x的最大整數(shù) floor(12.5) 12 floor(-12.4) -13 round(x) 四舍五入 round(12.5) 13 round(12.456,2) 12.46 trunc(x) 舍去尾數(shù) trunc(12.5) 12 trunc(12.456,2) 12.45 舍去日期的小時部分 select to_char(trunc(sysdate),yyyymmdd hh24:mi:ss) from dual; mod(x,n) x除以n以后的余數(shù) mod(5,2) 1 mod(4,2) 0 power(x,y) x的y次方 select p

25、ower(3,3) from dual;,數(shù)學函數(shù),求最大值 select greatest(100,90,80,101,01,19) from dual; 求最小值 select least(100,0,-9,10) from dual; 空值轉換函數(shù) nvl(comm,0) 字段為空值 那么就返回0 否則返回本身 select comm,nvl(comm,0) from emp; comm 類型和 值的類型是 一致的,混合函數(shù),decode 選擇結構 (if . elseif . elesif . else結構) 要求: sal=800 顯示低工資 sal=3000 正常工資 sal=50

26、00 高工資 只能做等值比較 select sal,decode(sal,800,低工資,3000,正常工資,5000,高工資,沒判斷) from emp; 表示如下的if else 結構 if sal=800 then 低工資 else if sal =3000 then 正常工資 else if sal = 5000 then 高工資 else 沒判斷 end if sal 800 sal -800 0,復雜的函數(shù),判斷正負 sign(x) x是正 1 x是負 -1 x是0 0 select sign(-5) from dual; 如何做大于小于的比較？ sal1000 顯示低工資 sal

27、-10000 sign(sal-1000) = -1 1000=sal=3000 正常工資 3000sal=5000 高工資 select sal,decode( sign(sal-1000),-1,低工資, decode(sign(sal-3000),-1,正常工資, 0,正常工資,1, decode(sign(sal-5000),-1,高工資,高工資) ) as 工資狀態(tài) from emp; 一般的情況 decode(x,y1,z1,y2,z2,z3) if x= y1 then z1 else if x = y2 then z2 else z3 end if,返回值是多條記錄 或計算后的

28、結果 group by sum avg 計算記錄的條數(shù) count select count(*) from emp; select count(1) from emp; select count(comm) from emp; 字段上count 會忽略空值 comm不為空值的記錄的條數(shù) 統(tǒng)計emp表中不同工作的個數(shù) ? select count(distinct job) from emp; select distinct job from emp; select distinct job,empno from emp; select job,empno from emp; 得到的效果是一樣的,distinct 是消去重復行 不是消去重復的列,分組函數(shù),group by 分組統(tǒng)計 -在沒有分組函數(shù)的時候 -相當于distinct 的功能 select job from emp group by job; select distinct job from