oracle初級（續）_ZenDei技術網路在線

oracle基本簡單的用法，之前的筆記稍作整理一下，希望對各位有用，如有問題可在下方留言，所有SQL都是經過oracle環境測試的，可複製可粘貼。今天先寫這些，未完，待續。。。。 ...

　　　　　　　　　　　　　　　　　　　　　　　　　　有志者、事竟成，破釜沉舟，百二秦關終屬楚；
　　　　　　　　　　　　　　　　　　　　　　　　　　苦心人、天不負，卧薪嘗膽，三千越甲可吞吳。

oracle基本簡單的用法，之前的筆記稍作整理一下，希望對各位有用，如有問題可在下方留言，所有SQL都是經過oracle環境測試的，可複製可粘貼。

  1 select * from emp order by deptno asc ;
  2 
  3 select * from dept;
  4 --oracle 拼接字元串
  5 --oracle中字元串需要在英文或其他文字兩端用單引號包含
  6 select empno, ename, '職位是' || job, sal from emp;
  7 
  8 --列名的別名起別名利用空格
  9 select empno, ename, '職位是' || job haha, sal from emp;
 10 --如果使用雙引號聲明別名，則可以區分大小寫
 11 select empno, ename, '職位是' || job "haha", sal from emp;
 12 --如果想在別名中顯示空格，一定要有雙引號
 13 
 14 --–關鍵字AS
 15 select empno, ename, '職位是' || job as "ha ha", sal from emp;
 16 --–註意：推薦使用as聲明別名，容易區分表達式與別名的界限
 17 
 18 --  去掉重覆行
 19 select distinct deptno from emp;
 20 -註意：distinct只能放在select與列名之間的位置
 21 -- select distinct 列1，列2，..列n
 22 --select和from 書寫順序 select…from
 23 執行順序 from…select
 24 
 25 --不等於
 26 select * from emp where deptno != 20;
 27 select * from emp where deptno <>20;--推薦這種寫法
 28 
 29 --查詢條件中的 與關係
 30 問題一：
 31 查詢在10號部門工作，並且月薪高於2000，
 32 並且職位是MANAGER的員工信息
 33 
 34 select * from emp where deptno = 10 and sal>2000 and job = 'MANAGER';
 35 --或關係or
 36 問題一：
 37 查詢在10號部門工作或者工資高於2000的員工
 38 
 39 select * from emp where deptno =10 or sal>2000;
 40 --如果and與or一起使用
 41 –and的優先順序要比or高（重要）
 42 如果要想修改優先順序，加括弧查詢在10號部門工作或者工資高於2000,並且職位是MANAGER的員工
 43 
 44 
 45 select * from emp where (deptno = 10 or sal > 2000) and job = 'MANAGER';
 46 
 47 -- 特殊的比較運算符
 48 
 49 --beetween ... and
 50 
 51 --beetween 上限 and 下限
 52 
 53 查詢emp表中，20號部門月薪在1000~2000之間的員工信息
 54 
 55 select * from emp where (sal between 1000 and 2000) and deptno = 20;
 56 
 57 –between and 使用範圍 數值和日期
 58 –資料庫的常見數據類型：
 59 –1）文字
 60 –2）數值
 61 –3）日期/時間
 62 
 63 select * from emp where hiredate between '1-1月-1982' and '31-12月-1982';
 64 
 65 --between and 也可以比較字元串，比較順序是按照字典的順序比較
 66 
 67 
 68 select * from emp where ename between 'A' and 'C';
 69 
 70 --上下限反著寫 
 71 select * from emp where sal between 2000 and 1000;
 72 -- 語句不報錯，但是邏輯關係沒有，沒有結果
 73 --關鍵字in 格式 列 in（值1，值2，值3…值n）列的取值是值1或值2或值3…或值n
 74 select ename, mgr from emp where mgr in(7902, 7698, 7788);
 75 --like… 像…（重點）
 76 模糊查詢
 77 通配字元
 78 % :0n個任意字元
 79 _:1一個任意字元
 80 --查詢所有員工首字母為B的員工信息
 81 select * from emp where ename like 'B%';
 82 --–練習：查詢倒數第二個字元是R的員工信息
 83 
 84 --like只能用於文字類型的模糊查詢匹配。
 85 select * from emp where ename like '%R_';
 86 
 87 --is null
 88 用於篩選空值
 89 因為=null的邏輯關係是null，不能篩選出空值
 90 查詢沒有獎金的人
 91 
 92 
 93 select * from emp where comm = 0 or comm is null;
 94 
 95 --NOT
 96 邏輯非關係,表示取反
 97 
 98 --not between and
 99 
