SQL語法-基本指令

列出表格中所有欄位的資料
SELECT * FROM 表格
例：SELECT * FROM departments;

從表格篩選列出某欄位的資料
SELECT 欄位 FROM 表格
例：SELECT employee_id , last_name FROM employees;
      SELECT last_name, salary, 12*salary+100 FROM employees;

 

替要篩選的欄位取匿名
SELECT 欄位 "別名" FROM 表格
SELECT 欄位 AS "別名" FROM 表格
例：SELECT employee_id "Emp #", last_name "Employee" FROM employees;
      SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;

 

欄位連結
SELECT 欄位1||欄位2 "別名" FROM 表格
例：SELECT last_name||job_id AS "Employees" FROM employees;
   
只列出表格/欄位內不同值的，也就是同值的只列一次
SELECT DISTINCT "欄位名" FROM "表格名"
例：SELECT DISTINCT department_id, job_id FROM employees;

 

查看表格結構
DESC[RIBE] 表格

例：DESCRIBE employees;

選擇性地抓資料
SELECT 欄位 FROM 表格 WHERE 條件
例：

SELECT last_name,salary FROM employees WHERE  salary > 12000; 

SELECT last_name,department_id FROM employees WHERE department_id = 90 ;

 

搜尋字串

SELECT last_name,job_id FROM employees WHERE  last_name = 'Whalen' ;

 

區間搜尋

SELECT last_name,salary FROM employees WHERE  salary BETWEEN 2500 AND 3500 ;

 

集合搜尋

SELECT last_name,salary,manager_id FROM employees WHERE manager_id IN (100, 101, 201)

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'SA_REP')

 

相似搜尋

SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;

 

%	代表0個或一個 以上字元
_	一個任意字元

 

ps.若要搜尋％或_字元可用'\'字元跳脫

SELECT employee_id,last_name,job_id FROM employees WHERE job_id LIKE '%SA\_%'

 

搜尋空值 

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

 

多個條件搜尋 

SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;

資料排序
SELECT 欄位 FROM 表格 WHERE 條件 ORDER BY 欄位 [ASC, DESC]

例：SELECT last_name, job_id hire_date FROM employees ORDER BY hire_date ;

設定變數

&變數名
例：

SELECT employee_id, last_name FROM employees WHERE employee_id = &employee_num ;

SELECT last_name, department_id, salary*12 FROM employees WHERE  job_id = '&job_title'

張貼者： 小槌子 於 22:40