幾個測試SQL,測試SQL處理字串

來源:互聯網
上載者:User
字串
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;


 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。