MySQL

DBMS는 서버-클라이언트 구조로 동작

서버 역할을 하는 하나의 프로그램이 실행되고 있고, 사용자는 클라이언트 역할을 하는 프로그램으로

해당 서버에 접속하여 그 서버에 SQL을 전송해서 명령을 내리는 구조-클라이언트 구조로 동작

 

 

 

CSV파일로 테이블 생성하기

Table Data Import

csv의 구분자 ,로 설정하는 것 주의

 

 

 

Primary Key(PK) - not null(NN)

Type 1 ) Surrogate Key : PK로 사용하기 위해 인위적으로 생성한 컬럼. 주로 1부터 순차적으로 증가하는 숫자가 들어감.

     Auto Increment(AI) - 자동 증가, MySQL이 자동으로 관리하는 컬럼

Type 2) Natural key는 그 값이 나중에 변경되면 모든 row의 값을 수정해야 한다는 문제가 있기에 보통 Surrogate key를 선택

 

 

 

 

1. 쿼리 창 생성 아이콘
2. 쿼리 창 탭
3. 쿼리 창 - SQL 입력
4. SQL문 실행 아이콘 (shift + cntl+ enter)
5. 새로고침
6. 테이블 조회
   
   1. 스패너 모양 : 해당 테이블의 컬럼과 각 컬럼의 데이터 타입 등을 볼 수 있음
   2. 표 모양 : 테이블의 전체 row를 조회할 수 있음
7. SQL 문으로 테이블의 데이터를 조회했을 때 그 조회 결과가 출력되는 영역
8. SQL 문이 잘 실행되었는지 알려주는 영역. 

 

 

코드잇의 'SQL 실행기'는 사용자가 이미 하나의 데이터베이스를 선택했다고 가정한 상태에서 작동합니다.

따라서 테이블을 나타낼 때는, 테이블 이름만 적으셔야 하고, 데이터베이스 이름은 적으시면 안 됩니다. 

예를 들어 member라는 테이블을 나타내고 싶을 때

SELECT * FROM copang_main.member; 라고 작성하시면 안 되고, 

SELECT * FROM member; 라고 작성하셔야 하는데요. 

영상에서는 테이블 이름을 나타낼 때 데이터베이스 이름을 함께 쓸 때도 있고 안 쓸 때도 있지만, ‘SQL 실행기’를 사용할 때는 꼭 테이블 이름만 써주세요!

참고로, ‘SQL 실행기’ 영역을 잠깐 가리고 영상을 좀더 크게 보고 싶으시면 아래 이미지의 빨간 박스로 표시된 버튼을 클릭하시면 됩니다.

 

 

 

SQL 문 끝에는 항상 세미콜론!

데이터베이스 이름 . 테이블 이름

 

 

 

SQL 작성 순서

 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
      
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
   [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

 

 

문자열 패턴 매칭 조건

% 임의의 길이를 가진 문자열

_   한 자리의 문자

해당 글자를 직접적으로 찾아야 한다면 이스케이프 코드 \ 백슬래시 사용

 

 

WHERE address LIKE "서울%"

문자열의 처음이 서울로 시작하는 행을 선택하는 쿼리문

 

WHERE address LIKE "%고앙시%"

문자열에 고양시가 포함되는 행을 선택하는 쿼리문

 

WHERE address LIKE "%__구%"

_로 적으면 글자 개수를 제한하여 검색

 

 

범위에 놓인 값, 범위 사이 값

column IN ( , , , ...)     BETWEEN a AND b

 

+) BINARY - 대소문자를 구별

'%g%' -> BINARY '%g%'

 

날짜 추출

년: YEAR(birthday) = '1992'

월: MONTH(sign_up_day) IN (6,7,8)

일 : DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31

시간: UNIX_TIMESTAMP(sign_up_day) - 1970년 1월 1일을 기준으로 총 몇 초 지났는지 나타낸 값

    FROM_UNIXTIME( UNIX_TIMESTAMP(sign_up_day) ) - DATETIME처럼 모양 예쁘게 해서 출력

 

 날짜 간 차이 구하기

DATEDIFF(날짜 A, 날짜 B)  => B일로 부터 A일 까지 날짜 차이 계산

 

 날짜 덧셈 뺄셈

DATE_ADD( sign_up_day, INTERVAL 300 DAY ) 

가입일 기준 300일 이후의 날짜 구하기

DATE_SUB( sign_up_day, INTERVAL 250 DAY ) 

가입일 기준 250일 이전의 날짜 구하기

 

 오늘의 날짜 확인

SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM cloth_main.member;

 

 +) 가입 당시 회원들의 나이 알아보기

가입일 - 생일 / 365

SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) / 365 FROM cloth_main.member;

 

 

 

정렬 ORDER BY

오름차순 ASC (기본값)

내림차순 DESC

 

가입년도가 최신인 사람부터 출력하되 동일년도인 경우 email을 기준으로 오름차순을 정렬하는 내용

SELECT email, sign_up_day FROM cloth_main.member

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

 

데이터 타입 변환 (일시적)

 

signed : 양수 음수 포함한 모든 정수를 나타내는 데이터 타입

ORDER BY CAST(column AS signed);

 