100 查詢月薪不在1000~2000之間的員工信息
101 
102 select * from emp where sal not between 1000 and 2000;
103 列名在not前後結果一致，保證列名在between前即可
104 --not in
105 
106 查詢其上司的員工編號不是7902,7698,7788的員工的信息
107 
108 select * from emp where mgr not in(7902, 7698, 7788);
109 
110 --not like 
111 
112 查詢所有不姓張的員工
113 
114 select * from emp
115 where ename not like '張%';
116 --is not null
117 select * from emp
118 where comm is not null;
119 
120 
121 排序
122 –order by
123 –升序 由小到大
124 –數值：由小到大
125 –日期：由早到晚
126 –文字：字典順序
127 
128 
129 查詢所有10號部門的員工信息，按照月薪升序進行排序
130 
131 
132 select * from emp where deptno = 10 order by sal ;
133 
134 有的環境下不加升序排列，環境預設的是升序，
135 oracle預設的是升序
136 
137 select * from emp where deptno = 10 order by sal asc ;
138 
139 推薦寫法
140 
141 SQl語句執行順序
142 書寫順序:select…from…where…order by
143 執行順序:from…where…select…order by
144 where不能使用別名，因為select中尚未執行，
145 所以還沒有創建出別名，order by可以使用別名，
146 因為order by在select之後執行，列的別名已經被創建
147 --多列排序
148 查詢員工信息，按照部門編號排序，月薪降序
149 select * from emp order by deptno asc, sal desc;
150 
151 --oracle函數
152 單行函數、多行函數（分組函數）
153 單行函數
154 dual
155 --系統顯示當前日期
156 select sysdate from dual;
157 
158 使用的單行函數，對查詢結果的每一行記錄都有效，
159 會影響到每一行的記錄
160 dual表為了補全sql語句
161 
162 --轉大寫 upper(str) 將str字元串轉成大寫
163 
164 select upper('hAHa') as 描述 from dual;
165 
166 可以為列增加別名
167 
168 --轉小寫lower(str) 將str字元串轉成小寫
169 
170 select lower('hAHa') from dual;
171 
172 --首字母轉大寫，其餘字母小寫initcap(str)
173 
174 select initcap('hAHa') from dual;
175 
176 select initcap('i LiKe GIRL') from dual;
177 
178 --以單詞為單位，每個單詞的首字母大寫，其餘的小寫
179 
180 select * from emp where lower(ename) = lower('ScOTT');
181 --lower()替換為upper()效果一樣
182 
183 字元截取函數
184 --substr(str,n1,n2)
185 str:被截取的字元串
186 n1：開始截取的位置（以1的位置開始）
187 n2：截取字元的個數
188 將str字元串從n1位置起，截取n2個字元
189 結果：字元型類
190 
191 
192 select substr('0123456', 3, 3) from dual;
193 
194 從n1的位置開始，要包含該位置的字元
195 
196 字元查找函數
197 --instr(str1,str2,n1,n2)
198 在str1中尋找str2的字元串，
199 從n1的位置起，第n2次出現的位置
200 結果是一個位置，位置是一個數值
201 
202 select instr('I LIKE GIRL', 'I', 2, 1) from dual;
203 
204 --字元拼接函數
205 select 'haha' || 'xixi' || 'hehe' from dual;
206 –將str1和str2拼接組成一個字元串，功能等同於 ||
207 --concat(str1,str2)
208 select concat('haha', 'xixi') from dual;
209 
210 select concat(concat('haha', 'xixi'),'hehe') from dual;
211 
212 字元替換函數
213 --replace(str1,str2,str3)
214 在str1字元串中，使用str3來替換全部的str2
215 
216 select replace('ABCDECD', 'CD', 'HAHA') from dual;
217 
218 字元串的長度函數
219 --length(str)
220 返回str字元串的文字的個數
221 
222 select length('abcde'), length('哈哈') from dual;
223 
224 字元串補齊函數
225 lpad(str1,n1,str2)
226 rpad(str1,n1,str2)
227 lpad:將str1字元串，使用str2在左側補充到n1個長度
228 rpad:將str1字元串，使用str2在右側補充到n1個長度
229 
230 select lpad('abc', 6, '哈呵'), rpad('abc', 2, '*')
231 from dual;
232 
233 --trunc(n1,n2)
234 –將n1數字捨棄，精確到小數點後n2位
235 select trunc(3.1415, 3), trunc(3.14, 0), trunc(6.14, -1)from dual;
236 --MOD(n1,n2)
237 求n1除以n2後得到的餘數 取餘
238 
239 select mod(5, 3), mod(5, -3), mod(-5, 3), mod(-5, -3)
240 from dual;
241 取餘運算區分正負值，看被除數 編程也是遵循這樣原則
242 
243 日期函數（重要）
244 --sysdate（函數）
245 獲取資料庫所在伺服器的當前系統時間
246 select sysdate from dual;
247 
248 
249 --months_between(date1,date2) date1與date2之間的月數差
250 假設emp表中，所有員工至今未辭職，計算他們現在的司齡
251 months_between(date1,date2)與between and上下限不是對應的
252 
253 
254 select ename, hiredate,trunc(months_between(sysdate, hiredate) / 12, 0) as 司齡 from emp;
255 用於計算年齡，司齡，工齡
256 這些類似的隨著時間流逝而改變的數據
257 
258 
259 --add_months(date1,d1)
260 為date1的時間，追加d1個月，結果還是一個日期
261 設公司的試用期為6個月，計算emp表中員工的轉正日期
262 
263 
264 select ename, hiredate, add_months(hiredate, 6)as 轉正日期 from emp;
265 
266 --last_day(date1)
267 date1所在月份最後一天的日期（結果的類型還是日期）
268 
269 select ename, hiredate, last_day(hiredate)
270 from emp;
271 
272 兩個日期之間的天數(重要)
273 
274 差值就是天數，沒有小數點
275 
276 --next_day(date1,n1)
277 返回date1起之後周幾的日期
278 1:周日 2：周一 … 7:周六
279 
280 select next_day(sysdate,7) from dual;
281 
282 
283 轉換函數（重點）
284 --to_number(str)
285 將str字元串轉為數字
286 
287 select to_number('1234') from dual;
288 
289 按照特定格式轉換
290 --to_number(str1,fmt) fmt格式也一定要是一個字元串 fmt也叫做格式碼
291 –$ : 美元符號
292 –, :三位分隔符或者千位分割符 100,222,119.00
293 –9 ：一位數字
294 –0 ：一位數字或者前導0(補全前面的位置)（瞭解）
295 
296 
297 如果實際數據超過fmt定義的長度，則數值無法顯示
298 實際數據小於fmt定義的長度，數值可以顯示
299 
300 
301 --to_char(n1,fmt)
302 將數值轉換為fmt表示的文字
303 
304 select to_number('$23,412,123.34','$99,999,999.99')from dual;
305 
306 select to_char(2236778,'$9,999,999.99') from dual;
307 
308 –日期轉字元 to_char(date1,fmt)
309 –日期的格式碼：
310 –YYYY 年
311 –MM 月
312 –DD 日
313 –HH24 24進位的小時
314 –HH12 12進位的小時
315 –MI 分
316 –SS 秒
317 –  / 常規日期分割符
318 –: 常規時間分割符
319 DATE類型 精確到秒
320 查詢員工姓名和入職日期
321 入職日期按照”年/月/日 時:分:秒”的格式顯示
322 
323 select ename, to_char(hiredate, 'YYYY/MM/DD HH24:MI:SS')from emp;
324 YY,RR 也是表示年
325 
326 --to_date
327 
328 select to_date('2017-4-7','YYYY-MM-DD') from dual;
329 
330 查詢在1982年期間入職的員工 按照規定格式顯示
331 XXXXXXXX
332 select * from empwhere hiredate between to_date('1982-1-1','YYYY-MM-DD')and to_date('1982-12-31','YYYY-MM-DD');
333 
334 
335 通用函數
336 --nvl(expr1,expr2)
337 當expr1值不是null時，函數結果是expr1
338 當expr1值是null時，函數的結果是expr2
339 計算員工月收入，如果員工有獎金，則獎金+500
340 如果員工沒有獎金，則不發獎金
341 
342 select ename, comm, sal + nvl(comm + 500,0) from emp;
343 --nvl2(expr1,expr2,expr3)
344 判斷expr1的值是否為null
345 如果是null使用expr3的值
346 如果不是null使用expr2的值
347 
348 select comm, nvl2(comm, 1, 2) from emp;
349 給員工發節日福利，有獎金的人節日福利是1000,沒有獎金的人節日福利是800
350 select comm, nvl2(comm, 1000, 800) from emp;
351 
352 多行函數（分組函數）
353 由多行記錄為一組統計出的數據，利用的是分組函數（多行函數）
354 常見的統計型數據
355 求和
356 --sum()
357 –查詢emp表中，所有月薪的總和
358 select sum(sal) from emp;
359 求平均
360 --avg()
361 求emp中平均司齡
362 select avg(months_between(sysdate,hiredate) / 12)from emp;
363 最大值
364 --max()
365 –查詢emp表中，最高月薪
366 select max(sal) from emp;
367 最小值
368 --min()
369 –查詢emp表中，最低月薪
370 select min(sal) from emp;
371 計數
372 --count()
373 查詢emp表中，有多少個員工
374 select count(empno) from emp;
375 select count(distinct deptno) from emp;
376 去掉重覆數據用distinct，在count函數內部
377 查詢每個部門的平均工資
378 --group by 依據XX分組 不是分組函數
379 只有使用了分組函數，才能使用group by
380 
381 select deptno ,avg(sal) from emp group by deptno;
382 
383 只有在group by中出現的列，才能寫在select之後（必須記住）
384 多次分組，第二次分組是在第一次分組的基礎上進行的
385 查詢10號和20號部門的平均工資
386 
387 select deptno,avg(sal)from emp where deptno in(10,20)group by deptno order by deptno asc;
388 --書寫順序
389 --執行順序 from...where...group by...select...order by...
390 分組函數是在group by 執行位置進行計算
391 查詢平均工資高於2500的部門id和其平均工資
392 select deptno, avg(sal)from emp where avg(sal) > 2500  --錯誤寫法group by deptno
393 -- where 後面不允許使用分組函數
394 --having
395 –專門處理分組函數的篩選
396 –group by… having…
397 
398 –查詢平均工資在2000~2500之間的部門ID和其平均工資
399 select deptno, avg(sal)from emp group by deptno having avg(sal) between 2000 and 2500;
400 select deptno, avg(sal)from emp group by deptno having avg(sal) >= 2000 and avg(sal) <= 2500;
401 select deptno, avg(sal)from emp group by deptno having avg(sal) > 2500;
402 --執行順序
403 
404 --from ...where... group by ... having... select ...order ...by

今天先寫這些，未完，待續。。。。