黑马程序员--SQL学习总结(下)

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------

--联合结果集 union合并两个查询结果集，并将其中完全重复的数据行合并为一条
			 union all 不合并重复的数据行
	原则：两个表每个结果集必须有相同的列数，每个结果集的列必须类型相容
		  没有的用相同类型的数据补足，如:'临时工，无部门'
select FName,FAge，FDepartment from T_Employee
union
select FName,FAge,'临时工，无部门' from T_TempEmployee
	Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用union all

--案例1 要求查询员工的最低年龄和最高年龄，临时工和正式员工分别查询
select '正式员工最高年龄',max(FAge) from T_Employee
union all
select '正式员工最低年龄',min(FAge) from T_Employee
union all
select '临时工最高年龄',max(FAge) from T_TempEmployee
union all
select '临时工最低年龄',min(FAge) from T_TempEmployee
查询结果：
	无列名				无列名
1	正式员工最高年龄	28
2	正式员工最低年龄	22
3	临时工最高年龄		46
4	临时工最低年龄		26

--案例2 查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计
select FNumber,FSalary from T_Employee
union all
select '工资总额',sum(FSalary) from T_Employee
查询结果：
  FNumber	Salary 
1	f001	  1000
2	f002	  1000
3	f003	  1000
4	f004	  1000
5 工资总额	  4000

--数字函数
abs():		求绝对值
ceiling():	舍入到最大整数
floor():	舍入到最小整数
round(-3.61,0):	四舍五入 -3.61 -> -4 保留0位有效数字

len('abc'): 计算字符串长度
	select FName,len(FName) from T_Employee
lower() upper() :转小写、大写
ltrim() :字符串左侧的空格去掉
rtrim() :字符串右侧的空格去掉
ltrim(rtrim()): 去掉两侧的空格

--字符串函数
substring()
	select substring('abcdef',2,3) --结果：bcd

--日期函数: getdate() :取得当前日期函数
dateadd(datepart,number,date):计算增加以后的日期。
	参数date为带计算的日期；
	参数number为增量；
	参数datepart为计量单位。
	dateadd(day,3,date)		为计算日期date的3天后的日期
	dateadd(month,-8,date)	为计算日期date的8个月之前的日期
select dateadd(day,3,getdate())

datediff(datepart,startdate,enddate):计算两个日期之间的差额。

datepart(datepart,date):返回一个日期的特定部分
	select datepart(year,getdate()) --得到当前年份

--类型转换函数:
cast(expression as data_type) -- expression:表达式
convert(data_type,expression)
	select cast('123',as int)+1,cast('2008-08-08',as datetime),
	convert(datetime,'2009-09-09'),convert(varchar(50),123)
right(FldNumber,3) as 后三位
cast(right(FldNumber,3) as integer) as 后三位的整数形式
cast(right(FldNumber,3) as integer)+1 as 后三位加1

--空值类型处理 
	isnull(expression,value)：如果expression不为空则返回expression,否则为value
	例如：select isnull(FName,'佚名') as 姓名 from T_Employee

--case函数用法：
--单值判断，相当于switch case
--case expression
when value1 then returnvalue1
when value2 then returnvalue2
when value3 then returnvalue3
else defaultreturnvalue4
end
例子：
--select FName,
--(
--	case FLevel
--	when 1 then '普通客户'
--	when 2 then '会员'
--	when 3 then '白金会员'
--	else '未知客户类型'
--	end
--) as 客户类型
--from T_Customer

select FNumber,
(
	case
	when FAmount>0 then FAmount
	else 0
	end
) as 收入,
(
	case
	when FAmount<0 then FAmount
	else 0
	end
) as 支出
from T_Test

单号	金额
RK1		10
RK2		20
RK3		-30
RK4		-10
将上面的表输出如下的格式：
单号	收入	支出
RK1		10		 0
RK2		20		 0
RK3		0		 30
RK4		0		 10


select Name,
sum(
	case Score
	when N'胜' then 1
	else 0
	end
) as 胜,
sum(
	case Score
	when N'负' then 1
	else 0
	end
) as 负
from T_Score

