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 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 '
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 '
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, 查看表的字段属性
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接