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