3,编写高级select语句（笔记）

1,使用group by 子句
group by 子句将表分为几组，此子句通常与为每个这样的组生产总结值的聚集函数组合。
使用不带聚集的group by 子句与在select 子句中使用的distinct（或unique)关键字很相似。
select distinct customer_num from orders;
selecct customer_num from orders group by customer_num;

group by 子句将行收集到组中，因此每一组中的每一行具有相同的客户号。在没有选择任何其它列的情况下，结果是唯一的customer_num值的列表。

select order_num,count(*) number,sum(total_price) price from items group by 1 order by 3;

select o.order_num,sum(i.total_price) from order o,items i where o.order_date > '01/01/98'
and ocustomer_num = 110 and o.order_num = i.order_num group by o.order_num;

使用having子句
要完成group by 子句，使用having 子句来在构成组之后将一个或多个限制条件应用于这些组。having子句对组的影响类似于
where 子句限制个别行的方式，使用having子句的一个优点是可以在搜索条件中包括聚集，而在where子句的搜索条件中却不能包括聚集。

每个having条件将组的一列或一个聚集表达式与组的另一个聚集表达式或与常量作比较。可以使用having来对列值或组列表中的聚集值设置条件。

下面的语句返回具有两个商品以上的订单上每个商品的平均总价格。having子句在每个构成的测试组，并选择由两行以上构成的那些组。
select order_num,count(*) number,avg(total_price) average from items group by order_num having count(*) > 2;

如果不带group by 子句使用having子句，则该having条件适应雨满足搜索条件的所有行。换言之，满足搜索条件的所有行构成一个组。
select avg(total_price) average from items having count(*) > 2;

select o.order_num,sum(i.total_price) price, paid_date - order_date span from orders o,items i
where o.order_date > '01/01/98'
and o.customer_num > 110
and o.order_num = i.order_num
group by 1,3
having count(*) < 5
order by 3
into temp temptab1;

创建高级连接

自连接
连接不一定都是涉及两个不同的表。可以将表连接至它本身，缠结自连接。当想要将列中的值与同一列中的其它值进行比较时，将表连接
至它自身非常有用。

要创建自连接，在from 子句中列示表两次，并且每次为它指定不同的别名。与表之间的连接一样，可以在自连接中使用算术表达式。
可以测试空值，可以使用order by 子句来以升序或将序对指定列中的值进行排序。

select x.order_num,x.ship_weight,x,ship_date,y.order_num,y.ship_weight,y.ship_date from order x,order y
where x.ship_weight >= 5*y.ship_date
and x.ship_date is not null
and y.ship_date is not null
order by x.ship_date;

如果想要将自连接的结果存储到临时表中，则将Into temp子句追加到select语句中，并至少对一组列指定显示标号，以重命名这些列。否则，
重复列名将导致错误，并且不会创建临时表。

select x.order_num orders1,x.po_num purch1,
x.ship_date ship1,y.order_num orders2,
y.po_num purch2,y.ship_date ship2
from order x,orders y
where x.ship_weight >= 5*y.ship_weight
and x.ship_date is not null
and y.ship_date is not null
order by orders1,orders2
into temp shipping;

自连接子句三次
select s1.manu_code,s2.manu_code,s3.manu_code,
s1.stock_num,s1.descripton
from stock s1,stock s2,stock s3
where s1.stock_num = s2.stock_num
and s2.stock_num = s3.stock_num
and s1.manu_code < s2.manu_code
and s2.manu_code < s3.manu_code
order by stock_num;

如果想要从payroll表选择行来确定那些职员的薪水高于他们的经理，按照下面select 语句所示来构造自连接：
select emp.employee_num,emp.gross_pay,emp.level,
emp.detp_num,mgr.employee_num,mgr.gross_pay,
mgr.dept_num,mgr.level
from payroll emp,payroll mgr
where emp.gross_pay > mgr.gross_pay
and emp.level < mgr.level
and emp.dept_num = mgr.dept_num
order by 4;

