mysql-sql

26 Nov 2019

分组查询某每个城市年龄最大的人。

select t.* from (select * from table_a  order by t.age desc ) t group by t.city_id;

mysql 命令行导入数据

load data infile 'C:/Users/Administrator/Desktop/cgm.csv' into table cgm_copy fields terminated by ',' 
optionally enclosed by '"' escaped by '"'   lines terminated by '\r\n';

上述命令向cgm_copy导入数据，csv字段以逗号分隔，行以\r\n标识，enclosed by 说明字段以冒号包围，optionally表示只有字符串类型以冒号 包围，如果没有optionally，所有字段以冒号包围，escaped-by表示转义。

mysql存储过程

游标循环

DELIMITER $$

DROP PROCEDURE IF EXISTS `init_label`$$

CREATE PROCEDURE `init_label`()
BEGIN
    -- 定义变量
	DECLARE v_dept_id bigint unsigned;
	declare done boolean; 
	declare dept_id_cursor cursor for select dept_id from sys_dept where parent_id=100;
	declare continue handler for not found set done=true;
	open dept_id_cursor; 
	read_loop : LOOP
		fetch dept_id_cursor into v_dept_id;
		
		IF done THEN
		    LEAVE read_loop;
		END IF;
		INSERT INTO `sys_label_config` ( `dept_id`, `label_name`, `create_by`, `create_time`, `update_by`, `update_time`) 
		VALUES 
		( v_dept_id, '疾病类型',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '高血压类',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '正常血压',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '正常高值',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '高血压',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '1级高血压',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '2级高血压',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '3级高血压',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '糖尿病类',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '1型糖尿病',  'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '2型糖尿病',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '妊娠期糖尿病',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '特殊类型糖尿病',  'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '心血管类疾病',   'admin', NOW(), 'admin', NOW()),
		( v_dept_id, '其他',   'admin', NOW(), 'admin', NOW());
    end LOOP read_loop;
    close dept_id_cursor;
END$$

DELIMITER ;

mysql 函数

获取医院id

DELIMITER $$

DROP FUNCTION IF EXISTS `hesiclouddev`.`getHospitalId`$$

CREATE FUNCTION `getHospitalId`(deptId bigint(20)) RETURNS bigint(20)
BEGIN   
DECLARE fid bigint(20) default 0;   
DECLARE hospitalId bigint(20) default 0;  
 
loop_label: LOOP
	IF deptId is null THEN
		LEAVE  loop_label;  #终止循环
	END IF;
	SET fid =(SELECT parent_id FROM sys_dept WHERE dept_id = deptId);
	IF fid =0 THEN   
		LEAVE  loop_label;  #终止循环
	ELSEIF  fid=100  THEN   
		SET hospitalId = deptId; 
                LEAVE  loop_label;
	ELSE
		SET deptId = fid;   
	END IF;  
        
END LOOP; 
return hospitalId;  
END$$

DELIMITER ;

递归获取省市区全名

DELIMITER $$

DROP FUNCTION IF EXISTS `getPcaFullNameByPcaId`$$

CREATE FUNCTION `getPcaFullNameByPcaId`(pcaId int(11)) RETURNS varchar(1000) 
BEGIN   
DECLARE v_pid int(11);   
DECLARE v_pca_name varchar(100);
DECLARE r_str varchar(1000) default '';   
  
WHILE pcaId is not null  do   
	SELECT parent_id,pca_name into v_pid,v_pca_name FROM pca WHERE pca_id = pcaId; 
    IF v_pca_name is not null THEN
        SET r_str = concat(v_pca_name, '', r_str);   
	END IF;   
	SET pcaId = v_pid;
END WHILE;   
return r_str;  
END$$

DELIMITER ;

mysql 获取时间戳和时间转换

select unix_timestamp(now());  #当前时间
select unix_timestamp(CURDATE()); #当天
select from_unixtime(1388776825); #时间戳转换为时间
SELECT  FROM_UNIXTIME( 1388776825, '%Y年%m月%d' ) #结果：2014年1月4

用户使用协议,只保留create_time最大的一条。

delete from mechanical.t_agreement where create_time not in 
(select c.create_time from (select max(b.create_time) create_time from mechanical.t_agreement b) c);