MySQL之常用函数、聚合函数以及合并

一，迪卡尔集

什么是笛卡尔集 ？

笛卡尔集的列数为每个表的列数之和，笛卡尔集的行数为每个表的行数相乘。我们经常做的多表查询就是在笛卡尔集中通过筛选条件得出的数据，所以笛卡尔集是多表查询的基础。

为什么要使用多表查询 ？

因为我们经常需要在很多个表之间查询数据。比如，查询会员的银行卡提现记录。首先我们在会员表中查询会员的银行卡id，再到提现记录表中根据银行卡id查询提现记录。

笛卡尔集会在下面条件下产生

省略连接条件

连接条件无效

所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件

二、等值/连接连接

使用连接在多个表中查询数据

在 WHERE 子句中写入连接条件
在表中有相同列时，在列名之前加上表名前缀

区分重复的列名

在不同表中具有相同列名的列可以用表的别名加以区分
如果使用了表别名，则在select语句中需要使用表别名代替表名
表别名最多支持32个字符长度，但建议越少越好

表的别名

使用别名可以简化查询。
使用表名前缀可以提高执行效率

连接多个表

连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件

连接查询案例

按功能分类：
内连接：                外连接：
等值连接                左外连接（右外连接）
非等值连接            全外连接
自连接                   交叉连接

1、等值连接（筛选，分组，排序，三表连接）

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

案例：查询表1名和对应的表2名

SELECT （表1name属性）,（表2name属性）FROM （表1名）,（表2名）WHERE （表1名.id属性）= （表2名.id属性）;

2、为表起别名

1，提高语句的简洁度

2，区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例：查询员工名、工种号、工种名（红色：别名，绿色：表属性，黄色：表名）（表顺序是可以调换的）

SELECT e.last_name,e.job_id,j.job_title  FROM t_mysql_employees e,jobs j  WHERE e.`job_id`=j.`job_id`;

2，非等值连接
案例：查询员工的工资和工资级别

SELECT salary,grade_level  FROM t_mysql_employees e,t_mysql_job_grades g  WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`  AND g.`grade_level`='A';

3、自连接

案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM  t_mysql_employees e,t_mysql_employees m  WHERE e.`manager_id`=m.`employee_id`;

三、join链接

分类

内连接 ：[inner] join on

外连接 ：左外连接 ：left [outer] join on          右外连接： right [outer] join on  ​

使用ON 子句创建连接

自然连接中是以具有相同名字的列为连接条件的

可以使用 ON 子句指定额外的连接条件

这个连接条件是与其它条件分开的

ON 子句使语句具有更高的易读性

join  案例

语法：
select 查询列表  from 表1 别名 【连接类型】 join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】

分类：
内连接（★）：inner
外连接：
左外(★):left 【outer】        右外(★)：right 【outer】         全外：full【outer】         交叉接：cross

内连接

语法：

select  查询列表from  表1  别名  inner join  表2  别名  on  连接条件;

特点：

1，添加排序、分组、筛选

2，inner可以省略

3，筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

4，inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表    右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

案例：查询哪个部门没有员工（红色：别名，绿色：表属性，黄色：表名）

左外

SELECT d.*,e.employee_id  FROM t_mysql_departments d  LEFT OUTER JOIN   t_mysql_employees e  ON d.`department_id` = e.`department_id`  WHERE e.`employee_id` IS NULL;

右外

SELECT d.*,e.employee_id  FROM t_mysql_employees e  RIGHT OUTER JOIN t_mysql_departments d  ON d.`department_id` = e.`department_id`  WHERE e.`employee_id` IS NULL;

全外

SELECT b.*,bo.*  FROM t_mysql_beauty b  FULL OUTER JOIN t_mysql_boys bo  ON b.`boyfriend_id` = bo.id;

四，常见函数

1、字符函数

 2、数字函数

 3，日期函数

 

 4、其他函数 

SELECT VERSION();

SELECT DATABASE();

SELECT USER();

五、聚合函数

1、 SUM()：求和。

2、 AVG()：求平均值。

3、MAX()：求最大值。

4、 MIN()：求最小值。（1、2、3、4、常与GROUP BY一起使用，也可单独使用）

5、 COUNT()：统计记录的条数。常与GROUP BY一起使用，也可单独使用 如果有筛选的条件加关键字having

六、合并

1、UNION：将所有的查询结果合并到一起，然后去除掉相同的记录

2、UNION ALL：将所有的查询结果合并到一起，不会去除掉相同的记录

  OK!  到这已经结束了，希望能帮到你！！！