SQL培训语句

------------------------导入数据------------------------------

--1、.csv文件导入。
--a、选择数据库，右键，“任务”“导入数据”。
--b、数据源选择“平面文件源”，选择文件路径，类型改为.csv。
--c、勾选“在第一个数据行中显示列名称”,在"文本限定符"添加双引号,高级里面,videourl字段列名长度增加
--d、修改导入文件的字段名、类型
--e、修改表格名称


--2、修改（null）为NULL
update VediodataTable20160731 set imsi =null where imsi='(null)'

--3、改字段类型：ramusage、cpuusage、source、cdmasid、cdmanid、cdmabsid、cdmadbm、ecio、snr、tclass这些改为int类型
-- videoavgspeed、videopeakspeed、longitude、latitude这些改为numeric(18, 4)


select maxx,* from dbo.Sheet1001 where maxx<17



----------------------------------------------分割线---------------------------------------------------------------------
select * from Vediodata20160731
--去掉重复
--插入自增列
Alter table Vediodata20160731 add id int IDENTITY(1,1) NOT NULL
--分组得到id
select Max(id) as id,imsi,videotesttime from dbo.Vediodata20160731 group by imsi,videotesttime
--查询不重复

select * from Vediodata20160731
group by city

select
Max(ramusage) as 'MAXV'
,COUNT(ramusage) as 'COUNR'
,MIN(ramusage)as 'MINV'
,sum(ramusage) as 'SUMV'
,AVG(ramusage) as 'AVG'
,city from Vediodata20160731
group by city

select * from Vediodata20160731

select ramusage,POWER(ramusage,2),* from Vediodata20160731

select ramusage,ramusage/3,ramusage%3,1.0*ramusage/3 from Vediodata20160731

select SIN(30 * 2*3.14/360)


select top 10 imsi,meid,ramusage into Vedio from Vediodata20160731

select * from dbo.Vedio

Insert into dbo.Vedio(imsi,meid,ramusage) values('123','44',5)

delete from dbo.Vedio where imsi='460110441669269'
and ramusage=49

Alter table dbo.Vedio add id int IDENTITY(1,1) NOT NULL

select top 20 imsi,phonetype,cpuusage into Vedio2 from Vediodata20160731

select top 5 * into #t123 from dbo.Vedio

select * from #t123
drop table #t123
-----------------------------------------------------

Create Index ve_index on dbo.Vedio(id)
drop index ve_index


select * from dbo.Vedio2

Create View VE_Vide as
select T1.imsi,T1.meid,T1.ramusage,t2.phonetype from dbo.Vedio t1
inner join dbo.Vedio2 t2 on t1.imsi = t2.imsi

select * from VE_Vide


where imsi='460110441669269'
and ramusage=49

select ABS(1)

select * from
(select Max(id) as id,imsi,videotesttime from dbo.Vediodata20160731 group by imsi,videotesttime ) t1
inner join Vediodata20160731 t2 on t1.id= t2.id


--求和排序（分组统计）
select city,SUM(ramusage) as 'r',SUM(cpuusage) as 'c',1.0*SUM(ramusage)/SUM(cpuusage) as 'cp'
from dbo.Vediodata20160731 group by city
Having 1.0*SUM(ramusage)/SUM(cpuusage)>2.0
order by c Desc

--统计
select websitenam

e as '行标签'
,sum(firstbytedelay)/COUNT(firstbytedelay) as '平均值项:firstbytedelay'
,sum(pageopendelay)/COUNT(pageopendelay) as '平均值项:pageopendelay'
from dbo.T1 group by websitename

select * from websitename


UNION all
select '合计' as '行标签'
,sum(firstbytedelay)/COUNT(firstbytedelay) as '平均值项:firstbytedelay'
,sum(pageopendelay)/COUNT(pageopendelay) as '平均值项:pageopendelay'
from dbo.T1



--时间类型转换
select SUBSTRING(Replace(videotesttime_all,'/','-'),2,10)
,SUBSTRING(Replace(videotesttime_all,'/','-'),13,9)
,case when SUBSTRING(videotesttime_all,22,2)='下午' then 'pm' else 'am' end
,* from Vediodata20160731

--转换（完成）
select cast(SUBSTRING(Replace(videotesttime_all,'/','-'),2,10)
+' '+SUBSTRING(Replace(videotesttime_all,'/','-'),13,9)
+' '+case when SUBSTRING(videotesttime_all,22,2)='下午' then 'pm' else 'am' end as datetime)
,* from Vediodata20160731

SELECT cast('2016-07-31 08:48:44 pm' as datetime)

