Sybase及SQL Anywhere SQL语句小结

根据SQL Anywhere User's Guide所作小结。绝大部分都可用直接于Sybase数据库。SELECT语句SELECT *; FROM employeeSELECT *; FROM employee; ORDER BY emp_lname ASCSELECT *; FROM employee; ORDER BY emp_lname DESCSELECT emp_lname, dept_id, birth_date; FROM employeeSELECT *; FROM employee; WHERE emp_fname='John'(一定使用单引号) SELECT emp_fname, emp_lname, birth_date; FROM employee; WHERE emp_fname = 'John'; ORDER BY birth_dateSELECT 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_idSELECT 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_nameSELECT emp_lname, dept_name; FROM employee; NATURAL JOIN department(找出两表间有相同的字段名，进行连结)集合SELECT count( * ); FROM employeeSELECTcount( * ), ; 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 = 195DELETE; FROM employee; WHERE termination_date IS NOT NULLDELETE; 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_idSELECT * ; FROM emp_dept(视图能自动更新状态)DROP VIEW emp_deptCREATE 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 SalesHeadCREATE VIEW SalesEmployee AS SELECT emp_id, emp_lname, emp_fnameFROM 'dba'.employeeWHERE dept_id = 200GRANT SELECT ON SalesEmployee TO M_KellyCONNECT 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 DESCSELECT *; 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)SELECTsales_order.id, sales_order.order_date,; ( SELECT company_name;FROM customer;WHERE customer.id = sales_order.cust_id ); FROMsales_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 employeeSELECT *; FROM employee; ORDER BY emp_lname ASCSELECT *; FROM employee; ORDER BY emp_lname DESCSELECT 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_dateSELECT 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_idSELECT 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_nameSELECT emp_lname, dept_name; FROM employee; NATURAL JOIN department(找出两表间有相同的字段名，进行连结)集合SELECT count( * ) ; FROM employeeSELECTcount( * ),; 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 = 195DELETE; FROM employee; WHERE termination_date IS NOT NULLDELETE; 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_idSELECT *; FROM emp_dept(视图能自动更新状态)DROP VIEW emp_deptCREATE 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 SalesHeadCREATE VIEW SalesEmployee ASSELECT emp_id, emp_lname, emp_fnameFROM 'dba'.employeeWHERE dept_id = 200GRANT SELECT ON SalesEmployee TO M_KellyCONNECT 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 DESCSELECT * ; 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)SELECTsales_order.id, sales_order.order_date,; ( SELECT company_name;FROM customer;WHERE customer.id = sales_order.cust_id ); FROMsales_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， 查看表的字段属性