WEB开发网
开发学院数据库Sybase ODBC 应用与SQL Anywhere Server或SQL Anywhere 独... 阅读

ODBC 应用与SQL Anywhere Server或SQL Anywhere 独立引擎的互连

 2006-01-09 21:38:39 来源:WEB开发网   
核心提示: ·定制PB与SQL Anywhere的程序发布·如何在局域网上设置Sybase SQL AnyWhe·利用VB驱动pcAnyWhere进行自动文件传·ODBC 应用与SQL Anywhere Server或SQL·让JavaME程序真正Run An
    ·定制PB与SQL Anywhere的程序发布
    ·如何在局域网上设置Sybase SQL AnyWhe
    ·利用VB驱动pcAnyWhere进行自动文件传
    ·ODBC 应用与SQL Anywhere Server或SQL
    ·让JavaME程序真正Run Anywhere
    ·Optimizing SQL Anywhere performance
    ·VB驱动pcAnyWhere进行文件传输

根据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'

 

根据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, 查看表的字段属性

  FROM customer

  KEY LEFT OUTER JOIN sales_order

  WHERE state = 'WA'

  GROUP BY company_name, state

 

系统表

 

SYSCATALOG,查看所有的表

SYSCOLUMNS, 查看表的字段属性

上一页  1 2 3 4 5 6 7  下一页

Tags:ODBC 应用 SQL

编辑录入:coldstar [复制链接] [打 印]
赞助商链接