mysql增删改查语句简单案例

学生表(student)
sno	sname	sage
1	周杰伦	18
2	周润发	18
3	吴孟达	25
4	刘德华	25
5	李连杰	29

 

课程表(cource)
cno	cname
1	语文
2	数学
3	英语

 

学生成绩表（score）
sno	cno	score
1	1	60
1	2	61
2	1	80

 

创建student表，cource表和学生score表：

create table student (                 

sno int not null primary key,

sname varchar(25) not null,

sage int not null

);

create table cource (

cno int not null primary key,

cname varchar(25) not null,

);

create table score (

sno int not null ,

sname varchar(25) not null,

score int not null

);

在表中插入数据；

insert into student(sno,sname,sage)values('1','周杰伦','18');

insert into cource(cno,cname)values('1,'语文');

insert into score(sno,cno,score)values('1','1','60');

 

查询1课程比二课程成绩高的所有学生的编号；

select a.sno from score a,score b where

a.sno=b.sno

and a.cno=1

and b.cno=2

and a.score>b.score

 

查询所有学生的学号、姓名、选课数、总成绩；

select sno,sname,

(select count(cno) from score where score.sno=student.sno ),

(select sum(score) from score where score.sno=student.sno) from

student;

 

查询没有学完所有课程的学生学号、姓名；

 

select student.sno,student.sname from student,

(select count(cno) as n1 from cource  )t1,

(select sno,count(cno) as n2 from score  group by sno )t2

where t1.n1>t2.n2 and student.sno=t2.sno;

 

查询单科课程成绩小于61分的同学的学号、姓名:

select student.sno,student.sname from student,

(select sno from score where score <61) t1 where

student.sno=t1.sno

查询所有课程成绩小于71分的同学的学号、姓名:

select student.sno,student.sname from student where sno not in(

select sno from score where score>71

)

查询平均成绩大于60分的同学学号和平均成绩：

select sno，avg(score) from score group by sno having avg(score)>60