D 프롬프트
앞에 서다

$ brew install duckdb

winget install DuckDB.cli

$ duckdb [OPTIONS] [FILENAME]

$ duckdb -help
Usage: duckdb [OPTIONS] FILENAME [SQL]
FILENAME is the name of an DuckDB database. A new …
if the file does not previously exist.
OPTIONS include:
   -append              append the database to th…
   -ascii               set output mode to 'ascii…
   -bail                stop after hitting an er…
   -batch               force batch I/O
   -box                 set output mode to 'box'
   -column              set output mode to 'colum…
   -cmd COMMAND         run "COMMAND" before read…
   -c COMMAND           run "COMMAND" and exit
   -csv                 set output mode to 'csv'
   -echo                print commands before exe…
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -json                set output mode to 'json…
   -line                set output mode to 'line…
   -list                set output mode to 'list…
   -markdown            set output mode to 'markd…
   -newline SEP         set output row separator
   -nofollow            refuse to open symbolic l…
   -no-stdin            exit after processing opt…
   -nullvalue TEXT      set text string for NULL
   -quote               set output mode to 'quote…
   -readonly            open the database read-on…
   -s COMMAND           run "COMMAND" and exit
   -separator SEP       set output column separat…
   -stats               print memory stats before…
   -table               set output mode to 'table…
   -unredacted          allow printing unredacted…
   -unsigned            allow loading of unsigned…
   -version             show DuckDB version

$ duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent da…
D 

$ duckdb mydb.duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
D 

D CREATE TABLE airlines as FROM airlines.csv;

D show tables;
┌──────────┐
│   name   │
│ varchar  │
├──────────┤
│ airlines │
└──────────┘

D .help
.bail on|off              Stop after hitting an e…
.binary on|off            Turn binary output on o…
.cd DIRECTORY             Change the working direc…
.changes on|off           Show number of rows chan…
.check GLOB               Fail if output since .te…
.columns                  Column-wise rendering of…
.constant ?COLOR?         Sets the syntax highligh…
.constantcode ?CODE?      Sets the syntax highligh…
.databases                List names and files of…
.dump ?TABLE?             Render database content…
.echo on|off              Turn command echo on or…
.excel                    Display the output of ne…
.exit ?CODE?              Exit this program with…
.explain ?on|off|auto?    Change the EXPLAIN forma…
.fullschema ?--indent?    Show schema and the cont…
.headers on|off           Turn display of headers…
.help ?-all? ?PATTERN?    Show help text for PATTE…
.highlight [on|off]       Toggle syntax highlighti…
.import FILE TABLE        Import data from FILE in…
.indexes ?TABLE?          Show names of indexes
.keyword ?COLOR?          Sets the syntax highligh…
.keywordcode ?CODE?       Sets the syntax highligh…
.lint OPTIONS             Report potential schema…
.log FILE|off             Turn logging on or off.
.maxrows COUNT            Sets the maximum number…
.maxwidth COUNT           Sets the maximum width i…
.mode MODE ?TABLE?        Set output mode
.nullvalue STRING         Use STRING in place of N…
.once ?OPTIONS? ?FILE?    Output for the next SQL…
.open ?OPTIONS? ?FILE?    Close existing database…
.output ?FILE?            Send output to FILE or s…
.parameter CMD ...        Manage SQL parameter bin…
.print STRING...          Print literal STRING
.prompt MAIN CONTINUE     Replace the standard pro…
.quit                     Exit this program
.read FILE                Read input from FILE
.rows                     Row-wise rendering of qu…
.schema ?PATTERN?         Show the CREATE statemen…
.separator COL ?ROW?      Change the column and ro…
.sha3sum ...              Compute a SHA3 hash of d…
.shell CMD ARGS...        Run CMD ARGS... in a sys…
.show                     Show the current values
.system CMD ARGS...       Run CMD ARGS... in a sys…
.tables ?TABLE?           List names of tables mat…
.testcase NAME            Begin redirecting output…
.timer on|off             Turn SQL timer on or off
.width NUM1 NUM2 ...      Set minimum column width…

D .database
mydb: mydb.duckdb

D .database
memory:

% duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent da…
D .open mydb2.duckdb
D CREATE TABLE airports as FROM airports.csv;
D show tables;
┌──────────┐
│   name   │
│ varchar  │
├──────────┤
│ airports │
└──────────┘

D ATTACH 'mydb.duckdb';

-- 별칭을 붙일 수도 있다. 생략하면 파일 이름이 기본 별칭이 된다
D ATTACH 'mydb.duckdb' as mydb;

