【数据库】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
转载自:https://juejin.cn/post/7111940523484512293