SQL查詢語句 （DQL）

SELECT [DISTINCT] {*,column alias,…}

FROM table alias

WHERE 條件表示式

ORDER BY 排序欄位列表 [asc|desc]

查詢所有列

--註釋
--查詢 select *(萬用字元)|欄位名,欄位名... from 資料來源;
--執行流程: from --> select

--1)  所有列

--查詢|檢索|獲取 所有員工的所有資訊 
--查詢的資料: 所有的員工資訊
--資料的來源: emp
--條件:
select * from emp; --所有資料的所有欄位值
select * from dept;
select deptno,dname,loc from dept;  --推薦

查詢所部分列

--2)  部分列
--查詢所有的員工名字
select ename from emp;

--3)
--查詢所有的員工編號和員工名稱,員工上級的編號
select empno, ename, mgr from emp;

--4)
--查詢所有員工所在的部門編號
select deptno from emp;

--查詢公司所有的部門編號
select deptno from dept;

去重

--5)
--查詢出所有有員工存在的部門編號,每個部門編號只出現一次 

--去重distinct
--對結果集中多條所有欄位都重複(完全重複)的資料做去重
select distinct deptno from emp;

--6)
--查詢出所有存在員工的部門的部門編號,以及員工名稱
select distinct deptno,empno from emp;

取別名 ：用雙引號，原封不動顯示。 不用雙引號，英文小寫變大寫，而且別名裡面不能有空格。

--7)
--查詢表示式  計算器
select 1+1 from dept;
select 123*456 from dept;
select empno,ename,sal,deptno,123*456 from emp;

--8)
--取別名  結果集中的欄位   表別名  
--""->原封不動顯示
--select 欄位名 (as) 別名,欄位名 別名,... from 資料來源 別名;
select empno,ename,sal,deptno,123*456 from emp;
select empno "no",ename 員工名字,sal as 薪資,deptno "部門      編號",123*456 星球編號 from emp e;

字串 ：使用單引號

--9)
--字串 
--''
select distinct 'abc' from emp;

select ename from emp;

--10)
--字串拼接  ||
select 'sxt-'||ename 員工姓名 from emp;

偽類：不存在的列，構建虛擬的列

--11)
--偽列 : 表中不存在欄位,但是可以穿: 表示式  數值  字串
select 1 from emp;

虛表 ： 用於計算表示式，顯示單條記錄的值

--12)
--虛表 dual
select distinct 123*456 from emp;
select 123*456 from dual;
select sysdate from dual;

null值運算

--null值和數字 參與 運算結果還是null
--null值和字串運算,結果為原串 
--處理null  -> nvl(欄位,值2)  當欄位值不為null,nvl函式最終的結果就為欄位值,如果欄位值null,函式最終的結果為值2

--13)
--給每一個員工在原來的基礎上+1塊錢獎金
select empno,ename,sal,comm 原獎金,nvl(comm,0)+1 現獎金 from emp;
select empno,ename,sal,comm 原獎金,comm||'1' 現獎金 from emp;
select nvl(comm,0) from emp;


--查詢所有員工的名字, 工種, 年薪(不帶獎金)
select ename 員工名字,job 工種,sal*12 "年薪(不帶獎金)" from emp;

--查詢所有員工的名字,工種,年薪(帶12月獎金的)
select ename 員工名字,job 工種,sal*12+nvl(comm,0)*12 "年薪(帶12個月獎金)" from emp;

--查詢所有員工的名字, 工種, 年薪(帶一次獎金的)
select ename 員工名字,job 工種,sal*12+nvl(comm,0)*1 "年薪(帶一個月獎金)" from emp;

條件查詢

​ --條件查詢
–select 資料 from 資料來源 where 行過濾條件;
–執行流程: from–>where–>select

​ where 過濾行記錄條件 ,條件有

​ a)、= 、 >、 < 、 >=、 <=、 !=、 <> 、 between and (betweend and 取的左右都是閉區間)

