java 自动化 20200324 连接查询作业

luojie · 2020年03月24日 · 最后由 luojie 回复于 2020年03月26日 · 832 次阅读

根据课上的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(两张表如何关联)onwhere之前  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;
共收到 69 条回复
3楼 已删除
jeck 回复

棒棒哒,满分

xiaolan 回复

第三题再想一想,是不是有更好的解决方案~

7楼 已删除
8楼 已删除

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

3ice 回复

大兄弟,这速度可以,解题的思路满分~

11楼 已删除

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

SELECT e.name,e.leader,e.is_enable,dt.nameas 部门 from emp e left JOIN dept dt on e.dept_id=dt.id where e.name like '张%';

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

#方法一:

SELECT count(*) from emp ep where ep.leader in(SELECT id from emp where name='张三丰');

#方法二:

SELECT count(em1.name) as 所管理员工数,em2.name as 领导 from emp em1 LEFT JOIN emp em2 on em1.leader=em2.id GROUP BY em2.name having em2.name='张三丰';

-- 查询出所有实习员工(实习员工无部门信息)

SELECT ep.*,dt.nameas 部门名称 from emp ep LEFT JOIN dept dt on ep.dept_id=dt.id where dt.name is NULL

15楼 已删除

1、查询张姓员工的员工信息和所在部门信息。 select * from emp left join dept on emp.dept_id = dept.id where emp.name like '张%' 2、查询张三丰管理了几个员工 select count(*) from emp e Inner join emp on e.id=emp.leader where emp.leader=1 3、查询出所有实习员工(实习员工无部门信息) select * from emp left join dept on emp.dept_id = dept.id where dept.id is null

19楼 已删除

太不容易了~~没有工具只能去网上马不停蹄地找工具然后下载安装(mysql服务器&客户端、navicat工具); 下载并成功安装后才开始写SQL语句! 我太难了😂 😂 😂

24楼 已删除

第二题真不知怎么算出来,我还是借鉴别的同学的,所以算是抄袭了

根据课上的emp和dept表完成下列作业: 1、查询张姓员工的员工信息和所在部门信息。 select * from emp as a inner join dept as b on a.dept_id=b.id where a.name like "张%"; 2、查询张三丰管理了几个员工 select count(*) from emp as a inner join emp as b on a.id=b.leader where a.name="张三丰" and b.is_enable=1; 3、查询出所有实习员工(实习员工无部门信息) select * from emp as a left join dept as b on a.dept_id=b.id where b.id is null and is_enable=1;

第一题:select * from emp as a inner join dept as b on a.dept_id=b.id where a.name like '张%';

第二题:select * from emp as a where dept_id=1 and leader=1;

不知道怎么计数

第三题:select * from emp as a where dept_id=1 and leader=1

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

1:select * from emp inner join dept on emp.id=dept.id where emp.name like '张%'

2: select count() from emp as a inner join emp as b on a.leader = b.id where b.name ='张三丰' select count() from emp as a inner join emp as b on a.leader = b.id and b.name ='张三丰' 发现第二题用where 和and 的结果是一样的

3:select * from emp where dept_id = 0

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

SELECT * FROM emp a LEFT JOIN dept b ON a.dept_id = b.id WHERE a.name LIKE '张%'

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

SELECT count(b.id) FROM emp a INNER JOIN emp b ON a.id = b.leader WHERE a.name = '张三丰'

3、查询出所有实习员工(实习员工无部门信息)

SELECT * FROM emp a LEFT JOIN dept b ON a.dept_id = b.id where b.id IS NULL

luojie 回复

好的

cherrybing 回复

666,多种方式开拓思维~

jtl 回复

第三题有点小瑕疵

zoe 回复

第二、三题可以看看其他同学的答案

hh_1 回复

兄弟,怎么只提交了一题的答案?

yingchun_x 回复

满分~

xnyf00 回复

第三题再想想~

happybird 回复

666,满分

mo_xiaoxi 回复

第三题能用连接查询做出来吗?

youmaobing233 回复

因为你是内连接,两个条件是可以并列的,所以where 和 and没有区别

~满分~~满分~

donghe 回复

图文结合,非常好,满分

13207145280 回复

第二题需要用到分组和统计函数 第二第三题都可以参考一下其他同学的答案

stern 回复

满分~~~666

xiaoh 回复

第二题不能直接用leader=1哦~

lingdang 回复

1、2、3题都有一些小瑕疵,可以看看其他同学怎么做的

freeza 回复

第二题有瑕疵~

bottle_7 回复

第三题有瑕疵~

sxd 回复

第一题有瑕疵~

yy717 回复

满分~~~~

huyping 回复

第一第二题能不能不用子查询解题呢?

hljmingxi 回复

不容易,加油~

XJJ_2020 回复

第二题不能直接用leader=1

weiyang77 回复

很棒~满分

13141001779 回复

第二题条件不全

Iam 回复

第二题为什么用like,直接用等号好就行了~

liuyue824 回复

第三题不用子查询怎么实现?

yimh 回复

满分!

-- 根据课上的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;

zhongyh 回复

满分~~~~!!!

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册