Mysql 查询的具体例题解析

最新推荐文章于 2024-05-20 16:30:30 发布

丶丶丶丶。

最新推荐文章于 2024-05-20 16:30:30 发布

阅读量406

点赞数 1

文章标签： mysql 数据库 java

本文链接：https://blog.youkuaiyun.com/weixin_44691025/article/details/125601365

版权

本文通过具体实例解析了Mysql查询技巧，涵盖了基本查询、多表连接、子查询等多种场景，帮助读者掌握复杂查询的构建方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql 查询例题解析

/*部门表*/
CREATE TABLE dept(
    deptnu      INT  PRIMARY KEY comment '部门编号',
    dname       VARCHAR(50) comment '部门名称',
    addr        VARCHAR(50) comment '部门地址'
);

/*员工表*/
CREATE TABLE emp(
    empno       INT  PRIMARY KEY comment '雇员编号',
    ename       VARCHAR(50) comment '雇员姓名',
    job         VARCHAR(50) comment '雇员职位',
    mgr         INT comment '雇员上级编号',
    hiredate    DATE comment '雇佣日期',
    sal         DECIMAL(7,2) comment '薪资',
    deptnu      INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*工资等级表*/
CREATE TABLE sal(
    grade       INT  PRIMARY KEY comment '等级',
    lowsal      INT comment '最低薪资',
    higsal      INT comment '最高薪资'
);



/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');


/*插入emp表数据*/
INSERT INTO emp VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000,  10);
INSERT INTO emp VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO emp VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO emp VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO emp VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO emp VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO emp VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO emp VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO emp VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500,  30);
INSERT INTO emp VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO emp VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO emp VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);

/*插入salgrade表数据*/
INSERT INTO sal VALUES (1, 7000, 12000);
INSERT INTO sal VALUES (2, 12010, 14000);
INSERT INTO sal VALUES (3, 14010, 20000);
INSERT INTO sal VALUES (4, 20010, 30000);
INSERT INTO sal VALUES (5, 30010, 99990);

-- 1查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数
	-- 首先我们要找出员工数大于一个的部门，这需要对emp表分组查询，找到大于一个人的部门编号deptnu，
	-- 然后再进行链接查询找到对应的部门名称和部门地址
 
		-- 1. 找到员工大于一个的部门
			select count(*) num ,deptnu from emp group by deptnu having num>1
		-- 2. 连接查询，查询部门位置和部门名称(对于上面已经查到了一个新表，这时我们就可以用伪表查询，节省内存)
			select dept.* from (select count(*) num ,deptnu from emp group by deptnu having num>1) a left join dept on dept.deptnu 	= a.deptnu
 
 
-- 2列出薪资比安琪拉高的所有员工
	-- 首先查询出安琪拉的工资，然后再将安琪拉的工资与其他人比较，
		-- 1.查询安琪拉的工资
			select sal from emp where ename = '安琪拉'
		-- 2.查询比安琪拉工资高的员工姓名
			select ename from emp where sal >(select sal from emp where ename = '安琪拉')
 
 
-- 3列出所有员工的姓名及其直接上级的姓名。
	-- 由表可知，员工每行中有一个mgr对应其领导编号，所以我们只需要链接表然后使mgr对应上即可，可直接用自查询
	-- a表为员工表，b表为上级表
		select	a.ename 员工,b.ename 上级 from  emp  b join emp  a on a.mgr = b.empno 


-- 4列出入职日期早于直接上级的所有员工的编号、姓名、部门编号

	-- 上一个题中已经查询下级和上级，这时我们只需要加一个where条件即可
	-- a表为员工表，b表为上级表 时间越大说明来的越晚
		select	a.ename 员工,a.deptnu,a.empno from  emp  b join emp  a on a.mgr = b.empno where a.hiredate<b.hiredate
	

-- 5列出所有文员的姓名及其部门名称，所在部门的总人数
	-- 先找出所有文员，然后再找出其部门的总人数
		-- 1.找出所有文员
			select 	* from emp where job ='文员'
		-- 2.将部门按组排序，并查找每个部门多少人
			select count(*),deptnu from emp group by deptnu
		-- 3. 将两张表联合起来
			select a.*,b.num from (select 	* from emp where job ='文员') a join (select count(*) num,deptnu from emp group by deptnu) b on a.deptnu = b.deptnu
			
			

-- 6列出最低薪资大于15000的各种工作及从事此工作的员工人数
	-- 先找出每个工作的最低薪资，再找出最低薪资大于15000的工作，并输出工作有多少人
		-- 1.查询每个工作的最低薪资和人数
			select min(sal),job,count(*) from emp group by job
		-- 2. 查询出最低工资大于15000，即在分组后面加一个条件即可
			select min(sal) m,job,count(*) from emp group by job having m>15000
	

-- 7列出与诸葛亮从事相同工作的所有员工信息。
	-- 先查询出诸葛亮的工作，然后在查找和诸葛亮一样工作的员工信息
		-- 查询诸葛亮的工作
			select job from emp where ename ='诸葛亮'
		-- 查询和诸葛亮一样工作的员工信息而且不包括诸葛亮
			select 	* from emp where job in (select job from emp where ename ='诸葛亮')and ename !='诸葛亮'
			

-- 8列出薪资比在30部门工作的员工的平均薪资还高的员工姓名和薪资、部门编号。
	-- 	先找出30部门的平均薪资，然后再查询比平均薪资还高的员工
			-- 30部门的平均薪资
				select avg(sal) ,deptnu from emp group by deptnu having deptnu =30
			-- 查询比平均薪资还高的员工
				select emp.* from emp join (select avg(sal) s ,deptnu from emp group by deptnu having deptnu =30) a on emp.deptnu = a.deptnu where sal>a.s


-- 9查询出所有员工的姓名，部门名称以及薪资等级
	-- 三表连接
		select emp.ename,dept.dname,sal.grade from emp  join dept on emp.deptnu = dept.deptnu join sal  on emp.sal between sal.lowsal and sal.higsal


-- 10查询所有员工的年薪，并按年薪从高到低排序
	-- 按年薪降序排序
		select sal*12  from emp GROUP BY sal ORDER BY sal desc