我是DB的新手,所以我甚至不确定这是否可能。我有两张表class
和students
CREATE TABLE `class` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'latin1_swedish_ci',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name` (`name`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;
CREATE TABLE `students` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`ref_class_id` INT(10) NOT NULL DEFAULT '0',
`name` VARCHAR(45) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_students_class` (`ref_class_id`) USING BTREE,
CONSTRAINT `FK_students_class` FOREIGN KEY (`ref_class_id`) REFERENCES `testdata`.`class` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3
;
然后插入
INSERT INTO `testdata`.`class` (`name`) VALUES ('foo');
INSERT INTO `testdata`.`students` (`ref_class_id`, `name`) VALUES ('2', 'student1');
INSERT INTO `testdata`.`students` (`ref_class_id`, `name`) VALUES ('2', 'student2');
所以,目前,我有两个学生与一个班级有关联。然后在我的C#代码中,我需要以某种方式从DB中检索这些数据,以便完成这项工作,我创建了过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `retrieve_class_by_id`(
IN `class_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT * FROM class WHERE id=class_id;
END
但我在这里,我不知道如何走得更远。。。事实上,我可以从代码中检索类对象,然后从表中获取所有学生,但我想知道是否可以在一个过程中实现它?
您必须连接两个表:
SELECT c.id, c.name, s.id, s.name
FROM class AS c
INNER JOIN students AS s ON (s.ref_class_id = c.id)
WHERE c.id = class_id;
如果你最终也需要没有学生的课程,请使用LEFT JOIN
:(