sql 学习

ALTER TABLE `pm_sys_packages`
ADD COLUMN `APP_NAME`  varchar(64) NULL AFTER `PACKAGE_USE_TYPE`,
ADD COLUMN `APP_CODE`  varchar(32) NOT NULL AFTER `APP_NAME`;

cancat 拼接：

update pm_sys_packages t,sm_system s set t.APP_CODE = 
CONCAT(s.SYS_NAME,'-',t.PACKAGE_ID) 
where t.SYS_ID = s.SYS_ID and 
( t.APP_CODE = '' or t.APP_CODE is null);

update pm_sys_packages pm,
( 
SELECT DISTINCT
T.SYS_ID,
T.packageId,
T.PACKAGE_NAME,
T.packageUseType,
T.packageType,
T.PACKAGE_USE_TYPE_NAME,
CONCAT(
T.packageUseType,
T.packageType
) as appName,
T.REPOS_NAME,
T.PACKAGE_DESC
FROM
(
SELECT
sm.SYS_ID,
pa.PACKAGE_NAME,
 (
 CASE
 WHEN pa.PACKAGE_TYPE = '163' THEN
 '应用发布包'
 WHEN pa.PACKAGE_TYPE = '164' THEN
 '静态发布包'
 END
 ) packageType,
 (
 CASE
 WHEN pa.PACKAGE_USE_TYPE = '183' THEN
 '前台'
 WHEN pa.PACKAGE_USE_TYPE = '184' THEN
 '后台'
 WHEN pa.PACKAGE_USE_TYPE = '298' THEN
 '中台'
 WHEN pa.PACKAGE_USE_TYPE = '294' THEN
 ''
 END
 ) packageUseType,
pa.PACKAGE_USE_TYPE_NAME,
pa.PACKAGE_ID AS packageId,
re.REPOS_NAME,
1,
pa.PACKAGE_DESC
FROM
sm_system sm,
pm_sys_packages pa,
cf_repository re
WHERE
sm.SYS_ID = pa.SYS_ID
AND re.RELATE_ID = sm.SYS_ID

AND re.TYPE = 115
) T) tab  set pm.APP_NAME = tab.appName where pm.PACKAGE_ID = tab.packageId  
and ( pm.APP_NAME = '' or pm.APP_NAME is null);

 

分组 获取 的数据进行去重筛选：（对查询回来属性列表中，部分属性distinct,不能整体distinct）,所以使用group by

 SELECT 
 t1.GROUP_ID,t1.ARTIFACT_ID,t3.VERSION, MAX(t1.CREATE_TIME) ,
 MAX(t2.UPDATE_TIME) 
 FROM 
 CF_BRANCH_BUILD_DETAILTASK t1 INNER JOIN CF_BRANCH_BUILD_BASICINFO  t2
 ON t1.BUILD_ID=t2.ID AND t2.BRANCH_STATUS='0' AND t2.BRANCH_ID <> '0'
 INNER JOIN CF_BRANCH t3 ON t2.BRANCH_ID=t3.BRANCH_ID
 INNER JOIN BM_PACKAGE_CONFIG t4 ON t2.SYS_ID=t4.SYS_ID 
 AND t4.PACKAGE_TYPE='MAVEN'
 WHERE
	t1.CREATE_TIME>='20170701' AND t1.CREATE_TIME<='20170801'
    AND t1.BUILD_STATUS='2'
	GROUP BY t1.GROUP_ID,t1.ARTIFACT_ID,t3.VERSION

 

删除一个表中重复的数据，且保留ID最大的一列(极为高效)：

 

DELETE AA FROM cf_branch_sonaranalyse AA , (SELECT DISTINCT A.ID,A.DETAIL_ID  
FROM cf_branch_sonaranalyse A,cf_branch_sonaranalyse B WHERE 
A.DETAIL_ID=B.DETAIL_ID AND A.ID<B.ID) 
BB WHERE AA.ID=BB.ID;

 

