MySQL高级系列-7种join理论

1
-- 部门表
2
CREATE TABLE `tbl_dept` (
3
  `id` int(11) NOT NULL AUTO_INCREMENT,
4
  `deptName` varchar(30) DEFAULT NULL,
5
  `locAdd` varchar(40) DEFAULT NULL,
6
  PRIMARY KEY (`id`)
7
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8

9
-- 员工表
10
CREATE TABLE `tbl_emp` (
11
  `id` int(11) NOT NULL AUTO_INCREMENT,
12
  `name` varchar(20) DEFAULT NULL,
13
  `deptId` int(11) DEFAULT NULL,
14
  PRIMARY KEY (`id`),
15
  KEY `deptId` (`deptId`)
16
  # CONSTRAINT `tbl_emp_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
17
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18

19
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
20
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
21
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
22
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
23
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
24

25
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (1, 'z3', 1);
26
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (2, 'z4', 1);
27
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (3, 'z5', 1);
28
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (4, 'w5', 2);
29
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (5, 'w6', 2);
30
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (6, 's7', 3);
31
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (7, 's8', 4);
32
INSERT INTO `tbl_emp`(`id`, `name`, `deptId`) VALUES (8, 's9', 51);