手写SQL

建表语句及原始数据

CREATE TABLE student
(
sid int PRIMARY KEY,
sname VARCHAR(20),
sage DATETIME,
ssex CHAR(4)
)

insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');

SELECT * FROM student

drop TABLE student

create table teacher(
tid int PRIMARY KEY,
tname VARCHAR(20)
)

INSERT INTO teacher VALUES(1,'张老师');
INSERT INTO teacher VALUES(2,'李老师');
INSERT INTO teacher VALUES(3,'王老师');
INSERT INTO teacher VALUES(4,'赵老师');
INSERT INTO teacher VALUES(5,'于老师');

SELECT * from teacher

CREATE TABLE course(
cid int PRIMARY KEY ,
cname VARCHAR(20),
tid int,
 CONSTRAINT fk_tid FOREIGN KEY (tid) REFERENCES teacher(tid)
)

insert INTO course VALUES(1,'语文',5);
insert INTO course VALUES(2,'数学',2);
insert INTO course VALUES(3,'英语',1);
insert INTO course VALUES(4,'物理',3);
insert INTO course VALUES(5,'化学',4);

SELECT * FROM course

DROP TABLE score

create TABLE score(
sid int ,
cid int ,
score int,
CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES course(cid),
CONSTRAINT fk_sid FOREIGN KEY (sid) REFERENCES student(sid)
)

INSERT INTO score VALUES(1,1,94);
INSERT INTO score VALUES(1,2,54);
INSERT INTO score VALUES(1,3,81);
INSERT INTO score VALUES(1,4,79);
INSERT INTO score VALUES(1,5,78);
INSERT INTO score VALUES(2,1,36);
INSERT INTO score VALUES(2,2,60);
INSERT INTO score VALUES(2,3,99);
INSERT INTO score VALUES(2,4,83);
INSERT INTO score VALUES(2,5,53);
INSERT INTO score VALUES(3,1,67);
INSERT INTO score VALUES(3,2,49);
INSERT INTO score VALUES(3,3,63);
INSERT INTO score VALUES(3,4,92);
INSERT INTO score VALUES(3,5,29);
INSERT INTO score VALUES(4,1,38);
INSERT INTO score VALUES(4,2,58);
INSERT INTO score VALUES(4,3,69);
INSERT INTO score VALUES(4,4,93);
INSERT INTO score VALUES(4,5,79);
INSERT INTO score VALUES(5,1,18);
INSERT INTO score VALUES(5,2,68);
INSERT INTO score VALUES(5,3,73);
INSERT INTO score VALUES(5,4,84);
INSERT INTO score VALUES(5,5,57);

SELECT * FROM score

查询“语文”课程成绩比“数学”课程成绩高的学生的ID

# 嵌套查询
SELECT a.sid 
FROM
 (SELECT sid,score FROM score WHERE cid=(SELECT cid FROM course WHERE cname='语文'))a,
 (SELECT sid,score FROM score WHERE cid=(SELECT cid FROM course WHERE cname='数学'))b
WHERE a.sid = b.sid and a.score>b.score;
# 验证结果的正确性
SELECT student.sid,score,course.cid FROM student,course,score
WHERE student.sid=score.sid
AND course.cid = score.cid
AND course.cname IN ('语文','数学')

INNER JOIN写法

# 先找出课程1所有的学生成绩信息
SELECT student.sid ,sc1.score as '语文',sc2.score as '数学' FROM student INNER JOIN score sc1 ON (student.sid = sc1.sid)AND sc1.cid=(SELECT cid FROM course WHERE cname='语文')
# 关联课程2的所有学生成绩信息
INNER JOIN score sc2 ON(student.sid = sc2.sid)and sc2.cid = (SELECT cid FROM course WHERE cname='数学')
# 最后判断成绩
WHERE sc1.score>sc2.score;

INNER JOIN和等值连接可以相互替换

SELECT * FROM teacher INNER JOIN course ON(teacher.tid=course.tid)
# 这两句的结果是一样的
SELECT * FROM teacher,course WHERE teacher.tid=course.tid