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条数据,存储过程实现。

 

 --11
 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

 

--13
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

 

--14
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。

--15
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