sql一些基本语句

1.with cube 用法 统计出全部的信息

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的区别

 

ALL	<ALL	小于最小值	和distinct使用防止返回被多次选中的行
	>ALL	大于最大值
	<>ALL	等同于NOT IN
ANY	<ANY	小于最大值
	>ANY	大于最小值
	= ANY	等同IN