Several test SQL, test SQL processing string

String
drop table if exists category;
CREATE table if not EXISTS category
(
C_ID bigint NOT NULL,
C_name varchar (255) Default ',
C_type int default 1,
Primary KEY (C_ID)
);

drop table if exists files;
CREATE table if not EXISTS files
(
f_id bigint NOT NULL,
C_ID bigint NOT NULL,
F_name varchar (255) Default ',
F_mids text,
Primary KEY (F_ID)
);

Drop table if exists members;
CREATE table if not EXISTS members
(
m_id bigint NOT NULL,
M_name varchar (255) Default ',
Primary KEY (M_ID)
);

INSERT into category (C_id,c_name,c_type) VALUES (1, ' Public ', 1);
INSERT into category (C_id,c_name,c_type) VALUES (2, ' private ', 2);
INSERT into category (C_id,c_name,c_type) VALUES (3, ' upload ', 3);
INSERT into category (C_id,c_name,c_type) VALUES (4, ' member001 ', 4);
INSERT into category (C_id,c_name,c_type) VALUES (5, ' member002 ', 4);

Insert into files (f_id,c_id,f_name,f_mids) values (1,1, ' f_public ', ' 1,2 ');
Insert into files (f_id,c_id,f_name,f_mids) values (2,1, ' f_public ', ' 1 ');
Insert into files (f_id,c_id,f_name,f_mids) values (3,1, ' f_public ', ' 3,4 ');

Insert into files (f_id,c_id,f_name,f_mids) values (4,2, ' f_private ', ' 1,2 ');
Insert into files (f_id,c_id,f_name,f_mids) values (5,2, ' f_private ', ' 1 ');
Insert into files (f_id,c_id,f_name,f_mids) values (6,2, ' f_private ', ' 3,4 ');


Insert into files (f_id,c_id,f_name,f_mids) values (7,3, ' f_upload ', ' 1,2 ');
Insert into files (f_id,c_id,f_name,f_mids) values (8,3, ' f_upload ', ' 1 ');
Insert into files (f_id,c_id,f_name,f_mids) values (9,3, ' f_upload ', ' 3,4 ');

Insert into files (f_id,c_id,f_name,f_mids) values (10,4, ' f_upload ', ' 1,2 ');
Insert into files (f_id,c_id,f_name,f_mids) values (11,4, ' f_upload ', ' 1 ');
Insert into files (f_id,c_id,f_name,f_mids) values (12,4, ' f_upload ', ' 3,4 ');

Insert into files (f_id,c_id,f_name,f_mids) values (13,5, ' f_upload ', ' 1,2 ');
Insert into files (f_id,c_id,f_name,f_mids) values (14,5, ' f_upload ', ' 1 ');
Insert into files (f_id,c_id,f_name,f_mids) values (15,5, ' f_upload ', ' 3,4 ');

#此SQL数据就为多目录及其目录下面的文件列表
SELECT * from category as a,files as b,members as C Where a.c_id=b.c_id order by b.c_type,b.c_id;

INSERT into the members (M_id,m_name) VALUES (1, ' A ');
INSERT into the members (M_id,m_name) VALUES (2, ' B ');
INSERT into the members (M_id,m_name) VALUES (3, ' C ');
INSERT into the members (M_id,m_name) VALUES (4, ' D ');

SELECT * from members;

#---Access to a (id=1) member's list of files
#INSTR (Concat (', ', F_mids, ', '), ', 1, ') >0 indicates that this ID exists in the member field associated with this file.
#即表示会员ID为1会员可以查看此文件

SELECT LOCATE (', 1, ', ', 1,2,3, ');
Select F_id,f_name,f_mids,
INSTR (Concat (', ', F_mids, ', '), ', 1, ') as checked
From files
where INSTR (concat (', ', F_mids, ', '), ', 1, ') >0;