mysql之常用函数、聚合函数以及合并(union&union all)

1. 笛卡尔集

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

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

加入where 可以避免无效查询

 二、等值连接/连接

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

 在 WHERE 子句中写入连接条件。

 在表中有相同列时，在列名之前加上表名前缀

 2、区分重复的列名

   在不同表中具有相同列名的列可以用表的别名

加以区分。

   如果使用了表别名，则在select语句中需要使

用表别名代替表名

   表别名最多支持32个字符长度，但建议越少越

好

3、表的别名

​ 使用别名可以简化查询。

​ 使用表名前缀可以提高执行效率。

区分多个重名的字段

 连接n个表,需要n-1个连接条件

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

4、连接查询案例

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

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

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

如何避免：添加有效的连接条件

分类：

按年代分类：

sql92标准:仅仅支持内连接

sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类：

内连接：

等值连接√

非等值连接

自连接

外连接：

左外连接√

右外连接√

全外连接

交叉连接

5、等值连接

1、等值连接

① 多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③ 多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

左外连接

SELECT b.*,bo.*

FROM t_mysql_boys bo

LEFT OUTER JOIN t_mysql_beauty b

ON b.`boyfriend_id` = bo.`id`

WHERE b.`id` IS NULL;

案例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;

4：常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：1、隐藏了实现细节 2、提高代码的重用性

调用：select 函数名(实参列表) 【from 表】;

特点：

①叫什么（函数名）

②干什么（函数功能）

分类：

 1、单行函数

如 concat、length、ifnull等

2、分组函数

功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：

一、单行函数

字符函数：

1.length 获取参数值的字节个数

SELECT LENGTH('john');

SELECT LENGTH('张三丰hahaha');

**concat 拼接字符串**

SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees;

**upper、lower**

SELECT UPPER('john');

SELECT LOWER('joHn');

示例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees;

**substr、substring**

注意：索引从1开始

截取从指定索引处后面所有字符

SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;

案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put

FROM t_mysql_employees;

**instr**

返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

**trim**

SELECT LENGTH(TRIM('   张翠山   ')) AS out_put;

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;

**lpad**

用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',2,'*') AS out_put;

**rpad**

用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') AS out_put;

**replace**

替换

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

###### 数学函数：

**round**

四舍五入

SELECT ROUND(-1.55);

SELECT ROUND(1.567,2);

**ceil**

向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02);

**floor**

向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99);

**truncate**

截断

SELECT TRUNCATE(1.69999,1);

**mod**

取余

mod(a,b) ： a-a/b*b

mod(-10,-3):-10- (-10)/(-3)*（-3）=-1

SELECT MOD(10,-3);

SELECT 10%3;

###### 日期函数：

**now**

返回当前系统日期+时间

SELECT NOW();

**curdate**

返回当前系统日期，不包含时间

SELECT CURDATE();

**curtime**

返回当前时间，不包含日期

SELECT CURTIME();

可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;

SELECT YEAR('1998-1-1') 年;

SELECT YEAR(hiredate) 年 FROM t_mysql_employees;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月;

**str_to_date**

 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

案例：

查询入职日期为1992--4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

###### 其他函数：

SELECT VERSION();

SELECT DATABASE();

SELECT USER();