前言

在平时的工作过程中虽然经常要用到Excel表格的公式去加工处理到手的数据，但很多时候数据量一大，公式就会显得力不从心，最近还发现了几个不可忽视的问题，所以闲暇之余我都会研究一遍如何用SQL数据库提（尽）高（情）效（摸）率（鱼）。

于是我就在这里持续更新使用SQL的问题和可能相应的解决方案

SQL顺序

书写顺序：SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT

执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> ORDER BY ->LIMIT

技巧&用法

WITH AS CTE

之前有一段时间我不知道有这个用法，在使用JOIN合并匹配查询的时候都是直接在FROM位置嵌套查询

WITH
  临时表名1 AS ( SELECT * FROM 表名......),
  临时表名2 AS ( SELECT * FROM 表名......)
SELECT
  *
FROM
  临时表名1 LEFT JOIN 临时表名2 ON 临时表名1.`字段名` = 临时表名2.`字段名`

查看当前运行任务

select *
from information_schema.processlist
where command != 'Sleep'
order by time desc ;

Navicat

• 右键数据库，选择 命令列界面可以进入SQL命令执行界面

注释符号

单行用双减号: --

块注释用组合符号:

/*
注释内容
*/

聚合函数

常用: sum, avg, count, max, min

在聚合函数使用时可以组合使用，比如

sum(`语文成绩`)/count(`语文成绩`)

自定义列名, 表名

在select中使用as可以查询字段结果自定义列名

select
  sum(`语文成绩`)/count(`语文成绩`) as '平均语文成绩' from `考试成绩`

在select所有子句后用as自定义表名

(
select
  sum(`语文成绩`)/count(`语文成绩`) as '平均语文成绩',
  sum(`数学成绩`)/count(`数学成绩`) as '平均数学成绩' from `考试成绩`,
  sum(`英语成绩`)/count(`英语成绩`) as '平均英语成绩' from `考试成绩`,
from
  `考试成绩`
where
  sum(`语文成绩`,`数学成绩`,`英语成绩`) >= 240
)
as tmp

JOIN的类型

LEFT JOIN

RIGHT JOIN

INNER JOIN

FULL OUTER JOIN

JOIN实现字段匹配筛选

在select语句中，join可以实现类似于excel的vlookup公式的效果，比如根据学号匹配三个科目的成绩

select
  `学生名册`.`学生名称`,
  `学生名册`.`学号`,
  `考试成绩`.`语文成绩`,
  `考试成绩`.`数学成绩`,
  `考试成绩`.`英语成绩`
from
  `学生名册` left join `考试成绩` on `学生名册`.`学号` = `考试成绩`.`学号`

join可以顺序嵌套实现多字段依次匹配

select
  `学生名册`.`学生名称`,
  `学生名册`.`学号`,
  `考试成绩`.`语文成绩`,
  `考试成绩`.`数学成绩`,
  `考试成绩`.`英语成绩`，
  `体育成绩`.`成绩`
from
  `学生名册` left join `考试成绩` on `学生名册`.`学号` = `考试成绩`.`学号` left join `体育成绩` on `学生名册`.`学号` = `体育成绩`.`学号`

索引的添加

使用where的时候尽量为使用到的字段添加索引，可以大大加速SELECT效率

函数记录

字符串替换

REPLACE(str,from_str,to_str)

日期相关

条件判断

IF(expr1,expr2,expr3)

定位字符串位置

POSITION(substr IN str)

LOCATE(substr,str,pos)

截取字符串

LEFT(str,len)

MID(str,pos,len)

RIGHT(str,len)

SUBSTRING_INDEX(str,delim,count)

count > 0 : 从左往右数，第N个分隔符的左边的全部内容 (不包含分隔符)

count < 0 : 从右往左数，第N个分隔符的右边的全部内容 (不包含分隔符)

截取中间第N和第N+1个分隔符的部分: substring_index(substring_index(str, '-', N+1), '-', -1)

基本语句

增

固定字段、数值

insert into
  `table_name`
  (column1, column2, column3,...columnN) 
values
  (value1, value2, value3,...valueN)

将select结果插入表内

insert into
  `table_name`
(
  select
    `table_name`.`column1`
  from
    `table_name`
  [where]...
)

删

delete
from 
  `table_name` 
where 
  `column1` ...

查

select
  `table_name`.`column`
from
  `table_name`  
[(left/inner/right/cross/full outer) join]
[where]
[group by]
[having]
[order by]
[limit]

改

update
  `表名`
set 
  column1 = value1,
  column2 = value2
where
  condition

版权属于：Zakikun

本文链接：https://blog.zakikun.com/archives/38.html

本文采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。
您可以自由的转载和修改，但请务必注明文章来源并且不可用于商业目的。