SQLserver 练习题

m0_50985216

已于 2024-09-13 10:14:03 修改

阅读量589

点赞数 6

分类专栏： SqlServer 文章标签：数据库

于 2024-07-17 08:42:53 首次发布

本文链接：https://blog.youkuaiyun.com/m0_50985216/article/details/140436769

版权

SqlServer 专栏收录该内容

4 篇文章

订阅专栏

一、TSQL、函数、存储过程、游标

参考自博主会洗碗的CV工程师在此表示感谢

1、编写T-SQL程序，计算 1 + 2 − 3 + 4 − 5 … − 99 + 100 =?

--解法1：直接算
declare @i int=2, @sum int = 1, @flag int = -1, @top int=100, @str varchar(500) = '1'
while(@i<=100)
begin
	if(@i%2=0)
	begin
		set @sum += @i;
		set @str += '+'+cast(@i as varchar(5));
	end
	else
	begin
		set @sum += @i*@flag;
		set @str += '-'+cast(@i as varchar(5));
	end
	set @i+=1;
end
print @str + '=' + str(@sum)


--解法2：定义成函数
create function Sum0_100( @top int) returns varchar(500)
begin
	declare @i int=2, @sum int = 1, @flag int = -1, @str varchar(500) = '1'
	while(@i<=100)
	begin
		if(@i%2=0)
		begin
			set @sum += @i;
			set @str += '+'+cast(@i as varchar(5));
		end
		else
		begin
			set @sum += @i*@flag;
			set @str += '-'+cast(@i as varchar(5));
		end
		set @i+=1;
	end
return @str + '=' + str(@sum)
end

select dbo.Sum0_100(100)

2、写一个求三个数最大值的函数，输入为三个整数，输出为其中的最大值。

create function max_3(@a int, @b int, @c int) returns int
begin
	declare @maxValue int = case when @a>@b and @a >@c then @a
							when @b>@a and @b >@c then @b
							else @c end;
	return @maxValue
end
select dbo.max_3(1,4,3)

3、写一个T-SQL函数 𝑓𝑎𝑡，利用标准体重计算公式，根据身高判断体重是否合乎标准

create function Fn_Fat(@height int, @weight int, @gender nvarchar(1)) returns varchar(40)
as 
begin 
	declare @str nvarchar(20), @weight_level int= case when @gender = '男' then @height-105 else @height-100 end
 
		if(@weight_level>@weight)
			set @str = '不标准，低于标准体重' + cast(@weight_level-@weight as varchar(10)) + '公斤'
		else if(@weight_level<@weight)
			set @str = '不标准，高于标准体重' + cast(@weight-@weight_level as varchar(10)) + '公斤'
		ELSE 
			set @str = '标准'
 
	return @str
 
end
go
select dbo.Fn_Fat(170,70,'男');

4、写一个T-SQL多语句表值函数 𝑏𝑙𝑜𝑜𝑑，根据父母的血型列出孩子的所有可能血型

create function fn_BloodRejedge(@fa nvarchar(10), @mo nvarchar(10)) returns @blood table (possibleBlood nvarchar(10))
as
begin
	if((@fa = 'A' and @mo = 'A') or (@fa = 'A' and @mo = 'O') or (@fa = 'O' and @mo = 'A'))
		insert into @blood values('A'),('O')
	--else if其他条件请自己判断，懒得写了
	else
		insert into @blood values('A'),('O'),('AB'),('B')
	return;
end
go
select * from dbo.fn_BloodRejedge('A','A')

5、写一个GetMax函数

create function Max_4num(@a int, @b int, @c int, @d int) returns int
as
begin
	declare @str varchar(10) = ''
	declare @tempTable table(numColumn int)
	insert into @tempTable values(@a),(@b),(@c),(@d)
	select @str += cast(numColumn as varchar(1)) from @tempTable order by numColumn desc
	return cast(@str as int)
end

select dbo.Max_4num(0,5,1,4) --5410

6、写一个存储过程AreYouFat

