mysql李玉婷网课配套笔记（三） 分组查询、连接查询bilibili

sql92语法：

        等值连接，非等值连接，自连接等

基础函数汇总：
一、单行函数
1、字符函数
concat    拼接
substr    截取子串
upper    变大写
lower    变小写
replace    替换
length    获取字节长度
trim    去前后空格
lpad    左填充
rpad    右填充
instr    获取子串第一次出现的索引
2、数学函数
ceil    向上取整
round    四舍五入
floor    向下取整
mod    取模
truncate    截断
rand    获取0-1之间随机数，取不到1
3、日期函数
now    当前日期和时间
year    年
month    月
day    日
date_format 日期转字符
curdate    日期
curtime    时间
hour    小时
minute    分钟
second    秒
datediff    前减后
monthname 月份的英文名字
4、其他函数
version    数据库服务器版本
database    当前打开的数据库
user    当前用户
password('字符') 自动加密
md5('字符') 返回md5加密形式
5、流程控制函数
if(条件表达式, 表达式1, 表达式2,)
相当于三目运算符

case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
……
else 值n
end

case
when 条件1 then 值1
when 条件2 then 值2
……
else 值n
end
 
6、分组函数
max    最大值
min    最小值
sum    求和
avg    平均值
count    计数

-- 进阶5： 分组查询
/*
select 分组函数,  group by 的组
from  表
【where 筛选条件】
【order by 子句】
注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段
*/

-- 案例1：查询每个工种的最高高能光子
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id;
-- 案例2：查询每个位置上的部门个数
SELECT COUNT(*),  location_id
FROM departments
GROUP BY location_id;

-- 添加筛选条件
-- 案例1：查询邮箱中包含a字符的， 每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees 
WHERE email LIKE '%a%'
GROUP BY department_id;
-- 案例2：查询有每个领导手下有奖金的员工的最高工资

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

-- 添加分组后的筛选条件
-- 案例1：查询哪个部门的员工个数大于2：
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;    -- having 让
-- 案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
SELECT MAX(salary), job_id
FROM employees 
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
-- 案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

-- 分组查询之按表达式或函数分组
-- 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*), LENGTH(last_name) len_name   
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5; 
 
 -- 分组查询之按多个字段分组
 -- 案例：查询每个部门每个工种的员工的平均工资
 SELECT AVG(salary), department_id, job_id
 FROM employees
 GROUP BY department_id, job_id;
 
 -- 分组查询之添加排序
 -- 案例：查询每个部门每个工种员工的平均工资并且平均工资高于10000按照平均工资的高低显示
 SELECT AVG(salary), department_id, job_id
 FROM employees
 WHERE department_id IS NOT NULL
 GROUP BY job_id, department_id
 HAVING AVG(salary) > 10000
 ORDER BY AVG(salary) DESC;
  
  
  -- 进阶6：连接查询
  /*
含义：又称多表查询，当查询的字段来自或涉及多个表时，用连接查询
 为防止笛卡尔乘积现象，要添加有效的连接条件   添加的时候用 .  连接表和栏名
 按年代分类：sql92,sql99
 按功能分类：内连接（等值连接、非等值连接、自连接）、外连接（左外连接、右外连接、全外连接）、交叉连接
 `information_schema`
  */
  
  -- 一、sql92标准
  -- 1、等值连接
  -- 案例1：查询女神名和对应男神名
  SELECT NAME, boyName
  FROM boys, beauty
  WHERE beauty.boyfriend_id = boys.id;
  -- 案例2：查询员工名和对应的部门名
  SELECT last_name, department_name
  FROM employees, departments
  WHERE employees.department_id = departments.department_id;
  
  -- 案例3. 查员工名，工种号，工种名
  SELECT last_name, employees.job_id, job_title
  FROM employees, jobs
  WHERE employees.`job_id` = jobs.job_id;
  
  -- 案例4：查询有奖金的员工名、部门名
  SELECT last_name, department_name, commission_pct
  FROM employees, departments
  WHERE employees.department_id = departments.`department_id`AND employees.commission.pct IS NOT NULL;
  
  -- 案例4：查询城市命中第二个字符为o的部门名和城市名
  SELECT department_name, city
  FROM departments d, locations l
  WHERE d.`location_id`=l.`location_id`
  AND city LIKE '_o%';
  
  -- 案例5：查询每个城市的部门个数（涉及到分组）
  SELECT city, COUNT(*)
  FROM locations l, departments d
  WHERE l.`location_id`=d.`location_id`
  GROUP BY l.city;
    
-- 案例6：查询有奖金的每个部门的部门名和部门的领导编号以及最低工资
SELECT department_name, d.manager_id, MIN(salary)
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL
GROUP BY department_name;

-- 案例7：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title, COUNT(*) 员工个数
FROM employees e, jobs j
WHERE e.job_id = j.`job_id`
GROUP BY j.`job_id`
ORDER BY 员工个数 DESC;

-- 可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.`department_id` AND
d.location_id = l.`location_id`;

/*
sql92标准的等值连接总结：
多表等值连接的结果是多表的交集部分
n表连接至少要有n - 1歌连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句
*/

-- 2、非等值连接
-- 案例1：查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
-- 创建等级表：
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES ('C', 6000, 9999);
INSERT INTO job_grades
VALUES ('D', 10000, 14999);
INSERT INTO job_grades
VALUES ('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);

-- 3.自连接
-- 案例：查询员工名和上级的名称
SELECT e.last_name employee, m.last_name hisLeader
FROM employees e, employees m
WHERE e.`manager_id`=m.`employee_id`;

/*心得：连接学到现在才发现连接的精髓却是最初排除的那一个反面例子。
就是笛卡尔积，它能帮助我们彻底理解连接这件事。简单说，where是对笛卡尔积的
过滤。把满足where的放到一行，其他的舍弃。就构成了合成后的新表
*/

-- 习题：
-- 1. 显示员工表的最大工资，工资平均值
SELECT MAX(salary), AVG(salary)FROM employees;
-- 2. 查询员工表的employee_id, job_id, last_name, anzhao department_id降序,salary升序
SELECT employee_id, job_id, last_name
FROM employees 
ORDER BY department_id DESC, salary ASC;
-- 3. 查询员工表的job_id中包含a 和e并且a再e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';
-- 4. 已知表student，里面有id(学号), name, gradId(年级编号)
-- 已知表grade,里面有id(年级编号), name (年级名)
-- 已知表result, 里面有id, score, studentNo(学号)
-- 要求查询姓名、年级名、成绩
SELECT s.name, g.name, score
FROM student s, grade g, result r
WHERE s.id=r.studentNo AND g.id=s.gradeId;

-- 5.显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM('   string   ');
SELECT TRIM(字符 FROM 'string ');
SELECT SUBSTR('string', INDEX);
SELECT SUBSTR('string', INDEX, LENGTH);