組函數和表關係

第三章組函數和表關係

字元串函數

-- concat   連接兩個字元串

select concat('abc','ABC') from dual;

select 'abc'||'ABC' from dual;

select concat(first_name,'_')||last_name con,t.* from EMPLOYEES t;

-- initcap  返回字元串，第一個大寫，其餘小寫

select initcap(email) from EMPLOYEES;

-- length   返回字元串長度

select length(email),t.* from EMPLOYEES t;

-- lower    所有字元小寫

-- upper    所有字元大寫

select lower(first_name),upper(last_name),t.* from EMPLOYEES t;

-- substr 字元串截取

select substr('123456789',3,4) from dual;

從第 3個 截取，截取 4 個字元

-- replace 字元串替換

select replace('he love you', 'he', 'i') from dual;

I love you

數學函數

-- CEIL 返回大於或等於給出數字的最大整數

select ceil('123.456789') from dual;

select ceil('123') from dual;

-- FLOOR 返回小於或等於給出數字的最小整數

select floor('123.456789') from dual;

-- Round 函數進行四捨五入

select round(124.1666,-2),round(124.1666,2) from dual;

-2,小數點前兩位，四捨五入

2，小數點前兩位，四捨五入

-- trunc 函數進行截取，直接截取，不進行四捨五入

select trunc(124.1666,-2) , trunc(124.16666,2) from dual;

日期函數

select sysdate from dual;

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

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

select to_date('2019-02-27 18:30:31','yyyy-mm-dd hh24:mi:ss') from dual;

select systimestamp from dual;

select to_timestamp('2019-02-27 18:30:31.123','yyyy-mm-dd hh24:mi:ss.ff') from dual;

-- add_Months 增加或減去月份

select add_months(sysdate，2) from dual;

select add_months(sysdate，-2) from dual;

-- months_between 顯示日期相差的月數

select months_between(

       to_date('2019-02-01','yyyy-mm-dd')，

       to_date('2019-10-01','yyyy-mm-dd')

) from dual;

聚合函數，組函數

-- max 最大值

select department_id,max(salary) from EMPLOYEES  group by department_id;

-- min 最小值

select manager_id,min(salary) from EMPLOYEES group by manager_id;

-- avg 平均

SELECT department_id,AVG(salary) AVGSAL FROM EMPLOYEES GROUP BY department_id;

-- sum 總和

SELECT department_id,SUM(salary) SUMSAL FROM EMPLOYEES GROUP BY department_id;

-- count 統計

select count(1) from EMPLOYEES;

表關係 設計階段

多表連接 SQL實現階段

示例：