SQL语句大全功能实现

首先我们创建两张表测试sql语句的正确性

用户表

1:写一个包含连接和分组的sql

SELECT dbo.Role.RoleId FROM dbo.Admin

INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId

GROUP BY dbo.Role.RoleId

2:写一个包含连接和case when的sql

SELECT dbo.Role.RoleId,

(CASE Admin.RoleId WHEN 1 THEN '管理员' ELSE '无' END)角色名

FROM dbo.Admin

INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId

3:写一个包含连接和Cast()函数的sql

SELECT CAST(dbo.Admin.RoleId AS DECIMAL(9,2))

FROM dbo.Admin

INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId

4:写一个包含连接和Convert()函数的sql

SELECT CONVERT(DECIMAL(9,2),Admin.RoleId)

FROM dbo.Admin

INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId

5:写一个包含子查询的sql

SELECT * FROM dbo.Admin 

WHERE RoleId IN(SELECT RoleId FROM dbo.Role)	

6:写一个视图的sql

CREATE VIEW view_1

AS 

SELECT * FROM dbo.Admin 

WHERE RoleId IN(SELECT RoleId FROM dbo.Role)

7:创建索引的sql

reate unique nonclustered        --表示创建唯一非聚集索引
index UQ_NonClu_MId        --索引名称
on dbo.Admin(MId)        --数据表名称（建立索引的列名）
with (  
   pad_index=on,    --表示使用填充 
   fillfactor=50,    --表示填充因子为50%   
   ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值    
   statistics_norecompute=off    --表示启用统计信息自动更新功
)

8:分页存储过程sql

CREATE PROCEDURE [dbo].[prco_PageResult]-- 获得某一页的数据 --
@currPage INT = 1,                        --当前页页码 (即Top currPage)
@showColumn VARCHAR(2000) = '*',   --需要得到的字段 (即 column1,column2,......)
@tabName VARCHAR(2000),           --需要查看的表名 (即 from table_name)
@strCondition VARCHAR(2000) = '',   --查询条件 (即 where condition......) 不用加where关键字@ascColumn VARCHAR(100) = '',--排序的字段名 (即 order by column asc/desc)
@bitOrderType BIT = 0,    --排序的类型 (0为升序,1为降序)
@pkColumn VARCHAR(50) = '',     --主键名称
@pageSize INT = 20,      --分页大小
@Count INT OUTPUT

AS
BEGIN -- 存储过程开始-- 该存储过程需要用到的几个变量 --
DECLARE @strTemp varchar(1000)
DECLARE @strSql nvarchar(4000)     --该存储过程最后执行的语句
DECLARE @strOrderType VARCHAR(1000)--排序类型语句 (order by column asc或者order by column desc)


BEGIN
IF
 @bitOrderType = 1   -- bitOrderType=1即执行降序
BEGIN    
    SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'   
    SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN  
    SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'   
    SET @strTemp = '>(SELECT max'
END
IF @currPage = 1    -- 如果是第一页
BEGIN    
IF @strCondition != ''      
      SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+                                           'WHERE '+@strCondition+@strOrderType 
   ELSE       
 SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+@strOrderType
END 


ELSE    -- 其他页BEGIN  
  IF @strCondition !=''  
      SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+        ' WHERE '+@strCondition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+      
  ' '+@pkColumn+' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType   
 ELSE      
  SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+        ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+       
 ' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
PRINT @strSql
EXEC (@strSql)
 --得到总数
BEGIN        
IF @strCondition = ''  
      SET @strSql='select @total= count('+@showColumn+') from '+@tabName  
  ELSE       
      SET @strSql='select @total= count('+@showColumn+') from '+@tabName+' where '+@strCondition	        			
END
PRINT @strSql	
    EXEC sp_executesql @strSql,N'@total int out',@total=@Count OUTPUT
END  -- 存储过程结束

9:写一个触发器sql

create trigger trig_delete

on dbo.Admin 

after delete

as

begin

    select Deleted.MId as 已删除的用户

    from deleted

end;

10:写一个存储过程包含事务的sql 

CREATE PROCEDURE [dbo].[pro_pro16]
AS
DECLARE @truc INT
SELECT @truc=@@trancount
IF @truc=0
BEGIN TRAN p1
ELSE
SAVE TRAN pl
IF (@truc=2)
BEGIN
ROLLBACK TRAN pl
RETURN 25
END
IF(@truc=0)
COMMIT TRAN pl
RETURN 0

12:分页的实现方式？至少写3种

--ROW_NUMBER() OVER()方式
select * from ( 
select *, ROW_NUMBER() OVER(Order by MId ) AS RowId from dbo.Admin ) 
as b
where RowId between 1 and 2
--top not in方式
select top 3 * from dbo.Admin
 where MId not in (select top 2 MId from dbo.Admin)
--Max()方法
SELECT  * FROM dbo.Admin
WHERE MId IN (
SELECT TOP 3 MId FROM dbo.Admin 	
where MId <(
select MAX(MId) from dbo.Admin WHERE MId<=6)	
ORDER BY MId DESC)
ORDER BY MId

13:写一个包含连接和分组,并且根据某个字段拼接的sql

select RoleId,
(select COUNT(1) FROM dbo.Admin where dbo.Admin.RoleId=Role.RoleId) as	Count,
stuff((select ',' + MName from dbo.Admin,dbo.Role where Role.RoleId=dbo.Admin.RoleId for xml path('')),1,1,'') as UserName
from Role
GROUP BY RoleId

14:写一个包含having写法的sql

SELECT MId FROM dbo.Admin
GROUP BY MId
having MId<7

15:写一个包含连接和分组,排序的sql。

SELECT MId,RoleName FROM dbo.Admin
INNER JOIN dbo.Role ON Role.RoleId = Admin.RoleId
GROUP BY MId,RoleName
ORDER BY MId