常用SQL语句

因为一个事情，五一捡起好久没用的sql处理一些数据。感觉有必要将有些东西记录下来，省得每次去找。
另外就是很多东西也是自己从网上学来的，也需要有些反馈、贡献才对。

一、查询——Select选择

1. 最基本的公式
select * from *
where * ----- 加入条件
group by * ----汇总、归类用
order by * — 排序。变量后面加入ASC（默认的），DESC（倒序）

示例：

 select * from (
 	select client, 
 		count(DISTINCT park_id) as num_park, GROUP_CONCAT(park_id) as route, 
	 	count(DISTINCT dayn) as num_day, GROUP_CONCAT(dayn) as day_series 
  	from W1_week_0503
  	where park_id < 99
  	GROUP BY client) as A
where A.num_park > 1
ORDER BY A.num_park
LIMIT 2000

2. select中常用到的几个函数或功能
count(distinct 变量)---- 注意中间没有，逗号
group concat(distinct 变量)----将变量中的选项汇总起来，变成一列。
limit 100 ----有些时候想看下字段是否都出来了，那么查询语句最后一定要加上limit，否则机器跑半天。
in (’’, ‘’,…) ----in和not in在条件判断中非常有用。当然in本身也可以嵌套select语句，不过这个时候好像只筛选出一个变量。

3. select嵌套
经常会遇到一种情况，就是希望将select语句中生成的某个临时变量作为某个条件去进一步使用。
那就需要用（select语句） as A来使用，A就是select语句的别名。然后后面就可以用 “A.变量” 来直接调用了。
示例：select * from （select * from table_name）as a;
最后的a，其实可以是任意的一个名称，仅仅是括号中筛选的别称而已。
这种方法有一个好处，就是有些时候筛选中生成了一些新变量，然后对这个变量有一些条件要求时，用这个方法就很方便。例如：

select * from 
（select name, 2020-birthday as age from table_name）as a
where a.age <= 60;

---

二、创建及更改——Create Table创建表

1.用select结果创建表
这个是最快的创建的方式，否则需要定义变量等。
操作非常简单：create table新表名，直接加select语句就行
示例：

CREATE TABLE 表名 
select * from **;

2.用select创建新表后还想增加变量怎么办，用alter+update试试
例如下面这个，为新表加上一个id。update中为id加了一个日期前缀。

alter table 表名 add id int auto_increment 
primary key FIRST;
update 表名 set id = 0501000000 + id;

3.删除字段

alter table table_name drop column variable_name;

---

三、更新与插入
1、更新-update
基本句式：
update table_name set variable_name = value where condition;
value就是具体选项或取值，condition就是具体的条件。

当根据一个字段的值来改变另外一个字段的值，条件值都很多的时候，也可以用case-when，例如下面的：

UPDATE table_name SET 
spot_id = 
CASE
WHEN  device = 'F9-5E' THEN 'D'
WHEN  device = 'F1-E4' THEN 'E'
WHEN  device = 'D1-10' THEN 'F'
WHEN  device = 'D9-10' THEN 'G'
ELSE 'Z' 
END;

2、插入——insert into

INSERT INTO table_name
(variable1，variable2......)
SELECT variable1，variable2...... FROM table_name2; 

从一个表中select出一些数据然后插入到一个新表中可以用上面方法。注意字段数量要一样，否则会出错。
如果新表有自动添加的id等的话，可以不用考虑这个id，其他字段对应上就可以了。

---

四、一些特殊用法
1、数学计算
2、count等于case when then end的结合使用
有些时候需要将count等计算的结果生成新的变量，但计算的过程中需要添加条件语句。这个时候用where然后通过join等将几个带where的select连接起来的话，sql就会比较长。
count、sum等中嵌套case when条件就非常有效了。

select hourn, 
round(count(distinct case when daytype=1 then client_mac end)) as d51,
round(count(distinct case when daytype=2 then client_mac end)) as dweekend,
round(count(distinct case when daytype=3 then client_mac end)) as dworkday
from p1_clean group by hourn

3、结合case when根据同一个变量选项不同产生出不同的新变量

select 
case park_id when 1 then 1 end as park1,
case park_id when 2 then 2 end as park2,
case park_id when 3 then 3 end as park3,
case park_id when 4 then 4 end as park4,
case park_id when 5 then 5 end as park5
from tablename 

4、left join
left join不是对两个select的合并。而是新建一个select对已经建立的两个select的合并。
因此如果需要合并多个表（n），那么好吧，就是做一个新的select，从这n个表中去查询。将这n个表联合起来查询的时候，需要有一个on来确认这些表是有一致性的，例如有一致的id。

select x.id, p1, p2, p1_time, p2_time 
from(
select id, park_id p1, start_time p1_time
from table1
where park_id = 1) x

left join

(select id, park_id p2, start_time p2_time
from table2
where park_id = 2) y

on x.id = y.id

---

五、时间戳 timestamp
时间戳的操作很多，可以进行提取，也可以进行转化，甚至直接通过转化来进行做一些条件判断等。时间戳最常见的格式如下：
“2020-05-01 10:26:44”

1、从时间戳提取对应的元素
可以通过
year(timestamp)
month(timestamp)
day…hour…minute…second
等将具体的年份、月份等信息提取出来。

2、