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

一、笛卡尔集

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

缺点：

– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接

联表查询时会产生很多重复代码，效率慢，数据过多时可能会卡死数据库

解决方式：可以在where中加入有效条件

二、等值连接 

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

例：以t_mysql_beauty表和t_mysql_boys表举例

select * from t_mysql_boys a,t_mysql_beauty b where a.id=b.id;

在where子句中加入连接条件，如果有相同列名，在列名前加表名前缀

a.id=b.id就是连接条件，两个表都有id这个列所以起别名，方便调用

连接多个表时要有总数减一个查询条件把这些表连接起来

联表查询时表的顺序可以调换

可以加筛选

例：select * from t_mysql_boys a,t_mysql_beauty b where a.id=b.id and b.photo is not null

在原本的多表查询多了一个条件，从而优化代码

分组

例：查询每个男生的女朋友个数

select count(*),boyfriend_id from t_mysql_boys a,t_mysql_beauty b where a.id=b.id GROUP BY boyfriend_id;

解析：GROUP BY分组语句、先调用a.id=b.id查到数据在进一步按照boyfriend_id进行分组count(*)计算个数

三、非等值连接

例:查询员工的工资和工资级别（salary工资）

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';

原理：拿salary工资去对应 g.`lowest_sal` AND g.`highest_sal`AND g.`grade_level`='A';这三个条件去进行筛选

四：自连接 

自己跟自己进行连接

例;查询员工名与上级的名称

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`; 

原理：自己跟自己进行连接

上面sql语句分析：先运行e.`manager_id`=m.`employee_id`;然后根据e.`manager_id对应的上级名进行查询

五、使用on子句创建连接

使用on子句指定额外的连接条件

on子句是与其他条件分开的

on子句可以让语句变得更加易读

六、内连接/外连接/交叉连接 

一、内连接（inner）

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

特点：

①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

二、左外连接（left）

语法：select 查询列表 from 表1 别名 left join 表2 别名 on 连接条件; 

含义：左表的所有数据连接右表的部分数据

例：查询哪个部门没有员工

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;

三、右外连接（right）

语法：select 查询列表 from 表1 别名 right join 表2 别名 on 连接条件; 

含义：右表的所有数据连接左表的部分数据

例：查询哪个部门没有员工

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;

七、常见函数

一、字符函数

 

最常用的是获取字符长度

二、数字函数

 

三、日期函数

 

日期函数三个都是很常用的、记好笔记家银们

四、其他函数

version
database
user

五、控制函数 

if
case

八、子查询 

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

就相当于查询里面再套了个查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询

from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √

行子查询

exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

 子查询的例子

查询员工的信息，满足 salary>last_name = 'Abel'结果
SELECT *
FROM t_mysql_employees
WHERE salary>(

SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'

);

九、聚合函数

sum 求和

例：SELECT SUM(salary) FROM t_mysql_employees;

avg 平均值

例：SELECT AVG(salary) FROM t_mysql_employees;

max 最大值 

例：SELECT MAX(salary) FROM t_mysql_employees;

min 最小值 

例：SELECT MIN(salary) FROM t_mysql_employees;

count 计算个数

例：SELECT COUNT(salary) FROM t_mysql_employees;

十、合并 

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

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