mysql学生表选课表课程表_mysql查询（学生表、课程表、选课表）

************************************************************

为sc表中的sno和cno创建外键

alter table sc add foreign key(sno) references student(sno);

alter table sc add foreign key(cno) references course(cno);

************************************************************

a) 插入如下学生记录(学号：95030，姓名：李莉，年龄：18)

insert into student(sno,sname,sage) values('95030','李莉',18);

b) 插入如下选课记录(95030，1)

insert int sc(sno,cno) values('95030','001');

c) 计算机系学生年龄改成20

update student set sage = 20 where sdept = 'cs';

d) 数学系所有学生成绩改成0

update sc set grade = 0 where cno = '002';

e) 把低于总平均成绩的女同学成绩提高5分

f) 修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高

4％(两个语句实现，注意顺序)

update sc set grade=grade+grade*0.05 where cno='002' and grade<75;

update sc set grade=grade+grade*0.04 where cno='002' and grade>75;

g) 删除95030学生信息

delete from student where sno = '95030';

h) 删除SC表中无成绩的记录

delete from sc where grade is null;

i) 删除张娜的选课记录

delete from sc where sno = (select sno from student where sname = '张娜');

j) 删除数学系所有学生选课记录

delete from sc where cno = '002';

k) 删除不及格的学生选课记录

delete from sc where grade < 60;

l) 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

insert into stu(sno,sname,ssex)

select student.sno,student.sname,student.ssex

from student,sc

where student.sno=sc.sno and sc.grade>=80;

m) 把所有学生学号和课程号连接追加到新表中

insert into STUsc(sno,cno)

select student.sno,sc.cno from student,sc

where student.sno = sc.sno;

n) 所有学生年龄增1

update student set sage = sage+1;

o) 统计3门以上课程不及格的学生把

相应的学生姓名、系别追加到另外一个表中

insert into stu(sname,sdept) --插入表中

select sname,sdept from student,sc --选择出列名

where --条件

student.sno=(select sno from sc

where grade<60 group by sno having count(*)>3);

查询每个学生及其选课情况；

select sno,cno from sc;

将STUDENT,SC进行右连接

查询有不及格的学生姓名和所在系

select a.sname,a.sdept from student a right join sc b on a.sno = b.sno;

查询所有成绩为优秀(大于90分)的学生姓名

select a.grade,b.sname ,a.sno

from sc a right join student b on a.grade > 90 and a.sno = b.sno;

+-------+--------+-------+

| grade | sname | sno |

+-------+--------+-------+

| NULL | 丽丽 | NULL |

| NULL | 赵海 | NULL |

| 97 | 刘晨 | 08005 |

| 93 | 刘丹丹 | 08006 |

| NULL | 刘立 | NULL |

| NULL | 王江 | NULL |

| NULL | 高晓 | NULL |

| 100 | 张丽 | 08010 |

| NULL | NULL | NULL |

+-------+--------+-------+

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

join student b on a.grade > 90 and a.sno = b.sno;

+-------+--------+-------+

| grade | sname | sno |

+-------+--------+-------+

| 97 | 刘晨 | 08005 |

| 93 | 刘丹丹 | 08006 |

| 100 | 张丽 | 08010 |

+-------+--------+-------+

查询既选修了2号课程又选修了3号课程的

学生姓名、学号；

select sname,sno from student where sno in(

select sno from sc

where cno = '003'

and

sno in(select sno from sc where cno = '006'));

----或者

select a.sname,a.sno from student a join sc b where a.sno = b.sno and cno = '003' and b.sno in(select sno from sc where cno = '006');

查询和刘晨同一年龄的学生

select sno ,sname from student where sage = (select sage from student where sname = '刘晨');

选修了课程名为“数据库”的学生姓名和年龄

select a.sname,a.sage from student a join sc b on a.sno = b.sno and b.cno = '001

查询其他系比IS系任一学生年龄小的学生名单

select sname,sage from student where sage < any

(select sage from student where sdept = 'is');

查询其他系中比IS系所有学生年龄都小的学生名单

select sname,sage from student where sage < all

(select sage from student where sdept = 'is');

查询选修了全部课程的学生姓名

select sname,sno

from student where sno in(select sno from sc);

查询计算机系学生及其性别是男的学生

select *from student where sdept = 'cs' and ssex = '男';

查询选修课程1的学生集合和选修2号课程学生集合的差集

select sno from sc where cno = '001' and sno not

in(select sno from sc where cno = '002');

查询李丽同学不学的课程的课程号