D .database
mydb2: mydb2.duckdb
mydb: mydb.duckdb

-- 먼저 나열된 것이 현재 활성 데이터베이스다. 바꾸려면 USE에 별칭을 준다
D USE mydb2;

D .table
airlines     airports

D .dump airlines
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE airlines(IATA_CODE VARCHAR, AIRLINE VARCHAR);
INSERT INTO airlines VALUES('UA','United Air Lines Inc.');
INSERT INTO airlines VALUES('AA','American Airlines Inc.');
INSERT INTO airlines VALUES('US','US Airways Inc.');
INSERT INTO airlines VALUES('F9','Frontier Airlines Inc.');
INSERT INTO airlines VALUES('B6','JetBlue Airways');
INSERT INTO airlines VALUES('OO','Skywest Airlines Inc.');
INSERT INTO airlines VALUES('AS','Alaska Airlines Inc.');
INSERT INTO airlines VALUES('NK','Spirit Air Lines');
INSERT INTO airlines VALUES('WN','Southwest Airlines Co.');
INSERT INTO airlines VALUES('DL','Delta Air Lines Inc.');
INSERT INTO airlines VALUES('EV','Atlantic Southeast Airlines');
INSERT INTO airlines VALUES('HA','Hawaiian Airlines Inc.');
INSERT INTO airlines VALUES('MQ','American Eagle Airlines Inc.');
INSERT INTO airlines VALUES('VX','Virgin America');
COMMIT;

CREATE TABLE airports2 as FROM airports.csv;
SELECT * FROM airports2;

D .read commands.sql
┌───────────┬──────────────────────┬───┬─────────…
│ IATA_CODE │       AIRPORT        │ … │ COUNTRY…
│  varchar  │       varchar        │   │ varchar…
├───────────┼──────────────────────┼───┼─────────…
│ ABE       │ Lehigh Valley Inte…  │ … │ USA
│ ABI       │ Abilene Regional A…  │ … │ USA
│ ABQ       │ Albuquerque Intern…  │ … │ USA
...
│ YAK       │ Yakutat Airport      │ … │ USA
│ YUM       │ Yuma International…  │ … │ USA
├───────────┴──────────────────────┴───┴─────────…
│ 322 rows (40 shown)

% duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent da…
D CREATE TABLE airports as FROM read_csv_auto(airports.csv);

D EXPORT DATABASE 'airports_db';

$ duckdb mydb3.duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
D IMPORT DATABASE 'airports_db';
D show tables;
┌──────────┐
│   name   │
│ varchar  │
├──────────┤
│ airports │
└──────────┘

% duckdb library.duckdb
v0.10.1 4a89d97db8
Enter ".help" for usage hints.
D 

D CREATE TABLE Authors (
      author_id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      nationality TEXT,
      birth_year INTEGER
  );

D CREATE TABLE Borrowers (
      borrower_id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT,
      member_since DATE
  );

D CREATE TABLE Books (
      book_id INTEGER PRIMARY KEY,
      title TEXT NOT NULL,
      author_id INTEGER NOT NULL,
      genre TEXT,
      publication_year INTEGER,
      FOREIGN KEY (author_id) REFERENCES Authors(author_id)
  );

D CREATE TABLE Borrowings (
      borrowing_id INTEGER PRIMARY KEY,
      book_id INTEGER NOT NULL,
      borrower_id INTEGER NOT NULL,
      borrow_date DATE,
      return_date DATE,
      status TEXT,
      FOREIGN KEY (book_id) REFERENCES Books(book_id),
      FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
  );

FOREIGN KEY (author_id) REFERENCES Authors(author_id)

D show tables;
┌────────────┐
│    name    │
│  varchar   │
├────────────┤
│ Authors    │
│ Books      │
│ Borrowers  │
│ Borrowings │
└────────────┘

D DESCRIBE Authors;
┌─────────────┬─────────────┬─────────┬─────────┬…
│ column_name │ column_type │  null   │   key   │…
│   varchar   │   varchar   │ varchar │ varchar │…
├─────────────┼─────────────┼─────────┼─────────┼…
│ author_id   │ INTEGER     │ NO      │ PRI     │…
│ name        │ VARCHAR     │ NO      │         │…
│ nationality │ VARCHAR     │ YES     │         │…
│ birth_year  │ INTEGER     │ YES     │         │…
└─────────────┴─────────────┴─────────┴─────────┴…

-- SHOW 문으로도 같은 일을 한다
D SHOW Authors;

