数据库语句-mysql

 

create table student( --学生表
sno varchar(10) primary key, --学生号
sname varchar(20), --名字
sage bigint(2), --年龄
ssex varchar(5) --性别
);

create table teacher( --老师表
tno varchar(10) primary key, --老师号
tname varchar(20) --老师名
);
create table course( --课程表
cno varchar(10), --课程号
cname varchar(20), --课程名
tno varchar(20), --老师名
constraint pk_course primary key (cno,tno)
);
create table sc( --成绩表
sno varchar(10), --学生号
cno varchar(10), --课程号
score FLOAT(4), --学生成绩
constraint pk_sc primary key (sno,cno)
);

COMMIT;

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT student.*,sc1.score,sc2.score from student,sc as sc1,sc as sc2 where student.sno=sc1.sno and student.sno=sc2.sno
and sc1.cno='c001' and sc2.cno='c002'
and sc1.score>sc2.score;

--2.查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩
select a.sno,b.sname,avg(a.score) from sc a
join student b on a.sno=b.sno
group by sno
having avg(a.score) >= 60;

--14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT sc1.sno,st.sname FROM
(SELECT * FROM sc WHERE cno IN
(SELECT cno FROM sc WHERE sno='s001') AND sno<>'s001') AS sc1,student AS st where st.sno=sc1.sno
GROUP BY sc1.sno HAVING COUNT(sc1.cno)=
(SELECT COUNT(cno) FROM sc GROUP BY sno HAVING sno='s001');

3.查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩(包含无成绩的)

select a.sno,a.sname,avg(b.score) from student a
left join sc b on a.sid=b.sno
group by sno
having avg(b.score) <=60;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.sno,b.sname,count(a.cno),sum(a.score) from sc a
join student b on a.sno=b.sno
group by sno;

5.查询"李"姓老师的数量

select count(tname) from teacher
where tname like '李%';

6.统计课程01分最高的前三名

select a.sno,b.sname,a.score from sc a

join student b on a.sno=b.sno where cno='01'

order by score desclimit 3;

7.统计课程分最高的前三名,不分课程(分数的前三，人数可能大于三)

解法一

select a.sname, a.score, a.cno from 

(select Student.sname, sc.score, sc.cno from Student

join SC on Student.sno = SC.sno) as a

join (select distinct sc.score from SC

order by score  desc limit 3) as b

on a.score = b.score;

解法二

select a.sname,b.score,b.cno from student a

join sc b on a.sno=b.sno 

join (select distinct sc.score from SC

order by score  desc limit 3) as c

on b.score = c.score;

8.统计分数出现次数前三的分数

select score, count(*) from SC

group by score

order by count(*) desc limit 3;

9.统计每个学生选课总数

select a.sno,b.sname,count(a.cno) from sc a

join student b on a.sno=b.sno

group by a.sno

 

10.每个老师教多少学生（一个教师给某个学生教两门课，计入两次）

 

select c.tid,c.tname,count(a.cno) from course a

 

join sc b on a.cno=b.cno

 

right join teacher c on a.tid=c.tid

 

group by tid

 

order by tid;