************************************************************
为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 = 20where sdept = 'cs';
d) 数学系所有学⽣成绩改成0
update sc set grade = 0where cno = '002';
e) 把低于总平均成绩的⼥同学成绩提⾼5分
f) 修改2号课程的成绩，若成绩⼩于75分提⾼5％，成绩⼤于75时提⾼
4％（两个语句实现，注意顺序）
update sc set grade=grade+grade*0.05where cno='002'and grade<75;
update sc set grade=grade+grade*0.04where 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 ade>=80;
m) 把所有学⽣学号和课程号连接追加到新表中
insert into STUsc(sno,cno)
select student.sno,sco 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 --条件b总001
student.sno=(select sno from sc
where grade<60group 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分）的学⽣姓名
ade,b.sname ,a.sno
from sc a right join student b ade > 90 and a.sno = b.sno;
+-------+--------+-------+
| grade | sname | sno |
| grade | sname | sno |
+-------+--------+-------+
| NULL | 丽丽 | NULL |
| NULL | 赵海 | NULL |
| 97 | 刘晨 | 08005 |
| 93 | 刘丹丹 | 08006 |
| NULL | 刘⽴ | NULL |
| NULL | 王江 | NULL |
| NULL | ⾼晓 | NULL |
| 100 | 张丽 | 08010 |
| NULL | NULL | NULL |
+-------+--------+-------+
ade,b.sname ,a.sno from sc a
join student b ade > 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 bo = '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 ao 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 = '⼥');
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 18and20; 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 10and19;
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;
发布评论