MS SQL Server学习笔记（二）

1、类型转换函数
	--cast(表达式 as 数据类型)
	--convert(数据类型,表达式)
	SELECT 100.0+CAST('1000' AS INT)
	SELECT 100.0+CONVERT(INT,'1000')
	SELECT '您的班级编号：'+CONVERT(VARCHAR(1),1)
	SELECT * FROM dbo.TblStudent ORDER BY CAST(tsClass AS INT) DESC--字符串进行类型转换然后排序
	PRINT CONVERT(VARCHAR(10),GETDATE(),120)--Convert对日期的转换
2、联合结果集union(集合运算符)--把多个结果集中的行联合起来，连接join（多个结果集中的列联合起来）
	SELECT tsName FROM dbo.TblStudent
	UNION ALL--不自动去除重复并不进行数据的自动排列
	SELECT ttname FROM dbo.TblTeacher

	SELECT tsName FROM dbo.TblStudent
	UNION --自动去除重复并对联合数据自动进行排列
	SELECT ttname FROM dbo.TblTeacher
	--大多数情况下联合的时候不需要去除重复，同时要保持数据的顺序，所以一般建议使用union all
	SELECT 
		商品名称,
		销售价格=SUM(销售价格*销售数量)
	FROM Myorders
	GROUP BY 商品名称
	UNION ALL
	SELECT '总销售价格',SUM(销售价格*销售数量) FROM Myorders
	ORDER BY 销售总价 ASC
	--要查询出成绩表中的：最高分，最低分和平均分
	SELECT 
		MAX(tmath) AS 最高分,
		MIN(tmath) AS 最低分,
		AVG(tmath) AS 平均分
	FROM dbo.TblScore

	SELECT 
		最高分=(SELECT MAX(tmath)FROM dbo.TblScore),
		最高分=(SELECT MIN(tmath)FROM dbo.TblScore),
		平均分=(SELECT AVG(tmath)FROM dbo.TblScore)
		
	SELECT 名称='最高分',分数=MAX(tmath) FROM dbo.TblScore
	UNION ALL
	SELECT 名称='最低分',分数=min(tmath) FROM dbo.TblScore
	UNION ALL
	SELECT 名称='平均分',分数=AVG(tmath) FROM dbo.TblScore
	--使用union向表中插入多条数据
	INSERT INTO dbo.TblStudent
			( tsName ,
			  tsGender ,
			  tsAddress ,
			  tsAge ,
			  tsBirthday ,
			  tsCardID ,
			  tsClass
			)
	SELECT '丁许aa','男','sadsadsa',40,'1992-09-11',12321,2
	UNION ALL
	SELECT '丁许bb','男','sadsadsa',40,'1992-09-11',12321,2
	UNION ALL
	SELECT '丁许cc','男','sadsadsa',40,'1992-09-11',12321,2
	UNION ALL
	SELECT '丁许dd','男','sadsadsa',40,'1992-09-11',12321,2
	--在使用union进行插入数据的时候，也要注意union会去除重复
	INSERT INTO dbo.TblStudent
			( tsName ,
			  tsGender ,
			  tsAddress ,
			  tsAge ,
			  tsBirthday ,
			  tsCardID ,
			  tsClass
			)
	SELECT '丁许aa','男','sadsadsa',40,'1992-09-11',12321,2
	UNION
	SELECT '丁许aa','男','sadsadsa',40,'1992-09-11',12321,2
	UNION
	SELECT '丁许aa','男','sadsadsa',40,'1992-09-11',12321,2
	UNION
	SELECT '丁许aa','男','sadsadsa',40,'1992-09-11',12321,2
3、一次性向表中插入多行数据
	--将tblstudent表的结构和表中数据备份到tblstudent2017Backup表中。
	--tblstudent2017Backup表是在执行select into语句时创建的。
	--select into语句不能重复执行，因为每次执行都会创建一个tblstudent2017Backup表
	--tblstudent表结构包括自动编号列都会在tblstudent2017Backup中创建，但是tblstudent表中的约束并不会出现在tblstudent2017Backup表中
	SELECT * INTO tblstudent2017Backup FROM dbo.TblStudent
	--只复制表结构，不复制数据
	SELECT TOP 0 * INTO tblstudent2017Backup FROM dbo.TblStudent


	--使用insert into 表 select ...from 表
	INSERT INTO tblstudent2017backup
	SELECT tsName,tsGender,tsAddress,tsage,tsBirthday,tsCardID,tsClass FROM dbo.TblStudent WHERE tsGender='女'
