涉及3个表--
-- 表1的结构 `User`
-- CREATE TABLE `User` (
`userID` int(8) NOT NULL auto_increment,
`email` varchar(150) NOT NULL UNIQUE,
`password` varchar(50) NOT NULL,
`truename` varchar(20) NOT NULL,
`sex` tinyint(1) NOT NULL,
`grade` varchar(10) NOT NULL,
`issecret` tinyint(1) NOT NULL,
PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;--
-- 导出表中的数据 `User`
-- INSERT INTO `User` (`email`, `password`, `truename`, `sex`, `grade`, `issecret`) VALUES
('[email protected]', 'admin', '虾皮', 0, '2006', 0);
INSERT INTO `User` (`email`, `password`, `truename`, `sex`, `grade`, `issecret`) VALUES
('[email protected]', 'admin', 'asd', 0, '2006', 0);
--
-- 表2的结构 `UserInfo`
-- CREATE TABLE `UserInfo` (
`userID` int(8) NOT NULL,
`academicID` int(8) NOT NULL,
`majorID` int(8) NOT NULL,
`userphoto` varchar(200) character set latin1 NOT NULL,
`englishname` varchar(30),
`birthday` varchar(20),
`star` varchar(20),
`bloodtype` varchar(20),
`liveplace` varchar(150),
`address` varchar(150),
`email` varchar(150),
`cellphone` varchar(15),
`telphone` varchar(15),
`qq` varchar(10),
`postcode` varchar(7),
`visit` int,
FOREIGN KEY (`userID`) REFERENCES User(`userID`),
FOREIGN KEY (`academicID`) REFERENCES Academic(`academicID`),
FOREIGN KEY (`majorID`) REFERENCES Major(`majorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- 表3的结构 `DynamicInfo`
-- CREATE TABLE `DynamicInfo` (
`sourceID` int(8) NOT NULL,
`destID` int(8) NOT NULL,
`content` varchar(100),
`date` datetime NOT NULL,
`type` tinyint(2) NOT NULL,
FOREIGN KEY (`sourceID`) REFERENCES User(`userID`),
FOREIGN KEY (`destID`) REFERENCES User(`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `xiaoyou`.`dynamicinfo` (
`sourceID` ,
`destID` ,
`content` ,
`date` ,
`type`
)
VALUES (
'1', '2', NULL , '', ''
);--------------------------------------------------------------
现我想搜索destID为2的sourceID的userID , truename ,userphoto , date 应该出现的是 useID为1的信息吧 可就是不出结果 请教下啊 谢谢了 我到语句如下:
SELECT user.userID AS userID, user.truename, userinfo.userphoto,dynamicinfo.date
FROM user, userinfo, dynamicinfo
WHERE user.userID
IN ( SELECT sourceID
FROM dynamicinfo
WHERE destID =2
AND TYPE =4)
AND user.userID = userinfo.userID
AND user.userID =dynamicinfo.sourceID
AND destID =2
-- 表1的结构 `User`
-- CREATE TABLE `User` (
`userID` int(8) NOT NULL auto_increment,
`email` varchar(150) NOT NULL UNIQUE,
`password` varchar(50) NOT NULL,
`truename` varchar(20) NOT NULL,
`sex` tinyint(1) NOT NULL,
`grade` varchar(10) NOT NULL,
`issecret` tinyint(1) NOT NULL,
PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;--
-- 导出表中的数据 `User`
-- INSERT INTO `User` (`email`, `password`, `truename`, `sex`, `grade`, `issecret`) VALUES
('[email protected]', 'admin', '虾皮', 0, '2006', 0);
INSERT INTO `User` (`email`, `password`, `truename`, `sex`, `grade`, `issecret`) VALUES
('[email protected]', 'admin', 'asd', 0, '2006', 0);
--
-- 表2的结构 `UserInfo`
-- CREATE TABLE `UserInfo` (
`userID` int(8) NOT NULL,
`academicID` int(8) NOT NULL,
`majorID` int(8) NOT NULL,
`userphoto` varchar(200) character set latin1 NOT NULL,
`englishname` varchar(30),
`birthday` varchar(20),
`star` varchar(20),
`bloodtype` varchar(20),
`liveplace` varchar(150),
`address` varchar(150),
`email` varchar(150),
`cellphone` varchar(15),
`telphone` varchar(15),
`qq` varchar(10),
`postcode` varchar(7),
`visit` int,
FOREIGN KEY (`userID`) REFERENCES User(`userID`),
FOREIGN KEY (`academicID`) REFERENCES Academic(`academicID`),
FOREIGN KEY (`majorID`) REFERENCES Major(`majorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- 表3的结构 `DynamicInfo`
-- CREATE TABLE `DynamicInfo` (
`sourceID` int(8) NOT NULL,
`destID` int(8) NOT NULL,
`content` varchar(100),
`date` datetime NOT NULL,
`type` tinyint(2) NOT NULL,
FOREIGN KEY (`sourceID`) REFERENCES User(`userID`),
FOREIGN KEY (`destID`) REFERENCES User(`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `xiaoyou`.`dynamicinfo` (
`sourceID` ,
`destID` ,
`content` ,
`date` ,
`type`
)
VALUES (
'1', '2', NULL , '', ''
);--------------------------------------------------------------
现我想搜索destID为2的sourceID的userID , truename ,userphoto , date 应该出现的是 useID为1的信息吧 可就是不出结果 请教下啊 谢谢了 我到语句如下:
SELECT user.userID AS userID, user.truename, userinfo.userphoto,dynamicinfo.date
FROM user, userinfo, dynamicinfo
WHERE user.userID
IN ( SELECT sourceID
FROM dynamicinfo
WHERE destID =2
AND TYPE =4)
AND user.userID = userinfo.userID
AND user.userID =dynamicinfo.sourceID
AND destID =2
解决方案 »
- 谁有空帮忙写个SQL语句啊?
- 进行一个树状菜单的数据库设计,这个树状菜单可以有无限级的子菜单
- vb 连接MYsql多表查询 出错 多步操作产生错误。请检查每一步的状态值
- postgresql 存储结构返回问题
- 求一句:相同代码的记录中,日期离现在时间最近的一条记录
- 在win2003下怎样修改mysql的连接数?默认是100;同时mysql在win2003下的日志文件在那里?
- mysql 导入导出某个指定的表
- 想问一下,mysql,postgresql能支持多大容量数据库?在事务,视图,存储过程,子查询方面是怎样的?
- 两个索引,explain选择的原则?
- 请教大家一个备份问题
- mysql 占用内存才64M,请问如何让它多占用些内存
- 如何安装InnoDB?
from DynamicInfo inner join User on DynamicInfo.sourceID=User.userID
left join UserInfo on UserInfo.userID=User.userID
where destID =2你的 UserInfo 表没有相应的记录。