SELECT

1

SELECT 10 / 5, 2 * 4;

1

SELECT trackid, name FROM tracks;

1

SELECT * FROM tracks;

INSERT

1

INSERT INTO artists (name) VALUES('Bud Powell');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

script = """
INSERT INTO artists (name) VALUES ("?");
"""

data = [
    ("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd")
]

cur.executemany(script, data)

1
2
3
4
5
6
7

SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC;

UPDATE

1

UPDATE employees SET lastname = 'Smith' WHERE employeeid = 3;

1
2
3
4
5
6

UPDATE employees
SET city = 'Toronto', 
    state = 'ON',
    postalcode = 'M5P 2N7'
WHERE
    employeeid = 4;

1
2

UPDATE employees
SET email = UPPER(firstname || "." || lastname || "@corp.co.kr");

Sorting

1
2
3
4
5
6
7
8

SELECT 
    TrackId, 
    Name, 
    Composer 
FROM 
   tracks
ORDER BY 
   Composer;
  • NULL Data인 None은 SQLite3에서 가장 작은 값으로 인식

Filtering

filter

DISTINCT

1

SELECT DISTINCT city FROM customers;
  • NULL을 포함한 중복값을 하나만 남기고 제외
1

SELECT DISTINCT city, country FROM customers;
  • 2개 열의 값이 모두 동일한 행들을 제외

WHERE

where

1
2
3
4
5
6
7
8
9

SELECT
   name,
   milliseconds,
   bytes,
   albumid
FROM
   tracks
WHERE
   (albumid = 10) AND (milliseconds > 250000);

WHERE & LIKE

wildcard

1
2
3
4
5
6
7

SELECT
    trackid,
    name
FROM
    tracks
WHERE
    name LIKE 'Wild%';

WHERE & IN

1
2
3
4
5
6
7
8

SELECT
    TrackId,
    Name,
    MediaTypeId
FROM
    Tracks
WHERE
    MediaTypeId IN (1, 2)

WHERE & LIMIT/OFFSET

1
2
3
4
5
6

SELECT
    trackId,
    name
FROM
    tracks
LIMIT 10 OFFSET 7;
  • LIMIT: 불러오는 값의 수
  • OFFSET: OFFSET에 해당하는 수만큼 떼어내 그 이후의 데이터 불러옴

WHERE & BETWEEN

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    InvoiceId,
    BillingAddress,
    Total
FROM
    invoices
WHERE
    Total BETWEEN 14.91 AND 18.86
ORDER BY
    Total;

WHERE & IS NULL

1
2
3
4
5
6
7
8
9

SELECT
    Name, 
    Composer
FROM
    tracks
WHERE
    Composer IS NULL
ORDER BY 
    Name;