第17课-数据库开发及ado.net 聚合函数,模糊查询like,通配符.空值处理.order by排序.分组group by-having.类型转换-cast,Convert.union all; Select 列 into 新表;字符串函数;日期函数
第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