create procedure Proce_AreYouFat (@weight float, @height Float, @msg nvarchar(20) output, @bmi float output)
AS 
BEGIN
	set @msg = ''
	set @bmi = @weight/power(@height,2);
	if(@bmi<18.5)
		set @msg = '您的体型过轻，请加强营养';
	else if(@bmi<=23.9)
		set @msg = '您的体型完全正常，请注意保持';
	else if(@bmi<=27.9)
		set @msg = '您的体型偏胖，请注意饮食';
	else 
		set @msg = '您的体型超重，请注意节食';
END 

declare @msg nvarchar(20), @bmi float
exec dbo.Proce_AreYouFat 60.0,1.78,@msg output,@bmi output
select 'bmi指数为'+cast(@bmi as nvarchar(20))
select '建议为'+@msg
drop procedure Proce_AreYouFat

7、写一个T-SQL多语句表值函数 𝑟𝑎𝑛𝑘𝑠𝑖𝑔𝑛

create function f_ranks(@junxian nvarchar(10)) returns @sign_table table(signcolumn nvarchar(20))
as
begin
	if(@junxian like '_将')
		insert into @sign_table values('一花')
	else if(@junxian like '_校')
		insert into @sign_table values('二杠')
	else
		insert into @sign_table values('一杠')

	if(@junxian like '上_')
		insert into @sign_table values('三星')
	else if(@junxian like '中_')
		insert into @sign_table values('二星')
	else if(@junxian like '少_')
		insert into @sign_table values('一星')
	else
		insert into @sign_table values('四星')

	return;
end

select * from f_ranks('中尉')

8、创建MissWife表，写一个存储过程ReversePoem

create procedure p_ReversePoem
as
begin
	declare @str nvarchar(500) = ''  --拼接上的诗句，是一个字符串
	declare @shiju nvarchar(20)  --负责接收游标的数据
	declare line_cursor cursor for select linetext from dbo.MissWife  --声明游标
	print '思妻诗'
	open line_cursor	--开启游标
	fetch next from line_cursor into @shiju --接收游标的数据
	while(@@FETCH_STATUS=0)
	begin
		print @shiju
		set @str += @shiju
		fetch next from line_cursor into @shiju
	end
	close line_cursor
	deallocate line_cursor

	print ''
	print '思夫诗'
	declare @r int = 1
	set @str = reverse(@str)
	while(@r<len(@str))
	BEGIN 
		print substring(@str,@r,7)
		set @r += 7
	END	
end

exec p_ReversePoem

9、利用 𝑆𝑒𝑎𝑠𝑜𝑛 表，参照结果，写一个存储过程𝑆𝑒𝑎𝑠𝑜𝑛𝐿𝑎𝑛𝑑𝑆𝑐𝑎𝑝

alter procedure p_seasonLandScape
as
begin
	declare @shiju nvarchar(100);
	declare line_cursor cursor for select linetext from Season
	open line_cursor
	fetch next from line_cursor into @shiju
	while @@FETCH_STATUS=0
	begin
		print @shiju
		fetch next from line_cursor into @shiju
	end
	close line_cursor
	deallocate line_cursor

	declare line2_cursor cursor for select linetext from Season
	open line2_cursor
	fetch next from line2_cursor into @shiju
	while @@FETCH_STATUS=0
	begin
		print ''
		print substring(@shiju,1,7) + ',' + substring(@shiju,4,7)
		print substring(reverse(@shiju),1,7) + ',' + substring(@shiju,4,7)
		fetch next from line2_cursor into @shiju
	end
	close line2_cursor
	deallocate line2_cursor
end

exec p_seasonLandScape

10、写一个存储过程 𝑀𝑎𝑔𝑖𝑐𝑁𝑢𝑚𝑏𝑒

--写一个存储过程 𝑀𝑎𝑔𝑖𝑐𝑁𝑢𝑚𝑏𝑒
alter procedure p_MagicNumber
as
begin
	declare @i1 varchar(10) = '1', @i2 int = 1
	while @i2 <=9
	begin
		print @i1 +'*' +'8 + ' + cast(@i2 as varchar(10)) + '=' + cast(cast(@i1 as int) * 8 + @i2 as varchar(10) )
		set @i2+=1
		set @i1 +=  cast(@i2 as varchar(10))
	end
