[zero-base/] DS Part 5. SQL - 38일차 스터디 노트

손윤재·2024년 1월 25일

Database db mysql sql 스터디 노트 제로베이스 DS 22기

제로베이스 DS 22기

목록 보기

39/55

✅ With SELECT

❕ CONCAT

CONCAT() - 여러 문자열을 하나로 합치거나 연결하는 함수

    SELECT CONCAT('string1', 'string2', ...);

예제

	SELECT CONCAT('concat', ' ', 'test');
    +-------------------------------+
    | CONCAT('concat', ' ', 'test') |
    +-------------------------------+
    | concat test                   |
    +-------------------------------+
    
    SELECT CONCAT('이름 : ', name) FROM celeb;
    +---------------------------+
    | CONCAT('이름 : ', name)   |
    +---------------------------+
    | 이름 : 아이유              |
    | 이름 : 이미주              |
    | 이름 : 송강                |
    | 이름 : 강동원              |
    | 이름 : 유재석              |
    | 이름 : 차승원              |
    | 이름 : 이수현              |
    +---------------------------+

❕ ALIAS

AS - Column이나 Table 이름에 별칭을 붙여주는 명령어

    // Column
    SELECT column1 AS alias1, column2 AS alias2, ... FROM table_name;
    
    // Table
    SELECT column1, column2, ... FROM table_name AS alias;

AS는 생략 가능하다.

    SELECT column1 alias1, column2 alias2, ... FROM table_name alias;

예제

❕ DISTINCT

검색한 결과의 중복을 제거하는 명령어

    SELECT DISTINCT column1, column2, ... FROM table_name;

예제1. 연예인 소속사 정보를 검색 - 중복 포함 vs. 중복 제외

    SELECT agency FROM celeb; // 중복 포함
    SELECT DISTINCT agency FROM celeb; // 중복 제외

예제2. 가수 중에서, 성별과 직업별 종류를 검색 - 중복 포함 vs. 중복 제외

    // 중복 포함
    SELECT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

    // 중복 제거
    SELECT DISTINCT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

❕ LIMIT

검색 결과를 정렬된 순에서 LIMIT로 주어진 숫자만큼 조회하는 명령어

    SELECT column1, column2, ... FROM table_name WHERE condition LIMIT number;

예제1. celeb Table에서 id로 정렬된 데이터 3개만 가져오기

	SELECT * FROM celeb LIMIT 3;

예제2. 나이가 가장 적은 연예인 4명을 검색

	SELECT * FROM celeb ORDER BY age LIMIT 4;

❗ Scalar Functions

입력값을 기준으로 단일 값을 반환하는 함수

❕ UCASE

영문을 대문자로 변환하는 함수

	/*문법*/ SELECT UCASE("string");

    SELECT UCASE('This is ucase test.');
    +------------------------------+
    | UCASE('This is ucase test.') |
    +------------------------------+
    | THIS IS UCASE TEST.          |
    +------------------------------+

예제. $15가 넘는 메뉴를 대문자로 조회

    SELECT UCASE(menu), price FROM sandwich WHERE price > 15;
    +----------------------------------+-------+
    | UCASE(menu)                      | price |
    +----------------------------------+-------+
    | LOBSTER ROLL                     |    16 |
    | GRILLED LAUGHING BIRD SHRIMP AND |    17 |
    | SHAVED PRIME RIB                 |    21 |
    +----------------------------------+-------+

❕ LCASE

영문을 소문자로 변환하는 함수

	/*문법*/ SELECT LCASE("string");

    SELECT LCASE("This IS LCASE Test.");
    +------------------------------+
    | LCASE("This IS LCASE Test.") |
    +------------------------------+
    | this is lcase test.          |
    +------------------------------+

예제. $5가 안되는 메뉴를 소문자로 조회

    SELECT LCASE(menu), price FROM sandwich WHERE price < 5;
    +--------------+-------+
    | LCASE(menu)  | price |
    +--------------+-------+
    | meatball sub |   4.5 |
    +--------------+-------+

❕ MID

문자열 부분을 변환하는 함수

    /*문법*/ SELECT MID("string", start_position, lenth);

    - string: 원본 문자열
    - start: 문자열 반환 시작 위치 → 첫글자는 1, 마지막 글자는 -1
    - length: 반환할 문자열 길이

