SQL知识点整合

一.SQL存储过程使用

----重命名存储过程-S--
--sp_rename newProc,reNewProc
----重命名存储过程-E--

----删除存储过程-S--
--drop newProc1,newProc2 --删除多个存储过程
----删除存储过程-E--

----自定义存储过程3-S--

--Create Proc newProc
--@testName varchar(30) output
--as
--begin
--    select @testName=MAX(UserName) from T_User
--end
------调用存储过程
--declare @MaxName varchar(30)
--exec newProc @MaxName output
----exec newProc 'admin'
----自定义存储过程3-E--

----自定义存储过程2-S--
--Create Proc newProc
--@testName varchar(30)
--as
--begin
--    select * from T_User where UserName=@testName
--end
----调用存储过程
--exec newProc 'admin'
----自定义存储过程2-E--

----自定义存储过程1-S--
--Create Proc newProc
--@testVarA int,
--@testVarB int,
--@testSum int Output
--as
--begin
--    set @testSum=@testVarA+@testVarB
--end
----调用存储过程
--declare @testA int
--set @testA=0
--exec newProc 600,800,@testA output
--print @testA
----自定义存储过程1-E--

-----------系统存储过程-S-----------
----使用SQL语句改变数据库名称-S--
----修改数据库
--exec sp_renamedb dbName,daNewName
----修改数据库对象
--use D_Platforms
--exec sp_rename 'table1','newTable1'
----使用SQL语句改变数据库名称-E--

----使用SQL语句附加数据库-S--
--exec sp_attach_db @dbname='数据库名称',@filename1='文件路径'
----使用SQL语句附加数据库-E--

-----------系统存储过程-E-----------

二.SQL函数使用

----自定义函数使用-S--
--create Function test_functionA(@test_var int) returns int    --returns定义返回值类型
--as
--begin
--declare @sum int
--set @sum=@test_var*8
--return @sum
--end
    --修改函数只需将create→alter
--print dbo.test_functionA(6000)    --使用自定义函数

----自定义函数使用-E--


----日期函数使用-S--
--declare @test_Var varchar(100)
--set @test_Var=GETDATE()
--print '当前系统长时间:'+@test_Var
--set @test_Var=CONVERT(varchar(30),YEAR(GETDATE()))+CONVERT(varchar(30),MONTH(GETDATE()))+CONVERT(varchar(30),DAY(GETDATE()))
--print  '当前系统日期:'+@test_Var
--set @test_Var=DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE())    --DATENAME函数,获取时间
--print '当前系统短时间:'+@test_Var
--set @test_Var=DATEPART(HOUR,GETDATE())    --DATEPART函数,获取时间
--print 'DATEPART小时:'+@test_Var
--set @test_Var=DATEADD(DAY,10,GETDATE())    --DATEADD函数,datetime类型向相加
--print 'DATEADD加10天后长时间:'+@test_Var
----日期函数使用-E--


----转换函数使用-S--
--declare @test_Var int
--set @test_Var=1000
--print 'Yes.'+CONVERT(varchar(30),@test_Var)    --参数1:系统指定的类型 参数2:需转换的参数
----转换函数使用-E--


----重复,反转,替换,空格函数使用-S--
--declare @test_String1 varchar(60),@test_String2 varchar(60)
--set @test_String1='hello'
--set @test_String2=REPLICATE(@test_String1,5)    --重复
--print 'REPLICATE输出结果为:'+@test_String2    
--set @test_String2=REVERSE(@test_String1)    --反转
--print 'REVERSE输出结果为:'+@test_String2
--set @test_String2=REPLACE(@test_String1,'l','a')    --取代
--print 'REPLACE输出结果为:'+@test_String2
--set @test_String2=@test_String1+space(5)+'空格后的内容'    --空格
--print 'space输出结果为:'+@test_String2
--set @test_String2=STUFF(@test_String1,2,3,'替换内容')    --指定字符串长度替换
--print 'STUFF输出结果为:'+@test_String2
----重复,反转,替换,空格函数使用-E--


----去除尾部空格,截取函数使用-S--
--declare @test_String1 varchar(60),@test_String2 varchar(60)
--set @test_String1='hello world!     '
--set @test_String1=RTRIM(@test_String1)
--print '字符串为:'+@test_String1
--set @test_String2=LEFT(@test_String1,5)
--print 'LEFT截取后字符串为:'+@test_String2
--set @test_String2=RIGHT(@test_String1,5)
--print 'RIGHT截取后字符串为:'+@test_String2
--set @test_String2=SUBSTRING(@test_String1,2,6)
--print 'SUBSTRING截取后字符串为:'+@test_String2
----去除尾部空格函数使用-E--