end

exec p_MagicNumber

11、利用𝐶𝑜𝑠𝑚𝑒𝑡𝑖𝑐 表，参照结果，写一个存储过程 𝐶𝑜𝑠𝑚𝑒𝑡𝑖𝑐𝑅𝑒𝑝𝑜𝑟

alter procedure p_Cosmetic
as
begin
	declare @bigClass nvarchar(20), @smallClass nvarchar(20), @Goods nvarchar(20)--, @lineText nvarchar(20)
	declare cursor1 cursor for select distinct 大类 from Cosmetic
	open cursor1
	fetch next from cursor1 into @bigClass
	print '-------------'
	while @@FETCH_STATUS=0
	begin
		print ''
		print '	'+@bigClass + '化妆品'

		declare @str nvarchar(40) = ''
		declare cursor2 cursor for select distinct 小类 from Cosmetic where 大类= @bigClass
		open cursor2
		fetch next from cursor2 into @smallClass
		while @@FETCH_STATUS=0
		begin
			set @str += '	'+@smallClass+':'

			declare cursor3 cursor for select distinct 品名 from Cosmetic where 大类= @bigClass and 小类 = @smallClass
			open cursor3
			fetch next from cursor3 into @Goods
			while @@FETCH_STATUS=0
			begin
				set @str += '  '+ @Goods
				fetch next from cursor3 into @Goods
			end
			print @str
			close  cursor3
			deallocate cursor3
			set @str = ''
			fetch next from cursor2 into @smallClass
		end
		close  cursor2
		deallocate cursor2

		fetch next from cursor1 into @bigClass
	end
	close  cursor1
	deallocate cursor1
end

exec p_Cosmetic

12、写一个存储过程 𝐹𝑖𝑏𝑜𝑛𝑎𝑐𝑐

alter PROCEDURE p_Fibonacci(@num int)
AS 
BEGIN 
	declare @a1 int = 0, @a2 int = 1, @i int = 1, @sum int = 0, @he int = 0, @str varchar(50) = '0'
	while @i < =@num
	BEGIN 
		set @sum += @a2
		set @he = @a1+@a2
		set @str += '+'+cast(@a2 as varchar)
		print 'f('+cast(@i as varchar(2))+')='+@str + '=' + cast(@sum as varchar)
		set @a1 = @a2
		set @a2 = @he
		set @i+=1
	END	
END

exec p_Fibonacci 9

13、建立存储过程 𝑆𝑎𝑛𝐺𝑢𝑜𝑅𝑒𝑝𝑜𝑟

alter procedure p_General
as
begin
	declare @lineCursor1 varchar(50)
	declare cursor1 cursor for select distinct 国籍 from general
	open cursor1
	fetch next from cursor1 into @lineCursor1
	while @@FETCH_STATUS=0
	begin
		print @lineCursor1+'国代表有'

		declare @str2 varchar(50) = '武将有：',@lineCursor2 varchar(50)
		declare cursor2 cursor for select distinct 姓名 from general where 武力>=80 and 国籍= @lineCursor1
		open cursor2
		fetch next from cursor2 into @lineCursor2
		while @@FETCH_STATUS=0
		begin
			set @str2 += ' '+@lineCursor2
			fetch next from cursor2 into @lineCursor2
		end
		print @str2
		close cursor2
		deallocate cursor2
		--set @str2 = ''

		declare @str3 varchar(50) = '谋士有：',@lineCursor3 varchar(50)
		declare cursor3 cursor for select distinct 姓名 from general where 智力>=80 and 国籍= @lineCursor1
		open cursor3
		fetch next from cursor3 into @lineCursor3
		while @@FETCH_STATUS=0
		begin
			set @str3 += ' '+@lineCursor3
			fetch next from cursor3 into @lineCursor3
		end
		print @str3
		close cursor3
		deallocate cursor3
		--set @str3 = ''

		declare @str4 varchar(50) = '美女有：',@lineCursor4 varchar(50)
		declare cursor4 cursor for select distinct 姓名 from general where 美貌>=60 and 国籍= @lineCursor1
		open cursor4
		fetch next from cursor4 into @lineCursor4
		while @@FETCH_STATUS=0
		begin
			set @str4 += ' '+@lineCursor4
			fetch next from cursor4 into @lineCursor4
		end
		print @str4
		close cursor4
		deallocate cursor4
		--set @str4 = ''

		fetch next from cursor1 into @lineCursor1
	end
	close cursor1
	deallocate cursor1