--修改了日期格式,插入一个新表
select cast(SUBSTRING(Replace(videotesttime_all,'/','-'),2,10)
+' '+SUBSTRING(Replace(videotesttime_all,'/','-'),13,9)
+' '+case when SUBSTRING(videotesttime_all,22,2)='下午' then 'pm' else 'am' end as datetime) as videotesttime_all2
,* into Vediodata from Vediodata20160731

--改成时间类型之后，可以使用内部公式改变时间
select DATEADD(DAY,-2,videotesttime_all2),* from Vediodata
select DATEADD(HH,-2,videotesttime_all2),* from Vediodata



--建立临时表
select top 10 imsi,ramusage into #t1 from Vediodata20160731

select * from #t1
Truncate table #t1
drop table #t1


--附常用语句------------------------------
select GetDATE() --时间

select LEN('abcedr') --字符串长度

select SUBSTRING ('abc12345',3,4) --截取字符串,从3开始,截取4位
select LEFT('abc12345',4) --从左边开始截取,4位
select Right('abc12345',4) --从左边开始截取,4位

select REPLACE('abc12345','1234','9999') --替换字符串

select '123'+'abc' --拼凑字符串

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

select MIN(ramusage),Max(ramusage),count(ramusage),SUM(ramusage),AVG(ramusage) from #t1

select 1.0 *11/3

--修改（null）为NULL
begin
update VediodataTable20160731 set imsi =null where imsi='(null)'
update VediodataTable20160731 set meid =null where meid='(null)'
update VediodataTable20160731 set phonetype =null where phonetype='(null)'
update VediodataTable20160731 set osversion =null where osversion='(null)'
update VediodataTable20160731 set baseband =null where baseband='(null)'
update VediodataTable20160731 set kernel =null where kernel='(null)'
update VediodataTable20160731 set innerversion =null where innerversion='(null)'
update VediodataTable20160731 set ramusage =null where ramusage='(null)'
update VediodataTable20160731 set cpuusage =null where cpuusage

='(null)'
update VediodataTable20160731 set locationdesc =null where locationdesc='(null)'
update VediodataTable20160731 set province =null where province='(null)'
update VediodataTable20160731 set city =null where city='(null)'
update VediodataTable20160731 set source =null where source='(null)'
update VediodataTable20160731 set nettype =null where nettype='(null)'
update VediodataTable20160731 set apn =null where apn='(null)'
update VediodataTable20160731 set ssid =null where ssid='(null)'
update VediodataTable20160731 set cdmasid =null where cdmasid='(null)'
update VediodataTable20160731 set cdmanid =null where cdmanid='(null)'
update VediodataTable20160731 set cdmabsid =null where cdmabsid='(null)'
update VediodataTable20160731 set cdmadbm =null where cdmadbm='(null)'
update VediodataTable20160731 set lteci =null where lteci='(null)'
update VediodataTable20160731 set ltepci =null where ltepci='(null)'
update VediodataTable20160731 set ltetac =null where ltetac='(null)'
update VediodataTable20160731 set ltersrp =null where ltersrp='(null)'
update VediodataTable20160731 set ltesinr =null where ltesinr='(null)'
update VediodataTable20160731 set innerip =null where innerip='(null)'
update VediodataTable20160731 set outerip =null where outerip='(null)'
update VediodataTable20160731 set ecio =null where ecio='(null)'
update VediodataTable20160731 set snr =null where snr='(null)'
update VediodataTable20160731 set ltersrq =null where ltersrq='(null)'
update VediodataTable20160731 set videoname =null where videoname='(null)'
update VediodataTable20160731 set videourl =null where videourl='(null)'
update VediodataTable20160731 set videoip =null where videoip='(null)'
update VediodataTable20160731 set videotesttime =null where videotesttime='(null)'
update VediodataTable20160731 set videoavgspeed =null where videoavgspeed='(null)'
update VediodataTable20160731 set videopeakspeed =null where videopeakspeed='(null)'
update VediodataTable20160731 set tclass =null where tclass='(null)'
update VediodataTable20160731 set buffercounter =null where buffercounter='(null)'
update VediodataTable20160731 set videosize =null where videosize='(null)'
update VediodataTable20160731 set videototletraffic =null where videototletraffic='(null)'
update VediodataTable20160731 set location =null where location='(null)'
update VediodataTable20160731 set longitude =null where longitude='(null)'
update VediodataTable20160731 set latitude =null where latitude='(null)'
update VediodataTable20160731 set carrieroper =null where carrieroper='(null)'
update VediodataTable20160731 set videotesttime_all =null where videotesttime_all='(null)'
end