INSERT INTO movies VALUES ( 'The Godfather', 1980, 'The best movie', 10, 'FFC');CREATE TABLE movies( title, released, overview, rating, director);
여러 개의 데이터를 한번에 삽입하는 방법
INSERT INTO movies VALUES ('The Godfather', 1980, 'The best movie', 10, 'FFC'),('1984', 10, 'gooood', 10, 'don`t know');
위 방식대로 데이터를 삽입한다면 항상 열의 순서를 기억해야 한다는 문제가 존재함.
또한, 항상 각 열의 모든 값을 알아야 한다는 문제가 있음. 모르는 값이 있으면 전체 데이터가 해당하는 열에 맞지 않게 배치됨
이 문제를 해결하는 가장 쉬운 방법은 ==NULL==을 사용하는 것이나 여전히 열의 순서를 알아야한다는 문제는 해결되지 않았다.
대신 아래와 같이 ()안에 내가 입력하고 싶은 column만 명시하고 값을 입력.
INSERT INTO movies (title) VALUES ('Thor');
위와 같이 작성하면 title 외 나머지 column은 모두 Null이 되며, 열의 순서를 기억할 필요가 없어진다. 앞으로 이 방식을 가장 많이 사용함.
DDL로 다시 돌아와서 ... 현재 위 SQLite에는 column의 이름만 있을 뿐 별도로 타입이 지정되어 있지 않기 때문에 예를 들어 title에 Number 타입이 들어가도 전혀 문제가 되지 않는다.
각 columns 타입 지정 방법(SQLite 에서 지원하는 타입 기준, database별로 서로 다른 타입을 가지므로 참고만!)
CREATE TABLE movies( title TEXT, released INTEGER, -- 1,2,3... overview TEXT, rating REAL, -- 1.2, 0.45 director TEXT, for_kids INTEGER,-- 0이면 false, 1이면 true를 의미, 0,1외 다른 정수는 못들어오게 제약조건 설정해야 함. image BLOB -- DB에 이미지를 저장할 때 사용) STRICT; -- SQLite가 타입 검사를 해서 오류를 반환토록
Constraint(제약조건) : column에 입력될 데이터를 통제하는 것을 도와주는 역할.
title의 값은 고유해야 하며, title을 포함한 몇몇 column들의 값이 반드시 존재해야 한다면
CREATE TABLE movies( title TEXT UNIQUE NOT NUll released INTEGER NOT NUll -- 1,2,3... overview TEXT NOT NUll, rating REAL NOT NUll, -- 1.2, 0.45 director TEXT, for_kids INTEGER,-- 0이면 false, 1이면 true를 의미, 0,1외 다른 정수는 못들어오게 제약조건 설정해야 함. image BLOB -- DB에 이미지를 저장할 때 사용) STRICT; -- SQLite가 타입 검사를 해서 오류를 반환토록
기본값 추가
CREATE TABLE movies( title TEXT UNIQUE NOT NUll released INTEGER NOT NUll -- 1,2,3... overview TEXT NOT NUll, rating REAL NOT NUll, -- 1.2, 0.45 director TEXT, for_kids INTEGER NOT NULL DEFAULT 0,-- 0이면 false, 1이면 true를 의미, 0,1외 다른 정수는 못들어오게 제약조건 설정해야 함. image BLOB -- DB에 이미지를 저장할 때 사용) STRICT; -- SQLite가 타입 검사를 해서 오류를 반환토록
Check Constraint : 매우 강력한 제약조건
데이터 유효성을 검사할 때, 나만의 로직을 추가할 수 있음
예를 들어 rating, 년도가 음수이면 안되며, overview가 50자를 넘지않고 title이 특정 단어를 포함하지 않도록 하거나 for_kids에 제한을 두어서 0, 1의 값만 가능하도록 하는 것들이 가능하다.
CREATE TABLE movies( title TEXT UNIQUE NOT NUll released INTEGER NOT NUll -- 1,2,3... overview TEXT NOT NUll, rating REAL NOT NUll, -- 1.2, 0.45 director TEXT, for_kids INTEGER NOT NULL DEFAULT 0 CHECK (for_kids = 0 OR for_kids = 1),-- 0이면 false, 1이면 true를 의미, 0,1외 다른 정수는 못들어오게 제약조건 설정해야 함. image BLOB -- DB에 이미지를 저장할 때 사용) STRICT;
for_kids 를 보면 DEFAULT 기본값 다음에 ==CHECK ()==이 보이는데 CHECK 이후에는 어떠한 로직이 와도 상관없다.
대신 그건 참이 되어야 한다.
for_kids가 0 이거나 1어야 된다고 하는 걸 아래와 같이 수정할 수 도 있음
for_kids INTEGER NOT NULL DEFAULT 0 CHECK (for_kids BETWEEN 0 AND 1);
released와 rating이 항상 양수가 되게끔 해보자 (released > 0 / rating BETWEEN 10 AND 10)
CREATE TABLE movies( title TEXT UNIQUE NOT NUll released INTEGER NOT NUll CHECK (released > 0) overview TEXT NOT NUll, rating REAL NOT NUll CHECK (rating BETWEEN 0 AND 10), director TEXT, for_kids INTEGER NOT NULL DEFAULT 0 CHECK (for_kids = 0 OR for_kids = 1),) STRICT;
마지막으로 overview가 100자를 넘지 말아야 한다는 제약조건을 걸어보자!
이를 통해 DB의 내장 함수에 대해 알아보려 한다. 함수는 데이터를 조작하거나 데이터를 통해 정보를 얻거나 SQL만으로는 할 수 없는 일을 할 수 있게 도와주며, 함수는 DB에 내장되어 있기 때문에 언제든지 호출할 수 있고 DBMS 별로 내장함수의 종류가 다르다.
예를 들어 SQLite에서 length(x) 함수는 텍스트의 길이를 보여주는 내장 함수이다.
overview TEXT NOT NUll CHECK (LENGTH(overview) < 100),
기본키(Primary Key) : 각 행을 고유하게 식별하는 식별자이다.
기본키 column에는 2가지 특성이 있다.
변경이 불가능하도록 불변해야 하며,
고유해야 한다.(Unique)
행을 생성하는 순간 그 행은 고유 식별자로 사용할 열을 가져야 하고 그 고유 식별자는 변경할 수 없는 불변성을 가져야 한다.
기본키에는 두가지 유형이 있다.
자연 기본키(natural primary key) : 테이블의 데이터와 논리적 관계를 갖는 기본키이다.
대체 기본키(surrogate primary key)
위 예제에서 자연 기본키로 적절한 열은 title이다. 왜냐하면 title은 고유하며, 변경할 소요가 없기 때문이다. 그러므로 title을 기본키로 설정하는 방법은 다음과 같다.
title TEXT PRIMARY KEY NOT NUll UNIQUE,
대체 기본키는 다음과 같이 생겼다.
CREATE TABLE movies( movie_id INTEGER PRIMARY KEY title TEXT NOT NUll UNIQUE, ...) STRICT;
여기서 movie_id가 대체 기본키인데, 보다시피 movie_id는 여기 있는 어떤 데이터와도 연관이 없다.
다른 열과 논리적 관계가 전혀 없이 row를 위한 고유 식별자가 필요하기 때문에 사용되는 키이다.
보통은 자연 기본키를 불변하게 유지하기 어렵기 때문에 대체 기본키를 많이 사용하게 된다.
SQLite에만 특화된 기능으로 기본키에 _auto increment_를 사용하게 되면 항상 새롭고 고유한 id를 기본키로 갖게될 것을 보장받는다.
sqlite_sequence 라는 테이블이 갑자기 생성된걸 볼 수 있는데 이는 sqlite에서 자동으로 만들어진건데 movies 데이터베이스를 만들때 기본키인 movie_id에 auto inacrement를 지정했기 때문에 자동으로 생성됨
sqlite는 얼마나 많은 id가 생성되었는지 추적해야 하기 때문에 이런 테이블을 자동으로 생성해서 추적/관리함.
DML은 2가지 카테고리로 나뉜다 update 명령과 query 명령이다.
query는 select 명령, update 명령에는 update, delete가 있음
UPDATE
-- UPDATEUPDATE movies SET rating = 10; -- movies 테이블의 모든 rating을 10으로 바꾸는 불상사 발생..UPDATE movies SET rating = 10 WHERE title = 'The Lord of The Rings'; -- where를 사용해서 범위를 좁힐 수 있음UPDATE movies SET rating + 2 WHERE title = 'The Lord of The Rings'; -- 이런 식으로 행의 값을 참조할 수도 있음UPDATE movies SET director = 'Unkown' WHERE director IS NULL; -- director = NUll 이라고 하면 항상 false 값을 반환하므로 동작하지 않음..왜냐면 Null은 value가 아니기때문.
DELETE
-- DELETEDELETE FROM movies; -- movies테이블의 모든 행을 삭제해버림..DELETE FROM movies WHERE movie_id = 2 AND director IS NULL; -- where 조건 확장도 가능
SELECT
테이블을 결과물로 제공하는 명령.이라고 이해할 수 있음.
즉, columns와 row가 있다는 의미.
예를 들어 아래 sql의 결과는 다음과 같다.
SELECT 1+1, 2+2, 'hello';
select가 올바르게 호출되면 항상 table을 결과물로 제공한다는걸 알 수 있음
또한, 위 코드는 table에 포함할 column을 지정한다는걸 알 수 있음
이제 DB에서 data를 가져오고 어떤 데이터를 가져올지 정하려면 from절을 사용함.
from은 테이블로부터 data를 가져올 수 있게함
그러면 from절이 먼저 실행되고 select는 결과물을 구성하는 순으로 동작함.
SELECT * FROM movies; -- movies 테이블의 모든 column을 선택.SELECT title, rating FROM movies; -- movies 테이블(source table)에서 title, rating 행만 선택
select는 단순히 표현식을 실행하는 것 이상의 기능을 제공한다.
예를 들어 overview column을 가져오는데 여기에 function을 결합할 수도 있고 이 column에 별칭을 줄수도 있다.
또한, title과 같은 결과 column의 모양을 내가 원하는 방식으로 포맷팅 할 수도 있다.(replace function활용)
-- SELECT ExpressionsSELECT title, rating, UPPER(overview) FROM movies; -- overview 컬럼의 데이터를 모두 대문자로.SELECT title, rating * 2, UPPER(overview) AS overview_alias FROM movies; -- overview 컬럼에 AS로 별칭 지정SELECT REPLACE(title, ': Part One', 'I') AS title, rating * 2, UPPER(overview) AS overview_alias FROM movies; -- replace를 활용해서 기존 데이터 중 표현방식을 내가 원하는대로 수정,-- upper나 replace와 같이 함수를 사용하게 되면 결과 column명이 그 함수식으로 그대로 출력되니 as를 활용해서 별칭을 지어줌.
이제부터 약 25만개에 달하는 영화를 가지고 있는 실제 db를 가지고 나머지 강의를 진행함.
이 db는 의도적으로 잘못 설계되어 있고 추후 어떤 문제가 있고 어떤 식으로 다시 디자인해야 할지 확인하고, 또한 쿼리 최적화를 통해 실제 비교가 가능함.
WHERE 절
-- WHERE 절SELECT *FROM moviesWHERE -- 조건이나 predicate -- 동등 조건 director = 'Guy Ritchie'; -- 같지 않다 director != 'Guy Ritchie'; original_language <> 'en'; -- 비교(논리 연산자) release_date > 2023; release_date < 2023; -- null은 다른 null과 동일하지 않음. revenue = NULL; -- 아무런 데이터도 반환하지 못함. revenue IS NULL; -- 이게 맞는 표현 revenue IS NOT NULL; -- revenue가 NULL이 아닌 행들을 조회 -- 논리연산자를 사용하여 여러 조건을 함께 연결하면 where 조건을 더 강력하게 만들 수 있음 status = 'Planned' AND budget <> 0 AND budget IS NOT NULL -- status가 planned이고 budget이 0이 아니며, NULL이 아닌 것들을 조회 -- AND가 사용되었으므로 모든 조건들이 true여야 함. -- 평점이 9점을 넘는 영화이거나 개봉일이 2024년보다 큰 영화를 조회 rating > 9 OR release_date > 2024; -- OR 는 모든 조건이 true일 필요가 없이 둘 중 true인 것들을 조회함. -- 중첩 조건 rating > 9 OR (rating IS NULL AND genres = 'Documentary') -- 평점이 9이상이거나 평점이 null이 아니고 종류가 다큐멘터리인 영화를 조회