MySQL练习题

最新推荐文章于 2024-09-12 01:15:37 发布

陌影2272424

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量506

点赞数 3

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

原文链接：http://www.monkey1024.com/database/844

版权

JavaWeb教程专栏收录该内容

51 篇文章 2 订阅

订阅专栏

本文详细介绍了如何使用SQL进行复杂查询，包括获取每个部门的最高薪水员工、筛选薪水高于部门平均薪水的员工、计算部门平均薪水等级、不使用MAX函数获取最高薪水、找出平均薪水最高的部门以及最低薪水等级的部门等。同时，还展示了如何处理涉及多个表的关联查询，如找出未选过特定老师的学生、不及格课程超过两门的学生以及同时选修特定课程的学生。通过对SQL查询的深入探讨，帮助读者提升数据库操作能力。

摘要由CSDN通过智能技术生成

取得每个部门最高薪水的人员名称
第一步，取得每个部门的最高薪水，按照部门分组求最大值：

   SELECT 
        deptno, MAX(sal) AS maxsal
    FROM
        emp
    GROUP BY deptno

第二步，将第一步的查询结果作为临时表t与emp表做连接查询：

SELECT 
    e.ename, t.deptno, t.maxsal
FROM
    emp e
JOIN
    (SELECT 
        deptno, MAX(sal) AS maxsal
    FROM
        emp
    GROUP BY deptno) t 
ON e.deptno = t.deptno
WHERE
    t.maxsal = e.sal;

哪些人的薪水在部门平均薪水之上
第一步，找出部门的平均薪水：

SELECT 
    deptno, AVG(sal) AS avgSal
FROM
    emp
GROUP BY deptno

第二步，将第一步的查询结果作为临时表t与emp表做连接查询

SELECT 
    e.ename, t.deptno, e.sal
FROM
    emp e
        JOIN
    (SELECT 
        deptno, AVG(sal) AS avgSal
    FROM
        emp
    GROUP BY deptno) t ON e.deptno = t.deptno
WHERE
    e.sal > t.avgSal;

取得部门中（所有人的）平均薪水等级
第一步，计算部门平均薪水：

SELECT 
        deptno, AVG(sal) AS avgSal
    FROM
        emp
    GROUP BY deptno

第二步，将第一步的查询结果作为临时表t与salgrade表做连接查询：

SELECT 
    t.deptno, t.avgSal, s.grade
FROM
    salgrade s
        JOIN
    (SELECT 
        deptno, AVG(sal) AS avgSal
    FROM
        emp
    GROUP BY deptno) t 
ON t.avgSal BETWEEN s.losal AND s.hisal;

不用max函数，取得最高薪水

使用倒序排序，之后取得第一条数据：

select ename,sal from emp order by sal desc limit 0,1;

取得平均薪水最高的部门的部门编号

方法一：

第一步，取得部门的平均薪水：

select deptno, avg(sal) from emp group by deptno;

第二步，将第一步的结果看做新的表，使用max函数取得最高的平均薪水“

SELECT 
MAX(t.avgSal) AS maxSal
FROM
(SELECT 
    deptno, AVG(sal) AS avgSal
FROM
    emp
GROUP BY deptno) t;

第三步，使用having来匹配第二步查询的结果

SELECT 
deptno, AVG(sal) AS avgSal
FROM
emp
GROUP BY deptno
HAVING avgSal = (SELECT 
    MAX(t.avgSal) AS maxSal
FROM
    (SELECT 
        deptno, AVG(sal) AS avgSal
    FROM
        emp
    GROUP BY deptno) t);

方法二：
按部门编号分组求平均薪水的最大值，使用limit取得第一条记录：

SELECT 
deptno, AVG(sal) AS avgSal
FROM
    emp
GROUP BY deptno
ORDER BY avgSal DESC
LIMIT 0 , 1;

取得平均薪水最高的部门的部门名称

SELECT 
d.dname, t.deptno, t.avgSal
FROM
dept d
    JOIN
(SELECT 
    deptno, AVG(sal) AS avgSal
FROM
    emp
GROUP BY deptno
ORDER BY avgSal DESC
LIMIT 0 , 1) t ON d.deptno = t.deptno;

求平均薪水的等级最低的部门的部门名称
注：平均薪水最低的等级肯定最低，平均薪水不是最低的也有可能等级最低。
第一步，取得部门编号和平均薪水：

SELECT 
    deptno, AVG(sal) AS avgSal
FROM
    emp
GROUP BY deptno

第二步，将第一步的查询结果作为表t，与表t做内连接查询取得平均薪水等级和部门编号和部门名称：

