[4기] 52일차 SQL(기본명령어 DML, 내장함수, 그룹화, JOIN)

오늘의 학습 SQL2!!

 

 

SELECT now() 를 사용하면 오늘의 날짜를 반환해준다! 

 

SELECT to_char(now(), 'yyyy-mm-dd hh:mi:ss')
//연도 : yyyy, 월 : mm, 일자 : dd 시간 : hh 분 : mi 초 : ss
//mySQL 연도 : %y, 월 %m, 일자 : %d 로 표시

 

 

삽입

 

INSERT INTO

 

테이블에 새로운 행을 삽입할때는 INSERT INTO 사용한다.

 

 

한 행 삽입

 

INSERT INTO students (name, age, address)
VALUES ('김이박', 40, '서울특별시');

 

students 테이블에 새로운 학생 삽입

 

 

한 행 삽입 + 특정 열만

INSERT INTO students (name, age)
VALUES ('신기루', 41);

 

address값에 아무것도 삽입하지 않을려고 할때? 

 

실행하면?

 

에러가 발생한다. 

 

 

우리가 테이블을 작성하였을때 

 

CREATE TABLE students (
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  address VARCHAR(255) NOT NULL
);

not null이라는 속성으로 세팅하였기때문에 

 

반드시 데이터를 입력해야합니당!

 

만약 not null 이아니였다면?

 

 

 

여러 행 삽입

 

INSERT INTO students (name, age, address)
VALUES ('학생1',20,'경기도'),('학생2',22,'경기도'),('학생3',23,'경기도')

 

여러행을 한번에 삽입하는 것을 bulk insert라고한다.

 

 

조회 후 삽입

 

INSERT INTO students (name, age, address)
SELECT name,age,address FROM students WHERE age < 30;

 

students에서 30살 미만의 학생들을 조회후 INSERT INTO 에서

조회한 학생정보를 삽입한다.

 

그럼 실행결과는? 

 

30살미만의 학생들의 정보고 중복으로 존재하겠졍?

 

 

 

수정

 

UPDATE, SET, WHERE

UPDATE	수정할 테이블을 지정
SET	데이터 수정
WHERE	수정할 데이터를 필터링

 

 

필드 한 개 수정

 

UPDATE students
SET age = 99
WHERE name = '이황';

 

 

이황이 99살됐당 ㅎㅎㅎㅋㅋㅋㅋㅋㅋㅋ

 

 

필드 여러 개 수정

UPDATE students
SET age = 10,
    address = '서울특별시'
WHERE name = '정약용';

 

 

한번에 나이를 10살, 주소를 서울특별시로 정약용 수정완료!

 

 

조건부 수정

 

UPDATE students
SET address = '인천광역시'
WHERE age < 33;

 

students 테이블에서 주소를 인천광역시로 수정한다. 나이 33살 미만 학생들을 

 

실행하면?

 

 

짜라란!!!!! ㅋㅋㅋㅋㅋㅋㅋㅋ SQL 실습하니깐 잼네용ㅋㅋㅋㅋㅋㅋㅋㅋㅎㅎㅎ

 

자바만 하다가 해서그런강 ㅎㅎㅎㅋㅋㅋ

 

 

삭제

 

DELETE

DELETE FROM	삭제할 테이블을 지정
WHERE	삭제할 데이터를 필터링

 

 

특정 행 삭제

DELETE
FROM students
WHERE name ='이황'

 

 

이황 실종!!! ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

사라졌당 !!!!!

 

 

 

여러 행 삭제

DELETE
FROM students
WHERE age BETWEEN 30 AND 33;

 

삭제한다 / student 테이블에서 / 나이가 30~ 33살 사이인 학생들을

 

실행하면?

 

 

나이 30~33살 사이 학생들이 전부 사라집니당 ㅎㅎ

 

 

모든 행 삭제

DELETE
FROM students;

 

 

테이블의 모든 데이터 훠이훠이~~~~~~ 삭제 되었습니다!!

 

 

내장 함수

 

함수란?

- 입력 값을 받아 계산을 수행하고 결과를 반환하는 구조

 

 

내장 함수란?

 

- DBMS에서 기본적으로 제공하는 함수 

 

 

자주 사용하는 내장 함수들

 

SUM

- 숫자의합을 반환한다.

 

SELECT sum(age) FROM students

 

 

AVG

- 숫자의 평균을 반환한다.

 

SELECT avg(age) FROM students

 

 

 

MAX

-숫자의 최대값

SELECT max(age) FROM students

 

 

 

MIN

-숫자의 최소 값

SELECT min(age) FROM students

 

 

COUNT

-행의 개수를 반환한다.

SELECT count(DISTINCT address) FROM students

 

 

중복을 제거한 값이 몇개인지! 경상북도,경기도,전라북도,서울특별시,경상남도 5개이다

 

 

CONCAT

- 두문자를 연결한다.

 

SELECT CONCAT(name, address) FROM students;

 

 

 

LENGTH

 

- 문자열의 길이를 반환한다.

SELECT address, length(address) FROM students

 

 

REPLACE

- 특정 문자열을 다른 문자열로 치환

 

SELECT replace(address,'도','레')FROM students

 

주소에서 도라는 문자열을 레로 바꾸는것! 

 

 

 

그룹화, 정렬

 

그룹화?

- 같은 값을 가진 행끼리 하나의 그룹으로 뭉치는 기능 

 

 

