大学期间的一些数据库代码O(∩_∩)O哈哈~

当时年少无知！

/*创建数据库*/
create database ST
ON
(
	NAME="ST",
	FILENAME="D:\SQL\ST.MDF",
	SIZE=5MB,
	MAXSIZE=50mb,
	FILEGROWTH=10%
)
LOG ON 
(
	NAME="ST_LOG",
	FILENAME="D:\SQL\ST.LDF",
	SIZE=2MB,
	MAXSIZE=5MB,
	FILEGROWTH=1MB
)


/*创建表*/
use ST
GO
create table Student
(
	Sno char(10) not null,
	Sname char(10) not null,
	primary key(Sno,Sname),
	Ssex bit null default 1,
	Sage int null,
	Sdept char(20) null default '空'
)


GO
create table Course
(
	Cno char(10) not null,
	Cname char(10) not null,
	primary key(Cno),
	Cpno char(2) null,
	Ccredit int null
)


GO
create table SC
(
	Sno char(10) not null,
	Cno char(10) not null,
	Grade int default 0,
	primary key(Sno,Cno)
)

GO
create table xsb1
(
	Sno char(10) not null,
	Cno char(10) not null,
	Grade int default 0,
	primary key(Sno,Cno)
)

/*修改表结构*/
ALTER TABLE xsb1
	add name char(8),
	Sdate char(10)

ALTER TABLE xsb1
	alter column Sdate smalldatetime

ALTER TABLE xsb1
	drop column Sdate

/*删除表*/
drop table xsb1


/*添加数据*/
/*指定列*/
use ST
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
	values('200215121','李勇',1,20,'CS'),
	('200215122','刘晨',0,19,'CS'),
	('200215123','王敏',0,18,'MA'),
	('200215125','张立',1,19,'IS')
/*不指定列*/
use ST
INSERT INTO Student
	values('200215121','李勇',1,20,'CS'),
	('200215122','刘晨',0,19,'CS'),
	('200215123','王敏',0,18,'MA'),
	('200215125','张立',1,19,'IS')
go
INSERT INTO Course
	values('1','数据库','5',4),
	('2','数学','',2),
	('3','信息系统','1',4),
	('4','操作系统','6',3),
	('5','数据结构','7',4),
	('6','数据处理','',2),
	('7','PASCAL语言','6',4)
go
INSERT INTO SC
	values('200215121','1',92),
	('200215121','2',85),
	('200215121','3',88),
	('200215122','2',90),
	('200215122','3',80)

/*更改数据*/
update Student
	set Sdept='CS',
		sno='200515125'
	where Sname='张立'
update Student
	set Sage =Sage+1

/*删除数据*/
delete from Student where Sno='200215123'
go
delete from Course where Cno='3'



/*查询数据库*/
/*第一种*/
use ST
select Sno,Sname,Ssex,Sage,Sdept
	from Student

/*第二种*/
go
select *
	from Student

/*查询部分列并计算列和备注列*/
go
select Sname,Sage,year(GETDATE())-Sage as '出生年月','武汉纺织大学' as '备注'
	from Student
/*查询并根据值来判定等级*/
--case方法
select Sno,Cno ,Grade =
	case 
		when Grade <=100 and Grade >= 90 then '优秀'
		when Grade <90 and Grade >= 80 then '良好'
		when Grade <80 and Grade >= 70 then '良'
		when Grade <70 and Grade >= 60 then '合格'
		else '不及格'
	end
from SC 
/*查询成绩小于60学生的学号*/
select distinct Sno from SC where Grade < 60;

/*查询学分为2到3之间的课程名*/
==范围查询==
select Cname from Course where Ccredit between 2 and 3;
==集合查询==
select Cname from Course where Ccredit in (2,3);
==多重条件查询==
select Cname from Course where Ccredit>=2 and Ccredit<=3;
select Cname from Course where Ccredit=2 or Ccredit=3;

/*查询所有不姓刘的学生姓名*/
select * from Student where Sname not like '刘%';

/*查询计算机系或者信息系年龄小于21的学生信息*/
select * from Student where (Sdept='CS' or Sdept='MS') and Sage <21;
select * from Student where Sdept in ('CS','MS') and Sage <21;

/*查询学生选课情况，查询结果按学号升序排列，同一学生按课程号降序排列*/
select * from SC  order by Sno ,Cno desc;             /*注意排序不要where命令*/

/*查询学生总人数和查询选修了课程的学生人数*/
==学生总人数==
select count(*) from Student;
==查询选修了课程的学生人数==
select count(distinct Sno) from SC;

/*查询学生200215121选修课程的总学分数*/
select sum(Grade) from SC where Sno = '200215121' and Grade is not null;

/*求各个课程号及相应的选课人数*/
select cno,count(Sno) from SC  group by Cno;