end

exec p_General

二、触发器

1、禁用、开启触发器

ALTER TRIGGER C_update  
ON COURSE  
DISABLE;--禁用
--ENABLE  开启触发器

2、练习

2.1、SQLserver，为COURSE表创建update触发器C_update,当course表的CNO和CNAME （第1列和第2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事物。其余的列（第3、4、5列）被更新时，触发器将更新前后的数据写入C_Upinfo表中

CREATE TRIGGER C_update  
ON COURSE  
AFTER UPDATE  
AS  
BEGIN   
    -- 检查是否有CNO或CNAME被更新  
    IF EXISTS (  
        SELECT 1  
        FROM inserted i  
        INNER JOIN deleted d ON i.CNO = d.CNO  
        WHERE i.CNO <> d.CNO OR i.CNAME <> d.CNAME  
    )  
    BEGIN  
        -- 抛出一个错误，提示CNO和CNAME不能更新  
        RAISERROR('CNO 和 CNAME 不能被更新。', 16, 1);  
        -- 注意：由于这是AFTER UPDATE触发器，并且已经抛出了错误，所以整个事务将自动回滚  
        -- 不需要显式执行ROLLBACK TRANSACTION;  
    END  
    ELSE  
    BEGIN  
        -- 如果只是其他列被更新，则将更新前后的数据写入C_Upinfo表  
        INSERT INTO C_Upinfo (  
            CNO,  
            OldCNAME,  
            NewCNAME,  
            -- 假设还有其他需要记录的列，如OldCredit, NewCredit等  
            -- 这里以OldCredit, NewCredit为例  
            OldCredit,  
            NewCredit  
        )  
        SELECT  
            i.CNO,  
            d.CNAME AS OldCNAME,  
            i.CNAME AS NewCNAME,  
            d.Credit AS OldCredit,  
            i.Credit AS NewCredit  
        FROM  
            inserted i  
            INNER JOIN deleted d ON i.CNO = d.CNO;  
        -- 注意：这里假设C_Upinfo表已经存在，并且有足够的列来存储旧值和新值  
    end  
END;

2.2、为Products表创建一个触发器，当产品的价格或库存量发生更新时，在ProductChanges表中记录产品的ID、变更时间、变更前的价格和库存量以及变更后的价格和库存量

CREATE TRIGGER trg_AfterProductUpdate  
ON Products  
AFTER UPDATE  
AS  
BEGIN    
    -- 假设我们只关心Price和StockQuantity字段的变更  
    INSERT INTO ProductChanges (ProductID, ChangeTime, OldPrice, NewPrice, OldStock, NewStock)  
    SELECT   
        d.ProductID,  
        GETDATE(),  
        CONVERT(DECIMAL(10, 2), d.Price),  
        CONVERT(DECIMAL(10, 2), i.Price),  
        d.StockQuantity,  
        i.StockQuantity  
    FROM   
        inserted i  
        INNER JOIN deleted d ON i.ProductID = d.ProductID  
    WHERE   
        i.Price <> d.Price OR i.StockQuantity <> d.StockQuantity;  
END;

2.3、为Orders表创建一个触发器，确保在尝试插入新订单时，订单中的产品ID必须在Products表中存在。如果产品ID不存在，则回滚插入操作并抛出一个错误消息。

