第三章組函式和表關系
字串函式
-- 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實作階段
示例：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/14939.html
標籤：Oracle
上一篇：Oracle體系架構和匯入匯出
下一篇：Oracle索引和事務