학생테이블에 있는 데이터 주소를 기준으로 그룹을 나누고 싶을때, 주소별로 몇명의 학생이 있는지

 

위 그림과 같은 과정을 거치는데 그룹화를 잘 이용하면 다양한 기준으로 데이터를 분석가능 

 

 

GROUP BY

SELECT 열, 집계함수
FROM 테이블
[WHERE 필터 조건]
GROUP BY 열

 

- GROUP BY절은 FROM, WHERE 절 뒤에 위치한다.

- GROUP BY 절은 어떤 열을 기준으로 그룹화할지 명시하여야한다.

-GROUP BY절에 명시된 열은 SELECT절에도 존재해야한다.

- WHERE절 실행 후에 GROUP BY 가 실행

 

 

주소별 학생 수

주소를 기준으로 그룹핑하고, 그룹별 학생수를 구한다.

 

SELECT address, count(*)
FROM students
GROUP BY address

 

 

 

주소별 학생 평균 나이

SELECT address, AVG(age)
FROM students
GROUP BY address

 

 

 

HAVING

- GROUP BY절에 의해 생성된 그룹 중 원하는 조건에 부합하는 그룹만을 선택하는 구문

 

SELECT 열, 집계함수
FROM 테이블
[WHERE 필터 조건]
GROUP BY 열
HAVING 그룹 필터 조건

 

 

학생 수가 2명 이상인 주소만 조회하고 싶을 때

SELECT address, COUNT(*)
FROM students
GROUP BY address
HAVING count(*) >= 2

 

 

 

ORDER BY

- 특정 기준에 따라 정렬하는 구문

 

- 실행 순서: WHERE -> GROUP BY -> HAVING -> ORDER BY

- 어떤 열을 기준으로 정렬할 지 명시

- 명시된 열은 SELECT 절에 존재하지 않아도 됨 GROUP BY 차이점

- ASC : 오름차순 DESC : 내림차순

 

SELECT *
FROM students
ORDER BY age DESC

 

 

나이가 많은 학생 TOP 3만 조회

SELECT *
FROM students
ORDER BY age DESC
LIMIT 3

 

TOP3 까지이므로 LIMIT로 3명 제한을 결면 된다!

 

 

 

GROUP BY + HAVING + ORDER BY

SELECT address, count(*)
FROM students
WHERE age >= 29
GROUP BY address
HAVING  count(*) >= 2
ORDER BY count(*) DESC;

 

1. (WHERE) 나이가 29세 이상인 학생만 필터링

2. (GROUP BY) 주소를 기준으로 그룹핑

3. (HAVING) 학생 수가 2명 이상인 그룹만 필터링

4.(ORDER BY) 학생 수가 많은 순서대로 정렬

 

 

 

JOIN, UNION

 

JOIN

- 테이블을 연결하기 위해 key가 존재해야함

name이라는 열을 이용하여 students와 classes를 엮어보자!

 

두 테이블의 관계는 1: N 한 학생이 여러개의 강의를 수강할 수 있다는 의미!

 

 

INNER JOIN

SELECT *
FROM students AS A
    INNER JOIN classes AS B
    ON A.name = B.name;

 

 

왜 한용운과 박두진 학생은 조회가 되지 않았을까?

 

그이유는 두테이블 모두가 지정한 열의 데이터가 존재해야 조인된다. 

즉 students에는 있지만 classes에는 없기때문에 조인결과에 보이지 않았다는거 

 

즉 교집합을 생각하면 같은 개념이라는거!

 

그럼 이 두명을 다보이게하려면?

 

FULL OUTER JOIN

SELECT *
FROM students AS A
    FULL OUTER JOIN classes AS B
    ON A.name = B.name;

 

짜잔!!!

 

안보이던게 보이는것은? 아 FULL OUTER JOIN은 합집합과 같은 개념이군! 알수있져?

 

 

OUTER JOIN의 종류

 

 

UNION

[SQL 1]
UNION
[SQL 2];

 

<조건>

- SQL 1과 SQL 2의 열 개수가 같아야 한다.

- SQL 1과 SQL 2의 열 이름이 같아야 한다.

- SQL 1과 SQL 2의 각 열의 데이터 타입이 동일해야한다.

 

SELECT name, age FROM students WHERE age < 30
UNION
SELECT name, age FROM students WHERE age < 32;

 

 

 

UNION ALL

- UNION 과 다른점은 중복 제거 여부 이다.

 

 UNION의 경우 중복이 제거 된 상태로 보여지지만 UNION ALL은 제거하지않고 모두보여준다.

 

 

중복제거없이 다보여주는모습!!!!! ㅎㅎㅎㅎ

 

ㅎㅎ원래는 조인의 기본 개념까지만 간단하게 봤지만

 

예습 해보고자 자습시간에 한번 다 해봤습니당!!

 

이론보다는 실습 짱짱 ㅋㅋㅋㅋ

 

나름 재밌네요 ㅎㅎㅎ

 

ㅋㅋㅋㅋㅋㅎㅎㅎㅎ 작성하면서 하니 

 

더욱 재밌고 공부에 집중이 잘된거같아요!!! ㅎㅎㅎ 이해하기도 쉽고 ㅎㅎㅎ

 

오늘도 열심히 공부했구!!

 

내일도 파이팅!!!!!!!! 

 

 

오늘은 장보러 가야해서 밥먹구 SQL 자격증 공부하고 자야겠어요!!!!