D .schema
CREATE TABLE Authors(author_id INTEGER PRIMARY KEY, name VARCHAR
NOT NULL, nationality VARCHAR, birth_year INTEGER);
CREATE TABLE Books(book_id INTEGER PRIMARY KEY, title VARCHAR NOT NULL,
author_id INTEGER NOT NULL, genre VARCHAR, publication_year INTEGER,
FOREIGN KEY (author_id) REFERENCES Authors(author_id));
CREATE TABLE Borrowers(borrower_id INTEGER PRIMARY KEY, name VARCHAR
NOT NULL, email VARCHAR, member_since DATE);
CREATE TABLE Borrowings(borrowing_id INTEGER PRIMARY KEY, book_id
INTEGER NOT NULL, borrower_id INTEGER NOT NULL, borrow_date DATE,
return_date DATE, status VARCHAR, FOREIGN KEY (book_id) REFERENCES
Books(book_id), FOREIGN KEY (borrower_id) REFERENCES
Borrowers(borrower_id));

D CREATE TABLE OverdueBorrowers (
          borrower_id INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          email TEXT,
          member_since DATE
      );

D DROP TABLE OverdueBorrowers;

Catalog Error: Could not drop the table because t…
main key table of the table "Books"

D INSERT INTO Authors (author_id, name, nationality, birth_year)
  VALUES
      (1, 'Jane Austen', 'British', 1775),
      (2, 'Charles Dickens', 'British', 1812),
      (3, 'Agatha Christie', 'British', 1890),
      (4, 'J.K. Rowling', 'British', 1965),
      (5, 'Tolkien', 'British', 1892);

-- 한 행만 넣는 문장
D INSERT INTO Authors (author_id, name, nationality, birth_year)
  VALUES (6, 'Mark Twain', 'American', 1835);

