我们以电信的收费系统为例的SQL数据库查询:
数据库biao1 里面有几个表:call、 call-history
1、-------从biao1的数据库里查询表call的全部信息-------
use biao1
select * from call
2、将查询的列名翻译为你知道的汉语
select id '编号',starttime '开始时间',endtime '结束时间',phonenumber '电话号码',calltype '电话类型' from call
3、查询某一电话号码出现的总数
select COUNT(*)from call where phonenumber=13001013336
4、查询在某时间段内所有电话号码包含“333”的长途,并按开始时间降序排列
select * from call where phonenumber like '%333%' and starttime between '2010-08-01 00:00:00' and '2010-08-05 23:23:23' and calltype=1
order by starttime DESC
5、查询某一电话号码在某时间段内的数据
select * from call where starttime>'2010-08-01 00:00:00' and starttime<='2010-08-06 00:00:00' and phonenumber=13001013336
6、查询某一号码的市话费用总和
select sum(DATEDIFF(mi,starttime,endtime))*0.2 as '市话费'
from call where calltype=0 and phonenumber=13001013336
7、查找某电话的长途通话中最大通话时间是多少
select max(DATEDIFF(mi,starttime,endtime))as '长途最大通话时间'
from call where calltype=1 and phonenumber=13001013336
8、查询某电话所有市话里通话最短的时间
select min(DATEDIFF(mi,starttime,endtime))as '市话最小通话时间'
from call where calltype=0 and phonenumber=13001013336
9、查询某电话所有市话的平均话费
select '用户13001013336的平均市话费为:' + cast((avg(DATEDIFF(mi,starttime,endtime))*0.2)as varchar(10))as '电话费'
from call where calltype=0 and phonenumber=13001013336
10、从表call和表call-history中查找电话号码相同的,且符合某些条件的数据
select * from call INNER JOIN [call-history] ON CALL.phonenumber=[call-history].phonenumber
where call.calltype=0 and [call-history].calltype=1 and call.id<100010 and [call-history].id>100015
11、把所有数据复制到表a 中
select * into a from call
12、把查询到的结果写入到表b中
select call.phonenumber into b from call
where starttime between '2010-08-01 00:00:00' and '2010-08-05 23:23:23' and calltype=0
转载于:https://blog.51cto.com/fuming/428261