《oracle查詢語句2》

標籤：esc   ret   desc   mil   計算   decode   sum   替換   group   

單行函數

SQL> select upper(first_name), lower(last_name), length(last_name) from employees;

SQL> select (sysdate-hire_date)/7 from employees;

SQL> select trunc((sysdate-hire_date)/30, 0) from employees;

SQL> select trunc(months_between(sysdate,hire_date), 0) from employees;//解決累積誤差

SQL> select sysdate+3650 from dual;

SQL> select add_months(sysdate, 120) from dual;//解決累積誤差

SQL> select next_day(‘2015-09-01‘, ‘friday‘) from dual; //下一個周五

SQL> select next_day(‘2015-10-01‘, 6) from dual;

SQL> select last_day(sysdate) from dual;

 

SQL> select round(to_date(‘2015-10-10‘,‘yyyy-mm-dd‘), ‘MONTH‘) from dual;

SQL> select round(to_date(‘2015-10-16‘,‘yyyy-mm-dd‘), ‘MONTH‘) from dual;

SQL> select round(to_date(‘2015-10-10‘,‘yyyy-mm-dd‘), ‘YEAR‘) from dual;

SQL> select round(sysdate, ‘DAY‘) from dual;

 

練習：

找出各月最後三天內受雇的所有僱員

extract(month from hire_date+4) != extract(month from hire_date)

找出早於25年之前受雇的僱員

months_between(sysdate, hire_date)/300>=25

顯示正好為6個字元的僱員姓名

length(last_name)=6

顯示所有僱員的姓名的前三個字元

substr(last_name, 1, 3)

顯示所有僱員的姓名，用a替換所有‘A‘

replace(last_name, ‘A‘, ‘a‘)

類型轉換和其他函數

SQL> select to_char(salary, ‘$999,999.00‘) from employees;

SQL> select last_name, to_char(hire_date, ‘dd-Mon-RR‘) from employees;

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

SQL> select to_char(sysdate, ‘yyyy-mm-dd hh:mi:ss AM‘) from dual;

SQL> select last_name from employees where hire_date=to_date(‘2006-05-23‘, ‘yyyy-mm-dd‘);

SQL> select to_number(‘$123,456.78‘, ‘$999,999.00‘) from dual;

 

練習：

查詢2006年入職員工：

select last_name

from employees

where hire_date between to_date(‘2006-01-01‘, ‘yyyy-mm-dd‘)

and  to_date(‘2006-12-31‘, ‘yyyy-mm-dd‘);

 

select last_name

from employees

where to_char(hire_date, ‘yyyy‘)=‘2006‘;

 

select last_name

from employees

where extract(year from hire_date)=2006;

 

--不推薦

select last_name

from employees

where hire_date like ‘2006%‘;

 

查詢曆年9月份入職的員工：

select last_name

from employees

where to_char(hire_date, ‘mm‘)=‘09‘;

 

select last_name

from employees

where extract(month from hire_date)=9;

 

其他函數：

nvl：

nvl(val1, val2)

if val1 is not null

then

    return val1;

else

    return val2;

SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;

 

練習：

顯示所有員工部門編號，沒有部門的顯示“未分配部門”

 

case和decode：

IT_PROG +1000

SA_REP +1500

ST_CLERK +2000

其他人工資不變

 

select salary+1000 from employees where job_id=‘IT_PROG‘;

 

select last_name, job_id, salary,

case job_id

  when ‘IT_PROG‘ then salary+1000

  when ‘SA_REP‘ then salary+1500

  when ‘ST_CLERK‘ then salary+2000

  else salary

end new_salary

from employees;

 

select last_name, job_id, salary,

decode( job_id,

  ‘IT_PROG‘, salary+1000,

  ‘SA_REP‘,  salary+1500,

  ‘ST_CLERK‘, salary+2000,

  salary) new_salary

from employees;

練習：

按照員工工資，對員工分級顯示：

A 20001-25000

B 15001-20000

C 10001-15000

D 5001-10000

E 0-5000

    分組函數

只有  count(*):對行做統計，對空值保留做統計，其他都是將空值去掉後做統計

