Drop Table if ixists 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)); 0000-00-00 DEFAULT', C_TYPE INT DEFAULT 1, PRIMARY Key (C_ID));
Drop Table if ixists files; create Table if not existsses Files (f_id bigint not null, c_id bigint not null, f_name varchar (255) default ', f_mids text, primary key (f_id)); 0000-00-00 DEFAULT', F_MIDS TEXT, PRIMARY KEY (F_ID));
Drop Table if ixistics mens; create table if not exists, 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');
# This SQL data is a multi-directory and its directory list 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 MEMBERS (M_ID, M_NAME) VALUES (1, 'A'); INSERT INTO MEMBERS (M_ID, M_NAME) VALUES (2, 'B'); Insert Into Members (m_id, m_name) Values (3, 'c') Insert Into Members (m_id, m_name) VALUES (4, 'D');
Select * from members;
# --- A (ID = 1) member has the file list #instr (constru ", ',', f_mids, ','), ', 1,')> 0 indicates that this file is present in the Member field associated with this file. This ID, # 即 means that the member ID is 1 member to view this file
Select Locate (', 1,', ', 1, 2, 3,'); SELECT F_ID, F_NAME, F_MIDS, INSTR (Concat (',', f_mids, ','), ', 1,') as checkted From Files Where INSTR (Concat (',', f_mids, ','), ', 1,')> 0;
