SQL语句

---分组-------


--分组的目的：对数据进行汇总，统计.  与聚合函数一块使用。


select COUNT(*) from dbo.TblStudent --对表进行分组了，作为一组。


select * from TblStudent


select tSGender, COUNT(*) from TblStudent group by tSGender--先对学生表中的数据按照性别分组，分组以后再执行Count(*)


--统计每个班级有多少人?


select tSClassId,COUNT(*) as 人数 from TblStudent group by tSClassId


select tSName,tSClassId,COUNT(*) as 人数 from TblStudent group by tSClassId--显示的内容必须包含在分组中。（该语句错误）


select tSName, COUNT(*)as 总人数 from TblStudent--count(*),聚合函数返回一个值，无法与tsName对应.(错误!!)


select count(tSName), COUNT(*)as 总人数 from TblStudent


---在select的后面不能跟非分组列.


--1.请从学生表中查询出每个班的班级Id和班级人数：（见备注1）


select tSClassId,count(*) as 人数 from TblStudent group by tSClassId


--2 .请从学生表中查询出每个班的班级Id和班级中男同学的人数： （见备注2）
select --4
     tSClassId,count(*) as 人数 from TblStudent ---1
    where tSGender='男' ---2，由于where条件比group by 现执行，那么就是先过滤条件，然后再进行分组.
    group by tSClassId--3
    
    ----------------------------Having-----------------------
    ---having：表示对组进行过滤，符合having指定条件的组将显示，否则不显示。having是在group by 后执行，并且只能与group by 连用.
    
  --3.  统计出班级人数大于10人的班级，显示出班级编号。(先分组，后统计)
  
  select tSClassId,count(*) as 人数  from TblStudent group by tSClassId having count(*)>10
  
  --where与having区别: where：先过滤后分组，并且该关键字可以任意结构 。having,是在分组之后，再进行过滤，并且只能用于分组结构中。where后面不能跟聚合函数，但是having可以。
  
  select count(*) from TblStudent where count(*)>20(错误)
  
   select tSClassId,count(*) as 人数  from TblStudent group by tSClassId having 
   tSGender='男'--(错误)
   
   
 select * from TblStudent
  


select tSGender, COUNT(*) AS 人数 from TblStudent group by tSClassId(错误)




select tSClassId,count(*)from TblStudent  where TsGender='男' group by tSClassId having count(*)>2
----------------------练习----------------------
--1.热销售商品排名表，【即按照每种商品的总销售数量排序】。
select 商品名称,SUM(销售数量) as 销售数量 from MyOrders  GROUP BY 商品名称  order by 销售数量 desc


--2.请统计销售总价超过3000元的商品名称和销售总价,并按销售总价降序排序。
select 商品名称, SUM(销售价格*销售数量)as 销售总价 from MyOrders  GROUP by 商品名称  having SUM(销售价格*销售数量)>3000  order by 销售总价  desc--注意:having不能再跟别名，因为having在select之前执行.


--3.统计各个客户对"可口可乐"的喜爱度(既统计每个购买人对“可口可乐”的购买量)


select 购买人, sum(销售数量) from MyOrders  where 商品名称='可口可乐'  group by 购买人 


--select 购买人 from dbo.MyOrders where 商品名称='可口可乐'
select * from dbo.MyOrders
--------------------------转换函数-------------------------------
select * from MyOrders


select 商品名称+100 from MyOrders
select 商品名称+'100' from MyOrders


select 商品名称+cast(100 as nvarchar(10)) from MyOrders


select 商品名称+convert(nvarchar(10),100) from MyOrders


select * from dbo.ColumnDataTypeConvert


select * from dbo.ColumnDataTypeConvert order by CAST(bid as int) desc
----对日期进行格式转换


select CONVERT( nvarchar(20), GETDATE(),120)


---------------------联合结果集union--------------------------


union:--将多个结果集组成一个结果集,但是union会执行去除重复操作


union all(推荐)--将多个结果集组成一个结果集,但是union all 不会执行取出重复操作
--union 会执行一次去除重复的操作，性能上相对于union all低


