MySQL关键字及顺序书写顺序 select -> distinct -> from -> join -> on ->

书写顺序

select -> distinct -> from -> join -> on -> where -> group by -> with -> having -> order by -> limit

执行顺序

SQL语句执行顺序\ from->on->join->where->group by（开始使用select中的别名，后面的语句中都可以使用别名）->sum、count、max、avg->having->select->distinct->order by->limit

根据sql的执行顺序，where时还没有别名，order by时有别名因此where不能使用列别名作为过滤条件而order by可以使用别名，group by也是可以用别名的（在oracle中是不可以用别名的）

where条件语句后面不能加聚合函数（分组函数）我们也可以这样记忆：where后面只能跟表中存在的字段。
having 不能单独使用，必须和group by 联合使用

SQL的执行顺序_sql执行顺序优先级_洛禾符的博客-CSDN博客

blog.csdn.net/he_wen_jie/…

关键字含义

mysql使用大全链接 www.sjkjc.com/mysql/

limit

limit后面可以跟1个或2个数字

limit x : 返回前x条数据

limit x, y : x代表偏移量，从0开始，y代表返回行数

limit 0, 5 : 返回前5条数据等价于 limit 5

limit 5, 10 : 返回第6 - 15 条数据

limit 5, -1 : 返回从第6条开始到最后所有的数据

查找第n页，每页10条的数据

limit (n-1)*10 , 10

distinct

有的时候distinct后面会加括号，distinct(name)也能正确执行，mysql虽然能正确解析但不代表这样用法是规范的，distinct是关键字并非函数，所以无需用括号

排除重复的行——根据参数指定的列排除重复的行

在 SELECT 语句中使用 DISTINCT 关键字会返回一个没有重复记录行的结果集。 DISTINCT 的用法如下：

SELECT DISTINCT
    columns_list
FROM
    table_name

说明：

DISTINCT 关键字位于 SELECT 关键字的后面。
columns_list 指定要查询的字段列表，也是 DISTINCT 评估记录行是否唯一的字段。
columns_list 可以是一个字段，也可以是多个字段。当指定多个字段值时， DISTINCT 使用多个字段组合确定记录行的唯一性。
columns_list 也可以是 *****。
当 DISTINCT 遇到 NULL 值时，只保留一个 NULL 值。因为 DISTINCT 认为所有的 NULL 值都是相同的，这与字段的类型无关。

using

using等价于关联操作中的on

当两个表中的关联字段名字相同时，比如Customers 的cust_id和Orders的cust_id

传统写法

SELECT
	order_num,
	cust_name 
FROM
	Orders
	LEFT JOIN Customers ON Customers.cust_id = Orders.cust_id;

using写法

SELECT
	order_num,
	cust_name 
FROM
	Orders
	LEFT JOIN Customers using(cust_id);

order by

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   column2 [ASC|DESC],
   ...;

说明：

ORDER BY 子句可以指定一个或多个字段。
[ASC|DESC] 代表排序是升序还是降序，这是可选的。
ASC 代表升序，DESC 代表降序。
未指定 [ASC|DESC] 时，默认值是 ASC。即，默认是按指定的字段升序排序。
当指定多个列时，首先按照前面的字段排序，其次按照后面的字段排序。
当指定多个列时，最好每个列都明确写明ASC还是DESC，且多个列不能共用一个ASC/DESC
如果出现null值，在升序中，NULL值出现在非null值之前；在降序中，NULL值出现在非null值之后

union时order by写在在哪里？

union前后的两个子句不加括号时，只能在最后写order by，不能每个子句都加order by

SELECT * FROM t1 WHERE username LIKE 'l%' ORDER BY score 
UNION
SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score

SELECT * FROM t1 WHERE username LIKE 'l%'
UNION
SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score

这样的意思是先union，然后对整个结果集进行order by。如果我想分别对每个结果集排序后再union呢？

需要加括号。

SELECT * FROM
(SELECT * FROM t1 WHERE username LIKE 'l%' ORDER BY score) t3
UNION 
SELECT * FROM
(SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score) t4

group by

SELECT列表中的列必须是GROUP BY子句中的列或聚合函数

示例1 平均活跃天数和月活人数

select
    date_format (submit_time, '%Y%m') month,
    round(
        count(distinct uid, date (submit_time)) / count(distinct uid),
        2
    ) avg_active_days,
    count(distinct uid) mau
from
    exam_record
where
    year (submit_time) = '2021'
group by
    date_format (submit_time, '%Y%m')

示例2 月总刷题数和日均刷题数

select
    date_format (submit_time, '%Y%m') submit_month,
    count(id) month_q_cnt,
    any_value (
        round(count(id) / DAY (LAST_DAY (submit_time)), 3)
    ) avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = '2021'
group by
    date_format (submit_time, '%Y%m')
union all
select
    '2021汇总' submit_month,
    count(id) month_q_cnt,
    round(count(id) / 31, 3) avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = 2021
order by
    submit_month;

示例3 未完成试卷数大于1的有效用户

