************************************************************
为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 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;
