周末作业

一：创建数据库Login,并新建两张表，插入数据

二：写sql语句
1: 写一个包含连接和分组的sql

select COUNT(*) Count,s.Sname from [Admin] a Inner join [Subject] s on s.Id=a.Id group by s.Id,s.Sname

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

select Name,  CASE IsStatu when   'true' THEN '是' when   'false' THEN '否' END  from [Admin]

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

select CAST(Password) from dbo.Login

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

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

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

select Name from [Admin] where  Sid =( select Id from [Subject] where Sname='自然科学')

6：写一个视图的sql

create view [UserInfo]  AS select  Name,Password from [Admin] where IsStatu='false'

7：创建索引的sql

create index userId ON [Admin] (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

9:写一个触发器sql

create trigger Admin on [Admin]
instead of delete
as
begin 
declare @id int
select @id=Id from deleted
delete from [Subject] where Id=@id
delete from [Admin] where Id=@id
end
go 
delete from [Subject] where Id=1

10:写一个存储过程包含事务的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(@Sname,@Remark,@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_InsertSuject] '英语','无','2019-5-6 12:23:34',1

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

写一个存储过程，里面写一个循环，就可以了。主键你现在不是自增的，所以写语句的时候，就Insert到3个字段中。
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_auto_insertdata`$$
CREATE PROCEDURE `proc_auto_insertdata`()
BEGIN
DECLARE init_data INTEGER DEFAULT 1;
WHILE init_data <= 10 DO 
INSERT INTO t_1 VALUES(init_data, CONCAT('测试', init_data), init_data + 10);
SET init_data = init_data + 1;
END WHILE; 
END$$
DELIMITER ;
CALL proc_auto_insertdata();

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

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

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

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

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

select s.Name ,STUFF((select ',' +a.Name from [Admin] a where a.SId=r.Id for xml path('')),1,1,'') children  from [Subject] s group by s.Id ,s.Name

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

select a.Name,s.Sname from [Admin] a  inner join [Subject] s on a.SId=s.Id group by s.Id , a.Name,s.Sname  having s.Id=1

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

select a.Name,s.Sname from [Admin] a  inner join [Subject] r on a.SId=s.Id  group by s.Id  , a.Name,s.Sname order by  s.Id