MySQL中表的创建

MySQL中表的创建
#创建班级表
create table if not exists classes (
id int primary key auto_increment comment ‘班级id’ ,
name VARCHAR(20) comment ‘班级名称’,
descr VARCHAR(100) comment ‘班级描述’

)；

#创建学生表
create table if not EXISTS student (
id int PRIMARY KEY auto_increment comment ‘学生id’,
sn int unique comment ‘学生学号’,
name VARCHAR(20) DEFAULT ‘unkown’ comment ‘学生姓名’, #默认为unkown
qq_mail VARCHAR(20) comment ‘学生邮箱’,
classes_id int comment ‘学生班级id’,
FOREIGN KEY (classes_id) REFERENCES classes(id) #外键约束

);

#创建课程表
create table if not EXISTS course (
id int PRIMARY KEY auto_increment comment ‘课程id’,
name VARCHAR(20) comment ‘课程名称’
);

#创建分数表
create table score (
id int PRIMARY key auto_increment comment ‘分数id’,
score decimal(3,1) comment ‘分数’,
student_id int comment ‘学生id’,
course_id int comment ‘班级id’,
FOREIGN key (student_id) REFERENCES student(id),
FOREIGN key (course_id) REFERENCES course(id)
);

#为班级表添加数据
INSERT into classes(name,descr) values
(‘计算机系2019级1班’,‘学习了计算机原理’),
(‘中文系系2019级2班’,‘学习了中国传统文化’),
(‘自动化系2019级5班’,‘学习了机械自动化’);

INSERT into claeese(name,descr) values
(),

insert into student(sn,name,qq_mail,classes_id) values
(‘00987’,‘小明’,‘ming@qq.com’,1),
(‘00017’,‘小花’,‘hua@qq.com’,2),
(‘00647’,‘小龙’,null,3),
(‘00365’,‘小胡’,null,1),
(‘00234’,‘小飞’,‘fei@qq.com’,2),
(‘00345’,‘小奚’,null,1);

INSERT into course(name) values
(“java”),(“中国传统文化”),(“计算机原理”),(“语文”),(“马克思主义”);

insert into score(score,student_id,course_id) VALUES
(80.7,1,1),(90.7,1,3),(77.7,1,4),
(98.4,2,1),(56.7,2,3),
(80.7,3,1),(87.9,3,2),(77.2,3,5),
(80.7,4,1),(92.7,5,3),(67.7,6,4),
(87.9,5,1),(66.7,5,3),(83.7,5,4),
(67.7,6,1);

#查询小奚的成绩
SELECT student.name,score.score from score
join student on student.id = score.student_id
where student.name = “小奚”;

#查询所有人的平均成绩以及其他信息
SELECT student.id,student.name,AVG(score.score) from score
join student on student.id = score.student_id
group by student.id;

#查询平均成绩最高的前三名
SELECT student.id,student.name,AVG(score.score) from score
join student on student.id = score.student_id
GROUP BY student.id
ORDER BY AVG(score.score) DESC
LIMIT 3;

#查询平均成绩排名第三的学生信息
SELECT student.id,student.name,AVG(score.score) from score
join student on student.id = score.student_id
GROUP BY student.id
ORDER BY AVG(score.score) desc
LIMIT 2,1;

#查询所有人的成绩以及其他信息
SELECT student.id,student.name,course.name,score.score from score
join student on student.id = score.student_id
join course on course.id = score.course_id
order by student.id;

SELECT student.name,student.id,course.name,score.score from score
LEFT JOIN student on student.id = score.course_id
LEFT JOIN course on course.id = score.course_id
ORDER BY student.id;

#显示所有“计算机原理”成绩比“Java”成绩高的学生信息，包含学生名称与两科成绩！
select stu.name, computer.score, java.score from student stu
join score computer on stu.id = computer.student_id
join score java on stu.id = java.student_id
join course cou1 on cou1.id = computer.course_id
join course cou2 on cou2.id = java.course_id
where cou1.name = ‘计算机原理’ and cou2.name = ‘Java’
and computer.score > java.score;

#查找"小奚" 同学的同班同学
SELECT classes_id from student where name = “小奚”;
SELECT name from student where classes_id = 1 ;

#解法二
SELECT stu1.name from student stu1
join student stu2 on stu2.name = “小奚”
where stu1.classes_id = stu2.classes_id;

#解法三
SELECT name from student where classes_id in(
SELECT classes_id from student where name = “小奚”
);

#查询所有比“计算机系2019级1班”平均分高的成绩信息。
SELECT * from score where score > (
SELECT AVG(score.score) from score
join student on student.id = score.student_id
join classes on classes.id = student.classes_id
where classes.name = “计算机系2019级1班”
);

#查询id小于3，或者名字为“语文”的课程
SELECT * from course
where id < 3 or name = “语文”;