decimal 소수점 포함

ORDER BY CAST(height AS decimal);

 

 

데이터 일부만 추려보기 - LIMIT

10개의 데이터만 보이기

SELECT email, sign_up_day FROM cloth_main.member

ORDER BY YEAR(sign_up_day) DESC, email ASC;

LIMIT 10;

 

 

8번째 행(실제로는 9번째)부터 2개의 데이터만 보이기

LIMIT 8, 2;

 

사용 예시

주로 검색 결과창이나, 게시판 페이지에서 볼 수 있습니다. 우리는 1, 2, 3 버튼을 눌러가면서 한 페이지씩 그 내용들을 살펴보곤 하죠?

매 페이지에는 아래와 같은 식으로 서로 다른 내용들이 담겨있습니다.

• 1페이지 : 1~10 번까지의 내용
• 2페이지 : 11~20 번까지의 내용
• 3페이지 : 21~30 번까지의 내용
• 4페이지 : 31~40 번까지의 내용 ...

우리가 새로운 페이지를 누르면 그때마다, 10개의 새로운 내용들을 로드(load)할 텐데요. 이런 걸 전문 용어로 '페이지네이션(Pagination)'이라고 합니다. 전체 결과를 한번에 로드하는 게 아니라 이렇게 페이지 단위로 쪼개서 그때그때 요청이 있을 때마다 부분 결과를 조금씩 로드하는 방식을 말하죠. 

 

더불어 스크롤링 하여 댓글을 계속 보여주는 무한 로딩식 페이지BMS는 서버-클라이언트 구조로 동작

 

서버 역할을 하는 하나의 프로그램이 실행되고 있고, 사용자는 클라이언트 역할을 하는 프로그램으로

 

해당 서버에 접속하여 그 서버에 SQL을 전송해서 명령을 내리는 구조-클라이언트 구조로 동작

 

 

 

 

 

 

 

CSV파일로 테이블 생성하기

 

Table Data Import

 

csv의 구분자 ,로 설정하는 것 주의

 

 

 

 

 

 

 

Primary Key(PK) - not null(NN)

 

Type 1 ) Surrogate Key : PK로 사용하기 위해 인위적으로 생성한 컬럼. 주로 1부터 순차적으로 증가하는 숫자가 들어감.

 

     Auto Increment(AI) - 자동 증가, MySQL이 자동으로 관리하는 컬럼

 

Type 2) Natural key는 그 값이 나중에 변경되면 모든 row의 값을 수정해야 한다는 문제가 있기에 보통 Surrogate key를 선택

 

 

 

 

 

 

 



 

 

쿼리 창 생성 아이콘

쿼리 창 탭

쿼리 창 - SQL 입력

SQL문 실행 아이콘 (shift + cntl+ enter)

새로고침

테이블 조회

스패너 모양 : 해당 테이블의 컬럼과 각 컬럼의 데이터 타입 등을 볼 수 있음

표 모양 : 테이블의 전체 row를 조회할 수 있음

SQL 문으로 테이블의 데이터를 조회했을 때 그 조회 결과가 출력되는 영역

SQL 문이 잘 실행되었는지 알려주는 영역. 

 

 

 

 

코드잇의 'SQL 실행기'는 사용자가 이미 하나의 데이터베이스를 선택했다고 가정한 상태에서 작동합니다.

 

따라서 테이블을 나타낼 때는, 테이블 이름만 적으셔야 하고, 데이터베이스 이름은 적으시면 안 됩니다. 

 

예를 들어 member라는 테이블을 나타내고 싶을 때

 

SELECT * FROM copang_main.member; 라고 작성하시면 안 되고, 

 

SELECT * FROM member; 라고 작성하셔야 하는데요. 

 

영상에서는 테이블 이름을 나타낼 때 데이터베이스 이름을 함께 쓸 때도 있고 안 쓸 때도 있지만, ‘SQL 실행기’를 사용할 때는 꼭 테이블 이름만 써주세요!

 

참고로, ‘SQL 실행기’ 영역을 잠깐 가리고 영상을 좀더 크게 보고 싶으시면 아래 이미지의 빨간 박스로 표시된 버튼을 클릭하시면 됩니다.

 

 

 

 

 

 

 

SQL 문 끝에는 항상 세미콜론!

 

데이터베이스 이름 . 테이블 이름

 

 

 

 

 

 

 

SQL 작성 순서

 

 

 

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

    [HIGH_PRIORITY]

    [STRAIGHT_JOIN]

    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr [, select_expr] ...

    [into_option]

    [FROM table_references

      [PARTITION partition_list]]

      

[WHERE where_condition]

