第17课–数据库开发及ado.net
聚合函数,模糊查询like,通配符.空值处理.order by排序.分组group by-having.类型转换-cast,Convert.union all; Select 列 into 新表;字符串函数;日期函数
SQL聚合函数
MAX(最大值)、MIN(最小值)、AVG(平均值)、SUM(和)、COUNT(数量：记录的条数)
聚合函数对null不计算。如果一行数据都是null，count(*)包含对空值行、重复行的统计。
–聚合函数演示
select * from TblStudent
–cast convert ???
–平均年龄
select AVG(tsage*1.0) as 平均值 from TblStudent
select AVG(tsage*1.0) 平均值 from TblStudent
select 平均值=AVG(tsage*1.0) from TblStudent
–报错.当使用聚合函数的时候，注意在select查询列表中不要出现除了使用聚合函数以外的其他列，
–除非该列也使用了聚合函数或者该列包含在了Group By子句中。
select 平均值=AVG(tsage*1.0),tSName from TblStudent
–求和
select SUM(tsage) from TblStudent
–求最大值
select MAX(tsage) from TblStudent
–最小值
select min(tsage) from TblStudent
–总条数
select count(*) from TblStudent
–将tSId=32和tSId=30 tsage=null
update TblStudent set tSAge=null where tSId=32 or tSId=30
—
select COUNT(tsage) as 记录条数 from TblStudent
select
COUNT(tsage),
SUM (tsage),
MAX (tsage),
MIN (tsage),
AVG(tsage)
from tblstudent
select * from TblStudent–主键表
select * from TblScore–外键表
–查询英语没有及格的学生的学号
select tsid from TblScore where tEnglish <60
–查询年龄在-30岁之间的男学生
select * from TblStudent where tsage<=30 and tsage>=20 and tSGender =’男’
–not and or 是个逻辑运算符，优先级not→and→or
–Between…and…在之间
–查询年龄在-30岁之间的男学生
select * from TblStudent where tSAge between 20 and 30
–查询Math成绩在-70分之间的所有学生
select tsid from TblScore where tmath between 65 and 70
–查询班级ID为,2,3的所有学生
select * from TblStudent where tSClassId in (1,2,3)
select * from TblStudent where tSClassId =1 or tSClassId =2 or tSClassId =3
–如果上面的写法可以优化为下面的这种写法,则尽量用下面这种写法.
select * from TblStudent where tSClassId >=1 and tSClassId <=3
模糊查询（都是针对字符串操作的）
–模糊查询，通配符
select * from TblStudent
–之前的查询使用:=
select * from TblStudent where tSName =N’张犇’
–第一个通配符% 表示任意多个任意字符。
–当使用通配符来匹配的时候必须使用Like
select * from TblStudent where tSName like N’张%’
–通过[]将%包含起来,则%不在是表示一个通配符,而是表示一个普通的字符
select * from TblStudent where tSName like N’%[%]%’
–通配符:_ 表示（单个）一个任意字符。
select * from TblStudent where tSName like N’貂_’
select * from TblStudent where tSName like N’貂%’ and LEN(tSName)=2
–通配符:[]
select * from TblStudent where tSName like N’张_妹’
select * from TblStudent where tSName like N’张[0-9]妹’
select * from TblStudent where tSName like N’张[a-z]妹’
–like 和 not like [^] 通配符的使用注意区分
select * from TblStudent where tSName like N’张_妹’
select * from TblStudent where tSName not like N’张_妹’
select * from TblStudent where tSName not like N’张[^0-9]妹’
空值处理
–空值处理
–null
select * from TblStudent
update TblStudent set tSName =null where tSId=1 –表示数据库的空值
update TblStudent set tSName =” where tSId=2 –表示一个长度为零的字符串
–请查询出所有tsage为null的学生记录（以下两种都是错误的。）
select * from TblStudent where tSAge =null
select * from TblStudent where tSAge <>null
–数据库中的null值比较特殊，表示一个unknow的值。不知道的值。
–在数据库中对NULL值不能用=或<>来判断，要判断null值，只能使用一个特殊的运算符IS来判断。
select * from TblStudent where tSAge is null
select * from TblStudent where tSAge is not null
–isnull(),表示一个函数区别以上的is null
–null与任何运算结果都是NULL
数据排序(order by)
ASC：升序（从小到大排序）
DESC：降序（从大到小排序）
use Itcast2013
select * from tblscore order by tenglish desc,tMath asc
select *, (tEnglish +tMath )as 总分 from TblScore order by (tEnglish +tMath )desc
–order by 在sql语句中位置一定在最后
–select…3> 对筛选出的数据再进行列的筛选
–from …1>从数据源中获取数据
–where …2>从获取的数据中筛选一部分符合要求的数据，where是进行行的筛选
数据分组(group by) 为了数据统计
Group by子句必须放在where语句之后，Group By与 Order By 都是对筛选后的数据进行处理，而where是用来筛选数据。
use Itcast2013
select * from tblscore order by tenglish desc,tMath asc
select *, (tEnglish +tMath )as 总分 from TblScore order by (tEnglish +tMath )desc
–order by 在sql语句中位置一定在最后
–select…3> 对筛选出的数据再进行列的筛选
–from …1>从数据源中获取数据
–where …2>从获取的数据中筛选一部分符合要求的数据，where是进行行的筛选
–数据分组
–Group by子句必须放在where语句之后，Group By与Order By 都是对筛选后的数据进行处理，而where是用来筛选数据。
–1.请从学生表中查询出每个班的班级Id和班级人数：
–从该学生表中统计出每个班的人数。
–当在查询中使用了group by 分组后，则在select 中出现的列，必须是group by 中包含的列或者该列必须在聚合函数中
select tSClassId as 组编号 ,COUNT(*) 该组中的记录条数 from TblStudent group by tSClassId
–2.请从学生表中查询出每个班级的班级ID和班级中男同学的人数。
select
tSClassId as 组编号 ,
COUNT(*) 男同学人数 –4.
from TblStudent –1.
where tSGender =N’男’ –2.
group by tSClassId –3.
–在分组前对数据筛选使用where
–而在分组后，对于哪些组保留，哪些组不保留，对于组的筛选则使用另外一个关键是字：Having
–having 是对组后，进行筛选
select
tSClassId as 班级编号,–4.
COUNT(*) as 人数
from TblStudent –1.
group by tSClassId –2.
HAVING COUNT(*) >10 –3.
–HAVING 人数>10
–3.在这里不能使用别名”人数”,因为sql语句的执行顺序是先执行group by 与having 然后才执行select
–在执行having的时候select还没执行，所以无法使用select中列的别名
select tSClassId,COUNT(tSName)人数 from TblStudent group by tSClassId
select tSClassId,COUNT(tSName)人数,tSAge from TblStudent group by tSClassId,tSAge
select tsgender,tSAddress ,count(*)
from tblstudent
group by tsgender,tSAddress
–多列分组，则必须满足多列的条件。
–完整的select语句执行顺序
1.from
2.on
3.join
4.where
5.group by
6.with cube 或with rollup
7.having
8.select
9.distinct
10.order by
11.top
—-Group by 练习
select * from MyOrders
–1.热销商品排名表
select 商品名称,sum(销售数量) 销售总数
from MyOrders
group by 商品名称
order by 销售总数 desc
–2.统计销售总价超过元的商品名称和销售总结,并按销售总价格降序排列
select 商品名称, 销售总价=sum(销售数量*销售价格)
from MyOrders
group by 商品名称
having sum(销售数量*销售价格)>3000
order by 销售总价 desc
–3.统计各个客户对”可口可乐”的喜爱程度.
select sum(销售数量) 喜爱程度,购买人
from MyOrders
where 商品名称=N’可口可乐’
group by 购买人
order by 喜爱程度 desc
类型转换函数（CAST与 CONVERT）
—-类型转换—-
–Cast()
–Convert()
select ‘考试人数：’+100
select ‘考试人数：’+CONVERT (varchar(3),100)
select ‘考试人数：’+cast (100 as varchar(3))
–注意如果number是字符串类型，则一定要转为int类型，否则排序有误。
select * from mytest1001 order by CAST(number as int )desc
select * from mytest1001 order by CONVERT(int,number) asc
select GETDATE()
–convert()比cast()多了一个参数，等，查看帮助，可知。（时间转换只有CONVERT()）
select CONVERT (char(10),GETDATE(),102)
联合结果集（union）集合运算符
–联合结果集（union）集合运算符
select ‘黄林’,18,’huangling@163.com’
union all–通过union all 就可以把多个结果集联合在一起，把行联合在一起
select ‘Magi’,18,’yan@163.com’
—-必须需要有相同的列，列的数目及类型需要一样
select ‘黄林’,18,
union all–通过union all 就可以把多个结果集联合在一起，把行联合在一起
select ‘Magi’,18,’yan@163.com’
–当使用union 或union all的时候必须保证多个结果集中的列的数目一致
–并且对应的列的数据类型得一致，至少得兼容
–使用带from子句的union
select * from TblStudent
select * from TblTeacher
–根据上面得出的不一样的列,我们可以选择类型一样的列,且只能对最终结果进行排序
select tSName,tSGender,tSAge from TblStudent
union all
select tTName ,tTGender ,tTAge from TblTeacher order by tSAge desc
–union 与union all的区别
–union all在进行联合的时候不会去除重复数据
–而union在联合的时候,自动去除重复数据
select tSName ,tSGender ,tSAge from TblStudent
union all
select tSName ,tSGender ,tSAge from TblStudent
—通过union 实现底部汇总
select * from MyOrders
select
商品名称,
销售价格=(SUM(销售数量*销售价格))
from MyOrders
group by 商品名称
union all
select ‘销售总额’,SUM(销售数量*销售价格) from MyOrders
insert into 用法
–select into 用法
–1.insert into 表（列，列）select 列,列from 表where …
–上面这种写法表示要向一张已存在的表中插入一些数据，这些数据来源于表中。
–2.insert * into 表 from 表where …
–上面这种写法表示要将表的数据插入到表中，在运行这条SQL语句后，表才会被创建
–如果在执行该SQL语句前，表已存在，则报错。即：该条语句不能重复执行。
–通过select * into 这种方式创建的表与源表的表结构（包括列的个数与列的数据类型，连同列的自增长特性，也会一起创建）
–但是新创建的表中没有任何约束，包括源表中的主键约束也没有了，如果需要则要手动创建。
–select * into 主要目的是为了复制一部分数据。
–如果只想复制表结构，而不要数据，则
select * into NewOrders from MyOrders 1<>1
–更好的方法如下：
select top 0 * into NewOrders from MyOrders
–也可以将多个表的集合插入到新的表中
select tsname into NewSchool from TblStudent
union all
select ttname from TblTeacher
字符串函数
—-字符串函数—
–显示字符的个数,无论中文英文一个字符就是一个字符。
select LEN(‘我你tA’)–结果为
–表示占有的字节数,这个不是字符串函数
select DATALENGTH(‘我你tA’)–结果为
len():计算字符串长度（字符的个数）
datalength():计算字符串所占用的字节数
lower()：转小写
upper():转大写
ltrim():字符串左侧的空格去掉
rtrim():字符串右侧的空格去掉
left():截取字符串
right():截取字符串
substring():截取字符串
日期函数（在帮助中输入“日期函数”）
—-日期函数（在帮助中输入“日期函数”）
getdate():取得当前日期时间
–dateadd():在指定的日期上加一定的时间或日期新值
print dateadd(month,2,’1990-10-10′)
print dateadd(month,-2,’1990-10-10′)
dateadd(detepart,number,date)
–计算添加以后的日期。
–参数date为待计算的日期，参数number为增量，参数datepart为计算单位。
datediff(datepart,startdate,enddate) 计算俩个日期之间的差额。
–datepart为计量单位，可取值参考DateAdd.
datepart(datepart,date)
–返回一个日期的特定部分
–思考:查询出入职一年以上的员工(含一年)
select userName from Test1002 where dateadd(year,1,joinDate)<=GETDATE()
select userName from Test1002 where dateadd(day,365,joinDate)<=GETDATE()
–DateDiff函数，获取俩个日期时间的差值，根据单位不同，返回值不同。
select DATEDIFF (YEAR,’1991-10-10′,getdate())
select DATEDIFF (day,’1991-10-10′,getdate())
select DATEDIFF (HOUR,’1991-10-10′,getdate())
–统计不同年份出生的同学的人数
select
datepart(year,tSBirthday),COUNT(*)
from TblStudent group by datepart(year,tSBirthday)
–统计不同年龄的同学的人数
select
datediff(year,tSBirthday,GETDATE()),COUNT(*)
from TblStudent group by datediff(year,tSBirthday,GETDATE())
课后作业
use [Itcast2013]
select * from CallRecords
–输出所有数据中通话时间最长的条记录。
select top 5 * ,’通话时长’=DATEDIFF (SECOND,StartDateTime ,EndDateTime) from CallRecords order by ‘通话时长’ desc
–输出所有数据中拨打长途号码(对方号码以开头)的总时长、Like、SUM
select
拨打长途电话的总时长=SUM(DATEDIFF (SECOND,StartDateTime ,EndDateTime))
from CallRecords
where TelNum like ‘0%’
–输出【本月】通话总时长最多的前三个呼叫员的编号：group by count(*)
–假设今天是‘-07-31’
select
top 3
CallerNumber,本月通话总时长=SUM(DATEDIFF (SECOND,StartDateTime ,EndDateTime))
from CallRecords
where datepart(YEAR,StartDateTime) =YEAR(‘2010-07-31’)
and datepart(MONTH ,StartDateTime) =month(‘2010-07-31’)
–限制本月,本月表示表中记录的年份、月份都与当前日期的年份月份相同。才叫本月
group by CallerNumber
order by 本月通话总时长 desc
–输出本月拨打电话次数最多的前三个呼叫员的编号:group by count(*)
select top 3 CallerNumber ,
次数= COUNT(*)
from CallRecords
where datepart(YEAR,StartDateTime) =YEAR(‘2010-07-31’)
and datepart(MONTH ,StartDateTime) =month(‘2010-07-31’)
group by CallerNumber
order by 次数 desc
–每个月打电话时间最长的前三个呼叫员（**）
select * from vw where 通话时长 in
(select top 3 通话时长 from vw as vw2 where vw2.月份=vw.月份 order by vw2.通话时长 desc )
order by vw.月份,vw.通话时长 desc
