sql 데이터의 삭제
delete : 만약 where 로 특정 행(row)을 지정해주지 않으면 테이블의 모든 데이터를 지운다.
DELETE FROM member WHERE city_name = 'BLK';데이터의 수정
update 테이블 이름 set 바꿀 데이터 : 만약 where로 특정 조건을 줘서 row를 지정해주지 않으면 테이블의 모든 데이터가 수정된다.
UPDATE member
SET mem_name = '방탄소년단', mem_id = 'BTS'
WHERE mem_name = '블랙핑크';
함수를 이용해 문자열 연결하기
concat() 함수를 이용하면 두 문자열을 이을수 있다. 쉼표로 구분된 두 문자열을 잇는다.
특정 데이터를 추출해내서 출력하기 특정 문자열을 덧붙이고 싶을때 사용한다.
UPDATE member
SET mem_name = CONCAT('수정된 데이터', mem_name), mem_id = CONCAT('수정된 데이터', mem_id)
where mem_id = 'BTS';
데이터의 타입과 범위
1. 정수형
INT, BIGINT, SMALLINT, TINYINT 가 존재
표현범위와 용량
TINYINT : 1바이트, -128 ~ +127
SMALLINT : 2바이트, -32,768~ + 32767
INT : 4바이트, -21억 ~ +21억
BIGINT : 8바이트, -900경, +900경
2. 실수형
FLOAT : 4바이트 : -3 * 10^38승 ~ +3 * 10^38승
DOUBLE : 8바이트 : -1.797 * 10^308승 ~ -1.79 * 10^308승
** 양수만 쓰고자 한다면 테이블을 만들때 데이터 타입 옆에 UNSIGNED 속성을 주면 표현할수 있는 양수의 범위가 두배가 되며 음수는 사용하지 못하게 된다.**
3. 문자형
CHAR() : 괄호 안에 글자 개수만큼의 숫자를 넣어서 사용하며, 그 숫자가 용량이 된다. 1~255바이트까지 쓸수 있다.
VARCHAR() : CHAR와 마찬가지이다. 다만 VARCHAR는 10개로 지정하고 5글자만 쓰면 알아서 5바이트의 용량만 사용하는 동적 데이터다. CHAR보다 더 느리다. 1~16383바이트까지 쓸수 있다.
TEXT : 1~65535까지의 데이터를 차지할 수 있다. 긴 글(영화의 자막 등) 을 저장할때 사용한다.
LONGTEXT: 최대크기 4GB의 데이터 타입.
BLOB : 바이너리 이진수(동영상이나 이미지가 변환된 이진수)등을 담을때 사용한다. 65535 바이트
LONGBLOB : 최대 크기 4GB의 데이터 타입.
** BLOB로 실제 데이터를 담기보다는, 이름만 담고 클라우드나 파일 업로드를 이용하는게 좋다.**
4. 날짜/ 시간형
DATE : "1000-01-01" ~ "9999-12-31" : 심볼( yyyy-mm-dd)
TIME : "00:00:01" UTC ~ "23:59:59"UTC : 심볼 (HH:MM:SS)
DATETIME : 날짜 시간 동시 표현. "1970-01-01 00:00:01" UTC ~ "9999-12-31 23:59:59" UTC
SQL에서의 변수 사용
@변수명을 통해 변수를 선언하며, set 명령어를 통해 선택한다.
set @aaa = 10;다만 =이 같다는 뜻인지, 아니면 대입연산자인지 햇갈릴수가 있기 때문에 :=로 표현할수도 있다.
set @aaa := 30;SET @txt = '그룹 이름==> ' ;
SET @name ='ITZY' ;
SELECT @txt , mem_name FROM member WHERE mem_name = 'ITZY' ;그러나 변수를 지정하고 그 변수를 다시 LIMIT의 값으로 받는 것은 안된다.
SET @aaa = 10;
SELECT * FROM member LIMIT aaa; => 오류발생이럴때 사용할수 있는 것이 PREPARE, EXCUTE, USING이다.
PREPARE 절
형식.
1. 먼저 SET을 통해 변수를 선언한다.
2. 이후 PREPARE를 통해 물음표(?) 가 포함된 쿼리문을 문자열 형태로 변수로 받는다.
3. EXCUTE를 통해 쿼리문자열 변수를 실행하는데, 이때 USING을 이용해 1에서 선언한 값을 ? 자리에 넣는다.
예시
SET @count = 5;
PREPARE mySQL
FROM 'SELECT * FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @COUNT;데이터의 형변환
1. 함수를 이용한 형변환.
CAST ()
CONVERT()
SELECT CONVERT(AVG(price) , SIGNED) '평균 가격' FROM buy ;
SELECT CAST (AVG(price), SIGNED) '평균가격' FROM buy;
DATE타입이 아닌 것을 DATE타입으로 변경도 가능하다.
SELECT CAST('2022-12-12' AS DATE);
SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);- 가 정석이기는 하나, $, /, %, @ 로 대체해서 표현해도 문제 없다.
2. 묵시적 형변환
숫자형 데이터를 산술연산하면, 따옴표로 감싸 문자열로 표현해도 자동적으로 숫자형으로 인식이 된다.
SELECT '100' + '200'; -- 산술연산이 됨.
SELECT '100' + 200; -- 산술연산이 됨.같은 맥락에서 따옴표로 감싸지 않더라도 concat함수를 이용하면 문자열 연결이 된다.
SELECT CONCAT( '100', '200' ); -- 문자열 연결됨.
SELECT CONCAT( '100', 200 ); -- 문자열 연결됨.
SQL의 TRUE/ FALSE 표현
SQL에서 FALSE 는 0, TRUE는 1이상의 숫자로 나타내어 진다.
SELECT 1 > 0; -- 1이 나옴
SELECT 1 > 3; -- 0이 나옴그렇다면 숫자와 문자열을 비교하면 어떤 값이 나오는지 확인해 보자.
SELECT 1 > 'mega'; -- 1이 나옴. TRUE
SELECT 0 > 'megasdf1111a'; -- 0이 나옴. FALSE
SELECT 0 = '111mega111'; -- 1 이 나옴. TRUE문자열이 얼마나 길건, 숫자가 포함되어 있건 아니건, 숫자로만 이뤄진게 아닌 문자열은 0으로 인식된다.
조인(join)
두개 이상의 테이블을 가로로 이어붙이는 것.
한 테이블이 다른 테이블의 값을 참조할때 사용한다. 두개 이상의 테이블을 연결하기 위해서는 둘을 잇는연결고리가 필요한데, foregin key(외래키 fk) 가 바로 그 역할을 수행한다.
조인은 총 다섯가지 종류가 존재한다.
1. CROSS JOIN (데카르트 곱)
한 테이블의 행 하나에 연결된 다른 테이블의 모든 행을 대응시키는 JOIN이다. 즉, 모든 경우의 수를 나타낸다.
데카르트 곱(모든 경우의 수) 만큼의 ROW가 나온다. 개인적으로 이걸 어디다 써먹나 싶고, 왜 필요한가 싶다.
2. LEFT JOIN : 왼쪽 테이블( table1) 의 모든 행을 출력한다.
3. RIGHT JOIN : 오른쪽 테이블( table2) 의 모든 행을 출력한다.
4. INNER JOIN : 특정열의 값을 기준으로 값이 같은 행을 출력. 조건식을 달아서 사용한다.
5. FULL OUTER JOIN : 모든 테이블의 행을 출력한다.
CROSS JOIN의 경우는 행의 갯수가 row1 * row2 만큼 생기지만, FULL OUTER JOIN은 row1 + ro2만큼 생긴다.
아우터조인 ( LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)
LEFT JOIN
왼쪽 테이블의 모든 값을 출력한다. 설사 CULOMN 의 값이 NULL 이더라도 출력된다. 허나 만약 왼쪽 테이블 열의 값만 출력한다면, JOIN하지 않고 그냥 왼쪽 테이블에서만 출력한 것과 같은 결과가 나온다.
SELECT m.mem_id, m.mem_name, m.addr, b.pord_name
FROM member m
LEFT OUTER JOIN buy b
ON m.mem_id = b.mem_id
ORDER BY m.mem_id;즉, 이때 member 테이블에 buy 테이블 prod_name에 null값만 가진 데이터들이다 하더라도 null로 출력이 된다는 것이다.
SELECT DISTINCT m.mem_id, b.prod_name, m.mem_name, m.addr
FROM member m
LEFT OUTER JOIN buy b
ON m.mem_id = b.mem_id
WHERE b.prod_name IS NULL
ORDER BY m.mem_id;이런 식으로 NULL 값인 데이터들만 출력할수도 있다.
RIGHT JOIN
LEFT 와 그 순서만 바뀐게 전부다.
FULL OUTER JOIN
아쉽게도 mySQL과 maridDB에서는 full outer join을 위한 다른 명령어가 없다. 대신 UNION 명령어를 통해 두 테이블을 이어붙일수 있다.
SELECT m.mem_id, b.prod_name, m.mem_name, m.addr
FROM member m
LEFT OUTER JOIN buy b
ON m.mem_id = b.mem_id
UNION
SELECT m.mem_id, b.prod_name, m.mem_name, m.addr
FROM member m
RIGHT OUTER JOIN buy b
ON m.mem_id = b.mem_id;각각 left join 과 right join을 한 다음, union을 통해 한개로 합친 합쳐진다. 가로로 합쳐지며, union뒤에 나온 테이블이 뒤로 간다.
UNION은 기본적으로 DISTINCT의 성질을 가지고 있어 중복되는 결과를 제거한다.
이를 원하지 않는다면 UNION ALL을 사용하면 된다.
UNION하는 열의 갯수는 같아야 한다. 열의 갯수가 다르면 오류가 발생한다.
출력하는 열의 이름은 달라도 상관 없으나, 출력시 열의 이름은 먼저 선언된 join의 값으로 정해진다.
SELECT m.mem_id, b.prod_name, m.mem_name, m.addr
FROM member m
LEFT OUTER JOIN buy b
ON m.mem_id = b.mem_id
WHERE m.mem_id = 'MMU'
UNION
SELECT m.mem_id, b.prod_name, m.mem_id, m.addr
FROM member m
RIGHT OUTER JOIN buy b
ON m.mem_id = b.mem_id
WHERE b.mem_id= 'MMU';
WHERE 와 ON의 차이
ON절은 where절보다 순서상으로 더 빨리 처리된다. 이는 inner join일때는 큰 상관 없는 것처럼 보이지만, outer join의 경우, 함께 사용하면 where절의 필터링을 거치고 남은 데이터중, 값이 null인 데이터가 삭제되어 최종 출력에는 보이지 않게 될수도 있다.
아래 링크를 참조해서 이해하자
https://myjamong.tistory.com/229
[Oracle] ON절과 WHERE절 조건 차이, JOIN 대상 차이
ON절과 WHERE절 차이 조인문을 사용할때 ON절을 이용해서 해당 조건으로 테이블 조인을 하게됩니다. 그런데 JOIN을 하기 위해 ON절이 아닌 WHERE절에 사용할때도 있습니다. 같은 내용같지만 확실히 두
myjamong.tistory.com
INNER JOIN (교집합)
두 테이블에서 특절 열의 값이 일치하는 행(row)만 검색한다.
즉, 두 테이블에 모두 포함된 데이터로 한정해서 테이블을 하나 새로 만든다고 생각하면 편하다. 당연히 null값이 있는 데이터들은 출력되지 않는다.
이를 이용하면 다음과 같이 출력할수도 있다.
SELECT m.mem_name, b.mem_id, b.price, SUM(price * amount) '구매총액'
FROM member m
INNER JOIN buy b
ON m.mem_id = b.mem_id
WHERE prod_name = '지갑'
GROUP BY b.mem_id;
가장 자주 쓰이는 join이나, 동시에 가장 설명할 거리가 없는 join이기도 하다.
SELF JOIN (자체조인)
자기 자신을 참조한다고 보면 된다.
SELECT m1.mem_id, m1.mem_name
FROM member m1
INNER JOIN member m2
ON m1.mem_id = m2.mem_id
ORDER BY m1.mem_id;다음과 같은 형식으로 사용할수 있다.