--要求：进行组合的结果集必须列的个数相同，而且每个列的类型一致或者是兼容.


 select * from  dbo.TblStudent


   select * from dbo.TblTeacher
   
   select  tsName, tsGender  from TblStudent union all
   select  ttName,tTGender from TblTeacher
   
   select * from dbo.TblScore
   
   select 最高分=MAX(tEnglish),最低分=MIN(tMath),平均分=AVG(tMath) from TblScore
   
   select 说明='最高分',分数=Max(tEnglish) from TblScore union all
   select 说明='最低分',分数=MIN(tMath) from TblScore union all
   select 说明='平均分',分数=avg(tMath) from TblScore
   ---------------------------向表中插入多条记录------------------------
   select * from dbo.Contacts
   
   --将多个结果集组合成一个结果集，然后插入到数据表中。
   insert into Contacts(contactName, cellPhone, email, groupId) 
   select '张小三','88888811111','[e-mail]',4 union all
   select '张小四','88888811111','[e-mail]',4 union all
   select '张小五','88888811111','[e-mail]',4
   ----------------批量插入--------------
   select * from Myabc
   
   select * into Myabc from Contacts--将Contacts表中的数据插入到Myabc，如果Myabc表没有，那么会创建一个。那么Myabc表与Contacts结构以及存储的数据一致。如果Myabc表已经存在了，那么在执行上面的语句出错.
   
   select * into Myabcd from Contacts where 1<>1--只是将Contacts的结构复制到Myabcd
   
   select * from Myabcd
   drop table Myabcd
   
   select top 0 * into Myabcd from Contacts--只是将Contacts的结构复制到Myabcd
   
   -------------------批量插入3-------------------------------------
   select * from dbo.TblStudent
   
   insert into Contacts(contactName, cellPhone, email, groupId) select tsName,tSPhone,'[e-mail]',tSClassId from dbo.TblStudent where  tSGender='男'--注意：查询select后面的跟的字段一定要与insert 后面跟的字段个数一致，类型一致。
   
   select * from Contacts
   
   -----------------字符串函数----------------------------
   
   select LEN('sadfjlkdsaf中国')--字符长度
   select datalength('sadfjlkdsaf中国')--字节数.
   
  select LOWER('Aaa') --转成小写字母
  
  select UPPER ('bbb') --大写字母
   
  select LTRIM('    SADFDSAFDSA    ')
  select LTRIM(RTRIM ('             bbb            '))--去掉两测空格。
sELECT LEFT('abcdefg',2) 

select SUBSTRING('abcdef111',2,3) 
   
   ----------------日期函数------------
   select  DATEADD(MONTH,12,'2012-3-11')
   
   select DATEDIFF(DAY,'2012-3-11','2014/3/11')
    
    --假设 当前时间 1967年 7月31日
   select * from  dbo.Employees
   
   select * from Employees where DATEDIFF(year,BirthDate,'1967-7-31')>5
   
  select DATEPART(month,getdate())
   
--统计员工的生日月份个数
select 月份=DATEPART(month,BirthDate),count(*) from Employees group by DATEPART(month,BirthDate)


 select year(getdate())
 select month(getdate())
  select day(getdate())
  ----------------------------练习-------------------------------
  select * from CallRecords


---假设2010-7-31日


--输出所有数据中通话时间最长的5条记录。orderby datediff
select top 5 *,DATEDIFF(SECOND,StartDateTime,EndDateTime) AS 通话秒数 from CallRecords order by 通话秒数 desc


--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select sum(datediff(SECOND,StartDateTime,EndDateTime))as 总时长 from CallRecords where TelNum like'0%'




--输出本月通话总时长最多的前三个呼叫员的编号。
select top 3  CallerNumber  from CallRecords where datepart(month,StartDateTime)=month('2010-7-31')   group by CallerNumber   order by sum(Datediff(second,StartDateTime,EndDateTime))  desc


--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)


select top 3 CallerNumber,count(*) as 拨打次数 from CallRecords where datediff(month,StartDateTime,'2010-7-31')=0 group by CallerNumber order by 拨打次数 desc 


select * from dbo.TblScore