25-多表查询-练习

最新推荐文章于 2023-02-10 20:25:16 发布
最新推荐文章于 2023-02-10 20:25:16 发布
阅读量1.3k 收藏 2
点赞数
分类专栏： JavaWeb 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shwjakak/article/details/121449424
版权

  注意这里要会看架构表

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');



-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');



-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);



-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

-- 需求：

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/* 分析：
1.五个列的信息 前三个是emp表中的信息需要查询emp表    职务名称，职务描述需要查询job表
所以第一步是要先看查哪几个表
2.查询条件where  emp.job_id(emp表的job_id)=job.id(job表的主键) 这里有外键*/
SELECT t1.`id`,
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`
FROM emp t1,job t2 
WHERE  t1.`job_id`=t2.`id`;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
   -- 分析：
  /* 1.员工编号，员工姓名，工资查的表为emp ，职务描述，部门名称job表
    部门名称，部门位置 dept表
    2.查询条件 emp.job_id =job.id  emp.job_id= job.id;*/
SELECT 
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`
FROM
emp t1,
job t2,
dept t3
WHERE
t1.`dept_id`=t3.`id` AND
t1.job_id= t2.id;
-- 3.查询员工姓名，工资，工资等级`emp``job``salarygrade`
/*分析：
1.员工姓名，工资查emp表    工资等级查salarygrade表  但是这两个表emp和salarygrade没有关联
2.条件 emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary
       简化：emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   
*/
SELECT
t1.`ename`,
t1.`salary`,
t2.`grade`
FROM
emp t1,
salarygrade t2
WHERE
t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：员工姓名，工资 查询emp表; 职务名称，职务描述 查询job表 ；
 部门名称，部门位置查询dept表； 工资等级查询salarygrade表
查询条件：
emp.job_id(emp表的job_id)=job.id(job表的主键) and emp.job_id =job.id 
and emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   
*/
SELECT 
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`,
t4.`grade`
FROM 
emp t1,
job t2,
dept t3,
salarygrade t4
WHERE
t1.`job_id`=t2.`id`  -- 员工和职位的关联
AND t1.`dept_id`= t3.`id`   -- 员工和部门的关联
AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
 /*分析
 1.部门编号、部门名称、部门位置 查询dept表
 部门人数 emp员工表
 2.使用分组查询按照emp.dept.id完成分组 查询count(id)
 3.使用子查询将第二步的查询结果和dept表进行关联查询
 */
 SELECT
 dept_id,
 COUNT(id)
 FROM
 emp
 GROUP BY 
 dept_id;
   -- 上面这个表作为一个虚拟表
   
   SELECT
   t1.`id`,t1.`dname`,t1.`loc`,t2.total
   FROM
   dept t1,
   (SELECT dept_id , COUNT(id) total -- 这里是给count起别名
   FROM emp
   GROUP BY  dept_id) t2 -- 虚拟表要打括号去引号
   WHERE
   t1.`id`= t2.dept_id; -- t1的主键接外键
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
分析：
1.姓名 查emp表 直接上级姓名mgr查询emp表  所以emp表的id和mgr是自关联
所以我们是在一个emp里查询
2.emp.id=emp.mgr
3.需要查询左表的全部数据 罗贯中没有领导  和交集数据
*/
SELECT
t1.`ename` '员工' ,
t1.`mgr`,
t2.`id`,
t2.`ename` '领导'  -- 这里也能起别名
FROM
emp t1,
emp t2  -- 查询两个emp表的方法就是起别名
WHERE
t1.`mgr`=t2.`id`; -- 注意这个顺序也不能错 
-- 一定是先找mgr=id 否则表内容是反的

SELECT
t1.`ename`,
t1.`mgr`,
t2.`id`,
t2.`ename`
FROM
emp t1
LEFT JOIN emp t2
ON t1.`mgr`=t2.`id`;

 

 这个连线就是外键  谁对应谁 注意看 1 ∞ 对应的元素位置都是有讲究的 从这里能够得到外键练习和where后面的查询条件

1.1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
分析：
1.五个列的信息 前三个是emp表中的信息需要查询emp表    职务名称，职务描述需要查询job表
所以第一步是要先看查哪几个表
2.查询条件where  emp.job_id(emp表的job_id)=job.id(job表的主键) 这里有外键

 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/* 分析：
1.五个列的信息 前三个是emp表中的信息需要查询emp表    职务名称，职务描述需要查询job表
所以第一步是要先看查哪几个表
2.查询条件where  emp.job_id(emp表的job_id)=job.id(job表的主键) 这里有外键*/
SELECT t1.`id`,t1.`ename`,t1.`salary`,t2.`jname`,t2.`description`
FROM emp t1,job t2 
WHERE  t1.`job_id`=t2.`id`;

 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
   -- 分析：
  1.员工编号，员工姓名，工资查的表为emp ，职务描述，部门名称job表
    部门名称，部门位置 dept表
    2.查询条件 emp.job_id =job.id  emp.job_id= job.id;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
   -- 分析：
  /* 1.员工编号，员工姓名，工资查的表为emp ，职务描述，部门名称job表
    部门名称，部门位置 dept表
    2.查询条件 emp.job_id =job.id  emp.job_id= job.id;*/
SELECT 
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`
FROM
emp t1,
job t2,
dept t3
WHERE
t1.`dept_id`=t3.`id` AND
t1.job_id= t2.id;

 

