MySQL练习题，记录

一、写出相应的SQL语句：

（1）向student表增加入学时间列，列名为sentrance，数据类型为日期型。

（2）删除student表中的入学时间列，列名为sentrance。

（3）修改student表中的sage列，列名为ssage，类型为INT，不允许取空值，默认值为21。

（4）为course表增加表级完整性约束条件——cname列的值不允许重复。

（1）ALTER TABLE student ADD sentrance DATE;

（2）ALTER TABLE student DROP sentrance;

（3）ALTER TABLE student CHANGE COLUMN sage ssage INT NOT NULL DEFAULT 21;

（4）ALTER TABLE course ADD UNIQUE(cname);

基于课本52页图2.4中的student、course、sc表，使用SQL查询语句完成下列各题：

（1）查询student表中的所有记录。

（2）查询计算机系的学生的姓名和年龄。

（3）查询没有先修课的课程的名称。

（4）查询计算机系、数学系、信息系这三个系姓刘的、年龄在18至23岁之间的女同学的学号和姓名。

select * from student;

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

select cname from course where cpno is null;

select sno,sname from student where sdept in('CS', 'MA', 'IS') and sname like '刘%' and sage between 18 and 23 and ssex='女';

基于课本52页图2.4中的student、course、sc表，使用SQL查询语句完成下列各题：

（1）查询课程总门数。

（2）查询选修了课程的学生人数。

（3）查询选修了1号课程的学生人数。

（4）查询有不及格成绩(低于60分)的学生人数。

（5）查询选修了1号课程的学生的平均成绩。

select count(*) from course;

select count(distinct sno) from sc;

select count(*) from sc where cno='1';

select count(distinct sno) from sc where grade<60;

select avg(grade) from sc where cno='1';

基于课本52页图2.4中的student、course、sc表，使用SQL查询语句完成下列各题：

（1）查询学生的学号及其平均成绩。

（2）查询各个课程号及其平均成绩。

（3）查询平均成绩大于等于86分的学生学号和平均成绩。

（4）查询有两个或两个以上学生选修的课程的课程号和选课人数。

select sno,avg(grade) from sc group by sno;

select cno,avg(grade) from sc group by cno;

select sno,avg(grade) from sc group by sno having avg(grade)>=86;

select cno,count(*) from sc group by cno having count(*)>=2;

基于课本52页图2.4中的student、course、sc表，使用SQL查询语句完成下列各题：

（1）查询学生的基本情况，查询结果按所在系升序排列，同一系中的学生按年龄降序排列。

（2）查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

（3）查询选修了课程的学生的学号。

（4）查询有不及格成绩(低于60分)的学生的学号。

select * from student order by sdept,sage desc;

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

select distinct sno from sc;

select distinct sno from sc where grade<60;

基于课本52页图2.4中的student、course、sc表，使用SQL查询语句完成下列各题：

（1）查询每个学生的学号、姓名、选修的课程名称及成绩。

（2）查询选修2号课程且成绩在90分以上的学生的学号和姓名。

（3）查询学生201215122选修课程的名称。

（4）查询学生201215122选修课程的总学分数。

（5）查询每个学生（选了课的）选修课程的总学分数。

（6）查询计算机系平均成绩大于等于86分的学生的学号。

（7）查询每一门课的间接先修课。

select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;

select student.sno,sname from student,sc where student.sno=sc.sno and cno='2' and grade>90;

select cname from sc,course where sc.cno=course.cno and sno='201215122';

select sum(ccredit) from sc,course where sc.cno=course.cno and sno='201215122';

select sno,sum(ccredit) from sc,course where sc.cno=course.cno group by sno;

select student.sno from student,sc where sdept='CS' and student.sno=sc.sno group by sc.sno having avg(grade)>=86;

select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

写出创建下列索引的SQL语句。

（1）给student表按性别升序建立一个索引，索引名为idxssex。

（2）给student表按学号升序建立一个唯一索引，索引名为idxsno。

（3）给sc表按学号升序和课程号降序建立一个唯一索引，索引名为idxsc。

create index idxssex on student(ssex);

create unique index idxsno on student(sno);

create unique index idxsc on sc(sno asc,cno desc);

基于课本52页图2.4中的student、course、sc表，使用连接查询完成下列各题。

（1）查询选修了2号课程的学生的姓名。

（2）查询“201215121”选修了的课程的名称。

（3）查询选修了“信息系统”的学生的学号。

（4）查询“刘晨”选修了的课程的课程号。

（5）查询选修了“信息系统”的学生的姓名。

（6）查询“刘晨”选修了的课程的名称。

（1）select sname from student,sc where student.sno=sc.sno and cno='2';

（2）select cname from sc,course where sc.cno=course.cno and sno='201215121';

（3）select sno from sc,course where sc.cno=course.cno and cname='信息系统';

（4）select cno from student,sc where student.sno=sc.sno and sname='刘晨';