[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

   [HAVING where_condition]

    [WINDOW window_name AS (window_spec)

        [, window_name AS (window_spec)] ...]

[ORDER BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [into_option]

    [FOR {UPDATE | SHARE}

        [OF tbl_name [, tbl_name] ...]

        [NOWAIT | SKIP LOCKED]

      | LOCK IN SHARE MODE]

    [into_option]

 

into_option: {

    INTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        export_options

  | INTO DUMPFILE 'file_name'

  | INTO var_name [, var_name] ...

}

 

 

문자열 패턴 매칭 조건

% 임의의 길이를 가진 문자열

 

_ 한 자리의 문자

 

해당 글자를 직접적으로 찾아야 한다면 이스케이프 코드 \ 백슬래시 사용

 

 

 

 

 

WHERE address LIKE "서울%"

 

문자열의 처음이 서울로 시작하는 행을 선택하는 쿼리문

 

 

 

WHERE address LIKE "%고앙시%"

 

문자열에 고양시가 포함되는 행을 선택하는 쿼리문

 

 

 

WHERE address LIKE "%__구%"

 

_로 적으면 글자 개수를 제한하여 검색

 

 

 

 

 

범위에 놓인 값, 범위 사이 값

 

column IN ( , , , ...) BETWEEN a AND b

 

 

 

+) BINARY - 대소문자를 구별

 

'%g%' -> BINARY '%g%'

 

 

 

날짜 추출

년: YEAR(birthday) = '1992'

 

월: MONTH(sign_up_day) IN (6,7,8)

 

일 : DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31

 

시간: UNIX_TIMESTAMP(sign_up_day) - 1970년 1월 1일을 기준으로 총 몇 초 지났는지 나타낸 값

 

    FROM_UNIXTIME( UNIX_TIMESTAMP(sign_up_day) ) - DATETIME처럼 모양 예쁘게 해서 출력

 

 

 

 날짜 간 차이 구하기

 

DATEDIFF(날짜 A, 날짜 B) => B일로 부터 A일 까지 날짜 차이 계산

 

 

 

 날짜 덧셈 뺄셈

 

DATE_ADD( sign_up_day, INTERVAL 300 DAY ) 

 

가입일 기준 300일 이후의 날짜 구하기

 

DATE_SUB( sign_up_day, INTERVAL 250 DAY ) 

 

가입일 기준 250일 이전의 날짜 구하기

 

 

 

 오늘의 날짜 확인

 

SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM cloth_main.member;

 

 

 

 +) 가입 당시 회원들의 나이 알아보기

 

가입일 - 생일 / 365

 

SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) / 365 FROM cloth_main.member;

 

 

 

 

 

 

 

정렬 ORDER BY

오름차순 ASC (기본값)

 

내림차순 DESC

 

 

 

가입년도가 최신인 사람부터 출력하되 동일년도인 경우 email을 기준으로 오름차순을 정렬하는 내용

 

SELECT email, sign_up_day FROM cloth_main.member

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

 

 

 

데이터 타입 변환 (일시적)

 

 

signed : 양수 음수 포함한 모든 정수를 나타내는 데이터 타입

 

ORDER BY CAST(column AS signed);

 

 

 

decimal 소수점 포함

 

ORDER BY CAST(height AS decimal);

 

 

 

 

 

데이터 일부만 추려보기 - LIMIT

10개의 데이터만 보이기

 

SELECT email, sign_up_day FROM cloth_main.member

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

LIMIT 10;

 

 

 

 

 

8번째 행(실제로는 9번째)부터 2개의 데이터만 보이기

 

LIMIT 8, 2;

 

 

 

사용 예시

 

주로 검색 결과창이나, 게시판 페이지에서 볼 수 있습니다. 우리는 1, 2, 3 버튼을 눌러가면서 한 페이지씩 그 내용들을 살펴보곤 하죠?

 

매 페이지에는 아래와 같은 식으로 서로 다른 내용들이 담겨있습니다.

 

1페이지 : 1~10 번까지의 내용

2페이지 : 11~20 번까지의 내용

3페이지 : 21~30 번까지의 내용

4페이지 : 31~40 번까지의 내용 ...

우리가 새로운 페이지를 누르면 그때마다, 10개의 새로운 내용들을 로드(load)할 텐데요. 이런 걸 전문 용어로 '페이지네이션(Pagination)'이라고 합니다. 전체 결과를 한번에 로드하는 게 아니라 이렇게 페이지 단위로 쪼개서 그때그때 요청이 있을 때마다 부분 결과를 조금씩 로드하는 방식을 말하죠. 

 

 

 

더불어 스크롤링 하여 댓글을 계속 보여주는 무한 로딩식 페이지 또한 가능합니다.

 

 

산술 함수

SUM()

COUNT()

AVG()평균, STD()표준편차

ROUND()반올림

MAX(), MIN()

SORT() 제곱근,

CEIL() 올림, FLOOR() 내림

 

 

NULL 다루기

WHERE column IS NULL -> NULL값이 들어간 ROW만 추출

WHERE column IS NOT NULL -> NULL값 제외하기

 

COALESCE() 합치다. column에 null이 들어있다면 "###"이 출력되도록 함

SELECT COALESCE(colname, "###") FROM member;

 

