MySQL笔记3

补充：常见函数中属于单行函数的

数学函数  rand 获取随机数，返回0-1之间的小数

日期函数 datediff 返回两个日期相差的天数

                monthname 以英文形式返回月     

其他函数 password('') 给单引号里字符随机加密     

                MD5('')  也是给单引号里的字符加密                 

   

目录

进阶5：分组查询

5.1 引入

简单案例：

5.2 分类

5.2.1 添加分组前筛选案例：

5.2.2 添加复杂的筛选条件 即添加分组后 案例：

特点：

5.2.3 按表达式（函数）分组   

5.2.4 按多个字段分组

5.2.5 添加排序

测试： 

进阶6：连接查询（又称多表查询，当查询字段涉及多表时）

6.1 引入

笛卡尔乘积现象：表1 m行 ，表2 n行，结果却有m*n行

6.2分类：

按年代分类 

按功能分类：

6.2.1 按sql92分类

sql92分类的 等值连接

总结等值连接：

sql92分类的 非等值连接 （把等值连接中的= 换成别的）

sql92分类的 自连接

6.2.2 sql99分类（内连接☆、外连接：左外☆，右外☆、交叉连接）

连接类型：【内连接inner、左外连接left (outer) 、右外连接right(outer) 、

语法：select 查询列表

1、sql99 内连接

2. sql99外连接

6.2.3 sql92 与 sql99 比较

6.3 总结连接查询

6.4 测试

---

     

进阶5：分组查询

5.1 引入

goup by 子句语法： select 分组函数, 列（要求出现group by的后面）

                                  from 表名

                                【where 筛选条件】

                                  group by 分组的列表

                                【order by 子句】

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

简单案例：

简单案例1：查询每个工种的最高工资 

SELECT MAX(salary),job_id 
FROM employees
GROUP BY job_id;

案例2：查询每个位置上的部门个数

SELECT COUNT(department_id),location_id FROM departments 
                #或者count(*)
GROUP BY location_id;

5.2 分类

5.2.1 添加分组前筛选案例：

案例3：查询邮箱中包含a字符的每个部门的平均工资

SELECT AVG(salary),department_id FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

案例4：查询有奖金的每个领导手下员工的最高工资

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

5.2.2 添加复杂的筛选条件 即添加分组后 案例：

案例1：查询哪个部门的员工个数>2

先查询每个部门的员工个数

SELECT COUNT(*),department_id FROM employees
GROUP BY department_id;

再根据第一步结果进行筛选

#①
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 manager_id,MIN(salary) FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

特点：

                                     数据源                    位置                       关键字

分组前筛选                   原始表                group by子句前          where

分组后筛选                   结果集                group by子句后          having

故    分组函数做条件一定是在having 子句中

故    能用分组前筛选的 就优先考虑用分组前筛选

5.2.3 按表达式（函数）分组   

案例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT COUNT(*),LENGTH(last_name) FROM employees
GROUP BY LENGTH(last_name)     
HAVING COUNT(*)>5;

补充：group by、having 后支持别名，但是where后不支持

5.2.4 按多个字段分组

案例1：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

多个字段分组，就在group by后面加上分组的字段即可，用逗号隔开

5.2.5 添加排序

查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

补充：排序放在分组查询之后

测试： 

1、查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

2、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary),manager_id FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id 
HAVING MIN(salary)>=6000;

3、查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary) FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

进阶6：连接查询（又称多表查询，当查询字段涉及多表时）

6.1 引入

笛卡尔乘积现象：表1 m行 ，表2 n行，结果却有m*n行

                  原因：没有有效的连接条件

                  避免：加上有效的连接条件

6.