[mysql] group_concat


::record
category_id , people_id
16             11
16             12
16             13
17             11
18             12

SELECT DISTINCT category_id, GROUP_CONCAT( DISTINCT people_id) from pictures p GROUP by p.category_id ORDER by category_id asc


:: result

category_id , people_id
16             11,12,13
17             11
18             12

SQL VIEW, TRIGGER, PROCEDURE

/** view */

select `ol`.`id` AS `id`,`ol`.`order_id` AS `order_id`,`ol`.`member_id` AS `member_id`,`ol`.`product_id` AS `product_id`,`p`.`name` AS `product_name`,`o`.`order_date` AS `order_date`from  `order_line` `ol`  join `order` `o`  on `ol`.`order_id` = `o`.`id` join `product` `p`  on `p`.`id` = `ol`.`product_id`

/** trigger*/
CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,b4 INT DEFAULT 0);

DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1FOR EACH ROW BEGININSERT INTO test2 SET a2 = NEW.a1;DELETE FROM test3 WHERE a3 = NEW.a1;UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;END;|
DELIMITER ;

 

/** procedure*/

create table user (userid int auto_increment primary key, fname varchar(10), lname varchar(10));create table users (userid int primary key, fname varchar(10), lname varchar(10));
delimiter |create procedure copy_row(uid int)beginINSERT INTO users (userid, fname, lname)SELECT userid, fname, lname from user WHERE userid = uid;end;|delimiter ;
mysql> insert into user (fname, lname) values (‘joe’, ‘smith’), (‘steve’, ‘brown’),(‘jon’, ‘haddad’);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> call copy_row(1);Query OK, 1 row affected (0.01 sec)
mysql> select * from user;