小鱼尼莫


专注于前端(JavaScript、jQuery、html+css)

博主做程序同时,还经营了一家手工发饰淘宝店,纯手工精致发饰发夹, 程序猿送女友必备神器!需要的可以支持一下哦!

posts - 95,comments - 33,views - 53万

      

  重点介绍数字函数、字符串函数、日期函数、类型转换函数cast /convert、表连接join等基础,并详细讲述一个综合练习,以巩固所学到的sql知识。

  例子或代码中用到的table,若涉及到汇总(二)的表,如有需要请查阅汇总(二)!

一. 数字函数

  1.1 代码

复制代码
--绝对值:3.1415,3.1415
select abs(3.1415);    
select abs(-3.1415); --舍入到最大整数:4,4,-3,-3 select ceiling(3.2); select ceiling(3.7); select ceiling(-3.2); select ceiling(-3.7); --舍入到最小整数:3,3,-4,-4 select floor(3.2); select floor(3.7); select floor(-3.2); select floor(-3.7); --四舍五入到“离半径最近的数”、后面的3即保留三位小数:3.1410,3.1420,-3.1410,-3.1420 select round(3.1413,3);
select round(3.1418,3); select round(-3.1413,3); select round(-3.1418,3);
复制代码

 

------------------------------------------------

  博主经营一家发饰淘宝店,都是纯手工制作哦,开业冲钻,只为信誉!需要的亲们可以光顾一下!谢谢大家的支持!
店名:
  小鱼尼莫手工饰品店
经营:
  发饰、头花、发夹、耳环等(手工制作)
网店:
  http://shop117066935.taobao.com/

  ---------------------------------------------------------------------

继续正题... 

  

 

二. 字符串函数

  2.1 代码

select len('abcd');                --字符串长度:4
select lower('abcdEMN');            --转小写
select upper('abcdEMN');           --转大写
select ltrim('   ss   ');         --去左空格
select rtrim('   ss   ');           --去右空格
select rtrim(ltrim('   ss   '));   --去左右空格
select substring('abcdef',3,3);     --结果:cde;格式:substring(string,startIndex,length)

  2.2 分析

  substring(string,startIndex,length),startIndex为子字符串在住字符串中的起始位置,length为子字符串的最大长度

三.日期函数

  3.1代码

复制代码
--当前时间
select getdate();
--分别获取年/月/日
select DatePart(year,getdate()),DatePart(month,getdate()),DatePart(day,getdate());
--在当前日期运算。格式:DateAdd(datePart,number,date);注:可用hh,dd,mm,yy表示hour/day/month/year select DateAdd(hour,3,getdate()); --加三小时hour select DateAdd(day,3,getdate());   --三天day select DateAdd(month,3,getdate());  --三月month select DateAdd(year,3,getdate());   --三年year --日期差值 格式:DateDiff(datePart,startDate,endDate); select DateDiff(hh,getdate(),DateAdd(dd,3,getdate()));
复制代码

  3.2 分析

  参考下面的例子

1)员工入职年限表
select FName, FInDate as 入职日期,DateDiff(year,FInDate,getdate()) as 入职年限 from T_Employee;
2)入职n年的人数统计表(FInDate为入职时间)
select DateDiff(year,FInDate,getdate()) as 入职年限, count(*) as 入职人员数 from T_Employee
group by DateDiff(year,FInDate,getdate())
3)每年入职人数统计表
select DatePart(year,FInDate),count(*) from T_Employee
group by DatePart(year,FInDate)

四. 类型转换

  4.1代码

复制代码
1) 类型转换两种方式:
select cast('123'as int);
select cast('2008-08-08' as datetime);
select convert(datetime,'2009-09-09');
select convert(varchar(50),123);
2)注意:
select '123'+1;                     --不好!有时会报错
select cast('123' as int)+1;        --最好转化为同一类型再运算
select DatePart(year,'2008-8-8')                      --不好!有时会报错
select DatePart(year,cast('2008-8-8' as datetime))    --最好转化为同一类型再运算
复制代码

4.2分析

  注意使用cast、convert时候,需要考虑转换前后类型是否可以匹配,如要select cast('aaa'as int)是肯定会报错的

  在"2)"中说到的情况还是应该注意,有些时候会出现错误!!!

五.case语句

  5.1代码:

