MySQL学习第五天 子查询 分页查询 联合查询

 SQL文件在第一天和第三天

#子查询
/*
出现在其他语句中的select语句，称为子查询

分类：
按子查询出现的位置：
  select 后面 
      仅仅支持标量子查询
  from 后面    
      支持表子查询
  where 或having 后面！！！！！  
      标量  行  列子查询
  exists 后面
      表子查询

按结果集的行列数不同
  标量子查询 结果一行一列
  列子查询   结果一列多行
  行子查询   结果多列一行
  表子查询   结果多列多行

*/

# 一 where 或having 后面     标量  行  列子查询
/*特点：
  子查询放在小括号内
  子查询一般放在条件的右侧
  标量子查询，一般搭配这单行操作符使用
     》 《 》= 《= 
  列子查询：一般搭配多行操作符使用
  in any /some ，all
  子查询优先与母查询执行
  
  */
  
#1 标量子查询
#谁的工资比Abel高
   #先查他的工资 再查其他大于其工资的员工信息
 SELECT *
 FROM `employees`
 WHERE salary>(
           SELECT `salary`
           FROM `employees`
           WHERE `last_name`="Abel"
 )

#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `job_id`=(
SELECT `job_id`
FROM `employees`
WHERE `employee_id`=141
)AND `salary`>(
SELECT `salary`
FROM `employees`
WHERE `employee_id`=143
)

#返回公司工资最少的员工的last_name,job_id和salary

SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`=(
	SELECT MIN(`salary`)
	FROM `employees`
)

#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT `department_id`,MIN(`salary`)
FROM `employees`
GROUP BY `department_id`
HAVING MIN(`salary`)>(SELECT MIN(`salary`)
	FROM `employees`
	WHERE `department_id`=50
)




#2，列子查询
#返回location_id是1400或1700的部门中的所有员工姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN (
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)
)

#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary	

SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary`<ALL(
SELECT `salary`
FROM `employees`
WHERE `job_id`="IT_PROG"
)
	
#3,行子查询 （了解）
#查询员工编号最小并且工资最高的员工信息
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`)=(
  SELECT MIN(`employee_id`),MAX(`salary`)
  FROM `employees`
)


#二 select后面
#查询每个部门的员工个数
SELECT d.*,(
 SELECT COUNT(*)
 FROM `employees`
 WHERE `employees`.`department_id`=d.`department_id`
)
FROM `departments` d


#三 from后面
/*
子查询结果作为一张表必须起别名
*/
#查询每个部门的平均工资的工资等级

SELECT ag.*,`job_grades`.`grade_level`
FROM (
	SELECT  AVG(`salary`) av,`department_id`
	FROM `employees`
	GROUP BY `department_id`
) ag
JOIN `job_grades`
ON ag.av BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`

#四 exists 后面（相关子查询）
/*
 exists(完整查询语句)
 结果1或0
*/

SELECT EXISTS(SELECT `salary` FROM `employees`)
#查询有员工的部门名
SELECT  `department_name`
FROM `departments` d
WHERE EXISTS(
  SELECT *
  FROM `employees` e
  WHERE d.`department_id`=e.`department_id`
)


#分页查询
/*
应用场景：当一页显示不全是，就要分页提交SQL请求
语法：
  select 查询列表
  from 表
  join 表
  on 连接条件
  group by 分组字段
  having 分组帅选
  order by 排序
  limit offset，size；
  
  offset要显示条目的其实索引 起始索引从0开始
  size 要显示的条目个数
  limit （page-1）*size，size
*/
#查询前5条员工信息
SELECT * FROM`employees` LIMIT 0,5

#查询低11条到低25条数据
SELECT * FROM`employees` LIMIT 10,15

#联合查询
/*
 union 将多条查询语句的结果合并成一个结果
 
 语法： 
 查询语句1
 union
  查询语句2
   union
   。。。
   
 适用场景：当查询的结果（查询列数要一致）来自多个表，且两表中没有相关联系，适用联合查询
 uninon默认去重
 可以用uninon all取消去重
*/

#查询部门编号大于90或邮箱中包含a的员工信息
SELECT *FROM`employees` WHERE `email` LIKE "%a%" OR `department_id`>90

SELECT *FROM`employees` WHERE `email` LIKE "%a%"
UNION
SELECT *FROM`employees` WHERE `department_id`>90

 

 

SELECT `last_name`,`salary`
FROM `employees`
WHERE `department_id`=(
SELECT `department_id`
FROM `employees`
WHERE `last_name`="Zlotkey"
)



SELECT `employee_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary`>(
SELECT AVG(`salary`)
FROM `employees`
)



SELECT `employee_id`,`last_name`,`salary`,`employees`.`department_id`
FROM `employees`
INNER JOIN (
SELECT AVG(`salary`) av,`department_id`
FROM `employees`
GROUP BY `department_id`
) av_g
ON `employees`.`department_id`=av_g.`department_id`
WHERE `employees`.`salary`>av_g.av



SELECT `department_id`,`last_name`
FROM `employees`
WHERE `department_id` IN(
SELECT DISTINCT`department_id`
FROM `employees`
WHERE `last_name` LIKE "%u%"
)



SELECT `last_name`,`employee_id`
FROM `employees`
WHERE `department_id` IN(
SELECT `department_id`
FROM `departments`
WHERE `location_id` =1700
)



SELECT `last_name`,`salary`
FROM `employees`
WHERE `manager_id` IN(
SELECT `employee_id`
FROM `employees`
WHERE `last_name`="K_ing"
)



SELECT CONCAT(`first_name`,`last_name`)
FROM `employees`
WHERE `salary`=(
SELECT MAX(`salary`)
FROM `employees`
)