Sybase及SQL Anywhere SQL語句小結(轉)

根據SQL Anywhere User's Guide所作小結。絕大部分都可用直接於Sybase資料庫。

SELECT語句

SELECT *

FROM employee

SELECT *

FROM employee

ORDER BY emp_lname ASC

SELECT *

FROM employee

ORDER BY emp_lname DESC

SELECT emp_lname, dept_id, birth_date

FROM employee

SELECT *

FROM employee

WHERE emp_fname='John'

(一定使用單引號)

SELECT emp_fname, emp_lname, birth_date

FROM employee

WHERE emp_fname = 'John'

ORDER BY birth_date

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date < 'March 3, 1964'

(=、<、>、<=、>=、<>，加上AND與OR)

SELECT emp_lname, emp_fname

FROM employee

WHERE emp_lname LIKE 'br%'

(%、_)

SELECT emp_lname, emp_fname

FROM employee

WHERE SOUNDEX( emp_lname ) = SOUNDEX( 'Brown' )

(找出英文中發音相同的記錄，中文下用處不大)

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date BETWEEN '1965-1-1' AND '1965-3-31'

SELECT emp_lname, emp_id

FROM employee

WHERE emp_lname IN ('yeung', 'bucceri', 'charlton')

連線表

SELECT *

FROM sales_order, employee

WHERE sales_order.sales_rep = employee.emp_id

SELECT E.emp_lname, S.id, S.order_date

FROM sales_order as S, employee as E

WHERE S.sales_rep = E.emp_id

ORDER BY E.emp_lname

連線兩表的快捷鍵：KEY JOIN及NATURAL JOIN，最好用WHERE.

SELECT emp_lname, id, order_date

FROM sales_order

KEY JOIN employee

(主鍵與外部鍵對應的地方，就可以用KEY JOIN)

SELECT company_name,

CAST( SUM(sales_order_items.quantity * product.unit_price) AS INTEGER) AS value

FROM customer

KEY JOIN sales_order

KEY JOIN sales_order_items

KEY JOIN product

GROUP BY company_name

SELECT emp_lname, dept_name

FROM employee

NATURAL JOIN department

(找出兩表間有相同的欄位名，進行連結)

集合

SELECT count( * )

FROM employee

SELECT count( * ),

min( birth_date ),

max( birth_date )

FROM employee

(MIN, MAX, COUNT, AVG, SUM, LIST，作為單獨的一列選出)

SELECT sales_rep, count( * )

FROM sales_order

GROUP BY sales_rep

(在使用GROUP BY時，對於GROUP BY指定的欄位，其每一個不同的值都會組成一行)

SELECT sales_rep, count( * )

FROM sales_order

KEY JOIN employee

GROUP BY sales_rep

HAVING count( * ) > 55

更新資料庫

INSERT

INTO department ( dept_id, dept_name, dept_head_id )

VALUES ( 220, 'Eastern Sales', 902 )

INSERT

INTO department

VALUES ( 220, 'Eastern Sales', 902 )

UPDATE employee

SET dept_id = 400, manager_id = 1576

WHERE emp_id = 195

DELETE

FROM employee

WHERE termination_date IS NOT NULL

DELETE

FROM employee

WHERE LEFT( phone, 3 ) = '617' AND manager_id = 902

檢視

CREATE VIEW emp_dept AS

SELECT emp_fname, emp_lname, dept_name

FROM employee

JOIN department ON department.dept_id = employee.dept_id

SELECT *

FROM emp_dept

(檢視能自動更新狀態)

DROP VIEW emp_dept

CREATE VIEW emp_dept(FirstName, LastName, Department) AS

SELECT emp_fname, emp_lname, dept_name

FROM employee JOIN department ON department.dept_id = employee.dept_id

(建立檢視不能使用ORDEY BY，但使用檢視可以使用)

SELECT LastName, dept_head_id

FROM emp_dept, department

WHERE emp_dept.Department = department.dept_name

(將檢視與其他表進行進一步的連結)

檢視許可權管理

GRANT CONNECT TO M_Kelly

IDENTIFIED BY SalesHead

CREATE VIEW SalesEmployee AS

SELECT emp_id, emp_lname, emp_fname

FROM "dba".employee

WHERE dept_id = 200

GRANT SELECT ON SalesEmployee TO M_Kelly

CONNECT USER M_Kelly IDENTIFIED BY SalesHead ;

SELECT * FROM "dba".SalesEmployee

子查詢

SELECT *

FROM sales_order_items

WHERE prod_id IN

( SELECT id

FROM product

WHERE quantity < 20 )

ORDER BY ship_date DESC

SELECT *

FROM fin_data

WHERE fin_data.code = ANY ( SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

（=ANY 相當於IN）

SELECT *

FROM fin_data

WHERE fin_data.code <> ALL ( SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

(相當於NOT IN)

SELECT sales_order.id, sales_order.order_date,

( SELECT company_name

FROM customer

WHERE customer.id = sales_order.cust_id )

FROM sales_order

WHERE order_date > '1994/01/01'

ORDER BY order_date

(如果其他表只要求產生一個欄位，就可以使用子查詢來代替連線)

SELECT company_name, state,

( SELECT MAX( id )

FROM sales_order

WHERE sales_order.cust_id = customer.id )

FROM customer

WHERE state = 'WA'

SELECT company_name, MAX( sales_order.id ),state

FROM customer

KEY LEFT OUTER JOIN sales_order

WHERE state = 'WA'

GROUP BY company_name, state

系統表

SYSCATALOG，檢視所有的表

SYSCOLUMNS， 檢視錶的欄位屬性

KEY LEFT OUTER JOIN sales_order

WHERE state = 'WA'

GROUP BY company_name, state

系統表

SYSCATALOG，檢視所有的表

SYSCOLUMNS， 檢視錶的欄位屬性