예제

    --> 예제1. 1번 위치에서 4글자 조회
    mysql> SELECT MID('This is mid test', 1, 4);
    +-------------------------------+
    | MID('This is mid test', 1, 4) |
    +-------------------------------+
    | This                          |
    +-------------------------------+
    
    --> 예제2. 6번 위치에서 5글자 조회
    mysql> SELECT MID('This is mid test', 6, 5);
    +-------------------------------+
    | MID('This is mid test', 6, 5) |
    +-------------------------------+
    | is mi                         |
    +-------------------------------+
    
    --> 예제3. -4번 위치(뒤에서 4번째 위치)에서 4글자 조회
    mysql> SELECT MID('This is mid test', -4, 4);
    +--------------------------------+
    | MID('This is mid test', -4, 4) |
    +--------------------------------+
    | test                           |
    +--------------------------------+
    
    --> 예제4. -8번 위치(뒤에서 8번째 위치)에서 3글자를 조회
    mysql> SELECT MID('This is mid test', -8, 3);
    +--------------------------------+
    | MID('This is mid test', -8, 3) |
    +--------------------------------+
    | mid                            |
    +--------------------------------+
    
    --> 예제5. 11위 카페이름 중 두번째 단어만 조회: 6번 위치에서 4글자
    mysql> SELECT cafe FROM sandwich WHERE ranking=11;
    +-----------+
    | cafe      |
    +-----------+
    | Lula Cafe |
    +-----------+
    
    mysql> SELECT MID(cafe, 6, 4) FROM sandwich WHERE ranking=11;
    +-----------------+
    | MID(cafe, 6, 4) |
    +-----------------+
    | Cafe            |
    +-----------------+
    --> 다른 방식
    //  -4번 위치(뒤에서 4번째)에서 4글자로도 가져올 수 있다.
    mysql> SELECT MID(cafe, -4, 4) FROM sandwich WHERE ranking=11;
    +------------------+
    | MID(cafe, -4, 4) |
    +------------------+
    | Cafe             |
    +------------------+

❕ LENGTH

문자열의 길이를 반환하는 함수

    /*문법*/ SELECT LENGTH("string");

예제

    --> 예제1. 다음 문장의 길이 조회
    mysql> SELECT LENGTH('This is len test.');
    +-----------------------------+
    | LENGTH('This is len test.') |
    +-----------------------------+
    |                          17 |
    +-----------------------------+
    
    --> 예제2. 문자가 없는 경우 길이는 0
    mysql> SELECT LENGTH('');
    +------------+
    | LENGTH('') |
    +------------+
    |          0 |
    +------------+
    
    --> 예제3. 공백 문자도 길이에 포함되므로 길이는 1
    mysql> SELECT LENGTH(' ');
    +-------------+
    | LENGTH(' ') |
    +-------------+
    |           1 |
    +-------------+
    
    --> 예제4. NULL의 경우 길이가 없으므로 결과는 NULL이다.
    mysql> SELECT LENGTH(NULL);
    +--------------+
    | LENGTH(NULL) |
    +--------------+
    |         NULL |
    +--------------+
    
    --> 예제5. sandwich Table에서 Top 3의 주소 길이를 검색
    mysql> SELECT LENGTH(address), address FROM sandwich ORDER BY ranking LIMIT 3;
    mysql> SELECT LENGTH(address), address FROM sandwich WHERE ranking <= 3;
    +-----------------+---------------------+
    | LENGTH(address) | address             |
    +-----------------+---------------------+
    |              19 | 2109 W. Chicago Ave |
    |              18 | 800 W. Randolph St  |
    |              16 |  445 N. Clark St    |
    +-----------------+---------------------+

❕ ROUND

지정한 자리에서 숫자를 반올림하는 함수 (0이 소수점 첫째 자리)

    /*문법*/ SELECT ROUND(number, deciamls_place);

    - number: 반올림할 대상 숫자    
    - decimals: [option] 반올림할 소수점의 위치

