SQL 초급 - MySQL 기초 (기본 CRUD)

SQL

SQL 초급 - MySQL 기초 (기본 CRUD)

H-V 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
);

AUTO_INCREMENT PRIMARY KEY - 오토 인크리먼트와 프라이머리키는 항상 같이 간다고 보면 됨.
AUTO_INCREMENT - 값을 넣을때 자동적으로 ID 값을 부여하고 ID값 순서를 높여줌(1,2,3,4...)
PRIMARY KEY - '기본 키'라고 불리며 테이블의 각 행을 고유하기 만들어 식별 가능하도록 함, 테이블당 하나만 가능
또한 기본 키는 기본적으로 인덱스를 생성하여 기본키 행 기준 빠른 검색이 가능토록 함
NOT NULL - 공백(NULL)값 입력 불가
UNIQUE - 중복 입력 불가
UNSIGNED - 숫자 양수만 가능
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 데이터 변경/삭제 해보기

데이터는 다음 링크로 가서 복붙!(https://www.yalco.kr/@sql/3-3/)

1) 삭제

DELETE FROM businesses WHERE status = '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 문이 뭘 가르치는지 나온다.

section_name = '한식' 은 section_id = 1
S.section_id = B.fk_section_id → 즉 1 = 1 이 가리키는 것은 business_id = 5,6
다시 돌아가서 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;

더 많은 내용은 다음 링크로 (https://www.yalco.kr/@sql-adv/4-3/)