Mysql的一些简单使用

1 查询某个字段在哪个表中

 SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='qktype';

2 查询所有的触发器

SELECT * FROM information_schema.`TRIGGERS`

示例1

BEGIN	
	IF new.sf_p_key IS NOT NULL THEN
		
		-- 	导入数据 新增system_user_post
		INSERT INTO xin_cooperop_mix.system_user_post 
		(system_user_id,userid,post,is_primary,state,department,position,businessline,user_type)
		SELECT 
			concat(sut.prix,CONVERT(RIGHT(concat(substr('0000000000', 1, sut.sub), new.id), sut.sub) USING utf8)) 
			as system_user_id,
			new.id as userid,new.post,1 as is_primary,1 as state,new.department,p.position,p.businessline
			,new.type as user_type 
		FROM system_post p
		LEFT JOIN system_user_type sut on sut.type=new.type
		WHERE  p.id=new.post;
		
		-- 导入数据  新增 xin_hr 的 hr_system_user_extend
		INSERT INTO xin_hr.hr_system_user_extend (userid,system_user_id)
		SELECT userid,system_user_id FROM system_user_post WHERE userid=new.id;
	

END IF;


END

示例2

BEGIN	
	IF new.user_dept IS NOT NULL THEN
		SET new.type = 'employee';
		SET new.department = 20  ;
		SET new.idcard_type = 1  ;
		SET new.idcard_no = '000000000000000000'  ;
		SET new.`password` = 'j6iftLjzt3bnwxO6cGaK0WwYMAck5vMjs'  ;
		SET new.state = 1  ;
		SET new.post = 64  ;
		SET new.user_state = -1 ;
END IF;

IF new.no IS NULL or new.no ='' THEN
		SET new.no = uuid() ;
END IF;

END