本文共 4009 字，大约阅读时间需要 13 分钟。
创建学生信息表 t_student：
create table t_student ( Sno CHAR(7) primary key, Sname CHAR(10), Ssex CHAR(2), Sage int DEFAULT 20, Sdept CHAR(20));
插入学生数据：
insert into t_student(Sno, Sname, Ssex, Sage, Sdept)values("0804601", "lily", "m", 12, "jk"), ("0804602", "mary", "f", 17, "jk19"), ("0804603", "bob", "m", 18, "jk18"), ("0804604", "hani", "m", 10, "jk"), ("0804605", "job", "m", 13, "jk"); 创建课程信息表 t_course：
create table t_course ( Cno CHAR(10) primary key, Cname CHAR(20) NOT NULL, Ccredit int CHECK(Ccredit > 0), Semester int CHECK(Semester > 0), Period int CHECK(Period > 0));
插入课程数据：
insert into t_course(Cno, Cname, Ccredit, Semester, Period)values("TX4601", "sjk", 3, 1, 2), ("TX4602", "java", 4, 2, 2), ("TX4603", "jw", 3, 1, 2), ("TX04604", "web", 2, 1, 2), ("TX04605", "mks", 2, 1, 2); 创建选课信息表 t_SC：
create table t_SC ( Sno CHAR(7) NOT NULL, Cno CHAR(10) NOT NULL, Grade int, primary key (Sno, Cno), foreign key (Sno) references t_student(Sno), foreign key (Cno) references t_course(Cno));
插入选课数据：
insert into t_SC(Sno, Cno, Grade)values("0804601", "TX4601", 99), ("0804602", "TX4602", 98), ("0804603", "TX4603", 97), ("0804604", "TX4604", 96), ("0804605", "TX4605", 95); 按 Cno 升序、Grade 降序查询 t_SC 表所有记录：
select * from t_SC order by Cno asc, Grade desc;
查询所有学生的 Sname、Cname 和 Grade 列（连接查询，嵌套查询）：
select Sname, Cname, Grade from t_student s, t_course c, t_SC scwhere s.Sno = sc.Sno and sc.Cno = c.Cno;
查询选修“计算机导论”课程的同学成绩（Cname 为 "jw"）：
select sname, grade from t_SC sc, t_student s, t_course cwhere c.Cname = "jw" and sc.Cno = c.Cno and s.Sno = sc.Sno;
查询与 "李军" 同性别的同学姓名（自身连接查询，嵌套查询）：
select Sname from t_student s where s.Ssex = (select Ssex from t_student where Sname = "lily");
查询所有学生的基本信息及选课情况，包括未选课的同学（外连接查询）：
select s.Sno, Sname, Ssex, Sage, Sdept, Grade, c.Cno, Cname, Ccredit, Semester, Periodfrom t_student sright join t_SC sc on s.Sno = sc.Snoright join t_course c on sc.Cno = c.Cno;
查询选修 "C05" 号课程且成绩高于 80 分的同学姓名：
select sname from t_student s join t_SC sc on s.Sno = sc.Snojoin t_course c on sc.Cno = c.Cnowhere c.Cno = "TX4601" and grade > 80;
查询学号为 "0608002" 的同学同年出生的所有学生：
select distinct a.Sno, a.Sname from t_student ajoin t_student b on a.Sage = (select Sage from t_student where Sno = "0804601");
查询王毅同学的所有成绩：
select sname, grade from t_student s join t_SC sc on s.Sno = sc.Snojoin t_course c on sc.Cno = c.Cnowhere sname = "lily";
查询非计算机系且年龄低于计算机系所有学生年龄的学生姓名：
select sname from t_student a where a.Sage < any( select Sage from t_student where Sdept = "jk") and a.Sdept != "jk";
查询存在成绩超过 85 分的课程 Cno：
select distinct sc.Cno from t_SC scjoin t_course c on sc.Cno = c.Cnowhere exists (select grade from t_SC where grade > 85);
查询计算机系的学生人数：
select count(Sno) from t_student where Sdept = "jk";
查询数学系统学选课程的平均分：
select avg(grade) from t_SC scjoin t_course c on sc.Cno = c.Cnowhere c.Cname = "java";
查询学生表中年龄最大和最小的同学：
select * from t_student where sage >= all(select sage from t_student) or sage <= all(select sage from t_student);
查询最高分同学的 Sno 和 Cno：
select Sno, Cno from t_SC where grade >= all(select grade from t_SC);
查询 "C08" 号课程的平均分：
select avg(grade) from t_SC sc where sc.Cno = "TX4602";
查询选修 "C08" 号课程且成绩低于课程平均分的同学：
select sno, grade from t_SC scwhere (select avg(grade) from t_SC sc where sc.Cno = "TX4602") > gradeand sc.Cno = "TX4602";
查询选修 "C06" 号课程或 "C01" 号课程的同学：
select sno from t_SC sc where sc.Cno = "TX4602" or sc.Cno = "TX.4603";
查询各课程的选课人数：
select cno, count(*) from t_SC group by cno;
查询选修超过 3 门课程且总分超过 200 分的同学：
select sno from t_SCwhere sno = (select sno, count(*) from t_SC group by sno having count(*) > 2)group by sno having sum(grade) > 200;
查询比自己平均分高的课程 Cno：
select cno from t_SCwhere (select avg(grade) from t_SC sc where sno = "0804601") < gradeand sno = "0804601";
以上查询示例可根据具体需求进行调整，适用于不同场景的数据库操作。
转载地址：http://enmz.baihongyu.com/