有三个表:1 学生表STUDENT,2 学科表SUBJECT,还有3 分数表SCORE。1.1 创建学生表STUDENT:
CREATE TABLE `student` (
`student_id` int(11) DEFAULT NULL,
`student_name` varchar(10) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`addr` varchar(50) DEFAULT NULL,
`CLASS_ID` int(11) DEFAULT NULL COMMENT '所在班级ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;1.2 学生表STUDENT插入数据:
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('1', 'Jim', '1', '10', 'BJ', '1');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('2', 'Tom', '1', '15', 'SH', '2');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('3', 'Lily', '0', '18', 'GZ', '3');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('4', 'Lucy', '0', '24', 'SZ', '2');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('5', 'Zeo', '0', '28', 'SZ', '1');
2.1 创建学科表SUBJECT:
CREATE TABLE `subject` (
`subject_id` varchar(10) DEFAULT NULL,
`subject_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.2 学科表SUBJECT插入数据:INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Chinese', '语文');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Math', '数学');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('English', '英语');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Art', '艺术');
3.1 创建分数表SCORE:
CREATE TABLE `score` (
`student_id` int(11) DEFAULT NULL,
`subject_id` varchar(10) DEFAULT NULL,
`subject_score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;3.2 分数表SCORE插入数据:INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'Chinese', '73');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'Math', '76');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'English', '79');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'Chinese', '83');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'Math', '86');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'English', '89');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'Chinese', '93');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'Math', '96');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'English', '99');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('4', 'Math', '100');
表数据如下:
然后进行下面2个查询,都是既有逗号关联,又有INNER JOIN关联:SELECT * FROM SCORE,STUDENT
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;不过就是逗号关联的SCORE表和STUDENT表调换一下顺序,却是不同的查询结果:
SELECT * FROM STUDENT,SCORE
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
这是为什么呢?
CREATE TABLE `student` (
`student_id` int(11) DEFAULT NULL,
`student_name` varchar(10) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`addr` varchar(50) DEFAULT NULL,
`CLASS_ID` int(11) DEFAULT NULL COMMENT '所在班级ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;1.2 学生表STUDENT插入数据:
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('1', 'Jim', '1', '10', 'BJ', '1');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('2', 'Tom', '1', '15', 'SH', '2');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('3', 'Lily', '0', '18', 'GZ', '3');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('4', 'Lucy', '0', '24', 'SZ', '2');
INSERT INTO `student` (`student_id`, `student_name`, `gender`, `age`, `addr`, `CLASS_ID`) VALUES ('5', 'Zeo', '0', '28', 'SZ', '1');
2.1 创建学科表SUBJECT:
CREATE TABLE `subject` (
`subject_id` varchar(10) DEFAULT NULL,
`subject_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.2 学科表SUBJECT插入数据:INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Chinese', '语文');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Math', '数学');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('English', '英语');
INSERT INTO `subject` (`subject_id`, `subject_name`) VALUES ('Art', '艺术');
3.1 创建分数表SCORE:
CREATE TABLE `score` (
`student_id` int(11) DEFAULT NULL,
`subject_id` varchar(10) DEFAULT NULL,
`subject_score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;3.2 分数表SCORE插入数据:INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'Chinese', '73');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'Math', '76');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('1', 'English', '79');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'Chinese', '83');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'Math', '86');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('2', 'English', '89');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'Chinese', '93');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'Math', '96');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('3', 'English', '99');
INSERT INTO `score` (`student_id`, `subject_id`, `subject_score`) VALUES ('4', 'Math', '100');
表数据如下:
然后进行下面2个查询,都是既有逗号关联,又有INNER JOIN关联:SELECT * FROM SCORE,STUDENT
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;不过就是逗号关联的SCORE表和STUDENT表调换一下顺序,却是不同的查询结果:
SELECT * FROM STUDENT,SCORE
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
这是为什么呢?
感觉这是把SCORE
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
先处理了,所以在
student
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
的时候就不行
SELECT * FROM
SCORE,
STUDENT INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID -- 这里找不到 SCORE, SCORE 与当前的表达式是并行的
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
SELECT * FROM
SCORE
INNER JOIN STUDENT ON SCORE.STUDENT_ID = STUDENT.STUDENT_ID
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID;
-- 可以引用 SCORE,因为 SCORE 在当前表达式前面出现
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
SELECT * FROM STUDENT,SCORE
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
INNER JOIN关联先执行(即红色字体部分),然后再执行逗号关联和WHERE条件。SELECT * FROM SCORE,STUDENT
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;
SELECT * FROM STUDENT,SCORE
INNER JOIN SUBJECT ON SUBJECT.SUBJECT_ID = SCORE.SUBJECT_ID
WHERE SCORE.STUDENT_ID = STUDENT.STUDENT_ID;