IFNULL(COLNAME, '###")

IF(COLNAME IS NOT NULL,  COLNAME, '###") -> (조건문,참,거짓)

 

 

!!주의

IS NULL 과 = NULL은 다르다

NULL은 어떤 값이 아니기 때문에 애초에 등호를 사용해서 비교할 수 있는 대상이 아님

 

 

이상한 값 제외 하기

WHERE age BETWEEN 5 AND 100;

WHERE address NOT LIKE '%호';

 

 

 

컬럼끼리 계산하기

+ - * / %

NULL이 포함된 계산식은 항상 NULL

 

컬럼에 alias 붙이기 => AS COLNAME

컬럼 이름에 공백이 포함된다면 '' 문자열 표식 안에 적어주기

 

CONCAT( COLNAME1, "동", COLNAME2, "호")

요렇게 나만의 형식을 갖춘 COLUMN 생성 가능

 

 

!!주의 SELECT 절에서 생성한 ALIAS는 WHERE, CASE 등에서 곧바로 사용 불가능

왜냐면 ALIAS는 SELECT절 안에서 설정했기 때문에 다른 곳에서는 인식 불가능는 하나의 프로그램이 실행되고 있고, 사용자는 클라이언트 역할을 하는 프로그램으로

 

해당 서버에 접속하여 그 서버에 SQL을 전송해서 명령을 내리는 구조-클라이언트 구조로 동작

 

 

 

 

 

 

 

CSV파일로 테이블 생성하기

 

Table Data Import

 

csv의 구분자 ,로 설정하는 것 주의

 

 

 

 

 

 

 

Primary Key(PK) - not null(NN)

 

Type 1 ) Surrogate Key : PK로 사용하기 위해 인위적으로 생성한 컬럼. 주로 1부터 순차적으로 증가하는 숫자가 들어감.

 

     Auto Increment(AI) - 자동 증가, MySQL이 자동으로 관리하는 컬럼

 

Type 2) Natural key는 그 값이 나중에 변경되면 모든 row의 값을 수정해야 한다는 문제가 있기에 보통 Surrogate key를 선택

 

 

 

 

 

 

 



 

 

쿼리 창 생성 아이콘

쿼리 창 탭

쿼리 창 - SQL 입력

SQL문 실행 아이콘 (shift + cntl+ enter)

새로고침

테이블 조회

스패너 모양 : 해당 테이블의 컬럼과 각 컬럼의 데이터 타입 등을 볼 수 있음

표 모양 : 테이블의 전체 row를 조회할 수 있음

SQL 문으로 테이블의 데이터를 조회했을 때 그 조회 결과가 출력되는 영역

SQL 문이 잘 실행되었는지 알려주는 영역. 

 

 

 

 

코드잇의 'SQL 실행기'는 사용자가 이미 하나의 데이터베이스를 선택했다고 가정한 상태에서 작동합니다.

 

따라서 테이블을 나타낼 때는, 테이블 이름만 적으셔야 하고, 데이터베이스 이름은 적으시면 안 됩니다. 

 

예를 들어 member라는 테이블을 나타내고 싶을 때

 

SELECT * FROM copang_main.member; 라고 작성하시면 안 되고, 

 

SELECT * FROM member; 라고 작성하셔야 하는데요. 

 

영상에서는 테이블 이름을 나타낼 때 데이터베이스 이름을 함께 쓸 때도 있고 안 쓸 때도 있지만, ‘SQL 실행기’를 사용할 때는 꼭 테이블 이름만 써주세요!

 

참고로, ‘SQL 실행기’ 영역을 잠깐 가리고 영상을 좀더 크게 보고 싶으시면 아래 이미지의 빨간 박스로 표시된 버튼을 클릭하시면 됩니다.

 

 

 

 

 

 

 

SQL 문 끝에는 항상 세미콜론!

 

데이터베이스 이름 . 테이블 이름

 

 

 

 

 

 

 

SQL 작성 순서

 

 

 

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

    [HIGH_PRIORITY]

    [STRAIGHT_JOIN]

    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr [, select_expr] ...

    [into_option]

    [FROM table_references

      [PARTITION partition_list]]

      

[WHERE where_condition]

[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

   [HAVING where_condition]

    [WINDOW window_name AS (window_spec)

        [, window_name AS (window_spec)] ...]

[ORDER BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [into_option]

    [FOR {UPDATE | SHARE}

        [OF tbl_name [, tbl_name] ...]

        [NOWAIT | SKIP LOCKED]

      | LOCK IN SHARE MODE]

    [into_option]

 

into_option: {

    INTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        export_options

  | INTO DUMPFILE 'file_name'

  | INTO var_name [, var_name] ...

}

 

 

문자열 패턴 매칭 조건

% 임의의 길이를 가진 문자열

 

_ 한 자리의 문자

 

해당 글자를 직접적으로 찾아야 한다면 이스케이프 코드 \ 백슬래시 사용

 

 

 

 

 

WHERE address LIKE "서울%"

 

문자열의 처음이 서울로 시작하는 행을 선택하는 쿼리문

 

 

 

WHERE address LIKE "%고앙시%"

 

문자열에 고양시가 포함되는 행을 선택하는 쿼리문

 

 

 

WHERE address LIKE "%__구%"

 

_로 적으면 글자 개수를 제한하여 검색

 

 

 

 

 

범위에 놓인 값, 범위 사이 값

 

column IN ( , , , ...) BETWEEN a AND b

 

 

 

+) BINARY - 대소문자를 구별

 

'%g%' -> BINARY '%g%'

 

 

 

날짜 추출

년: YEAR(birthday) = '1992'

 

월: MONTH(sign_up_day) IN (6,7,8)

 

일 : DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31

 

시간: UNIX_TIMESTAMP(sign_up_day) - 1970년 1월 1일을 기준으로 총 몇 초 지났는지 나타낸 값

 

    FROM_UNIXTIME( UNIX_TIMESTAMP(sign_up_day) ) - DATETIME처럼 모양 예쁘게 해서 출력

 

 

 

 날짜 간 차이 구하기

 

DATEDIFF(날짜 A, 날짜 B) => B일로 부터 A일 까지 날짜 차이 계산

 

 

 

 날짜 덧셈 뺄셈

 

DATE_ADD( sign_up_day, INTERVAL 300 DAY ) 

 

가입일 기준 300일 이후의 날짜 구하기

 

DATE_SUB( sign_up_day, INTERVAL 250 DAY ) 

 

가입일 기준 250일 이전의 날짜 구하기

 

 

 

 오늘의 날짜 확인

 

SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM cloth_main.member;

 

 

 

 +) 가입 당시 회원들의 나이 알아보기

 

