[SQL] 데이터 조회

Junghyeon Song·2022년 7월 20일

sql sqlite3

SQL

목록 보기

1/2

주피터 노트북 파일을 마크다운으로 변환하여 작성한 글입니다.
코드는 깃헙에 정리해두었습니다!
https://github.com/pirate-turtle/SQL

데이터 조회

데이터 조회 시 유용한 문법에 대해 알아봅니다.

SELECT
DISTINCT
ORDER BY
WHERE
- BETWEEN
- IN
- LIKE

사용한 데이터 정보

https://www.sqlitetutorial.net/sqlite-sample-database/

시작하기 전 실행하기!

import sqlite3
from prettytable import from_db_cursor

# SQLite 데이터베이스에 연결하기 위해 connect 생성
con = sqlite3.connect('./database/chinook.db')
con.row_factory = sqlite3.Row

# cursor 생성
cur = con.cursor()

SELECT

원하는 데이터를 조회할 때 사용합니다.

※ SQL은 대소문자를 구분하지 않습니다. 명령문을 대문자, 그 외는 소문자로 입력하면 가독성이 좋아집니다.

# 한번에 모든 컬럼 조회
# SELECT * FROM table_name;
cur.execute("""
    SELECT *
    FROM customers
    """)

# 데이터 양이 많으니 상위 10개만 일단 보기
from_db_cursor(cur)[:10]

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
3	François	Tremblay	None	1498 rue Bélanger	Montréal	QC	Canada	H2G 1A7	+1 (514) 721-4711	None	ftremblay@gmail.com	3
4	Bjørn	Hansen	None	Ullevålsveien 14	Oslo	None	Norway	0171	+47 22 44 22 22	None	bjorn.hansen@yahoo.no	4
5	František	Wichterlová	JetBrains s.r.o.	Klanova 9/506	Prague	None	Czech Republic	14700	+420 2 4172 5555	+420 2 4172 5555	frantisekw@jetbrains.com	4
6	Helena	Holý	None	Rilská 3174/6	Prague	None	Czech Republic	14300	+420 2 4177 0449	None	hholy@gmail.com	5
7	Astrid	Gruber	None	Rotenturmstraße 4, 1010 Innere Stadt	Vienne	None	Austria	1010	+43 01 5134505	None	astrid.gruber@apple.at	5
8	Daan	Peeters	None	Grétrystraat 63	Brussels	None	Belgium	1000	+32 02 219 03 03	None	daan_peeters@apple.be	4
9	Kara	Nielsen	None	Sønder Boulevard 51	Copenhagen	None	Denmark	1720	+453 3331 9991	None	kara.nielsen@jubii.dk	4
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4

# 원하는 컬럼만 지정해서 조회 가능
# SELECT column1, column2 FROM table_name;
cur.execute("""
    SELECT customerid, firstname, city, state
    FROM customers
    """)

from_db_cursor(cur)[:10]

CustomerId	FirstName	City	State
1	Luís	São José dos Campos	SP
2	Leonie	Stuttgart	None
3	François	Montréal	QC
4	Bjørn	Oslo	None
5	František	Prague	None
6	Helena	Prague	None
7	Astrid	Vienne	None
8	Daan	Brussels	None
9	Kara	Copenhagen	None
10	Eduardo	São Paulo	SP

# 간단한 연산 결과를 얻을 수도 있다
cur.execute("SELECT 1+1")

from_db_cursor(cur)

1+1
2

# 여러개도 가능하다!
cur.execute("SELECT 1+1, 2*5")

from_db_cursor(cur)

1+1	2*5
2	10

# 컬럼끼리 연산하는것도 가능함
cur.execute("""
    SELECT customerid + supportrepid, firstname, city, state
    FROM customers
    """)

from_db_cursor(cur)[:10]