select cno from course where cno not in(select a.cno from sc a join student b on a.sno = b.sno and b.sname = '李丽');

查询选修了3号课程的学生平均年龄

select avg(sage) from student where sno in(select sno from sc where cno = '003');

求每门课程学生的平均成绩

select avg(grade) from sc group by cno;

统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

select cno ,count(*) from sc group by cno having count(sno) > 3;

查询学号比刘晨大，而年龄比他小的学生姓名。

select sname from student where

sno > (select sno from student where sname = '刘晨')

and sage < (select sage from student where sname = '刘晨')；

求年龄大于女同学平均年龄的男同学姓名和年龄

select sname,sage from student where ssex = '男' and

sage > (select avg(sage) from student where ssex = '女');

求年龄大于所有女同学年龄的男同学姓名和年龄

select sname,sage from student where ssex = '男' and

sage > all (select sage from student where ssex = '女');

查询95001和95002两个学生都选修的课程的信息

select *from sc where sno in ('95001','95002');

-----------------------------------------------------

alter table student add test varchar(20); -

alter table student drop test; -

-----------------------------------------------------

为学生表按学号建立唯一索引

mysql> create UNIQUE INDEX stusno ON student(sno);

Query OK, 10 rows affected (0.61 sec)

Records: 10 Duplicates: 0 Warnings: 0

为course表按课程号升序(默认)建立唯一索引

mysql> create UNIQUE INDEX coucno ON course(cno);

Query OK, 7 rows affected (0.38 sec)

Records: 7 Duplicates: 0 Warnings: 0

为sc表按学号升序和课程号降序建立唯一索引

mysql> create UNIQUE INDEX scno ON sc(sno asc,cno desc);

Query OK, 11 rows affected (0.34 sec)

Records: 11 Duplicates: 0 Warnings: 0

//删除索引

drop index scno on sc;

//插入学生信息

insert into student (sno,sname,sage) values('95030','；李莉',18);

a) 查询全体学生的学号和姓名

select sno,sname from student;

b) 查询全体学生的详细记录

select *from student;

c) 查询所有选修过课程的学生学号

select distinct sno from sc ;

d) 查询考试有不及格的学生学号

select sno from sc where grade < 60;

e) 查询不是信息系(IS)、计算机系(CS)的学生性别、年龄、系别

select sname,ssex,sdept from student where sdept not in('is','cs');

f) 查询选修了4号课的学生学号和成绩，结果按成绩降序排列

select sno,grade from sc where cno = '004' order by grade desc;

g) 查询每个课程号和相应的选课人数

select cno,count(cno) as cnonumed from sc group by cno;

h) 查询计算机系(CS)的学生姓名、年龄、系别

select sname,sage,sdept from student where sdept = 'cs';

i) 查询年龄18－20岁的学生学号、姓名、系别、年龄；

select sname,sage,sdept from student where sage between 18 and 20;

j) 查询姓刘的学生情况

select *from student where sname like '刘%';

k) 查询既选修3号课程，又选修6号课程的学生学号和成绩

select sno ,grade from sc

where cno = '003'

and

sno in(select sno from sc where cno = '006');

l) 查询学生的姓名和出生年份(今年2003年)

select sname,sbirthday from student;

m) 查询没有成绩的学生学号和课程号

select sno,cno from sc where grade is null;

n) 查询总成绩大于200分的学生学号

select sno,sum(grade) from sc

group by sno having sum(grade) > 200;

o) 查询每门课程不及格学生人数

select cno,count(sno) from sc

where grade < 90

group by cno;

p) 查询不及格课程超过3门的学生学号

select cno,count(sno) from sc

where grade < 60

group by cno

having count(sno) > 3;

q) 查询年龄在10到19岁之间的学生信息

select *from student

where sage between 10 and 19;

r) 查询全体学生情况，按所在系升序排列，同一个系的学生按年龄降序排列

select *from student order by sdept asc,sage desc;

s) 查询选了1号课程的学生平均成绩

select cno,avg(grade) from sc where cno = '001' group by cno;

+-----+------------+

| cno | avg(grade) |

+-----+------------+

| 001 | 92.6667 |

+-----+------------+

1 row in set (0.00 sec)

mysql> select cno,avg(grade) from sc group by cno having cno = '001';

+-----+------------+

| cno | avg(grade) |

+-----+------------+

| 001 | 92.6667 |

+-----+------------+

t) 查询选了3号课程的学生的最高分

select cno, max(grade)

from sc where cno = '003' group by cno;

u) 查询每个同学的总成绩

select sno,sum(grade)

from sc

group by sno;