가입일 - 생일 / 365

 

SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) / 365 FROM cloth_main.member;

 

 

 

 

 

 

 

정렬 ORDER BY

오름차순 ASC (기본값)

 

내림차순 DESC

 

 

 

가입년도가 최신인 사람부터 출력하되 동일년도인 경우 email을 기준으로 오름차순을 정렬하는 내용

 

SELECT email, sign_up_day FROM cloth_main.member

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

 

 

 

데이터 타입 변환 (일시적)

 

 

signed : 양수 음수 포함한 모든 정수를 나타내는 데이터 타입

 

ORDER BY CAST(column AS signed);

 

 

 

decimal 소수점 포함

 

ORDER BY CAST(height AS decimal);

 

 

 

 

 

데이터 일부만 추려보기 - LIMIT

10개의 데이터만 보이기

 

SELECT email, sign_up_day FROM cloth_main.member

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

LIMIT 10;

 

 

 

 

 

8번째 행(실제로는 9번째)부터 2개의 데이터만 보이기

 

LIMIT 8, 2;

 

 

 

사용 예시

 

주로 검색 결과창이나, 게시판 페이지에서 볼 수 있습니다. 우리는 1, 2, 3 버튼을 눌러가면서 한 페이지씩 그 내용들을 살펴보곤 하죠?

 

매 페이지에는 아래와 같은 식으로 서로 다른 내용들이 담겨있습니다.

 

1페이지 : 1~10 번까지의 내용

2페이지 : 11~20 번까지의 내용

3페이지 : 21~30 번까지의 내용

4페이지 : 31~40 번까지의 내용 ...

우리가 새로운 페이지를 누르면 그때마다, 10개의 새로운 내용들을 로드(load)할 텐데요. 이런 걸 전문 용어로 '페이지네이션(Pagination)'이라고 합니다. 전체 결과를 한번에 로드하는 게 아니라 이렇게 페이지 단위로 쪼개서 그때그때 요청이 있을 때마다 부분 결과를 조금씩 로드하는 방식을 말하죠. 

 

 

 

더불어 스크롤링 하여 댓글을 계속 보여주는 무한 로딩식 페이지BMS는 서버-클라이언트 구조로 동작

 

 

 

서버 역할을 하는 하나의 프로그램이 실행되고 있고, 사용자는 클라이언트 역할을 하는 프로그램으로

 

 

 

해당 서버에 접속하여 그 서버에 SQL을 전송해서 명령을 내리는 구조-클라이언트 구조로 동작

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CSV파일로 테이블 생성하기

 

 

 

Table Data Import

 

 

 

csv의 구분자 ,로 설정하는 것 주의

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Primary Key(PK) - not null(NN)

 

 

 

Type 1 ) Surrogate Key : PK로 사용하기 위해 인위적으로 생성한 컬럼. 주로 1부터 순차적으로 증가하는 숫자가 들어감.

 

 

 

     Auto Increment(AI) - 자동 증가, MySQL이 자동으로 관리하는 컬럼

 

 

 

Type 2) Natural key는 그 값이 나중에 변경되면 모든 row의 값을 수정해야 한다는 문제가 있기에 보통 Surrogate key를 선택

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



 

 

 

 

 

쿼리 창 생성 아이콘

 

쿼리 창 탭

 

쿼리 창 - SQL 입력

 

SQL문 실행 아이콘 (shift + cntl+ enter)

 

새로고침

 

테이블 조회

 

스패너 모양 : 해당 테이블의 컬럼과 각 컬럼의 데이터 타입 등을 볼 수 있음

 

표 모양 : 테이블의 전체 row를 조회할 수 있음

 

SQL 문으로 테이블의 데이터를 조회했을 때 그 조회 결과가 출력되는 영역

 

SQL 문이 잘 실행되었는지 알려주는 영역. 

 

 

 

 

 

 

 

 

 

코드잇의 'SQL 실행기'는 사용자가 이미 하나의 데이터베이스를 선택했다고 가정한 상태에서 작동합니다.

 

 

 

따라서 테이블을 나타낼 때는, 테이블 이름만 적으셔야 하고, 데이터베이스 이름은 적으시면 안 됩니다. 

 

 

 