----字符串长度函数使用-S--
--declare @test_Var int,@test_String varchar(60)
--set @test_String='Money:'
--set @test_Var=LEN(@test_String)
--print '字符串长度为:'+STR(@test_Var,1,0)
----字符串长度函数使用-E--


----数值转换字符函数使用-S--
--declare @test_Var float,@test_String varchar(60)
--set @test_Var=1000.2355
--set @test_String='Money:'
--print @test_String+STR(@test_Var,7,2) --参数1:要转换数值 参数2:长度 参数3:小数精确位
----数值转换字符函数使用-E--


--大小写函数使用-S--
declare @test_String1 varchar(30),@test_String2 varchar(30),@test_Result varchar(30)
set @test_String1='upper'
set @test_Result=UPPER(@test_String1)
print @test_Result
set @test_String2='Lower'
set @test_Result=LOWER(@test_String2)
print @test_Result
--大小写函数使用-E--


----ASCII码函数使用-S--
--declare @test_String1 char(60),@test_String2 varchar(60)
--set @test_String1='H'
--select 'H的ASCII码是:',ASCII('H')
--select 'SCII码72转字符是:',CHAR(72)
----ASCII码函数使用-E--


----正负判断函数使用-S--
--declare @test_Var1 float,@test_Var2 float,@test_String varchar(30)
--set @test_Var1=RAND()
--print @test_Var1
--set @test_Var2=SIGN(@test_Var1)
--set @test_String=
--    case
--        when @test_Var2=1 then '正值'
--        else '负值'
--    end
--print @test_String
----正负判断函数使用-E--


----取整函数使用-S--
--declare @test_Var float,@test_Value1 float,@test_Value2 float
--set @test_Var=86.3
--set @test_Value1=CEILING(@test_Var)
--select '>=86.3的最小整数',@test_Value1
--set @test_Value2=FLOOR(@test_Var)
--select '<=86.3的最大整数',@test_Value2
----取整函数使用-E--


----平方根函数使用-S--
--declare @test_Var float,@test_Value float
--set @test_Var=100
--set @test_Value=SQRT(@test_Var)
--select '100平方根值:',@test_Value
----平方根函数使用-E--


----三角(反三角)函数使用-S--
----（三角）sin,cos,tan,cot类似，以sin为例
----(反三角)asin,acos,atan
--declare @test_Var float,@test_Value float
--set @test_Var=60
--set @test_Value=SIN(@test_Var)
--select '60弧度角sin值:',@test_Value
----三角(反三角)函数使用-E--

三.SQL游标的使用

----游标的使用-S--
----创建游标
--use EPS_LOB_Web
--declare testCursorB cursor scroll for
--select * from T_Test1


----打开游标
--open testCursorB


----查看游标(每次查找下一行数据)
--fetch next from testCursorB
----从后往前读
--fetch prior from testCursorB
----转向第一条(不能和只进游标一起使用[read only])
--fetch first from testCursorB
----转向第一条(不能和只进游标一起使用[read only])
--fetch last from testCursorB
----跳转到具体的某行
--fetch absolute 2 from testCursorB
----相对目前位置,向前为+,向后为-
--fetch relative 1 from testCursorB
----将提前的记录存取在变量中
--declare @testVar1 int,@testVar2 varchar(100),@testVar3 varchar(100),@testVar4 varchar(100)
--fetch relative 1 from testCursorB into @testVar1,@testVar2,@testVar3,@testVar4
--print convert(varchar(10),@testVar1) + @testVar2 +@testVar3 + @testVar4


----关闭游标
--close testCursorB


----释放游标
--deallocate testCursorA
----游标的使用-E--

四.SQL触发器的使用

----使用触发器中代码代替删除操作-S--
--create trigger testInsteadTriggerA on T_Test1 instead of delete as
--begin
-- print '替代触发器被调用'
--end


----测试替代触发器
--delete from T_Test1 where SerialNo=11
----使用触发器中代码代替删除操作-E--


----取消触发器执行顺序-S--
--sp_settriggerorder @triggername='testTriggerC',@order='none',@stmttype='insert'
----取消触发器执行顺序-S--


----定义触发器执行顺序-S--
--定义testTriggerC为第一个顺序
--sp_settriggerorder @triggername='testTriggerC',@order='first',@stmttype='insert'
--定义testTriggerA为最后一个顺序
--sp_settriggerorder @triggername='testTriggerA',@order='last',@stmttype='insert'
----定义触发器执行顺序-S--


----创建更新触发器-S--
--create trigger testTriggerB on T_Test1 for update as
----for Insert表示对表进行插入时触发
--begin
-- print '更新触发器被调用'
--end


----测试触发器
--update T_Test1 set Path='http://www.domi.com' where SerialNo=11


--select * from T_Test1
----创建更新触发器-E--