예제

    --> 예제1. 위치를 지정하지 않을 경우, 소수점 첫번째 자리(0)에서 반올림한다.
    mysql> SELECT ROUND(315.625);
    +----------------+
    | ROUND(315.625) |
    +----------------+
    |            316 |
    +----------------+
    
    --> 예제2. 소수점 첫번째 위치는 0
    mysql> SELECT ROUND(315.625, 0);
    +-------------------+
    | ROUND(315.625, 0) |
    +-------------------+
    |               316 |
    +-------------------+
    
    --> 예제3. 두번째 소수점 위치는 1
    mysql> SELECT ROUND(315.625, 1);
    +-------------------+
    | ROUND(315.625, 1) |
    +-------------------+
    |             315.6 |
    +-------------------+

    --> 예제4. 세번째 소수점 위치는 2
    mysql> SELECT ROUND(315.625, 2);
    +-------------------+
    | ROUND(315.625, 2) |
    +-------------------+
    |            315.63 |
    +-------------------+
    
    --> 예제5. 자연수 일단위 위치는 -1
    mysql> SELECT ROUND(315.625, -1);
    +--------------------+
    | ROUND(315.625, -1) |
    +--------------------+
    |                320 |
    +--------------------+
    
    --> 예제6. 자연수 십단위 위치는 -2
    mysql> SELECT ROUND(315.625, -2);
    +--------------------+
    | ROUND(315.625, -2) |
    +--------------------+
    |                300 |
    +--------------------+

    --> 예제7. sandwich Table에서 소수점 자리는 반올림하여 1달러 단위까지만 표시
    //		  (단, 최하위 3개만 표시한다.)
    SELECT ranking, price, ROUND(price) FROM sandwich ORDER BY ranking DESC LIMIT 3;
    +---------+-------+--------------+
    | ranking | price | ROUND(price) |
    +---------+-------+--------------+
    |      50 |  6.85 |            7 |
    |      49 |  8.75 |            9 |
    |      48 |   7.5 |            8 |
    +---------+-------+--------------+

❕ NOW

현재 날짜 및 시간을 반환하는 함수

    /*문법*/ SELECT NOW();

    mysql> SELECT NOW();
    +---------------------+
    | NOW()               |
    +---------------------+
    | 2024-01-24 19:53:34 |
    +---------------------+

❕ FORMAT

문자나 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

    /*문법*/ SELECT FORMAT(number, decimals_place);

    - number: 포맷을 적용할 문자 혹은 숫자
    - decimals: 표시할 소수점 위치

예제

    --> 예제1. 소수점을 표시하지 않을 경우 0
    mysql> SELECT FORMAT(12345.6789, 0);
    +-----------------------+
    | FORMAT(12345.6789, 0) |
    +-----------------------+
    | 12,346                |
    +-----------------------+
    
    --> 예제2. 소수점 두자리까지 표시할 경우 2
    mysql> SELECT FORMAT(12345.6789, 2);
    +-----------------------+
    | FORMAT(12345.6789, 2) |
    +-----------------------+
    | 12,345.68             |
    +-----------------------+
    
    --> 예제3. 소수점 열자리까지 표시
    mysql> SELECT FORMAT(12345.6789, 10);
    +------------------------+
    | FORMAT(12345.6789, 10) |
    +------------------------+
    | 12,345.6789000000      |
    +------------------------+
    
    --> 예제4. oil_price Table에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우
    //		  천원 단위에 콤마를 넣어서 조회
    mysql> SELECT FORMAT(가격, 0) FROM oil_price WHERE ROUND(가격, -3) >= 2000;
    +-------------------+
    | FORMAT(가격, 0)   |
    +-------------------+
    | 1,509             |
    | 1,598             |
    | 1,635             |
    | 2,160             |
    +-------------------+

✅ PRIMARY KEY

후보키들 중 DB 설계자에 의해서 개체(Table) 구별을 위해 선택된 하나의 키를 의미한다.

후보키(Candidate Key)란, 하나의 릴레이션(Relation)에서 유일성과 최소성을 가지고 있는 모든 속성 혹은 속성 집합이다.
Table 당 하나의 기본키를 가진다.
기본키로 선택된 속성은 중복되지 않는 고유값을 가진다. → UNIQUE
정의되지 않은 NULL 값을 포함할 수 없다. → NOT NULL
다른 Relation(Table)에 있는 외래키(FOREIGN KEY)에 의해 참조될 수도 있다.
PRIMARY KEY 생성 문법
- Table이 생성된 이후에도 ALTER TABLE을 통해 PRIMARY KEY를 지정할 수 있다.

    // 1. Table을 생성할 때 지정
    CREATE TABLE table_name(
    		column1 datatype NOT NULL,
    		column2 datatype NOT NULL,
    		...
    		[CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...)
    );
    --> CONSTRAINT은 생략 가능하다. 생략하면 자동으로 생성된다.
    
    // 2. 이미 정의되어 있는 Table에서 기본키를 설정하는 방법
    ALTER TABLE table_name ADD PRIMARY KEY (column1, column2, ...)