连表查询，自己的幼稚错误：

SELECT 
 a.BUILD_CONFIG_ID AS buildConfigId,
 a.APP_ID AS appID,
 a.APP_NAME AS appName,
 a.PACKAGE_VERSION AS packageVersion,
 a.PACKAGE_INNER_VERSION AS packageInnerVer,
 a.PACKAGE_EXCLUDE_UPGRADE AS packageExcludeVer,
 a.PACKAGE_UPGRADE_MSG AS packageUpgradeMsg,
 a.SYS_ID AS sysId,
 a.SYS_ENNAME AS sysEnname,
 a.REPOS_NAME AS reposName,
 a.OPERATOR AS operator,
 a.OPERATE_TIME AS operateTime,
 a.PKG_OSS_PATH AS pkgOSSPath,
 a.BUILD_RECORD_ID AS buildID,
 a.BUILD_NAME AS buildName,
 a.BUILD_VERSION AS buildVersion,
 a.QR_DOWN_URL AS appQRDownloadUrl,
 a.PACKAGE_TYPE AS packageType,
 a.BRANCH_URL AS BranchUrl,
 a.BRANCH_ID AS BranchId,
 a.VERSION_ID AS versionId,
 ai.APP_ICON_URL AS appIconUrl
FROM mobile_app_build_record_pkginfo a
LEFT JOIN app_info ai ON a.BUILD_CONFIG_ID = ai.BUILD_CONFIG_ID
LEFT JOIN build_record b ON a.BUILD_RECORD_ID = b.build_record_id
LEFT JOIN build_task c ON b.build_task_id = c.build_task_id
WHERE c.task_name=#{buildTaskName}
LIMIT 1

这是正确的，但是自己却写下：

SELECT
	a.BUILD_CONFIG_ID AS buildConfigId,
	a.APP_ID AS appID,
	a.APP_NAME AS appName,
	a.PACKAGE_VERSION AS packageVersion,
	a.PACKAGE_INNER_VERSION AS packageInnerVer,
	a.PACKAGE_EXCLUDE_UPGRADE AS packageExcludeVer,
	a.PACKAGE_UPGRADE_MSG AS packageUpgradeMsg,
	a.SYS_ID AS sysId,
	a.SYS_ENNAME AS sysEnname,
	a.REPOS_NAME AS reposName,
	a.OPERATOR AS operator,
	a.OPERATE_TIME AS operateTime,
	a.PKG_OSS_PATH AS pkgOSSPath,
	a.BUILD_RECORD_ID AS buildID,
	a.BUILD_NAME AS buildName,
	a.BUILD_VERSION AS buildVersion,
	a.QR_DOWN_URL AS appQRDownloadUrl,
	a.PACKAGE_TYPE AS packageType,
	a.BRANCH_URL AS BranchUrl,
	a.BRANCH_ID AS BranchId,
	a.VERSION_ID AS versionId,
	ai.APP_ICON_URL AS appIconUrl
FROM
	mobile_app_build_record_pkginfo a,
	app_info ai
LEFT JOIN build_record b ON a.BUILD_RECORD_ID = b.build_record_id
LEFT JOIN build_task c ON b.build_task_id = c.build_task_id
WHERE
	c.task_name =?
AND a.BUILD_CONFIG_ID = ai.BUILD_CONFIG_ID
LIMIT 1

当然连表可以直接使用where,但是不能where联合 与 join查询混用：

select a.name,b.email from a,b 
where a.nameid=b.nameid and a.fenleiid=3

查询每天数据的峰值：

SELECT
DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')  as T ,count(*)
FROM
cf_branch_build_detailtask
WHERE
CREATE_TIME >= '20180401'
AND CREATE_TIME <= '20180418'
GROUP BY T;

mysql 使用查询的数值去修改数据：

UPDATE `env` AS A INNER JOIN project AS B ON A.project_id=B.id SET A.`name` = 
CONCAT(B.`name`, '-', A.branch_name);