使用相关子查询来检索并列示预定的10种价格最高的商品
select order_num,total_price
from items a
where 10 >
(select count(*)
from items b
where b.total_price < a.total_price )
order by total_price;

外连接
外连接使其中一个表成为控制表（也成为外部表），控制其它从属表（也成为内部表）。
在内连接或简单连接中，结果只保护满足连接条件的行组合，废弃部满足连接条件的行。
在外连接中，结果包含满足连接条件的行与控制表中的行（如果在从属表中找不到匹配的行将废弃这些行）的组合。
在从属表中没有相匹配的行的控制表在从属表选择的列中包含null值。

外连接允许在应用连接条件之前将连接过虑器应用于内部表。

ANSI外连接语法用left join,left outer join,right join和right outer join关键字开始外连接。outer关键字是可选的。
查询可在on子句中指定连接条件和可选连接过虑器。where 子句指定后连接过虑器。

左外连接
在左外连接的语法中，外连接的控制表显示在开始外连接的关键字的左边。左外连接返回连接条件为true的所有行，除此之外，
还返回控制表中的所有其它行并将从属表中的相应值显示为Null。

select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c left outer join cust_calls u on c.customer_num = u.customer_num;

select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c left outer join cust_calls u
on c.customer_num = u.customer_num
where u.customer_num is null;

右外连接
在右外连接的语法中，外连接的控制表显示在开始外连接的关键字右边，右外连接返回连接条件为true的所有行，除此之外，
还返回控制表中的所有其它行并将从属表中的相应值显示为null

select c.customer_num,c.fname,c.lname,o.order_num,o.order_date,o.customer_num
from customer c right outer join orders o on (c.customer_num = o.customer_num);

简单连接
select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr from customer c,cust_calls u
where c.customer_num = u.customer_num;

对两个表的简单外连接
select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c,outer cust_calls u where c.customer_num = u.customer_num;

cust_calls表前面的附加关键字outer使该表成为从属表。外连接导致查询返回有关所有客户的信息，而不管他们是否已经致电客户
服务中心，检索控制表customer的所有行，并且将Null值指定给从属表cust_calls的列。

与第三个表进行简单连接的外连接
这种外连接也称为嵌套简单连接

select c.customer_num,c.lname,o.order_num,i.stock_num,i.manu_code,i.quantity
from customer c, left outer join (orders o,items i)
where c.customer_num = o.customer_num
and o.order_num = i.order_num
and manu_code in ('KAR','SHM')
ORDER BY lanme;

将两个表与第三个表进行外连接
作为两个表中的每一个与第三个表的外连接的结果的外连接。在此第三中类型的外连接中，连接关系可能仅仅使与
从属表之间的关系。

select c.customer_num,c.lname,o.order_num,order_date,call_dtime
from customer c,outer orders o,outer cust_calls x
where c.customer_num = o.customer_num
and c.customer_num = x.customer_num
order by lname
into temp service;

组合外连接的连接
要实现多级嵌套，可以创建使用三种外连接类型的任何组合的连接。使用ansi语法，创建作为对两个表与另一个外连接的简单外连接
组合结果的连接。

select c.customer_num,c.lname,o.order_num,stock_num,manu_code,quantity
from customer c,outer (orders o,outer items i)
where c.customer_num = o.customer_num
and o.order_num = i.order_num
and manu_code in ('KAR','SHM')
ORDER BY lname;

select 语句中的子查询

下列情况定义数据库服务器支持子查询类型：
1，嵌套在另一个select 语句的投影列表中的select语句
2，嵌套在另一个select语句（或insert,delete或update语句中）的where子句中的select语句。

相关子查询
相关子查询使引用不在其from子句中的列或表的子查询。该列可以在projection子句或在where子句中。要查找查询引用的表，搜索不相关的列
直到找到相关为止。