3、字符串函数
	--常用的字符串函数
	--1.len()计算字符的个数
	PRINT LEN('s丁许')--3
	--datalength()返回占用的字节个数，这个不是字符串函数
	PRINT datalength('s丁许')--5
	PRINT datalength(N's丁许')--6
	--2.
	PRINT UPPER('aaaA')--转换大写
	PRINT LOWER('aaaA')--转换小写
	--3.去掉两段空格
	PRINT '==============='+'      hello        '+'============'
	PRINT '==============='+LTRIM('      hello        ')+'============'
	PRINT '==============='+rTRIM('      hello        ')+'============'
	PRINT '==============='+LTRIM(rTRIM('      hello        '))+'============'
	--4.字符串截取函数
	---从左边开始截取，截取5个字符
	PRINT LEFT('中华人民共和国今天。。。',5)
	--4.2 right()
	-----从右边开始截取，截取5个字符
	PRINT right('中华人民共和国今天。。。',5)
	--4.3 substring()截取字符串
	PRINT SUBSTRING('中华人民共和国今天。。。',1,5)
	PRINT SUBSTRING('中华人民共和国今天。。。',5,2)
	PRINT SUBSTRING('中华人民共和国今天。。。',0,5)
	PRINT SUBSTRING('中华人民共和国今天。。。',-1,5)
4、日期函数
	PRINT GETDATE()
	PRINT SYSDATETIME()
	--dataAdd()，增加时间
	SELECT DATEADD(DAY,200,GETDATE())
	SELECT DATEADD(month,200,GETDATE())
	--datediff()计算两个日期的差
	--查询入职一年以上的员工
	SELECT 
		* 
	FROM dbo.TblStudent 
	WHERE DATEADD(YEAR,1,tsBirthday)<=GETDATE() 
	SELECT DATEDIFF(YEAR,'1991-05-31',GETDATE())
	--统计处入职n年的人的个数
	SELECT
		入职年数=DATEDIFF(year,tsBirthday,GETDATE()),
		人数=COUNT(*)
	FROM dbo.TblStudent
	GROUP BY DATEDIFF(year,tsBirthday,GETDATE())
	--获取日期的某部分值
	PRINT DATEPART(YEAR,GETDATE())
	PRINT MONTH(GETDATE())
	--返回日期的某部分，字符串表示形式，DATEPART式数字表示形式
	PRINT DATENAME(YEAR,GETANSINULL())

	--查询出不同年份入职的员工人数
	SELECT
		入职年份=DATEPART(YEAR,tsbirthday),
		员工人数=COUNT(*)
	FROM dbo.TblStudent
	GROUP BY DATEPART(YEAR,tsbirthday) 
5、综合练习
	--输出所有数据中通话时间最长的5条记录
	SELECT
		TOP 5*,
		通话时长=DATEDIFF(second,StartTime,EndTime)
	FROM callrecords
	ORDER BY DATEDIFF(second,StartTime,EndTime) DESC
	--输出所有数据中拨打长途号码（对方号码以0开头）的总时长
	SELECT
		SUM(DATEDIFF(second,StartTime,EndTime)) AS 通话总时长
	FROM callrecords
	WHERE telnumber LIKE '0%'

	--输出本月通话总时长最多的前三个呼叫员的编号
	SELECT
		TOP 3 callernumber AS 呼叫员编号,
		SUM(DATEDIFF(minute,StartTime,EndTime)) AS 通话时长
	FROM callrecords
	WHERE DATEDIFF(MONTH,starttime,'2010-01-04')=0
	GROUP BY callernumber
	ORDER BY SUM(DATEDIFF(minute,StartTime,EndTime)) DESC

	--输出本月拨打电话次数最多的前三个呼叫元的编号
	SELECT 
		TOP 3 callernumber AS 呼叫员编号,
		COUNT(*) AS 次数
	FROM callrecords
	WHERE DATEDIFF(MONTH,starttime,'2010-01-04')=0
	GROUP BY callernumber
	ORDER BY COUNT(*) DESC