mysql学习

欢迎来到handsomecui的博客空间

mysql学习主要按照需求来写吧；

需求一：从一张表中关联另外一张表，将id拼串放在另外一张表：

insert into thor_sale_extend(id,business_ids,region_ids)
SELECT s.id,concat(CONCAT(",",GROUP_CONCAT(bus_id)),",")as business_ids,concat(CONCAT(",",GROUP_CONCAT(reg_id)),",")as region_ids
from thor_sale s
left join thor_sal_bus b
on
s.id=b.sal_id
left join thor_sal_reg r
ON
s.id=r.sal_id
GROUP BY s.id

需求二：

存储过程，批量造数据：

BEGIN
    DECLARE i int;
    DECLARE a varchar(100);
    SET i = 5;
    SET a='双十一科技';
    WHILE i<10000 DO
        SET i = i+1;    
        insert into thor_custom(stage, company1, create_time1, update_time, user_id)
        values(i%8 + 3, concat(a,i), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), "55b1da780cf29498f9d2dc0a");
    END    WHILE;
END