SELECT
	uid,
	sum( CASE WHEN submit_time IS NULL THEN 1 ELSE 0 END ) incomplete_cnt,
	sum( CASE WHEN submit_time IS NOT NULL THEN 1 ELSE 0 END ) complete_cnt,
	group_concat( DISTINCT CONCAT ( date ( start_time ), ':', tag ) ORDER BY start_time SEPARATOR ';' ) detail 
FROM
	exam_record
	INNER JOIN examination_info USING ( exam_id ) 
WHERE
	YEAR ( start_time ) = '2021' 
GROUP BY
	uid 
HAVING
	incomplete_cnt BETWEEN 2 
	AND 4 
	AND complete_cnt >= 1 
ORDER BY
	incomplete_cnt DESC

union

union--连接表，对行操作。
union--将两个表做行拼接，同时自动删除重复的行。
union all---将两个表做行拼接，保留重复的行

like 和 not like和rlike

like用来模糊匹配

SELECT * FROM fruits where user like 'yuf%';

SELECT * FROM fruits where user like binary 'yuf%';

% 匹配零或多个任意字符。
_ 匹配单个任意字符。
如果需要匹配通配符，则需要使用 ** 转义字符，如 % 和 _**。
使用通配符匹配文本时，不区分字母大小写。

not like与like意思相反，其余都一样

SELECT * FROM category WHERE name NOT LIKE 'A%';

rlike等价于like + 通配符，rlike意思是只要包含某个关键字就行，效率比 like+通配符要高

rlike后面可以跟正则表达式

case when then

case when then 结构有两种格式，分别是确定条件列和不确定条件列。

case 列

when 值1 then 返回值1

when 值2 then 返回值2

else 默认返回值

end 列名

这种方式默认每个when 后面的条件是 ‘列=值’

case

when 条件1 then 返回值1

when 条件2 then 返回值2

else 默认返回值

end 列名

这种方式，条件可以是该行记录任何情况的条件，只要返回布尔值就行。\

select *,
    case score 
    when 100 then '满分'
    else score
　　end '分数'
from stucores ;
select *,
    case  
    when score=100 then '满分'
    when score<100 and score>=60 then '及格'
    when score<60 then '不及格'
　　end '评级'
from stucores ;

with rollup

用来对数据进行汇总，mysql中没有with cube

现在有这样一张学生表，里面的数据如下所示。

如果想对根据学生，对科目，分数求和，可以这样写。

如果想在这个的基础上，求出学生的总分数，应该怎么做。

replace

替换表中存在的数据，如果表中已经存在相同的唯一键或主键，则会删除旧的记录，并插入新的记录。

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

REPLACE操作会尝试插入新的记录，如果表中已经存在相同的唯一键或主键，则会删除旧的记录，并插入新的记录。

on duplicate key update

ON DUPLICATE KEY UPDATE操作用于在插入数据时，如果发生唯一键或主键冲突，则更新已存在的记录。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

replace和on duplicate key update区别：

REPLACE操作会删除已存在的记录，并插入新的记录，相当于先执行了DELETE操作，再执行了INSERT操作。而ON DUPLICATE KEY UPDATE操作会尝试插入新的记录，如果发生唯一键或主键冲突，则会更新已存在的记录。
REPLACE操作会导致自增主键的值发生变化，因为它实际上是删除旧记录并插入新记录。而ON DUPLICATE KEY UPDATE操作不会改变自增主键的值。
REPLACE操作在替换记录时是一个原子操作，要么替换成功，要么失败。而ON DUPLICATE KEY UPDATE操作可以在发生冲突时执行更新操作，可以在插入失败时执行其他操作。

需要注意的是，使用REPLACE或ON DUPLICATE KEY UPDATE操作时，要保证表中有唯一键或主键约束。否则，这些操作会等同于普通的INSERT操作，直接插入新记录。

exists 和 not exists

SELECT 
	c.CustomerId,
  c.CompanyName 
FROM 
	Customers c
WHERE 
	EXISTS(SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)

这里面的EXISTS是如何运作呢？子查询返回的是OrderId字段，可是外面的查询要找的是CustomerID和CompanyName字段，这两个字段肯定不在OrderID里面啊，这是如何匹配的呢？

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False 。EXISTS 指定一个子查询，检测行的存在。如果子查询至少存在一行就返回true，否则就返回false

with as

with as 也叫子查询，用来定义一个sql片段，且该片段会被整个sql语句反复使用很多次，这个sql片段就相当于是一个公用临时表

with tmp  as （select * from B）

select * from  tmp

先执行select * from B拿到一个结果，将这个结果记录为tmp，再执行 select * from tmp

因with as 子查询仅执行一次，将结果存储在用户临时表中，提高查询性能，所以适合多次引用的场景，如：复杂的报表统计，分页查询，且需要拿到sum、count、avg这类结果作为筛选条件，对查询出的结果进行二次处理！

with 
t1 as (select * from tb1),		-- 第一个共用表
t2 as (select * from tb2),		--（第二个共用表
t3 as (select * from tb3)		--（第三个共用表

MySQL关键字及顺序