点击联系发帖人7.1 一个案例引发的多表连接
7.1.1 笛卡尔积（或交叉连接）的理解
笛卡尔乘积是一个数学运算。
假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
#查询员工姓名和所在部门名称
-
笛卡尔积的错误会在下面条件下产生：
- 省略多个表的连接条件（或关联条件）
- 连接条件（或关联条件）无效
- 所有表中的所有行互相连接
- 为了避免笛卡尔积， 可以在WHERE加入有效的连接条件。
7.2 多表查询分类讲解
分类1：等值连接vs非等值连接
拓展1：区分重复的列名
- 多个表中有相同列时，必须在列名之前加上表名前缀。
- 使用别名可以简化查询。
- 列名前使用表名前缀可以提高查询效率。
需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
- 连接n个表,至少需要n-1个连接条件。
分类2：自连接vs非自连接
#练习：查询员工id,员工姓名及其管理者的id和姓名分类3：内连接 vs 外连接
-
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
-
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
- 如果是左外连接，则连接条件中左边的表也称为
主表，右边的表称为从表。 - 如果是右外连接，则连接条件中右边的表也称为
主表，左边的表称为从表。
- 如果是左外连接，则连接条件中左边的表也称为
- 使用JOIN...ON子句创建连接的语法结构：
利用Union关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数
和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
#实现查询结果是A∪B
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
| 返回X的符号。正数返回1，负数返回-1，0返回0 |
| 返回大于或等于某个值的最小整数 |
| 返回小于或等于某个值的最大整数 |
| 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机 |
| 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| 返回数字x截断为y位小数的结果 |
| 返回x的平方根。当X的值为负数时，返回NULL |
| 将角度转化为弧度，其中，参数x为角度值 |
| 将弧度转化为角度，其中，参数x为弧度值 |
| 返回x的正弦值，其中，参数x为弧度值 |
| 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| 返回x的余弦值，其中，参数x为弧度值 |
| 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| 返回x的正切值，其中，参数x为弧度值 |
| 返回x的反正切值，即返回正切值为x的值 |
| 返回两个参数的反正切值 |
| 返回x的余切值，其中，X为弧度值 |
| 返回e的X次方，其中e是一个常数，2.045 |
| 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| 返回f1进制数变成f2进制数 |
| 返回字符串S中的第一个字符的ASCII码值 |
| 返回字符串s的字节数，和字符集有关 |
| 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| 用字符串b替换字符串str中所有出现的字符串a |
| 将字符串s的所有字母转成大写字母 |
| 将字符串s的所有字母转成小写字母 |
| 返回字符串str最左边的n个字符 |
| 返回字符串str最右边的n个字符 |
| 用字符串pad对str最左边进行填充，直到str的长度为len个字符，实现右对齐效果 |
| 用字符串pad对str最右边进行填充，直到str的长度为len个字符，实现左对齐效果 |
| 去掉字符串s左侧的空格 |
| 去掉字符串s右侧的空格 |
| 去掉字符串s开始与结尾的空格 |
| 去掉字符串s开始与结尾的s1 |
| 去掉字符串s开始处的s1 |
| 去掉字符串s结尾处的s1 |
| 返回str重复n次的结果 |
| 比较字符串s1,s2的ASCII码值的大小 |
| 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| 返回字符串s在字符串列表中第一次出现的位置 |
| 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
注意：MySQL中，字符串的位置是从1开始的。
8.3 日期和时间函数
8.3.1 获取日期、时间
| 返回当前日期，只包含年、月、日 |
| 返回当前时间，只包含时、分、秒 |
| 返回当前系统日期和时间 |
| 返回UTC（世界标准时间）日期 |
| 返回UTC（世界标准时间）时间 |
8.3.2 日期与时间戳的转换
| 将时间date以UNIX时间戳的形式返回。 |
| 将UNIX时间戳的时间转换为普通格式的时间 |
8.3.3 获取月份、星期、星期数、天数等函数
| 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| 返回日期对应的季度，范围为1～4 |
| 返回日期是一年中的第几天 |
| 返回日期位于所在月份的第几天 |
| 返回周几，注意：周日是1，周一是2，。。。周六是7 |
8.3.4 日期的操作函数
| 返回指定日期中特定的部分，type指定返回的值 |
| 返回日期在一年中的第几个星期 |
| 返回日期在一年中的第几个月 |
| 返回日期在一年中的第几个季度 |
8.3.5 时间和秒钟转换的函数
| 将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟*60+秒 |
| 将 seconds 描述转化为包含小时、分钟和秒的时间 |
8.3.6 计算日期和时间的函数
| 返回与给定日期时间相差INTERVAL时间段的日期时间 |
上述函数中type的取值：
| 返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| 返回从0000年1月1日起，N天以后的日期 |
| 返回日期date距离0000年1月1日的天数 |
| 返回date所在月份的最后一天的日期 |
| 针对给定年份与所在年份中的天数返回一个日期 |
| 将给定的小时、分钟和秒组合成时间并返回 |
| 返回time加上n后的时间 |
8.3.7 日期的格式化与解析
| 按照字符串fmt格式化日期date值 |
| 按照字符串fmt格式化时间time值 |
| 返回日期字符串的显示格式 |
| 按照字符串fmt对str进行解析，解析为一个日期 |
上述非GET_FORMAT函数中fmt参数常用的格式符：
| 两位数字表示月份（01,02,03。。。） | |
| 两位数字表示月中的天数(01,02...) | |
| 两位数字表示小数，24小时制（01,02..） | 两位数字表示小时，12小时制（01,02..） |
| 数字形式的小时，24小时制(1,2,3) | 数字形式表示小时，12小时制（1,2,3,4....） |
| 两位数字表示分钟（00,01,02） | |
| 一周中的星期名称（Sunday...） | |
| 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 | |
| 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | |
8.5 加密与解密函数
| 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密。在mysql8.0中已弃用。 |
| 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。 |
| 返回当前MySQL的版本号 |
| 返回当前MySQL服务器的连接数 |
| 返回MySQL命令行当前所在的数据库 |
| 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” |
| 返回字符串value自变量的字符集 |
| 返回字符串value的比较规则 |
返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
|
| 将value的值进行不同进制之间的转换 |
| 将以点分隔的IP地址转化为一个数字 |
| 将数字形式的IP地址转化为以点分隔的IP地址 |
| 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
可以对数值型数据使用AVG 和 SUM 函数。
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
- COUNT(*)返回表中记录总数，适用于任意数据类型。
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
可以使用GROUP BY子句将表中的数据分成若干组
在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
9.2.2 使用多个列分组
使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
- 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。
当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。
| 先筛选数据再关联，执行效率高 | 不能使用分组中的计算函数进行筛选 |
| 可以使用分组中的计算函数 | 在最后的结果集中进行筛选，执行效率较低 |
你需要记住 SELECT 查询时的两个顺序：
1. 关键字的顺序是不能颠倒的：
在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。
需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
-
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
-
添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表vt2。
然后进入第三步和第四步，也就是GROUP和HAVING阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段 。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段 ，得到虚拟表vt6。
最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT阶段 ，得到最终的结果，对应的是虚拟表vt7。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
10.1 需求分析与问题解决
题目：谁的工资比Abel高？
10.1.2 子查询的基本使用
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
10.2.5 子查询中的空值问题
| 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| 需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| 实际上是ANY的别名，作用相同，一般常使用ANY |
题目：查询平均工资最低的部门id
MySQL中聚合函数是不能嵌套使用的。
10.4.1 相关子查询执行流程
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
方式二：在 FROM 中使用子查询
结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！
- 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行：
- 如果在子查询中存在满足条件的行：
- NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
