创建SQL练习数据

drop table Student
go
drop table Course
go
drop table Teacher
go
drop table Score
go

create table Student(
StudentId int,
StudentName char(100),
Age int,
Sex int
)
go
create table Course (
   CouorseId int,
   CourseName char(100),
   TeacherId int
)
go
create table Score (
	StudentId int,
	CouorseId int,
	Score float
)
go
create table Teacher(
	TeacherId int,
	TeacherName char(100)
)


if (exists (select * from sys.objects where name = 'inser_Student'))
    drop proc inser_Student
go
create proc inser_Student @num int
as
declare @aa int;
set @aa =1;
while(@aa< @num)
begin
INSERT INTO dbo.Student(StudentName,Age,Sex) VALUES('陈小红'+convert(char(10),@aa),floor(rand()*50),0);
set @aa=@aa+1;
end

--调用、执行存储过程
exec inser_Student '1000';

select * from Student;
delete Student 
------------------------------------------------------
---------------Teacher
if (exists (select * from sys.objects where name = 'inser_Teacher'))
    drop proc inser_Teacher
go
create proc inser_Teacher @num int
as
declare @aa int;
set @aa =1;
while(@aa< @num)
begin
INSERT INTO dbo.Teacher(TeacherName) VALUES('张三'+convert(char(10),@aa));
set @aa=@aa+1;
end

--调用、执行存储过程
exec inser_Teacher '1000';

select * from Teacher;
delete Teacher 

---------------------------------------
--------------Course

if (exists (select * from sys.objects where name = 'inser_Course'))
    drop proc inser_Course
go
create proc inser_Course @num int
as
declare @aa int;
set @aa =1;
while(@aa< @num)
begin
INSERT INTO dbo.Course(CourseName,TeacherId) VALUES('课程名'+convert(char(10),@aa),(select top 1 TeacherId from Teacher order by NEWID() ));
set @aa=@aa+1;
end

--调用、执行存储过程
exec inser_Course '1000';

select * from Course;
delete Course 


--------------Score

if (exists (select * from sys.objects where name = 'inser_Score'))
    drop proc inser_Score
go
create proc inser_Score @num int
as
declare @aa int;
set @aa =1;
while(@aa< @num)
begin
INSERT INTO dbo.Score(StudentId,CouorseId,Score) VALUES((select top 1 CouorseId from dbo.Course order by NEWID()),(select top 1 StudentId from dbo.Student order by NEWID()),floor(rand()*100.00));
set @aa=@aa+1;
end

--调用、执行存储过程
exec inser_Score '1000';

select * from Score;
delete Score