2021-01-25

##sql创建学生表，成绩表 欢迎补充

CREATE TABLE Student(
sno int PRIMARY KEY auto_increment, sname VARCHAR(55) NOT NULL, sage INT NOT NULL);truncate table student;INSERT into student VALUES(1001,“赵”,18);INSERT into student VALUES(1002,“钱”,18);INSERT into student VALUES(1003,“孙”,25);INSERT into student VALUES(1004,“李”,25);INSERT into student VALUES(1005,“周”,29);INSERT into student VALUES(1006,“吴”,22);CREATE TABLE Course( cno INT PRIMARY KEY auto_increment, cname VARCHAR(55) NOT NULL);INSERT INTO course VALUES(1,“语文”);INSERT INTO course VALUES(2,“数学”);INSERT INTO course VALUES(3,“英语”);CREATE TABLE Score(sno int ,cno int ,score DOUBLE not null,PRIMARY KEY(sno,cno));-- CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno)INSERT INTO score VALUES (1001,1,60);INSERT INTO score VALUES (1001,2,61);INSERT INTO score VALUES (1001,3,59);INSERT INTO score VALUES (1002,1,80);INSERT INTO score VALUES (1003,1,61);INSERT INTO score VALUES (1003,2,61);INSERT INTO score VALUES (1005,1,50);INSERT INTO score VALUES (1005,2,55);INSERT INTO score VALUES (1005,3,54);INSERT INTO score VALUES (1006,1,80);INSERT INTO score VALUES (1006,2,90);INSERT INTO score VALUES (1006,3,100);

create insert update

1、avg() max() min() sum() count()
—查询平均值，最大值，最小值，总和，总条数
select avg(s.sage) ,max(s.sage),min(s.sage),sum(s.sage),count(s.sno)
from student s

2、distinct
—查询不重复值
select distinct sage from student;
select count(distinct sage) from student;

3、where order by
—查询指定范围id内的数据
select sno,sname
from student
where sno in(2,5,6)/between/like
order by sno asc/desc

4、left join right join inner jion
—一科都没考的人
select stu.sno,stu.sname
from student stu
left join score sc
on stu.sno = sc.sno
where sc.sno is null；

5、简写
—参与考试的学生的成绩
select stu.sno,stu.sname,c.cname,s.score
from student stu,score s,course c
where stu.sno = s.sno and s.cno = c.cno
order by stu.sname,s.score desc;

select stu.sno,stu.sname,c.cname,s.score
from student stu 
left join score s on stu.sno = s.sno
left join course c on s.cno = c.cno
order by stu.sname,s.score desc

6、group by having
—查询平均分大于60分的学生编号，学生名，平均分
select s.sno,s.sname,avg(sc.score)
from score as sc,student as s
where s.sno = sc.sno
group by s.sno
having avg(sc.score) > 60;

select s.sno,s.sname,avg(sc.score) 
from score as sc 
inner join student as s 
on s.sno = sc.sno 
group by s.sno 
having avg(sc.score) > 60;	

7、子查询
—所有学生中有缺考的人
select stu.sno,stu.sname from student stu
left join score sc
on stu.sno = sc.sno
group by stu.sno
having count(sc.sno) <> (select count(*) from course);

8、—参加考试，但是有缺考的人
select stu.sno,stu.sname from student stu
left join score sc
on stu.sno = sc.sno
where sc.sno is not null
group by stu.sno
having count(sc.sno) <> (select count(*) from course);

9、—所有科目都不及格的人
select s.sname from student s where s.sno not in
(select s.sno from student s left join score sc on s.sno = sc.sno and sc.score > 60);

10、—查询所有学生的学号、姓名、选课数、总成绩;
select stu.sno,stu.sname,count(s.sno) ‘参考科目数’,sum(s.score) ‘总成绩’
from student stu
left join score s on stu.sno = s.sno
left join course c on s.cno = c.cno
group by stu.sno
order by sum(s.score) desc;