📚 Programming/SQL

[MYSQL] Data INSERT, UPDATE, DELETE

INSERT 기본형 INSERT INTO table VALUES (data); 전체 컬럼에 데이터 추가 INSERT INTO table VALUES (data1,data2,...); 컬럼 지정해서 데이터 추가 (NOT NULL이 존재하면 X) INSERT INTO table (column1, column2,...) VALUES (data1,data2,...); 한 번에 여러 row의 데이터를 추가 INSERT INTO table VALUES (data1,data2,...),(data1,data2,...); UPDATE 기본형 UPDATE table SET column1 = data1 WHERE column2=data2; 테이블에 있는 column1의 값을 data1로 모두 변경 UPDATE table S..

[MYSQL] 우유와 요거트가 담긴 장바구니

Programmers -> Summer/Winter Coding(2019) SELECT A.CART_ID FROM (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') AS A JOIN (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') AS B ON A.CART_ID = B.CART_ID; ❗ JOIN 할 때 ON으로 묶어줄 칼럼 정하는거 잊지말기 ❗

[MYSQL] SELECT is null FROM Programmers

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID; SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID; SELECT ANIMAL_TYPE, IFNULL(NAME, "No name") AS NAME, SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID; IFNULL(a,b) -a가 null이면 b로 대체

[MYSQL] SELECT groupby FROM Programmers

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS count FROM ANIMAL_INS GROUP BY ANIMAL_TYPE; SELECT NAME, COUNT(ANIMAL_ID) AS COUNT FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME HAVING COUNT>=2 ORDER BY NAME; HAVING - GROUP의 조건 SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT FROM ANIMAL_OUTS GROUP BY HOUR HAVING HOUR >=9 AND HOUR hour이라는 변수를 -1로 초기화

[MYSQL] SELECT sum,max,min FROM Programmers

SELECT MAX(DATETIME) FROM ANIMAL_INS; SELECT MIN(DATETIME) FROM ANIMAL_INS; SELECT COUNT(*) AS 'COUNT' FROM ANIMAL_INS; COUNT -갯 수 return하는 쿼리 AS -보여줄 칼럼이름 지정 SELECT COUNT(DISTINCT NAME) AS 'COUNT' FROM ANIMAL_INS WHERE NAME IS NOT NULL; DISTINCT - 중복을 제거하는 쿼리

[MYSQL] SELECT select FROM Programmers

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID; SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC; SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = 'Sick'; SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE NOT INTAKE_CONDITION = 'Aged'; SELECT ANIMAL_ID, NAME FROM ANIMAL_INS ORDER BY ANIMAL_ID; SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME, DATETIME DESC; SELE..