projection子句中的子查询
子查询可以在另一个select语句的投影列表中发生。
select customer.customer_num,
(select sum(ship_charge)
from orders
where customer.customer_num = orders.customer_num)
as total_ship_chg
from customer;

where 子句中的子查询
下来关键子在select语句的where子句中引入子查询
all
any
in
exists

使用all
在子查询前面使用关键字all来确定对返回的每个值的比较是否为true.如果字查询不返回任何值，则搜索条件为true。
（如果字查询不返回任何值，则对于所有零值条件为true)

select order_num,stock_num,manu_code,total_price
from items
where total_price < all
(select total_price from items
where order_num = 1023);

使用any
在子查询前使用关键字any(或其同义词some)来确定是否对至少一个返回值的比较为true.如果子查询不返回任何值，则搜索
条件为false.(因为没有值存在，所以对于其中一个值条件不能为true)

select distinct order_num from items where total_price > any
(select total_price from items where order_num = 1005);

单值子查询
如果你指定子查询可能对外部级别查询返回刚好一个值，则不需要包括关键字all或any.可如同对待函数一样对待函数一样对待
只返回一个值的子查询。这种子查询通常使用聚集函数，原因是聚集函数总是返回单个值。

select order_num from items where stock_num = 9 and quantity =
(select max(quantity) from items where stock_num = 9);

相关子查询
select po_num,ship_date from orders main where 10>
( select count (idstinct ship_date) from orders sub where sub.ship_date < main.ship_date) and
ship_date is not null
order by ship_date,po_num;

使用exists
关键字exists也称为存在限定符，原因是仅当外部select找到至少一行时，子查询才为true.
select unique manu_name,lead_time from manufact where exists
(select * from stock where description mantches '*shoe*'
and manufact.manu_code = stock.manu_code);

集合运算
标准集合运算联合，相交和差异允许你出来数据库信息。这三种运算允许你使用select语句在执行更新，
插入或删除之后检查数据库的完整性。

联合
联合运算使用union关键字或运算符来将两个查询组合成单个符合查询。可以在两个或多个select语句之间使用
union运算符来联合它们，并产生一个临时表，它包含存在于任何一个原始表或所有原始表中的行。还可以在视图
的定义中使用union运算符。

例子：
select distinct stock_num,manu_code from stock where unit_price < 25.00 union
select stock_num,manu_code from items where quantity > 3;

select distinct stock_num,manu_code from stock where unit_price < 25.00 union
select stock_num,manu_code from items where quantity > 3 order by 2;

将order by 与 union 配合使用
select distinct stock_num,manu_code from stock where unit_price < 25.
union select stock_num,manu_code from items where quantity > 3 order by 2;

使用union all
缺省情况下，union关键字排除重复的行。要保留重复的行，添加可选的关键字all

select stock_num,manu_code from stock where unit_price < 25.00
union all select stock_num,manu_code from items where quantity >3 order by 2 into temp stock item;

使用不同的列名
select distinct state from customer where customer_num between 120 and 15
union select distinct code from state where sname matches '*a';

将union与多个表配合使用
select stock_num,manu_code from stock where unit_price > 600.00 union all
select stock_num,manu_code from ctalog where catalog_num = 10025
union all select stock_num,manu_code from items where quantity = 10 order by 2;

相交
两个行集的相交产生一个表，它包含同时存在于两个原始表的行。使用关键字exists或in来引入显示两个集合相交的子查询
select stock_num,manu_code,unit_price from stock where stock_num in ( select stock_num from items)
order by stock_num;

差异
两个行集之间的差异产生一个表，它波包含在第一个行集中但不在第二个行集中的行，使用关键字not exists或not in 来
引入两个集合之间的差异的子查询。

select stock_num,manu_code,unit_price from stock where stock_num not in ( select stock_num from items)
order by stock_num;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/312079/viewspace-245653/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/312079/viewspace-245653/