mysql子查询

-- 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT * from (select t1.SId, class1,class2 from (SELECT SId, score as class1 from SC where sc.CId = '01') as t1,
(SELECT SId, score as class2 from SC where sc.CId = '02') as t2
where t1.SId = t2.SId and t1.class1 > t2.class2) r left join student on student.SId = r.SId;

-- 查询同时存在" 01 “课程和” 02 "课程的情况
select * from (select * from SC where SC.CId = '01') as t1,
                            (select * from SC where SC.CId = '02') as t2 where t1.SId = t2.SId;

-- 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from (select * from SC where SC.CId = '01') t1 left join  (select * from SC where SC.CId = '02') t2 on t1.SId = t2.SId;

-- 查询不存在" 01 “课程但存在” 02 "课程的情况
select * from SC where SC.SId not in (SELECT SId from SC where SC.CId = '01') AND SC.CId = '02';

-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select t1.SId,t2.Sname,t1.avg_score from  (select SId, AVG(score) avg_score from sc group by SId having AVG(score) >= 60) t1, student t2 where t1.SId = t2.SId; 

-- 查询在 SC 表存在成绩的学生信息
SELECT * from student where SId in (select DISTINCT SId from sc);
-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select * from (student t1 left join (select SId, SUM(score),count(CId) from sc group by SId) t2 on t1.SId = t2.SId);

-- 查有成绩的学生信息
SELECT * from student where SId in (select DISTINCT SId from SC);

-- 查询「李」姓老师的数量
select COUNT(*) from teacher where Tname like '李%';

-- 查询学过「张三」老师授课的同学的信息
select student.* from student, course, sc, teacher where teacher.Tname = '张三' and teacher.TId = course.TId and course.CId = sc.CId and sc.SId = student.SId;

-- 查询没有学全所有课程的同学的信息
SELECT * from student where SId in (select SId from sc group by sc.SId having COUNT(sc.cid) < (select count(cid) from course));

-- CASE中的子查询
-- 题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT
    employee_id,
    last_name,
    ( CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id = 1800 ) THEN location = 'Canada' ELSE 'USA' END ) location 
FROM
    employees;

-- 题目：查询平均工资最低的部门id
-- 3.平均工资最低的部门
SELECT
    department_id 
FROM
    employees 
GROUP BY
    department_id 
HAVING
    AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees WHERE department_id IS NOT NULL GROUP BY department_id ) avg_emp_salary );
                        
-- 方式二:让平均工资小于所有平均工资最小的,那直接就满足条件了

SELECT
    department_id 
FROM
    employees 
GROUP BY
    department_id 
HAVING
    AVG( salary ) <= ALL ( SELECT AVG( salary ) FROM employees GROUP BY department_id );

-- 题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name,salary,department_id from employee e1 where salary > (select AVG(salary) from employee e2 where e2.department_id = e1.department_id);

-- 方式二：在 FROM 中使用子查询

SELECT
    e1.last_name,
    e1.salary 
FROM
    employees e1,( 
    SELECT AVG( salary ) avg_salay, department_id FROM employees e2 GROUP BY department_id ) e2
WHERE
    e1.department_id = e2.department_id 
    AND e1.salary > e2.avg_salay;

-- 题目：查询员工的id,salary,按照department_name 排序
SELECT
    employee_id,
    salary
FROM
    employees e
ORDER BY
    (SELECT department_name FROM departments d WHERE e.department_id = d.department_id )

-- 题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同 id的员工的employee_id,last_name和其job_id

SELECT
    employee_id,
    last_name,
    job_id 
FROM
    employees e 
WHERE
    2 <= ( SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id );

-- 题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

-- 自连接
-- 如果能用自连接的话最好使用自连接，因为它的速度快，效率高
SELECT
    DISTINCT
    e1.employee_id,
    e1.last_name,
    e1.job_id,
    e1.department_id
FROM
    employees e1 JOIN employees e2a
WHERE
    e1.employee_id = e2.manager_id

-- 题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT
    department_id,
    department_name
FROM
    departments d
WHERE
    NOT EXISTS(SELECT DISTINCT department_id FROM employees e WHERE d.department_id = e.department_id)
-- 题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

-- 1.增加字段
ALTER TABLE employees ADD(department_name VARCHAR(14));

-- 2.插入数据
UPDATE employees e 
SET department_name = ( SELECT department_name FROM departments d WHERE e.department_id = d.department_id );

 

原文地址：https://blog.csdn.net/qq_38958829/article/details/144812439

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！