資料庫基礎查詢--單表查詢

1、基礎查詢
1)IS NULL
//查詢哪些員工沒有獎金
select * from emp_xu where bonus=null;//未選定行


select * from emp_xu where bonus is null;
注意：測試NULL值時要使用IS NULL(IS NOT NULL),
NULL不能用等於(=)或者不等於(<>)跟任何值進行比較。


2)條件查詢中的否定形式
//查詢哪些員工有獎金
select * from emp_xu where bonus is not null;


//查詢薪水不在5000到10000的員工資訊
select ename,salary
from emp_xu
where salary<5000 or salary>10000;


select ename,salary 
from emp_xu
where salary not between 5000 and 10000;


//查詢不是20部門和30部門的員工
select ename,deptno from emp_xu
where deptno<>20 and deptno<>30;


等同於：
select ename,deptno from emp_xu
where deptno not in(20,30);//not in表示判斷不在
列表項中，要全部滿足即可。


修改：
select ename,deptno from emp_xu
where deptno not in(20,30,null);//未選定行
當使用not in時，列表項中如果有null情況，查詢
不到任何滿足要求的資料




select ename,deptno from emp_xu
where deptno<>20 and deptno<>30
or deptno is null;


select ename,deptno from emp_xu
where nvl(deptno,0)<>20 and nvl(deptno,0)<>30


2、查詢語句中使用的函式
1)單行函式
每一行資料都會返回一個結果
//數字函式
a.round
round(數字,小數點後的位數)用於對數字進行四捨五入
//計算員工工資使用四捨五入函式
select salary*0.123456 s1,
round(salary*0.123456,2) s2,
round(salary*0.123456) s3
from emp_xu;//s1表示原樣輸出，s2表示四捨五入保留
2位小數，s3表示四捨五入保留到整數位


//round第二個引數是負數
select round(163.456,-2) from dual;
注意：如果round第二個引數是負數，則表示保留
到對應的整數位


b.trunc
trunc(數字,小數點後的位數)：用於擷取資料的，如果
沒有第二個引數，預設是0
//計算員工的工資用擷取函式
select salary*0.123456 s1,
trunc(salary*0.123456,2) s2,
trunc(salary*0.123456) s3
from emp_xu;//s1原樣輸出，s2擷取到小數後面2位，
s3擷取到整數位


//日期函式
a.sysdate
sysdate獲取當前系統時間
select sysdate from dual;


b.months_between
months_between(d1,d2)計算兩個日期之間相隔的月份
//計算員工入職多少個月
select trunc(months_between(sysdate,hiredate)) months 
from emp_xu;


c.add_months
add_months(date,number):在date時間上增加number
個月份
//計算3個月之後的時間
select add_months(sysdate,3) from dual;
//計算3個月之前的時間
select add_months(sysdate,-3) from dual;


d.last_day
last_day(date):計算當前月份的最後一天
select last_day(sysdate) from dual;


//轉換函式
a.to_char
to_char(日期資料,日期格式)：把日期資料按照指定
格式轉換成字元資料
select to_char(sysdate,'yyyy-mm-dd') from dual;


select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;


常用日期格式：
yyyy 四位的年 如：2018
year 全拼的年 如：twenty eighteen
mm  兩月的月份 如：05
month 全拼的月  如：May或5月(中文)
mon  簡拼的月  如：May或5月
dd  兩位的天 如：18
day 全拼的星期 如：Friday或星期五
dy  簡拼的星期 如：Fri
am/pm 上午/下午
select to_char(sysdate,'year') from dual;


b.to_date
to_date(字元資料,日期格式)：把字元資料按照指定
格式轉換成日期資料
select to_date('2018-05-18','DD-MON-YY') 
from dual;//文字與格式字串不匹配


select to_date('2018-05-18','yyyy-mm-dd') 
from dual;


//其他函式
a.coalesce
coalesce(引數列表)：返回引數列表中第一個非空引數
值，引數列表中最後一個值通常為常量。
//計算員工的年終獎，要求bonus不為null,年終獎就
是bonus值；如果bonus為null,年終獎為salary*0.05;
如果bonus和salary都是null,年終獎為1000
select ename,bonus,salary,
coalesce(bonus,salary*0.05,1000) year_money
from emp_xu;


b.case..when
case..when表示資料中分支語句
//根據員工的職位計算加薪之後的薪水值。要求：
如果職位是Analyst加薪20%；如果職位是Programmer
加薪5%；如果職位是Clerk加薪2%；其他職位薪水不變
select ename,position,salary,
case position 
when 'Analyst' then salary*1.2
when 'Programmer' then salary*1.05
when 'Clerk' then salary*1.02
else salary end new_salary
from emp_xu;//case..when中間沒有逗號，else相當於
java中case語句中的default,end是case語句中結束
標識


c.decode
decode作用類似於case..when
decode(判斷條件,匹配1,值1,匹配2,值2...預設值)