/*查询每个学生及其选修课程的情况*/
----自然连接----
select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;
----左连接----
select Student.*,SC.* from Student left outer join  SC on Student.Sno=SC.Sno;
----右链接----
select Student.*,SC.* from Student right outer join  SC on Student.Sno=SC.Sno;
----全连接----
select Student.*,SC.* from Student full outer join  SC on Student.Sno=SC.Sno;

/*查询计算机系选修了2号课程且成绩在80分以上的学生姓名及成绩*/
----自然连接----
select Student.Sname,SC.Grade from Student, SC where Student.Sno=SC.Sno and sc.Grade>=80 and SC.Cno='2' ;
----左连接----
select Student.Sname,SC.Grade from Student left outer join  SC on Student.Sno=SC.Sno where SC.Cno='2' and sc.Grade>=80;

/*查询计算机系选修了数据库且成绩在80分以上的学生姓名及成绩*/
select Student.Sname,SC.Grade from  Course,Student,SC where Student.Sno=SC.Sno and sc.Grade>=80 and Course.Cno=SC.Cno and Course.Cname='CS';

/*查询与“数据结构” 学分相同的课程名*/
select Course.Cname from Course where Course.Ccredit=(select Course.Ccredit from Course where Course.Cname='数据库' );
select Course.Cname from Course where Course.Ccredit  in (select Course.Ccredit from Course where Course.Cname='数据库' );
select a.Cname from Course a,Course b where a.Ccredit=b.Ccredit and b.Cname='数据库' and a.Cname != '数据库'

/*查询“李勇”可能选修的课程名*/
select Course.Cname from Course where Course.cno not in (select sc.Cno from SC where SC.Sno=(select Student.Sno from Student where Student.Sname='李勇'));
select Course.Cname from Course cross join Student where Student.Sname='李勇'
/*查询选修了数据库或者选修了数据结构的学生*/
select Student.Sname from Student where Student.Sno in (select SC.Sno from SC where sc.Cno in (select Course.Cno from Course where Course.Cname='数据库' or Course.Cname='数据结构' ));
select distinct Student.Sname from Student left outer  join SC cross join Course on Student.Sno=sc.Sno  where Student.Sno=SC.Sno and sc.Cno=Course.Cno and (Course.Cname='数据库' or Course.Cname='数据结构')

/*查询未选修“数据结构”课程的学生信息*/
select student.* from Student where Student.Sno not in(select SC.Sno from SC where SC.Cno in (select Course.Cno from Course where Course.Cname='数据结构'));




/*查询未选修“数据结构”课程的学生信息*/
--IN--
select student.* from Student where Student.Sno not in(select SC.Sno from SC where SC.Cno in (select Course.Cno from Course where Course.Cname='数据结构'));
--exists--
select student.* from Student where not exists (select SC.Sno from SC where exists (select Course.Cno from Course where Course.Cname='数据结构' and Student.Sno=SC.Sno and Course.Cno=sc.Cno));
--链接--
select Student.*,SC.* from Student left outer join Course on Course.Cname='数据结构' left outer join SC on SC.Sno=Student.Sno and SC.Cno=Course.Cno where SC.Cno is null

/*找出每个学生超过该门课程平均成绩的课程号*/
select x.Sno,x.Cno from SC x where exists(select y.Cno,avg(y.Grade) as avgm from SC y  group by y.Cno having x.Cno=y.Cno and x.Grade>=avg(y.Grade))

/*查询每门选修课程都及格的学生姓名*/
select distinct Student.Sname from Student left outer join SC on Student.Sno=SC.Sno where Student.Sno not in (select SC.Sno from SC where SC.Grade<60) and SC.Grade is not null

/*建立学生学号，姓名的视图Basic_S*/
create view Basic_S as select Student.Sno,Student.Sname from Student;

/*建立计算机系学生信息、选课信息及成绩的视图*/
create view Basic_C1 as select Student.*,Cno,Grade from Student,SC where Student.Sdept='CS' and Student.Sno=SC.Sno;

/*查询计算机系视图中不及格的学生及课程信息*/
/*create view Basic_C2 as select Student.*,Cno,Grade from Student,SC where Student.Sdept='CS' and Student.Sno=SC.Sno and SC.Grade<60;*/
select * from Basic_C1 where Basic_C1.Grade<60;

/*查询视图Basic_S中选修了2号课程的学生*/
select * from Basic_C1 where Basic_C1.Cno='2';
select * from Basic_S where Basic_S.Sno in(select SC.Sno from SC where SC.Cno='2');


--添加外键--
alter table SC add constraint Sno_foreign foreign key (Sno) references Student(Sno);
alter table SC add constraint Cno_foreign foreign key (Cno) references Course(Cno);
--建立登记表Stu
create table Stu(
	Sno int primary key,
	Sname char(8) not null unique,
	Sage int,
	Ssex char default '男',
	
	constraint check_Stu check(Sno>=1 and Sno<=99999 and Sage>=0 and Sage<=24 and Ssex in('男','女','未知'))
);
--修改约束条件--
alter table Stu
drop constraint check_Stu;
alter table Stu
add constraint check_Stu check(Sno>=90000 and Sno<=99999 and Sage>=0 and Sage<=40 and Ssex in('男','女','未知'));
--视图中增加一条记录--
insert into Basic_S values(
	'200515128','二豆子'
)
alter view Basic_S as select Student.Sno,Student.Sname from Student with check option;
insert into Basic_S values(
	'200515128','二豆子'
)




