【数据库】Sql Server 快速创建表模拟系、课程、老师、学生以及分数

在本篇文章中，主要是创建表以及如何快速模拟表数据，方便快速用于查询测试和验证 相对于增删改，查询对于任何系统来说，都是主要的功能，并且是在有一定量数据后才能进行的一些操作，比如：学生成绩平均分统计等

• 主要知识点列表

  编号	语言或插件	知识点	说明
  1	sql server	create table	关键词，创建表
  2	sql server	indentity	自增长函数
  3	sql server	primary key	设置主键标识
  4	sql server	len()	统计长度
  5	sql server	rand()	随机函数，0~1之间的值
  6	sql server	substring()	截取字符串，substring('',1,1)
  7	sql server	convert()	数据类型转换，convert(int,'')
  8	sql server	left join	做关联，以左边表为主，会出现3 * 5=15条记录的情况，需要合理使用好on条件
  9	sql server	avg()	求平均值

【查询练习要求】

1）列出存在86分以上成绩的学生姓名以及课程编号

2）列出“C语言”成绩比平均成绩低的同学

【表设计和分析】

1）通过上面的要求，可以简单设计出如下5张表，暂时省略班级表

2）所属系表

系编号、系名称

• 创建表

create table system_table
(
    system_id int identity(1,1) primary key,
    system_name nvarchar(50)
)

• 模拟数据

--假设学校有如下系数据：商学系、管理系、计算机系、建筑系、艺术系，5个系记录
insert into system_table(system_name)
values('商学系'),('管理系'),('计算机系'),('建筑系'),('艺术系')

3）课程表 系编号、课程编号、课程名称

create table course_table
(
    course_id int identity(1,1) primary key,
    course_name nvarchar(50),
    system_id int
)

• 模拟数据

--这个时候的数据就不一样了，会有关联表，不同得系会有不同得课程
--此处就只模拟计算机系下面的3课程，C语言、软件工程、数据库
insert into course_table(course_name,system_id)
values('C语言',3),('软件工程',3),('数据库',3)

3）教师表

系编号、课程编号、教师编号、教师名称

create table teacher_table
(
    teacher_id int identity(1,1) primary key,
    teacher_name nvarchar(50),
    course_id int,
    system_id int
)

• 模拟数据

--同样有关联关系，此处模拟3条记录，张三、李四、王五
insert into teacher_table(teacher_name,course_id,system_id)
values('张三',1,3),('李四',2,3),('王五',3,3)

4）学生表

系编号、课程编号、教师编号、学生编号、学生姓名、性别

create table student_table
(
    student_id int identity(1,1) primary key,
    student_name nvarchar(50),
    sex int, --1=男生，0=女生
    teacher_id int,
    course_id int,
    system_id int
)

• 数据模拟

declare @myName nvarchar(50)
declare @surname_arr nvarchar(1000)
declare @name_arr nvarchar(1000)
set @surname_arr='王李张刘陈杨黄赵吴周徐孙马朱胡郭'
set @name_arr='爱梦想箱钰郁思裴诗紫弦锐月夏幻音影暗依舞玲滴羽樱莹'

declare @sn_length int
declare @rand_index int

--模拟数据：计算机系、C语言课程、张三老师下的30名学生
declare @student_count int
set @student_count=30
declare @index int 
set @index=0
declare @sex int
set @sex=1

while @index<@student_count begin
	set @myName='' --赋初始值，否则无法使用+加号
	
	--获取姓值
	if len(@surname_arr)>0 begin
		set @sn_length=len(@surname_arr)
		set @rand_index=convert(int,(rand()*@sn_length))-1
		set @myName+=substring(@surname_arr,@rand_index,1)
	end

	--获取名字
	if len(@name_arr)>0 begin
		set @sn_length=len(@name_arr)
		set @rand_index=convert(int,(rand()*@sn_length))-1
		set @myName+=substring(@name_arr,@rand_index,2)
	end
	
	--性别
	set @sex=convert(int,(rand()*2))
	
	insert into student_table(student_name,sex,teacher_id,course_id,system_id)
	values(@myName,@sex,1,1,3)
	
	set @index+=1
end

• 查询

select a.*,b.teacher_name,c.course_name,d.system_name
from student_table a
left join teacher_table b on b.teacher_id=a.teacher_id
left join course_table c on c.course_id=a.course_id
left join system_table d on d.system_id=a.system_id

5）学生成绩表

学生编号、系编号、课程编号、教师编号、分数

create table student_score_table
(
    score_id int identity(1,1) primary key,
    score int,
    student_id int,
    teacher_id int,
    course_id int,
    system_id int
)

• 模拟数据

declare @index int
set @index=1
declare @score int
set @score=0

while @index<=30 begin
	
	set @score=convert(int,(rand()*50))+50
	
	insert into student_score_table(score,student_id,teacher_id,course_id,system_id)
	values(@score,@index,1,1,3)

	set @index+=1
end

• 查询

select a.*,a2.student_name,b.teacher_name,c.course_name,d.system_name
from student_score_table a
inner join student_table a2 on a2.student_id=a.student_id
left join teacher_table b on b.teacher_id=a.teacher_id
left join course_table c on c.course_id=a.course_id
left join system_table d on d.system_id=a.system_id

【正式查询前面提到的要求】

1）列出存在86分以上成绩的学生名称和课程编号

select a.score,c.student_name,a.course_id,b.course_name
from student_score_table a
left join course_table b on b.course_id=a.course_id
left join student_table c on c.student_id=a.student_id
where a.score>86

2）列出“C语言”成绩比平均成绩低的同学

这里使用了，内联子查询的方式进行对比平均分

select a.score,b.avg_score,a.student_id,c.student_name
from student_score_table a
left join(
	--C语言课程的平均分
	select course_id,avg(score) as avg_score
	from student_score_table 
	where course_id=1
	group by course_id
) as b on b.course_id=a.course_id
left join student_table c on c.student_id=a.student_id
where a.score<b.avg_score