1:写一个包含连接和分组的sql
select ai.Name,r.RoleName from AdminInfo ai
inner join Role r on ai.RoleId=r.RoleId
group by ai.Name,r.RoleName
2:写一个包含连接和case when的sql
select ai.Id,ai.Name,CASE ai.Sex WHEN 1 THEN ‘男’ WHEN 0 THEN ‘女’ ELSE ‘其他’ END from AdminInfo ai
3:写一个包含连接和Cast()函数的sql
select CAST(Id AS decimal(9,2)) from AdminInfo
4:写一个包含连接和Convert()函数的sql
select ai.Name,CONVERT(VARCHAR(19),ai.Age,106) from AdminInfo ai
5:写一个包含子查询的sql
select * from AdminInfo where RoleId in (select top 2 RoleId from Role)
6:写一个视图的sql
create view View_AdminInfo
with encryption
as
select ai.Name from AdminInfo ai
go
–使用视图
select *from View_AdminInfo
7:创建索引的sql
–非空索引
CREATE UNIQUE INDEX index_idNo ON AdminInfo(Id)
8:分页存储过程sql
create proc [dbo].[PageList]
(
@PageIndex int,
@PageSize int,
@TableName varchar(200),
@CoumnName varchar(500),
@Where varchar(500),
@OrderBy varchar(500),
@Count int out
)
as
declare @sql nvarchar(2000)
set @sql=’ select ‘+@CoumnName+’ from (select '+@CoumnName+
’ ,row_number() over(order by ‘+@OrderBy+’ ) as number from '+@TableName+ ’ where '+@Where+
’ ) t where t.number between '+ Cast(((@PageIndex-1)@PageSize+1) as varchar(200)) + ’ and '+
Cast((@PageIndex@PageSize)as varchar(200));
print @sql;
exec(@sql);
set @sql=‘select @total=count(*) from ‘+@TableName+’ where ‘+@Where;
print @sql;
exec sp_executesql @sql,N’@total int out’,@total=@Count output
EXECUTE PageList 1,3,‘AdminInfo’,’*’,‘1=1’,‘Id’,0
9:写一个触发器sql
CREATE TRIGGER TRI_BIISBN1
ON AdminInfo
FOR UPDATE
AS
BEGIN
UPDATE AdminInfo SET Sex = 3 FROM AdminInfo where Id=1
END
10:写一个存储过程包含事务的sql
11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。
create proc [dbo].[Proc_Add]
as
DECLARE @i int;
set @i=10;
WHILE (@i>0)
begin
insert into AdminInfo(Name, Age,RoleId,Sex)values(‘无名氏’,0,1,0);
set @i=@i-1;
END
EXECUTE [Proc_Add]
12:分页的实现方式？至少写3种
select * from (
select *, ROW_NUMBER() OVER(Order by Id ) AS RowId from AdminInfo
) as b
where RowId between 1 and 2
select* from AdminInfo ai
where ai.Id in (select top 5 Id from AdminInfo)
select* from AdminInfo ai
where ai.Id not in (select top 5 Id from AdminInfo)
13:写一个包含连接和分组,并且根据某个字段拼接的sql
select r.RoleId,r.RoleName,COUNT(Id)as Count,
Names=STUFF((select ‘,’+Name from dbo.AdminInfo where RoleId=r.RoleId FoR XML PATH(’’)),1,1,’’)
from dbo.AdminInfo a right join dbo.Role r on a.RoleId=r.RoleId
group by r.RoleId,RoleName
14:写一个包含having写法的sql
select ai.Id,ai.Name,r.RoleName from AdminInfo ai
inner join Role r on ai.RoleId=r.RoleId
group by ai.Id,ai.Name,r.RoleName
having ai.Id=2
15:写一个包含连接和分组,排序的sql。
select ai.Id,ai.Name,r.RoleName from AdminInfo ai
inner join Role r on ai.RoleId=r.RoleId
group by ai.Id,ai.Name,r.RoleName
order by ai.Id desc
3万+
