sql总结

子查询:

表达式子查询 

Select au_lname, au_fname from authors where city=  (select city from publishers where pub_name="abcde")

限定谓词子查询 
 Select pub_name from publishers from publishers  Where pub_id in  (select pub_id from titles where type='abcde')

相关查询  相关查询即嵌套查询依赖于外部父查询的值，嵌套查询要重复执行若干次。 
Select distinct t1.type from titles t1  Where t1.type in ( select t2.type from titles t2    where t1.pub_id!=t2.pub_id) 

集函数、分组与排序 
A、对查询结果进行聚集处理  聚集函数：
 Sum([all|distinct] expression),avg([all|distinct] exoression) ,
 Count([all|distinct]expression),
 count(*),
 max(expression),
 min(expression)
 Select count(*) from titles
B、用Group by 和 having 子句对查询结果分组  Select type ,avg(advance), sum(total_sales) from titles group by type  Select type from titles  group by type having count(*) >1  Having 类似于where , 但where 不能用聚集函数。 
C、用Order by 对查询结果进行排序  Select type ,avg(price) from titles group by type order by avg(price) 
D、Compute 子句  完成基于每一组中的值的聚集运算，聚集值作为一个新行出现在查询结果中。
 Select type ,price advance from titles order by type compute sum(price), sum(advance) by type

去重:

distinct

Select distinct tel from code_1th

group by ...having

Select type from titles  group by type having count(*) >1

like的用法:

通配符‘%’及‘-’，分别代表多个字符和单个字符

例: 查询以字母A 开头的:  select  *  from  table1 where name like 'A%'