SQL语句

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

select s.Id,s.Name,g.GradeName from Student s
inner join Grades g on s.GradeId=g.Id

2:写一个包含连接和case when的sql
--2查出每个年级总人数

select COUNT(1),
CASE GradeName
 WHEN 's1' THEN '一年级'
 WHEN 's2' THEN '二年级'
 end
 from Student s
inner join Grades g on s.GradeId=g.Id
GROUP BY
 CASE GradeName
 WHEN 's1' THEN '一年级'
 WHEN 's2' THEN '二年级'
 end

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

select CAST(s.Id AS decimal),s.Name,g.GradeName from Student s
inner join Grades g on s.GradeId=g.Id

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

select s.Id,s.Name,g.GradeName,CONVERT(varchar(100),s.Time, 12) as time from Student s
inner join Grades g on s.GradeId=g.Id

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

select s.Id,s.Name from Student s
where s.GradeId in(select g.Id from Grades g where g.GradeName='s1')

6:写一个视图的sql
--6

Create view vw1 as
select s.Id,s.Name,g.GradeName from Student s
inner join Grades g on s.GradeId=g.Id
select * from vw1

7:创建索引的sql
--7

CREATE INDEX index_name
ON Student (Name)

8:分页存储过程sql
--8

CREATE PROC Proc_Paging
 @TableFields NVARCHAR(512),
 @TableName NVARCHAR(512),
 @SqlWhere NVARCHAR(512),
 @OrderBy NVARCHAR(64),
 @PageIndex INT,
 @PageSize INT,
 @TotalCount INT OUTPUT
AS
 DECLARE @SQL1 NVARCHAR(2048), @SQL2 NVARCHAR(2048)  
 --SET @SQL1 = N'SELECT TOP ' + CONVERT(VARCHAR(2), @PageSize) + ' * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' + @TableFields + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ') AS TmpTable WHERE TmpTable.NID > (@PageIndex - 1) * @PageSize SELECT @TotalCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @SqlWhere  --用TOP关键字进行筛选
 SET @SQL1 = N'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS NID, ' + @TableFields + ' FROM ' + @TableName + ' WHERE ' + @SqlWhere + ') as TmpTable WHERE TmpTable.NID BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex* @PageSize SELECT @TotalCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @SqlWhere
 SET @SQL2 = N'@TableFields NVARCHAR(512),@TableName NVARCHAR(512),@SqlWhere NVARCHAR(512),@OrderBy NVARCHAR(64),@PageIndex INT,@PageSize INT,@TotalCount INT OUTPUT'
EXEC SP_EXECUTESQL @SQL1, @SQL2, @TableFields, @TableName, @SqlWhere,@OrderBy,@PageIndex,@PageSize,@TotalCount OUTPUT
PRINT @SQL1  --打印执行语句
9:写一个触发器sql
CREATE TRIGGER TRI_BIISBN1
ON dbo.Student  
FOR UPDATE  
AS
IF UPDATE(Name)    
BEGIN
 UPDATE dbo.Student SET Time =2000/2/2 FROM dbo.Student
END

10:写一个存储过程包含事务的sql
ALTER proc kkk
 @Name nvarchar(50)
 as
 begin
if(isnull(@Name,'')='')
begin
print('名字不能为空');
return;
end
begin tran  --开启事务
insert into Students(Name) values(@Name)
commit tran
end
11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。
Create proc sp_Insert_Student
 @No char(10),
 @Name varchar(20),
 @Sex char(2),
 @Age int,
 @rtn int output
 as
 declare
 @tmpName varchar(20),
 @tmpSex char(2),
 @tmpAge int
 
 if exists(select * from Student where No=@No)
 begin
 select @tmpName=Name,@tmpSex=Sex,@tmpAge=Age from Student where No=@No
 if ((@tmpName=@Name) and (@tmpSex=@Sex) and (@tmpAge=@Age))
 begin
 set @rtn=0  --有相同的数据，直接返回值
 end
 else
 begin
 update Student set Name=@Name,Sex=@Sex,Age=@Age where No=@No
 set @rtn=2  --有主键相同的数据，进行更新处理
 end
 end
 else
 begin
 insert into Student values(@No,@Name,@Sex,@Age)
 set @rtn=1  --没有相同的数据，进行插入处理
 end
12:分页的实现方式？至少写3种
--第一种
SELECT TOP 10 A.* FROM (SELECT TOP 20 * FROM Student ORDER BY ID ASC)A  ORDER BY A.ID asc
--第二种
select top 10*
from Student
where Id>=
(select max(Id)
from (select top 1 Id
from Student
order by  Id asc) temp_max_ids)
order by Id
--第三种
select top 10 *
from (select row_number()
over(order by Id asc) as rownumber,*
from Student) temp_row
where rownumber>0
13:写一个包含连接和分组,并且根据某个字段拼接的sql
select GradeName,stuff((select ',' + Name from Student for xml path('')),1,1,'')  as  field from Student s
inner join Grades g on s.GradeId=g.Id
group by GradeName
14:写一个包含having写法的sql
select s.Id,s.Name,g.GradeName from Student s
inner join Grades g on s.GradeId=g.Id
group by g.GradeName,s.Id,s.Name
having g.GradeName='s1'
15:写一个包含连接和分组,排序的sql。
select s.Id,s.Name,g.GradeName from Student s
inner join Grades g on s.GradeId=g.Id
group by g.GradeName,s.Id,s.Name
order by s.Id