MSSQL2005 存储过程 触发器 连接 等知识索引

select * from t_a
select * from t_b
--变量定义
--数据类型转换
--表连接
--视图创建
--数据类型转换
insert into t_b(U_ID,u_name) select id,name from t_a
--查询计划&查询优化
delete  from t_b
-----------------------------------------------------------------------------------------------------------
select t_b.u_id,t_b.u_name,t_a.id,t_a.name from t_a inner join t_b on t_a.id=t_b.u_id
select t_b.u_id,t_b.u_name,t_a.id,t_a.name from t_b inner join t_a on t_a.id=t_b.u_id
select t_b.u_id,t_b.u_name,t_a.id,t_a.name from t_b left join t_a on t_a.id=t_b.u_id
select t_b.u_id,t_b.u_name,t_a.id,t_a.name from t_b left join t_a on t_a.id=t_b.u_pid
select t_b.u_id,t_b.u_name,t_a.id,t_a.name from t_a left join t_b on t_a.id=t_b.u_pid

-- 左连接 14 条
-----------------------------------------------------------------------------------------------------------
select count(*) from t_a left join t_b on t_a.pid=t_b.u_pid --主：5  副：10 结果：50
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_a left join t_b on t_a.pid=t_b.u_pid
select count(*) from t_a left join t_b on t_a.pid=t_b.u_pid --主：10 副：5  结果：50
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_b left join t_a on t_a.pid=t_b.u_pid
--主:5  副:10  结果:10
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_a right join t_b on t_a.id=t_b.u_id
--主:10 副:5
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_b right join t_a on t_a.id=t_b.u_id
--主:10 副:5
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_a right join t_b on t_a.id=t_b.u_pid
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_b right join t_a on t_a.id=t_b.u_pid
--主:10 副:5
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_b right join t_a on t_a.pid=t_b.u_pid
--FULL OUTER JOIN
select t_b.u_id,t_b.u_name,t_b.u_pid,t_a.id,t_a.name,t_a.pid from t_b FULL OUTER JOIN t_a on t_a.id=t_b.u_id
------------------------------------------------------------------------------------------------------------
--Distinct
select distinct pid from t_a --指定的字段不重复 结果:1
-- union and union all
select * from t_a union select * from t_b
select * from t_b union select * from t_a
select * from t_a union  select * from t_b
select * from t_a union all select * from t_b
select t_a.id,t_a.name from t_a union all select * from t_b
-----------------------------------------------------------------------------------------------------------
--数据类型转换 convert cast
PRINT Convert(datetime,'2011-04-12')
PRINT CAST('2011-04-12' AS datetime) --建议尽量使用CAST进行转换
--varchar to int
print cast('100' as int)
select * from t_a
select cast(pid as nvarchar(10)) from t_a
-----------------------------------------------------------------------------------------------------------
declare @val1 int
set @val1=100
print cast(@value1 as varchar(500))
-----------------------------------------------------------------------------------------------------------
declare @val2 varchar(5)
set @val2='12345'
--print cast(@val2 as varchar(10))
Print cast(@val2 as varchar(3)) --varchar(5) to varchar(3) 自动丢失后2位
-----------------------------------------------------------------------------------------------------------
declare @val3 decimal(8,2)
set @val3=100.33  --结果仅仅显示：整数位
print(@val3)
print cast(@val3 as float)
-----------------------------------------------------------------------------------------------------------
declare @val4 float
set @val4=100.1234567
print(@val4)
-----------------------------------------------------------------------------------------------------------
declare @val5 datetime
set @val5=getdate()
print cast(@val5 as nvarchar(50))
print CONVERT(varchar(12) , getdate(),100 )
-----------------------------------------------------------------------------------------------------------

 

2.触发器

-- instead of 类型触发器

USE [TestDB]
GO
/****** Object:  Trigger [dbo].[tr_b_delete_insteadof]    Script Date: 04/13/2011 15:44:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
--触发器名称
ALTER TRIGGER [dbo].[tr_b_delete_insteadof]
--触发器操作表
ON
[dbo].[t_b] 
AFTER DELETE
--使用下面的类型定义，则不进行删除操作
--INSTEAD OF DELETE --一张表只能有一个Instead of(替换类型的)Tirgger触发器
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for trigger here
PRINT 'Evel Trigger tr_b_delete_insteadof'
END

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

update触发器 Triggers

USE [TestDB]
GO
/****** Object:  Trigger [dbo].[tr_b_update]    Script Date: 04/13/2011 15:47:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER TRIGGER
--触发器名称
[dbo].[tr_b_update]
   ON 
--触发器操作表
[dbo].[t_b]
--触发器执行类型
AFTER UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for trigger here
print 'evel triggers tr_b_update'
select * from t_b
END

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

Insert触发器 Triggers

USE [TestDB]
GO
/****** Object:  Trigger [dbo].[tr_b_insert]    Script Date: 04/13/2011 15:46:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:TimTian <Author,,Name>
-- Create date: <Create Date,2011,04,13>
-- Description:    <Description,触发器,>
-- =============================================
ALTER TRIGGER
--触发器名称
[dbo].[tr_b_insert]
ON 
--触发器操作表
[dbo].[t_b]
--触发器操作状态
AFTER INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;
    -- Insert statements for trigger here
print '--------------------------------------------------'
declare @val nvarchar(50) --不支持中文
--set @val='执行触发器操作'
--print @val
print 'evel tr_b_insert Tiggers with AFERT INSERT '
print '--------------------------------------------------'
END

 

3 .存储过程

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[pro_update_t_b]    Script Date: 04/13/2011 15:55:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_update_t_b]
    -- Add the parameters for the stored procedure here
    @olduid int,@uid int,@uname nvarchar(50),@upid varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    update t_b set u_id=@uid, u_name=@uname,u_pid=@upid where u_id=@olduid
END