MySQL之联表查询，聚合、常用函数

一、笛卡尔集：

                          1、笛卡尔集会在下面条件下产生：
                                                        – 省略连接条件
                                                        – 连接条件无效
                                                        – 所有表中的所有行互相连接
                                                        • 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。

二、等值/连接连接 ：

1、使用连接在多个表中查询数据 ：
• 在 WHERE 子句中写入连接条件。 
• 在表中有相同列时，在列名之前加上表名前缀

2、区分重复的列名:     
• 在不同表中具有相同列名的列可以用表的别名加以区分。
• 如果使用了表别名，则在select语句中需要使用表别名代替表名
 • 表别名最多支持32个字符长度，但建议越少越好            
 3、表的别名：
• 使用别名可以简化查询。
 • 使用表名前缀可以提高执行效率。
 4、连接多个表 :
 • 连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
5、连接查询案例 ：

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询.
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行.

发生原因：没有有效的连接条件.
如何避免：添加有效的连接条件.

分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

 分类：
                                                按年代分类：
                                                      sql92标准:仅仅支持内连接
                                                      sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
                                                按功能分类：
                                                       内连接：
                                                       等值连接
                                                       非等值连接
                                                       自连接
                                                       外连接：
                                                       左外连接
                                                       右外连接
                                                       全外连接
                                                       交叉文件  

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

#案例1：查询女生名和对应的男生名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

5.2、非等值连接: 

#案例1：查询员工的工资和工资级别
 
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';

  5.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

1、使用ON 子句创建连接 ：
                     自然连接中是以具有相同名字的列为连接条件的。
                     可以使用 ON 子句指定额外的连接条件。
                     这个连接条件是与其它条件分开的。
                     ON 子句使语句具有更高的易读性。
2、join案例 ：     
2.1语法：
         select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件
                  【where 筛选条件】
                  【group by 分组】
                  【having 筛选条件】
                  【order by 排序列表】 

 2.1.1内连接 ，左外连接，右外连接：

– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on

常见函数

1、字符函数

作用	函数	结果
转小写	LOWER('SQL Course')	sql course
转大写	UPPER('SQL Course')	SQL COURSE
拼接	CONCAT('Hello', 'World')	HelloWorld
截取	SUBSTR('HelloWorld',1,5)	Hello
长度	LENGTH('HelloWorld')	10
字符出现索引值	INSTR('HelloWorld', 'W')	6
字符截取后半段	TRIM('H' FROM 'HelloWorld')	elloWorld
字符替换	REPLACE('abcd','b','m')	amcd

 2、数字函数

作用	函数	结果
四舍五入	ROUND(45.926, 2)	45.93
截断	TRUNC(45.926, 2)	45.92
求余	MOD(1600, 300)	100

 3、日期函数 ：

 

作用	函数	结果
获取当前日期	now()
将日期格式的字符转换成指定格式的日期	STR_TO_DATE('9-13-1999','%m-%d-%Y')	1999-09-13
将日期转换成字符	DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)	2018年06月06日

五、聚合函数(SUM/COUNT/AVG/MAX/MIN)

   1) SUM()：求和。常与GROUP BY一起使用，也可单独使用,
   2) AVG()：求平均值。常与GROUP BY一起使用，也可单独使用
   3) MAX()：求最大值。常与GROUP BY一起使用，也可单独使用
   4) MIN()：求最小值。常与GROUP BY一起使用，也可单独使用
   5) COUNT()：统计记录的条数。常与GROUP BY一起使用，也可单独使用如果有筛选的条件加关键字having