댕기

4.1) 데이터 추가(4-16강 P.152) : INSERT INTO 테이블명 VALUES (값1,값2);

INSERT INTO FILMS VALUES ( 501,"기생충",2019,"KOREA",131,"KOREAN","15세관람가",15000000000,168163654*1190);

INSERT INTO FILMS VALUES ( 502,"Marriage Story",2019,"UK",137,"ENGLISH","15세관람가",323382,18600000);

INSERT INTO reviews VALUES ( 4969,,502,1022,295,8.0,174104,0);

4.2) DELETE (4-17강 P.160) : DELETE FROM 테이블명 WHERE 조건

DELETE FROM reviews WHERE ID =600;

4.3) UPDATE (4-18강 P.164) : UPDATE 테이블명 SET 컬럼=값 WHERE 조건

UPDATE REVIEWS SET imdb_score=8.6 WHERE film_id=501;

UPDATE REVIEWS SET num_user=40000000, num_critic=0, num_votes=62413,facebook_likes=62240 WHERE film_id=501;

데이터를 내맘대로 정렬한다.

3.1) 정렬 (3-9강 P94) : ORDER BY 열명 [ASC] DESC

  SELECT * FROM films  ORDER BY id

  SELECT * FROM  films ORDER BY id DESC

3.2) 다수 정렬 (3-10강 P.102)

  SELECT * FROM  films ORDER BY id , release_year;

  SELECT * FROM  films ORDER BY id DESC , release_year;

3.3) 특정 수만 조회(3-11강 P.108) :  LIMIT 숫자;

  SELECT * FROM films WHERE country="USA" LIMIT 2;

  SELECT * FROM films WHERE country<>'USA" ORDER BY id DESC LIMIT 4;

  SELECT * FROM films WHERE country<>'USA' ORDER BY id DESC LIMIT 4 OFFSET 2;

3.4) 사치연산 (3-12강 P.114) : + - / * %

  SELECT num_user + num_critic AS Fit1  FROM reviews ;

  SELECT num_user + num_critic AS Fit1 FROM reviews WHERE num_user > 600;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user > 600 ORDER BY id DESC;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user < 600 ORDER BY id DESC;

  SELECT imdb_score1 FROM reviews;

SELECT * FROM films WHERE duration /60 > 4;

SELECT duration /60,* FROM films WHERE duration /60 > 3 ORDER BY duration /60  DESC

3.5) 문자열 결합 (3-13강 P.130) : ||

SELECT title||country FROM films;

  SELECT substr(title,3) ||  " OF "||country FROM films;

  SELECT substr(title,3) ,trim(title) FROM films;

3.6) 날짜연산 (3-14강 P.136) : CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

SELECT CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

  쿼리 실행안됨 --SELECT CURRENT_DATE + INTERVAL 1 DAY

3.7) CASE문(3-15강 P.140) : CASE WHEN  조건 THEN 결과 ELSE 결과 END

SELECT CASE WHEN DURATION/60 > 3 THEN "LING MOVIE" END AS TYPE1,* FROM films;

데이터 조회는 from table에서 select로 조회한다.

SELECT * FROM Table명;

1.1) 단순조회(2-4강 P.52) : SELECT * FROM films

1.2) 테이블조회  DESC (안됨,2-5강 P.61) : DESC films

1.2.2) 자료형 : INTEGER형, CHAR형, VARCHAR형, DATE형, TIME형

1.3) 검색조건 넣고 조회(2-6강 P.65) : WHERE 조건

1.3.1) 조건과 일치 : SELECT * FROM films WHERE country="USA";

1.3.2) 조건과 다른 것 : SELECT * FROM films WHERE country<>"USA"

1.3.3) 널값인것과 아닌 것 : SELECT * FROM films WHERE  language IS NULL / IS NOT NULL

1.4) 조건 조합(7강 P.75) : WHERE 조건 AND 조건 , WHERE 조건 OR 조건 

  SELECT * FROM films WHERE country="USA" and language IS NULL

  SELECT * FROM films WHERE country='USA' or language IS NULL

1.5) 유사값 조회 (8강 P.83) : WHERE LIKE

SELECT * FROM films WHERE title like "REB%"                  SELECT * FROM films WHERE title NOT like "REB%"

SELECT * FROM films WHERE title like "%THE"                     SELECT * FROM films WHERE title like "%THE%" 

SELECT * FROM films WHERE title NOT like "%THE"              SELECT * FROM films WHERE title NOT like "%THE%"