MySQL多表查询

最新推荐文章于 2024-09-15 20:24:46 发布

poc999

最新推荐文章于 2024-09-15 20:24:46 发布

阅读量460

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_38179324/article/details/77170499

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

/*创建部门表*/
CREATE TABLE dept(
    deptno      INT     PRIMARY KEY,
    dname       VARCHAR(50),
    loc         VARCHAR(50)
);
/*插入dept表数据*/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

这里写图片描述

/*创建工资等级表*/
CREATE TABLE salgrade(
    grade       INT     PRIMARY KEY,
    losal       INT,
    hisal       INT
);

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

这里写图片描述

/*创建雇员表*/
CREATE TABLE emp(
    empno       INT     PRIMARY KEY,
    ename       VARCHAR(50),
    job     VARCHAR(50),
    mgr     INT,
    hiredate    DATE,
    sal     DECIMAL(7,2),
    COMM        DECIMAL(7,2),
    deptno      INT,
    CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);
/*插入emp表数据*/
INSERT INTO emp VALUES (1009, '刘邦', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '赵云', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '荆柯', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '小乔', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '大乔', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '妲已', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '貂蝉', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '王昭君', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '程咬金', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '黄忠', '文员', 1013, '2000-12-17', 8000, NULL, 20);

这里写图片描述

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
SELECT d.dname,d.loc,d.deptno,temp.cou FROM dept d,(SELECT deptno,COUNT(*) cou  FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.deptno;
2. 列出所有员工的姓名及其直接上级的姓名。
SELECT e.ename,d.ename FROM emp e, emp d WHERE e.mgr=d.empno;
3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
SELECT e.empno,e.ename,d.dname FROM emp e,dept d,emp em WHERE d.deptno=e.`deptno` 
AND e.`mgr`=em.`empno` AND e.`hiredate`<em.`hiredate`;
4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT d.dname,e.* FROM emp e,dept d WHERE e.`deptno`=d.`deptno`;
5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
SELECT job,COUNT(*)FROM emp GROUP BY job HAVING MIN(sal)>15000;
6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
SELECT e.ename FROM emp e,dept d WHERE e.`deptno`=d.`deptno` AND d.dname='销售部';
7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
SELECT e.*,d.dname AS '部门',em.`ename` AS'领导',sa.`grade`AS '工资等级' FROM 
emp e,dept d, emp em, salgrade sa WHERE e.`deptno`=d.`deptno` AND 
e.sal>(SELECT AVG(sal) FROM emp) AND e.`mgr`=em.`empno`AND e.`sal` BETWEEN sa.`losal` 
AND sa.`hisal`; 
8.列出与大乔从事相同工作的所有员工及部门名称。
SELECT e.*,d.dname FROM emp e,dept d WHERE e.`job`= (SELECT job FROM emp WHERE ename='大乔') 
AND e.`deptno`=d.`deptno`;
9.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。
SELECT e.ename,e.sal+IFNULL(COMM,0) AS '总薪金',d.dname FROM emp e,dept d
    WHERE e.`deptno`=d.`deptno` AND (e.`sal`+IFNULL(COMM,0))> 
    ALL(SELECT(sal+IFNULL(COMM,0)) FROM emp WHERE deptno = 30);