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