sql语句(2019.10.19)

首先我们创建两张表测试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
create 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

11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。
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