常用Sql语句练习

1.首先我们先创建两张表

Create database  TestAdmin
Use  TestAdmin


--管理表

Create table [AdminUser](
	Id int identity(1,1) primary key,--主键
	RId int,--角色关联Id
	Name varchar(50),--登录名
	Password varchar(36),--密码
	LastLoginTime datetime,--上次登录时间
	Enabled bit,--是否启用
	EmployeeName varchar(50),--姓名
	Email varchar(200),--邮箱
	LastLogicIP varchar(50),--上次登录IP
	Remark varchar(200),--备注
	CertNo varchar(50),--身份证
	IsDelete bit,--是否删除
	CreateTime datetime --创建时间
)
--角色表
Create table [Role](
	Id int identity(1,1) primary key,--主键
	
	Name varchar(50),--角色名称
	Remark varchar(200),--备注
	CreateTime datetime,--创建时间
	IsEnable bit,--是否启用

)

2.给我们表中插入一些数据

--管理员表
insert into [AdminUser](RId,Name,Password,LastLoginTime,Enabled,EmployeeName,Email,LastLogicIP,Remark,CertNo,IsDelete,CreateTime)
	values(3,'哒哒','admin','2019-09-08 12:23:34',1,'哒哒','243124@qq.com','深圳','无','2342353',0,'2019-09-08 12:23:23')

--角色表
insert into  [Role] (Name,Remark,CreateTime,IsEnable) values ('超级管理员','有所有权限','2019-9-8 12:34:56',1)

3.写一个包含连接和分组的sql

select COUNT(*) Count,r.Name from [AdminUser] a Inner join [Role] r on r.Id=a.RId group by r.Id,r.Name

4.写一个包含连接和case when的sql

select Name,  CASE IsDelete
when   'true' THEN '是'
when   'false' THEN '否'
END
from [AdminUser]

5.写一个包含连接和Cast()函数的sql

select CAST (IsDelete as int) from [AdminUser]

6.写一个包含连接和Convert()函数的sql

select CONVERT(varchar(100),CreateTime,100) from [AdminUser]

7.写一个包含子查询的sql

select    Name from   [AdminUser] where    RId =( select Id from [Role] where Name='栏目管理员')

8.写一个视图的sql

create view [UserInfo]  AS
select  Name,Password from [AdminUser]
where IsDelete='false'

9.创建索引的sql

create index Adminid ON [AdminUser] (Id)

10.分页存储过程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 count(*)  from '+@TableName+' where '+@Where;
print @sql;
exec sp_executesql @sql,N'@total int out',@total=@Count output
EXECUTE PageList 1,5,'dbo.AdminInfo','*','','AdminId',0

11.写一个触发器sql

create trigger Admin on [AdminUser]
instead of delete
as
begin 
declare @id int
select @id=Id from deleted
delete from [Role] where Id=@id

delete from [AdminUser] where RId=@id
end
go 
delete from [Role] where Id=1

12.写一个存储过程包含事务的sql

create  proc [dbo].[Proc_InsertRole]
@RoldName nvarchar(50),@RoldRemark  varchar(200),@RoldCreateTime datetime
as
begin
set nocount on  --on表示不返回计数
set xact_abort on  --当执行事务时，如果出错,会将transcation设置为uncommittable状态

begin try 
declare @stuCountByName int;
select @stuCountByName=count(*) from Role where Name=@RoldName;

if(isnull(@RoldName,'')='')
begin
print('名字不能为空');
return;
end

if(@stuCountByName>0)
begin
print('名字重复');
return
end

begin tran  --开启事务
insert into Role(Name,Remark,CreateTime) values(@RoldName,@RoldRemark,@RoldCreateTime)
commit tran  --提交事务

end try

begin catch
if xact_state()=-1
rollback tran;  --回滚事务
select ERROR_NUMBER() as ErrorNumber;
select ERROR_MESSAGE() as ErrorMsg;
end catch
set xact_abort off;
end

EXECUTE [Proc_InsertRole] '超级管理员','无','2019-5-6 12:23:34'

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

在这里插入代码片

14.分页的实现方式？至少写3种

select * from dbo.[AdminUser] where Id between 3 and 4

select top 5 * from (select top 5 * from( select top 10 * from dbo.[AdminUser] order by Id )t order by t.Id desc)s order by s.Id asc

select top 5 * from dbo.[AdminUser]  where Id not in(select top 2 Id from dbo.[AdminUser])

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

select r.Name ,STUFF(
(select ',' +a.Name from [AdminUser] a where a.RId=r.Id for xml path('')),1,1,'') children  from [Role] r group by r.Id ,r.Name

16.写一个包含having写法的sql

select a.Name,r.Name from [AdminUser] a  inner join [Role] r on a.RId=r.Id     group by r.Id  , a.Name,r.Name  having r.Id=1

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

select a.Name,r.Name from [AdminUser] a  inner join [Role] r on a.RId=r.Id     group by r.Id  , a.Name,r.Name order by  r.Id