作者：小5聊基础
简介：一只喜欢全栈方向的程序员，欢迎咨询，尽绵薄之力答疑解惑
编程原则：Write Less Do More
- 主要知识点列表
| 编号 | 语言或插件 | 知识点 | 说明 |
|---|---|---|---|
| 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() | 求平均值 |
接着上篇文章内容
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
