测试必备的Mysql常用sql语句系列
https://www.cnblogs.com/poloyy/category/1683347.html
查询类型 | 简述 | 图表 |
---|---|---|
inner join | 获取两个表中字段相互匹配关系的记录 即两表记录都不为null才会返回 | |
left join | 获取左表所有记录 右表为空的字段补null | |
right join | 获取右表所有记录 左表为空的字段补null | |
union | 获取左右表所有记录 | |
cross join | 两张表的笛卡尔积 |
后面详细讲每种多表查询的时候,会用到这些数据表和数据;先跑一跑创建下哦!
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` int(11) NOT NULL COMMENT '员工编号',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
`dept_id` int(11) NULL DEFAULT NULL COMMENT '部门编号',
`leader` int(11) NULL DEFAULT NULL COMMENT '直属领导id',
`is_enable` int(11) NULL DEFAULT NULL COMMENT '是否在职 1在职 0离职',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `emp` VALUES (1, '张三丰', 1, 0, 1);
INSERT INTO `emp` VALUES (2, '张无忌', 1, 1, 1);
INSERT INTO `emp` VALUES (3, '小龙女', 1, 1, 1);
INSERT INTO `emp` VALUES (4, '小白菜', 1, 3, 1);
INSERT INTO `emp` VALUES (5, '韦小宝', 2, 0, 1);
INSERT INTO `emp` VALUES (6, '令狐冲', 2, 0, 1);
INSERT INTO `emp` VALUES (7, '东方不败', 0, 8, 1);
INSERT INTO `emp` VALUES (8, '任我行', 3, 0, 1);
INSERT INTO `emp` VALUES (9, '李寻欢', 0, 8, 1);
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` int(11) NOT NULL COMMENT '部门id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `dept` VALUES (1, '销售部');
INSERT INTO `dept` VALUES (2, '信息技术部');
INSERT INTO `dept` VALUES (3, '财务部');
INSERT INTO `dept` VALUES (4, '有关部门');