4 - 基础查询进阶｜连接查询

数据库系列文章

1 - 数据库服务概述 | 构建MySQL服务 | 数据库基本管理 | MySQL基本类型
2 - 表结构 | MySQL键值
3 - 字段约束｜MySQL索引｜MySQL用户管理
4 - 基础查询进阶｜连接查询
5 - 视图｜存储过程
6 - 数据备份与恢复｜innobackupex
7 - MySQL主从同步｜主从同步模式
8 - MySQL数据读写分离｜MySQL多实例
9 - 数据分片概述｜部署MyCat服务
10 - MHA集群概述｜部署MHA集群
11 - PXC集群｜MySQL存储引擎

基础查询

• 字符函数
• 数学函数
• 聚集函数
  sum() avg() min() max() count()
• 时间函数

mysql> select curdate()

流程控制函数

• if 语句

if (条件,v1,v2) 如果条件是TRUE则返回v1 否则返回v2
---
ifnull(v1,v2) 如果v1不为null 则返回v1 否则返回v2

• case语法 （可以有多个判断添加）

CASE 字段名
WHEN 值1 THEN 结果
WHEN 值1 THEN 结果
WHEN 值1 THEN 结果
ELSE 结果
END

查询结果处理

语法格式：

select 字段名列表 from 库.表 [where 条件] 分组|排序|过滤|分页

1. 分组 group by 字段名
   统计使用每种shell的用户个数

mysql> select shell from tarena.user group by shell

2. 排序 把查找的数据排队，用来排队的字段数据类型应该是数字类型

order by 字段名 asc; # 从小到大排序
order by 字段名 desc ; # 从大到小排

查看满足条件记录的name和uid字段的值

mysql> select name,uid from user where uid is not null order by uid

SQL语句书写思路
第一步 确认使用的命令 （对数据处理使用 select update insert delete）
第二步 确认数据在哪个表 （可以确定from后的表名）
第三步 确定处理的是什么数据
第四步 确定处理条件

---

把2018年每个员工的收入由高到底排序
查看每个员工的总收入

select employee_id ,sum(basic+bonus) as total from tarena.salary
where year(data)=2018 group by employee_id;

按总收入降序排列

select employee_id,sum(basic+bonus) as total from tarena.salary
where year(date)=2018 group by employee_id order by total desc;

3. 过滤数据，select 查询结果 having 筛选条件
   对select查询到的数据 再次作筛选

select 字段名列表 from 库.表 [where 条件] having 筛选条件

查询部门人数少于5人

mysql> select dept_id ,count(name) from tarena.employees group by dept_id having count(name)<5;

4. 分页
   limit限制每次查询显示的行数

select 字段名列表 from 库.表 where 条件 limiter 数字
select 字段名列表 from 库.表 where 条件 limiter 数字1,数字2

比如 从表里查询100条记录 想分5页显示 100/5=20

select * from tarena.user limit 0,20; 显示第一页20条数据

连接查询

把多张表通过连接条件 组成一张新表，然后在组成的新表里查找数据
如果直接查询两张表，将会得到笛卡尔积（2张表里行数相乘的积）

连接查询分类

格式

select 字段列表 from 表1 [as] 别名 [连接类型] join 表2 [as] 别名
on 连接条件
where 分组前筛选条件
group by 分组
having 分组后筛选条件
order by 排序字段

内连接

select 字段列表 from 表1 [as] 别名 
inner join 表2 [as] 别名 on 连接条件
inner join 表3 [as] 别名 on 连接条件
where 分组前筛选条件
group by 分组
having 分组后筛选条件
order by 排序字段

• 等值连接 使用相同判断条件
  查询2018年每个员工的总工资

mysql> select name ,date,basic,bonus from employees 
inner join salary on employees.employee_id=salary.employee_id
where year(date)=2018 

mysql> select mame,sum(basic+bonus) from employees 
inner join salary on employees.employee_id=salary.employee_id
where year(date)=2018 group by name

不加sum横着加 加了sum竖着加

• 非等值连接 使用不同判断条件

# 查询2018年12月份员工各基本工资级别的人数
mysql> select grade,count(employess_id) as numbers
from salary as s 
inner join wage_grade as g on s.basic between g.low and g.high
where year(date)=2018 and month(data)=12
group by grade;

• 自连接 自己连接自己

外连接

用来比较2个表里记录的不同 或者 哪些数据当前表有而另一张表没有

• 左外连接 left join
  当左边表的记录全部显示出来 右边的表只显示与条件匹配记录 比左边表少的记录使用null匹配 显示对应个数的行

• 右外连接 right join
  当右边表的记录全部显示出来 左边的表只显示与条件匹配记录 比右边表少的记录使用null匹配 显示对应个数的行

• 全外连接（mysql不支持，可以使用union实现相同的效果）
  用来合并查询结果
  可以合并同一张的表的查询记录 （不同表的查询记录也可合并）
  要求查询时，多个select语句的检索到的字段数量必须一致
  每一条记录的各字段类型和顺序最好时一致的

# union关键字默认去重，可以使用union all包含重复项
# 语法格式
(select语句) union (select语句);
(select语句) union all (select语句);

子查询

select查询语句里 包含其他select