D INSERT INTO Borrowers (borrower_id, name, email, member_since)
  VALUES
      (1, 'John Smith', 'john.smith@example.com'…
      (2, 'Emma Johnson', 'emma.johnson@example.c…
      (3, 'Michael Brown', 'michael.brown@example…
      (4, 'Sophia Wilson', 'sophia.wilson@example…
      (5, 'William Taylor', 'william.taylor@examp…
      (6, 'Jane Doe', 'jane.doe@example.com', '20…

D INSERT INTO Books (book_id, title, author_id, genre, publication_year)
  VALUES
      (1, 'Pride and Prejudice', 1, 'Classic', 18…
      (2, 'Oliver Twist', 2, 'Novel', 1837),
      (3, 'Murder on the Orient Express', 3, 'Mys…
      (4, 'Harry Potter and the Philosopher''s St…
      (5, 'The Hobbit', 5, 'Fantasy', 1937);

D INSERT INTO Borrowings (borrowing_id, book_id, borrower_id,
  borrow_date, return_date, status)
  VALUES
      (1, 1, 1, '2022-04-10', '2022-04-25', 'Returned'),
      (2, 3, 2, '2022-03-20', NULL, 'On Loan'),
      (3, 4, 3, '2022-04-05', NULL, 'On Loan'),
      (4, 2, 4, '2022-04-15', NULL, 'On Loan'),
      (5, 5, 5, '2022-03-30', '2022-04-20', 'Returned'),
      (6, 1, 3, '2022-04-26', NULL, 'On Loan');

D UPDATE Borrowings
  SET return_date = '2022-04-05',
      status = 'Returned'
  WHERE borrowing_id = 3;

-- ① 이름으로
D DELETE FROM Borrowers
  WHERE name = 'Jane Doe';

-- ② 식별자로. 더 흔한 방식이다
D DELETE FROM Borrowers
  WHERE borrower_id = 6;

-- ③ 이름에 'Jane'이 들어간 레코드를 지운다
D DELETE FROM Borrowers
  WHERE name LIKE '%Jane%';

D SELECT *
  FROM Authors
  WHERE (YEAR(CURRENT_DATE) - birth_year) > 100;

D SELECT *
  FROM Books
  WHERE genre = 'Fantasy';

D SELECT *
  FROM Borrowers
  WHERE member_since >= '2022-01-01';

D SELECT b.book_id, b.title, a.name
  FROM Books b
  LEFT JOIN Authors a ON b.author_id = a.author_id;

D SELECT a.name, b.book_id, b.title
  FROM Authors a
  LEFT JOIN Books b on a.author_id = b.author_id;

D SELECT b.book_id, b.title, a.name
  FROM Books b
  RIGHT JOIN Authors a ON b.author_id = a.author_id;

D SELECT b.book_id, b.title, a.name
  FROM Books b
  INNER JOIN Authors a ON b.author_id = a.author_id;

D SELECT b.book_id, b.title, a.name
  FROM Books b
  FULL JOIN Authors a ON b.author_id = a.author_id;

D SELECT b.title AS book_title
  FROM Books b
  INNER JOIN Borrowings br ON b.book_id = br.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE bw.name = 'John Smith';
┌─────────────────────┐
│     book_title      │
│       varchar       │
├─────────────────────┤
│ Pride and Prejudice │
└─────────────────────┘

D SELECT bw.name AS borrower_name, b.title AS book_title
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id;

D SELECT bw.name AS borrower_name, b.title AS book_title, a.name
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  INNER JOIN Authors a ON b.author_id = a.author_id;

D SELECT b.book_id, b.title, br.borrow_date, br.status
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE bw.name = 'Michael Brown';

D SELECT bw.name AS borrower_name, COUNT(br.book_id) AS books_borrowed
  FROM Borrowings br
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  GROUP BY bw.name
  ORDER BY bw.name;

D SELECT b.book_id, b.title AS book_name, COUNT(*) AS num_borrowings
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  GROUP BY b.book_id, b.title
  ORDER BY num_borrowings DESC
  LIMIT 1;
┌─────────┬─────────────────────┬────────────────…
│ book_id │      book_name      │ num_borrowings
│  int32  │       varchar       │     int64
├─────────┼─────────────────────┼────────────────…
│       1 │ Pride and Prejudice │              2

-- 저자 전체의 평균 나이
D SELECT AVG(YEAR(CURRENT_DATE) - birth_year) AS average_age_of_authors
  FROM Authors;
┌────────────────────────┐
│ average_age_of_authors │
│         double         │
├────────────────────────┤
│                  162.5 │
└────────────────────────┘

-- 모든 책의 평균 출간 연도
D SELECT AVG(publication_year) AS avg_publication_year
  FROM Books;
┌──────────────────────┐
│ avg_publication_year │
│        double        │
├──────────────────────┤
│               1903.6 │
└──────────────────────┘

-- 가장 나이 많은 저자
D SELECT name, birth_year, YEAR(CURRENT_DATE) - birth_year AS age
  FROM Authors
  WHERE birth_year = (SELECT MIN(birth_year) FROM Authors);
┌─────────────┬────────────┬───────┐
│    name     │ birth_year │  age  │
│   varchar   │   int32    │ int64 │
├─────────────┼────────────┼───────┤
│ Jane Austen │       1775 │   249 │
└─────────────┴────────────┴───────┘

D SELECT bw.name AS borrower_name, b.title AS book_title, br.borrow_date,
         br.return_date,
         DATEDIFF('day', br.borrow_date, br.return_date) - 14 AS days_overdue
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE br.return_date IS NOT NULL AND
        DATEDIFF('day', br.borrow_date, br.return_date) > 14;

D CREATE VIEW overdue_borrowings AS
  SELECT bw.name AS borrower_name,
         b.title AS book_title,
         br.borrow_date,
         br.return_date,
         DATEDIFF('day', br.borrow_date, br.return_date) - 14 AS days_overdue
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE br.return_date IS NOT NULL
     AND DATEDIFF('day', br.borrow_date, br.return_date) > 14;

-- 만들어진 뷰는 DuckDB 데이터베이스에 영속된다
D SELECT * FROM overdue_borrowings;

D SELECT b.book_id, b.title, bw.name AS borrower_name,
         DATEDIFF('day', br.borrow_date, CURRENT_DATE) - 14 AS overdue
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE br.return_date IS NULL AND
        DATEDIFF('day', br.borrow_date, CURRENT_DATE) > 14;

D SELECT b.book_id, b.title, bw.name AS borrower_name,
         DATEDIFF('day', br.borrow_date, '2022-06-10') - 14 AS overdue
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE br.return_date IS NULL AND
        DATEDIFF('day', br.borrow_date, '2022-06-10') > 14;

D SELECT b.book_id, b.title AS book_name,
         bw.name AS borrower_name,
         br.borrow_date AS loan_date, br.return_date
  FROM Borrowings br
  INNER JOIN Books b ON br.book_id = b.book_id
  INNER JOIN Borrowers bw ON br.borrower_id = bw.borrower_id
  WHERE b.book_id IN (
      SELECT book_id
      FROM Borrowings
      GROUP BY book_id
      HAVING COUNT(*) = (
          SELECT MAX(num_borrowings)
          FROM (
              SELECT COUNT(*) AS num_borrowings
              FROM Borrowings
              GROUP BY book_id
          ) AS counts
      )
  )
  GROUP BY b.book_id, b.title, br.borrower_id, bw.name, br.borrow_date,
           br.return_date;