有一张表T_Scores，记录比赛成绩：
Date	  	Name	  Score
2008-8-8	拜仁		胜
2008-8-9	奇才		胜
2008-8-9	湖人		胜
2008-8-10	拜仁		负
2008-8-8	拜仁		负
2008-8-12	奇才		胜
要求输出下面的格式：
Name	胜		负
拜仁	1		2
湖人	1		0
奇才	2		0

--练习： 分解任务 一步步实现
验证只要两个月不是一个月份，就是相差一个月(不是按照天数算的)
select datediff(nonth,convert(datetime,'2012-06-01'),convert(datetime,'2012-07-02')),
datediff(nonth,convert(datetime,'2012-06-15'),convert(datetime,'2012-07-02'))

	--判断本月
	datediff(month,StartDateTime,getdate())=0 --StartDateTime表中的一列

	要求：
	1输出所有数据中通话时间最长的5条记录。
	select top 5 * from T_SQL_Test
	order by DATEDIFF(second,StartDateTime,EndDateTime) desc
	2输出所有数据中拨打长途号码(对方号码以0开头)
	select sum(DATEDIFF(second,StartDateTime,EndDateTime)) from T_SQL_Test
	where TelNum like '0%'
	3输出本月通话时间最长最多的前三个呼叫员的编号
	select top 3 CallerNumber from T_SQL_Test
	where DATEDIFF(month,StartDateTime,convert(datetime,'2010-07-01'))=0
	group by CallerNumber
	order by sum(DATEDIFF(second,StartDateTime,EndDateTime)) desc
	4输出本月拨打电话次数最多的前三个呼叫员的编号
	select CallerNumber ,COUNT(*)
	from T_SQL_Test
	where DATEDIFF(month,StartDateTime,convert(datetime,'2010-07-01'))=0
	group by CallerNumber
	order by COUNT(*) desc
	5输出所有数据的拨号流水，并且在左后一行添加呼叫时间长
		呼叫员编号、对方号码、通话时长
		....
		汇总[市内号码总时长][长途号码总时长]
	select CallerNumber,TelNum,DATEDIFF(second,StartDateTime,EndDateTime)
	from T_SQL_Test
	union all
	select '汇总',
	CONVERT(varchar(50),--字符串转换 电话号码 转换varchar值 时溢出了整数列，超过了其中最大的整数值。
	sum((
		case
		when TelNum not like '0%' then DATEDIFF(SECOND,StartDateTime,EndDateTIme)
		else 0
		end
	))
	) as 市内通话,
	sum((
		case
		when TelNum like '0%' then DATEDIFF(SECOND,StartDateTime,EndDateTIme)
		else 0
		end
	)) as 长途通话
	from T_SQL_Test

--索引Index
	全表扫描：对数据进行检索(select)效率最差的就是全表扫描，就是一条条的找。
--创建索引
	创建方式：在表设计器中点击右键，选择添加"索引/键"->添加—>在 列 中选择索引包括的列
	使用索引能提高查询效率，但是索引也是占空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。创建索引也不一定提高查询效率。如：like、函数、类型转换等语句中
	
--Join表连接
select * from
T_Orders as o join T_Customers as c on o.CustomerID=c.ID
where c.Age >(select avg(Age) from T_Customers)

--子查询
	将一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。
select * from
(
	select * from T_Orders
) as o1

select 1 as F1,2,(select min(FYearPublished) from T_Book),
(select max(FYearPublished) from T_Book) as F4
	
--错误 只能是单行单列的查询
select 1 as F1,(select min(FYearPublished),min(FYearPublished) from T_Book)
--错误 子查询返回的值不止一个
select 1 as F1,(select FYearPublished from T_Book)

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。
select * from T_Reader
where FYearOfJoin in
(
	select YearPublished from T_Book
)

--ROW_NUMBER() 函数 只能出现在select和order by中
select * from
(
select ROW_NUMBER() over(order by FSalary desc) as rownum,
FNumber,FName,FSalary,FAge from T_Employee
) as e1
where e1.rownum>=3 and e1.rownum<=5

----------------------  Windows Phone 7手机开发 、 .Net培训 、期待与您交流！ ----------------------