sql一些基本语句

select tem,Color,sum(people) as QtySum  from  auth group by tem，Color with cube

tem                 Color                QtySum                    
-------------------- -------------------- ---------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair                (null)               311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table                (null)               347.00                    
(null)               (null)               658.00                    
(null)               Blue                 225.00                    
(null)               Red                  433.00

2.with rollup 用法 只统计出tem这一列

tem                 Color                QtySum                    
-------------------- -------------------- ---------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair                (null)               311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table                (null)               347.00 

3.按字段数据的汉字笔画排序

select * from teacher order by tname collate chinese_PRC_STROKE_CI_AS_KS

4.全外连接

select dept.*,rom.* from dept FULL outer join rom on dept.dno=rom.dno

5.交叉连接

select dept.* from dept cross join rom order by dept.dno;

6.union进行多表查询

union all 不会消除重复行

7.Intersect取两个结果的交集

8.except 拥有在table1但不在table2的行并消除重复的行

9all，any的区别