SELECT 
    s.grade, t.deptno, t.dname
FROM
    salgrade s
        JOIN
    (SELECT 
        e.deptno, dname, AVG(sal) AS avgSal
    FROM
        emp e
    JOIN dept d ON e.DEPTNO = d.DEPTNO
    GROUP BY d.dname) t ON t.avgSal BETWEEN s.losal AND s.HISAL

第三步，查询出最低等级

SELECT 
    MIN(sal.grade) minGrade
FROM
    (SELECT 
        AVG(sal) AS avgSal
    FROM
        emp
    GROUP BY DEPTNO) tt
        JOIN
    salgrade sal ON tt.avgSal BETWEEN sal.losal AND sal.hisal

第四步，将第三步的查询结果作为第二个查询的条件

SELECT 
    a.grade, a.dname, a.deptno
FROM
    (SELECT 
        s.grade grade, t.deptno deptno, t.dname dname
    FROM
        salgrade s
    JOIN (SELECT 
        e.deptno, dname, AVG(sal) AS avgSal
    FROM
        emp e
    JOIN dept d ON e.DEPTNO = d.DEPTNO
    GROUP BY d.dname) t ON t.avgSal BETWEEN s.losal AND s.HISAL) a
WHERE
    a.grade = (SELECT 
            MIN(sal.grade)
        FROM
            (SELECT 
                AVG(sal) AS avgSal
            FROM
                emp
            GROUP BY DEPTNO) tt
                JOIN
            salgrade sal ON tt.avgSal BETWEEN sal.losal AND sal.hisal)

取得比普通员工（员工编号没有在mgr上出现的）的最高薪水还要高的经理人姓名
第一步，取得非普通员工的编号，排除null的数据：

select distinct mgr from emp where mgr is not null;

第二步，找出普通员工的最高薪水：

SELECT 
    max(sal) as maxSal
FROM
    emp
WHERE
    empno NOT IN (SELECT DISTINCT
            mgr
        FROM
            emp
        WHERE
            mgr IS NOT NULL);

第三步，将第二步中的查询结果作为条件，找出薪水大于普通员工最高薪水的经理人：

SELECT 
    e.ename
FROM
    emp e
        JOIN
    (SELECT DISTINCT
        (mgr) AS mgr
    FROM
        emp
    WHERE
        mgr IS NOT NULL) t ON e.empno = t.mgr
WHERE
    e.sal > (SELECT 
            MAX(sal) AS maxSal
        FROM
            emp
        WHERE
            empno NOT IN (SELECT DISTINCT
                    (mgr)
                FROM
                    emp
                WHERE
                    mgr IS NOT NULL));

取得每个薪水等级有多少员工
第一步，将emp表和salgrade表做内连接查询，找出每个员工的薪水等级：

SELECT 
    e.EMPNO, s.GRADE
FROM
    emp e
        JOIN
    salgrade s ON e.sal BETWEEN s.losal AND s.HISAL;

第二步，将第一步中数据作为表t，根据其grade进行分组，使用count函数统计出每个薪水等级的员工数量：

SELECT 
    t.grade, COUNT(t.empno)
FROM
    (SELECT 
        e.EMPNO, s.GRADE
    FROM
        emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.HISAL) t
GROUP BY t.grade;

列出受雇日期早于其直接上级的所有员工编号、姓名

第一步，将emp a当作员工表，将emp b当作领导表，进行表连接， a.mgr = b.empno，并且员工受雇日期早于（小于）领导受雇日期a.hiredate < b.hiredate

SELECT 
    a.empno, a.ename, a.HIREDATE
FROM
    emp a
        JOIN
    emp b ON a.mgr = b.empno
WHERE
    a.HIREDATE < b.HIREDATE;

列出至少有5个员工的部门名称
第一步，根据emp表中的deptno分组求出至少有5个员工的部门编号：

SELECT 
    COUNT(e.empno), e.deptno
FROM
    emp e
GROUP BY e.deptno
HAVING COUNT(e.empno) >= 5;

第二步，将第一步中的deptno与dept表中的deptno做连接，查询出部门名称：

SELECT 
    d.dname
FROM
    dept d
        JOIN
    (SELECT 
        COUNT(e.empno), e.deptno
    FROM
        emp e
    GROUP BY e.deptno
    HAVING COUNT(e.empno) >= 5) t ON d.deptno = t.deptno;

列出最低薪水大于1500的工作及从事此工作的雇员人数
第一步，按照Job分组求出最低薪水并且大于1500：

select job, min(sal) as minSal from emp group by job having min(sal) > 1500

