有三个表: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;
这是为什么呢?
解决方案 »
- 关系型数据库查询优化技术的研究
- MySql服务启动不了...急
- 如何实现两个mysql数据库内两张表的自动定时同步?
- mysql 触发器操作写入二进制日志
- 如何将 Oracle 10g 的数据, 导入到 MySQL 中呢?
- PostgreSQL 大小写的问题?
- 大侠帮忙mysql的 table handle怪报错!!!!
- 我简直要疯了!!!为什么mysql这样便无法联通???快请进!
- 有个mysql问题
- mysql表名为数字,无法执行select * from 1000532;为什么,怎么办?急急急!望高手解答,谢谢。
- 将json解析并导入数据库中
- windows 下mysql load data infile 的路径问题
感觉这是把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;