3.查询员工姓名，工资，工资等级`emp``job``salarygrade`

-- 3.查询员工姓名，工资，工资等级`emp``job``salarygrade`
分析：
1.员工姓名，工资查emp表    工资等级查salarygrade表  但是这两个表emp和salarygrade没有关联
2.条件 emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary
     用between and来  简化：emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   
 

-- 3.查询员工姓名，工资，工资等级`emp``job``salarygrade`
/*分析：
1.员工姓名，工资查emp表    工资等级查salarygrade表  但是这两个表emp和salarygrade没有关联
2.条件 emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary
       简化：emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   
*/
SELECT
t1.`ename`,
t1.`salary`,
t2.`grade`
FROM
emp t1,
salarygrade t2
WHERE
t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;

 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
分析：员工姓名，工资 查询emp表; 职务名称，职务描述 查询job表 ；
 部门名称，部门位置查询dept表； 工资等级查询salarygrade表
查询条件：
emp.job_id(emp表的job_id)=job.id(job表的主键) and emp.job_id =job.id 
and emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：员工姓名，工资 查询emp表; 职务名称，职务描述 查询job表 ；
 部门名称，部门位置查询dept表； 工资等级查询salarygrade表
查询条件：
emp.job_id(emp表的job_id)=job.id(job表的主键) and emp.job_id =job.id 
and emp.salary between salarygrade.losalary and emp.salary<=salarygrade.hisalary   
*/
SELECT 
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`,
t4.`grade`
FROM 
emp t1,
job t2,
dept t3,
salarygrade t4
WHERE
t1.`job_id`=t2.`id`  -- 员工和职位的关联
AND t1.`dept_id`= t3.`id`   -- 员工和部门的关联
AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;

 5. 查询出部门编号、部门名称、部门位置、部门人数
分析
 1.部门编号、部门名称、部门位置 查询dept表
 部门人数 emp员工表
 2.使用分组查询按照emp.dept.id完成分组 查询count(id)
 3.使用子查询将第二步的查询结果和dept表进行关联查询

 5.查询出部门编号、部门名称、部门位置、部门人数
 /*分析
 1.部门编号、部门名称、部门位置 查询dept表
 部门人数 emp员工表
 2.使用分组查询按照emp.dept.id完成分组 查询count(id)
 3.使用子查询将第二步的查询结果和dept表进行关联查询
 */
 SELECT
 dept_id,
 COUNT(id)
 FROM
 emp
 GROUP BY 
 dept_id;
   -- 上面这个表作为一个虚拟表
   
   SELECT
   t1.`id`,t1.`dname`,t1.`loc`,t2.total
   FROM
   dept t1,
   (SELECT dept_id , COUNT(id) total -- 这里是给count起别名
   FROM emp
   GROUP BY  dept_id) t2 -- 虚拟表要打括号去引号
   WHERE
   t1.`id`= t2.dept_id; -- t1的主键接外键

 6. 查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
分析：
1.姓名 查emp表 直接上级姓名mgr查询emp表  所以emp表的id和mgr是自关联
所以我们是在一个emp里查询
2.emp.id=emp.mgr
3.需要查询左表的全部数据 罗贯中没有领导  和交集数据

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
分析：
1.姓名 查emp表 直接上级姓名mgr查询emp表  所以emp表的id和mgr是自关联
所以我们是在一个emp里查询
2.emp.id=emp.mgr
3.需要查询左表的全部数据 罗贯中没有领导  和交集数据
*/
SELECT
t1.`ename` '员工' ,
t1.`mgr`,
t2.`id`,
t2.`ename` '领导'  -- 这里也能起别名
FROM
emp t1,
emp t2  -- 查询两个emp表的方法就是起别名
WHERE
t1.`mgr`=t2.`id`; -- 注意这个顺序也不能错 
-- 一定是先找mgr=id 否则表内容是反的

SELECT
t1.`ename`,
t1.`mgr`,
t2.`id`,
t2.`ename`
FROM
emp t1
LEFT JOIN emp t2
ON t1.`mgr`=t2.`id`;

这样查满足不了没有领导的员工也需要查询所以需要左表链接

山东大葱
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql基础题解
baid000的博客
01-14 2199
Mysql的一些基础题解，单表查询，多表查询
SQL数据库综合练习题及答案
weixin_42688085的博客
02-25 2880
写再前面的话 1，本文为Java学习五部分第二部分所有项目回顾（数据库练习系列-对应Java体系第二部分第一阶段内容）这些再前面的博客里有，再写会重复的。但是一想到我设计的第五部分第一阶段本来就要把那些值得记录的项目练习单独整理一遍。所以，理应如此，我会写的更好的。 2，整体项目系列博客（包括整体快递e站系列，数据库练习系列，前端练习系列，Javaweb系列，SSM系列，微服务系列，实战系列，等） 3，完整的Java体系链接（比你想的更多的Java内容），感谢一下开课吧。 需求介绍 自己没事的时候练习的。
[转载]Oracle数据库基础--SQL查询经典例题
weixin_30247307的博客
01-15 157
Oracle基础练习题，采用Oracle数据库自带的表，适合初学者，其中包括了一些简单的查询，已经具有Oracle自身特点的单行函数的应用 本文使用的实例表结构与表的数据如下： emp员工表结构如下： NameTypeNullableDefaultComments --------------------------------------...
oracle数据库--练习题
qq_43802706的博客
10-22 981
–1.列出至少有一个员工的所有部门。 select dname from dept where deptno in(select deptno from emp); –2.列出薪金比“SMITH”多的所有员工。 select * from emp where sal>(select sal from emp where ename =‘SMITH’); –3.列出所有员工的姓名及其直接上级的姓名。 select a.ename,(select ename from emp b where b.empn
查询每个员工的姓名和他的领导姓名
诗琪芮的博客
11-18 8928
//方法一：表重命名法 SELECT a.ename,(SELECT ename from emp b where b.empno = a.mgr) as boss from emp a; //方法二：左连接法 select a.ename , b.ename as boss_name from emp a left join emp b on b.empno = a.mgr;
经典sql查询(一)
东四十桥的博客
07-28 1315
员工表结构如下： CREATE TABLE `emp` ( `EMPNO` int(0) NOT NULL AUTO_INCREMENT COMMENT '员工号', `ENAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名', `JOB` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_
帆软FCRP-D数据库练习题2
07-13
本练习题涵盖了数据库管理系统中多个方面的知识点，包括数据查询、数据分析、数据统计、数据关联等。通过本练习题，读者可以熟悉数据库管理系统的基本概念和操作，提高数据库管理和分析能力。 1. 找出供应商名称，...
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL 查询练习（50题） MySQL 查询练习（50题） I 1 -- 建表 1 1.1 -- 学生表 1 1.2 -- 课程表 1 1.3 -- 教师表 1 1.4 -- 成绩表 2 1.5 -- 插入学生表测试数据 2 1.6 -- 课程表测试数据 2 1.7 -- 教师表测试数据 2 ...
JavaDz1-25-03-2021
03-26
【JavaDz1-25-03-2021】这个压缩包文件的标题和描述虽然简短，但可以推断出它可能是一个关于Java编程的学习资料或者项目源码，日期“25-03-2021”可能是课程、讲座或更新的时间。由于标签明确为“Java”，我们可以...
class-2021-03-25
04-01
【标题】"class-2021-03-25" 可能是指一个教育或培训课程，可能是一个编程、软件开发或者信息技术相关的课程，发生在2021年3月25日。这个标题暗示了这是一个特定日期的课堂活动记录，可能是教学材料、课件或者学生...
cruz-whatslab25
03-12
【描述】由于描述只给出了相同的标题“cruz-whatslab25”，没有提供更多的具体信息，我们可以推测这可能是一个学习或研究任务，可能包含一系列的HTML代码练习，或者是某个Web开发项目的一部分。这个项目可能要求参与...
MySQL学习笔记06【多表查询、子查询、多表查询练习】
upward
02-05 969
多表查询、子查询、多表查询练习
Day06 MySQL的多表查询总练习
m0_67042480的博客
02-10 489
MySQL多表查询的练习，建表时要注意外键的设置，练习涉及连接查询，子查询的使用
mysql-第三章 多表查询
a19464448的博客
09-07 975
– 第三章 多表查询 – 从多个表中获取数据 – oracle的多表连接写法：将连接条件写在where子句中 sql99标准写法 – oracle的多表连接写法：将连接条件写在where子句中 select * from emp; select * from dept; – 笛卡尔积 多表连接没有连接的条件 select * from emp,dept; – 60 条数据 15*4=60 1、查询员工姓名、员工编号、工作 、部门编号、工作地点、部门名称 – 注意： – ①连接条件中的列名相同时，使用表名.
MySQL基础学习-2
weixin_42152235的博客
11-04 145
DQL语言 基础查询 条件查询 排序查询 聚合函数 分组查询 分页查询 1. 语法：limit 开始的索引,每页查询的条数; 2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数 -- 每页显示3条记录 SELECT * FROM student LIMIT 0,3; -- 第1页 SELECT * FROM student LIMIT 3,3; -- 第2页 SELECT * FROM student LIMIT 6,3; -- 第3页 3. li
PLSQL练习
GHHCNGC的博客
07-17 1247
---1、列出所有员工的姓名以及其直接上级的姓名 select e.ename,m.ename from emp e,emp m where e.empno=m.mgr ---2、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称 select empno,ename,dname from emp,dept where emp.deptno=dept.deptno ---3、列出部门名...
Oracle查询每个员工领导名称，要求显示结果为:员工号，员工姓名 员工领导名
十年饮冰，难凉热血
07-11 9554
--子查询 SELECT t.empno 员工号,t.ename 员工姓名, (SELECT ename FROM emp WHERE empno=t.mgr) 领导姓名 FROM emp t
sql查询练习
aixing0262的博客
08-11 1537
题目和条件 -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'...
mysql查询练习
最新发布
05-31
以下是一个简单的 MySQL 查询练习： 给定一个名为 `students` 的表，包含以下列：`id`、`name`、`age`、`gender`、`class`、`score`。 请编写 SQL 查询语句，以显示所有年龄在 18 到 25 岁之间、成绩大于 90 分的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值