select ename,position,salary,
decode(position,'Analyst',salary*1.2,
'Programmer',salary*1.05,
'Clerk',salary*1.02,salary) new_salary
from emp_xu;


2)組函式
多行資料返回一個結果
count():求記錄數，能夠處理任何資料型別，
忽略空值
//查詢員工表中有多少記錄
select count(empno) from emp_xu;//13


select count(position) from emp_xu;//12 忽略空值


select count(*) from emp_xu;// *不忽略空值的


sum():求和
avg():求平均
注意：只能處理數值型別


min():最小值
max():最大值
注意：能處理任何資料型別


//計算員工的薪水總和
select sum(salary) from emp_xu;


//計算員工的人數總和、薪水總和及平均薪水
select count(*),sum(salary),avg(salary)
from emp_xu;//avg忽略空值，資料不正確


select count(*),sum(salary),avg(nvl(salary,0))
from emp_xu;


//查詢員工的最低、最高薪水
select min(salary),max(salary) from emp_xu;


總結：
count/sum/avg/min/max 如果函式中寫列名預設是
忽略空值的，count(*)不忽略空值。sum/avg針對
數值型別的操作，min/max針對全部資料型別的操作。


3、排序
order by語句進行排序
將查詢結果進行排序。(先有結果集再進行排序)
asc:表示升序，預設是升序
desc:表示降序


//查詢員工的薪水按由低到高進行排序
select ename,salary 
from emp_xu
order by salary asc;//asc可以不寫預設是升序




select ename,salary 
from emp_xu
order by salary desc;//降序
注意：排序時空值null被看做是最大值


//按照入職時間進行排序，入職時間越早的在前面
select ename,hiredate 
from emp_xu
order by hiredate;//升序


//按照部門升序排序，同一個部門按照薪水降序排序
select deptno,salary
from emp_xu
order by deptno asc,salary desc;//排序使用列名


select deptno d,salary s
from emp_xu
order by d asc,s desc;//排序使用列別名


select deptno,salary 
from emp_xu
order by 1 asc,2 desc;//排序使用數字表示的是結果
集的列數


總結：
排序語句(order by)的執行在SELECT之後,因此排序
可以使用結果集的列名、列別名、表示式、函式還有
數字。(數字表示按照結果集第幾列排序，第一列是
用1)


4、分組
分組使用的是group by語句
group by column_name:按照指定的列進行分組


//查詢每個部門的最高薪水和最低薪水
select deptno,max(salary),min(salary)
from emp_xu
where deptno is not null
group by deptno;


//查詢每個部門的薪水總和和平均薪水，沒有部門號
 的不算在內
select deptno,sum(salary),avg(nvl(salary,0))
from emp_xu
where deptno is not null
group by deptno;


//按照職位分組，每個職位的最高、最低薪水和人數
select position,max(salary),min(salary),
count(*)
from emp_xu
where position is not null
group by position;


select nvl(position,'No Position'),max(salary),min(salary),
count(*)
from emp_xu
group by nvl(position,'No Position');


注意：SELECT之後出現的列凡是沒有被組函式包圍的，
必須出現在group by語句中


having語句
having用於對分組後的資料進行過濾


注意：where語句是對錶中的記錄進行過濾，having
是對分組得到的資料進一步過濾


//查詢平均薪水大於5000的部門號、平均薪水，要求
沒有部門的不算在內
select deptno,avg(nvl(salary,0))
from emp_xu
where deptno is not null
group by deptno
having avg(nvl(salary,0))>5000;


select deptno,avg(nvl(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno
having avg_salary>5000;//avg_salary識別符號無效，
不能直接使用列別名，having在select之前執行的


//查詢薪水總和大於20000的部門號和薪水總和，沒有
部門的不算在內
select deptno,sum(salary)
from emp_xu
where deptno is not null
group by deptno
having sum(salary)>20000;


//查詢哪些職位的人數超過2個人，沒有職位的不算在
內
select position,count(*)
from emp_xu
where position is not null
group by position
having count(*)>2;


//查詢哪些職位的人數超過2個人，沒有職位的不算
在內，求出每個職位的平均薪水，按照平均薪水進行
排序
select position,count(*),avg(nvl(salary,0))
from emp_xu
where position is not null
group by position
having count(*)>2
order by avg(nvl(salary,0));


select position,count(*),avg(nvl(salary,0)) avg_salary
from emp_xu
where position is not null
group by position
having count(*)>2
order by avg_salary;


select position,count(*),avg(nvl(salary,0)) 
from emp_xu
where position is not null
group by position
having count(*)>2
order by 3;


總結：基礎查詢語句
寫法：select->from->where->group by->having->
 order by
 
執行：from->where->group by->having->select->
order by  


where語句和having語句的區別：
a.where過濾的是行記錄，having過濾的是組
b.where可以跟任何列名、單行函式，不能用組函式
c.having只能包含group by之後的表示式或組函式
d.where執行在前的，having執行在後的
e.where和having都不能跟列別名