sql語句聯表查詢之(join….on)

NO IMAGE

表1:

— 表的結構 `bumen`

CREATE TABLE IF NOT EXISTS `bumen` (
  `bid` int(10) NOT NULL AUTO_INCREMENT,
  `mingcheng` varchar(30) DEFAULT NULL,
  `jingli` varchar(30) DEFAULT NULL,
  `tel` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;


— 轉存表中的資料 `bumen`

INSERT INTO `bumen` (`bid`, `mingcheng`, `jingli`, `tel`) VALUES
(1, ‘技術部’, ‘小雍’, ‘15236018201’),
(2, ‘人事部’, ‘小東’, ‘15236015648’),
(3, ‘市場部’, ‘小高’, ‘154565648’);
表2:

— 表的結構 `zhigong`

CREATE TABLE IF NOT EXISTS `zhigong` (
  `zid` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `zhiwu` varchar(30) DEFAULT NULL,
  `price` int(10) DEFAULT NULL,
  `bnumber` int(10) DEFAULT NULL,
  PRIMARY KEY (`zid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;


— 轉存表中的資料 `zhigong`

INSERT INTO `zhigong` (`zid`, `name`, `age`, `zhiwu`, `price`, `bnumber`) VALUES
(1, ‘小高’, 40, ‘員工’, 2000, 1),
(2, ‘李亮’, 42, ‘員工’, 2000, 2),
(3, ‘李亮’, 42, ‘員工’, 2000, 2),
(4, ‘小亮’, 38, ‘員工’, 2000, 2),
(5, ‘張三’, 40, ‘員工’, 2000, 3),
(6, ‘李四’, 41, ‘員工’, 2000, 2);

如:查詢 “人事部”年齡在40歲以上的職工號,姓名,職務,年齡;
有兩種查詢方式:
select z.zid,z.name,z.zhiwu,z.age from zhigong z join bumen b on z.bnumber=b.bid where b.mingcheng=’人事部’ and age>40;
select z.zid,z.name,z.zhiwu,z.age from zhigong z,bumen b where z.bnumber=b.bid and b.mingcheng=’人事部’ and age>40;
這兩個都可以;建議使用join…on