15sql

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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

424560476

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值