MySQL学习笔记——DQL练习

1. 判断输出结果是否相同

#不同
SELECT * FROM employees;#输出全部员工信息

SELECT 
  * 
FROM
  employees 
WHERE `commission_pct` LIKE '%%' 
  AND last_name LIKE '%%' ;
#通配符不能代表null，因此commission_pct为null的员工信息不会被筛选出来

2. 子查询经典案例

#1.查询工资最低的员工信息
SELECT 
  * 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

#2.查询平均工资最低的部门信息
#(1)查询各部门的平均工资
SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
GROUP BY department_id ;
#(2)查询(1)中的最低平均工资
SELECT 
  MIN(ag) 
FROM
  (SELECT 
    AVG(salary) ag,
    department_id 
  FROM
    employees 
  GROUP BY department_id) ad_dep ;
#(3)查询哪个部门的平均工资等于(2)
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) ad_dep) ;
#(4)根据(3)查询到的部门编号查询部门信息
SELECT 
  * 
FROM
  departments 
WHERE 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) ad_dep)) ;
#---------------------------------------
/*
简单方法：
	将部门分组之后，按照平均工资由低到高排序，
	则第一条数据的department_id就是平均工资最低的部门id，
	之后再查询部门详细信息即可
*/
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id 
ORDER BY AVG(salary) ASC 
LIMIT 0, 1 ;
#查询部门详细信息
SELECT 
  * 
FROM
  departments 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY AVG(salary) ASC 
  LIMIT 0, 1) ;

#3.查询平均工资最低的部门信息和该部门的平均工资
#(1)将各部门分组，按平均工资升序排序，第一条数据就是平均工资最低的部门id
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id 
ORDER BY AVG(salary) ASC 
LIMIT 0, 1 ;
#(2)查询部门号为(1)的部门信息和平均工资
SELECT 
  d.*,
  AVG(salary) 
FROM
  departments d 
  INNER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
WHERE d.`department_id` = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY AVG(salary) ASC 
  LIMIT 0, 1) ;
#--------------------------------
SELECT 
  d.*,
  ag_dep.ag 
FROM
  departments d 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      department_id 
    FROM
      employees 
    GROUP BY department_id 
    ORDER BY AVG(salary) ASC 
    LIMIT 0, 1) ag_dep 
    ON d.`department_id` = ag_dep.`department_id` ;

#4.查询平均工资最高的job信息
#(1)查询平均工资最高的job_id
SELECT 
  job_id 
FROM
  employees 
GROUP BY job_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 ;
#(2)查询job_id等于(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.查询平均工资高于公司平均工资的部门有哪些
#(1)查询工资的平均工资
SELECT 
  AVG(salary) 
FROM
  employees ;
#(2)查询平均工资高于(1)的部门id
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING AVG(salary) > 
  (SELECT 
    AVG(salary) 
  FROM
    employees) ;
#(3)查询平均工资高于(1)的部门信息
SELECT 
  * 
FROM
  departments 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  HAVING AVG(salary) > 
    (SELECT 
      AVG(salary) 
    FROM
      employees)) ;

#6.查询公司中所有manager的详细信息
SELECT 
  * 
FROM
  employees 
WHERE employee_id IN 
  (SELECT 
    DISTINCT manager_id 
  FROM
    employees) ;

#7.各个部门中，最高工资最低的那个部门的最低工资是多少
#(1)查询各个部门的最高工资，按升序排后取第一条数据，即可得到最高工资中最低的那个部门的id
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id 
ORDER BY MAX(salary) ASC 
LIMIT 1 ;
#(2)查询(1)部门的最低工资
SELECT 
  department_id,
  MIN(salary) 
FROM
  employees 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY MAX(salary) ASC 
  LIMIT 1) ;

#8.查询平均工资最高的部门的manager的详细信息：last_name, department_id, email, salary
#(1)查询平均工资最高的部门的id
SELECT 
  department_id 
FROM
  employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 0, 1 ;
