Mysql8.0习题系列

软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9

文章目录

1.多表查询1

1.1题目

1.显示所有员工的姓名，部门号和部门名称。

2.查询90号部门员工的job_id和90号部门的location_id

3.选择所有有奖金的员工的 last_name , department_name , location_id , city

4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp # manager Mgr#

kochhar 101 king 100

7.查询哪些部门没有员工

8. 查询哪个城市没有部门

9. 查询部门名为 Sales 或 IT 的员工信息

1.2答案

1.显示所有员工的姓名，部门号和部门名称。

SELECT

  last_name,

  e.department_id,

  d.department_name

FROM

  employees e

  LEFT OUTER JOIN departments d

    ON e.department_id = d.department_id ;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT

  job_id,

  location_id

FROM

  employees e

  INNER JOIN departments d

    ON e.department_id = 90 ;

3.选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name, department_name, d.location_id, city

FROM employees e

LEFT OUTER JOIN departments d

ON e.`department_id` = d.`department_id`

LEFT OUTER JOIN locations l

ON l.`location_id` = d.`location_id`

WHERE e.`commission_pct` IS NOT NULL;

4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

SELECT e.last_name, e.job_id,e.department_id,department_name

FROM employees e

JOIN departments d

ON e.`department_id` = d.`department_id`

JOIN locations l

ON d.`location_id` = l.`location_id`

WHERE city = 'Toronto';

5 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT department_name, street_address, last_name, job_id, salary

FROM employees e

JOIN departments d

ON e.`department_id` = d.`department_id`

JOIN locations l

ON d.`location_id` = l.`location_id`

WHERE department_name ='Executive';

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp # manager Mgr#
kochhar 101 king 100

SELECT e.last_name, e.employee_id 'emp#' , m.last_name manager , m.employee_id 'mgr#'

FROM employees e

JOIN employees m

ON e.`manager_id` = m.`employee_id`

WHERE e.last_name = 'kochhar';

7.查询哪些部门没有员工。

思路：用外连接得到departments表与employees的连接关系，再筛选employee_id为null的departments表

SELECT d.department_id

FROM departments d

LEFT OUTER JOIN employees e

ON e.`department_id` = d.`department_id`

WHERE employee_id IS NULL;

8.查询哪个城市没有部门 查询哪个城市没有部门，同样的思路，先查询location表，外连接departments表

SELECT city

FROM locations l

LEFT OUTER JOIN departments d

ON l.`location_id` = d.`location_id`

WHERE department_id IS NULL;

9. 查询部门名为 Sales 或 IT 的员工信息

查询部门名为sales或id的员工信息

SELECT * FROM

employees e

JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE department_name IN ('Sales','IT');

2.多表查询2

2.1题目

#1.所有有门派的人员信息

#2.列出所有用户，并显示其机构信息

#3.列出所有门派

#4.所有不入门派的人员

#5.所有没人入的门派

#6.列出所有人员和机构的对照关系

MySQL Full Join的实现 。因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

#7.列出所有没入派的人员和没人入的门派

首先创造数据集

CREATE DATABASE ex4;

CREATE TABLE `t_dept` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`deptName` VARCHAR(30) DEFAULT NULL,

`address` VARCHAR(40) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20) DEFAULT NULL,

`age` INT(3) DEFAULT NULL,

`deptId` INT(11) DEFAULT NULL,

empno INT NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_dept_id` (`deptId`)

#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');

INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');

INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');

INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');

INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');

INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);

2.2 答案

1.所有有门派的人员信息

SELECT * FROM

t_emp e

INNER  JOIN

t_dept d

ON e.`deptId` = d.id;

2.列出所有用户，并显示其机构信息

SELECT * FROM

t_emp e LEFT JOIN t_dept d

ON e.`deptId` = d.`id`;

3.列出所有门派

SELECT * FROM t_dept;

4.所有不入门派的人员

SELECT * FROM t_emp e

LEFT OUTER JOIN t_dept d

ON e.`deptId` = d.`id`

WHERE deptId IS NULL;

5.所有没人入的门派

SELECT * FROM t_dept d

LEFT OUTER JOIN t_emp e

ON e.`deptId` = d.`id`

WHERE e.`deptId` IS NULL;

6.列出所有人员和机构的对照关系

MySQL Full Join的实现 。因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join

SELECT *

FROM t_emp e LEFT JOIN t_dept d

ON e.`deptId` = d.`id`

UNION

SELECT *

FROM t_emp e RIGHT JOIN t_dept d

ON e.deptId = d.id;

7.列出所有没入派的人员和没人入的门派

SELECT *

FROM t_emp e LEFT OUTER JOIN t_dept d

ON e.deptId = d.id

WHERE e.`deptId` IS NULL

UNION

SELECT *

FROM t_emp e RIGHT OUTER JOIN t_dept d

ON e.deptId = d.id

WHERE e.`deptId` IS NULL;

最新文章

  1. iFrame 功能详解
  2. SQL语句优化实践
  3. Java for LeetCode 236 Lowest Common Ancestor of a Binary Tree
  4. Spring的定时任务配置2（转）
  5. http://www.cnblogs.com/Matrix54/archive/2012/05/03/2481260.html
  6. Qt, 我回来了。。。
  7. TCP协议的3次握手与4次挥手过程详解
  8. ANDROID内存优化(大汇总——上)
  9. UIImagePickerController从拍照、图库、相册获取图片
  10. 硬盘重装Ubuntu12.04的感受
  11. remine chart2安装
  12. date()中的getYear的小问题
  13. AQS原理以及AQS同步组件总结
  14. hive drop 报错
  15. 洛谷P3038 [USACO11DEC]牧草种植Grass Planting
  16. HDU1789 Doing Homework again 做作业【贪心】
  17. How to Use GNOME Shell Extensions
  18. Xmind 8 pro 软件破解版
  19. #incldue<cctype>函数系列
  20. 实习第一天：static 声明的 变量和 方法

热门文章

  1. C++11(列表初始化+变量类型推导+类型转换+左右值概念、引用+完美转发和万能应用+定位new+可变参数模板+emplace接口)
  2. Python实验报告（第2章）
  3. 2022弱口令实验室招新赛CTF赛道WriteUp
  4. ESXI 7.0封装网卡驱动
  5. Java学习笔记：2022年1月7日
  6. 真正“搞”懂HTTP协议09之这个饼干不能吃
  7. （20）go-micro微服务Elasticsearch使用
  8. 迁移学习（ADDA）《Adversarial Discriminative Domain Adaptation》
  9. 报错One record is expected, but the query result is multiple records
  10. Python风格规范(转载)