查询课程编号为01的学生比课程编号为02的学生成绩高的所有学生的编号
1. 首先建立数据表
建立四张数据表(, 成绩表(score))
1. 学生表(students)
1.学生id ---- s_id
2. 学生姓名 ---- s_name
3. 学生生日 ---- s_birth
4. 学生性别 ---- s_sex
2. 教师表(teacher)
1. 教师id ---- t_id
2. 教师姓名 ---- t_name
3. 课程表(course)
1.课程id ---- c_id
2. 课程名 ---- c_name
3. 教师id ----- t_id(与教师表对应, 方便建立数据库连接)
4. 成绩表(score)
1. 学生id ---- s_id(与学生表对应, 方便建立数据库连接)
2. 课程id ---- c_id(与课程表对应, 方便建立数据库连接)
3. 学生成绩 ----- s_score
============ 表的建立代码:
1 CREATE TABLEstudents(2 s_id VARCHAR(20) PRIMARY KEY,3 s_name VARCHAR(20) NOT NULL DEFAULT '',4 s_birth VARCHAR(20) NOT NULL DEFAULT '',5 s_sex VARCHAR(10) NOT NULL DEFAULT ''
6 );7
8 CREATE TABLEcourse(9 c_id VARCHAR(20) PRIMARY KEY,10 c_name VARCHAR(20) NOT NULL DEFAULT '',11 t_id VARCHAR(20) NOT NULL
12 );13
14 CREATE TABLEteacher(15 t_id VARCHAR(20) PRIMARY KEY,16 t_name VARCHAR(20) NOT NULL DEFAULT ''
17 );18
19 CREATE TABLEscore(20 s_id VARCHAR(20),21 c_id VARCHAR(20),22 s_score INT(3),23 PRIMARY(s_id, c_id)24 );
2. 导入数据
导入数据信息:insert into ... values(信息)
============数据导入代码
1 INSERT INTO students VALUES('01', '赵雷', '1990-01-01', '男');2 INSERT INTO students VALUES('02', '钱电', '1990-12-21', '男');3 INSERT INTO students VALUES('03', '孙风', '1990-05-20', '男');4 INSERT INTO students VALUES('04', '李云', '1990-12-01', '男');5 INSERT INTO students VALUES('05', '周梅', '1991-12-01', '女');6 INSERT INTO students VALUES('06', '吴兰', '1992-03-01', '女');7 INSERT INTO students VALUES('07', '郑竹', '1989-07-01', '女');8 INSERT INTO students VALUES('08', '王菊', '1990-01-20', '女');9
10 INSERT INTO course VALUES('01', '语文', '02');11 INSERT INTO course VALUES('02', '数学', '01');12 INSERT INTO course VALUES('03', '英语', '03');13
14 INSERT INTO teacher VALUES('01', '张三');15 INSERT INTO teacher VALUES('02', '李四');16 INSERT INTO teacher VALUES('03', '王五');17
18 INSERT INTO score VALUES('01', '01', 80);19 INSERT INTO score VALUES('01', '02', 90);20 INSERT INTO score VALUES('01', '03', 99);21 INSERT INTO score VALUES('02', '01', 70);22 INSERT INTO score VALUES('02', '02', 60);23 INSERT INTO score VALUES('02', '03', 80);24 INSERT INTO score VALUES('03', '01', 80);25 INSERT INTO score VALUES('03', '02', 80);26 INSERT INTO score VALUES('03', '03', 80);27 INSERT INTO score VALUES('04', '01', 50);28 INSERT INTO score VALUES('04', '02', 30);29 INSERT INTO score VALUES('04', '03', 20);30 INSERT INTO score VALUES('05', '01', 76);31 INSERT INTO score VALUES('05', '02', 87);32 INSERT INTO score VALUES('06', '03', 31);33 INSERT INTO score VALUES('06', '01', 34);34 INSERT INTO score VALUES('07', '02', 89);35 INSERT INTO score VALUES('07', '03', 98);
效果图:
course表:
score表:
teacher表:
students表:
3. 处理问题
1. 思路: 我们希望得到如下一张表, 只需要用简单的select * from table where 01成绩>02成绩 就能解决问题
---- 我们无法直接将这个直观表构建出来, 所以我们就可以想到---构建子查询, 一步一步向外嵌套
s_id
c_id_01
c_id_02
学生编号
课程01的成绩
课程02的成绩
select * from table where 01>02
2. 首先, 我们先把课程为01的学生信息, 成绩寻找出来.
select s_id, c_id, s_score from score where c_id = '01';
输出结果:
3. 再寻找课程等于02的学生信息
select s_id, c_id, s_score from score where c_id = '02';
输出结果:
4. 我们希望把01和02的成绩拼成一张表
-------把两张(分别命名(as)为a, b)表inner join起来, 做一个连接查询, 并用学生编号(s_id, c_id)给关联(=)起来(on)
select * from(select s_id, c_id, s_score from score where c_id = '01')asainner join(select s_id, c_id, s_score from score where c_id = '02')as b on a.s_id = b.c_id
输出结果:
5. 再带入条件(where), 我们的条件是学生01科目成绩要大于02科目的成绩(a.s_score > b.s_score), 并且需要的是学生的编号(* = a.s_id(或者b.s_id,两者一样随便取一个))
select a.s_id from
(
select s_id, c_id, s_score FROM score where c_id = '01'
) as a
inner join
(
select s_id, c_id, s_score FROM score where c_id = '02'
) as b on a.s_id = b.c_id
where a.s_score > b.s_score
输出结果:
内容来源于网络如有侵权请私信删除