#(2)查询(1)部门的manager信息
SELECT 
  e.last_name,
  d.department_id,
  e.email,
  e.salary 
FROM
  departments d 
  INNER JOIN employees e 
    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 0, 1) ;

3. 综合练习

#1.查询每个专业的学生人数
SELECT 
  majorid,
  COUNT(*) 
FROM
  student 
GROUP BY majorid ;

#2.查询每个学生的平均分、最高分
SELECT 
  studentno,
  AVG(score),
  MAX(score) 
FROM
  result 
GROUP BY studentno ;

#3.查询姓张的学生中最低分大于60的学生的学号、姓名
SELECT 
  s.studentno,
  s.studentname 
FROM
  student s 
  INNER JOIN result r 
    ON s.`studentno` = r.`studentno` 
WHERE studentname LIKE '张%' 
GROUP BY s.studentno 
HAVING MIN(score) > 60 ;
#-------------------------------------
SELECT 
  s.studentno,
  studentname 
FROM
  student s 
  INNER JOIN 
    (SELECT 
      MIN(score) m_s,
      studentno 
    FROM
      result 
    GROUP BY studentno) min_res 
    ON s.`studentno` = min_res.studentno 
WHERE s.`studentname` LIKE '张%' 
  AND min_res.m_s > 60 ;

#4.查询生日在“1988-1-1”后的学生姓名、专业名称
SELECT 
  s.studentname,
  m.majorname 
FROM
  student s 
  INNER JOIN major m 
    ON s.`majorid` = m.`majorid` 
WHERE DATEDIFF(s.`borndate`, '1988-1-1') > 0 ;

#5.查询每个专业的男生人数和女生人数分别是多少
SELECT 
  majorid 专业,
  sex 性别,
  COUNT(*) 人数 
FROM
  student 
GROUP BY majorid,
  sex ;
#----------------
SELECT 
  majorid,
  (SELECT 
    COUNT(*) 
  FROM
    student 
  WHERE sex = '男' 
    AND majorid = s.majorid) 男生,
  (SELECT 
    COUNT(*) 
  FROM
    student 
  WHERE sex = '女' 
    AND majorid = s.majorid) 女生 
FROM
  student s 
GROUP BY majorid ;

#6.查询专业和张翠山一样的学生的最低分
SELECT 
  MIN(score) 
FROM
  result r 
  INNER JOIN student s 
    ON r.`studentno` = s.`studentno` 
WHERE s.`majorid` = 
  (SELECT 
    majorid 
  FROM
    student 
  WHERE studentname = '张翠山') ;

#7.查询大于60分的学生的姓名、密码、专业名
SELECT 
  s.studentname,
  loginpwd,
  majorname 
FROM
  student s 
  INNER JOIN major m 
    ON s.`majorid` = m.`majorid` 
  INNER JOIN result r 
    ON s.`studentno` = r.`studentno` 
WHERE r.`score` > 60 ;

#8.按邮箱位数分组，查询每组的学生个数
SELECT 
  LENGTH(email),
  COUNT(*) 
FROM
  student 
GROUP BY LENGTH(email) ;

#9.查询学生名、专业名、分数
SELECT 
  s.studentname,
  majorname,
  score 
FROM
  student s 
  INNER JOIN major m 
    ON s.`majorid` = m.`majorid` 
  LEFT JOIN result r 
    ON s.`studentno` = r.`studentno` ;

#10.查询哪个专业没有学生，分别用左连接和右连接实现
SELECT 
  majorname 
FROM
  major m 
  LEFT JOIN student s 
    ON m.`majorid` = s.`majorid` 
WHERE s.studentno IS NULL ;
#-------------------------------
SELECT 
  majorname 
FROM
  student s 
  RIGHT JOIN major m 
    ON m.`majorid` = s.`majorid` 
WHERE s.studentno IS NULL ;

#11.查询没有成绩的学生人数
SELECT 
  COUNT(*) 
FROM
  student s 
  LEFT JOIN result r 
    ON s.`studentno` = r.`studentno` 
WHERE r.`id` IS NULL ;