customerid + supportrepid	FirstName	City	State
4	Luís	São José dos Campos	SP
7	Leonie	Stuttgart	None
6	François	Montréal	QC
8	Bjørn	Oslo	None
9	František	Prague	None
11	Helena	Prague	None
12	Astrid	Vienne	None
12	Daan	Brussels	None
13	Kara	Copenhagen	None
14	Eduardo	São Paulo	SP

LIMIT

쿼리 결과를 원하는 행 수 만큼만 받도록 합니다.

cur.execute("""
    SELECT *
    FROM customers
    LIMIT 5
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
3	François	Tremblay	None	1498 rue Bélanger	Montréal	QC	Canada	H2G 1A7	+1 (514) 721-4711	None	ftremblay@gmail.com	3
4	Bjørn	Hansen	None	Ullevålsveien 14	Oslo	None	Norway	0171	+47 22 44 22 22	None	bjorn.hansen@yahoo.no	4
5	František	Wichterlová	JetBrains s.r.o.	Klanova 9/506	Prague	None	Czech Republic	14700	+420 2 4172 5555	+420 2 4172 5555	frantisekw@jetbrains.com	4

OFFSET 키워드로 행을 얼마만큼 건너뛸것인지 지정할 수 있습니다.

# offset을 n으로 지정하면 n행 건너뛰고 n+1행부터 리턴
cur.execute("""
    SELECT *
    FROM customers
    LIMIT 5 OFFSET 10
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4
14	Mark	Philips	Telus	8210 111 ST NW	Edmonton	AB	Canada	T6G 2C7	+1 (780) 434-4554	+1 (780) 434-5565	mphilips12@shaw.ca	5
15	Jennifer	Peterson	Rogers Canada	700 W Pender Street	Vancouver	BC	Canada	V6C 1G8	+1 (604) 688-2255	+1 (604) 688-8756	jenniferp@rogers.ca	3

OFFSET 키워드를 사용하지 않고 지정할 수도 있습니다.

# 앞에 오는 숫자가 offset, 뒤에 오는 숫자가 limit
cur.execute("""
    SELECT *
    FROM customers
    LIMIT 10, 5
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4
14	Mark	Philips	Telus	8210 111 ST NW	Edmonton	AB	Canada	T6G 2C7	+1 (780) 434-4554	+1 (780) 434-5565	mphilips12@shaw.ca	5
15	Jennifer	Peterson	Rogers Canada	700 W Pender Street	Vancouver	BC	Canada	V6C 1G8	+1 (604) 688-2255	+1 (604) 688-8756	jenniferp@rogers.ca	3

연결 연산자 (||)

여러 컬럼의 값 또는 문자열을 합쳐 하나의 컬럼으로 출력합니다.
문자열은 작은 따옴표(')로 감싸서 입력해야 합니다.

cur.execute("""
    SELECT firstname||' '||lastname
    FROM customers
    LIMIT 10
    """)

from_db_cursor(cur)

firstname\|\|' '\|\|lastname
Luís Gonçalves
Leonie Köhler
François Tremblay
Bjørn Hansen
František Wichterlová
Helena Holý
Astrid Gruber
Daan Peeters
Kara Nielsen
Eduardo Martins

cur.execute("""
    SELECT customerid, 'My name is '||firstname||' '||lastname
    FROM customers
    LIMIT 10
    """)

from_db_cursor(cur)

CustomerId	'My name is '\|\|firstname\|\|' '\|\|lastname
1	My name is Luís Gonçalves
2	My name is Leonie Köhler
3	My name is François Tremblay
4	My name is Bjørn Hansen
5	My name is František Wichterlová
6	My name is Helena Holý
7	My name is Astrid Gruber
8	My name is Daan Peeters
9	My name is Kara Nielsen
10	My name is Eduardo Martins

AS

컬럼 및 테이블에 임시로 별명을 지어줄 수 있습니다.

# SELECT column1 AS nickname FROM tablename
cur.execute("""
    SELECT customerid, firstname||' '||lastname AS Name
    FROM customers
    LIMIT 10
    """)

from_db_cursor(cur)

CustomerId	Name
1	Luís Gonçalves
2	Leonie Köhler
3	François Tremblay
4	Bjørn Hansen
5	František Wichterlová
6	Helena Holý
7	Astrid Gruber
8	Daan Peeters
9	Kara Nielsen
10	Eduardo Martins

# 별명에 공백, 특수문자 등을 포함하고 싶은 경우 작은따옴표로 감싸서 문자열 형태로 만들어야 함
cur.execute("""
    SELECT customerid, firstname||' '||lastname AS 'My Name☆'
    FROM customers
    LIMIT 10
    """)

from_db_cursor(cur)

CustomerId	My Name☆
1	Luís Gonçalves
2	Leonie Köhler
3	François Tremblay
4	Bjørn Hansen
5	František Wichterlová
6	Helena Holý
7	Astrid Gruber
8	Daan Peeters
9	Kara Nielsen
10	Eduardo Martins

DISTINCT

중복된 데이터를 제외하고 유니크한 값만 보여줍니다

# 원하는 컬럼 앞에 DISTINCT 입력
# SELECT DISTINCT column1, column2 FROM table_name;
cur.execute("""
    SELECT DISTINCT country
    FROM customers
    LIMIT 10
    """)

from_db_cursor(cur)

Country
Brazil
Germany
Canada
Norway
Czech Republic
Austria
Belgium
Denmark
USA
Portugal

ORDER BY

특정 컬럼을 기준으로 데이터를 정렬할 수 있습니다.

# SELECT * FROM table_name ORDER BY column1
# LIMIT은 ORDER BY 다음에 작성
cur.execute("""
    SELECT *
    FROM customers
    ORDER BY country
    LIMIT 10
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
56	Diego	Gutiérrez	None	307 Macacha Güemes	Buenos Aires	None	Argentina	1106	+54 (0)11 4311 4333	None	diego.gutierrez@yahoo.ar	4
55	Mark	Taylor	None	421 Bourke Street	Sidney	NSW	Australia	2010	+61 (02) 9332 3633	None	mark.taylor@yahoo.au	4
7	Astrid	Gruber	None	Rotenturmstraße 4, 1010 Innere Stadt	Vienne	None	Austria	1010	+43 01 5134505	None	astrid.gruber@apple.at	5
8	Daan	Peeters	None	Grétrystraat 63	Brussels	None	Belgium	1000	+32 02 219 03 03	None	daan_peeters@apple.be	4
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4
3	François	Tremblay	None	1498 rue Bélanger	Montréal	QC	Canada	H2G 1A7	+1 (514) 721-4711	None	ftremblay@gmail.com	3

# 2개 이상의 컬럼을 정렬 기준으로 넣을 수도 있음
cur.execute("""
    SELECT DISTINCT country, city
    FROM customers
    ORDER BY country, city
    LIMIT 15
    """)

from_db_cursor(cur)

Country	City
Argentina	Buenos Aires
Australia	Sidney
Austria	Vienne
Belgium	Brussels
Brazil	Brasília
Brazil	Rio de Janeiro
Brazil	São José dos Campos
Brazil	São Paulo
Canada	Edmonton
Canada	Halifax
Canada	Montréal
Canada	Ottawa
Canada	Toronto
Canada	Vancouver
Canada	Winnipeg

컬럼별로 정렬 순서를 지정할 수 있습니다.

ASC: 오름차순 (따로 입력하지 않으면 default로 오름차순)
DESC: 내림차순

# 컬럼 뒤에 정렬 순서 입력하기 (ASC는 생략 가능)
cur.execute("""
    SELECT DISTINCT country, city
    FROM customers
    ORDER BY country ASC, city DESC
    LIMIT 15
    """)

from_db_cursor(cur)

Country	City
Argentina	Buenos Aires
Australia	Sidney
Austria	Vienne
Belgium	Brussels
Brazil	São Paulo
Brazil	São José dos Campos
Brazil	Rio de Janeiro
Brazil	Brasília
Canada	Yellowknife
Canada	Winnipeg
Canada	Vancouver
Canada	Toronto
Canada	Ottawa
Canada	Montréal
Canada	Halifax

WHERE

조건에 맞는 데이터만 가져올 수 있습니다.
연산자를 이용하여 조건문을 작성할 수 있습니다.

비교 연산자

연산자	의미
=	일치하는 경우
<> !=	일치하지 않는 경우
<	해당 값 미만인 경우
<=	해당 값 이하인 경우
>	해당 값 초과인 경우
>=	해당 값 이상인 경우

cur.execute("""
    SELECT *
    FROM customers
    WHERE customerid <= 5
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
3	François	Tremblay	None	1498 rue Bélanger	Montréal	QC	Canada	H2G 1A7	+1 (514) 721-4711	None	ftremblay@gmail.com	3
4	Bjørn	Hansen	None	Ullevålsveien 14	Oslo	None	Norway	0171	+47 22 44 22 22	None	bjorn.hansen@yahoo.no	4
5	František	Wichterlová	JetBrains s.r.o.	Klanova 9/506	Prague	None	Czech Republic	14700	+420 2 4172 5555	+420 2 4172 5555	frantisekw@jetbrains.com	4

cur.execute("""
    SELECT *
    FROM customers
    WHERE state='SP'
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5

BETWEEN

지정한 범위 내의 값이면 참입니다.

# BETWEEN a AND b -> a~b 범위 안이면 참
cur.execute("""
    SELECT *
    FROM customers
    WHERE customerid BETWEEN 15 AND 20
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
15	Jennifer	Peterson	Rogers Canada	700 W Pender Street	Vancouver	BC	Canada	V6C 1G8	+1 (604) 688-2255	+1 (604) 688-8756	jenniferp@rogers.ca	3
16	Frank	Harris	Google Inc.	1600 Amphitheatre Parkway	Mountain View	CA	USA	94043-1351	+1 (650) 253-0000	+1 (650) 253-0000	fharris@google.com	4
17	Jack	Smith	Microsoft Corporation	1 Microsoft Way	Redmond	WA	USA	98052-8300	+1 (425) 882-8080	+1 (425) 882-8081	jacksmith@microsoft.com	5
18	Michelle	Brooks	None	627 Broadway	New York	NY	USA	10012-2612	+1 (212) 221-3546	+1 (212) 221-4679	michelleb@aol.com	3
19	Tim	Goyer	Apple Inc.	1 Infinite Loop	Cupertino	CA	USA	95014	+1 (408) 996-1010	+1 (408) 996-1011	tgoyer@apple.com	3
20	Dan	Miller	None	541 Del Medio Avenue	Mountain View	CA	USA	94040-111	+1 (650) 644-3358	None	dmiller@comcast.com	4

IN

지정한 목록에 있는 값 중 하나와 일치하면 참입니다.

cur.execute("""
    SELECT *
    FROM customers
    WHERE customerid IN (1, 5, 10)
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
5	František	Wichterlová	JetBrains s.r.o.	Klanova 9/506	Prague	None	Czech Republic	14700	+420 2 4172 5555	+420 2 4172 5555	frantisekw@jetbrains.com	4
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4

LIKE

패턴과 일치하는 경우 참입니다.
%와 를 이용하여 패턴을 지정할 수 있습니다.
%와 는 모든 문자와 매칭되며, %는 문자열, _는 문자 하나를 의미합니다.

# 전화번호가 +55로 시작하는 경우
cur.execute("""
    SELECT *
    FROM customers
    WHERE phone LIKE '+55%'
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4

# 전화번호에 55를 포함하는 경우
cur.execute("""
    SELECT *
    FROM customers
    WHERE phone LIKE '%55%'
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
5	František	Wichterlová	JetBrains s.r.o.	Klanova 9/506	Prague	None	Czech Republic	14700	+420 2 4172 5555	+420 2 4172 5555	frantisekw@jetbrains.com	4
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4
14	Mark	Philips	Telus	8210 111 ST NW	Edmonton	AB	Canada	T6G 2C7	+1 (780) 434-4554	+1 (780) 434-5565	mphilips12@shaw.ca	5
15	Jennifer	Peterson	Rogers Canada	700 W Pender Street	Vancouver	BC	Canada	V6C 1G8	+1 (604) 688-2255	+1 (604) 688-8756	jenniferp@rogers.ca	3
36	Hannah	Schneider	None	Tauentzienstraße 8	Berlin	None	Germany	10789	+49 030 26550280	None	hannah.schneider@yahoo.de	5

# state가 B로 끝나는 2글자인 경우
cur.execute("""
    SELECT *
    FROM customers
    WHERE state LIKE '_B'
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
14	Mark	Philips	Telus	8210 111 ST NW	Edmonton	AB	Canada	T6G 2C7	+1 (780) 434-4554	+1 (780) 434-5565	mphilips12@shaw.ca	5
32	Aaron	Mitchell	None	696 Osborne Street	Winnipeg	MB	Canada	R3L 2B9	+1 (204) 452-6452	None	aaronmitchell@yahoo.ca	4

# 성이 on으로 끝나는 문자열
cur.execute("""
    SELECT *
    FROM customers
    WHERE lastname LIKE '%on'
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
15	Jennifer	Peterson	Rogers Canada	700 W Pender Street	Vancouver	BC	Canada	V6C 1G8	+1 (604) 688-2255	+1 (604) 688-8756	jenniferp@rogers.ca	3
23	John	Gordon	None	69 Salem Street	Boston	MA	USA	2113	+1 (617) 522-1333	None	johngordon22@yahoo.com	4
24	Frank	Ralston	None	162 E Superior Street	Chicago	IL	USA	60611	+1 (312) 332-3232	None	fralston@gmail.com	3
51	Joakim	Johansson	None	Celsiusg. 9	Stockholm	None	Sweden	11230	+46 08-651 52 52	None	joakim.johansson@yahoo.se	5

# email 도메인이 2글자인 경우 (. 뒤에 2글자인 경우)
cur.execute("""
    SELECT *
    FROM customers
    WHERE email LIKE '%.__'
    LIMIT 10
    """)

from_db_cursor(cur)

CustomerId	FirstName	LastName	Company	Address	City	State	Country	PostalCode	Phone	Fax	Email	SupportRepId
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
4	Bjørn	Hansen	None	Ullevålsveien 14	Oslo	None	Norway	0171	+47 22 44 22 22	None	bjorn.hansen@yahoo.no	4
7	Astrid	Gruber	None	Rotenturmstraße 4, 1010 Innere Stadt	Vienne	None	Austria	1010	+43 01 5134505	None	astrid.gruber@apple.at	5
8	Daan	Peeters	None	Grétrystraat 63	Brussels	None	Belgium	1000	+32 02 219 03 03	None	daan_peeters@apple.be	4
9	Kara	Nielsen	None	Sønder Boulevard 51	Copenhagen	None	Denmark	1720	+453 3331 9991	None	kara.nielsen@jubii.dk	4
10	Eduardo	Martins	Woodstock Discos	Rua Dr. Falcão Filho, 155	São Paulo	SP	Brazil	01007-010	+55 (11) 3033-5446	+55 (11) 3033-4564	eduardo@woodstock.com.br	4
11	Alexandre	Rocha	Banco do Brasil S.A.	Av. Paulista, 2022	São Paulo	SP	Brazil	01310-200	+55 (11) 3055-3278	+55 (11) 3055-8131	alero@uol.com.br	5
12	Roberto	Almeida	Riotur	Praça Pio X, 119	Rio de Janeiro	RJ	Brazil	20040-020	+55 (21) 2271-7000	+55 (21) 2271-7070	roberto.almeida@riotur.gov.br	3
13	Fernanda	Ramos	None	Qe 7 Bloco G	Brasília	DF	Brazil	71020-677	+55 (61) 3363-5547	+55 (61) 3363-7855	fernadaramos4@uol.com.br	4