十一周
--用substring 查询以’数据’开头的课程信息--
select * from Course where substring(Cname,1,2)='数据'
--创建函数count_selectedStu,输入课程名，输出选课的人数--
create function count_selectedStu(@Cname char(20)) returns int
	begin
		declare @num int
		set @num=(
			select count(*) from Course,SC   where  sc.Cno=Course.Cno and Course.Cname=@Cname
		)
		return @num
	end
select dbo.count_selectedStu('数据库')
--创建函数count_selectedList,输入课程名，输出选课学生列表--
create function count_selectedList(@Cname char(20)) returns table
return(
	select student.* from Student left outer join Course on Course.Cname=@Cname  left outer join SC on SC.Cno=Course.Cno where sc.Sno=Student.Sno
	)
select * from dbo.count_selectedList('数据结构')
--创建一个函数random_name，随机生成指定长度的字符串--
create view Randint_view as select randint=rand(checksum(newid()))
create function random_name1(@num int) returns nvarchar(max)
begin
	declare @times int,@randint int,@char_one nvarchar(max);
	set @times=0;
	set @char_one='';
	while(@times<@num)
	begin
	set @randint=(select cast(ceiling(randint*122) as int) from Randint_view)
		if(@randint>=48 and @randint<=57)
		begin
		set @char_one=(select CONCAT(@char_one,char(@randint)))
		set @times=@times+1;
		end
		else if(@randint>=65 and @randint<=90)
		begin
		set @char_one=(select CONCAT(@char_one,char(@randint)))
		set @times=@times+1;
		end
		else if(@randint>=97 and @randint<=122)
		begin
		set @char_one=(select CONCAT(@char_one,char(@randint)))
		set @times=@times+1;
		end
	end
	return @char_one
end
select dbo.random_name1(20)




create proc crouse_zhuangtai1 @xc_sno char(10),@kc_cno char(20),@zhuangtai varchar(20) output,@num int output
as
begin
	select @num = 选课人数 from Course where Cno = @kc_cno
	if @num>=30
	begin
		set @zhuangtai = '该课程名额已满'
	end
	else
	begin
		insert into SC values(@xc_sno,@kc_cno,null)
		set @num = @num + 1
		update Course set 选课人数 = @num where Cno = @kc_cno
		set @zhuangtai = '选课成功'
	end
end 
declare @zhuangtaii char(20)
declare @num_1 int
exec crouse_zhuangtai1 '200515128','1',@zhuangtaii output,@num_1 output
PRINT replace((@zhuangtaii)+',当前选课人数为：'+convert(char(5),@num_1),' ','')
select @zhuangtaii as '插入情况',@num_1 as '当前选课人数'
PRINT @zhuangtaii

create trigger Sc_cheks on SC after insert
as
begin
	declare @checksno varchar(10),@checkcno varchar(10),@cnozt char(10),@snozt char(10)
	select @checksno = Sno from inserted
	select @checkcno = Cno from inserted
	select @cnozt = Cno from Course where Course.Cno = @checkcno
	select @snozt = Sno from Student where student.Sno = @checksno
	print @cnozt
	if (@cnozt is null) or (@snozt is null)
	begin
		delete from SC where Sno = @checksno and Cno = @checkcno
		print '插入失败，请检查学号或者课程号是否存在！！！'
	end
	else
	begin
		print '插入成功'
	end
end

insert into sc values('200215121','3',50)






--选课前三的课程名
select top(3) Cname,A from Course,(select Cno,count(*) as 'A' from SC  group by Cno) as CountA where course.Cno = CountA.Cno order by A desc
select top(3) Cname,count(*) as A from Course left join SC on Course.Cno=SC.Cno where SC.Sno is not null group by Cname order by A desc
select top(3) Cname,A.选课人数 from Course,(select Cno,count(*) as 选课人数 from sc GROUP BY Cno) as A where Course.Cno = A.Cno order by 选课人数 desc
--存储过程
create proc cunchu_test @Test_cno char(10),@Test_sno char(10)
as
begin
	declare @num int;
	select @num = 选课人数 from Course where Course.Cno = @Test_cno;
	if(@num>=30)
	begin
		print('选课失败，当前课程已满！！！');
	end
	else 
	begin
		insert into SC values(@Test_sno,@Test_sno,null);
		print('选课成功');
		set @num = @num + 1;
		update Course set 选课人数 = @num where Cno = @Test_cno;
	end
end
exec cunchu_test '5','200215122'