SQL语句基本操作

最新推荐文章于 2024-03-11 09:50:07 发布

一个二货不在犯二

最新推荐文章于 2024-03-11 09:50:07 发布

阅读量309

点赞数

本文链接：https://blog.csdn.net/Y1387958264/article/details/102646795

版权

要求:创建两张测试表

表一用户表:

表二地址表:

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

select a.UserId,a.AddressName,u.UserName from UserInfo u
inner join Address a
on u.UserId=a.UserId
order by u.UserId

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

select Id,
CASE  
	when Address.AddressName='深圳宝安区' then '宝安区'
	when Address.AddressName='深圳南山区' then '南山区'
	else '无' end '区域'
from dbo.Address

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

select 
Cast(AddressName as varchar(2) )
from UserInfo 
inner join Address on UserInfo.UserId=Address.UserId

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

select 
CONVERT(DECIMAL(9,2),Address.Id)
from UserInfo 
inner join Address on UserInfo.UserId=Address.UserId

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

select Id,AddressName from Address
where UserId in (select UserId  from UserInfo where UserName ='张三')

6:写一个视图的sql

create view UserInfoView_1
as 
select * from UserInfo
where UserId in (select dbo.Address.UserId from dbo.Address where Address.AddressName='深圳南山区')

7:创建索引的sql

create  NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引
on UserInfo(UserId)  --为userinfo表的userid字段创建索引
WITH FILLFACTOR = 30 --填充因子为30%

8:分页存储过程sql

create proc PageLisePrc
(
	@PageIndex int,--当前页面
	@PageSize int,--显示页面
	@TabName varchar(200),--当前表
	@CalumName varchar(500),--字段
	@Where varchar(2000),--条件
	@OrderBy varchar(500),--分组
	@Count int Out--总数
)
AS
	 DECLARE @SqlStr nvarchar(2000);
	 
	 set @SqlStr= 'select '+ @CalumName +' from 
					(select '+ @CalumName +' ,row_number() 
					 over(order By '+ @OrderBy +' ) as rownumber   from '+ @TabName +' where '+ @Where +' )t 
					 where  t.rownumber  between  '+ CAST(((@PageIndex-1)*@PageSize+1) as varchar(200)) +' and '+ CAST((@PageSize*@PageIndex) as varchar(200) );
	 
	 print @SqlStr
	 
	 exec (@SqlStr)
	 --总行数
	 set @SqlStr ='select @total=count(1) from  '+ @TabName +'  where  '+@Where
	 print @SqlStr
	 
	 
	 exec sp_executesql @SqlStr,N'@total int out ',@total=@Count output
 
go;

declare @num int
exec PageLisePrc 1,1,'UserInfo',' * ','1=1','UserId',@num output;
print @num

9:写一个触发器sql

create trigger trig_delete
on UserInfo 
after delete
as
begin
    select Deleted.UserId 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条数据,存储过程实现。

  DECLARE @Data NVARCHAR(max)

    SET @Data='tanw,keenboy' --Id,Name

    DECLARE @Temp TABLE

    (

    Id INT IDENTITY(1,1),

    Name NVARCHAR(50)

    )

    DECLARE @Id INT

    DECLARE @Name NVARCHAR(50)

    DECLARE @Results NVARCHAR(MAX) SET @Results=''

    INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,';'))

    WHILE EXISTS(SELECT * FROM @Temp)

    BEGIN

    SELECT TOP 1 @Id=Id,@Name=Name from @Temp

    DELETE FROM @Temp where [id] = @Id

    SET @Results=@Results+@Name+','

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

--top
select top 2 * from
( select top 2 *from 
(select top 3 *  from UserInfo )t order by UserId desc)a 
order by UserId


--not in
select top 2 * from UserInfo where UserId not in
(select top 2 UserId from UserInfo)

--max()
 SELECT  * FROM UserInfo
WHERE UserId IN (SELECT TOP 3 UserId FROM UserInfo
	where UserId <(select MAX(UserId) from UserInfo WHERE UserId<=6)
	ORDER BY UserId DESC)
ORDER BY UserId

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

select Address.UserId,
(select COUNT(1) FROM UserInfo where UserInfo.UserId=Address.UserId) as	Count,
stuff((select ',' + UserName from UserInfo,Address where UserInfo.UserId=Address.UserId for xml path('')),1,1,'') as UserName
 from Address
 GROUP BY Address.UserId

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

select MAX(Id) from dbo.Address
having  MAX(Id)>2

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


select count(Id),UserName,AddressName
from UserInfo 
inner join Address on UserInfo.UserId=Address.UserId
group by UserName,AddressName
order by UserName