Avg：對非空值取平均值

Nvl：將空值賦予值為0，在做統計

 

Group by分組做統計時，不會將空值捨去

                  Select 中只會出現分組列，分組函數

SQL> select count(*), sum(salary), avg(salary), minsalary), max(salary) from employees;

 

SQL> create table t1(x int);

SQL> insert into t1 values (null);

SQL> insert into t1 values (1);

SQL> commit;

SQL> select count(*) from t1;

SQL> select count(x) from t1;

SQL> select max(x) from t1;

SQL> select min(x) from t1;

SQL> select sum(x) from t1;

SQL> select avg(x) from t1;

 

SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;     對空值賦值為0做統計

SQL> select count(distinct department_id) from employees; 去除重複值做統計

 

Group by分組：

SQL> select department_id, avg(salary) from employees group by department_id;

多列分組：

SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;

SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 錯誤文法

 

練習：

公司中不同職位的數量

SQL> select count(distinct job_id) from employee

計算每個部門的人數

SQL> select  department_id, count(employee_id) from employees group by department_id;

=SQL> select  department_id, count(last_name) from employees group by department_id;

按年份分組，求員工的工資總和

SQL> select extract(year from hire_date), sum(salary) from employees group by extract(year from hire_date);  //將年份抽取出來

 

 

Having語句：相當於group by之後的where

SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 錯誤語句

SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;

 

練習：

按部門求出所有有部門的普通員工的平均工資，部門平均工資少於5000的不顯示，最終結果按平均工資的降序排列。

select department_id, avg(salary) avg_sal

from employees

where job_id not like ‘%\_MGR‘ escape ‘\‘ and department_id is not null

group by department_id

having avg(salary)>=5000

order by avg_sal desc;

多表串連

emp： dept:

empno ename deptno deptno dname

100 abc 10 10 sales

101 def 10 20 market

102 xyz 20 30 it

103 opq null

 

for emp in 100 .. 103

  for dept in 10 .. 30

    emp.deptno=dept.deptno

 

100         abc         10              10          sales

101         def         10              10          sales

102         xyz         20              20          market

 

 

訂單表:

CustID  StoreID     ProdID  ChannelID

100 S100        P100    C100

 

客戶表：

CustID  name  creditlevel

100         abc   

 

地址表：

CustID  adress

100         bj

100         tj

 

擷取如下資訊，準備工作：

employees:

員工總數：107

SQL> select count(*) from employees;

有部門的員工數：106

SQL> select count(*) from employees where department_id is not null;

SQL> select count(department_id) from employees;

沒有部門的員工數：1

SQL> select count(*) from employees where department_id is null;

 

departments：

部門總數：27

SQL> select count(*) from departments;

有員工的部門數：11

SQL> select count(distinct department_id) from employees;

沒有員工的部門數：16

SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);

 

for dept in 1..27

  for emp in 1..107

   dept.deptid不在emp表中出現

where e.department_id(+)=d.department_id

 

select count(*)

from employees e, departments d

and e.employee_id is null;

 

select count(*)

from departments d

where not exists

(select 1 from employees where department_id=d.department_id);

 

select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;

 

內串連：106(106, 11)

select e.last_name, d.department_name

from employees e, departments d               //對錶進行重新命名

where e.department_id=d.department_id;  //

 

select e.last_name, d.department_name

from employees e join departments d on e.department_id=d.department_id;

 

左外串連：107（106+1） //將e表的不符合的也添加進來，就在d的後面+“(+)”

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from departments d, employees e

where e.department_id=d.department_id(+);

Sql99標準寫法

select e.last_name, d.department_name

from employees e left outer join departments d

on e.department_id=d.department_id;

 

右外串連：122（106+16） 

select e.last_name, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外串連：123（106+1+16）

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id;

 

多表串連的擴充：

n張表串連：

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e join departments d on e.department_id=d.department_id

               join locations l on d.location_id=l.location_id;

 

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+);

 

select e.last_name, d.department_name, l.city

from employees e left outer join departments d on e.department_id=d.department_id

               left outer join locations l on d.location_id=l.location_id;

《oracle查詢語句2》