sql是一門獨立的語言, 有自己的語法結構, 所有的關係型數據庫都是支援sql這種語言的
- sql的執行是在用戶端, 相當於個伺服器發送請求, 伺服器通過不同的sql回覆 回復不同的數據
不管是什麼型別的語言對應的都是不同語法格式的字串
Data Manipulation Language）
Data Definition Language）
Data Control Language）
select 列名 from 表 where 條件;
使用語句 desc 表名;
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) 員工編號
ENAME VARCHAR2(50) Y 員工姓名
JOB VARCHAR2(20) Y 職位
MGR NUMBER(4) Y 直屬領導(編號)
HIREDATE DATE Y 入職時間
SAL NUMBER(7,2) Y 工資
COMM NUMBER(7,2) Y 傭金
DEPTNO NUMBER(2) Y 所屬部門的編號
其中：Name這一列下都是 欄位 名
欄位代表表中的列，記錄代表表中的行。
使用語句select * from 表名;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------------------------------------- -------------------- ----- -----------
7369 文斯莫克·山治 廚師 7566 1980/12/17 3200.00 800.00 20
。。。。。。
。。。。。。
從得到的查詢中抽離出以上的資訊，那麼第一行代表所有的欄位，第二行以及下面 下麪的每一行，代表記錄
語法：
SELECT 列名1|表達式, 列名2|表達式(1+2), ... FROM 表名;
-- 查詢"emp" 表中員工號, 姓名, 月薪, 傭金
SQL> select empno,ename,sal,comm from emp;
EMPNO ENAME SAL COMM
----- -------------------------------------------------- --------- ---------
7369 文斯莫克·山治 3200.00 800.00
7499 羅羅諾亞·索隆 3450.00 300.00
7521 娜美 4560.00 500.00
7566 蒙奇·D·路飛 5450.00 1000.00
7654 妮可·羅賓 5500.00 2400.00
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00 1000.00
7782 貝波 2450.00 20.00
7788 烏索普 3000.00
7839 尾田_榮一郎 5000.00
7844 弗蘭奇 1500.00 0.00
7876 布魯克 1100.00
7900 甚平 2000.00
7902 香克斯 6000.00 1500.00
7934 托尼托尼·喬巴 800.68 10.00
7840 岸本_齊史 5000.00
7709 Jinbe 3550.00 600.00
7699 夏奇 2050.00 200.00
7700 佩金 3000.00 200.00
7937 拉基·路 3600.00 500.00
7938 洛克斯達 2600.00 200.00
7783 強巴魯 3600.00 200.00
7784 伊卡庫 3000.00 200.00
7785 庫裡奧尼 3500.00 200.00
7935 本·貝克曼 5500.00
7936 耶穌布 5500.00 500.00
6100 馬歇爾·D·帝奇 5675.00
6101 芝沙斯·巴沙斯 4321.00 5.00
6102 阿巴羅·皮薩羅 2540.00
6103 範·奧卡 3450.00
1000 Monkey·D·Luffy 5500.00
1001 Trafalgar·D·Water·Law 5800.00
1002 Marshall·D·Teach 7800.00
1003 Portgas·D·Ace 5500.00
1004 Hagwar·D·Sauro 3450.00
1005 Gol·D·Roger 7500.00
1006 Monkey·D·Dragon 6550.00
1007 Monkey·D·Garp 7890.00
7701 Roronoa Zoro 2000.00 800.00
7702 Nami 3000.00 1000.00
7703 Usopp 2500.00 500.00
7704 Sanji 2800.00 1200.00
7705 Tony Tony Chopper 100.00 300.00
7706 Nico Robin 5000.00 800.00
7707 Franky 2000.00 500.00
7708 Brook 3000.00 900.00
45 rows selected
-- 表達式: 基於列做加減乘除等相關操作
-- 查詢"emp" 表中員工號, 姓名, 月薪, 傭金, 年薪(sal*12)。
-- 年薪的列名依也就是sal*12
SQL> select empno,ename,sal,comm,sal*12 from emp;
EMPNO ENAME SAL COMM SAL*12
----- -------------------------------------------------- --------- --------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400
7499 羅羅諾亞·索隆 3450.00 300.00 41400
7521 娜美 4560.00 500.00 54720
7566 蒙奇·D·路飛 5450.00 1000.00 65400
7654 妮可·羅賓 5500.00 2400.00 66000
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00 1000.00 67860
7782 貝波 2450.00 20.00 29400
7788 烏索普 3000.00 36000
7839 尾田_榮一郎 5000.00 60000
7844 弗蘭奇 1500.00 0.00 18000
7876 布魯克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000
7934 托尼托尼·喬巴 800.68 10.00 9608.16
7840 岸本_齊史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600
7699 夏奇 2050.00 200.00 24600
7700 佩金 3000.00 200.00 36000
7937 拉基·路 3600.00 500.00 43200
7938 洛克斯達 2600.00 200.00 31200
7783 強巴魯 3600.00 200.00 43200
7784 伊卡庫 3000.00 200.00 36000
7785 庫裡奧尼 3500.00 200.00 42000
7935 本·貝克曼 5500.00 66000
7936 耶穌布 5500.00 500.00 66000
6100 馬歇爾·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852
6102 阿巴羅·皮薩羅 2540.00 30480
6103 範·奧卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000
7702 Nami 3000.00 1000.00 36000
7703 Usopp 2500.00 500.00 30000
7704 Sanji 2800.00 1200.00 33600
7705 Tony Tony Chopper 100.00 300.00 1200
7706 Nico Robin 5000.00 800.00 60000
7707 Franky 2000.00 500.00 24000
7708 Brook 3000.00 900.00 36000
45 rows selected
在查詢的時候, 表頭預設是列的名字, 如果列名不直觀, 可以指定別名, 別名主要用於顯示, 顯示到表頭
上, 替換原來的列名
語法： select 列|表達式 （as，可不寫） "別名"…… from 表;
SQL> select empno "員工編號",ename "姓名",sal "月薪",comm "傭金",sal*12 "年薪" from emp;
員工編號 姓名 月薪 傭金 年薪
----- -------------------------------------------------- --------- --------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400
7499 羅羅諾亞·索隆 3450.00 300.00 41400
7521 娜美 4560.00 500.00 54720
7566 蒙奇·D·路飛 5450.00 1000.00 65400
7654 妮可·羅賓 5500.00 2400.00 66000
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00 1000.00 67860
7782 貝波 2450.00 20.00 29400
7788 烏索普 3000.00 36000
7839 尾田_榮一郎 5000.00 60000
7844 弗蘭奇 1500.00 0.00 18000
7876 布魯克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000
7934 托尼托尼·喬巴 800.68 10.00 9608.16
7840 岸本_齊史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600
7699 夏奇 2050.00 200.00 24600
7700 佩金 3000.00 200.00 36000
7937 拉基·路 3600.00 500.00 43200
7938 洛克斯達 2600.00 200.00 31200
7783 強巴魯 3600.00 200.00 43200
7784 伊卡庫 3000.00 200.00 36000
7785 庫裡奧尼 3500.00 200.00 42000
7935 本·貝克曼 5500.00 66000
7936 耶穌布 5500.00 500.00 66000
6100 馬歇爾·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852
6102 阿巴羅·皮薩羅 2540.00 30480
6103 範·奧卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000
7702 Nami 3000.00 1000.00 36000
7703 Usopp 2500.00 500.00 30000
7704 Sanji 2800.00 1200.00 33600
7705 Tony Tony Chopper 100.00 300.00 1200
7706 Nico Robin 5000.00 800.00 60000
7707 Franky 2000.00 500.00 24000
7708 Brook 3000.00 900.00 36000
45 rows selected
查詢員工號, 姓名, 月薪, 傭金, 年薪, 總收入 。 [ 總收入 = 年薪 + 傭金 ]
查詢總收入時遇到一個問題：因爲傭金有的爲NULL，當用NULL 與一個數據進行計算時，得出的結果也是NULL
SQL> select empno "員工編號",ename "姓名",sal "月薪",comm "傭金",sal*12 "年薪",sal*12+comm "總收入" from emp;
員工編號 姓名 月薪 傭金 年薪 總收入
----- -------------------------------------------------- --------- --------- ---------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400 39200
7499 羅羅諾亞·索隆 3450.00 300.00 41400 41700
7521 娜美 4560.00 500.00 54720 55220
7566 蒙奇·D·路飛 5450.00 1000.00 65400 66400
7654 妮可·羅賓 5500.00 2400.00 66000 68400
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00 1000.00 67860 68860
7782 貝波 2450.00 20.00 29400 29420
7788 烏索普 3000.00 36000
7839 尾田_榮一郎 5000.00 60000
7844 弗蘭奇 1500.00 0.00 18000 18000
7876 布魯克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000 73500
7934 托尼托尼·喬巴 800.68 10.00 9608.16 9618.16
7840 岸本_齊史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600 43200
7699 夏奇 2050.00 200.00 24600 24800
7700 佩金 3000.00 200.00 36000 36200
7937 拉基·路 3600.00 500.00 43200 43700
7938 洛克斯達 2600.00 200.00 31200 31400
7783 強巴魯 3600.00 200.00 43200 43400
7784 伊卡庫 3000.00 200.00 36000 36200
7785 庫裡奧尼 3500.00 200.00 42000 42200
7935 本·貝克曼 5500.00 66000
7936 耶穌布 5500.00 500.00 66000 66500
6100 馬歇爾·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852 51857
6102 阿巴羅·皮薩羅 2540.00 30480
6103 範·奧卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000 24800
7702 Nami 3000.00 1000.00 36000 37000
7703 Usopp 2500.00 500.00 30000 30500
7704 Sanji 2800.00 1200.00 33600 34800
7705 Tony Tony Chopper 100.00 300.00 1200 1500
7706 Nico Robin 5000.00 800.00 60000 60800
7707 Franky 2000.00 500.00 24000 24500
7708 Brook 3000.00 900.00 36000 36900
45 rows selected
那麼解決這個問題就需要用到一個函數 nvl(參數1，參數2)
該函數用於當參數1爲空時，爲參數1指定一個代替的值，爲參數2
nvl(參數1,參數2)
那麼可以把語句修改爲：
SQL> select empno "員工編號",ename "姓名",sal "月薪",comm "傭金",sal*12 "年薪",sal*12+nvl(comm,0) "總收入" from emp;
員工編號 姓名 月薪 傭金 年薪 總收入
----- -------------------------------------------------- --------- --------- ---------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400 39200
7499 羅羅諾亞·索隆 3450.00 300.00 41400 41700
7521 娜美 4560.00 500.00 54720 55220
7566 蒙奇·D·路飛 5450.00 1000.00 65400 66400
7654 妮可·羅賓 5500.00 2400.00 66000 68400
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00 1000.00 67860 68860
7782 貝波 2450.00 20.00 29400 29420
7788 烏索普 3000.00 36000 36000
7839 尾田_榮一郎 5000.00 60000 60000
7844 弗蘭奇 1500.00 0.00 18000 18000
7876 布魯克 1100.00 13200 13200
7900 甚平 2000.00 24000 24000
7902 香克斯 6000.00 1500.00 72000 73500
7934 托尼托尼·喬巴 800.68 10.00 9608.16 9618.16
7840 岸本_齊史 5000.00 60000 60000
7709 Jinbe 3550.00 600.00 42600 43200
7699 夏奇 2050.00 200.00 24600 24800
7700 佩金 3000.00 200.00 36000 36200
7937 拉基·路 3600.00 500.00 43200 43700
7938 洛克斯達 2600.00 200.00 31200 31400
7783 強巴魯 3600.00 200.00 43200 43400
7784 伊卡庫 3000.00 200.00 36000 36200
7785 庫裡奧尼 3500.00 200.00 42000 42200
7935 本·貝克曼 5500.00 66000 66000
7936 耶穌布 5500.00 500.00 66000 66500
6100 馬歇爾·D·帝奇 5675.00 68100 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852 51857
6102 阿巴羅·皮薩羅 2540.00 30480 30480
6103 範·奧卡 3450.00 41400 41400
1000 Monkey·D·Luffy 5500.00 66000 66000
1001 Trafalgar·D·Water·Law 5800.00 69600 69600
1002 Marshall·D·Teach 7800.00 93600 93600
1003 Portgas·D·Ace 5500.00 66000 66000
1004 Hagwar·D·Sauro 3450.00 41400 41400
1005 Gol·D·Roger 7500.00 90000 90000
1006 Monkey·D·Dragon 6550.00 78600 78600
1007 Monkey·D·Garp 7890.00 94680 94680
7701 Roronoa Zoro 2000.00 800.00 24000 24800
7702 Nami 3000.00 1000.00 36000 37000
7703 Usopp 2500.00 500.00 30000 30500
7704 Sanji 2800.00 1200.00 33600 34800
7705 Tony Tony Chopper 100.00 300.00 1200 1500
7706 Nico Robin 5000.00 800.00 60000 60800
7707 Franky 2000.00 500.00 24000 24500
7708 Brook 3000.00 900.00 36000 36900
45 rows selected
使用 distinct 關鍵字：select distinct 列 from 表;
SQL> select distinct deptno from emp;
DEPTNO
------
30
20
40
50
10
60
6 rows selected
SQL> select distinct deptno,job from emp;
DEPTNO JOB
------ --------------------
20 考古學家
30 航海士
20 廚師
20 航海士
60 海賊王
40 幹部
40 副船長
40 狙擊手
60 海軍中將
20 船長
60 革命軍首領
20 狙擊手
20 船工
40 船長
50 提督
50 狙擊手
20 醫生
30 船員
60 隊長
30 船長
20 音樂家
20 舵手
40 船員
50 船長
60 船長
20 劍士
10 漫畫家
60 提督
28 rows selected
由以上兩條查詢可以看出，distinct 關鍵字寫在select後面，作用於後面全部的列
SELECT *|{[DISTINCT] column |expression [alias],...}
FROM 表名 [WHERE expression]
可以使用的比較運算子: > , < , = , >= , <= , != , <> , between and
SQL> select ename,deptno from emp where deptno=20;
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
羅羅諾亞·索隆 20
娜美 20
蒙奇·D·路飛 20
妮可·羅賓 20
烏索普 20
弗蘭奇 20
布魯克 20
甚平 20
托尼托尼·喬巴 20
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
19 rows selected
注意：如果欄位的值是字串的話，描述的時候要加上 ’ ’ 兩個單引號
SQL> select ename,deptno from emp where ename='Nico Robin';
ENAME DEPTNO
-------------------------------------------------- ------
Nico Robin 20
SQL> select ename,sal from emp where sal!=5500;
SQL> select ename,sal from emp where sal<>5500;
ENAME SAL
-------------------------------------------------- ---------
文斯莫克·山治 3200.00
羅羅諾亞·索隆 3450.00
娜美 4560.00
蒙奇·D·路飛 5450.00
特拉法爾加·D·瓦鐵爾·羅 5655.00
貝波 2450.00
烏索普 3000.00
尾田_榮一郎 5000.00
弗蘭奇 1500.00
布魯克 1100.00
甚平 2000.00
香克斯 6000.00
托尼托尼·喬巴 800.68
岸本_齊史 5000.00
Jinbe 3550.00
夏奇 2050.00
佩金 3000.00
拉基·路 3600.00
洛克斯達 2600.00
強巴魯 3600.00
伊卡庫 3000.00
庫裡奧尼 3500.00
馬歇爾·D·帝奇 5675.00
芝沙斯·巴沙斯 4321.00
阿巴羅·皮薩羅 2540.00
範·奧卡 3450.00
Trafalgar·D·Water·Law 5800.00
Marshall·D·Teach 7800.00
Hagwar·D·Sauro 3450.00
Gol·D·Roger 7500.00
Monkey·D·Dragon 6550.00
Monkey·D·Garp 7890.00
Roronoa Zoro 2000.00
Nami 3000.00
Usopp 2500.00
Sanji 2800.00
Tony Tony Chopper 100.00
Nico Robin 5000.00
Franky 2000.00
Brook 3000.00
40 rows selected
SQL> select ename,sal from emp where sal between 1000 and 2000;
ENAME SAL
-------------------------------------------------- ---------
弗蘭奇 1500.00
布魯克 1100.00
甚平 2000.00
Roronoa Zoro 2000.00
Franky 2000.00
邏輯運算子包括:
或: or
與: and
非: not
SQL> select ename,deptno from emp where deptno=10 or deptno=20;
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
羅羅諾亞·索隆 20
娜美 20
蒙奇·D·路飛 20
妮可·羅賓 20
烏索普 20
尾田_榮一郎 10
弗蘭奇 20
布魯克 20
甚平 20
托尼托尼·喬巴 20
岸本_齊史 10
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
21 rows selected
SQL> select ename,sal,deptno from emp where deptno=20 and sal=5500;
ENAME SAL DEPTNO
-------------------------------------------------- --------- ------
妮可·羅賓 5500.00 20
-- 需要使用日期轉換函數 to_date() ，可以將字串型別轉換爲日期，然後通過日期的比較來進行判斷
to_char('1981/2/1', 'yyyy-mm-dd')，to_char('1982/1/31','yyyy-mm-dd')
SQL> select ename, hiredate from emp where hiredate>=to_date('1981/2/1', 'yyyy-mm-dd') and hiredate<=to_date('1982/1/31','yyyy-mm-dd');
ENAME HIREDATE
-------------------------------------------------- -----------
羅羅諾亞·索隆 1981/2/20
娜美 1981/2/22
蒙奇·D·路飛 1981/4/2
妮可·羅賓 1981/9/28
特拉法爾加·D·瓦鐵爾·羅 1981/5/1
貝波 1981/6/9
尾田_榮一郎 1981/11/17
弗蘭奇 1981/9/8
甚平 1981/12/3
香克斯 1981/12/3
托尼托尼·喬巴 1982/1/23
岸本_齊史 1981/11/17
夏奇 1981/5/1
佩金 1981/5/1
強巴魯 1981/5/1
伊卡庫 1981/5/1
庫裡奧尼 1981/5/1
17 rows selected
-- 數據庫中日期和字元直接是可以自動轉換的
-- 要求日期格式和字串格式能匹配上就可以進行比較, 如果格式對不上就不能比較
-- 數據庫中日期的預設儲存形式: NLS_DATE_FORMAT DD-MON-RR
-- 不推薦使用這種方式, 適用性不強
select * from emp where hiredate>'01-2月-81' and hiredate<='31-1月-82';
-- 如何判斷空欄位
-- 使用 is null 關鍵字，判斷不爲空要使用 is not null 關鍵字
--如果使用 =null 或者 !=null ，這兩個判斷得到的結果都是false，則不能進行正確的判斷
SQL> select ename, comm from emp where comm is null;
ENAME COMM
-------------------------------------------------- ---------
烏索普
尾田_榮一郎
布魯克
甚平
岸本_齊史
本·貝克曼
馬歇爾·D·帝奇
阿巴羅·皮薩羅
範·奧卡
Monkey·D·Luffy
Trafalgar·D·Water·Law
Marshall·D·Teach
Portgas·D·Ace
Hagwar·D·Sauro
Gol·D·Roger
Monkey·D·Dragon
Monkey·D·Garp
17 rows selected
SQL> select ename, comm from emp where comm is not null;
ENAME COMM
-------------------------------------------------- ---------
文斯莫克·山治 800.00
羅羅諾亞·索隆 300.00
娜美 500.00
蒙奇·D·路飛 1000.00
妮可·羅賓 2400.00
特拉法爾加·D·瓦鐵爾·羅 1000.00
貝波 20.00
弗蘭奇 0.00
香克斯 1500.00
托尼托尼·喬巴 10.00
Jinbe 600.00
夏奇 200.00
佩金 200.00
拉基·路 500.00
洛克斯達 200.00
強巴魯 200.00
伊卡庫 200.00
庫裡奧尼 200.00
耶穌布 500.00
芝沙斯·巴沙斯 5.00
Roronoa Zoro 800.00
Nami 1000.00
Usopp 500.00
Sanji 1200.00
Tony Tony Chopper 300.00
Nico Robin 800.00
Franky 500.00
Brook 900.00
28 rows selected
-- 邏輯比較符使用的時候要注意優先順序：
-- not > and > or
-- 如果要修改優先順序，需要加上()括號
SQL> select deptno, sal from emp where (deptno=20 or deptno=30) and sal=3000;
DEPTNO SAL
------ ---------
20 3000.00
30 3000.00
30 3000.00
20 3000.00
20 3000.00
集合的表示：in (元素, 元素, 元素)
語法格式: in() -> 表示要判斷的數據在集合裏邊, 只有集閤中有一個元素滿足判斷條件即可，in集合相當於or
-- 如果使用邏輯運算子
SQL> select ename, deptno from emp where deptno=10 or deptno=20;
-- 使用in集合
SQL> select ename, deptno from emp where deptno in(10,20);
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
羅羅諾亞·索隆 20
娜美 20
蒙奇·D·路飛 20
妮可·羅賓 20
烏索普 20
尾田_榮一郎 10
弗蘭奇 20
布魯克 20
甚平 20
托尼托尼·喬巴 20
岸本_齊史 10
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
21 rows selected
-- in集合可不可以出現null，比如：in(10,20,null)
-- in集合相當於是or操作，而與null的判斷恆爲false，所以不會影響結果的產生，如果誤寫上去不會造成查詢的影響
SQL> select ename, deptno from emp where deptno in(10,20,null);
-- not in 表示不在集閤中，查詢以上資訊可以使用not in
-- not in 是相當於 and 比較，不等於元素1 and 不等於元素2 。。。
-- 使用邏輯運算子進行查詢
SQL> select ename, deptno from emp where deptno!=10 and deptno!=20;
-- 使用not in集合
SQL> select ename, deptno from emp where deptno not in(10,20);
ENAME DEPTNO
-------------------------------------------------- ------
特拉法爾加·D·瓦鐵爾·羅 30
貝波 30
香克斯 40
夏奇 30
佩金 30
拉基·路 40
洛克斯達 40
強巴魯 30
伊卡庫 30
庫裡奧尼 30
本·貝克曼 40
耶穌布 40
馬歇爾·D·帝奇 50
芝沙斯·巴沙斯 50
阿巴羅·皮薩羅 50
範·奧卡 50
Monkey·D·Luffy 60
Trafalgar·D·Water·Law 60
Marshall·D·Teach 60
Portgas·D·Ace 60
Hagwar·D·Sauro 60
Gol·D·Roger 60
Monkey·D·Dragon 60
Monkey·D·Garp 60
24 rows selected
-- not in中是否可以加null？ 不可以！
-- null與值的判斷得到的結果是false，而not in做的是and的邏輯判斷，出現一個false，則無法查詢到任何的記錄
-- 結論：如果在not in 集閤中出現了null，查詢結果爲空
SQL> select ename, deptno from emp where deptno not in(10,20,null);
ENAME DEPTNO
-------------------------------------------------- ------
在sql查詢中, like 關鍵字出現在where 語句中欄位名的後邊, 欄位值是前邊
模糊查詢中需要使用萬用字元, 常用的有兩個:
- % - 匹配任意多個字元
- 0個或者1個或者多個
- _ - 匹配一個字元
SQL> select ename from emp where ename like '%D%';
ENAME
--------------------------------------------------
蒙奇·D·路飛
特拉法爾加·D·瓦鐵爾·羅
馬歇爾·D·帝奇
Monkey·D·Luffy
Trafalgar·D·Water·Law
Marshall·D·Teach
Portgas·D·Ace
Hagwar·D·Sauro
Gol·D·Roger
Monkey·D·Dragon
Monkey·D·Garp
11 rows selected
SQL> select empno from emp where empno like '79%';
EMPNO
-----
7900
7902
7934
7935
7936
7937
7938
7 rows selected
SQL> select ename from emp where ename like '____';
ENAME
--------------------------------------------------
拉基·路
洛克斯達
庫裡奧尼
範·奧卡
Nami
-- 模糊查詢中需要查詢的普通字串中有一個 _ ，那麼需要做的就是對 _ 進行跳脫
-- 使用 escape 關鍵字進行跳脫，sql中沒有一個固定的跳脫字元，所以需要自定義一個跳脫字元，使用escape
SQL> select ename from emp where ename like '%*_%' escape '*';
ENAME
--------------------------------------------------
尾田_榮一郎
岸本_齊史
--其中 escape 指定了 * 爲跳脫字元，那麼跟在它後面的 _ 被跳脫成了普通的字元
-- 排序的關鍵字：order by
-- order by 有兩種排序的方式：asc升序和desc降序，預設是asc升序，預設可不寫
select 列名|表達式 from 表名 where 條件 order by 列名|別名|表達式|number(序號) asc|desc;
-- 升序
SQL> select ename,hiredate from emp order by hiredate;
ENAME HIREDATE
-------------------------------------------------- -----------
文斯莫克·山治 1980/12/17
Brook 1980/12/17
Franky 1980/12/17
Nico Robin 1980/12/17
Roronoa Zoro 1980/12/17
Sanji 1980/12/17
SQL> select ename, sal from emp order by sal desc;
ENAME SAL
-------------------------------------------------- ---------
Monkey·D·Garp 7890.00
Marshall·D·Teach 7800.00
Gol·D·Roger 7500.00
Monkey·D·Dragon 6550.00
香克斯 6000.00
Trafalgar·D·Water·Law 5800.00
馬歇爾·D·帝奇 5675.00
SQL> select ename, comm from emp order by comm desc;
-- 當使用該語句時會發現一個問題，所有的comm爲null的記錄全部顯示在最上面
-- 那麼也就是null在排序的時候當作最大值來排序
-- 將值爲 null 的記錄顯示在最下面 下麪，需要新增關鍵字：nulls last
SQL> select ename, comm from emp order by comm desc nulls last;
ENAME COMM
-------------------------------------------------- ---------
妮可·羅賓 2400.00
香克斯 1500.00
Sanji 1200.00
蒙奇·D·路飛 1000.00
特拉法爾加·D·瓦鐵爾·羅 1000.00
Nami 1000.00
Brook 900.00
文斯莫克·山治 800.00
-- 當有多個巢狀排序時，在前一個排序規則後面接着寫下一個排序規則即可
-- 先升序排序deptno ，當deptno相等的時候，再降序排序sal
SQL> select ename, deptno, sal from emp order by deptno, sal desc;
ENAME DEPTNO SAL
-------------------------------------------------- ------ ---------
岸本_齊史 10 5000.00
尾田_榮一郎 10 5000.00
妮可·羅賓 20 5500.00
蒙奇·D·路飛 20 5450.00
Nico Robin 20 5000.00
娜美 20 4560.00
Jinbe 20 3550.00
羅羅諾亞·索隆 20 3450.00
文斯莫克·山治 20 3200.00
Brook 20 3000.00
烏索普 20 3000.00
Nami 20 3000.00
Sanji 20 2800.00
Usopp 20 2500.00
甚平 20 2000.00
Franky 20 2000.00
Roronoa Zoro 20 2000.00
弗蘭奇 20 1500.00
布魯克 20 1100.00
托尼托尼·喬巴 20 800.68
Tony Tony Chopper 20 100.00
特拉法爾加·D·瓦鐵爾·羅 30 5655.00
強巴魯 30 3600.00
庫裡奧尼 30 3500.00
佩金 30 3000.00
伊卡庫 30 3000.00
貝波 30 2450.00
夏奇 30 2050.00
香克斯 40 6000.00
耶穌布 40 5500.00
本·貝克曼 40 5500.00
拉基·路 40 3600.00
洛克斯達 40 2600.00
馬歇爾·D·帝奇 50 5675.00
芝沙斯·巴沙斯 50 4321.00
範·奧卡 50 3450.00
阿巴羅·皮薩羅 50 2540.00
Monkey·D·Garp 60 7890.00
Marshall·D·Teach 60 7800.00
Gol·D·Roger 60 7500.00
Monkey·D·Dragon 60 6550.00
Trafalgar·D·Water·Law 60 5800.00
Portgas·D·Ace 60 5500.00
Monkey·D·Luffy 60 5500.00
Hagwar·D·Sauro 60 3450.00
45 rows selected
-- 序號指的是在查詢欄位在結果集中的位置，位置編號從1號開始
-- 假如有：select a,b,c,d ，那麼a就是1號，b就是2號，c就是3號，d就是4號
-- order by 指定編號 即可實現排序
查詢員工編號, 員工姓名和工資, 按照 工資的序號 進行排序
SQL> select empno, ename, sal from emp order by 3 desc;
EMPNO ENAME SAL
----- -------------------------------------------------- ---------
1007 Monkey·D·Garp 7890.00
1002 Marshall·D·Teach 7800.00
1005 Gol·D·Roger 7500.00
1006 Monkey·D·Dragon 6550.00
7902 香克斯 6000.00
1001 Trafalgar·D·Water·Law 5800.00
6100 馬歇爾·D·帝奇 5675.00
7698 特拉法爾加·D·瓦鐵爾·羅 5655.00
-- sql語句中的 3 號代表sal
-- 別名: 就是數據庫表中給某個欄位設定的別名.
按照員工的年收入進行排序
SQL> select ename "姓名", sal*12+nvl(comm,0) "年收入" from emp order by "年收入" desc;
姓名 年收入
-------------------------------------------------- ----------
Monkey·D·Garp 94680
Marshall·D·Teach 93600
Gol·D·Roger 90000
Monkey·D·Dragon 78600
香克斯 73500
Trafalgar·D·Water·Law 69600
特拉法爾加·D·瓦鐵爾·羅 68860
妮可·羅賓 68400
馬歇爾·D·帝奇 68100
耶穌布 66500
-- 直接在order by 後面指定前面設定好的別名即可
按照員工的年收入進行排序
SQL> select ename "姓名", sal*12+nvl(comm,0) "年收入" from emp order by sal*12+nvl(comm,0) desc;
姓名 年收入
-------------------------------------------------- ----------
Monkey·D·Garp 94680
Marshall·D·Teach 93600
Gol·D·Roger 90000
Monkey·D·Dragon 78600
香克斯 73500
Trafalgar·D·Water·Law 69600
特拉法爾加·D·瓦鐵爾·羅 68860
妮可·羅賓 68400
馬歇爾·D·帝奇 68100
耶穌布 66500
--直接在 order by 後面指定表達式接即可
任何東西，只要它
能接收輸入，對輸入進行加工併產生輸出，它就可以被稱爲函數。
單行函數只對表中的一行數據進行操作，並且對每一行數據只產生一個輸出結果。單行函數可以接受一個或多個參數，其產生的輸出結果的數據型別可能與參數的數據型別不同。單行函數分爲五種型別：字元函數、數值函數、日期函數、轉換函數、通用函數
LOWER、UPPER、INITCAP
-- LOWER(列名|表達式)： 該函數是把字元轉換成小寫。
-- UPPER(列名|表達式)： 該函數是把字元轉換成大寫。
-- INITCAP(列名|表達式)： 該函數是把每個字的頭一個字元轉換成大寫，其餘的轉換成小寫。
-- dual是一個虛表, 沒有數據, 主要作用是和select配合使用, 組成一個完整的sql語句
-- 虛表是一個沒有任何數據的表, 在select的時候使用dual目的是將這個sql語句補充完整, 但是這個dual在這個查詢語句中起不到任何作用
-- lower() 字母轉換爲小寫
SQL> select lower(ename) from emp;
SQL> select lower('Hello WORLD') from dual;
LOWER('HELLOWORLD')
-------------------
hello world
-- upper() 字母轉換爲大寫
SQL> select upper(ename) from emp;
SQL> select upper('Hello World') from dual;
UPPER('HELLOWORLD')
-------------------
HELLO WORLD
-- initcap(), 單詞首字母轉換爲大寫
SQL> select initcap(ename) from emp;
SQL> select initcap('hello world') from dual;
INITCAP('HELLOWORLD')
---------------------
Hello World
CONCAT
-- 有兩個參數,連線兩個字串
-- CONCAT(列名|表達式，列名|表達式)：該函數是把頭一個字串和第二個字串連線成一個字串。
-- 注意事項:
-- 該函數只能連線兩個字串, 如果需要連線多個, 需要使用 ||
-- 使用 函數 concat() 連線兩個字串
SQL> select concat (empno, ename) from emp;
SQL> select concat('hello,', ' world') from dual;
CONCAT('HELLO,','WORLD')
------------------------
hello, world
-- 使用 || 實現多個字串的連線
SQL> select empno || '-' || ename || sal from emp;
SQL> select 'hello world' || 'abcdefg' || 'xxx' from dual;
'HELLOWORLD'||'ABCDEFG'||'XXX'
------------------------------
hello worldabcdefgxxx
SUBSTR
-- SUBSTR(列名|表達式，pos,[len])：字串擷取, 返回的字串是從第pos個字元開始，其長度爲len。
SQL> select substr(ename, 1, 5) from emp;
-- 從第8個字元開始, 擷取長度爲5的子字串
SQL> select substr('hello, world', 8, 5) from dual;
SUBSTR('HELLO,WORLD',8,5)
-------------------------
world
LENGTH、LENGTHB
-- LENGTH(列名|表達式)： 該函數是返回列中或表達式中 字串 的長度。
-- LENGTHB(列名|表達式)：該函數是返回列中或表達式中字串的 位元組 的長度。
-- 查詢字串長度
SQL> select dname, length(dname) from dept;
DNAME LENGTH(DNAME)
-------------------------------------------------- -------------
黑鬍子海賊團 6
黑桃海賊團 5
D之一族 4
週刊少年Jump 8
草帽海賊團 5
紅心海賊團 5
紅髮海賊團 5
7 rows selected
-- 查詢位元組數
SQL> select dname, lengthb(dname) from dept;
DNAME LENGTHB(DNAME)
-------------------------------------------------- --------------
黑鬍子海賊團 18
黑桃海賊團 15
D之一族 10
週刊少年Jump 16
草帽海賊團 15
紅心海賊團 15
紅髮海賊團 15
7 rows selected
-- 判斷字串中有沒有中文
SQL> select ename from emp where length(ename)=lengthb(ename);
ENAME
--------------------------------------------------
Jinbe
Roronoa Zoro
Nami
Usopp
Sanji
Tony Tony Chopper
Nico Robin
Franky
Brook
9 rows selected
INSTR
-- INSTR(列名|表達式，'子字串'，[m],[n])：該函數是返回所給子字串出現的位置，沒有返回0
-- 參數: m表示從第m個字元開始搜尋，n表示所給字串出現的次數，它們的預設值都爲1。
SQL> select ename, instr(ename, 'D', 2, 2) from emp;
ENAME INSTR(ENAME,'D',2,2)
Portgas·D·Ace 0
Hagwar·D·Sauro 0
Gol·D·Roger 0
Monkey·D·Dragon 10
Monkey·D·Garp 0
Roronoa Zoro 0
TRIM
-- TRIM([leading|trailing|both] '要去掉的字元' FROM '源字串')：去掉字串兩端的指定字元
-- TRIM('要去掉的字元' FROM '源字串'): 預設是both 去掉兩端的指定字元
-- 去空格(前後都去掉)
SQL> select trim(' hello, world ') || 'end' from dual;
TRIM('HELLO,WORLD')||'END'
--------------------------
hello, worldend
-- 其他字元
-- 去掉頭部的 *
SQL> select trim(leading '*' from '*******hello, world*******') || 'end' from dual;
TRIM(LEADING'*'FROM'*******HELLO,WORLD*******')||'END'
------------------------------------------------------
hello, world*******end
-- 去掉尾部的 *
SQL> select trim(trailing '*' from '*******hello, world*******') || 'end' from dual;
TRIM(TRAILING'*'FROM'*******HELLO,WORLD*******')||'END'
-------------------------------------------------------
*******hello, worldend
-- 頭部和尾部的 * 都去掉
SQL> select trim(both '*' from '*******hello, world*******') || 'end' from dual;
TRIM(BOTH'*'FROM'*******HELLO,WORLD*******')||'END'
---------------------------------------------------
hello, worldend
REPLACE
-- REPLACE(正文表達式，old，new)
-- 該函數是在"正文表達式"中查詢"old"，如果找到了就用"new"替代。
-- 注意：該函數會替換所有正文表達式中包含的old爲new
SQL> select replace('aaaaccbb', 'cc', 'xx') from dual;
REPLACE('AAAACCBB','CC','XX')
-----------------------------
aaaaxxbb
LPAD、RPAD
-- LPAD(列名|表達式, len, ch): 返回len長度的字串, 如果不夠len, 在字串左側填充ch
-- RPAD(列名|表達式, len, ch): 返回len長度的字串, 如果不夠len, 在字串右側填充ch
-- 使用這兩個函數也可以用來做截斷，即指定比當前字串len小的一個值
SQL> select lpad('aaaaccbb', 12, '*') from dual;
-- 在左側填充*, 最終得到12個位元組
LPAD('AAAACCBB',12,'*')
-----------------------
****aaaaccbb
-- 在左側填充*, 最終得到6個位元組, 如果字串長度>=6位元組, 不需要填充並且刪除多餘的部分
SQL> select lpad('aaaaccbb', 6, '*') from dual;
LPAD('AAAACCBB',6,'*')
----------------------
aaaacc
-- 在右側填充*, 最終得到16個位元組
SQL> select rpad('aaaaccbb', 16, '*') from dual;
RPAD('AAAACCBB',16,'*')
-----------------------
aaaaccbb********
ROUND
-- ROUND(列名|表達式，[n])：該函數將列名或表達式所表示的數值四捨五入到小數點後的n位。
SQL> select round(12.346, 2) from dual;
ROUND(12.346,2)
---------------
12.35
SQL> select round(12.346) from dual;
ROUND(12.346)
-------------
12
SQL> select round(12.546) from dual;
ROUND(12.546)
-------------
13
TRUNC
-- TRUNC(列名|表達式，[n])：該函數將列名或表達式所表示的數值擷取到小數點後的n位。
SQL> select trunc(12.546) from dual;
TRUNC(12.546)
-------------
12
SQL> select trunc(12.546, 2) from dual;
TRUNC(12.546,2)
---------------
12.54
MOD
-- MOD(m,n)：該函數將m除以n並取餘數。
SQL> select mod(5, 3) from dual;
MOD(5,3)
----------
2
CEIL、FLOOR
-- CEIL(m): 向上取整
-- FLOOR(m)：向下取整
SQL> select ceil(12.6) from dual;
CEIL(12.6)
----------
13
SQL> select ceil(12.1) from dual;
CEIL(12.1)
----------
13
SQL> select floor(12.1) from dual;
FLOOR(12.1)
-----------
12
SQL> select floor(12.9) from dual;
FLOOR(12.9)
-----------
12
TO_CHAR
日期轉換
-- TO_CHAR(日期，‘fmt')：將日期型數據轉換成指定模式(fmt)的字串
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
---------------------------------------
2019-11-28 17:20:09
SQL> select to_char(sysdate, 'yyyy/mm/dd -- hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY/MM/DD--HH24:MI:SS')
-----------------------------------------
2019/11/28 -- 17:20:22
常用的日期模式 (不區分大小寫):
| 日期模式 | 解釋 |
|---|---|
| YYYY | 完整的年份數位表示(如2001) |
| YEAR | 年份的英文表示(如NINETEEN EIGHTY-SEVEN) |
| MM | 用兩位數位來表示月份 |
| MONTH/MON | 月份中文名 |
| DAY/DY | 表示星期幾 |
| DD | 使用兩位數位表示日期 |
| YY/RR | 使用兩位數位表示年份 |
| HH/HH24 | HH24表示24小時計時法的時間, hh需要在時間尾部加 am |
| MI | 使用兩位數表示分鐘 |
| SS | 使用兩位數表示秒數 |
# MONTH/MON: 如果月份是用英文表示的, month表示完整的英文月份, mon表示英文月份的縮寫
# DAY/DY: 如果月份是用英文表示的, DAY表示星期幾的完整的英文, DY用3個英文字元的縮寫來表示星期幾
數位轉換
-- TO_CHAR(數位，‘fmt')：該函數的這種格式把數位型數據轉換成變長字串。
SQL> select ename, to_char(sal, 'L9,999.99') from emp;
ENAME TO_CHAR(SAL,'L9,999.99')
文斯莫克·山治 ￥3,200.00
羅羅諾亞·索隆 ￥3,450.00
娜美 ￥4,560.00
蒙奇·D·路飛 ￥5,450.00
妮可·羅賓 ￥5,500.00
特拉法爾加·D·瓦鐵爾·羅 ￥5,655.00
常用的數位模式
| 數位模式 | 解釋 |
|---|---|
| 9 | 表示數位 |
| 0 | 表示數位 |
| $ | 顯示美元符號 |
| L | 顯示本地貨幣符號 |
| . | 顯示小數點 |
| , | 顯示千位符號, 如: 9,999 |
| MI | 在數的右邊顯示減號 |
| PR | 把負數用尖括號擴起來 |
TO_NUMBER
-- TO_NUMBER(字串 ,'fmt‘)：該函數把字串轉換成數位。
SQL> select to_number('￥3,090.73', 'L9,999.99') from dual;
TO_NUMBER('￥3,090.73','L9,999.99')
----------------------------------
3090.73
TO_DATE
-- TO_DATE(字串 ,'fmt’)：該函數把字串轉換成日期型數據。
SQL> select to_date('1970/01/01 13:24:34', 'yyyy/mm/dd hh24:mi:ss') from dual;
TO_DATE('1970/01/0113:24:34','YYYY/MM/DDHH24:MI:SS')
----------------------------------------------------
1970/1/1 13:24:34
隱式轉換
將變長字元型(VARCHAR2)或定長字元型(CHAR)轉換成數位型(NUMBER)；
將變長字元型(VARCHAR2)或定長字元型(CHAR)轉換成日期型(DATE)；
將數位型(NUMBER)轉換成變長字元型(VARCHAR2)；
將日期型(DATE)轉換成變長字元型(VARCHAR2)。
SYSDATE
SYSDATE是我們在Oracle開發中經常要用到的一種單行函數(single row function), 該函數用以返回當前的日期與時間，常和DUAL僞表一起合作。
SYSDATE實際上指的是SYSDATE()函數，但是因爲這個函數沒有參量，所以這裏可以省略()圓括號。通常用於獲取數據庫所在的操作系統的當前時間值的, 我們可以使用 TO_CHAR 函數來獲得我們想要的SYSDATE日期格式 。
注意：sysdate函數不要寫括號，否則會出錯
MONTHS_BETWEEN：
-- MONTHS_BETWEEN(日期1，日期2)：該函數是返回日期1和日期2之間的月數, 返回值=日期1-日期2
SQL> select months_between('11-1月-19', '9-10月-18') from dual;
MONTHS_BETWEEN('11-1月-19','9-10月-18')
-------------------------------------
3.06451612903226
SQL> select months_between('11-1月-19', '9-10月-19') from dual;
MONTHS_BETWEEN('11-1月-19','9-10月-19')
-------------------------------------
-8.93548387096774
ADD_MONTHS：
-- ADD_MONTHS(日期，n)：該函數是把n個月加到日期上。
SQL> select add_months(sysdate, 10) from dual;
ADD_MONTHS(SYSDATE,10)
----------------------
2020/9/29 9:07:44
NEXT_DAY
-- NEXT_DAY(日期，'星期幾'|1-7)：該函數返回指定日期的下一個星期幾是多少號。
SQL> select next_day(sysdate, '星期五') from dual;
-- 星期一, 星期二, .....
NEXT_DAY(SYSDATE,'星期五')
-----------------------
2019/12/6 9:09:39
-- 1: 星期天, 2: 星期一, 3:星期二....
SQL> select next_day(sysdate, 5) from dual;
NEXT_DAY(SYSDATE,5)
-------------------
2019/12/5 9:10:24
LAST_DAY
-- LAST_DAY(日期)：該函數是返回該日期所在月的最後一天。
SQL> select last_day('1-2月-19') from dual;
LAST_DAY('1-2月-19')
-------------------
2019/2/28
SQL> select last_day('1-2月-00') from dual;
LAST_DAY('1-2月-00')
-------------------
2000/2/29
ROUND和TRUNC函數用於日期型數據
-- ROUND(日期，日期模式): 對指定日期四捨五入
-- TRUNC(日期，日期模式): 對指定日期截斷
SQL> select hiredate, round(hiredate, 'mm') from emp;
HIREDATE ROUND(HIREDATE,'MM')
----------- --------------------
1980/12/17 1981/1/1
1981/2/20 1981/3/1
SQL> select hiredate, round(hiredate, 'yyyy') from emp;
HIREDATE ROUND(HIREDATE,'YYYY')
----------- ----------------------
1980/12/17 1981/1/1
1981/2/20 1981/1/1
-- TRUNC(日期，日期模式): 對指定日期截斷
SQL> select hiredate, trunc(hiredate, 'mm') from emp;
HIREDATE TRUNC(HIREDATE,'MM')
----------- --------------------
1980/12/17 1980/12/1
1981/2/20 1981/2/1
SQL> select hiredate, trunc(hiredate, 'yyyy') from emp;
HIREDATE TRUNC(HIREDATE,'YYYY')
----------- ----------------------
1980/12/17 1980/1/1
1981/2/20 1981/1/1
1981/2/22 1981/1/1
NVL
-- NVL(expr1, expr2):
-- 如果oracle第一個參數爲空那麼顯示第二個參數的值，
-- 如果第一個參數的值不爲空，則顯示第一個參數本來的值
SQL> select ename,NVL(comm, -1) from emp;
NVL2
-- NVL2(expr1, expr2, expr3)
-- 如果該函數的第一個參數爲空那麼顯示第三個參數的值
-- 如果第一個參數的值不爲空，則顯示第二個參數的值
SQL> select ename, comm, nvl(comm,0), nvl2(comm, 0, 1) from emp;
ENAME COMM NVL(COMM,0) NVL2(COMM,0,1)
文斯莫克·山治 800.00 800 0
羅羅諾亞·索隆 300.00 300 0
娜美 500.00 500 0
蒙奇·D·路飛 1000.00 1000 0
妮可·羅賓 2400.00 2400 0
特拉法爾加·D·瓦鐵爾·羅 1000.00 1000 0
貝波 20.00 20 0
烏索普 0 1
尾田_榮一郎 0 1
弗蘭奇 0.00 0 0
布魯克 0 1
甚平 0 1
香克斯 1500.00 1500 0
托尼托尼·喬巴 10.00 10 0
岸本_齊史 0 1
## 5.6 條件表達式
```sql
sql> select ...,
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
from table;
-- 查詢的sql語句語法:
select 列名|表達式 from 表名 [where 條件 order by 類名 asc|desc];
select 列名|帶條件的表達式 from 表名;
-- c語言條件判斷: if ... else
-- sql條件判斷: when ... then ... when ... then ... else (當某個條件滿足, 我就去幹
什麼)
老闆打算給員工漲工資, 要求：
船長漲1000，廚師漲800，其他人漲400, 請將漲前，漲後的薪水列出。
ENAME JOB 漲薪前 漲薪後
-------------------------------------------------- -------------------- --------- ----------
文斯莫克·山治 廚師 3200.00 4000
羅羅諾亞·索隆 劍士 3450.00 3850
娜美 航海士 4560.00 4960
蒙奇·D·路飛 船長 5450.00 6450
妮可·羅賓 考古學家 5500.00 5900
特拉法爾加·D·瓦鐵爾·羅 船長 5655.00 6655
貝波 航海士 2450.00 2850
烏索普 狙擊手 3000.00 3400
尾田_榮一郎 漫畫家 5000.00 5400
弗蘭奇 船工 1500.00 1900
布魯克 音樂家 1100.00 1500
甚平 舵手 2000.00 2400
香克斯 船長 6000.00 7000
托尼托尼·喬巴 醫生 800.68 1200.68
岸本_齊史 漫畫家 5000.00 5400
Jinbe 舵手 3550.00 3950
夏奇 船員 2050.00 2450
佩金 船員 3000.00 3400
拉基·路 幹部 3600.00 4000
洛克斯達 船員 2600.00 3000
強巴魯 船員 3600.00 4000
伊卡庫 船員 3000.00 3400
庫裡奧尼 船員 3500.00 3900
本·貝克曼 副船長 5500.00 5900
耶穌布 狙擊手 5500.00 5900
馬歇爾·D·帝奇 提督 5675.00 6075
芝沙斯·巴沙斯 船長 4321.00 5321
阿巴羅·皮薩羅 船長 2540.00 3540
範·奧卡 狙擊手 3450.00 3850
Monkey·D·Luffy 船長 5500.00 6500
Trafalgar·D·Water·Law 船長 5800.00 6800
Marshall·D·Teach 提督 7800.00 8200
Portgas·D·Ace 隊長 5500.00 5900
Hagwar·D·Sauro 海軍中將 3450.00 3850
Gol·D·Roger 海賊王 7500.00 7900
Monkey·D·Dragon 革命軍首領 6550.00 6950
Monkey·D·Garp 海軍中將 7890.00 8290
Roronoa Zoro 劍士 2000.00 2400
Nami 航海士 3000.00 3400
Usopp 狙擊手 2500.00 2900
Sanji 廚師 2800.00 3600
Tony Tony Chopper 醫生 100.00 500
Nico Robin 考古學家 5000.00 5400
Franky 船工 2000.00 2400
Brook 音樂家 3000.00 3400
45 rows selected
和單行函數相比，oracle提供了豐富的基於組的，多行的函數。這些函數能在select或select的having
子句中使用，當用於select子串時常常都和GROUP BY一起使用。多行函數分爲接收多個輸入，返回一
個輸出。
多行函數又稱爲分組函數，因爲嘗與group by 搭配分組使用。
多行函數也可以稱之爲分組函數, 分組函數具有過濾空欄位的功能.
COUNT(列名)
-- 統計員工總數
-- 只要ename的值不爲空，則計算在內，計數+1
SQL> select count(ename) from emp;
COUNT(ENAME)
------------
45
-- count(*) 表示只要表中的記錄不爲全空，則計算在內
SQL> select count(*) from emp;
COUNT(*)
----------
45
-- 統計工種總數
SQL> select count(job) from emp;
COUNT(JOB)
----------
45
-- 以上的sql語句查詢結果中有非常多的重複項，沒有做去重的處理
-- 去重使用distinct關鍵字，放在count()函數的括號裏面
SQL> select count(distinct job) from emp;
COUNT(DISTINCTJOB)
------------------
19
AVG(列名)
-- 求員工的平均薪資
SQL> select avg(sal) from emp;
AVG(SAL)
----------
3932.03733
-- 求員工的平均傭金 單獨使用avg不計算爲null的記錄
SQL> select avg(comm) from emp;
AVG(COMM)
----------
583.392857
SUN(列名)
-- 求員工的平均薪資
SQL> select sum(sal)/count(*) from emp;
SUM(SAL)/COUNT(sal )
-----------------
3932.03733333333
-- 求員工的平均傭金
SQL> select sum(comm)/count(comm) from emp;
SUM(COMM)/COUNT(COMM)
---------------------
583.392857142857
-- 如果使用count(*)作爲除數，那麼計算結果將與以上不同。
-- 原因：count(comm)不統計comm爲null的記錄。count(*)統計了comm爲null的記錄，所以除數變大。
SQL> select sum(comm)/count(*) from emp;
SUM(COMM)/COUNT(*)
------------------
363
MAX(列名)
-- 求員工的最高工資
SQL> select max(sal) from emp;
MAX(SAL)
----------
7890
MIN(列名)
-- 求員工的最低工資
SQL> select min(sal) from emp;
MIN(SAL)
----------
100
在使用多行函數進行統計的時候, 如何將空欄位也統計在內?
使用nvl(comm,0)
SQL> select sum(comm)/count(nvl(comm,0)) from emp;
SUM(COMM)/COUNT(NVL(COMM,0))
----------------------------
363
-- 基本格式
-- 按照group by後給定的表達式, 將from後的table進行分組, 針對每一組使用分組函數
-- 如果select後邊的列名沒有出現在分組函數中, 那麼一定出現在group by的後邊
select 列名1, 列名2, ..., 分組函數(列名) from 表名 where ... group by 列名1, 列名2 having ...;
結論：select後面寫什麼 group by後面就寫什麼，select後面的內容必須時group by後面內容的真子集。該內容不包括分組函數。
-- 根據部門進行分組, 然後再計算每個部門的平均薪資
-- 在進行分組統計的時候, 出現在select後邊的欄位名, 也必須出現在group by的後邊,
-- 也就是說要對那些資訊進行分組統計, 那麼就在結果集中顯示哪些資訊
SQL> select deptno,avg(sal) from emp group by deptno order by deptno;
DEPTNO AVG(SAL)
------ ----------
10 5000
20 2868.98315
30 3322.14285
40 4640
50 3996.5
60 6248.75
6 rows selected
-- 根據部門和工種進行分組，然後在組內進行平均工資的計算
SQL> select deptno, job, avg(sal) from emp group by deptno,job;
DEPTNO JOB AVG(SAL)
------ -------------------- ----------
20 考古學家 5250
30 航海士 2450
20 廚師 3000
20 航海士 3780
60 海賊王 7500
40 幹部 3600
40 副船長 5500
40 狙擊手 5500
60 海軍中將 5670
20 船長 5450
60 革命軍首領 6550
20 狙擊手 2750
20 船工 1750
40 船長 6000
50 提督 5675
50 狙擊手 3450
20 醫生 450.34
30 船員 3030
60 隊長 5500
30 船長 5655
20 音樂家 2050
20 舵手 2775
40 船員 2600
50 船長 3430.5
60 船長 5650
20 劍士 2725
10 漫畫家 5000
60 提督 7800
28 rows selected
-- 先分組計算每個部門的平均工資，然後篩選出大於4000的
SQL> select deptno, avg(sal) from emp group by deptno having avg(sal)>4000;
DEPTNO AVG(SAL)
------ ----------
40 4640
10 5000
60 6248.75
-- 有兩種方法
-- 1. 先分組求出每個部門的平均薪水，然後篩選出部門號爲20號的。
SQL> select deptno,avg(sal) from emp group by deptno having deptno=20;
DEPTNO AVG(SAL)
------ ----------
20 2868.98315
-- 2. 先篩選出部門號爲20號的，然後使用分組求平均薪資
SQL> select deptno,avg(sal) from emp where deptno=20 group by deptno;
DEPTNO AVG(SAL)
------ ----------
20 2868.98315
-- 有關效率：方法2比方法1高
-- 原因：方法1計算了每個部門平均薪資後篩選出20號部門，方法2先篩選出20號部門後只計算20號部門內的數據，計算量來看方法2較少。
-- 結論：方法2更優
sqlplus / as sysdbaSQL> start upSQL> shutdown immediatequitlsnrctl startlsnrctl stopalter user 使用者名稱 account lock;alter user 使用者名稱 account unlock;alter user 使用者名稱 identified by 新密碼;select userenv('language') from dual;conn 使用者/密碼 as 角色在sql語句中什麼時候使用單引號''什麼時候使用雙引號""？ 字串型別用單引號，別名用雙引號。