ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • SQL 초급 - MySQL 기초 (기본 CRUD)
    SQL 2021. 10. 1. 14:02

    인프런 '갖고노는 MySQL 데이터베이스 by 얄코' 참조 

     

     

    01 테이블 생성/수정/삭제 

    • 테이블은 직접 코드로 만질 수 있으나 아래와 같이 버튼을 눌러서 만드는 방식도 있다.

    • 생성
    CREATE TABLE people (
      person_id INT,
      person_name VARCHAR(10),
      age TINYINT,
      birthday DATE
    );

     

    • 변경
    -- 테이블명 변경
    ALTER TABLE people RENAME TO  friends,
    -- 컬럼 자료형 변경
    CHANGE COLUMN person_id person_id TINYINT,
    -- 컬럼명 변경
    CHANGE COLUMN person_name person_nickname VARCHAR(10), 
    -- 컬럼 삭제
    DROP COLUMN birthday,
    -- 컬럼 추가
    ADD COLUMN is_married TINYINT AFTER age;

     

     

    • 삭제 - DROP TABLE friends;

     

     

     

    02 데이터 만지기

    • 데이터 삽입
    • NOT NULL에 대한 제한이 없으면 컬럼명 + 데이터 생략하고 집어 넣을 수 있다. 
    • 자료형에 맞지 않는 값 삽입 시 오류 걸림
    * 각각 테이블에 넣기
    INSERT INTO people (person_id, person_name, age, birthday)
    VALUES (1, 'KIM', 22, '2000-02-02');
    
    * 모든 칼럽에 값 넣을때는 컬럼명 생략 가능
    INSERT INTO people 
    VALUES (2, 'CHA', 33, '1989-11-12');
    
    * NULL 제한 없이
    INSERT INTO people 
    (person_id, person_name, birthday)
    VALUES (3, 'PARK', '1995-05-07');
    
    *여러개 한번에
    INSERT INTO PEOPLE
    (PERSON_ID, PERSON_NAME, AGE, BIRTHDAY)
    VALUES
    (4, 'JOHN', 25, '1998-03-01'),
    (5, 'LEE', 28, '1993-03-01'),
    (6, 'VIC', 26, '1995-03-01');

     

     

     

    03 테이블 제약 넣기

    CREATE TABLE people (
      person_id INT AUTO_INCREMENT PRIMARY KEY,
      person_name VARCHAR(10) NOT NULL,
      nickname VARCHAR(10) UNIQUE NOT NULL,
      age TINYINT UNSIGNED,
      is_married TINYINT DEFAULT 0
    );
    1. AUTO_INCREMENT PRIMARY KEY - 오토 인크리먼트와 프라이머리키는 항상 같이 간다고 보면 됨. 
      AUTO_INCREMENT - 값을 넣을때 자동적으로 ID 값을 부여하고 ID값 순서를 높여줌(1,2,3,4...)
      PRIMARY KEY - '기본 키'라고 불리며 테이블의 각 행을 고유하기 만들어 식별 가능하도록 함, 테이블당 하나만 가능
                           또한 기본 키는 기본적으로 인덱스를 생성하여 기본키 행 기준 빠른 검색이 가능토록 함
    2. NOT NULL - 공백(NULL)값 입력 불가
    3. UNIQUE - 중복 입력 불가
    4. UNSIGNED - 숫자 양수만 가능
    5. DEFAULT - 값 입력이 없을 시 설정된 값으로 적용 (즉 여기서는 0)
    INSERT INTO PEOPLE 
    (PERSON_NAME, NICKNAME, AGE)
    VALUES ('PARK', 'HOHO', 22);
    
    INSERT INTO PEOPLE 
    (PERSON_NAME, NICKNAME, AGE, IS_MARRIED)
    VALUES ('KIM', 'AAA', NULL, 1);

     

     

     

    04 MySQL 자료형

    • 웬만한 서비스들은 아래 자료형들로 충분히 가능하다

     

    1) 정수형 

    • 정수형은 TINYINT → SMALLINT → MEDIUMINT → INT → BIG INT 순으로 바이트형(1,2,3,4,8)인데 자주 쓰는것은 INT형이며 더 큰 형태에서는 보통 LONG을 많이 쓴다
    • 양수/음수 둘다 적용 가능 하다

     

    2) 소수점

    • 고정 소수점 (DECIMAL) - 좁은 범위의 수 표현 가능, 보다 정확한 값을 나타냄
      (DECIMAL (S,D) - 실수 부분 총 자릿수(S) & 소수 부분 자릿수(D)
    • 부동 소수점 (FLOAT/DOUBLE) - 넓은 범위의 수 표현 가능, 정확하지 않은 값 

     

    3) 문자열

    • CHAR - 고정 사이즈(남는 글자는 스페이스로 채움) 최대 255 바이트
    • VARCHAR - 가변 사이즈 최대 65,535 바이트
    • CHAR이 검색시에 유리, VARCHAR를 선언하고 4글자보다 작으면 자동적으로 CHAR로 적용 됨

     

    4) 텍스트

    • TINYTEXT → TEXT → MEDIUMTEXT →  LONGTEXT 순으로 바이트형(255,65,535...) 으로 게시판등을 만들때 사용하며 보통 TEXT를 많이 쓴다.

     

    5) 시간 자료형 

    • DATE →  TIME → DATETIME → TIMESTAMP 순으로 시간/날짜를 나타낼때 쓰며 보통 DATETIME & TIMESTAMP 를 많이 쓴다
    • 시간 데이터를 가감없이 기록할시에는 DATETIME, 시간+자동기록+국제적 서비스등은 TIMESTAMP를 씀. DATETIME은 어디에서 읽는 적힌 시간대로 읽히지만 TIMESTAMP 어디를 가든 그 시간에 맞춰서 저장 됨

     

     

     

     

     

    05 데이터 변경/삭제 해보기

     

    1) 삭제

    DELETE FROM businesses WHERE status = 'CLS';

    13/15 등 CLS 날라감

    • DELETE로 행을 지우면 아이값이 기억이 된 채로 진행이 된다. 그래서 ID값을 초기화하면서 DELETE를 하려면 'TRUNCATE' 를 써서 지워야 한다. 즉 TRUNCATE는 테이블을 초기화 하는것과 같다

     


    2) 수정

    • SET '컬럼명' = ' ' WHERE '컬럼명' = ' ' 을 잘 쓰는게 중요하다

    UPDATE menus SET MENU_NAME = '삼선짜장'
    WHERE MENU_ID = 12;

     

     

     

    UPDATE menus
    SET
    MENU_NAME = '열정떡볶이',
    KILOCALORIES = 492.78,
    PRICE = 5000
    WHERE
    FK_BUSINESS_ID = 4
    AND MENU_NAME = '국물떡볶이';

     

    • 아래와 같은 계산을 해서 적용도 가능
    UPDATE menus
    SET PRICE = PRICE + 1000
    WHERE FK_BUSINESS_ID = 8;

     

     

     

     

    • JOIN을 써서 업데이트
    UPDATE menus
    SET menu_name = CONCAT('전통 ', menu_name)
    WHERE fk_business_id IN (
      SELECT business_id 
      FROM sections S
      LEFT JOIN businesses B
        ON S.section_id = B.fk_section_id 
      WHERE section_name = '한식'
    );

    → 가운데 셀렉트문 부터 확인을해봐야 한다

      SELECT business_id 
      FROM sections S
      LEFT JOIN businesses B
        ON S.section_id = B.fk_section_id 
      WHERE section_name = '한식'

    • 조인을 시켜 테이블을 보면 가운데 SQL 문이 뭘 가르치는지 나온다. 
    1. section_name = '한식' 은 section_id = 1 
    2. S.section_id = B.fk_section_id → 즉 1 = 1 이 가리키는 것은 business_id = 5,6 
    3. 다시 돌아가서 fk_business_id가 5,6 인데 1,2번이 가르키는것과 하나라도 충족이 되면 'menu_name' 에 '전통' 을 넣어서 수정을 하라는 말

     

     

    06 기본키

    • 중복되지 않는 고유값만 허용, 테이블당 1나만 가능
    CREATE TABLE people (
      first_name CHAR(2) PRIMARY KEY,
      last_name CHAR(3),
      nickname VARCHAR(10)
    );
    *기본키를 추가/삭제 가능
    
    ALTER TABLE people DROP PRIMARY KEY;
    ALTER TABLE people ADD PRIMARY KEY (last_name);
    • 기본키안에 여러개의 컬럼 설정 가능(즉 여러개 컬럼이 동시에 키를 걸고 움직이기때문에 두 개중 하나만 겹쳐도 오류가 안걸리고 계속 데이터가 들어 갈 수 있음)

     

     

    07 고유키

    • UNIQUE 로 걸 수 있으며 중복 제한이 있지만 NULL 값은 허용 됨
    • 고유키도 기본키처럼 여러개의 컬럼을 같이 걸 수 있음 
    CREATE TABLE people (
      person_id INT AUTO_INCREMENT PRIMARY KEY,
      first_name CHAR(2) UNIQUE,
      last_name CHAR(3)
    );

     

     

     

    08 외래키

    • 테이블끼리의 연결시에 필요한 키로 보통 외래키는 본 테이블이 타 테이블을 가르키는 용도로 쓰임 

    이 테이블이 section 테이블의 각 ID 부분을 외래키로 가르킨다는 뜻 

    ALTER TABLE _자식테이블명
      ADD CONSTRAINT _제약명 
      FOREIGN KEY ( _자식테이블외래키 )
      REFERENCES 부모테이블명 ( _부모테이블기본키 )
      -- ON DELETE _삭제시제약 
      -- ON UPDATE _수정시제약

     

     

     

    09 뷰 - 가상 테이블

    • 실제 테이블에서 나온 결과를 실제 테이블처럼 사용 가능한 것
    -- 뷰 생성
    CREATE VIEW section_view AS
    SELECT section_id, section_name
    FROM sections;

    • 뷰는 보안 - 테이블 구조 및 내용 숨기기 / 편의 - 복잡한 쿼리 사전 구현의 이유로 쓴다
    CREATE VIEW menu_view AS
    SELECT
      menu_id,
      menu_name,
      CONCAT(
        SUBSTRING(business_name, 1, 1),
        REPEAT('*', CHAR_LENGTH(business_name) - 1)
      ) AS business,
      price,
      likes AS evaluation
    FROM businesses B
    INNER JOIN menus M
      ON M.fk_business_id = B.business_id;

     

     

     

    10 인덱스

    • 특정 컬럼(들)에 대한 목차 생성 - 검색 가속화
    • 테이블 내용 변경(추가/수정/삭제)시 시간 더 소모 함
    -- 인덱스 생성
    ALTER TABLE `mydatabase`.`businesses` 
    ADD INDEX index_biz_name (business_name);
    
    -- 다중 컬럼 인덱스
    ALTER TABLE menus
    ADD INDEX index_name_cal (menu_name, kilocalories);
    
    -- 인덱스 조회
    SHOW INDEX FROM businesses;
    
    -- 인덱스 삭제
    ALTER TABLE businesses
    DROP INDEX index_biz_name;

     

     

Designed by Tistory.