Java学习-MySQL-DQL数据查询-联表查询JOIN

Java学习-MySQL-DQL数据查询-联表查询JOIN

1.分析需求，查找那些字段
2.分析查询的字段来自哪些表
3.确定使用哪种连接查询
4.确定交叉点
5.确定判断条件

操作	描述
inner join	返回左右表的交集
left join	返回左表，即使右表没有
right join	返回右表，即使左表没有

初始化

有三位学生：刘星、刘星星、刘六留没有参加任何考试，有两位同学参加了数据库结构-4考试但填错了学号：6666、9999，得分：66、99

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
 `studentno` INT(4) NOT NULL COMMENT '学号',
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址，允许为空',
    PRIMARY KEY (`studentno`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
 `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
 `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
 `studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入学生数据
INSERT INTO `student` (`studentno`,`studentname`,`sex`,`gradeid`,`address`)
VALUES
(1000,'赵云',1,1,'北京朝阳'),
(1001,'刘禅',1,1,'河南洛阳'),
(1002,'诸葛亮',1,2,'天津红桥'),
(1003,'曹操',1,3,'广西桂林'),
(1004,'刘备',1,3,'云南大理'),
(1005,'关羽',1,3,'新疆乌鲁木齐'),
(1006,'张飞',1,3,'四川成都'),
(1007,'孙策',1,4,'北京海淀'),
(1008,'刘邦',1,4,'北京东城'),
(1009,'韩信',1,4,'北京西城'),
(1010,'刘星',1,4,'北京丰台'),
(1011,'刘星星',1,4,'北京石景山'),
(1012,'刘六留',1,4,'北京房山');

-- 插入成绩数据
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,3,'2013-11-12 16:00:00',87),
(1001,1,'2013-11-11 16:00:00',78),
(1001,3,'2013-11-12 16:00:00',88),
(1002,2,'2013-11-11 09:00:00',88),
(1002,4,'2013-11-12 09:00:00',96),
(1002,5,'2013-11-13 09:00:00',92),
(1003,6,'2013-11-11 16:00:00',75),
(1003,7,'2013-11-12 16:00:00',88),
(1004,6,'2013-11-11 16:00:00',66),
(1004,7,'2013-11-12 16:00:00',62),
(1005,6,'2013-11-11 16:00:00',74),
(1005,7,'2013-11-12 16:00:00',68),
(1006,6,'2013-11-11 16:00:00',84),
(1006,7,'2013-11-12 16:00:00',83),
(1007,8,'2013-11-13 16:00:00',66),
(1008,8,'2013-11-13 16:00:00',67),
(1009,8,'2013-11-13 16:00:00',68),
(6666,8,'2013-11-13 16:00:00',66),
(9999,8,'2013-11-13 16:00:00',99);


-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'C语言-1',110,1),
(4,'C语言-2',110,2),
(5,'Java程序设计-2',110,2),
(6,'Java程序设计-3',110,3),
(7,'数据库结构-3',110,3),
(8,'数据库结构-4',110,4);

查询参加了考试的学生的成绩

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s INNER JOIN result AS r
WHERE s.studentno=r.studentno

查询所有学生的成绩，不论有没有参加考试

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s LEFT JOIN result AS r
ON s.studentno=r.studentno

查询考试成绩，不论考生是不是学生

SELECT r.studentno,studentname,subjectno,studentresult
FROM student AS s RIGHT JOIN result AS r
ON s.studentno=r.studentno

查询缺考的同学

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s LEFT JOIN result AS r
ON s.studentno=r.studentno
WHERE studentresult IS NULL

查询考试成绩无效的试卷

SELECT r.studentno,studentname,subjectno,studentresult
FROM student AS s RIGHT JOIN result AS r
ON s.studentno=r.studentno
WHERE s.studentno IS NULL

查询参加考试的学生的信息：学号、姓名、科目名、分数

SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
INNER JOIN result AS r
ON r.studentno=s.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno=r.subjectno

查询所有试卷：学号、姓名、科目名、分数

SELECT r.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentno=s.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno=r.subjectno