SQL111

--规格表
CREATE TABLE Spec (
	Id int identity(1,1) primary key, --规格id
	Spec_Name varchar(50), --规格名称
	CreateTime datetime, --添加时间
	ModifiedTime datetime --最后修改时间
) 

--规格值表
CREATE TABLE Spec_Value (
	Id int identity(1,1) primary key, --规格值id
	Spec_Id int,--规格表Id
	Spec_value varchar(50), --规格值
	ModifiedTime datetime, --最后修改时间
	CreateTime datetime, --添加时间
)

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

select k.Id,k.Spec_Name,COUNT(v.Id) from dbo.Spec k inner join dbo.Spec_Value v on k.Id = v.Spec_Id group by k.Id,k.Spec_Name

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

select Id,CASE Spec_Id  
          WHEN '1' THEN '颜色' 
          WHEN '2' THEN '尺寸' 
          WHEN '3' THEN '版本' 
          ELSE '其他' END,
Spec_Value,ModifiedTime,CreateTime from dbo.Spec_Value

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

select CAST(Spec_Id AS varchar) from dbo.Spec_Value

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

select *,CONVERT(VARCHAR(15),ModifiedTime,108) from dbo.Spec_Value

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

select * from dbo.Spec_Value where Spec_Id = (select Id from dbo.Spec where Spec_Name = '版本')

6:写一个视图的sql

--创建视图
CREATE VIEW spec_Key_Value AS select k.Spec_Name,v.* from dbo.Spec k inner join dbo.Spec_Value v on k.Id = v.Spec_Id
--查询视图
select * from spec_Key_Value

7:创建索引的sql

CREATE INDEX index_name
ON dbo.Spec_Value (Spec_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 count(*)  from '+@TableName+' where '+@Where;
print @sql;
exec sp_executesql @sql,N'@total int out',@total=@Count output

GO

9:写一个触发器sql

create trigger t_spec on dbo.Spec
instead of delete
as
begin 
declare @id int
select @id=Id from deleted
delete from dbo.Spec where Id=@id

delete from dbo.Spec_Value where Spec_Id=@id
end
go 
--触发
delete from dbo.Spec where Id=1

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

create  proc [dbo].[Proc_InsertSpec_Value]
@Spec_Id int,@Spec_Value varchar(200),@ModifiedTime datetime,@CreateTime datetime2
as
begin
set nocount on  --on表示不返回计数
set xact_abort on  --当执行事务时，如果出错,会将transcation设置为uncommittable状态

begin try 
declare @stuCountByName int;
select @stuCountByName=count(*) from dbo.Spec_Value where Spec_value=@Spec_Value;

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

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

begin tran  --开启事务
insert into dbo.Spec_Value(Spec_Id,Spec_value,ModifiedTime,CreateTime) values(@Spec_Id,@Spec_value,@ModifiedTime,@CreateTime)
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_InsertSpec_Value] 1,'','2019-5-6 12:23:34','2019-5-6 12:23:34'

EXECUTE [Proc_InsertSpec_Value] 1,'红色','2019-5-6 12:23:34','2019-5-6 12:23:34'

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

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

--TOP方式：查询6到10条
select top 5 * from
(
    select top 5 * from
    ( 
        select top 10 * from dbo.Spec_Value order by Id 
    )t order by t.Id desc
)s order by s.Id asc

--not in方式： 查询6到10条;
select top 5 * from dbo.Spec_Value  where Id not in(select top 5 Id from dbo.Spec_Value order by Id)

select * from dbo.Spec_Value where Id between 6 and 10

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

SELECT k.Spec_Name, 
STUFF(
     ( 
      SELECT ','+ Spec_value FROM dbo.Spec_Value v WHERE k.Id = v.Spec_Id FOR XML PATH('')
     ),1 ,1, '') children 
FROM dbo.Spec k
GROUP BY k.Spec_Name ,k.Id

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

select k.Id,k.Spec_Name,COUNT(v.Id) from dbo.Spec k inner join dbo.Spec_Value v on k.Id = v.Spec_Id group by k.Id,k.Spec_Name having k.Id = 1 

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

select k.Id,k.Spec_Name,COUNT(v.Id) from dbo.Spec k inner join dbo.Spec_Value v on k.Id = v.Spec_Id group by k.Id,k.Spec_Name ORDER BY COUNT(v.Id)