DAY 02 : CRUD, 연산자, 집계 함수, join, view

- CRUD

CREATE, INSERT / SELECT / UPDATE / DELETE

 

 

- 데이터 수정

# 해당 열의 값들이 전체가 다 바뀜
UPDATE 테이블명 SET 필드명1 = 값1, 필드명2 = 값2,...

# 조건에 맞는 값들만 바뀜
UPDATE 테이블명 SET 필드명1 = 값1, 필드명2 = 값2 , ... WHERE 조건절

Mysql의 경우 처음 UPDATE를 하면 오류 메시지가 뜬다. 따라서 아래처럼 해주면 뜨지 않는다.

 To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. : Safe 모드 작동으로 update, delete 안됨
메뉴 -> edit -> Preferences -> SQL Editor -> Safe Updates 체크 없앰

 

 

---

UPDATE 구문 사용해보기!

 

 

반하나 주소 바꾸기, 채애리 취미 바꾸기

UPDATE tb_member SET mem_address1 = '경기도', mem_address2 = '수원시', mem_address3 = '영통구' 
		WHERE mem_userid ='banana';
UPDATE tb_member SET mem_hobby = '음악듣기' WHERE mem_userid ='cherry';

반하나 데이터 수정

이채리 데이터 수정

---

- SQL 연산자

 

- 산술 연산자

덧셈	+
뺄셈	-
나누기	/ , div (나누기를 한 후에 소수부분을 버림)
곱셈	*
나머지 연산	mod

 

 

- 대입 연산자

     =

 

 

- 비교 연산자

=, <, >, >=, <= , <>	= (같다),  <> (다르다) , 나머지는 일반적으로 쓰이는 비교 연산
IS  (A IS B)	A와 B가 같으면 참, 다르면 거짓
BETWEEN A AND B	값이 A보다 크거나 같고, B보다 작거나 같으면 참, 아니면 거짓
IN( )	연산자의 값이 매개변수로 전달받은 리스트에 존재하면 참
LIKE	패턴으로 문자열 검사

 

- 논리 연산자 

AND, OR, XOR, NOT

 

 

---

연산자 사용해보기!

위에 결과는 멤버의 아이디, 이름, 포인트만 검색한 결과이다. 

 

 

OR, IN 연산

# OR 과 IN 을 사용해서 포인트가 300,400,500 인 멤버의 아이디, 이름, 포인트 검색하기!
SELECT mem_userid, mem_name, mem_point FROM tb_member 
		WHERE mem_point = 300 OR mem_point = 400 OR mem_point = 500;
SELECT mem_userid, mem_name, mem_point FROM tb_member WHERE mem_point IN (300,400,500);

위 코드의 결과

조건절에서 쓰인 연산자가 다르지만, 같은 검색 결과가 나오는 것을 확인할 수 있다.

 

 

BETWEEN 연산

# BETWEEN A AND B를 이용해서 포인트가 300 이상 500이하의 멤버 아이디, 이름, 포인트 검색하기!
SELECT mem_userid, mem_name, mem_point FROM tb_member 
		WHERE mem_point BETWEEN 300 AND 500;
SELECT mem_userid, mem_name, mem_point FROM tb_member 
		WHERE mem_point >= 300 AND mem_point <= 500;

위 코드의 결과

위의 Between 연산으로 검색한 결과이다. between a and b는 a이상 b 이하 이기 때문에 바로 아래 코드로도 표현할 수 있다.

 

 

LIKE 연산

#LIKE 를 이용해 아이디에 a가 들어간 멤버 검색하기
SELECT mem_userid, mem_name FROM tb_member WHERE mem_userid LIKE '%a%';

# LIKE를 이용해 아이디가 y 로 끝나는 멤버 검색하기
SELECT mem_userid, mem_name FROM tb_member WHERE mem_userid LIKE '%y';

첫 번째 코드의 결과

두 번째 코드의 결과

 

---

- 데이터 삭제

DELETE FROM 테이블명

DELETE FROM 테이블명 WHERE 조건절

 

 

- 데이터 검색

SELECT 필드1, 필드2, ... FROM 테이블명

SELECT 필드1, 필드2, ... FROM 테이블명 WHERE 조건절

 

 

 ✔️ 별칭

SELECT 컬럼명 AS ['변경할 컬럼명'] FROM 테이블명

SELECT 컬럼명 FROM 테이블명 AS '별칭'

컬럼명 뿐만 아니라 테이블, 함수, 서브 쿼리 뒤에 AS 써주고 변경할 별칭을 써줘도 사용이 가능하다.

여기서 주의점은 띄어쓰기가 없는경우에는 ' ' (따옴표)를 따로 써주지 않아도되지만, 띄워쓰기가 있는 경우에는 꼭 써줘야 한다.

따라서 기본적으로 따옴표를 써서 별칭을 주는 것이 좋다.

 

---

- 데이터 정렬

SELECT 필드1, 필드2, ... FROM 테이블명 [WHERE 조건절] 
ORDER BY 필드 [asc : 오름차순] / desc : 내림차순

# ASC는 생략 가능

 

 

- LIMIT

# MySQL에만 존재
SELECT 필드1, 필드2, ... FROM 테이블명 LIMIT 가져올 행의 갯수
SELECT 필드1, 필드2, ... FROM 테이블명 LIMIT 시작행, 가져올 행의 갯수

 

 

 

---

정렬 사용해보기!

 

 

ORDER BY

# ORDER BY 로 포인트 오름차순 정렬
SELECT mem_userid, mem_name, mem_point FROM tb_member ORDER BY mem_point ASC;

