Chinaunix首页 | 论坛 | 博客

mysql乐园(linux)67248

首页　| 　博文目录　| 　关于我

x5f4T4r

  • 博客访问： 616688
  • 博文数量： 825
  • 博客积分： 5000
  • 博客等级： 大校
  • 技术积分： 4980
  • 用 户 组： 普通用户
  • 注册时间： 2008-10-27 14:19
文章分类

全部博文（825）

文章存档

2011年（1）

2008年（824）

我的朋友
最近访客
推荐博文
Sybase及SQL Anywhere SQL语句小结-PLSQL-Oracle频道-中国IT实验室

分类：

2008-10-27 14:26:47

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

【责编:Amy】

--------------------next---------------------

阅读(308) | 评论(0) | 转发(0) |
0

上一篇：利用SQL语句完成位操作

下一篇：Oracle 分析函数的使用二-PLSQL-Oracle频道-中国IT实验室

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6