--   = 、 >、 < 、 >=、 <=、

--查詢員工名稱為SMITH的員工資訊
select * from emp where ename = 'SMITH';

--查詢30部門的員工資訊
select * from emp where deptno = 30;

--查詢薪資>1500的員工資訊
select * from emp where sal>= 1500;





--   !=、 <>

--查詢除了30部門以外的員工資訊
select * from emp where deptno != 30;

select * from emp where deptno <> 30;

select * from emp where not deptno = 30;





--between and 
select * from emp where sal between 1500 and 3000;

--查詢EMP表顯示在   1981年2月1日到1981年5月1日   之間僱傭的 僱員名、崗位及僱傭日期， 並以僱傭日期進行排序。
select ename,job,hiredate from emp where hiredate between  
to_date('1981-02-01','yyyy-mm-dd') and
to_date('1981-05-01','yyyy-mm-dd') order by hiredate;

-- 使用函式 to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')得到日期

​

​ b)、and 、or、 not、 union、 union all、 intersect 、minus

-- and 、or、 not、
--查詢薪資>1500 同時<3000的員工資訊

select * from emp where sal>= 1500 and sal<=3000;

--查詢10或者20部門的員工資訊

select * from emp where deptno = 10 or deptno = 20;




--   union、 union all、 intersect 、minus 
--集合函式: 對結果集的操作
--union 並集(去重)、  union all並集不去重、  intersect交集 、minus 差集
select * from emp where sal>= 1500
intersect
select * from emp where  sal<=3000;

--查詢沒有員工存在的部門編號
select deptno from dept
minus
select distinct deptno from emp;

​ c)、 is null 、 is not null

-- null   只能使用  is null   或者    is not null
--獎金 為null的員工資訊
select * from emp where comm is null;


--獎金 不為null的員工資訊
select * from emp where not comm is null;
select * from emp where  comm is not null;

​ d)、like (表示模糊查詢) % (表示匹配任意個字元) _ (表示匹配一個字元) escape(‘單個字元’) (表示單個字元為轉義字元)

--模糊匹配 like

--%:任意個任意字元    _: 一個任意字元
select * from emp where ename like 'SMITH';
--名字中包含A被查詢
select * from emp where ename like '%A%';
--名字中第二個字元為A
select * from emp where ename like '_A%';

​ f)、in 、 exists(難點) 及 子查詢

-- in相當於使用or的多個等值

-- 查詢emp中800或900工資的所有員工資訊
select * from emp where sal in(900,800);

--10或30部門的僱員資訊
select * from emp where deptno in(10,30);

--部門名稱為 SALES 或 ACCOUNTING 的僱員資訊 
select deptno from dept where dname in('SALES','ACCOUNTING');

--子查詢(查詢中再有查詢) in 只能存在一個欄位
select * from emp where sal in (select sal
from emp e where deptno=10);

--在emp中，'SALES', 'ACCOUNTING'對應部門號的所有的員工資訊
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN ('SALES', 'ACCOUNTING'));


--exists 存在  存在即合法,存在就保留
--exists (查詢語句) 從from後資料來源中拿出一條資料,判斷exists(結果集)如果存在資料,當前這條資料就滿足條件

-- exists   全部取到  ---1 
-- 因為內層查詢存在資料 true，那麼外層每一條資料進去比對 都能 返回 exists的 存在 true
select * from emp 
where exists (select deptno,dname from dept 
             where dname in ('SALES', 'ACCOUNTING'));


-- exists     e.deptno = d.deptno   只有 10 和 30 的deptno取到  ---2
--因為內層 第一個查詢存在資料 兩列 部門號d.deptno (此處得到10和30) 和部門名，然後第二個查詢是要求 外層每一行資料的部門號e.deptno 進去比對d.deptno，都是看看e.deptno是否等於10或者30，如果是能和其中一個相等，那麼這行就算是exists存在的，返回得到true。所以最後返回的是emp中部門號為10的所有資料還有部門號為30所有資料，一共9行。
select * from emp e
 where exists (select deptno, dname from dept d 
              where dname in ('SALES', 'ACCOUNTING') and e.deptno = d.deptno);
              

