几个测试SQL,测试SQL处理字符串_MySQL数据库_黑客防线网安服务器维护基地--Powered by WWW.RONGSEN.COM.CN

几个测试SQL,测试SQL处理字符串

作者:黑客防线网安MYSQL维护基地 来源:黑客防线网安MYSQL维护基地 浏览次数:0

本篇关键词:测试字符串处理
黑客防线网安网讯: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 tabl...

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 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)会员有权限的文件列表
#INSTR(concat(',',f_mids ,','),',1,') >0 表示此文件关联的Member字段里面存在此ID
#即表示会员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;

    黑客防线网安服务器维护方案本篇连接:http://www.rongsen.com.cn/show-8402-1.html
网站维护教程更新时间:2012-03-20 04:55:28  【打印此页】  【关闭
我要申请本站N点 | 黑客防线官网 |  
专业服务器维护及网站维护手工安全搭建环境,网站安全加固服务。黑客防线网安服务器维护基地招商进行中!QQ:29769479

footer  footer  footer  footer