【MySQL】听柠檬班公开课后，学习笔记及作业（一）

此笔记为听柠檬班公开课后笔记，特为其打call，如有侵权，请随时联系我删帖哦

一、课堂笔记

交叉连接

结果为笛卡尔积（两表条数相乘），两种写法，分别如下：

• select * from table1, table2;
• select * from table1 cross join table2;

内连接

显示两表连接后，匹配的字段，几种写法如下，推荐第一种写法：

• 标准内连接写法：
  select * from table1 as a
  inner join table2 as b on a.id = b.id;
  on 两张表如何连接
• 隐式内连接：
  select * from table1 as a, table2 as b
  where a.id = b.id;
  where 在结果集上做条件筛选
• 省略 inner，直接…join…on…
• …cross join …on…（mysql支持，其他数据库不一定支持）
• 特殊内连接，自连接：
  select * from table as a
  inner join table as b on a.id = b.leader;

外连接

显示内容比内连接多，相当于内连接的补充

• 左连接：
  select * from table1 as a
  left join table2 as b on a.id = b.id;
  table1 为主表，显示所有数据
  table2 为从表，只显示匹配上on条件的数据
• 左连接，查询从表为空的数据：
  select …from…
  left join …on…
  where b.id is null;
• 右连接：right join

全连接

• union 相当于sql语句1+sql语句2，且去重
• union all 不去重

作业

建表语句

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, '有关部门');

表

• dept
  
• emp
  

作业

根据课上的emp和dept表完成下列作业：
1、查询张姓员工的员工信息和所在部门信息。
2、查询张三丰管理了几个员工
3、查询出所有实习员工（实习员工无部门信息）

解题

#根据课上的emp和dept表完成下列作业： 
#1、查询张姓员工的员工信息和所在部门信息。
SELECT * FROM emp ep 
INNER JOIN dept dt on ep.dept_id = dt.id
WHERE ep.name LIKE '张%'

#2、查询张三丰管理了几个员工
SELECT a.name, b.name as zhang_emps FROM emp a 
LEFT JOIN emp b on a.id = b.leader 
WHERE a.name = '张三丰' and  a.is_enable = 1

SELECT a.name, b.name as zhang_emps FROM emp a 
INNER JOIN emp b on a.id = b.leader
and a.name = '张三丰' and  a.is_enable = 1

#3、查询出所有实习员工（实习员工无部门信息）
SELECT * FROM emp ep 
LEFT JOIN dept dt on ep.dept_id =dt.id 
WHERE dt.id IS NULL and ep.is_enable = 1