예를 들어 member라는 테이블을 나타내고 싶을 때

 

 

 

SELECT * FROM copang_main.member; 라고 작성하시면 안 되고, 

 

 

 

SELECT * FROM member; 라고 작성하셔야 하는데요. 

 

 

 

영상에서는 테이블 이름을 나타낼 때 데이터베이스 이름을 함께 쓸 때도 있고 안 쓸 때도 있지만, ‘SQL 실행기’를 사용할 때는 꼭 테이블 이름만 써주세요!

 

 

 

참고로, ‘SQL 실행기’ 영역을 잠깐 가리고 영상을 좀더 크게 보고 싶으시면 아래 이미지의 빨간 박스로 표시된 버튼을 클릭하시면 됩니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL 문 끝에는 항상 세미콜론!

 

 

 

데이터베이스 이름 . 테이블 이름

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL 작성 순서

 

 

 

 

 

 

 

SELECT

 

    [ALL | DISTINCT | DISTINCTROW ]

 

    [HIGH_PRIORITY]

 

    [STRAIGHT_JOIN]

 

    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

 

    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

 

    select_expr [, select_expr] ...

 

    [into_option]

 

    [FROM table_references

 

      [PARTITION partition_list]]

 

      

 

[WHERE where_condition]

 

[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

 

   [HAVING where_condition]

 

    [WINDOW window_name AS (window_spec)

 

        [, window_name AS (window_spec)] ...]

 

[ORDER BY {col_name | expr | position}

 

      [ASC | DESC], ... [WITH ROLLUP]]

 

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

 

    [into_option]

 

    [FOR {UPDATE | SHARE}

 

        [OF tbl_name [, tbl_name] ...]

 

        [NOWAIT | SKIP LOCKED]

 

      | LOCK IN SHARE MODE]

 

    [into_option]

 

 

 

into_option: {

 

    INTO OUTFILE 'file_name'

 

        [CHARACTER SET charset_name]

 

        export_options

 

  | INTO DUMPFILE 'file_name'

 

  | INTO var_name [, var_name] ...

 

}

 

 

 

 

 

문자열 패턴 매칭 조건

 

% 임의의 길이를 가진 문자열

 

 

 

_ 한 자리의 문자

 

 

 

해당 글자를 직접적으로 찾아야 한다면 이스케이프 코드 \ 백슬래시 사용

 

 

 

 

 

 

 

 

 

 

 

WHERE address LIKE "서울%"

 

 

 

문자열의 처음이 서울로 시작하는 행을 선택하는 쿼리문

 

 

 

 

 

 

 

WHERE address LIKE "%고앙시%"

 

 

 

문자열에 고양시가 포함되는 행을 선택하는 쿼리문

 

 

 

 

 

 

 

WHERE address LIKE "%__구%"

 

 

 

_로 적으면 글자 개수를 제한하여 검색

 

 

 

 

 

 

 

 

 

 

 

범위에 놓인 값, 범위 사이 값

 

 

 

column IN ( , , , ...) BETWEEN a AND b

 

 

 

 

 

 

 

+) BINARY - 대소문자를 구별

 

 

 

'%g%' -> BINARY '%g%'

 

 

 

 

 

 

 

날짜 추출

 

년: YEAR(birthday) = '1992'

 

 

 

월: MONTH(sign_up_day) IN (6,7,8)

 

 

 

일 : DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31

 

 

 

시간: UNIX_TIMESTAMP(sign_up_day) - 1970년 1월 1일을 기준으로 총 몇 초 지났는지 나타낸 값

 

 

 

    FROM_UNIXTIME( UNIX_TIMESTAMP(sign_up_day) ) - DATETIME처럼 모양 예쁘게 해서 출력

 

 

 

 

 

 

 

 날짜 간 차이 구하기

 

 

 

DATEDIFF(날짜 A, 날짜 B) => B일로 부터 A일 까지 날짜 차이 계산

 

 

 

 

 

 

 

 날짜 덧셈 뺄셈

 

 

 

DATE_ADD( sign_up_day, INTERVAL 300 DAY ) 

 

 

 

가입일 기준 300일 이후의 날짜 구하기

 

 

 

DATE_SUB( sign_up_day, INTERVAL 250 DAY ) 

 

 

 

가입일 기준 250일 이전의 날짜 구하기

 

 

 

 

 

 

 

 오늘의 날짜 확인

 

 

 

SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM cloth_main.member;

 

 

 

 

 

 

 

 +) 가입 당시 회원들의 나이 알아보기

 

 

 

가입일 - 생일 / 365

 

 

 

SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday) / 365 FROM cloth_main.member;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

정렬 ORDER BY

 

오름차순 ASC (기본값)

 

 

 

내림차순 DESC

 

 

 

 

 

 

 

가입년도가 최신인 사람부터 출력하되 동일년도인 경우 email을 기준으로 오름차순을 정렬하는 내용

 

 

 

SELECT email, sign_up_day FROM cloth_main.member

 

 

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

 

 

 

 

 

 

 

 

데이터 타입 변환 (일시적)

 

 

 

 

 