CREATE TRIGGER trg_PreventInvalidOrderInsert  
ON Orders  
INSTEAD OF INSERT  
AS  
BEGIN  
    -- 检查订单中的每个产品ID是否存在于Products表中  
    IF NOT EXISTS (  
        SELECT 1  
        FROM Products p  
        INNER JOIN inserted i ON p.ProductID = i.ProductID  
    )  
    BEGIN  
        -- 如果存在无效的产品ID，则抛出错误并回滚（尽管在INSTEAD OF触发器中通常不需要显式回滚）  
        RAISERROR('订单中包含无效的产品ID。', 16, 1);  
        RETURN; -- 退出触发器，不执行任何插入操作  
    END  
  
    -- 如果所有产品ID都有效，则执行插入  
    INSERT INTO Orders (OrderID, ProductID, ...) -- 假设还有其他字段  
    SELECT OrderID, ProductID, ... -- 从inserted表中选择相应的字段  
    FROM inserted;  
END;

三、自定义函数

1、创建一个标量函数fn_GetAge，该函数接收两个参数：出生日期（@BirthDate DATE）和当前日期（可选，如果不传则默认为当前系统日期），并返回计算出的年龄（整数）

CREATE FUNCTION fn_GetAge  (  
    @BirthDate DATE,  
    @Today DATE = NULL  )  
RETURNS INT  
AS  
BEGIN  
    DECLARE @Age INT;  
    IF @Today IS NULL  
        SET @Today = GETDATE();  
    SET @Age = DATEDIFF(YEAR, @BirthDate, @Today);  
    -- 减去超过出生日的天数来修正年龄  
    IF (MONTH(@Today) < MONTH(@BirthDate)) OR (MONTH(@Today) = MONTH(@BirthDate) AND DAY(@Today) < DAY(@BirthDate))  
        SET @Age = @Age - 1;  
    RETURN @Age;  
END;  
GO  
  
-- 调用示例  
SELECT dbo.fn_GetAge('1990-05-15') AS Age;

2、创建一个内联表值函数fn_GetEmployeesByDepartment，该函数接收一个部门ID（@DepartmentID INT）作为参数，并返回该部门下所有员工的ID、姓名和职位。

CREATE FUNCTION fn_GetEmployeesByDepartment  
(  
    @DepartmentID INT  
)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT EmployeeID, Name, Position  
    FROM Employees  
    WHERE DepartmentID = @DepartmentID  
);  
GO  
  
-- 调用示例  
SELECT * FROM dbo.fn_GetEmployeesByDepartment(1);

3、创建一个多语句表值函数fn_GetEmployeeDetails，该函数也接收一个员工ID（@EmployeeID INT）作为参数，并返回该员工的详细信息，包括姓名、职位、部门名称以及入职日期。

CREATE FUNCTION fn_GetEmployeeDetails  
(  
    @EmployeeID INT  
)  
RETURNS @Result TABLE  
(  
    EmployeeName NVARCHAR(100),  
    Position NVARCHAR(100),  
    DepartmentName NVARCHAR(100),  
    HireDate DATE  
)  
AS  
BEGIN  
    INSERT INTO @Result (EmployeeName, Position, DepartmentName, HireDate)  
    SELECT   
        e.Name,   
        e.Position,   
        d.Name AS DepartmentName,   
        e.HireDate  
    FROM Employees e  
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID  
    WHERE e.EmployeeID = @EmployeeID;  
  
    RETURN;  
END;  
GO  
  
-- 调用示例  
SELECT * FROM dbo.fn_GetEmployeeDetails(1);

4、输入产品ID返回该产品的所有信息

--函数功能：输入产品ID返回该产品的所有信息
GO
CREATE FUNCTION inquire_ALLInfomation
(@ProductId nvarchar(255))
RETURNS @tb TABLE(
	产品ID NVARCHAR(255),
	产品名称 NVARCHAR(255),
	子类别 NVARCHAR(255),
	类别 NVARCHAR(255)
)
AS
BEGIN
	INSERT INTO @tb SELECT * FROM [产品数据] 
					WHERE [产品ID]=@ProductId
	RETURN
END