create database studentManage

go

use studentManage

go
create table Teacher(
	id char(5) primary key,
	姓名 nvarchar(10),
	性别 nchar(1) constraint tsex_chk check(性别 in ('男','女')),
	职称 nchar(3)constraint title_chk check(职称 in ('助教','讲师','副教授','教授')),
	领导 char(5)
)

--drop table Teacher

go

create table class(
	ID char(5) primary key,
	名称 varchar(40) ,
	班主任 char(5),
	辅导员 char(5)
)

go

create table student(
	学号 char(9) primary key,
	姓名 varchar(10),
	性别 nchar(1) constraint 性别 check(性别 in ('男','女')),
	年龄 int,
	入学年份 int,
	班级 char(5)
)

go

create view view_teacher
as
	select t.id,t.姓名,t.性别,t.职称,t1.姓名 as 领导名字,c.名称 from Teacher t 
	inner join class c on t.id=c.班主任
	left join Teacher t1 on t.领导 = t1.id
	union
	select t.id,t.姓名,t.性别,t.职称,t1.姓名 as 领导名字,c.名称 from Teacher t 
	inner join class c on t.id=c.辅导员
	left join Teacher t1 on t.领导 = t1.id
	
go


insert into Teacher(id,姓名,性别,职称,领导)
select * from 教师$

insert into class(id,名称,班主任,辅导员)
select * from 班级$

insert into student(学号,姓名,性别,年龄,入学年份,班级)
select * from 学生$


go

select * from view_teacher

go

create view view_class
as
	select c.ID,名称,t.姓名 as 班主任姓名,t1.姓名 as 辅导员姓名,COUNT(c.ID) as 班级总人数 from class c 
	left join student s on c.ID = s.班级
	inner join Teacher t on c.班主任 = t.id 
	inner join Teacher t1 on c.辅导员 = t1.id
	group by c.ID,名称,t.姓名 ,t1.姓名
go	

create view view_student
as
	select 学号,姓名,性别,年龄,入学年份,c.名称,
	(case (YEAR(GETDATE()) - s.入学年份) 
		when '1' then '大一' 
		when '2' then '大二' 
		when '3' then '大三' 
		when '4' then '大四' end)
	as 年级
	from student s
	left join class c on s.班级 = c.ID


go


create procedure view_teacher1222 @value char(20)
as
	select t.id,t.姓名,t.性别,t.职称,t1.姓名 as 领导姓名,c.名称 from Teacher t
	left join Teacher t1 on t.领导 = t1.id
	left join class c on t.id = c.班主任 or t.id = c.辅导员
	where t.id = @value or t.姓名 = @value or t.性别 = @value  or t.职称 = @value  or t.领导 = @value 
go


exec view_teacher1222 教授




select 姓名 from Teacher where id = '副教授' or 姓名 = '副教授' or 性别 = '副教授'  or 职称 = '副教授'  or 领导 = '副教授' 


go

create procedure view_teacher23333343333 @column char(20),@value char(20)
as
BEGIN  
    SET NOCOUNT ON;  
  
    DECLARE @sql NVARCHAR(MAX);  
  
    SET @sql = N'SELECT teacher.*,c.名称,t1.姓名 as领导名称 FROM Teacher 
    left join class c on teacher.id=c.辅导员 or teacher.id=c.班主任 
    left join teacher t1 on t.领导 = t1.id
    WHERE' + QUOTENAME(@column) + ' = @value';  
  
    EXEC sp_executesql   
        @sql,   
        N'@value NVARCHAR(128)',   
        @value = @value;  
END 

go

select * from Teacher where 姓名 = '钱向'
exec view_teacher23333343333 姓名,钱向

select * from Teacher


CREATE FUNCTION fact(@i int)
RETURNS int
AS
BEGIN
	declare @p int=1,@k int=1
	while @k<=@i
	begin
		set @p=@p*@k
		set @k=@k+1
	end
	return @p
	
	END
	GO



create function fun2(@banjiID char(5))
	returns table
	as return
	(
	select*from 学生 where 班级ID=@banjiID
	)





alter function fun3(banjiID char(5))
	RETURNS @snolist TABLE
	(	sno nvarchar(9),
		name nvarchar(9)
	)
	as
	begin
		if @banjiID is null
			insert into @snolist select 学号 ,姓名 from 学生
		else
			insert into @snolist select 学号,姓名 from 学生 where 班级ID=@banjiID
		return
end

select * from Teacher

insert into Teacher(id,姓名,性别,职称,领导) values('J0005','张小小','女','助教','J0001')
go

create proc update_teacher
@ID char(5),
@name nvarchar(10),
@sex nchar(1) = null,
@zhicheng nchar(3) = null,
@TID char(5)=null
as
	declare @tmp char(5) = ''
	select @tmp = id from Teacher where id = @ID	
	if(@tmp = '')
		begin
			print '不存在该教师'
		end
	else
		begin
			update Teacher set 姓名 = @name where id = @tmp
		end

go

exec update_teacher111 J0001,张三



select * from Teacher
select * from student
select * from class

create trigger leadercheck 
on teacher 
after update 
as 
begin 
	select  from Teacher
	rollback 
end