mysql常用的语句

• 数据表备份

1.创建表结构
CREATE TABLE srm_table_bpk.sodr_po_header_NEWLPS_2140 like sodr_po_header;
2.同步数据
INSERT INTO srm_table_bpk.sodr_po_header_NEWLPS_2140 SELECT * from  sodr_po_header WHERE po_num in ('PO202202001578', 'PO202202002302', 'PO202201000076', 'PO202112004423');

• 创建用户并授权

create user 'hzero'@'%' identified by 'hzero';
CREATE DATABASE hzero_platform DEFAULT CHARACTER SET utf8mb4;
GRANT ALL PRIVILEGES ON hzero_platform.* TO hzero@'%';
flush  privileges;
drop user chg@'%';

• 获取表设计

select column_name columnName, 
   data_type dataType, 
   column_comment columnComment, 
   column_key columnKey, 
   extra ,
   is_nullable as isNullable,
   column_type as columnType 
from information_schema.columns
where table_name = 'smdm_item' AND data_type = 'varchar'
   and table_schema = (select database()) 
order by ordinal_position;

• 更新数据表

UPDATE aatn_counting_lines liness 
LEFT JOIN aafm_asset asset  ON liness.asset_id = asset.asset_id
LEFT JOIN halm_mdm.amdm_organization owning_org ON ( asset.using_org_id = owning_org.org_id )
SET liness.attribute12 = owning_org.org_name 
WHERE liness.attribute12 is null;

• 获取数据表 字段

SELECT 
	GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';

• 列拼接

GROUP_CONCAT( user_id SEPARATOR ',' ) 

• 查询数据库包含某列的表

select * from information_schema.columns where table_schema='srm-uat-1204' and column_name='kpi_codes' 
AND TABLE_NAME NOT IN ('smdm_order_subsidy_rule','smdm_order_ensure_rule','smdm_kpi_rule_header','smdm_item','sprm_pr_header','ssrc_rfx_unusual_action_header','ssrc_rfx_header','spcm_pc_header','ssrc_price_library','ssrc_price_library_copy','ssrc_price_temporary_library');

• mysql 定时备份

#!/bin/bash
PASSWORD=HH123
BACKUP_PATH=$(date +%Y%m%d)
cd /data/mysql/log
if [ ! -d "$BACKUP_PATH/" ];then
mkdir -p $BACKUP_PATH
fi
docker exec mysql mysqldump -uroot -p --databases test > /data/mysql/log/$BACKUP_PATH/test$(date +%Y%m%d).sql

• 字段替换

UPDATE `smdm_item` SET `item_name` = replace (`item_name`,'21300000','21:30-00:00') WHERE `item_name` LIKE '%21300000%'

• mysql 死锁排查

#1.查看进程  
select GROUP_CONCAT( ID SEPARATOR ',' )  from information_schema.processlist where db= 'srm' AND state = 'Sending data';
select *  from information_schema.processlist where db= 'srm' AND state = 'Sending data' AND time > 10

#1.查看哪些进程一直在执行
select *  from information_schema.processlist where db= 'srm' AND state = 'Sending data' AND ID IN (8113811,8113835,8114454,8113822,8113793)

#2.查看是否锁表
SHOW OPEN TABLES WHERE In_use > 0;

#3.查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#4.查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

#5.查询 正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;

• 行转列

select * from (
SELECT
  substring_index( substring_index( line.serial, ',', b.help_topic_id + 1 ), ',',- 1 )  AS serial 
 FROM
  payment line
  JOIN mysql.help_topic b ON b.help_topic_id < ( length( line.serial ) - length( REPLACE ( line.serial, ',', '' ))+ 1 )
	where line.id = 5 
) tmp where tmp.serial like '12%'

---

• spfm_company_* 与 hpfm_company

