sqlServer学习

sql

数据库

发布日期: 2020-03-10

更新日期: 2020-05-06

文章字数: 1,326

阅读时长: 6 分

阅读次数:

记录项目中的业务处理(环境：sqlserver2012、vs2017)

使用sql语句将数据转换成相应的格式

1.使用cast函数,不能转换日期类型

cast(表达式 as 数据类型)

> select CAST(PLAN_LENGTH AS FLOAT) '长度' from [dbo].[CTS_PROJECTPLAN_DETAIL]

2.使用Convert函数通用

Convert(数据类型，表达式)
> SELECT CONVERT(FLOAT ,PLAN_LENGTH) '长度' FROM [dbo].[CTS_PROJECTPLAN_DETAIL]

除法时遇到分母为0时的处理

简单的示范：

--定义变量并且设置分母为0，这样一定会出现错误
declare @a int;
declare @b int;
set @a = 10;
set @b = 0;
select @a/@b


--1.可以使用case语句进行判断操作,当分母为0时将其转换成1
select case @b when 0 then 1 else @a/@b 


--2.使用函数nullif，该函数
--nullif(express1,express2),如果express1==express2时，直接为null，将表达式2设置为0，遇到0时直接为null
declare @a int;
declare @b int;
set @a=10
set @b=5
select @a/nullif(@b,0)


--当然最后的需要并不会是null值，可以使用isnull函数进行控制
--isnull(express1,express2),如果e1为null时，返回e2
declare @a int;
declare @b int;
set @a=10
set @b=5
select isnull(@a/nullif(@b,0),1)

项目需求：

将两列数据位varchar类型的数据进行类型转换并进行除法运算

自己编写的语句是：

select

isnull(Convert(float,ziduan1)/nullif(Convert(float,字段2),0),1 ) ‘新列名’

from 表名

–简单的解释就是，两列char类型的数据进行float转换后进行除法，进行除数为0判断

查询数据库中的数据进行数据监视

要求：能够查出每周、每月、每年的工作量

每日统计:
select 日期时间=convert(char(10), 日期时间, 120), 数量=sum(数量)
from T
group by convert(char(10), 日期时间, 120)

每周统计:
select 日期时间=datepart(week, 日期时间), 数量=sum(数量)
from T
where year(日期时间)=year(getdate())
group by datepart(week, 日期时间)

每月统计:
select 日期时间=convert(char(7), 日期时间, 120), 数量=sum(数量)
from T
group by convert(char(7), 日期时间, 120)

直接进行更改，查询项目工作量的表中的workload列数据，按照周、月、日进行分组统计

select 周计划=datepart(week,[YEARMONTH] ),每周计划量=sum([WORKLOAD])
from [dbo].[CTS_PROJECTPLAN_WORKLOAD] 
where year([YEARMONTH])=year(getdate())
group by datepart(week, [YEARMONTH])

select 月计划=datepart(MONTH,[YEARMONTH] ),每周计划量=sum([WORKLOAD])
from [dbo].[CTS_PROJECTPLAN_WORKLOAD] 
where month([YEARMONTH])=month(getdate())
group by datepart(MONTH, [YEARMONTH])


select 日期时间=convert(varchar(20), [YEARMONTH], 120), 数量=sum([WORKLOAD])
from [CTS_PROJECTPLAN_WORKLOAD]
group by convert(varchar(20), [YEARMONTH], 120)

存储过程(基于sqlserver2012进行记录)

项目中要求的数据进行拼接过于多，因此使用存储过程，自己也对存储过程进行简单的学习。

存储过程的概念：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

--情况1不带参数的存储过程
create proc sp_name
as
begin 
   print 'helloworld'
end
--执行语句
exec sp_name
--删除当前的存储过程
drop proc sp_name

--2.带参数的存储过程
create proc sp_name
    @name nvarchar(50),
    @author nvarchar(50)
as 
begin
    select * from Books where title = @name and Author = @author
end

--执行带有参数的存储过程
exec sp_name 'C# Primer 习题解答（第5版）','蒋爱军，李师贤，梅晓勇  著'
exec sp_name @name = 'C# Primer 习题解答（第5版）' , @author = '蒋爱军，李师贤，梅晓勇  著'

--3.带有默认值的存储过程
create proc sp_name
    @patt nvarchar(50) = '1046002111',
    @repla nvarchar(50)
as 
begin
    select * from Articel_Words where [WordPattern] = @patt and [ReplaceWord] = @repla
end
--对参数进行更改
exec sp_name @patt = 'newvalue'

--4.带有输出参数的存储过程
create proc usp_output
@bookname nvarchar(50),
@recordCount int output --关键字代表输出参数
as
begin
    select * from hero where bookname=@bookname
    --把查询的记录条数赋值给变量@recordCount
    set @recordCount = (select count(*) from hero where bookname=@bookname)
end 
--调用带有参数的存储过程，需要定义变量，并把变量传递
declare @num int
exec usp_output @bookname='书剑恩仇录',@recordCount=@num output
select @num as 记录条数

--5.存储过程实现分页
create proc usp_fenye
@pagesize int=3, --每页记录的条数
@index int=1,    --当前查看第几页的内容
@recordcount int output, --总的条数
@pagecount int output --总的页数
as
begin
    --分页
    select
        t.id,
        t.bookname,
        t.hero
    from (select *,rn=row_number() over(order by id asc) from hero) as t
    where t.rn between (@index-1) * @pagesize + 1 and @pagesize * @index
    --计算总的条数
    set @recordcount =( select count(*) from hero)
    --计算总的页数
    set @pagecount=ceiling(@recordcount * 1.0 / @pagesize)  --ceiling向上取整
end
--执行存储过程
declare @tiaoshu int
declare @yeshu int
exec usp_fenye @pagesize=5,@index =3,@recordcount=@tiaoshu output,@pagecount=@yeshu output
select @tiaoshu as 总的条数
select @yeshu as 总的页数