数据库知识与技巧日常汇总

1. 快速导出数据库的字段到Excel

（2020年6月22日）

SELECT
	COLUMN_NAME 列名,
	COLUMN_TYPE 数据类型,
	DATA_TYPE 字段类型,
	CHARACTER_MAXIMUM_LENGTH 长度,
	IS_NULLABLE 是否为空,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注
FROM
	INFORMATION_SCHEMA. COLUMNS
WHERE
	-- senta-service-mdt2.0为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
	table_schema = 'ms_convenience'
AND -- s_patient_list为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称
table_name = 'con_my_address'

2. SQL快速查找是否"存在"

# 正常写法
##### SQL写法:
SELECT count(*) FROM table WHERE a = 1 AND b = 2

##### Java写法:
int nums = xxDao.countXxxxByXxx(params);
if ( nums > 0 ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}
# 优化写法
##### SQL写法:
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

##### Java写法:
Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}

是真的猛！SQL 语法速成手册

3. 查询树形表格某个节点的所有子节点

通过创建函数，然后调用函数即可。

字符串形

CREATE FUNCTION `getChildList`(rootId varchar(36)) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp='$';
SET sTempChd=cast(rootId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp=concat(sTemp,',',sTempChd);
SELECT group_concat(code) INTO sTempChd
FROM
	organ
WHERE
   FIND_IN_SET(pcode, sTempChd) > 0;

END
WHILE;
RETURN substr(sTemp, 3);
END

调用

select getChildList('10000005') as childList;

4. 自增主键从某个值开始

-- 清空表的所有内容，包括自增序列
TRUNCATE TABEL USER;
-- 设置自增主键从某个值开始
ALTER TABLE USER AUTO_INCREMENT=109; 

5. 列转行使用max

SELECT orgcode,MAX(carry) carry,MAX(haircut) haircut,MAX(water) water,MAX(wash) wash FROM(
SELECT organization_code orgcode,count(1) carry,0 haircut,0 wash,0 water FROM con_carry_order GROUP BY organization_code
UNION
SELECT organization_code orgcode,0 carry,count(1) haircut,0 wash,0 water FROM con_haircut_order GROUP BY organization_code
UNION
SELECT organization_code orgcode,0 carry,0 haircut,count(1) wash,0 water FROM con_water_order GROUP BY organization_code
UNION
SELECT organization_code orgcode,0 carry,0 haircut,0 wash,count(1) water FROM con_wash_order GROUP BY organization_code) a GROUP BY orgcode

6. 根据多个字段删除重复数据

DELETE 
FROM
	table_name 
WHERE
	(字段 1, 字段 2 ) IN (
	SELECT
		t.字段 1,
		t.字段 2 
	FROM
		( SELECT 字段 1, 字段 2 FROM table_name GROUP BY 字段 1, 字段 2 HAVING count( 1 ) > 1 ) t 
	) 
	AND id NOT IN (
	SELECT
		dt.id 
	FROM
	( SELECT min( id ) AS id FROM table_name GROUP BY 字段 1, 字段 2 HAVING count( 1 ) > 1 ) dt 
	)