MySQL语法

MySQL

1.连接查询

1.1.笛卡尔积现象

select ename, deptno from emp

ename	deptno
SMITH	20
ALLEN	30
CLARK	10

select * from dept

deptno	dname
10	ACCOUNT
20	RESEARCH
30	SALES
40	OPERATIONS

# 若两张表在没有任何条件的情况下连接，会共产生1*4*4=16条记录
select ename, dname from emp, dept

ename	dname
SMITH	ACCOUNT
SMITH	RESEARCH
SMITH	SALES
SMITH	OPERATIONS
....	....

1.2.内连接之等值连接

SQL92语法：

select
	e.ename, d.dname
from 	
	emp e, dept d
where
	e.deptno = d.deptno

SQL99语法：

select
	e.ename, d.dname
from 
	emp e
inner(可省略) join
	dept d
on 
	e.deptno = d.deptno
where
	....

SQL99语法相较于SQL92语法，结构更加清晰

因为在SQL92中，where后面拼接的是连表相关的语句，但是如果后续还有条件需要过滤，需要不停的在后面拼接上and，把其它的过滤条件和连表的过滤条件全部都糅杂一起了，结构不清晰

而在SQL99中，通过join的形式，每join一个表后面on跟上对应的连表条件，如果需要对其它数据进行过滤，只需要继续用where进行过滤即可

1.3.内连接之非等值连接

# 案例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级
select
	e.ename, e.sal, s.grade
from 
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal # 条件不是一个等量条件，称为非等值连接

1.4.内连接之自连接

# 案例：查询员工的上级领导，要求显示员工名和对应的领导名
select empno, ename, mgr from emp

技巧：一张表看成两张表

特点：能够完全匹配上条件的数据查询出来（A和B两张表没有主次关系）

e1:

empno	ename	mgr
7369	SMITH	7902
7499	ALLEN	7698
7566	WARD	7698
7902	FORD	7566

e2:

empno	ename	mgr
7369	SMITH	7902
7499	ALLEN	7698
7566	WARD	7698
7902	FORD	7566

select
	e1.ename as staff, e2.ename as leader
from
	emp e1
join
	emp e2
on
	e1.mgr = e2.empno

# 分析可得，SMITH的上级领导是FORD，FORD的上级领导是WARD

1.5.外连接

特点：能够匹配上条件的数据查询出来，不能匹配的数据置为null。具体谁为null，看谁是做为主表，谁是做为次表。

1.5.1.左外连接（左连接）

# emp为主表，dept为次表
select * from emp
left outer(一般省略不写) join dept
on emp.deptno = dept.deptno

1.5.2.右外连接（右连接）

# dept为主表，emp为次表
select * from emp
right outer(一般省略不写) join dept
on emp.deptno = dept.deptno

1.5.3.全连接

全连接的方式有union和union all

如果我们需要将两个 select 语句的结果作为一个整体显示出来，我们就需要用到 union 或者 union all 关键字。union (或称为联合)的作用是将多个结果合并在一起显示出来。

union 和 union all 的区别是，union 会自动压缩多个结果集合中的重复结果，而 union all 则将所有的结果全部显示出来，不管是不是重复。

union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表 union。

SELECT create_time FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK'
UNION
SELECT create_time FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK'

SELECT create_time FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK'
UNION ALL
SELECT create_time FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK'

注意事项

1.UNION 和 UNION ALL 内部的 SELECT 语句必须拥有相同数量的列

2.每条 SELECT 语句中列的顺序必须相同

如果顺序不同,结果字段的顺序以union all 前面的表字段顺序为准

SELECT * 
FROM(
	SELECT msku,create_time FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK'
UNION ALL
	SELECT create_time,msku FROM `e_msku_sku` WHERE msku = '21-BQLEDNL120W-BK') t

2.分组函数

定义：分组函数又称多行处理函数，输入多行，最终输出一行

• count 计数
• sum 求和
• avg 平均值
• min 最小值
• max 最大值

1.注意：分组函数在使用的时候必须先进行分组，然后才能使用。

如果没有对数据进行分组，整张表默认为一组

# 找出最高工资
select max(sal) from emp

# 计算员工数量
select count(ename) from emp

# 其余的类似....

2.注意：分组函数在使用的时候会自动忽略null

例如求和的数据中有一些为null，则求和的时候会自动忽略null，对有值的数据进行相加

3.注意：分组函数不能够直接使用在where子句中（解决方法看分组查询）

# 这种语法是不被允许的
select sal from emp where sal > min(sql)

2.1.count(*)和count(具体字段)区别

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。

count(*)：统计表当中的总行数。（只要有一行数据count则++)

3.分组查询

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。这个时候我们需要使用分组杳询．怎么进行分组杳询呢?

1.将之前的关键字全部组合在一起，来看一下他们的执行顺序?

select
	...
from
	...
where
	...
group by
	...
order by
	...

2.以上关键字的顺序不能颠倒，需要记忆。执行顺序是什么?

from -> where -> group by -> select -> order by

# 这条语法where后面没有分组就直接使用了分组函数，所以报错
select sal from emp where sal > min(sal)

3.多个字段分组

技巧：两个字段联合成1个字段看

# 找出每个部门不同工作岗位的最高薪资
select
	deptno,
    job,
    max(sal)
from 
	emp
group by
	deptno, job

3.1.having

定义：having不能单独使用，having不能代替where，having必须和group by联合使用

# 找出每个部门最高薪资，要求显示最高薪资大于3000的数据
select 
	deptno, 
	max(sal) maxsal 
from 
	emp 
group by 
	deptno
having 
	max(sal) > 3000
    (或者用别名) maxsal > 3000

思考：以上语句执行效率高吗？

答：比较低，实际上可以这样考虑:先将大于3000的都找出来，然后再分组

# 先筛选出大于3000的工资，然后分组，最后在这些组里高于3000的查出最高值
3001,5000,4000 => | deptno | sal | => | deptno | sal |
                      1	     3001			1    3001
                      2	     5000			2 	 5000
                      2	     4000
select
	deptno, 
	max(sal) maxsal 
from 
	emp
where
	sal > 3000
group by 
	deptno

优化策略：where和having优先使用where

where没办法的

# 找出每个部门的平均薪资，要求显示平均薪资高于2500的数据

# 如果这样写，相当于是查出了所有工资大于2500的数据，再对部门进行分组，假如有1部门和2部门，1部门中工资大于2500的有一个人，2部门中工资大于2500的有一个人，那么下面语句查出来的就是这两个部门中的这两个人的薪资和他们对应的部门编号。
# 而我们希望的应该是对1部门中所有员工的薪资求平均值，然后查询出该部门的平均薪资以及部门编号

select 
	deptno,
    avg(sal) 
from 
	emp
where
	sal > 2500
group by
	deptno
	
# 应该是先分组，分组之后求出每个组的平均薪资，再和2500做比较，所以这边只能用having过滤
select 
	deptno, 
	avg(sal)
from 
	emp 
group by 
	deptno
having 
	avg(sal) > 2500