signed : 양수 음수 포함한 모든 정수를 나타내는 데이터 타입

 

 

 

ORDER BY CAST(column AS signed);

 

 

 

 

 

 

 

decimal 소수점 포함

 

 

 

ORDER BY CAST(height AS decimal);

 

 

 

 

 

 

 

 

 

 

 

데이터 일부만 추려보기 - LIMIT

 

10개의 데이터만 보이기

 

 

 

SELECT email, sign_up_day FROM cloth_main.member

 

 

 

ORDER BY YEAR(sign_up_day) DESC, email ASC;

 

 

 

LIMIT 10;

 

 

 

 

 

 

 

 

 

 

 

8번째 행(실제로는 9번째)부터 2개의 데이터만 보이기

 

 

 

LIMIT 8, 2;

 

 

 

 

 

 

 

사용 예시

 

 

 

주로 검색 결과창이나, 게시판 페이지에서 볼 수 있습니다. 우리는 1, 2, 3 버튼을 눌러가면서 한 페이지씩 그 내용들을 살펴보곤 하죠?

 

 

 

매 페이지에는 아래와 같은 식으로 서로 다른 내용들이 담겨있습니다.

 

 

 

1페이지 : 1~10 번까지의 내용

 

2페이지 : 11~20 번까지의 내용

 

3페이지 : 21~30 번까지의 내용

 

4페이지 : 31~40 번까지의 내용 ...

 

우리가 새로운 페이지를 누르면 그때마다, 10개의 새로운 내용들을 로드(load)할 텐데요. 이런 걸 전문 용어로 '페이지네이션(Pagination)'이라고 합니다. 전체 결과를 한번에 로드하는 게 아니라 이렇게 페이지 단위로 쪼개서 그때그때 요청이 있을 때마다 부분 결과를 조금씩 로드하는 방식을 말하죠. 

 

 

 

 

 

 

 

더불어 스크롤링 하여 댓글을 계속 보여주는 무한 로딩식 페이지 또한 가능합니다.

 

 

 

 

 

산술 함수

SUM()

 

COUNT()

 

AVG()평균, STD()표준편차

 

ROUND()반올림

 

MAX(), MIN()

 

SORT() 제곱근,

 

CEIL() 올림, FLOOR() 내림

 

 

 

 

 

NULL 다루기

WHERE column IS NULL -> NULL값이 들어간 ROW만 추출

 

WHERE column IS NOT NULL -> NULL값 제외하기

 

 

 

COALESCE() 합치다. column에 null이 들어있다면 "###"이 출력되도록 함

 

SELECT COALESCE(colname, "###") FROM member;

 

 

 

