MSSQL编程：几个测试SQL，测试SQL处置字符串

我们分析上面的两个操作，都有一个“删除数据”的过程。字符串
droptableifexistscategory;
createtableifnotexistscategory
(
c_Idbigintnotnull,
c_namevarchar(255)default,
c_typeintdefault1,
primarykey(c_ID)
);

droptableifexistsfiles;
createtableifnotexistsfiles
(
f_Idbigintnotnull,
c_idbigintnotnull,
f_namevarchar(255)default,
f_midstext,
primarykey(f_ID)
);

droptableifexistsmembers;
createtableifnotexistsmembers
(
m_Idbigintnotnull,
m_namevarchar(255)default,
primarykey(m_ID)
);

insertintocategory(c_id,c_name,c_type)values(1,public,1);
insertintocategory(c_id,c_name,c_type)values(2,private,2);
insertintocategory(c_id,c_name,c_type)values(3,upload,3);
insertintocategory(c_id,c_name,c_type)values(4,member001,4);
insertintocategory(c_id,c_name,c_type)values(5,member002,4);

insertintofiles(f_id,c_id,f_name,f_mids)values(1,1,F_public,1,2);
insertintofiles(f_id,c_id,f_name,f_mids)values(2,1,F_public,1);
insertintofiles(f_id,c_id,f_name,f_mids)values(3,1,F_public,3,4);

insertintofiles(f_id,c_id,f_name,f_mids)values(4,2,F_private,1,2);
insertintofiles(f_id,c_id,f_name,f_mids)values(5,2,F_private,1);
insertintofiles(f_id,c_id,f_name,f_mids)values(6,2,F_private,3,4);


insertintofiles(f_id,c_id,f_name,f_mids)values(7,3,F_upload,1,2);
insertintofiles(f_id,c_id,f_name,f_mids)values(8,3,F_upload,1);
insertintofiles(f_id,c_id,f_name,f_mids)values(9,3,F_upload,3,4);

insertintofiles(f_id,c_id,f_name,f_mids)values(10,4,F_upload,1,2);
insertintofiles(f_id,c_id,f_name,f_mids)values(11,4,F_upload,1);
insertintofiles(f_id,c_id,f_name,f_mids)values(12,4,F_upload,3,4);

insertintofiles(f_id,c_id,f_name,f_mids)values(13,5,F_upload,1,2);
insertintofiles(f_id,c_id,f_name,f_mids)values(14,5,F_upload,1);
insertintofiles(f_id,c_id,f_name,f_mids)values(15,5,F_upload,3,4);

#此SQL数据就为多目次及其目次上面的文件列表
select*fromcategoryasA,filesasB,membersasCWhereA.c_id=B.c_idorderbyB.c_type,B.c_id;

insertintomembers(m_id,m_name)values(1,A);
insertintomembers(m_id,m_name)values(2,B);
insertintomembers(m_id,m_name)values(3,C);
insertintomembers(m_id,m_name)values(4,D);

SELECT*FROMmembers;

#---获得A(id=1)会员有权限的文件列表
#INSTR(concat(,,f_mids,,),,1,)>0暗示此文件联系关系的Member字段内里存在此ID，
#即暗示会员ID为1会员能够检察此文件

SELECTLOCATE(,1,,,1,2,3,);
Selectf_id,f_name,f_mids,
INSTR(concat(,,f_mids,,),,1,)ASchecked
Fromfiles
whereINSTR(concat(,,f_mids,,),,1,)>0;



支持多线程，充分利用CPU资源