-- not exists  e.deptno = d.deptno  ---2 的取反
-- 這個與上面的取反
select * from emp e 
where not exists (select deptno, dname from dept d 
                 where dname in ('SALES', 'ACCOUNTING')and e.deptno = d.deptno);              
              
--exists     全部取到   --- 3 
--因為內層 第一個查詢存在資料 兩列 部門號d.deptno (此處得到10和30) 和部門名，然後第二個查詢是要求 外層每一行資料的部門號e.deptno 進去比對d.deptno，都是看看e.deptno是否不等於10或者30，如果是能和其中一個不相等，那就是true。如 此時的e.deptno是10,和10比較是相等，不符合，和30比較，達到了不相等的條件，那麼就返回true。
select * from emp e
where exists (select deptno, dname from dept d 
             where dname in ('SALES', 'ACCOUNTING')and e.deptno != d.deptno);

--不是exists  用in取  
--只返回5行資料
select ename, sal from emp
where sal in (select sal from emp e2 
             where e2.sal >= 2000); 
             
--exists     全部取到   --- 1          
--返回12行資料，內層永遠是true，所以外層都能表示exists的true，那麼全部都符合(和上面第一個exists情況相同)
select ename, sal from emp 
where exists (select ename, sal from emp e2 
             where e2.sal >= 2000);
         
         
-- exists     ---2          
--內層的第一個查詢empno, ename, sal, comm，可以返回四行資料，由於第二個查詢e1.empno = e2.empno,那麼外層每個 e1.empno 和 第一個查詢的4個e2.empno進行相等，所以最後整個得到4行資料。 (和上面第二個exists情況相同)
select empno, ename, sal from emp e1 
       where exists (select empno, ename, sal, comm from emp e2
                    where comm is not null and e1.empno = e2.empno); 
                    
-- exists     ---2  隱含                    
--內層的第一個查詢empno, ename, sal, comm，可返回四行資料，由於第二個查詢e1.deptno = e2.deptno,可以看到內層第一個查詢沒有deptno，但是實際上四行資料對應deptno只有30，所以也可以理解為第一個查詢隱含了deptno為30，那麼外層每個 e1.deptno 和 第一個查詢的裡面隱含的所有e2.deptno進行相等，最後得到了整個deptno = 30的6行資料。和 select empno, ename, sal from emp where deptno = 30 的結果一樣。
select empno, ename, sal from emp e1 
       where exists (select empno, ename, sal, comm from emp e2 
                    where comm is not null and e1.deptno = e2.deptno);

排序 order by

​ 使用 ORDER BY 排序，排序不是真實改變儲存結構的順序，而是獲取的集合的順序。

​ 從上到下升序 :asc（預設） 從上到下降序 : desc

​ 多欄位: 在前面欄位相等時，使用後面的欄位排序

--select 資料 from 資料來源 where 行過濾條件 order by 排序欄位1 desc|asc,排序欄位2...  預設升序

--執行流程: from --> where  -->select -->排序
--根據薪資降序排序,查詢10,30部門
select * from emp where deptno in(10,30) order by sal,comm desc;

​

注意: null 預設為最大，在升序時在最下面，在降序時在最上面

​ 但可以強制修改null位置: nulls first|last

--nulls first|last
select * from emp order by comm  nulls first ;   -- 本來null在最下面，這樣強制把null放最上面

獲取所有行的記錄

select * from emp; 

--這裡使用 1=1是為了寫java程式碼方便。
--如果沒有使用1=1，那麼在java拼接的時候，需要先判斷是否是第一個行過濾語句(即在where右邊最近的行過濾語句)，如果是的話那麼直接拼接  + "行過濾語句"  ，如果不是的話，那麼就要拼接 + "and 行過濾語句"  。
--如果使用了1=1 ，那麼不需要判斷是不是第一個行過濾語句，直接使用  + "and 行過濾語句"  拼接。
select * from emp where 1=1 ;  

