第二天MySQL总结：多表查询，函数，分组过滤，子查询

1.多表查询

以下是两张表的连接

1.1内连接

查询员工姓名和部门名称

SELECT e.`first_name`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;

1.1左外连接：除匹配的内容，还有右表为null的内容

SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`

1.2右外连接：与左外连接相反

SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`

1.3满外连接：除匹配的内容还有两表中不匹配的内容

思考？满外连接可能导致重复的数据，如何解决

union：可以将两张表中的内容合起来（去重），因为查询了两次，会出现两次中有重合的
部分，用union可以去重；用full join会更方便，但是MySQL不支持；

查询所有员工和部门名称（包括有部门的员工和没员工的部门）

SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
UNION
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`

查询员工姓名，部门名称，部门所在城市名称

SELECT e.`first_name`,d.`department_name`,l.city
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`;

注：后面的join on是加在了前面新建的姓名部门名称表中

`票号：区分关键字和字段名，如select `select`

1.4 distinct:去重

查询有员工的部门号

SELECT department_id
 FROM employees e
 WHERE  e.`department_id` IS NOT NULL;

 SELECT DISTINCT department_id
 FROM employees e
 WHERE e.`department_id` IS NOT NULL;

2.函数

以下是通用函数

2.1 ifnull(字段名，默认值)：如果字段名内容为null用默认值替换

查询所有员工的工资（工资加奖金）

 SELECT salary+IFNULL(commission_pct,0)*salary
 FROM employees;

2.2 case表达式

 case表达式
 case 字段名
 when 值1 then 返回值1
 when 值2 then 返回值2
 when 值3 then 返回值3
else 返回值n
end 

查询部门号为10，20，30的员工信息，若部门号为10，20，30，打印工资1.1，1.2，1.3倍...

SELECT department_id,salary,CASE department_id
                             WHEN 10 THEN salary*1.1
                             WHEN 20 THEN salary*1.2
                             WHEN 30 THEN salary*1.3
                             ELSE salary
                             END newsalary#也可以 end as”阿迪斯“
 FROM employees
 WHERE department_id IN(10,20,30);

 查询所有员工的薪水若大于10000显示嫩模，小于10000显示下海干活，等于10000在再接再厉
 

SELECT salary,CASE 
               WHEN salary>10000 THEN "会所嫩模"
               WHEN salary<10000 THEN "下海干活"
               WHEN salary=1000 THEN "再接再厉"
               END 评价
 FROM employees;

 

2.3以下是单行函数

LOWER('SQL Course') ：将字符串内容全部变成小写
UPPER('SQL Course') ：将字符串内容全部变成大写

SELECT LOWER('abcDeFgKl'),UPPER('abcDeFgKl');

SELECT LOWER(first_name),UPPER(last_name)
FROM employees;

CONCAT('Hello', 'World') : 字符串拼接
SUBSTR('HelloWorld',1,5) : 截取子串（索引位置从1开始）
	1 ：开始的位置
	5 ： 长度（偏移量）
LENGTH('HelloWorld') : 内容的长度
INSTR('HelloWorld', 'W') : W在当前字符串中首次出现的位置

LPAD(salary,10,'*') : 向右对齐
	如果内容长度不够10用 *补
	
RPAD(salary, 10, '*') ：向左对齐
	如果内容长度不够10用 *补

TRIM('H' FROM 'HelloWorld') : 去除字符串两端指定的字符
REPLACE('abcd','b','m') : 将字符串中所有的b替换成m

SELECT CONCAT(first_name,'-',last_name)
FROM employees;

SELECT SUBSTR('abcdef',2,3);

SELECT first_name,LENGTH(first_name)
FROM employees;

SELECT INSTR('abcdc','c');

SELECT LPAD(salary,10,' '),RPAD(salary,10,' ')
FROM employees;

SELECT TRIM('H' FROM 'HHHHHAHHHBHHHH');

SELECT REPLACE('abcccdba','c','C');

2.4多行函数（组函数-聚合函数）

AVG() : 求平均值  
SUM() ：求和
注意：上面的函数只能对数值类型做运算

MAX() ：求最大值
MIN() ：求最小值
COUNT()：统计结果的数量 

求所有员工薪水的最大值，最小值，平均值，总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

注意：下面的写法不对，select后面出现组函数后将不能再出现其他字段，除非该函数出现在group by的后面。

#以下写法不对
SELECT first_name,AVG(salary)
FROM employees;
#以下正确
SELECT first_name,AVG(salary)
FROM employees
GROUP BY department_id;

COUNT(字段名)：统计查询的结果中该字段内容不为null的有多少条
count(*) : 统计查询的结果有多少条数据
count(数值) ：和count(*)的作用一样。count(数值)效率高一些。

SELECT COUNT(commission_pct),COUNT(*),COUNT(1)
FROM employees;

结果如图

 

求平均值时是否包含null? 答案不包含null

SELECT SUM(commission_pct)/107,SUM(commission_pct)/35,AVG(commission_pct)
FROM employees;

3.分组和过滤

select 字段名1，字段名2,.....
from 表名
where 过滤条件
group by 字段名1，字段名2，......
having 过滤条件
order by 字段名1 desc/asc,字段名2 desc/asc，......

where和having的区别？
1.where是在分组前进行过滤，having是在分组后过滤。

2.where后面不能使用组函数，having后面可以使用组函数。
查询各部门中最高薪水

说明：select后面出现组函数后将不能再出现其它字段，除非该字段出现在group by的后面。

SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

查询各部门中平均薪水并按照最高薪水进行排序-降序
 

 SELECT department_id,AVG(salary)
 FROM employees 
 WHERE department_id IS NOT NULL
 GROUP BY department_id
 ORDER BY AVG(salary) DESC;
 

 查询各部门中不同的工种的最低薪水是多少,先按照不同部门分组，再按照部门中不同工作分组。
 

SELECT department_id,job_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id;

查询10,20,30部门的平均薪水大于5000的有哪个部门。
方式一（效率高）

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IN(10,20,30)
GROUP BY department_id
HAVING AVG(salary)>5000;

方式二

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN(10,20,30) AND AVG(salary)>5000;

上面不能用where

4.子查询

子查询 ：在一个a查询语句中再嵌套一个查询语句b。b语句叫作子查询（内查询）a语句叫作主查询(外查询)

子查询分类 ：单行子查询 vs 多行子查询

单行子查询 : 子查询的结果只有一条。
多行子查询 ： 子查询的结果有多条。

单行子查询使用的运算符 : > >= < <= = <>
多行子查询使用的运算符 ：in any all

注意：先执行子查询（内查询）再执行外查询（主查询）

谁的工资比 Abel 高?

方式一：

1.查询Abel的工资，然后查询工资高于11000的员工

SELECT salary
FROM employees
WHERE last_name='Abel';#11000

SELECT last_name,salary
FROM employees
WHERE salary>11000;

方式二：自连接

SELECT e1.`last_name`,e1.`salary`
FROM employees e1 JOIN employees e2
ON e1.`salary`>e2.`salary` AND e2.`last_name`='Abel'; 

方式三：子查询

SELECT last_name,salary
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

SELECT 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
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
	#50号部门最低薪水
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);

下面错误代码： 1242
Subquery returns more than 1 row,返回的多于一个

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

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

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY (
	#查询IT部门所有员工的薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG' ;

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

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ALL (
	#查询IT部门所有员工的薪水
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG' ;

累死了，再接再厉叭！