mysql 添加字段(可直接复制)
alter table
carInfo #表名
add
carIdentity #要添加的字段
varchar(100) #类型
COMMENT '身份证' #备注
AFTER carTeamName #要跟随的字段名
说明:alter table + 表名 + add + 要添加的字段 字段类型 + (comment +注释+) after + 要跟随的字段名
mysql 添加多字段
ALTER TABLE
driver #表名
add (
`carIdentity` varchar(100) DEFAULT NULL COMMENT '身份证', #添加字段1
`Registrationtime` datetime DEFAULT NULL COMMENT '注册时间', #添加字段2
`Activationtime` datetime DEFAULT NULL COMMENT '激活时间', #添加字段3
`carPhoneModel` varchar(100) DEFAULT NULL COMMENT '手机型号' #添加字段4,备注,最后一个不要加逗号
)
mysql 更新字段(可直接复制)
ALTER TABLE
carInfo #表名
CHANGE
carType #原始名字
carType #需要修改名字
varchar(100) #类型
COMMENT '类型' #备注
mysql 删除字段(可直接复制)
ALTER TABLE
carInfo #表名
DROP
carPhoneModel #删除的字段
sql格式化日期
select
DATE_FORMAT(update_time,'%Y-%m-%d') AS created #格式化时间,年月日
from
wx_accountc #表名
sql多表联查,进行每天数据统计
##复杂
SELECT
b.ds,
substring_index(
substring_index(a.key_words, '|', b.cotitle + 1),
'|',
-1
) course
FROM
search_task a
JOIN daily b on b.cotitle < (
length(a.key_words) - length(REPLACE(a.key_words, '|', '')) + 1
)
sql查询每日任务量(表中只有一条数据,进行多表联查,目标表的日期数据)
SELECT
b.ds,(
SELECT
COUNT(0) #计数
FROM
tousu.heimao_search_task #表
) as count_i #别名
FROM
mao_search a #字段
JOIN video_daily b #关联字段
GROUP by
ds
sql平均数
select ds,concat(
TRUNCATE(
(
(SELECT count(1) FROM wx_account_stat where company_id > 0)/
(SELECT count(1) FROM wx_account_stat where unit_nature='企业')
)
* 100,2),'%'
) as rate from wx_account_stat group by ds;
sql字段添加
replace into video_collection_stat.video_daily (ds , cnt ,platform_name)
sql 计算每天增量(累计数据)
SELECT a.ds AS date_t,SUM(b.num) AS cum FROM
(SELECT sum(cnt) as num ,ds FROM video_daily GROUP BY ds) a
JOIN
(SELECT sum(cnt) as num ,ds FROM video_daily GROUP BY ds) b
ON a.ds >= b.ds GROUP BY a.ds
sql 去除空值进行计算
select cotitle, sum(cnt) as sum_int from video_daily
where cotitle!='' GROUP BY cotitle
sql 黑窗口时候使用(列显示)
查询:select * from t_copr_tort limit 1 \G;
sql (LEFT JOIN向上填充)
SELECT
a.ds,
COUNT(1)
FROM
(SELECT ds FROM yuqing_daily GROUP BY ds) a
LEFT JOIN
(SELECT DATE_FORMAT(beg_time, '%Y-%m-%d') AS ds FROM t_search_task) b
ON a.ds >= b.ds
GROUP BY a.ds
sql 前后相减(join)
SELECT a.day , a.account_number - b.account_number as ram_sum FROM
(SELECT DATE_FORMAT(DATE_SUB(day,INTERVAL 1 DAY),'%Y-%m-%d') as day,account_number from wx_public) AS a
JOIN
(SELECT day,account_number from wx_public) AS b
ON a.day = b.day