# ORDER BY로 포인트 내림차순 정렬
SELECT mem_userid, mem_name, mem_point FROM tb_member ORDER BY mem_point DESC;

첫 번째 코드의 결과

두 번째 코드의 결과

ORDER BY는 기본이 ASC (오름차순) 이기 때문에 ASC는 생략해도 같은 결과를 얻을 수 있다.

 

 

LIMIT

# LIMIT로 3개의 행 검색
SELECT mem_idx, mem_userid, mem_name FROM tb_member LIMIT 3;

# LIMIT로 2번째 행부터 2개의 행 검색
SELECT mem_idx, mem_userid, mem_name FROM tb_member LIMIT 1,2;

첫 번째 코드의 결과

두 번째 코드의 결과

 

---

- 집계 함수

COUNT()	행의 갯수를 세는 함수
DISTINCT()	뒤에 나오는 필드에 대하여 같은 값을 중복된 행을 제외
SUM()	행의 값을 더해주는 함수
AVG()	행의 평균값을 구하는 함수
MIN()	행의 최소 값을 구하는 함수
MAX()	행의 최대값을 구하는 함수

 

 

- GROUP BY

SELECT 그룹을 맺은 컬럼 혹은 집계함수 FROM 테이블명 [WHERE 조건절] 
GROUP BY 필드 HAVING 조건절 ORDER BY 필드 [asc] / desc

 

 

---

집계 함수, group by 사용해보기!

 

DISTINCT

#  취미 중복 제거 하기
SELECT distinct mem_hobby FROM tb_member;

위 코드의 결과

중복인 음악 듣기가 하나만 나오는 것을 확인할 수 있다.

 

 

SUM(), AVG() 

# 성별로 point의 합, 평균구하기
SELECT SUM(mem_point) FROM tb_member WHERE mem_gender ='남자';
SELECT SUM(mem_point) FROM tb_member WHERE mem_gender ='여자';

SELECT AVG(mem_point) FROM tb_member WHERE mem_gender='남자';
SELECT AVG(mem_point) FROM tb_member WHERE mem_gender='여자';

위 코드의 결과

 

 

GROUP BY

# 문제 : 포인트가 100이상인 회원의 집합에서 남자, 여자로 그룹을 나눠 
# 포인트의 평균을 구하고 포인트가 300이상인 성별을 검색하여 포인트로 내림차순 정렬
select mem_gender, avg(mem_point) as avg from tb_member 
	where mem_point >= 100 group by mem_gender having avg >= 300 order by avg desc;

위 코드의 결과

 

---

- 조인 (JOIN)

SELECT 필드1, 필드2, ...  FROM 테이블1 [INNER, LEFT, RIGTH] JOIN 테이블2 
ON 테이블1.필드 = 테이블2.필드 (두개의 공통된 필드를 써야됨)

 

 

---

조인 사용해보기!

 

INNER 

# tb_order.idx와 tb_member.idx이 같다는 조건으로 inner 조인
 SELECT mem_idx, mem_userid ID, mem_name NAME, mem_hp HP, or_num NUM, or_zipcode ZIPCODE 
 	FROM tb_member INNER JOIN tb_order ON tb_member.mem_idx = tb_order.or_idx;

위 코드의 결과

or_idx와 mem_idx가 같은 것은 1,4 이기 때문에 1,4인 것들만 검색된 것을 확인할 수 있다.

 

 

LEFT

 SELECT mem_idx IDX,mem_userid ID, mem_name NAME, mem_hp HP, or_num NUM, or_zipcode ZIPCODE 
 	FROM tb_member LEFT JOIN tb_order ON tb_member.mem_idx = tb_order.or_idx;

위 코드의 결과

or_idx와 mem_idx가 같은 멤버뿐만 아니라 tb_member의 모든 멤버가 검색되는 것을 확인할 수 있다.

 

 

RIGHT

 SELECT mem_idx IDX,mem_userid ID, mem_name NAME, mem_hp HP, or_num NUM, or_zipcode ZIPCODE 
 	FROM tb_member RIGHT JOIN tb_order ON tb_member.mem_idx = tb_order.or_idx;

위 코드의 결과

같은 조건과 별개로 tb_order의 모든 주문이 다 검색된 것을 확인할 수 있다.

 

---

- 뷰 (VIEW)

가상의 테이블을 생성한다.

실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 저장하고 있지는 않다.

삽입, 삭제, 수정 작업에 제한 사항을 가진다.

자신만의 인덱스를 가질 수 없다.

한번 정의된 VIEW는 변경할 수 없다.

 

사용 이유 : SQL 코드를 간결하게 만들기 위함, 보안상 내부 데이터를 전체 공개하고 싶지 않을 때 사용한다.

 

 

- 뷰 생성 

CREATE VIEW 뷰이름
AS
SELECT 쿼리문..

 

 

- 뷰 대체 

CREATE OR REPLACE VIEW 뷰이름
AS
SELECT 쿼리문...

 

 

- 뷰 삭제

DROP VIEW 뷰이름

 

---

뷰 사용해보기!

# tb_member view 만들고 검색하기
CREATE VIEW vw_member_simple AS
	SELECT mem_idx, mem_userid, mem_userpw, mem_name, mem_hp FROM tb_member;
    
SELECT * FROM vw_member_simple;

select의 결과

tb_member는 주소 나 성별이 들어가 있지만, vw_member_simple의 경우 idx, userid, userpw, name, hp 만 검색해서

만들었기 때문에 * 로 검색해도 4개의 칼럼만 나오는 것을 확인할 수 있다.