赞
踩
MySQL数据库(嵌套子查询)答案-武洪萍主编
代码如下:
- 一,
- -- 1,创建数据库 t10
- 1,删除数据库
- DROP DATABASE IF EXISTS t10;
- 2,创建数据库
- CREATE DATABASE t10;
- 3,修改数据库编码方式和字符集排列顺序
- ALTER DATABASE t10 CHARACTER SET utf8 COLLATE utf8_bin;
- 4,使用数据库
- USE t10;
- -- 2, 表的结构数据从脚本a1导入
- -- 3,查询a1中所有的表记录,并用别名显示
- -- 查看各表数据
- SELECT*FROM course; -- [课程表]主键cno(课程编号)课程名称cname
- SELECT*FROM sc; -- [成绩表]双主键(sno,cno)外键sno参考学生表student,外键cno参考课程course,degree 分数,
- SELECT*FROM student; -- [学生表]主键sno(学号),姓名snmae,性别ssex,电话sphone,地址saddress,生日sbirthday,所属院系sdept,照片sphoto,专业speciality,
- SELECT*FROM suser; -- [用户表]用户名username,密码password,用户权限 userlevel
- SELECT*FROM teacher; -- [教师表]主键tno(教师编号)教师性别ssex,教师生日sbirthday,所属院系sdept,
- SELECT*FROM teaching; -- [授课表]课程编号cno(参考课程表),教师编号(教师表tno),开课学期cterm,
- -- 查看各表结构
- DESCRIBE course;
- DESCRIBE sc;
- DESCRIBE student;
- DESCRIBE suser;
- DESCRIBE teacher;
- DESCRIBE teaching;
-
- -- 四,嵌套子查询
- -- 1,查询‘李勇’同学所选课程的成绩
- SELECT degree 成绩
- FROM sc
- WHERE EXISTS(SELECT cno
- FROM student
- WHERE student.sno=sc.sno AND sname='李勇');
- -- 2,查询‘李新’教师所授课程的课程名称
- SELECT cname 课程名称
- FROM course a
- WHERE EXISTS(
- SELECT b.cno
- FROM teaching b,teacher c
- WHERE b.tno=c.tno AND b.cno=a.cno
- AND tname='李新'
- );
- -- 3,查询女教师所授课程的课程号及课程名称
- SELECT a.cno 课程号,cname 课程名称
- FROM course a
- WHERE EXISTS(
- SELECT cno
- FROM teacher b,teaching c
- WHERE b.tno=c.tno AND c. cno=a.cno
- AND tsex='女'
- );
- -- 4,查询‘王’的学生所学的课程名称
- SELECT cname 课程名称
- FROM course a
- WHERE EXISTS(
- SELECT c.cno
- FROM student b,sc c
- WHERE a.cno=c.cno AND b.sno=c.sno
- AND sname LIKE '王%'
- );
- -- 5,查询‘C02’课程不及格的学生信息
- SELECT *
- FROM student
- WHERE sno IN(
- SELECT sno
- FROM sc
- WHERE cno='c01'
- AND degree<60);
- -- 6,查询选修‘数据库’课程且成绩在80~90分的学生学号及成绩
- SELECT sno 学号,degree 成绩
- FROM sc a
- WHERE EXISTS(
- SELECT cno
- FROM course b
- WHERE a.cno=b.cno
- AND cname='数据库' AND degree BETWEEN 80 AND 90
- );
- -- 7,查询选修‘C04’课程的学生的平均年龄
- SELECT AVG(YEAR(CURDATE())-YEAR(sbirthday)) 平均年龄
- FROM student a
- WHERE EXISTS(
- SELECT a.sno
- FROM sc b
- WHERE a.sno=b.sno AND cno='c04'
- );
- -- 8,查询选修课程名为‘数学’的学生学号与姓名
- SELECT sno 学号,sname 姓名
- FROM student a
- WHERE EXISTS(
- SELECT b.cno
- FROM sc b,course c
- WHERE b.cno=c.cno AND a.sno=b.sno
- AND cname LIKE '%数学%'
- );
- -- 9,查询‘钱军’教师任课的课程号、选修其课程的学生的学号与成绩
- SELECT cno 课程号,sno 学号,degree 成绩
- FROM sc
- WHERE cno=(
- SELECT cno
- FROM teaching
- WHERE tno=(
- SELECT tno
- FROM teacher
- WHERE tname='钱军'
- )
- );
- -- 10,查询在第3学期所开课程的课程名称及学生的成绩
- SELECT cno 课程名称,degree 成绩
- FROM sc
- WHERE cno=ANY(
- SELECT cno
- FROM teaching
- WHERE cterm='2'
- );
- -- 11,查询与‘李勇’同一个系的学生姓名
- SELECT sname 姓名
- FROM student
- WHERE sdept=(
- SELECT sdept
- FROM student
- WHERE sname='李勇'
- )AND sname<>'李勇';
- -- 12,查询学号比刘晨学生的大,而出生日期比她的小的学生姓名
- SELECT sname 姓名
- FROM student
- WHERE (sno>(
- SELECT sno
- FROM student
- WHERE sname='刘晨'
- ))AND(sbirthday>(
- SELECT sbirthday
- FROM student
- WHERE sname='刘晨'
- )
- );
- -- 13,查询出生日期大于所有女生出生日期的男生的姓名及系别
- SELECT sname 姓名,sdept 系别
- FROM student
- WHERE (sbirthday>ALL(
- SELECT sbirthday
- FROM student
- WHERE ssex='女'
- ))AND ssex='男';
- -- 14,查询成绩比该课程平均成绩高的学生的学号和成绩
- SELECT sno 学号,degree 成绩
- FROM sc
- WHERE degree>=
- (SELECT AVG(degree)
- FROM sc
- WHERE sc.cno=sc.cno
- );
- -- 15,查询不讲授'C01'课的教师姓名
- SELECT tname 教师姓名
- FROM teacher
- WHERE EXISTS(
- SELECT tno
- FROM teaching
- WHERE cno<>'c01'
- );
- -- 16,查询没有选修‘C02’课程的学生学号及姓名
- SELECT sno 学号,sname 姓名
- FROM student
- WHERE sno IN(
- SELECT sno
- FROM sc
- WHERE cno<>'c02'
- );
- -- 17,查询选修‘数据库’课程的学生学号,姓名和系别
- SELECT sno 学号,sname 姓名,sdept 系别
- FROM student
- WHERE sno IN(
- SELECT sno
- FROM sc
- WHERE cno=(
- SELECT cno
- FROM course
- WHERE cname='数据库'
- ));
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。