SQL基础及高级操作-优快云博客

分类： SQL

一、SQL基础语法

选择：select * from table where 范围

插入：insert into table（field1，field2）values（value1，value2）

删除：delete from table where 范围

更新：update table set field1=value1 where 范围

查找：select * from table1 where field1 like '%value1%'

排序：select *from table order by field1，field2[desc]

总数：select count as totalcount from table

求和：select sum（field）as sumvalue from table

平均：select avg（field）as avgvalue from table

最大：select max（field）as maxvalue from table

最小：select min（field）as minvalue from table

1.提取数据（提取、增加、删除、修改、查询）

SELECT

RTRIM ( HuoPinPinPai_b.PinPaiBianHao)as PinPaiBianHao,

RTRIM ( HuoPinLeiXing_b.HuoPinLeiXingBianHao)as HuoPinLeiXingBianHao,

RTRIM (KuCunShu )as KuCunShu

FROM

HuoPinDangAn_b INNER JOIN

HuoPinPinPai_b ON HuoPinDangAn_b.PinPaiBianHao = HuoPinPinPai_b.PinPaiBianHao INNER JOIN

HuoPinLeiXing_b ON HuoPinDangAn_b.HuoPinLeiXingBianHao =HuoPinLeiXing_b.HuoPinLeiXingBianHao

where HuoPinLeiXing_b.HuoPinLeiXingBianHao=@hplx1 and HuoPinPinPai_b.PinPaiBianHao=@ppbh1

order by HuoPinLeiXing_b.CangKuBianHao,HuoPinDangAn_b.HuoPinLeiXingBianHao

2.新增

insert into HuoPinDangAn_b

(HuoPinDangAn_b.HuoPinLeiXingBianHao,

HuoPinDangAn_b.HuoPinMingCheng)

values (@hplx1,@hpmc1)

3.删除

delete from HuoPinDangAn_b

where HuoPinBianHao =@hpbh1

4.修改

update HuoPinDangAn_b

set

HuoPinMingCheng=@hpmc1 ,

HuoPinJianPin=@hpjp1

where HuoPinBianHao=@hpbh1

5.查询

SELECT

RTRIM ( HuoPinPinPai_b.PinPaiBianHao)as PinPaiBianHao,

RTRIM ( HuoPinLeiXing_b.HuoPinLeiXingBianHao)as HuoPinLeiXingBianHao

RTRIM (KuCunShu )as KuCunShu

FROM

HuoPinDangAn_b INNER JOIN

HuoPinPinPai_b ON HuoPinDangAn_b.PinPaiBianHao = HuoPinPinPai_b.PinPaiBianHao INNER JOIN

HuoPinLeiXing_b ON HuoPinDangAn_b.HuoPinLeiXingBianHao = HuoPinLeiXing_b.HuoPinLeiXingBianHao

where HuoPinDangAn_b.HuoPinMingCheng like '%'+@hpmc1+'%'

6.判断新增数据的出/入库，然后改变库存表中的库存数

insert into HuoPinChuRu_b (YuanShiDanHao, DanJuHao)

Values ( @YuanShiDanHao, @DanJuHao )

if (@ChuRuLeiXingBianHao=8001)--入库

begin

update HuoPinDangAn_b

set HuoPinDangAn_b.KuCunShu=HuoPinDangAn_b.KuCunShu+@HuoPinShuLiang

where HuoPinBianHao =@HuoPinBianHao

end

else --出库

begin

update HuoPinDangAn_b

set HuoPinDangAn_b.KuCunShu=HuoPinDangAn_b.KuCunShu-@HuoPinShuLiang

where HuoPinBianHao =@HuoPinBianHao

end

二、在SQL中实现循环每一行做一定的操作

1.While语句：

select Department_No as departmentNo,ROW_NUMBER(）

OVER(ORDER BY Department_No) AS rowNumber into

#depTemp --建立临时表
from departments;

declare @max int --用来获得最大的rowNumber5 B,

select @max=max(rownumber)

from #depTemp

declare @rowNo int

    set @rowNo=1
    while @rowNo<=@max   --用来对每一个rowNumber来进行循环操作
    begin                --此处对每一行要进行的操作的代码
    set @rowNo=@rowNo+1

end

drop table #depTemp --清除临时表

2.游标

declare @id int,@name varchar(20); -- 定义游标

    declare cur cursor fast_forward for
    select id,name from a;
    open cur;                           -- 打开游标
    fetch next from cur into @id,@name;       -- 提取游标数据
    while @@fetch_status=0
    begin

    --此处对每一行要进行的操作的代码
    fetch next from cur into @id,@name;       -- 提取游标数据
    end
    close cur;                                -- 关闭游标
    deallocate cur;                           -- 释放游标

三、其他常用语句

1.数据库中的四舍五入
  SELECT  ROUND(JiangJinJinE , 1) AS JinE --精确到小数点后一位

from JiangJinLeiXing_b

2.提取某个表中最后一条数据

   select top 1 JiaRenXinXi_b.*,

      ZhiWuXinXi_b.ZhiWuMingChen

   FROM        JiaRenXinXi_b INNER JOIN

                ZhiWuXinXi_b ON JiaRenXinXi_b.ZhiWuBianHao = ZhiWuXinXi_b.ZhiWuBianHao

order by ZiDongBianHao desc

3.从数据库中得到当前时间
select DATENAME(YEAR,GETDATE())+'-'+DATENAME(MONTH ,GETDATE())+'-'+DATENAME (DAY,GETDATE ())+' '+DATENAME (HOUR ,GETDATE ())+':'+DATENAME (MINUTE ,GETDATE ())+':'+DATENAME (SECOND ,GETDATE ())+':'+DATENAME (MILLISECOND ,GETDATE ())

4.SQL 如何得到本月第一个星期天的日期
declare @NowWeekDay nvarchar(20)

set @NowWeekDay=( select datename(dw,datename(year,getdate())+datename(month,getdate())+'01'))select case @NowWeekDay

when '星期日' then datename(year,getdate())+datename(month,getdate())+'01'

when '星期一' then datename(year,getdate())+datename(month,getdate())+'07'

when '星期二' then datename(year,getdate())+datename(month,getdate())+'06'

when '星期三' then datename(year,getdate())+datename(month,getdate())+'05'

when '星期四' then datename(year,getdate())+datename(month,getdate())+'04'

when '星期五' then datename(year,getdate())+datename(month,getdate())+'03'

when '星期六' then datename(year,getdate())+datename(month,getdate())+'02'

end

5.获取某一个表的所有字段

（1）select name

from syscolumns