（5）select sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='信息系统';

（6）select cname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname='刘晨';

基于课本52页图2.4中的student、course、sc表，使用带有比较运算符、any或all的子查询完成下列各题。

（1）查询与“刘晨”在同一个系学习的学生。

（2）查询选修了“信息系统”的学生的学号。

（3）查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄。

（4）查询非计算机系中比计算机系所有学生年龄都小的学生姓名和年龄。

（1）select sno,sname,sdept from student where sdept = (select sdept from student where sname='刘晨');

（2）select sno from sc where cno = (select cno from course where cname='信息系统');

（3）select sname,sage from student where sdept<>'CS' and sage<ANY(select sage from student where sdept='CS');

（4）select sname,sage from student where sdept<>'CS' and sage<ALL(select sage from student where sdept='CS');

基于课本52页图2.4中的student、course、sc表，先用带有IN谓词的子查询完成下列各题，再用带有EXISTS谓词的子查询完成下列各题。

（1）查询选修了2号课程的学生的姓名。

（2）查询没有选修2号课程的学生的姓名。

（3）查询“201215121”选修了的课程的名称。

（4）查询“201215121”没有选修的课程的名称。

（1）select sname from student where sno in (select sno from sc where cno='2');

（2）select sname from student where sno not in (select sno from sc where cno='2');

（3）select cname from course where cno in (select cno from sc where sno='201215121');

（4）select cname from course where cno not in (select cno from sc where sno='201215121');

（1）select sname from student where EXISTS (select * from sc where sno=student.sno and cno='2');

（2）select sname from student where NOT EXISTS (select * from sc where sno=student.sno and cno='2');

（3）select cname from course where EXISTS (select * from sc where cno=course.cno and sno='201215121');

（4）select cname from course where NOT EXISTS (select * from sc where cno=course.cno and sno='201215121');

基于课本52页图2.4中的student、course、sc表，分别使用带有IN谓词的子查询和带有EXISTS谓词的子查询完成下列各题。

（1）查询选修了“信息系统”的学生的姓名。

（2）查询没有选修“信息系统”的学生的姓名。

（3）查询“刘晨”选修了的课程的名称。

（4）查询“刘晨”没有选修的课程的名称。

（1）

select sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select sname from student where EXISTS(select * from sc where sno=student.sno and cno in(select cno from course where cname='信息系统'));

select sname from student where EXISTS(select * from sc where sno=student.sno and EXISTS(select * from course where cno=sc.cno and cname='信息系统'));

（2）

select sname from student where sno not in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select sname from student where NOT EXISTS(select * from sc where sno=student.sno and cno in(select cno from course where cname='信息系统'));

select sname from student where NOT EXISTS(select * from sc where sno=student.sno and EXISTS(select * from course where cno=sc.cno and cname='信息系统'));

（3）

select cname from course where cno in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select cname from course where EXISTS(select * from sc where cno=course.cno and sno in(select sno from student where sname='刘晨'));

select cname from course where EXISTS(select * from sc where cno=course.cno and EXISTS(select * from student where sno=sc.sno and sname='刘晨'));

（4）

select cname from course where cno not in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select cname from course where NOT EXISTS(select * from sc where cno=course.cno and sno in(select sno from student where sname='刘晨'));

select cname from course where NOT EXISTS(select * from sc where cno=course.cno and EXISTS(select * from student where sno=sc.sno and sname='刘晨'));

基于课本52页图2.4中的student、course、sc表，分别使用带有IN谓词的子查询和带有EXISTS谓词的子查询完成下列各题。

（1）查询选修了“信息系统”的学生的学号。

（2）查询没有选修“信息系统”的学生的学号(该生选了课的)。

（3）查询“刘晨”选修了的课程的课程号。

（4）查询“刘晨”没有选修的课程的课程号(该课程有人选了的)。

（1）

select sno from sc where cno in (select cno from course where cname='信息系统');

select sno from sc where EXISTS(select * from course where cno=sc.cno and cname='信息系统');

（2）

select distinct sno from sc where sno not in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select distinct sno from sc scx where NOT EXISTS (select * from sc where sno=scx.sno and cno in (select cno from course where cname='信息系统'));

select distinct sno from sc scx where NOT EXISTS (select * from sc scy where sno=scx.sno and EXISTS (select * from course where cno=scy.cno and cname='信息系统'));

（3）

select cno from sc where sno in (select sno from student where sname='刘晨');

select cno from sc where EXISTS(select * from student where sno=sc.sno and sname='刘晨');

（4）

select distinct cno from sc where cno not in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select distinct cno from sc scx where NOT EXISTS (select * from sc where cno=scx.cno and sno in (select sno from student where sname='刘晨'));

select distinct cno from sc scx where NOT EXISTS (select * from sc scy where cno=scx.cno and EXISTS (select * from student where sno=scy.sno and sname='刘晨'));