表连接相关SQL语句操作

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

select a.LoginName,a.TelPhone,b.CharacterName from AdminInfo as a inner join CharacterInfo as b on (a.CharacterId=b.CharacterId)
group by a.LoginName,a.TelPhone,b.CharacterName;

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

SELECT
 CASE
   WHEN b.CharacterName IS NULL THEN '管理员' 
   ELSE b.CharacterName 
 END AS CharacterName 
FROM
  AdminInfo as a inner join CharacterInfo as b on a.CharacterId=b.CharacterId
  where a.Id=7;

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

SELECT CAST(b.CreateTime AS Date) from CharacterInfo as a
inner join AdminInfo as b on a.CharacterId=b.CharacterId
where a.CharacterId=2;

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

select a.LoginName as 登录名,a.Gender as 性别,b.CharacterName as 角色名,convert(Varchar(10),a.Age)+'岁' as 年龄,a.CreateTime as 创建时间 
from AdminInfo AS a
inner join CharacterInfo AS b on a.CharacterId=b.CharacterId

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

select * from AdminInfo where 
CharacterId=
(select CharacterId from CharacterInfo where CharacterName='超级管理员');

6:写一个视图的sql

create view HomeWorkView as 
select LoginName,Gender,TelPhone
from AdminInfo
where Id=1;

7:创建索引的sql

CREATE UNIQUE INDEX AdminIndex ON AdminInfo (LoginName,Gender);
select * from AdminInfo WITH(index(AdminIndex)) where Id =2

8:分页存储过程sql

CREATE PROCEDURE pagelistprocAdoNet
	(@pageIndex int,
	@pageSize int,
	@tableName varchar(200),
	@columnName varchar(500),
	@orderby varchar(50),
	@sort varchar(50),
	@where varchar(500))	
AS
BEGIN
	declare @sql nvarchar(2000);
    --查询结果集
    set @sql='select '+@columnName+' from (select'+@columnName+',ROW_NUMBER()over(order by '+@orderby+')
    as number from '+@tableName+ ' where '+@where+')t where t.number between '+cast((@pageIndex-1)*@pageSize as varchar(200))+' and '+cast(@pageIndex*@pageSize as varchar(200))+' order by '+@orderby+' '+@sort+'';
    exec(@sql);
        
    --定义总数sql变量
    declare @countSql nvarchar(2000);
    set @countSql='select count(1) from '+@tableName;
    print (@countSql);
    exec(@countSql);
END
GO
exec pagelistprocAdoNet 1,10,'AdminInfo','*','Id','asc','1=1';

9:写一个触发器sql

CREATE TRIGGER AdminTrigger
ON AdminInfo
FOR INSERT
AS
DECLARE @LoginName varchar(20)
SELECT @LoginName=LoginName from AdminInfo
INSERT INTO C (TableName,Type,dDate,LoginName) VALUES ('AdminInfo','Insert',getdate(),@LoginName)
GO

10:写一个存储过程包含事务的sql 

CREATE PROCEDURE YourProcedure    
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY---------------------开始捕捉异常
       BEGIN TRAN------------------开始事务
		UPDATE AdminInfo SET RealName = '玉玉' where Id=2
        UPDATE CharacterInfo SET CharacterName = '很帅的管理员' where CharacterId=1
    COMMIT TRAN -------提交事务
    END TRY-----------结束捕捉异常
    BEGIN CATCH------------有异常被捕获
        IF @@TRANCOUNT > 0---------------判断有没有事务
        BEGIN
            ROLLBACK TRAN----------回滚事务
        END 
    END CATCH--------结束异常处理
END

exec YourProcedure

11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。

CREATE PROCEDURE InsertProcedure    
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY---------------------开始捕捉异常
       BEGIN TRAN------------------开始事务
       DECLARE @count int
		SET @count=0
		while @count<10
		BEGIN
			insert into AdminInfo(RealName,LoginPwd,TelPhone,Email,CharacterId,Age,QQ,Content,CreateTime,States)
			values('胡歌1','12356','15712065114','2369120574@qq.com',4,24,2369120574,'请注意！','2019-8-5',0);
        SET @count=@count+1
		End
    COMMIT TRAN -------提交事务
    END TRY-----------结束捕捉异常
    BEGIN CATCH------------有异常被捕获
        IF @@TRANCOUNT > 0---------------判断有没有事务
        BEGIN
            ROLLBACK TRAN----------回滚事务
        END 
    END CATCH--------结束异常处理
END
exec InsertProcedure

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

--ROW_NUMBER() OVER()方式
select * from (
	select *, ROW_NUMBER() OVER(Order by Id ) AS LoginId from AdminInfo) 
	as b 
	where Id between 1 and 10
	
--top not in方式
select top 10 * from AdminInfo
where Id not in (select top 0 Id from AdminInfo)

--利用max（主键）
select top 10 * 
from AdminInfo 
where Id>=
(select max(Id) 
from (select top ((1-1)*10+1) Id
from AdminInfo 
order by  Id asc) temp_max_ids) 
order by Id;

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

select b.CharacterId,b.CharacterName,COUNT(a.CharacterId)as count,LoginName=stuff((select '，' +LoginName from AdminInfo where CharacterId=b.CharacterId for xml path('')),1,1,''),
b.Content from AdminInfo as a right join CharacterInfo as b on a.CharacterId = b.CharacterId
group by b.CharacterId,b.CharacterName,b.Content

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

select a.CharacterId,a.RealName from AdminInfo AS a
inner join CharacterInfo AS b on a.CharacterId=b.CharacterId
group by a.CharacterId,a.RealName
Having a.CharacterId=1;

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

select a.Id,a.RealName,b.CharacterName from AdminInfo AS a
inner join CharacterInfo AS b on a.CharacterId=b.CharacterId
order by a.Id DESC