sql指令总结

一、SQL指令

一般查找 select     store from     store_information

  去重复查找distinct select     distinct store_name from     store_information

  选择性抓取 select     store_name,date from     store_information where     sales>1000      用法and,or select     store_name from     store_information where     sales>1000 or (sales<500 and sales>275)

  用法in  (放置事先已知的值) select     * from     store_information where     store_name in ('los','san')

  用法between and select     * from     store_information where     date between 1000 and 1200     用法like select     * from     store_information where     store_name like'%AN%'     用法order by select     sales from     store_information order by     sales desc         函数用法 select sum(sales) from store_information select max(sales) from store_information select min(sales) from store_information select avg(sales) from store_information select count(sales) from store_information  

用法count select     count(store_name) from     store-information where     store_name is not null   结合count,distinct select     count(distinct store_name) from     store_information

用法group by select     store_name,sum from     store_information group by     store_name     用法having(对函数产生的值设定条件) select     store-name,sum(sales) from     store_information group by     store_name having     sum(sales)>1500         二、表格链接

常义链接 select     a1.region_name as Region, a2.sum(sales) as Sales from     geography as a1,store_information as a2 where  a1.store_name=a2.store_name group by     a1.region_name     左链接(left join)    select     * from   store_information as a1  left join  geography as a2  on a1.store_name=a2.store_name

  右链接(outer join)  select     * from   store_information as a1  right join  geography as a2  on a1.store_name=a2.store_name     内部链接(inner join) select     * from   store_information as a1  inner join  geography as a2  on a1.store_name=a2.store_name

  三、表格

视图view（虚拟表格，根据具体表格不同而不同） create view vwstore as select     store_name,region_name,sales from     store_information    

  四、进阶SQL

子查询