mysql操作笔记

批量插入

DELIMITER $$

USE `diaoyoutianxia`$$

DROP PROCEDURE IF EXISTS `p_make_data`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_make_data`()
BEGIN
    DECLARE i INT ;
		set i=0;
    WHILE i<l000 DO
        insert into `33hao_member_common` (`member_id`) values('');
        SET i = i + 1;
    END WHILE;
    END$$

DELIMITER ;

两个表连查 表A 表B  a.member_id  =  b.member_id  但是有可能 B表的数据有多个，但只取其中一条

SELECT DISTINCT 33hao_bbs_child.id as new_id,33hao_bbs_child.*,33hao_bbs_parents.parents_name,33hao_bbs_parents.parents_phone FROM 33hao_bbs_child LEFT JOIN 33hao_bbs_parents ON 33hao_bbs_child.member_id = 33hao_bbs_parents.member_id  GROUP BY new_id HAVING count(*)>0

统计相应的个数

SELECT count(*) FROM (SELECT DISTINCT 33hao_bbs_child.id as new_id,33hao_bbs_child.*,33hao_bbs_parents.parents_name,33hao_bbs_parents.parents_phone FROM 33hao_bbs_child LEFT JOIN 33hao_bbs_parents ON 33hao_bbs_child.member_id = 33hao_bbs_parents.member_id  GROUP BY new_id HAVING count(*)>0) aa