##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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/252350.html
標籤：基礎類
上一篇：求幫忙