Mybatis中的连接查询与嵌套查询

最新推荐文章于 2025-06-27 21:51:09 发布

原创

最新推荐文章于 2025-06-27 21:51:09 发布 · 1.3w 阅读

60 ·

CC 4.0 BY-SA版权

文章标签：

#Mybatis #连接查询 #嵌套查询

首先在mysql中确立表：

#表一：地址国家表
CREATE TABLE address(aid INT AUTO_INCREMENT PRIMARY KEY,aname VARCHAR(20));

INSERT INTO address VALUES(NULL,"魏国");
INSERT INTO address VALUES(NULL,"蜀国");
INSERT INTO address VALUES(NULL,"吴国");


#表二：出场人物表
CREATE TABLE person(
	pid INT AUTO_INCREMENT PRIMARY KEY,
	pname VARCHAR(20),
	paid INT,
	CONSTRAINT pafk FOREIGN KEY person(paid) REFERENCES address(aid) ON UPDATE CASCADE ON DELETE CASCADE
	);
	
INSERT INTO person VALUES(1,"曹操",1);
INSERT INTO person VALUES(2,"荀彧",1);
INSERT INTO person VALUES(3,"张辽",1);

INSERT INTO person VALUES(4,"刘备",2);
INSERT INTO person VALUES(5,"关羽",2);
INSERT INTO person VALUES(6,"张飞",2);
INSERT INTO person VALUES(7,"诸葛亮",2);

INSERT INTO person VALUES(8,"孙权",3);
INSERT INTO person VALUES(9,"周瑜",3);
INSERT INTO person VALUES(10,"陆逊",3);

INSERT INTO person VALUES(11,"公孙瓒",NULL);


#表三：交通工具表
CREATE TABLE tool(tid INT AUTO_INCREMENT PRIMARY KEY,tname VARCHAR(20));
INSERT INTO tool VALUES(1,"马");
INSERT INTO tool VALUES(2,"船");


#表四：地址国家——交通工具 多对多关系表
CREATE TABLE aandt(
	a_aid INT,
	a_tid INT,
	PRIMARY KEY(a_aid,a_tid),#联合主键，是指多个字段组成一个组合，该组合在数据表中唯一
	CONSTRAINT FOREIGN KEY aandt(a_aid) REFERENCES address(aid) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT FOREIGN KEY aandt(a_tid) REFERENCES tool(tid) ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO aandt VALUES(1,1);
INSERT INTO aandt VALUES(2,1);
INSERT INTO aandt VALUES(2,2);
INSERT INTO aan