SELECT语句高级用法.docx

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/98and 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 iwhere o.order_date 01/01/98and o.customer_num 110and o.order_num = i.order_numgroup by 1,3having count(*) = 5*y.ship_dateand x.ship_date is not nulland y.ship_date is not nullorder 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 ship2from order x,orders y where x.ship_weight = 5*y.ship_weightand x.ship_date is not nulland y.ship_date is not nullorder by orders1,orders2into temp shipping;自连接子句三次select s1.manu_code,s2.manu_code,s3.manu_code,s1.stock_num,s1.descriptonfrom stock s1,stock s2,stock s3where s1.stock_num = s2.stock_numand s2.stock_num = s3.stock_numand s1.manu_code s2.manu_codeand s2.manu_code mgr.gross_payand emp.level (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,pany,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,pany,c.phone,u.call_dtime,u.call_descrfrom customer c left outer join cust_calls u on c.customer_num = u.customer_numwhere u.customer_num is null;右外连接在右外连接的语法中，外连接的控制表显示在开始外连接的关键字右边，右外连接返回连接条件为true的所有行，除此之外，还返回控制表中的所有其它行并将从属表中的相应值显示为nullselect c.customer_num,c.fname,c.lname,o.order_num,o.order_date,o.customer_numfrom customer c right outer join orders o on (c.customer_num = o.customer_num);简单连接select c.customer_num,c.lname,pany,c.phone,u.call_dtime,u.call_descr from customer c,cust_calls uwhere c.customer_num = u.customer_num;对两个表的简单外连接select c.customer_num,c.lname,pany,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_numand o.order_num = i.order_numand 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 xwhere c.customer_num = o.customer_numand 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_numand o.order_num = i.order_numand 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_chgfrom customer;where 子句中的子查询下来关键子在select语句的where子句中引入子查询allanyin exists使用all在子查询前面使用关键字all来确定对返回的每个值的比较是否为true.如果字查询不返回任何值，则搜索条件为true。（如果字查询不返回任何值，则对于所有零值条件为true)select order_num,stock_num,manu_code,total_pricefrom itemswhere 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 3;select distinct stock_num,manu_code from stock where unit_price 3 order by 2;将order by 与 union 配合使用select distinct stock_num,manu_code from stock where unit_price 3 order by 2;使用union all缺省情况下，union关键字排除重复的行。要保留重复的行，添加可选的关键字allselect stock_num,manu_code from stock where unit_price 3 order by 2 into temp stock item;使用不同的列名select distinct state from customer where custo