SQL Basic 02

Album Table

CREATE TABLE "Album" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  artist_id INTEGER,
  "title" TEXT
)

위의 테이블을 해석하면

• id 는 정수에 null 이 절대로 올수 없고 주키에 자동증가하는 테이블에서 유일무이한 값
• artist_id 는 외래키로 사용가능하고 정수
• title 은 text

Track Table

CREATE TABLE "Track" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  title TEXT,
  album_id INTEGER,
  genre_id INTEGER,
  len INTEGER, rating, INTEGER, count INTEGER
)

위 테이블은 Track 테이블로 중복되지 않았었던 값들을 갖고 있고 중복되는 항목에 대해서는 외래키로 받아서 사용할 수 있게끔 테이블이 짜여져 있다.

• id 는 정수에 null 이 올수없으며 주키역할을 하고 데이터가 추가될때마다 자동으로 id 값을 업데이트하는 이 테이블의 유일무이한 값이다.
• 나머지 항목은 첫번째 테이블과 동일한 해석 내용을 담을 수 있다.

Artist Table

CREATE TABLE "Artist" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  "name" TEXT
)

Genre Table

CREATE TABLE "Genre" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  "name" TEXT
)

INSERT DATA

INSERT INTO Artist (name) VALUES ('Led Zepplin');
INSERT INTO Artist (name) VALUES ('AC/DC');

INSERT INTO Genre (name) VALUES ('ROCK');
INSERT INTO Genre (name) VALUES ('Metal');

INSERT INTO Album (title, artist_id) VALUES ('Who Made Who', 2);
INSERT INTO Album (tltie, artist_id) VALUES ('IV', 1);

INSERT INTO Track (title, rating, len, count, album_id, genre_id) VALUES ('Black Dog', 5, 297, 0, 2, 1)
INSERT INTO Track (title, rating, len, count, album_id, genre_id) VALUES ('Stairway', 5, 482, 0, 2, 1)
INSERT INTO Track (title, rating, len, count, album_id, genre_id) VALUES ('About to Rock', 5, 313, 0, 1, 2)
INSERT INTO Track (title, rating, len, count, album_id, genre_id) VALUES ('Who Made Who', 5, 207, 0, 1, 2)

Join

만약 해당 테이블들에서 앨범의 제목과 가수의 이름을 동시네 매칭시켜서 보여주길 원한다면 join 을 사용하여 만들어 낼 수 있다.

SELECT Album.title, Artist.name from Album join Artist on Album.artist_id=Artist.id

위의 구문은 앨범에서 타이틀 아티스트테이블에서 이름을 가져올건데 앨범과 아티스트 테이블을 조인한것에서 가지고 올것이다. 그리고 가져올때 조건은 on 이후에 나와있다.
앨범의 아티스트아이디와 아티스트테이블의 아이디가 같을때만 이라는 조건이 붙어있다.

다대다 관계

다대다관계의 가장 보편적인 예로 들수 있는건 수강신청 모델이라고 할 수 있다.

• 한명의 학생은 여러개의 강의를 수강 가능하다.
• 하나의 수업은 여러명의 학생을 수용 가능하다.
• 한명의 교수는 여러개의 강의를 진행 할 수 있다.
• 한명의 교수는 어려명의 학생들을 수용 할 수 있다.

User Table

CREATE TABLE User (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  name TEXT UNIQUE,
  email TEXT
)

Course Table

CREATE TABLE Course (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  title TEXT UNIQUE,
)

Member Table

CREATE TABLE Member (
  user_id INTEGER,
  course_id INTEGER,
  role INTEGER, -- 선생일경우 1, 학생일경우 0
  PRIMARY KEY (user_id, course_id)
)

Insert Data

INSERT INTO USER (name, email) VALUES ('Jane', 'jane@google.com');
INSERT INTO USER (name, email) VALUES ('Ed', 'ed@google.com');
INSERT INTO USER (name, email) VALUES ('Sue', 'sue@google.com');

INSERT INTO Course (title) VALUES ('PYTHON');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('JAVASCRIPT');

-- (1, 1, 1) 1번은 1번 수업의 선생
-- (2, 1, 0) 2번은 1번 수업의 학생
INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);

INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);

INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);

데이터 추출

SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND
Member.course_id = Course.id
ORDER BY Corse.title, Member.role DESC, User.name