复制代码
--case函数:实现离散值的判断
select FName,
(
case FLevel           --加上'判断值'
when 1 then'普通客户'
when 2 then'会员'
when 3 then 'VIP'
else '未知客户关系'
end
)as 客户类型
from T_Customer
--case函数:实现值域的判断
select FName,FSalary,
(
case                   --不写'判断值'
when FSalary<2000 then '低收入'
when FSalary>=2000 and FSalary <5000 then '中收入'
else '高收入'
end
)as 收入水平
from T_Employee
复制代码

  5.2 分析

  1).上面方式分别针对离散值判断、值域判断;继续参考下面的小例子

  2).有一张表记录了比赛成绩,创建代码已经给出(大家可以参考),根据该表,完成以下题目

--创建T_Scores
create table T_Scores(FName nvarchar(50) ,score nvarchar(50))
insert into T_Scores(FName,score)values('Tom',''); 
insert into T_Scores(FName,score)values('Tom','');
insert into T_Scores(FName,score)values('Lucy','');
insert into T_Scores(FName,score)values('Lucy','');
insert into T_Scores(FName,score)values('Jerry','');

  eg_1.输出新表,如果胜为1,负为0 

复制代码
select FName,
(
case score
when N'' then 1
else 0
end
)as '',
(
case score
when N'' then 1
else 0
end
)as ''
from T_Scores
复制代码

  eg_2.输出格式(统计胜负)

  FName 胜  负
  Tom  1    1
  Lucy   0    2
  Jerry 1    0

复制代码
select FName,
sum(
    case score
    when N'' then 1
    else 0
    end
)as '',
sum(
    case score
    when N'' then 1
    else 0
    end
)as ''
from T_Scores
group by FName
复制代码

  3).有一张表记录了正负值(正表示收入,负表示指出),创建代码已经给出(大家可以参考),根据该表,完成以下题目

--创建表 T_Order
create table T_Order(FNumber varchar(50) not null,FAmount int not null);
insert into T_Order (FNumber,FAmount)values('Rk1',10);
insert into T_Order (FNumber,FAmount)values('Rk2',20);
insert into T_Order (FNumber,FAmount)values('Rk3',-30);
insert into T_Order (FNumber,FAmount)values('Rk4',-10);

  统计出收支情况,字段为 单号  收入  支出

复制代码
select FNumber as 单号,
(
case 
when FAmount>0 then FAmount
else 0
end
)as 收入,
(
case 
when FAmount<0 then abs(FAmount)
else 0
end
)as 支出
from T_Order
复制代码

六. 表连接join...on

  6.1 代码:

--常规定义:
select T_Orders.BillNo,T_Customers.name
from T_Orders join T_Customers on T_Orders.CustomerId=T_Customers.Id;
--可以定义如此,书写更简单:
select o.BillNo as 订单号,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id;

6.2分析

  参考下方两个定义的表格,实现下方要求的操作。

复制代码
--T_Customers表创建
create table T_Customers(
    Id int not null,
    Name nvarchar(50) collate Chinese_PRC_CI_AS null,
    Age int null
);
insert into T_Customers(Id,Name,Age)values(1,N'tom',10);
insert into T_Customers(Id,Name,Age)values(2,N'jerry',15);
insert into T_Customers(Id,Name,Age)values(3,N'john',22);
insert into T_Customers(Id,Name,Age)values(4,N'lily',18);
insert into T_Customers(Id,Name,Age)values(5,N'lucy',18);
--T_Orders表创建
create table T_Orders(
    Id int not null,
    BillNo nvarchar(50) collate Chinese_PRC_CI_AS null,
    CustomerId int null   --看做T_Customers表的外键(虽然实际关系并未建立)!
);
insert into T_Orders(Id,BillNo,CustomerId)values(1,N'001',1);
insert into T_Orders(Id,BillNo,CustomerId)values(2,N'002',1);
insert into T_Orders(Id,BillNo,CustomerId)values(3,N'003',3);
insert into T_Orders(Id,BillNo,CustomerId)values(4,N'004',2);
insert into T_Orders(Id,BillNo,CustomerId)values(5,N'005',2);
insert into T_Orders(Id,BillNo,CustomerId)values(6,N'006',5);
insert into T_Orders(Id,BillNo,CustomerId)values(7,N'007',4);
insert into T_Orders(Id,BillNo,CustomerId)values(8,N'008',5);
复制代码

   内容示意图

  要求及答案:

