熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Sybase及SQLAnywhereSQL語句小結

2013-11-13 15:35:50  來源: Oracle 

  根據SQL Anywhere Users 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

  (=<><=>=<>加上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 AND

   

  SELECT emp_lname emp_id

  FROM employee

  WHERE emp_lname IN (yeung bucceri charlton)

   

  連接表

   

  SELECT *

  FROM sales_order employee

  WHERE sales_ordersales_rep = employeeemp_id

   

  SELECT Eemp_lname Sid Sorder_date

  FROM sales_order as S employee as E

  WHERE Ssales_rep = Eemp_id

  ORDER BY Eemp_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_itemsquantity * productunit_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( * ) >

   

  更新數據庫

   

  INSERT

  INTO department ( dept_id dept_name dept_head_id )

  VALUES ( Eastern Sales )

   

  INSERT

  INTO department

  VALUES ( Eastern Sales )

   

  UPDATE employee

  SET dept_id = manager_id =

  WHERE emp_id =

   

  DELETE

  FROM employee

  WHERE termination_date IS NOT NULL

   

  DELETE

  FROM employee

  WHERE LEFT( phone ) = AND manager_id =

  

  視圖

   

  CREATE VIEW emp_dept AS

  SELECT emp_fname emp_lname dept_name

  FROM employee

  JOIN department ON departmentdept_id = employeedept_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 departmentdept_id = employeedept_id

   

  (創建視圖不能使用ORDEY BY但使用視圖可以使用)

   

  SELECT LastName dept_head_id

  FROM emp_dept department

  WHERE emp_deptDepartment = departmentdept_name

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

   

  視圖權限管理

   

  GRANT CONNECT TO M_Kelly

  IDENTIFIED BY SalesHead

   

  CREATE VIEW SalesEmployee AS

  SELECT emp_id emp_lname emp_fname

  FROM dbaemployee

  WHERE dept_id =

   

  GRANT SELECT ON SalesEmployee TO M_Kelly

   

  CONNECT USER M_Kelly IDENTIFIED BY SalesHead ;

  SELECT * FROM dbaSalesEmployee

   

  子查詢

   

  SELECT *

  FROM sales_order_items

  WHERE prod_id IN

  ( SELECT id

  FROM product

  WHERE quantity < )

  ORDER BY ship_date DESC

   

  SELECT *

  FROM fin_data

  WHERE de = ANY (  SELECT de

  FROM fin_code

  WHERE type = revenue )

  (=ANY 相當於IN)

   

  SELECT *

  FROM fin_data

  WHERE de <> ALL (  SELECT de

  FROM fin_code

  WHERE type = revenue )

  (相當於NOT IN)

   

  SELECT   sales_orderid sales_orderorder_date

  ( SELECT company_name

  FROM customer

  WHERE customerid = sales_ordercust_id )

  FROM   sales_order

  WHERE order_date > //

  ORDER BY order_date

  (如果其他表只要求產生一個字段就可以使用子查詢來代替連接)

   

  SELECT company_name state

  ( SELECT MAX( id )

  FROM sales_order

  WHERE sales_ordercust_id = customerid )

  FROM customer

  WHERE state = WA

   

  根據SQL Anywhere Users 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

  (=<><=>=<>加上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 AND

   

  SELECT emp_lname emp_id

  FROM employee

  WHERE emp_lname IN (yeung bucceri charlton)

  

  連接表

   

  SELECT *

  FROM sales_order employee

  WHERE sales_ordersales_rep = employeeemp_id

   

  SELECT Eemp_lname Sid Sorder_date

  FROM sales_order as S employee as E

  WHERE Ssales_rep = Eemp_id

  ORDER BY Eemp_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_itemsquantity * productunit_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( * ) >

   

  更新數據庫

   

  INSERT

  INTO department ( dept_id dept_name dept_head_id )

  VALUES ( Eastern Sales )

   

  INSERT

  INTO department

  VALUES ( Eastern Sales )

   

  UPDATE employee

  SET dept_id = manager_id =

  WHERE emp_id =

   

  DELETE

  FROM employee

  WHERE termination_date IS NOT NULL

   

  DELETE

  FROM employee

  WHERE LEFT( phone ) = AND manager_id =

   

  視圖

   

  CREATE VIEW emp_dept AS

  SELECT emp_fname emp_lname dept_name

  FROM employee

  JOIN department ON departmentdept_id = employeedept_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 departmentdept_id = employeedept_id

   

  (創建視圖不能使用ORDEY BY但使用視圖可以使用)

   

  SELECT LastName dept_head_id

  FROM emp_dept department

  WHERE emp_deptDepartment = departmentdept_name

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

  

  視圖權限管理

   

  GRANT CONNECT TO M_Kelly

  IDENTIFIED BY SalesHead

   

  CREATE VIEW SalesEmployee AS

  SELECT emp_id emp_lname emp_fname

  FROM dbaemployee

  WHERE dept_id =

   

  GRANT SELECT ON SalesEmployee TO M_Kelly

   

  CONNECT USER M_Kelly IDENTIFIED BY SalesHead ;

  SELECT * FROM dbaSalesEmployee

   

  子查詢

   

  SELECT *

  FROM sales_order_items

  WHERE prod_id IN

  ( SELECT id

  FROM product

  WHERE quantity < )

  ORDER BY ship_date DESC

   

  SELECT *

  FROM fin_data

  WHERE de = ANY (  SELECT de

  FROM fin_code

  WHERE type = revenue )

  (=ANY 相當於IN)

   

  SELECT *

  FROM fin_data

  WHERE de <> ALL (  SELECT de

  FROM fin_code

  WHERE type = revenue )

  (相當於NOT IN)

   

  SELECT   sales_orderid sales_orderorder_date

  ( SELECT company_name

  FROM customer

  WHERE customerid = sales_ordercust_id )

  FROM   sales_order

  WHERE order_date > //

  ORDER BY order_date

  (如果其他表只要求產生一個字段就可以使用子查詢來代替連接)

   

  SELECT company_name state

  ( SELECT MAX( id )

  FROM sales_order

  WHERE sales_ordercust_id = customerid )

  FROM customer

  WHERE state = WA

   

  SELECT  company_name MAX( sales_orderid )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 查看表的字段屬性


From:http://tw.wingwit.com/Article/program/Oracle/201311/16984.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.