之前學過用SELECT字串加上 || column 來組合查詢結果
SELECT 'Hi ! ' || ename AS HI
FROM emp
也可以用CONCAT來達成同樣的效果
SELECT CONCAT('Hi ! ', ename) AS HI
FROM emp
| HI |
|---|
| Hi !KING |
| Hi !BLAKE |
| Hi !CLARK |
| Hi !JONES |
| Hi !SCOTT |
| Hi !FORD |
| Hi !SMITH |
| Hi !ALLEN |
| Hi !WARD |
| Hi !MARTIN |
假如我們輸入以下query
SELECT UPPER('hello')
FROM emp
WHERE deptno = 20
這樣就只會有符合條件的資料會變成大寫HELLO
| UPPER('HELLO') |
|---|
| HELLO |
| HELLO |
| HELLO |
| HELLO |
| HELLO |
DUAL表
簡單來說，dual表就是oracle與數據字典自動創建的一張表，這張表是一個單行單列的表，這個表只有1列：DUMMY，數據類型為VERCHAR2(1)，dual表中只有一個數據'X', Oracle有內部邏輯保證dual表中永遠只有一條數據。dual表主要是用來選擇系統變量或是求一個表達式的值。
比如：
--求系統當前時間
SELECT sysdate FROM daul
--求系統當前時間，並按設定的格式顯示
select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') from dual;
--當計算器使用
select 1+2 from dual
除了upper把字都轉大寫以外當然也要有把字轉小寫的功能
這就要透過lower來完成
query:
select LOWER('hello')
from DUAL
| LOWER('HELLO') |
|---|
| hello |
練習query
SELECT
CONCAT(CONCAT(LOWER(ename), UPPER(' is the name')), CONCAT(Lower(' and their job is : '), UPPER(job))) AS sentence
FROM
emp
WHERE
deptno = 20
| SENTENCE |
|---|
| jones IS THE NAME and their job is : MANAGER |
| scott IS THE NAME and their job is : ANALYST |
| ford IS THE NAME and their job is : ANALYST |
| smith IS THE NAME and their job is : CLERK |
| adams IS THE NAME and their job is : CLERK |
0 Comments:
張貼留言