--這個 ename like '%' 也是和上面 1=1 一樣的效果，不過like查詢效率更低。
select * from emp where ename like '%'; 

函式

單行函式

​ 日期函式:

​ add_month(d,x)

​ lastdate(hiredate)

​ months_between(date1,date2)

​ next_day(sysdate, ‘星期六’)

​ to_char(d,m)

​ to_date(c,m)

--當前日期
select sysdate from dual;
select current_date from dual;

--日期可以直接做加減 (加減的是天數)
select current_date+1 from dual;

--日期加減月份 (這裡加的是90天，但是 3個月 有 28 30 31等等 不一定是90天)
select hiredate 入職日期,hiredate+90 轉正日期 from emp;

-- add_month(d,x)  d是日期，x是幾個月，給日期新增月份
select hiredate 入職日期,add_months(hiredate,3) 轉正日期 from emp;

--lastdate(hiredate) 本月份的最後一天  每個員工入職 
select last_day(hiredate), last_day(sysdate) from emp;

--months_between(date1,date2) 返回date1和date2之間月的數目  
-- date1 放後面的日期 ，date2放前面的日期

--下一個 星期x  距下一個星期x最近的的日期
select next_day(sysdate, '星期六') from dual;
--結果：2020/11/7 20:37:56


-- 日期和字串的互相轉化
--to_char(d,m)     -> 日期以指定格式轉換為字串
select to_char(hiredate,'yyyy"年"mm"月"dd ') from emp;
select to_char(sysdate,'yyyy"年"mm"月"dd  hh24:mi:ss') from emp;
select to_char(sysdate, 'yyyy') from emp;

--to_date(c,m)     -> 字串以指定格式轉換為日期  可以對日期進行加減
select to_date('2021年02月22日','yyyy"年"mm"月"dd"日"')+1 from dual;

​ 判定函式:

​ nvl(string1,string2)

​ decode(判定欄位,值1,結果1,值2,結果2…,預設值)

​ case when then else end

--判定函式 

--nvl(string1,string2) 如果string1為 null,則結果為string2的值 
select ename, nvl(null,0) from emp; select ename, nvl(to_char(comm),'hello') from emp;

--decode(判定欄位,值1,結果1,值2,結果2...,預設值)
--查詢部門資訊,新增一個欄位顯示部門編號 的中文形式 10,十   20,二十...
select deptno,
       dname,
       loc,
       decode(deptno, 10, '十', 20, '二十', 30, '三十', '四十') 部門中文名稱
  from dept;


--case when then  else end
--部門號10漲1.1   20漲1.08   30漲1.15   其他部門1.2
select ename,
       sal,
       deptno,
       (case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.08
         when 30 then
          sal * 1.15
         else
          sal * 1.2
       end) raisesal
  from emp;

多行函式

​ 多行函式 只能和其他 多行函式 ,或者 分組欄位 一起使用

count()

sum()

max()

min()

avg()

--多行函式---------------------------------------------

--組函式|多行函式|聚合函式
--count(1|*|欄位名 )  sum()  max()  min()  avg()
--select 後面可以多個組函式同時使用
--先確定要計算的結果集|組|多條資料,然後再進行組函式計算
--注意:
--在select後面,組函式只能和 其他組函式 ,或者 分組欄位 一起使用


-- count()

-- 統計一下一共有多少個員工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;


-- count(去重 distinct)
-- 統計有員工存在的部門總數
select count(distinct deptno) from emp;


-- count() where 判定條件
-- 統計20部門一共有多少人
select count(*) from emp where deptno = 20;
-- 統計有獎金的員工有幾個
select count(1) from emp where comm is not null;


--count() 偽列
select count(1) from emp;
-- count() 不計算null
select count(comm) from emp;


-- sum()

-- 計算本公司每個月一共要在工資上花費多少錢
select sum(sal) from emp;