第二步，在第一步的基础上计算雇员的人数：

select job, min(sal) as minSal, count(*) as total from emp group by job having min(sal) > 1500;

列出在部门“SALES”工作的员工的姓名

第一步，在dept表中获取sales的部门编号：

select deptno from dept where dname='sales'

第二步，从emp表中查找与第一步结果相等的部门编号的员工姓名：

SELECT 
    e.ename
FROM
    emp e
WHERE
    e.DEPTNO = (SELECT 
            deptno
        FROM
            dept
        WHERE
            dname = 'sales');

求部门名称中带“S”字符的部门员工的工资合计、部门人数
第一步，使用连接查询将emp和dept中的员工工资合计，部门人数，部门名称获取出来：

SELECT 
    d.dname, COUNT(e.ENAME) AS total, SUM(e.sal) AS totalSal
FROM
    emp e
        RIGHT JOIN
    dept d ON e.deptno = d.DEPTNO
GROUP BY d.DNAME;

第二步，在第一步的sql中加入部门名称中带’s’的条件：

SELECT 
    d.dname, COUNT(e.ENAME) AS total, SUM(e.sal) AS totalSal
FROM
    emp e
        RIGHT JOIN
    dept d ON e.deptno = d.DEPTNO
where d.DNAME like '%s%'
GROUP BY d.DNAME;

面试题
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：

找出没选过“黎明”老师的所有学生姓名。
列出2门以上（含2门）不及格学生姓名及平均成绩。
即学过1号课程又学过2号课所有学生的姓名。

初始化数据：

DROP TABLE IF EXISTS SC;
CREATE TABLE SC(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
DROP TABLE IF EXISTS S;
CREATE TABLE S(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);
DROP TABLE IF EXISTS C;
CREATE TABLE C(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 

INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4');  

INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');

找出没选过“黎明”老师的所有学生姓名
第一步，找出“黎明”老师所授课程编号：

select cno from c where cteacher='黎明';

第二步，通过SC表查找cno=第一步查询结果的sno，这些sno都是选了“黎明”老师的课程的学号：

SELECT 
    sno
FROM
    sc
WHERE
    cno = (SELECT 
            cno
        FROM
            c
        WHERE
            cteacher = '黎明');

第三步，在S学生表中查询 sno not in 第二步查询结果的 sno：

SELECT 
    sno, sname
FROM
    s
WHERE
    sno NOT IN (SELECT 
            sno
        FROM
            sc
        WHERE
            cno = (SELECT 
                    cno
                FROM
                    c
                WHERE
                    cteacher = '黎明'));

列出2门以上（含2门）不及格学生姓名及平均成绩

第一步，查找两名以上不及格的学生编号：

select sno from sc where scgrade<60 group by sno having count(*)>=2;

第二步，将sc表与第一步中的查询结果做连接查询，计算平均成绩：

SELECT 
    t2.sno, AVG(t1.scgrade) AS avgScgrade
FROM
    sc t1
        JOIN
    (SELECT 
        sno
    FROM
        sc
    WHERE
        scgrade < 60
    GROUP BY sno
    HAVING COUNT(*) >= 2) t2 ON t1.sno = t2.sno
GROUP BY t2.sno;

第三步，将学生表s与第一步的查询结果将sno作为条件做连接查询找出学生姓名：

SELECT 
    s.sname, t.avgScgrade
FROM
    s
        JOIN
    (SELECT 
        t2.sno, AVG(t1.scgrade) AS avgScgrade
    FROM
        sc t1
    JOIN (SELECT 
        sno
    FROM
        sc
    WHERE
        scgrade < 60
    GROUP BY sno
    HAVING COUNT(*) >= 2) t2 ON t1.sno = t2.sno
    GROUP BY t2.sno) t ON s.sno = t.sno;

即学过1号课程又学过2号课所有学生的姓名
第一步，找出学过1号课程学生编号：

select sno from sc  where cno=1;

第二步，找出学过2号课程学生编号：

select sno from sc  where cno=2;

第三步，将第一步和第二步的查询结果做连接查询，及学过1号课程又学过2号课程的学生编号：

select t1.sno from
    (select sno from sc  where cno=1) t1
join
    (select sno from sc  where cno=2) t2
on t1.sno=t2.sno;

第四步，从学生表s中找出学生编号与第三步结果一致的学生姓名

select s.sname from s
join
    (select t1.sno from
        (select sno from sc  where cno=1) t1
    join
        (select sno from sc  where cno=2) t2
    on t1.sno=t2.sno) t3
on s.sno=t3.sno;