联表查询相关语法
1.查询sql语句的执行顺序
sql:语法
select distinct * from 左表名
(left/inner/right)join 右表名
on 连接条件
where 筛选条件
group by 分组的列表(按什么字段分组)
having 分组条件
order by 排序的字段
limit 分页
以上为语法结构，顺序不能乱
执行顺序：
- from 左表名
- on 连接条件
- 连接类型 (inner/left/right) join 右表名
- where 筛选条件
- group by 分组列表(按什么字段分组)开始使用select中的别名，后面的语句中都可以使用
- 聚合函数avg、sum、max、min、count
- having having_condition
- select
- distinct 查询列表(要查询的字段)
- order by 排序的字段等
- limit limit_number
123步骤先确定要查询的大表；4步骤确定要的记录，去掉不要的记录；56分组并筛选要的；78留下要的字段列；9排序一下；10留下想要的记录数
- 详解：从上述顺序中不难发现，所有的查询语句都是从from语句开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
-
首先对from子句中的前两个表执行一个笛卡尔积，此时生成虚拟表vt 1(选中相对小的表做基础表)
-
应用on筛选器，on中的逻辑表达式将应用到vt 1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表vt 2。
-
如果是outer join 那么这一步就将添加外部行，left outer join 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt 3 。
-
如果 from 子句中的表数目多余两个表，那么就将vt 3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt 3。
-
where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表 vt 4
注意where和on的区别：先执行on，后执行where；on是建立关联关系，在生成临时表时执行；where是在临时表生成后对数据进行筛选的
-
group by子句将列中的唯一的值组合成为一组，得到虚拟表 vt 5。如果应用了 group by ，那么后面是所有步骤都只能得到vt 5 的列或聚合函数，原因在于最终的结果集中只为每一个组包含一行从这一步开始才可以使用select中的别名，他返回的是一个游标，而不是一个表，所以在where中不可以使用select中的别名，而having却可以使用
-
聚合函数，为vt 5生成超组，生成vt 6
-
having 筛选器，生成vt 7，having筛选器是第一个也是唯一一个应用到已分组数据的筛选器
-
处理select子句，将vt 7中的select中出现的列筛选出来，生成 vt 8
-
distinct，对vt 8进行去重，生成vt 9
-
order by，按照排序条件排序 vt 9，此时返回的一个游标，而不是虚拟表
-
limit ，生成 vt 10 返回结果给请求者即用户
outer join：完全外连接，返回左表和右表的所有行
left (outer)join:左外连接，返回匹配条件的行和左表的所有行
right(Outer)join:右外连接，返回匹配条件的行和右表的所有行
笛卡尔积： 假设A和B是两个集合，存在一个集合，它的元素是用A中元素为第一元素，B中元素为第二元素构成的有序二元组，这个集合称为集合A和集合B的笛卡尔积，记为A X B。
2.联表查询
多表联合查询：同时查询两个或两个以上的表
why：当我们查询的结果不在一张表中，就需要联表查询
联表查询的种类：
- 内连接：查询两个表中公共部分
- 外连接
- 左外连接：查询左边中所有的内容以及公共内容
- 右外连接：查询右边中所有的内容以及公共内容
- 自连接：自己和自己连接
2.1表与表之间的关系
- 表与表之间有联系：外键
- 表与表之间的关系：
- 多对一(n : 1)：员工表和部门表**外键放置在多那方**
- 多对多(n : n)：学生和课程设置一个中间表，用来存放两个外键，即两张表的主键
- 一对一(1 : 1)
2.2内连接
- 语法
Select * from 左表名
inner from 右表名
on 联表条件
-- 查询员工信息以及员工对应的部门信息。(我们查询的内容不在一张表中 需要联表查询)
-- select * from 表1 inner join 表2 on 联表条件
select * from tb_emp inner join tb_dept; -- 如果没有联表条件 则会出现笛卡尔积
-- 如何得到结果不在一张表中，就需要联表查询。
select * from tb_emp inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select e.*,d.name dname from tb_emp e inner join tb_dept d on e.dept_id=d.id
2.3左外连接
- 语法
select * from 左表名
left join 右表名
on 联表条件
-- 查询员工信息以及对应部门得信息 没有部门的员工也显示。
select * from tb_emp e left join tb_dept d on e.dept_id=d.id
2.4右外连接
- 语法
select * from 左表名
right join 右表名
on 联表条件
-- 查询员工信息以及对应部门得信息 显示没有员工的部门信息
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;
2.5自连接
- 自己连接自己的表，连接类型为上述三种
-- 查询员工姓名以及对应的领导名字
select yg.name 员工姓名,ld.name 领导名 from tb_emp yg inner join tb_emp ld on yg.managerid=ld.id
-- -- 查询员工姓名以及对应的领导名字 显示没有领导员工信息
select yg.name 员工姓名,ld.name 领导名 from tb_emp yg left join tb_emp ld on yg.managerid=ld.id
3.嵌套查询
把一个sql查询的结果作为另一个sql查询的条件的值
例子: 查询研发部的员工信息。
【1】查询研发部的部门号 【2】根据查询的部门号查询对应的员工信息
select * from tb_emp where dept_id=( select id from tb_dept where name='研发部')例子: 查询研发部，市场部的员工信息
select * from tb_emp where dept_id in( select id from tb_dept where name='研发部' or name='市场部')例子: 查询薪资最高的员工信息。
select * from tb_emp where salary=( select max(salary) from tb_emp);
4.把查询的结果作为一个临时表
例子: 查询各个部门中最高薪资的员工信息
select e.* from tb_emp e inner join (select max(salary) m,dept_id from tb_emp group by dept_id) t on e.dept_id=t.dept_id and e.salary=t.m
5. mysql中内置的函数
mysql里面存在了很多函数方法，字符串函数，时间函数，数学函数等。
5.1字符串函数
操作字符串
| 函数 | 功能 |
|---|---|
| concat(s1,s2,…sn) | 字符串拼接，将s1,s2,…sn拼接成一个字符串 |
| lower(string) | 将字符串全部转为小写 |
| upper(string) | 将字符串全部转为大写 |
| lpad(string,n,pad) | 左填充，用字符串pad对string的左边进行填充，达到n个字符串长度 |
| rpad(string,n,pad) | 右填充，用字符串pad对string的右边进行填充的，达到n个字符串长度 |
| trim(string) | 去掉字符串头部和尾部的空格 |
| substring(string,start,len) | 返回从字符串string的start位置起的len个长度的字符串 |
| char_length(string) | 返回字符串的长度 |
-- concat()字符串拼接
select name,concat('hello',name,'吃吗',salary) from tb_emp;
-- upper(str)转化为大写字母
select name, upper(name) from tb_emp;
-- lpad左填充
select name,rpad(name,2,'A') from tb_emp;
-- trim()去除左右空格
select name,trim(name) from tb_emp;
-- substring()截取字符串 下标从1开始
select name,substring(name,1,1)from tb_emp;
-- 姓灭
select * from tb_emp where substring(name,1,1)='灭'
-- char_length 求字符串的长度
select name,char_length(name) from tb_emp;
-- 求名字长度为3
select * from tb_emp where char_length(name)=3
5.2 日期函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日期 |
| date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| datediff(date 1,date 2) | 返回起始时间date 1和结束时间date 2之间的天数 |
-- 查找今天入职的员工信息
select * from tb_emp where entrydate=curdate()
-- 当前日期
select entrydate,curdate(),curtime(),now(),year(entrydate),month(entrydate) from tb_emp;
-- date_add(日期数据,interval 值 单位) : 单位:year month day hour MINUTE second
select entrydate,date_add(entrydate,INTERVAL 1 year) from tb_emp;
-- datediff 两个日期的相差的天数
select entrydate, datediff(now(),entrydate) from tb_emp;
5.3 数学函数
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回x/y的模 |
| abs(x) | 求某个数的绝对值 |
-- 查询最近1月的订单
select salary,ceil(salary),floor(salary),RAND()*10 from tb_emp;
6. 查询存在一个表而不在另一个表中的数据
问题描述： A、B两表，找出ID字段中，存在A表，但是不存在B表的数据。A表总共13w数据，去重后大约3W条数据，B表有2W条数据，且B表的ID字段有索引。
- 方法一： 使用 not in ,容易理解,效率低 ~执行时间为：1.395秒
1 select distinct A.ID from A where A.ID not in (select ID from B)
- 方法二： 使用 left join…on… , “B.ID isnull” 表示左连接之后在B.ID 字段为 null的记录 ~执行时间：0.739秒
1 select A.ID from A left join B on A.ID=B.ID where B.ID is null
- 方法三： 逻辑相对复杂,但是速度最快 ~执行时间: 0.570秒
select * from A where (select count(1) from B where A.ID=B.ID) = 0
方法三完美！由于数据量大，方法一，二，根本打不开