HELLICAT

UPADE / DELETE

DROP TABLE movies;

CREATE TABLE movies(
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	title TEXT UNIQUE NOT NULL,
  released INTEGER NOT NULL CHECK(released >= 1800),
  overview TEXT,
  rating REAL CHECK(rating BETWEEN 0 AND 10),
  director TEXT,
  adult INTEGER CHECK(adult BETWEEN 0 AND 1)
);

INSERT INTO movies
	(title, released, rating, adult)
VALUES
	('ZZANGGU', 1900, 8.1, 0),
  ('x-mans',2000,7.5,0);
  
--UPDATE

UPDATE movies SET released = 1991; --모든 영화의 개봉일 1991이 된다.
UPDATE movies SET released = 2001 WHERE title = 'x-mans' ;
UPDATE movies SET director = 'who?' WHERE director IS NULL;

--DELETE

-- DELETE FROM movies; -- 모든 영화가 삭제된다.
DELETE FROM movies WHERE id = 1;

SELECT

-- SELECT
SELECT * FROM movies; -- 모든 movies의 데이터를 불러올 수 있다.

SELECT id, title, released, rating, adult  FROM movies WHERE title = 'ZZANGGU'; 

SELECT 
	id,
  title,
  released,
  rating,
  adult
FROM
	movies WHERE id = 2;
  
SELECT 
	id,
  UPPER(title) as upper_title ,
  released * 2 AS double_rating,
  REPLACE(rating, 7.5, 44) as replace_rate,
  adult
FROM
	movies WHERE id = 2;

WHERE

SELECT
	*
FROM
	movies
WHERE
-- 	title <> 'star' -- <> != 은 같다;
-- release_date > 2023;
-- release_date is null; -- null을 검색하기 위해서는 * = null 아니라 * is null / is not null로 검색해야한다.movies
-- 	original_language != 'en' AND release_date >2022
-- release_date BETWEEN 2020 AND 2022
-- genres IN ('Documentray' , 'Comedy')
-- title LIKE 'star'
-- title LIKE 'THE%' -- %는 와일드카드
-- title LIKE '%Star%'
title LIKE '___ter'
;

Conditional

SELECT
	title,
  CASE WHEN rating >= 7 THEN
  	'GOOD'
  WHEN rating < 7 THEN
  	'SOSO'
  ELSE
  	'NAH~'
  END AS string_rating
FROM
	movies
;

Order

SELECT
	*
FROM
	movies
WHERE
	title LIKE 'star wars%'
ORDER BY
	rating DESC --- ASC 오름차순 DESC 내침차순
;

OFFSET

SELECT
	*
FROM
	movies
LIMIT
	5 -- 몇개를 보여줄지
OFFSET
  5 -- 몇개를 넘어갈지
  -- 1*5
  -- 2*5
;

SELECT
	*
FROM
	movies
WHERE
	title LIKE '%star%'
ORDER BY
	rating	DESC
LIMIT
	15 -- 몇개를 보여줄지
OFFSET
  0 * 15 -- 몇개를 넘어갈지
;

GROUP BY

SELECT
	director,
-- 	SUM(revenue) as total_revenue
	AVG(revenue) as avg_revenue
FROM
	movies
WHERE
	director IS NOT NULL
  AND
  	revenue IS NOT NULL
GROUP BY 
	director
ORDER BY
-- 	total_revenue DESC
	avg_revenue DESC
;

HAVING

SELECT
	director,
-- 	SUM(revenue) as total_revenue
	AVG(revenue) as avg_revenue
FROM
	movies
WHERE
	director IS NOT NULL
  AND
  	revenue IS NOT NULL
GROUP BY 
	director
HAVING
	avg_revenue > 800000000
ORDER BY
-- 	total_revenue DESC
	avg_revenue DESC
;

테이블 생성

CREATE TABLE movies (
	title,
    released,
    overview,
    rating,
    director
);

테이블 삭제

DROP TABLE movies;

테이블에 데이터 삽입

-- 데이터값의 순서와 모든 데이터값이 존재함을 지켜야함

INSERT INTO movies VALUES (
  'movie_title',
  '1999',
  'good',
  '10/10',
  'who?'
);

-- 해당 데이터에만 값을 넣기
INSERT INTO movies (title) VALUES ('SINGLE_MOVIE_TITLE');
INSERT INTO movies (title,rating) VALUES ('SINGLE_MOVIE_TITLE no.2','8/10');

테이블 데이터 값에 타입지정 (SQLITE)

CREATE TABLE movies (
  title TEXT,
  released INTEGER, -- 1,2,3,4..
  overview TEXT,
  rating REAL, -- 1.1 , 2.8 , 3.6 ...
  director TEXT,
  adult INTEGER, -- sqlite에서 boolean을 넣기 위해서는 0,1 로 넣어 동작한다.
  forster BLOB, -- 데이터베이스에 이미지같은 파일을 저장하기 위해서는 BLOB을 사용한다.
);

테이블 데이터 값에 제약 넣기

CREATE TABLE movies (
  title TEXT UNIQUE NOT NULL,
  released INTEGER CHECK (released > 0), -- 1,2,3,4..
  overview TEXT NOT NULL CHECK(LENGTH(overview) < 100),
  rating REAL, -- 1.1 , 2.8 , 3.6 ...
  director TEXT NOT NULL,
  adult INTEGER NOT NULL DEFAULT 0 CHECK (adult = 0 OR adult = 1)
  -- adult INTEGER NOT NULL DEFAULT 0 CHECK (adult BETWEEN 0 AND 1)
);

INSERT INTO movies VALUES (
  'movie_title',
  1999,
  'good',
  9.7,
  'who?',
  1
);

INSERT INTO movies (title, overview, director, adult) VALUES 
	('SINGLE_MOVIE_TITLE','fine','thomas',0);

기본키(Primary Key)

기본키는 유니크해야하며 수정 불가해야한다.

기본키는 두가지 유형이 있는데 이는 기본기(Natural Primary Key) 대체 기본키 (Surrogate Primary key)가 있다.

자연 기본키는 테이블의 데이터와 논리적 관계를 갖는 기본키이다.

대체 기본키를 사용하는것을 추천

CREATE TABLE movies (
-- surrogate_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, --아래 데이터와 관련이 없다. 단지 고유식별자가 필요하기 때문이다.
  title TEXT UNIQUE NOT NULL PRIMARY KEY, -- natural키는 데이터와 연관이 있다.
.
.
.
);