IFNULL(COLNAME, '###")

 

IF(COLNAME IS NOT NULL, COLNAME, '###") -> (조건문,참,거짓)

 

 

 

 

 

!!주의

 

IS NULL 과 = NULL은 다르다

 

NULL은 어떤 값이 아니기 때문에 애초에 등호를 사용해서 비교할 수 있는 대상이 아님

 

 

 

 

 

이상한 값 제외 하기

WHERE age BETWEEN 5 AND 100;

 

WHERE address NOT LIKE '%호';

 

 

 

 

 

 

 

컬럼끼리 계산하기

+ - * / %

 

NULL이 포함된 계산식은 항상 NULL

 

 

 

컬럼에 alias 붙이기 => AS COLNAME

컬럼 이름에 공백이 포함된다면 '' 문자열 표식 안에 적어주기

 

 

 

CONCAT( COLNAME1, "동", COLNAME2, "호")

 

요렇게 나만의 형식을 갖춘 COLUMN 생성 가능

 

 

 

 

 

!!주의 SELECT 절에서 생성한 ALIAS는 WHERE, CASE 등에서 곧바로 사용 불가능

 

왜냐면 ALIAS는 SELECT절 안에서 설정했기 때문에 다른 곳에서는 인식 불가능

 

 

컬럼의 값 변환해서 보기 (CASE - 조건문)

 

(CASE
	WHEN weight IS NULL OR height IS NULL THEN '비만 여부 알 수 없음'
    WHEN weight/((height/100)*(height/100)) >= 25 THEN '과체중 또는 비만'
    ELSE '저체중'
END) AS obesity_check

ORDER BY obesity_check ASC;

 

 

 

고유값의 개수 구하기

SELECT COUNT(DISTINCT(gender)) FROM member;

성별은 결국 여자 아니면 남자이므로, 고유값의 개수는 2가 출력됨

 

SELECT COUNT(DISTINCT(SUBSTRING(address,1,2))) AS region_count

    FROM member;

서울 경기 처럼 주요 지역을 나타내는 단어의 종류는 9개 이기에 9가 출력됨.

 

 

문자열 관련 함수

1. LENGTH - 문자열의 길이

SELECT LENGTH(address) FROM member;

 

2. UPPER, LOWER - 대문자, 소문자로 전환

SELECT UPPER(email), LOWER(email) FROM member;

 

3. LPAD, RPAD - 왼쪽 또는 오른쪽을 특정 문자열로 채워주는(PADDING) 함수

SELECT LPAD(aeg,10,'0' FROM member;

age 컬럼의 값을, 왼쪽에 문자 0을 붙여서 총 10자리로 만드는 함수

보통 어떤 숫자의 자릿수를 맞출 때 자주 사용

대신 숫자형이 자동으로 문자열로 형변환되니 주의

 

4. TRIM, LTRIM, RTRIM - 공백 제거

TRIM은 왼쪽 오른쪽 양쪽 다 공백 삭제

SELECT TRIM(word) FROM member;

 

 

HAVING :: GROUP BY 후 특정 그룹 선별

WHERE은 SELECT절에서 맨 처음에 ROW를 필터링 할 때 사용.

그 후로 그룹화 후에 HAVING을 써서 그룹들을 필터링

GROUP BY를 안 썼을 때

GROUP BY를 쓴 후에 생성된 각 그룹(하나의 ROW로 표현되었던)에 대해서 AVG, MIN 등의 집계함수 (aggregate func)가 각각 동작했던 아래와 같은 화면 속 이미지를 잘 기억하셔야 합니다.

 

예를 들어, 그룹화 후 집계 함수 중 하나인 COUNT 함수를 사용하면, 아래와 같이 각 그룹의 ROW 수가 잘 출력됩니다.

 

GROUP BY를 안 썼을 때는 전체 테이블이 그냥 하나의 그룹이라고 생각하시면 좋습니다. 전체 ROW가 하나의 그룹이고, 그 하나의 그룹에 집계함수가 작동했던 겁니다. 하지만 그룹핑 후에는 각 그룹에 대해서 집계 함수가 작동됩니다.

 

 

GROUP BY 사용 시 규칙

SELECT SUBSTRING(address, 1, 2) AS region, gender, COUNT(*)    FROM member;

GROUP BY SUBSTRING(address, 1, 2), gender

HAVING region IS NOT NULL

ORDER BY region ASC, gender DESC;

 

위의 쿼리문은

1. 주요 지역  2. 성별의 조합 (서울-남성, 서울-여성, 경기-남성, 경기-여성)을 기준으로 그룹화 됐습니다.

 

GROUP BY 사용시 지켜야할 규칙은

SELECT 절에는

1. GROUP BY 뒤에서 사용한 컬럼들 사용 가능

2. COUNT, MAX등과 같은 집계 함수 사용 가능

하다는 점입니다.

이건 거꾸로 말해 GROUP BY 뒤에 쓰지 않은 컬럼 이름을 SELECT 뒤에 쓸 수 없습니다.

 

그 이유는, 각 그룹은 단순한 하나의 ROW가 아닙니다. 하나의 ROW는 하나의 그룹을 의미하기에 그 안에 여러 ROW가 포함된 걸로 생각해야 맞습니다.

그런데 GROUP BY 뒤에 스지 않은, 그러니까 그룹화 기준으로 사용하지 않은 컬럼명을 

SELECT 절 뒤에 서서 조회하면, 각 그룹의 ROW들 중에서 해당 컬럼의 값을

어느 ROW에서 가져와야할 지 결정할 수 없습니다.

 

예를 들어 위의 SQL 문에서 그룹화에 사용하지 않은 age 컬럼을 SELECT문 뒤에 붙이면

다음과 같은 에러메세지가 뜹니다.

그룹화에 사용되지 않은 컬럼(nonaggregated column)이 SELECT 절에 존재하면 안 된다는 뜻입니다.

 

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'copang_main.member.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

대신 집계함수는 사용할 수 있습니다.

SELECT 절 뒤에 age를 바로 쓸 순 없지만, AVG(age)처럼 집계 함수의 인자로 사용하는 건 괜찮습니다.

왜냐하면 각 그룹에서 특정 row의 age 값을 보여주는 게 아니라 그저 각 그룹 내 모든 row들의 age 컬럼값의 평균을 구하면 되기 때문입니다.

 

 

자, 이때까지의 내용을 정리하자면,

(1) GROUP BY 절 뒤에 쓴 컬럼 이름들만, SELECT 절 뒤에도 쓸 수 있다.

(2) 대신 SELECT 절 뒤에서 집계 함수에 그 외의 컬럼 이름을 인자로 넣는 것은 허용된다.

입니다.

 

 

 

SELECT 문의 실행 순서

 

• FROM : 어느 테이블을 대상으로 할 것인지를 먼저 결정합니다. 
• WHERE : 해당 테이블에서 특정 조건(들)을 만족하는 row들만 선별합니다. 
• GROUP BY : row들을 그루핑 기준대로 그루핑합니다. 하나의 그룹은 하나의 row로 표현됩니다.
• HAVING : 그루핑 작업 후 생성된 여러 그룹들 중에서, 특정 조건(들)을 만족하는 그룹들만 선별합니다. 
• SELECT : 모든 컬럼 또는 특정 컬럼들을 조회합니다. SELECT 절에서 컬럼 이름에 alias를 붙인 게 있다면, 이 이후 단계(ORDER BY, LIMIT)부터는 해당 alias를 사용할 수 있습니다.
• ORDER BY : 각 row를 특정 기준에 따라서 정렬합니다. 
• LIMIT : 이전 단계까지 조회된 row들 중 일부 row들만을 추립니다.