因为一个事情,五一捡起好久没用的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、