MySQL关系表查询两个表的数据

如下，有四张表：游戏类型表，游戏表，点卡和游戏关系表，点卡表

CREATE TABLE `gamesType`(
`tId` INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT'游戏类型编号',
`tName` VARCHAR(10) NOT NULL COMMENT'游戏类型名称'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='游戏类型表';

CREATE TABLE `games`(
`gid` INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT'游戏编号',
`gName` VARCHAR(10) NOT NULL COMMENT'游戏名称',
`tId` INT NOT NULL COMMENT'所属游戏类型'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='游戏表';

CREATE TABLE `gameOrCard`(
`id` INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT'编号',
`cid` INT NOT NULL COMMENT'点卡编号',
`gId` INT NOT NULL COMMENT'支持支付游戏编号'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='点卡和游戏关系表';

CREATE TABLE `cards`(
`cId` INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT'点卡编号',
`cName` VARCHAR(20) NOT NULL COMMENT'点卡名称',
`titleImg` VARCHAR(20) NOT NULL COMMENT'点卡图标',
`iniPrice` DOUBLE NOT NULL COMMENT'原价格',
`price` DOUBLE NOT NULL COMMENT'售价',
`cInfo` VARCHAR(200) NOT NULL COMMENT'点卡介绍',
`praiseRate` DOUBLE NOT NULL COMMENT'好评率',
`upTime` TIMESTAMP NOT NULL COMMENT'上架时间',
`isShelves` INT(1) NOT NULL COMMENT'上架状态0上架1下架',
`number` INT NOT NULL COMMENT'数量'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='点卡表';

 

上述已经建立好了主外键关系。

要查找“游戏类型为网络游戏的游戏信息及其支持该游戏充值得点卡信息，并且该点卡为上架状态”：

SELECT c.*,g.* FROM `cards` AS c JOIN `gameOrCard` AS ga ON c.cid=ga.cid JOIN `games` AS g ON g.`gid`=ga.`gid` WHERE `tid`=1 AND c.isShelves=0 ORDER BY g.gid;

 

转载于:https://www.cnblogs.com/zeussbook/p/8734775.html