20200324 连接查询作业

本贴最后更新于 1525 天前,其中的信息可能已经时移世异

根据课上的emp和dept表完成下列作业:

  1. 查询张姓员工的员工信息和所在部门信息。
  2. 查询张三丰管理了几个员工
  3. 查询出所有实习员工(实习员工无部门信息)

建表语句

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

上课实战脚本

#交叉连接
select * from emp;
#笛卡尔积
select * from emp,dept;
select * from emp cross join dept;
#内连接
#显示内连接标准内连接 on 两张表如何关联
select * from emp as a inner join dept as b on a.dept_id = b.id;
#隐式内连接 where 在结果集的基础上做条件筛选
select * from emp as a,dept as b where a.dept_id = b.id;
#内连接 join 省略 inner 
select * from emp as a join dept as b on a.dept_id = b.id;
#内连接 cross join
select * from emp as a cross join dept as b on a.dept_id = b.id;
# mysql cross join on  inner join on 结果上没有区别
# 标准sql中 cross join 不能使用onmysql中支持on的操作
#特殊内连接 不等值内连接
select * from emp as a inner join dept as b on a.dept_id > b.id;
#特殊内连接 自连接
select * from emp as a inner join emp as b on a.id = b.leader;
#外连接
#外连接显示的内容比内连接要多内连接的补充 >=
#左外连接左表为主表右表为从表主表所有数据都显示从表只有匹配上的数据才显示
select * from emp as a left join dept as b on a.dept_id = b.id;
#左外连接右表为主表左表为从表主表所有数据都显示从表只有匹配上的数据才显示
select * from emp as a right join dept as b on a.dept_id = b.id;
select * from dept as b left join emp as a  on a.dept_id = b.id;
#外连接 查询从表为空的数据
select * from emp as a left join dept as b on a.dept_id = b.id
where b.id is null;
# on两张表如何关联on在where之前  where结果集的基础上做条件筛选的区别 
select * from emp as a left join dept as b 
on a.dept_id = b.id where b.id is null;
#全连接 full join 
#sql1
#UNION
#sql2;
#sql1 + sql2 
select * from emp as a left join dept as b on a.dept_id = b.id
union
select * from emp as a right join dept as b on a.dept_id = b.id;
# union all 不去重复
select * from emp as a left join dept as b on a.dept_id = b.id
union all
select * from emp as a right join dept as b on a.dept_id = b.id;
1 操作
luojie 在 2020-08-06 17:33:22 更新了该帖
76 回帖
请输入回帖内容 ...
  • jeck

    image.png
    image.png
    image.png

  • 其他回帖
  • che

    1、查询张姓员工的员工信息和所在部门信息。

    select * from emp left join dept on dept.id=emp.dept_id where emp.name like '张%'

    2、查询张三丰管理了几个员工

    select emp.name from emp A join emp on A.id=emp.leader where emp.leader=1

    3、查询出所有实习员工(实习员工无部门信息)
    select * from emp left join dept on dept.id=emp.dept_id where dept.name is null

  • stern

    image.png
    image.png
    image.png
    image.png

  • zhongyh

    -- 根据课上的emp和dept表完成下列作业:
    -- 1、查询张姓员工的员工信息和所在部门信息。
    #判断以哪张表为基表,看最主要的是得到那张表的信息,或者要筛选的是哪那张表的信息
    SELECT * from emp e left JOIN dept d on e.dept_id =d.id where e.name like '张%';

    -- 2、查询张三丰管理了几个员工
    select count(*) from emp e1 INNER JOIN emp e2 on e1.leader=e2.dept_id and e1.name='张三丰';

    -- 3、查询出所有实习员工(实习员工无部门信息)
    select * from emp e left join dept d on e.dept_id = d.id where d.id is NULL;

  • 查看更多回帖