美文网首页
MySQL Demo 04

MySQL Demo 04

作者: GeekGray | 来源:发表于2018-10-04 16:40 被阅读43次

    阅读原文

    MySQL Demo 04

    一、查询每个专业的学生人数

    SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

    二、查询参加考试的学生中，每个学生的平均分、最高分

    SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

    三、查询姓张的每个学生的最低分大于60的学号、姓名

    SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;

    四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

    SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;

    五、查询每个专业的男生人数和女生人数分别是多少

    SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

    六、查询专业和张翠山一样的学生的最低分

    ①查询张翠山的专业编号

    SELECT majorid
FROM student
WHERE studentname = '张翠山'

    ②查询编号=①的所有学生编号

    SELECT studentno
FROM student
WHERE majorid=(
    SELECT majorid
    FROM student
    WHERE studentname = '张翠山'

)

    ②查询最低分

    SELECT MIN(score)
FROM result
WHERE studentno IN(

    SELECT studentno
    FROM student
    WHERE majorid=(
        SELECT majorid
        FROM student
        WHERE studentname = '张翠山'

    )
)

    七、查询大于60分的学生的姓名、密码、专业名

    SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

    八、按邮箱位数分组，查询每组的学生个数

    SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

    九、查询学生名、专业名、分数

    SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
LEFT JOIN result r ON s.studentno=r.studentno

    十、查询哪个专业没有学生，分别用左连接和右连接实现

    左

    SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

    右

    SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN  major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

    十一、查询没有成绩的学生人数

    SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL

    子查询经典案例

    1. 查询工资最低的员工信息: last_name, salary

    ①查询最低的工资

    SELECT MIN(salary)
FROM employees

    ②查询last_name,salary，要求salary=①

    SELECT last_name,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

    2. 查询平均工资最低的部门信息

    方式一：

    ①各部门的平均工资

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

    ②查询①结果上的最低平均工资

    SELECT MIN(ag)
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep

    ③查询哪个部门的平均工资=②

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
    SELECT MIN(ag)
    FROM (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep

);

    ④查询部门信息

    SELECT d.*
FROM departments d
WHERE d.`department_id`=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary)=(
        SELECT MIN(ag)
        FROM (
            SELECT AVG(salary) ag,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep

    )

);

    方式二：

    ①各部门的平均工资

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

    ②求出最低平均工资的部门编号

    SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

    ③查询部门信息

    SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1
);

    3. 查询平均工资最低的部门信息和该部门的平均工资

    ①各部门的平均工资

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

    ②求出最低平均工资的部门编号

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

    ③查询部门信息

    SELECT d.*,ag
FROM departments d
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1

) ag_dep
ON d.`department_id`=ag_dep.department_id;

    4. 查询平均工资最高的 job 信息

    ①查询最高的job的平均工资

    SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

    ②查询job信息

    SELECT * 
FROM jobs
WHERE job_id=(
    SELECT job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1

);

    5. 查询平均工资高于公司平均工资的部门有哪些?

    ①查询平均工资

    SELECT AVG(salary)
FROM employees

    ②查询每个部门的平均工资

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

    ③筛选②结果集，满足平均工资>①

    SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
    SELECT AVG(salary)
    FROM employees

);

    6. 查询出公司中所有 manager 的详细信息.

    ①查询所有manager的员工编号

    SELECT DISTINCT manager_id
FROM employees

    ②查询详细信息，满足employee_id=①

    SELECT *
FROM employees
WHERE employee_id =ANY(
    SELECT DISTINCT manager_id
    FROM employees

);

    7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

    ①查询各部门的最高工资中最低的部门编号

    SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

    ②查询①结果的那个部门的最低工资

    SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1


);

    8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

    ①查询平均工资最高的部门编号

    SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1

    ②将employees和departments连接查询，筛选条件是①

        SELECT 
        last_name, d.department_id, email, salary 
    FROM
        employees e 
        INNER JOIN departments d 
            ON d.manager_id = e.employee_id 
    WHERE d.department_id = 
        (SELECT 
            department_id 
        FROM
            employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1) ;

    相关文章

      网友评论

          本文标题：MySQL Demo 04

          本文链接：https://www.haomeiwen.com/subject/pypxaftx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL Demo 04|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！