复制代码
--显示所有订单号对应的姓名、年龄:
select o.BillNo as 订单号,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id;
--显示15岁以上顾客对应的订单号、年龄、年龄:
select o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id
where c.Age>15
--显示年龄大于‘平均年龄’的顾客的购买订单: (涉及子查询)
select o.BillNo,c.Name,c.Age
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语句来完成。

要求:

  1)输出所有数据中通话时间最长的5条记录。(分析:需要用到orderby/ datediff。)

--所有通话时长(第一步)
select *, DateDiff(second,StartDateTime,EndDateTime) as '通话时长'from T_Call;
--通话时间最长的5条记录(第二步)
select top 5* from T_Call
order by DateDiff(second,StartDateTime,EndDateTime) Desc;

  2)输出所有数据中拨打长途号码(以0开头)的总时长(分析:需要用到like/ datediff /sum。)

--获取长途号码(第一步)
select * from T_Call where TelNum like '0%';
--长途总时长:
select'长途总时长:', sum(DateDiff(second,StartDateTime,EndDateTime))
from T_Call
where TelNum like '0%';

  3)输出本月通话时长最多的前三位呼叫员的编号(分析:datediff / DatePart / sum/order by)

复制代码
****假定当前时间为'2010-7-20'
--分辨是否为当月,0为是,其他为否(第一步)
select  CallerNumber, TelNum,DateDiff(month,StartDateTime,convert(datetime,'2010-7-20')) as '当前月'
from T_Call
--取当月的数据(第二步)
select  * from T_Call
where DatePart(month,StartDateTime)=DatePart(month,'2010-7-20')
--本月通话总时长最多的前3个呼叫员的编号(第三步)
select top 3 CallerNumber from T_Call where DatePart(month,StartDateTime)=DatePart(month,'2010-7-20') group by CallerNumber order by sum(DateDiff(second,StartDateTime,EndDateTime)) Desc --按照总量排序
备注:当月判断也可使用 where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
复制代码

  4)输出本月拨打电话次数最多的前三位呼叫员的编号。(分析:group by/count/ order by)

复制代码
--每个呼叫员拨打电话次数(第一步)
select CallerNumber, count(*)from T_Call
group by CallerNumber
--当月(第二步)
select CallerNumber, count(*)from T_Call
where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
group by CallerNumber
--本月拨打次数最多的前3个呼叫员的编号(第三步)
select top 3 CallerNumber, count(*)as '拨打次数'from T_Call
where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
group by CallerNumber
order by count(*) Desc
复制代码

  5)输出所有数据的拨号流水,并且在最后一行添加总呼叫时长,即

  呼叫员编号  对方号码     通话时长
   ...     ...     ...
   汇总  市内号码总时长 长途号码总时长

复制代码
--通话时长(第一步)
select Id,CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)as '通话时长(s)'
from T_Call
--长途判断(第二步)
select (
case
when TelNum like '0%' then 0
else 1
end
)
from T_Call
--呼叫时长分析(第三步)
select N'汇总',(
case 
when TelNum not like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end
) as '市话',(
case 
when TelNum like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end
) as '长途'
from T_Call
--呼叫员工编号/对方号码/通话时长 union all 通话时长(市话/长途)(第四步)
select CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime) as '通话时长'
from T_Call
union all
--呼叫时长统计
select N'汇总',
convert(varchar(50),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_Call

 

店名:
  小鱼尼莫手工饰品店
经营:
  发饰、头花、发夹、耳环等(手工制作)
网店:
  http://shop117066935.taobao.com/

 


复制代码
posted on   qiongmiaoer  阅读(2505)  评论(9编辑  收藏  举报
编辑推荐:
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
阅读排行:
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· PPT革命!DeepSeek+Kimi=N小时工作5分钟完成?
· What?废柴, 还在本地部署DeepSeek吗?Are you kidding?
· 赶AI大潮:在VSCode中使用DeepSeek及近百种模型的极简方法
· DeepSeek企业级部署实战指南:从服务器选型到Dify私有化落地
< 2012年6月 >
27 28 29 30 31 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
1 2 3 4 5 6 7

点击右上角即可分享
微信分享提示