-- 計算20部門每個月的工資花銷
select sum(sal) from emp where deptno = 20;

-- 計算出所有員工的獎金總和
select sum(comm) from emp;


-- max()

-- 查詢本公司的最高工資和最低工資
select max(sal),min(sal),sum(sal) from emp;


--檢視30部門的最高工資和最低工資
select max(sal),min(sal),sum(sal) from emp where deptno = 30;


-- avg()

--請查詢出 20部門的平均工資
select avg(sal) from emp where deptno  = 20;

--分組 group by 分組欄位
--請查詢出 20部門的平均工資， 部門編號
select avg(sal),deptno from emp where deptno = 20 group by deptno;

子查詢

​ 子查詢 對於內層的查詢可以使用 in 而不使用 =

--子查詢-------

--查詢 最高薪水的員工姓名,及薪水
select max(sal) from emp; 
--子查詢 薪資與最高薪資相等的員工姓名
select ename from emp where sal == (select max(sal) from emp);
--子查詢 對於內層的查詢可以使用 in 。因為如果內層查詢有兩行或者以上的資料，用 = 去取值，會出錯，用in不管裡面是一行或者多於一行都可以使用。
select ename from emp where sal in (select max(sal) from emp);

--查詢 平均薪資
select avg(sal) from emp;
--子查詢 查詢工資低於總平均工資的員工編號，姓名及工資
select empno,ename,sal from emp where sal < (select avg(sal) from emp);

分組

--分組----------------------------------------------------

--分組 把資料根據不同的邏輯分城不同的小組,以組委單位的計算
--如果一旦分組,select後面只能使用 分組欄位 或者 組函式
--select 資料 from 資料來源 where 行過濾條件 group by 分組欄位 having 組過濾資訊 order by 排序欄位;
--執行流程: from   where  group by   having   select   order by
--注意: where後面不能使用組函式,having後可以
--在where和having的後面都不能使用別名

select deptno,max(sal) from emp group by deptno;


-- 找出20部門和30部門的最高工資 
--先過濾後分組
select max(sal),deptno from emp where deptno in (20,30) group by deptno;
--先分組後過濾
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每個部門的平均工資
select avg(sal),deptno from emp group by deptno;

-- 求出每個部門員工工資高於1000的的平均工資
select avg(sal),deptno from emp where sal>1000 group by deptno order by avg(sal) desc;


-- 求出10和20部門的哪些工資高於1000的員工的平均工資
select avg(sal),deptno from emp where sal>1000 and deptno in(20,10) group by deptno order by avg(sal) desc;
select avg(sal),deptno from emp where sal>1000 group by deptno having deptno in(20,10) order by avg(sal) desc;



--所有部門的部門編號和平均工資
select avg(sal),deptno from emp group by deptno; 

--求出平均工資高於2000的部門編號和平均工資
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
select * from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal>2000;

--按 部門崗位(job) 查詢 平均工資
select avg(sal),job from emp group by job;

--按 崗位查詢 平均工資，且平均工資大於2000的崗位
select avg(sal) avg_sal,job from emp group by job having avg(sal)>2000;


--查詢 最低平均工資的部門編號
--1)每個部門的平均工資和部門編號
select avg(sal) from emp group by deptno ;
--2)找到最低平均薪資
select min(avg(sal)) from emp group by deptno ;
--3)部門平均薪資與最低平均薪資相等的部門編號
select avg(sal),deptno from emp group by deptno having avg(sal) = (select min(avg(sal)) from emp  group by deptno);

兩張表

行轉列

--1、行轉列 decode
select name,
       decode(course, '語文', score) 語文,
       decode(course, '數學', score) 數學,
       decode(course, '英語', score) 英語
  from tb_student;
--2、分組 
select name,
       min(decode(course, '語文', score)) 語文,
       min(decode(course, '數學', score)) 數學,
       min(decode(course, '英語', score)) 英語
  from tb_student