根據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_KellyIDENTIFIED BY SalesHead CREATE VIEW SalesEmployee AS
SELECT emp_id, emp_lname, emp_fnameFROM "dba".employeeWHERE 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' 根據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 ASSELECT emp_id, emp_lname, emp_fnameFROM "dba".employeeWHERE 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， 查看表的字段屬性 FROM customer KEY LEFT OUTER JOIN sales_order WHERE state = 'WA' GROUP BY company_name, state
系統表 SYSCATALOG，查看所有的表SYSCOLUMNS， 查看表的字段屬性