----创建插入触发器-S--
--create trigger testTriggerA on T_Test1 for Insert as
----for Insert表示对表进行插入时触发
--begin
-- print 'testTriggerC触发器被调用'
--end


----测试触发器
--insert into T_Test1 values('音乐','多米','www.domi.com')


--select * from T_Test1
----创建插入触发器-E--


--删除触发器... delete触发器以此类推 0_0


----利用触发器在两张表中同时插入数据-S--
----创建触发器
--create trigger triggerTestInsertA on T_Test1 for insert as
--begin
-- insert into T_Test2 values('7','8')
--end


----测试
--insert into T_Test1 values('音乐','酷我','http://www.kuwo.com')
--select * from T_Test1
--select * from T_Test2


----删除测试数据
--delete from T_Test1 where SerialNo=12
--delete from T_Test2 where ID=10


----释放替代触发器
--drop trigger triggerTestInsertA


----利用触发器在两张表中同时插入数据-E--


----修改触发器-S--
--alter trigger testTriggerA on T_Test1 for Insert as
--begin
-- print 'testTriggerA触发器修改后被调用'
--end 
----修改触发器-E--


--查看触发器基本信息-S--
exec sp_help testTriggerA
--查看触发器基本信息-E--


--查看触发器完整代码-S--
exec sp_helptext testTriggerA
--查看触发器完整代码-E--

五.SQL高级用法

数据库操作：
1.SQL数据库，在存储过程中执行其他存储过程
      --删除5天之前的工单
            exec Proc_DelWorkorderBeforeDay5
2.数据库定期删除逾时数据
    BEGIN
      --删除100天之前的料表
           delete from T_Component where WorkOrderID in (select SerialNo from T_WorkOrder where AddDate < dateadd(dd,-100,getdate()))
      --删除100天之前的工单
           delete from T_WorkOrder where AddDate < dateadd(dd,-100,getdate())
       --删除100天之前的临时表(记录DIP的状态)
           delete from T_RecordResult_DIP where WorkOrderID in (select SerialNo from T_WorkOrder where AddDate < dateadd(dd,-100,getdate()))

    END

3.--查询工单是否存在，若不存在，则写入机种ID，并返回1
    select @workorderID=SerialNo from T_WorkOrder where WorkOrder=@Workorder
    if(ISNULL(@workorderID,0) = 0)

        begin

           --工单不存在，写入工单和机种ID
           insert into T_WorkOrder(WorkOrder,ModelID) values(@Workorder,@ModelID);
           set @Result = 1;
           return @Result;

        end

{提示：ISNULL(列名,0)函数是用来判断列名是否为null,如果为NUll,则返回0. 否则,返回列名的值}

4.根据变量来修改数据表中的值

   --更改BOM比对结果
    update T_WorkOrder set Result_BOM=(case when @Result=1 then @Result else '-1' end) where SerialNo=@workorderID;

以下在学习SQL过程中遇到的问题（逐步更新）

1.     UNION的用法

——UNION指令的目的是将两个 SQL 语句的结果合并起来。从这个角度看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。union只是将两个结果联结起来一起显示，并不是联结两个表………… UNION 的语法如下：

[SQL 语句 1]
UNION
[SQL 语句 2]

注意，UNION内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT 语句中的列的顺序必须相同。

与UNIONALL的区别，UNIONALL会显示合并后的所有列，而UNION则不会。

2.     如何将两个表中相同类型的列联结起来一起显示，并去掉重复列

——selectComponent from T_BOM_M as M (推荐使用)

union

select S.Component from T_BOM_M as M, T_BOM_S as S

  或

select M.Component from T_BOM_M as M, T_BOM_S as S            

union

          select S.Component from T_BOM_Mas M, T_BOM_S as S

3.     怎样对多表连接之后的新表进行操作

——利用AS给新表起个别名，再进行操作

4.     存储过程的新建，不要使用反编译脚本生成，应该使用新建一个查询写代码，因为在反编译的文件中，误操作可能会修改文件的内容，导致使用了改存储过程的应用程序无法正常使用。

5.     关于给存储过程传参的问题

——a.存储过程默认参数传输方向是Input，若要返回值，需设置参数为Output

传入参数：cmd.Parameters.Add(newOleDbParameter("@MainPN", OleDbType.VarChar)).Value = strMainPN;

传出参数：cmd.Parameters.Add(newOleDbParameter("@Result", OleDbType.VarChar,1024)).Direction =ParameterDirection.Output;

            b.接收存储过程返回的值

cmd.ExecuteNonQuery();

            strSubPN =cmd.Parameters["@Result"].Value.ToString();

6.删除表中重复列（利用临时表）

——selectdistinct * into #tmp from T_User

       Delete* from T_User

          Insertinto T_User select * from #tmp