使用 SQLite實作『LEFT JOIN』+『WHERE』+『模糊查詢LIKE』(查詢適用於MySQL)

資料來源:http://jashliao.pixnet.net/blog

 

建立資料表:

create table A (uid INTEGER PRIMARY KEY,Name TEXT,Email TEXT);
create table B (uid INTEGER PRIMARY KEY,Name TEXT,Address TEXT);
———–

新增資料:

insert into A (Name,Email) values(‘jash’,’jash@aa.com’);
insert into A (Name,Email) values(‘jash.liao’,’jash.liao@aa.com’);
insert into A (Name,Email) values(‘kang’,’kang@aa.com’);
insert into A (Name,Email) values(‘yyy’,’yyy@aa.com’);

insert into B (Name,Address) values(‘jash’,’407台中市西屯區文華路100號’);
insert into B (Name,Address) values(‘jash.liao’,’402台中市南區興大路145號’);
insert into B (Name,Address) values(‘kang’,’402台中市南區興大路145號’);
————

查詢應用:

SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%jash%’;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%lia%’;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%a%’;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%ka%’;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%y%’;
SELECT A.Name, A.Email, B.Address FROM A LEFT JOIN B ON A.Name=B.Name WHERE A.Name  LIKE ‘%gy%’;