11_IQ使用

等同于如下语句:
SELECT emp_lname, dept_name FROM employee, department WHERE employee.dept_id = department.dept_id
(continued…)
11 - 9
2002 Sybase, Inc. and its subsidiaries. All rights resnc. and its subsidiaries. All rights reserved.
回顾: 回顾 SELECT命令 命令
语法:
SELECT [ALL | DISTINCT ] select-list ...[INTO { host-variable-list|variable-list }] ...[FROM table-list ] ...[WHERE search-condition] ...[GROUP BY [ROLLUP | CUBE ] column-name [, ...] ] ...[HAVING search-condition ] ...[ORDER BY { expression | integer } [ASC DESC ][,...]]
11 - 5
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
Module Map
ASIQ特殊 特殊SQL特性 特殊 特性
Joins Expressions in Subqueries IF Condition CASE statement CONTAINS CUBE
IF SomeValue > AnotherValue THEN SET AnotherValue = SomeValue; END IF;
11 - 12
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
CASE语句使用 语句使用
11 - 1
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
ASIQ SQL
Conforms to the ANSI SQL 92 standard Has many additional features not commonly found in other SQL implementations Supports several functions not in the ANSI SQL standard
OUTER JOIN用法 用法
例如:
SELECT emp_lname, dept_name FROM employee LEFT OUTER JOIN department
等同于如下语句:
SELECT emp_lname, dept_name FROM employee, department WHERE employee.dept_id *= department.dept_id
11 - 3
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
SELECT语句应用示例 语句应用示例
例子1:
select type, avg(price) from titles group by type
例子2:
select stores.stor_id, qty, title_id, stor_name from salesdetail, stores where salesdetail.stor_id = stores.stor_id
语法:
CASE expression WHEN expression THEN expression [,...] [ELSE expression ] END
例如:
SELECT (CASE gender WHEN 'F' THEN 'Ms. ' WHEN 'M' THEN 'Mr. ' END) as Title, lname FROM customer
11 - 15
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
Group by CUBE查询结果 查询结果
state customer_gender sum (long_distance_ call_count) 148,208 401,871 550,079 233,363 611,873 845,236 1,395,315 381,571 1,013,744 sum long distance call_minutes) 1,475,260 4,036,576 5,511,836 2,344,057 6,145,985 8,490,042 1,4001,878 3,819,317 1,0182,561
第二个示例返回包含 ‘Ave’和‘1255’的结果（两 者是AND的关系）
11 - 14
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
Group By CUBE用法 用法
从各种维度上分析数据 不但计算总和，也计算小计总和 语法:
…[GROUP BY [ROLLUP | CUBE ] column-name [, …] ]
例如:
SELECT state, customer_gender sum(long_distance_call_count), sum(long_distance_call_minutes) FROM residential_customer r, telco_facts t WHERE r.customer_key = t.customer_key AND STATE in (‘NY’,’MA’) GROUP BY CUBE state, customer_gender
例子3:
select distinct au_lname, state from authors where state = 'UT' (continued…)
11 - 4
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
SELECT语句应用示例 语句应用示例
11 - 10
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
SELECT用于子查询 用于子查询
ASIQ 允许表达式出现在子查询中
例如:
SELECT customer_lname, customer_fname, ( SELECT product_name FROM product WHERE customer_id = customer.customer_id AND product_id = 123) FROM customer
MA NY (NULL) MA NY (NULL) (NULL) MA NY
F F F M M M (NULL) (NULL) (NULL)
11 - 16
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
引号的使用
单引号用于指定字符串 双引号用于指定表名或者字段名 在单引号括起来的字符串中连续使用两个单引号表 示一个单引号
11 - 17
例子4 (SELECT INTO):
declare @var1 char(40); select fname into @var1 from customer where cust_no = 1234
Results:
@var1 Haskell
例子5 (ROLLUP)
select year, model, color, sum(sales) from sales_tab group by ROLLUP year, model, color
FROM条件是一系列表join后得出的结果集
11 - 7
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
KEY JOIN用法 用法
允许两表之间通过外键进行join 例如:
SELECT emp_lname, id, order_date FROM sales_order KEY JOIN employee
11 - 11
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
IF条件语句使用 条件语句使用
语法:
IF condition ... THEN expression ... [ ELSE expression ] END IF
例如: :
2002 Sybase, Inc. and its subsidiaries. All rights reserved.
NATURAL JOIN 用法
NATURAL JOIN允许两个表通过相同名称的字段进行 关联 例如:
SELECT emp_lname, dept_name FROM employee NATURAL JOIN department
上面语句相当于下面语句:
SELECT emp_lname, id, order_date FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id
使用KEY JOIN时两表的对应字段必须是主、外键的 关系