Mysql第一关之常规用法

简介

介绍Mysql常规概念，用法。包括DDL、DCL、DML、DQL，关键字、分组、连表、函数、排序、分页等。

一、 SQL

DCMQ，分别代表DDL、DCL、DML、DQL。模糊简记为DCMQ，看起来像一个消息队列。

D：Definition 定义语句
M： Manipulation 操作语句
C： Control 控制语句
Q:　Query 查询语句

其中D和Q好理解，M和C容易混淆，但M是增删查改，C是涉及表、用户等授权Grant关键字。
D代表Alter、Drop、Create等定义语句。

二、DML

在DML中，有常见的增、删、查、改四个动作。可以归纳为写、读两种行为。

写：
Insertinsert into table  value/values..
insert into table select .. from ..Updateupdate table set  (where)Deletedelete from table (where)truncate table (属于DDL)

读： select
常见关键字有几个优先级层级，也即是逻辑执行顺序

select … distinct … from … where … group by … having … order by … limit

确定数据总量行数
from 表… where
确定数据总量列数
select 字段

distinct去重数据行数

 经过上述三个步骤，即得到初步精简的数据，接下来是进一步加工。

Group by分组

Having筛选分组数据

 经过进一步数据加工，已经得到最精简的数据结果，接下来就是排序和截断。

Order by排序
Limit截断

上述步骤先后顺序的核心思想，就是先得到最精简的数据，然后再进行加工这样的效率是最高的，比如Order by排序采用任意算法时，总量越大其耗时越长。

三、分组和聚合函数

分组

GROUP BY是分组关键字，其后可以按多个字段的先后顺序进行分组。
Having是对分组后的表进行条件筛选的关键字。Having只能和Group by一起使用。
为什么设计者要涉及where和Having两个筛选关键字？
我认为是为了区分不同的作用域。
where作用域为from后，having的作用域为group by后。

聚合函数

常见聚合函数有 count(), max(), min(), sum()等，
聚合函数可以放在什么位置呢？

在上述关键字逻辑执行顺序中，将数据加工分为了初步加工和进一步加工。

对于初步加工过程来说，即Select … from … where 结构。
聚合函数可以放在select中，而对数据的筛选需要放在where中，如

select max(id) from table where id > 1;

而对于进一步加工过程，即Group by … Having…结构，分组本身就具有聚合行为（将多个数据合并为一组），既可以像where关键字一样，对分组后的字段进行筛选。也可以对分组后的字段使用聚合函数后再筛选。from关键字不具备聚合能力，因此where无法进行聚合筛选，不能在where中使用聚合函数。

select * from test group by id having id > 1;select * from test group by id having max(id) > 1;

四、连表

连表有两种行为，横向连表（Join）和纵向连表（Union）

纵向连表

纵向连表采用Union关键字，Union自动去重，Union all 不自动去重。

	select * from table1 union （all）select *  from  table2

纵向连表使用Union时必须针对于相同结构的结果集或表。采用纵向连接。
在这里插入图片描述

横向连接Join

横向连接有三个分类：

内连接
外连接
交叉连接（笛卡尔积）

内连接

其中内连接表示两个表之间的交集

select * from table1 inner join table 2 on ...

外连接

外连接分为全外连接、左外连接、右外连接。
由于连接过程中，未匹配的列将会产生null值，因此可以用null值排除已连接的数据。

select * from table1 full join table2 on table1.name = table2.name  (包含已匹配数据)
select * from table1 full join table2 on table1.name = table2.name where table1.name = null or table2.name = null（不包含已匹配数据）

交叉连接

即产生table1 * table2 行数据，形成笛卡尔积数据。

Join揭秘

在内连接和交叉连接时，on会被优化为where，而left join 和 right join不会。这是为什么呢？

在使用left 或 right join时，其中一个基础表已经确定为左表或者右表。因此会先使用on关键字，将目标表的数据对比基础表筛选一遍，如果没有满足on条件则会丢弃该数据，直到得到目标表被筛选后的精简表，然后再使用Join关键字进行连接。而inner join由于无法确定基础表（小于或等于原表），因此是采用笛卡尔积相连，然后用on进行筛选，其作用等同于where，所以对于inner join关键字而言，Mysql会将on优化为where。

排序

排序用Order by，其后可以接多个字段，按字段的先后顺序进行排序。

select * from table order by id,name;

分页

分页用limit，其后跟1或2个参数。

	select * from table limit 1, 10;