<select id="queryCompanyName" resultType="org.srm.platform.domain.entity.Company">
    <bind name="lang" value="@io.choerodon.mybatis.helper.LanguageHelper@language()"/>
    select 
    	ifnull(scbl.company_name, hc.company_name) company_name, hc.company_num
    from hpfm_company hc
    LEFT JOIN (SELECT MAX(version_number) version_number, company_id FROM spfm_company_basic WHERE process_status = 'COMPLETE' GROUP BY company_id) scba_max on scba_max.company_id = hc.source_key
    LEFT JOIN spfm_company_basic scb ON scb.company_id = scba_max.company_id AND scba_max.version_number = scb.version_number
    LEFT JOIN spfm_company_basic_tl scbl ON scb.company_basic_id = scbl.company_basic_id AND scbl.lang = #{lang}
    where hc.company_id = #{companyId}
</select>

• sslm_supplier_* 与 hpfm_company

<select id="listSupplierBasicBySourceKey" resultType="org.srm.supplier.domain.entity.SupplierBasic">
    SELECT
        ssb.supplier_basic_id,
        ssb.tenant_id,
        ssb.supplier_company_id,
        ssb.supplier_tenant_id,
        ssb.company_name,
        ssb.company_num,
        ssb.group_id,
        ssb.domestic_foreign_relation,
        ssb.unified_social_code
    FROM
        sslm_supplier_basic ssb
    INNER JOIN spfm_partner sp ON ssb.supplier_basic_id = sp.supplier_basic_id
    INNER JOIN hpfm_company hc ON sp.partner_company_id = hc.company_id
    WHERE sp.tenant_id = #{tenantId}
    AND hc.company_id = #{companyId}
</select>

• 合并空行

select
		GROUP_CONCAT(production_line_uom_id ORDER BY production_line_uom_id) AS production_line_uom_id,
		GROUP_CONCAT(production_line_uom_code ORDER BY production_line_uom_code) AS production_line_uom_code,
		GROUP_CONCAT(production_line_uom_name ORDER BY production_line_uom_name) AS production_line_uom_name,
		GROUP_CONCAT(devices_number_uom_id ORDER BY devices_number_uom_id) AS devices_number_uom_id,
		GROUP_CONCAT(devices_number_uom_code ORDER BY devices_number_uom_code) AS devices_number_uom_code,
		GROUP_CONCAT(devices_number_uom_name ORDER BY devices_number_uom_name) AS devices_number_uom_name
from (select
		CASE WHEN uom_code = 'TI' THEN uom_id END production_line_uom_id,
		CASE WHEN uom_code = 'TI' THEN uom_code END production_line_uom_code,
		CASE WHEN uom_code = 'TI' THEN uom_name END production_line_uom_name,
		CASE WHEN uom_code = 'SE' THEN uom_id END devices_number_uom_id,
		CASE WHEN uom_code = 'SE' THEN uom_code END devices_number_uom_code,
		CASE WHEN uom_code = 'SE' THEN uom_name END devices_number_uom_name
from srm.smdm_uom where uom_code  IN ('TI','SE') AND tenant_id = 42 ) tmp

• 查看建表语句

# 拼接SHOW CREATE TABLE
SELECT CONCAT('SHOW CREATE TABLE ', table_name, ';') FROM information_schema.tables
WHERE table_schema = 'srm_scux_yinpai' AND table_name like 'spuc_yp_capacity%'

#展示建表语句
SHOW CREATE TABLE spuc_yp_capacity_project;

• 员工 岗位 部门

SELECT
	he.employee_num "key",
	he.employee_num "code",
	he.NAME "name",
	'EMPLOYEE' dimension,
	hp.position_name,
	hu.unit_name,
	he.STATUS 
FROM
	srm.hpfm_employee he
	JOIN srm.hpfm_employee_assign heap ON he.employee_id = heap.employee_id
	LEFT JOIN srm.hpfm_position_tl hp ON heap.position_id = hp.position_id 
	AND hp.lang = 'zh_CN'
	LEFT JOIN srm.hpfm_unit_tl hu ON heap.unit_id = hu.unit_id 
	AND hu.lang = 'zh_CN' 
WHERE
	1 = 1 
	AND heap.tenant_id = 275 
	AND heap.position_id IN ( SELECT position_id FROM srm.hpfm_position WHERE position_code IN ( '50065778' ) ) 
	AND he.STATUS != 'LEAVE' 
	AND he.enabled_flag = 1