PRIMARY KEY 삭제 문법
- 여러 개의 컬럼이 기본키로 설정되어 있어도 삭제 방법은 동일하다.

    ALTER TABLE table_name DROP PRIMARY KEY;

예제1. person Table을 생성하면서 기본키 지정

    CREATE TABLE person(
        pid int NOT NULL,
        name varchar(16),
        age int,
        sex char,
        PRIMARY KEY (pid)
    );

예제2. person Table의 기본키 삭제

    ALTER TABLE person DROP PRIMARY KEY;

예제3. 이미 정의 되어 있는 person Table의 기본키 설정

    ALTER TABLE person ADD PRIMARY KEY (pid);

✅ FOREIGN KEY

외래키는 두 Relation 간의 관계를 맺어줄 때 사용되는 키로,
외래키로 정의된 속성(Attribute)는 반드시 상대 Relation의 기본키여야 한다.

Table 간의 관계를 연결해주는 역할을 하는 키이다.
외래키로 참조되는 Table의 항목(Column)은 그 Table의 기본키 혹은 단일값이어야 한다.
기본키와 달리 외래키는 한 Table 내에 여러 개일 수 있다. 즉, 여러 개의 Table을 참조할 수 있다.
- 외래키 확인을 위해 CONSTRAINT으로 지정한 이름을 확인할 수 있다.
- CONSTRAINT를 생략하면 이름이 자동으로 생성된다.
- 이 constraint_name을 알아야 해당 외래키를 삭제(DROP)할 수 있다.
FOREIGN KEY 생성 문법
- Table이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정할 수 있다.

    // 1. Table을 생성할 때 지정
    CREATE TABLE table_name(
    		column1 datatype NOT NULL,
    		column2 datatype NOT NULL,
    		column3 datatype,
    		column4 datatype,
    		...
    		[CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...)
    		[CONSTRAINT constraint_name]
    		   PRIMARY KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
    );
    
    // 2. 이미 정의되어 있는 Table에서 외래키를 설정하는 방법
    ALTER TABLE table_name ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column)
    --> CONSTRAINT이 생략되어 있지만 자동으로 생성된다.

자동으로 생성된 CONSTRAINT를 확인하는 방법

    SHOW CREATE TABLE table_name;

FOREIGN KEY 삭제 문법
- 외래키는 한 Table에 여러 개가 존재할 수 있으므로 삭제하려는 외래키를 지정해 줘야 한다.

    ALTER TABLE table_name DROP FOREIGN KEY FK_constrain_name;

예제1. CREATE TABLE에서 FOREIGN KEY를 지정하는 경우

    CREATE TABLE orders(
    		oid int NOT NULL,
    		order_no varchar(16),
    		pid int,
    		PRIMARY KEY (oid),
    		[CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid)
    );
    SHOW CREATE TABLE orders;

예제2. CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 생략할 수 있다.

    CREATE TABLE job(
    		jid int NOT NULL,
    		name varchar(16),
    		pid int,
    		PRIMARY KEY (jid),
    		FOREIGN KEY (pid) REFERENCES person(pid)
    );
    SHOW CREATE TABLE job;
    --> SHOW CREATE TABLE로 자동 생성된 constraint_name을 알 수 있다.

예제3. orders Table의 외래키 삭제

    ALTER TABLE orders DROP FOREIGN KEY FK_person;

예제4. 이미 정의 되어 있는 orders Table에서 외래키 설정

    ALTER TABLE orders
    		ADD [CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid);

손윤재

ISTP(정신승리), To Be Data Scientist

이전 포스트

[zero-base/] DS Part 5. SQL - 37일차 스터디 노트

다음 포스트

[zero-base/] DS Part 5. SQL - 38일차 스터디 노트

제로베이스 DS 22기

✅ With SELECT

❕ CONCAT

❕ ALIAS

❕ DISTINCT

❕ LIMIT

❗ Scalar Functions

❕ UCASE

❕ LCASE

❕ MID

❕ LENGTH

❕ ROUND

❕ NOW

❕ FORMAT

✅ PRIMARY KEY

✅ FOREIGN KEY

[zero-base/] DS Part 5. SQL - 37일차 스터디 노트

[zero-base/] DS Part 5. SQL - 39일차 스터디 노트

0개의 댓글