전체 글

UNION, UNION ALL, 교집합 , 차집합

UNION =중복 제거 UNION ALL = 중복 포함 SELECT절에서 가져오는 컬럼의 수가 같아야 함 ORDER BY는 쿼리 가장 마지막에 작성가능\ SELECT FROM UNION SELECT FROM UNION ALL : 중복된 값도 같이 붙임 SELECT FROM UNION ALL SELECT FROM 교집합 교집합 INTERSECT INNER JOIN 차집합 SELECT FROM LEFT JOIN ON t1.z WHERE t2.컬럼 IS NULL; mission 1) SELECT DISTINCT type FROM mypokemon UNION SELECT DISTINCT type FROM friendpokemon; mission 2) SELECT number, name, 'my' AS whose..

JOIN 실습

mission 1) SELECT t1.name, t2.attack, t2.defense FROM mypokemon AS t1 LEFT JOIN ability AS t2 ON t1.number= t2.number ; mission 2) SELECT t1.number, t2.name FROM ability AS t1 LEFT JOIN mypokemon AS t2 ON t1.number = t2.number ; mission 1) SELECT t1.type, t2.AVG(height) FROM mypokemon AS t1 INNER JOIN ability AS t2 ON t1.number=t2.number GROUP BY t1.type; mission 2) SELECT t1.type, t2.AVG(weight..

LEFT JOIN, INNER JOIN , 차집합

left join / right join SELECT t1.number, t1.name, t2.height, t2.weight FROM mypokemon AS t1 LEFT JOIN ability AS t2 ON t1.number = t2.number; SELECT * FROM mypokemon RIGHT JOIN ability ON mypokemon.number = ability.number; inner join - 두 테이블 모두에 있는 값만 합치기 OUTER JOIN , CROSS JOIN, SELF JOIN UNION = 두 쿼리의 결과를 중복을 제외하고 합쳐서 보여주는 집합 연산자 MySQL에는 키워드가 없어서 LEFT JOIN UNION RIGHT JOIN SELECT FROM LEFT JOI..

UDF(User Define Function), Delimiter

DELIMITER // CREATE FUNCTION isStrong(attack INT, defense INT) RETURNS VARCHAR(20) BEGIN DECLARE a INT; DECLARE b INT; DECLARE isstrong VARCHAR(20); SET a = attack; SET b = defense; SELECT CASE WHEN a+b>120 THEN 'very strong' WHEN a+b>90 THEN 'strong' ELSE 'not strong' END INTO isstrong ; RETURN isstrong; END // DELIMITER ; SELECT name, isStrong(attack, defense) AS isStrong FROM mypokemon;

IF, SUM IF, IFNULL, CASE WHEN

IF SELECT절에 사용 SELECT IF(조건, 참일때값, 거짓일때값) IFNULL IS NULL은 데이터가 NULL인지 아닌지 확인 IFNULL(컬럼, NULL일때값) SELECT절에서 사용하는 함수 IF mission 1) SELECT name, IF(number100 AND defense>100, True, False) AS ace FROM mypokemon; SUM (IF) select user_id from ( SELECT user_id , count(1) as cnt1 , sum( IF( item='device', 1, 0)) as cnt2 FROM tb_payment group by user_id ) AS tmp where cnt1 = cnt2 모든 고객의 id, 총 캐시 구매액, 총 기..

GROUP BY, Having 실습

ㄹㅇ mission 1) SELECT type, AVG(height) FROM mypokemon GROUP BY type; mission 2) SELECT type, AVG(weight) FROM mypokemon GROUP BY type; mission 3) SELECT type, AVG(height), AVG(weight) FROM mypokemon GROUP BY type; mission 4) SELECT type FROM mypokemon GROUP BY type HAVING AVG(height)>=0.5; mission 5) SELECT type FROM mypokemon GROUP BY type HAVING AVG(weight)>=20; mission 6) SELECT type, SUM(num..

숫자형데이터 실습

mission 1) SELECT type, AVG(weight) FROM pokemon.mypokemon WHERE LENGTH(name)>5 GROUP BY type HAVING AVG(weight)>=20 ORDER BY 2 DESC ; mission 2) SELECT type, MIN(height), MAX(height) FROM pokemon.mypokemon WHERE number=10 and MIN(weight)>=2 ORDER BY 2 DESC, 3 DESC; 테이블 생성하기 DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon( number INT, name VARCHAR(20..

쿼리실행순서

쿼리 실행순서 5 SELECT 1 FROM 2 WHERE 조건식 3 GROUP BY 4 HAVING 조건식 6 ORDER BY

문자형 데이터 실습

숫자형 데이터 ABS, CEILING, ROUND, POWER, MOD ABS(숫자)=숫자의 절댓값 반환 CEILING=정수로 올림해서 반환 FLOOR=정수로 내림해서 반환 ROUND=소숫점 자릿수까지 반올림해서 반환 TRUNCATE=소수점 자릿수까지 버림해서 반환 POWER(10, 2)=10의 2제곱 반환 MOD(10,5)=10을 5로 나눈 나머지 반환 따라서 2로 나누면 짝수인지 홀수인지 확인가능 mission1) SELECT name, LENGTH(name) FROM mypokemon ORDER BY LENGTH(name); mission2) SELECT name, RANK() OVER (ORDER BY defense DESC) AS defense_rank FROM mypokemon mission3..

문자열데이터(LENGTH, CONCAT, Locate, SUBSTR, LEFT, UPPER, Replace

ORDER BY 기본정렬규칙 = 오름차순 ORDER BY = ORDER BY ASC 내림차순 정렬 ORDER BY DESC LENGTH : 문자열의 글자수 반환 LENGTH("abc")=3 CONCAT : 문자열 합치기 CONCAT("ABC","DEF") CONCAT(LEFT(regymd,4), '년') SELECT part, LOCATE('i', lyric) FROM bts_music.butter; SUBSTRING *문자열에서 2번쨰 문자열부터 반환 SUBSTRING("abc", 2) SELECT part, SUBSTRING(lyric,3) FROM bts_music.butter; RIGHT("ABC", 1) -- ABC 오른쪽 1번째 문자까지 반환 LEFT(regymd, 4) -- ABC 왼쪾 4번..