[DB] 한 번에 끝내는 SQL & Database Part 2

테이블과 컬럼 생성할 때 SQL 써야 약간 멋있음

데이터베이스 생성, 삭제하는 법은 

CREATE DATABASE 데이터베이스이름

DROP DATABASE 데이터베이스이름

 

CREATE TABLE 테이블명 ( 컬럼1이름 datatype, 컬럼2이름 datatype, 컬럼3이름 datatype, ...등 );

마지막 줄에는 콤마 (,) 넣으면 안됨.

 

컬럼 생성, 변경, 삭제는 ALTER TABLE 테이블명 ADD 컬럼명 VARCHAR(100);

데이터타입이 변경 : ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 datatype;

컬럼 제거 ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

컬럼에 Constraints (제약조건) 주기

1. NOT NULL 제약조건 

 - 컬럼 만들 때 우측에 NOT NULL을 체크해주면 NULL 금지라는 제약을 걸 수 있다.

CREATE TABLE new_table (
    id INT NOT NULL,
    이름 VARCHAR(100) NOT NULL,
    나이 INT
)

 

2. UNIQUE 제약조건

 - 컬럼 만들 때 우측에 UNIQUE 를 체크해주면 UNIQUE 라는 제약을 걸 수 있다.

UNIQUE가 붙어있는 컬럼안에선 모든 행이 각각 다른 데이터를 가지고 있어야 한다.

그래서 다른 행에 있는 값을 저장하려고 하면 저장이 안됨

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    UNIQUE(이름, 나이)
)

 

3. CHECK() 제약조건

 - 등호 부등호 AND OR 써서 조건식처럼 입력하면 됩니다. IN 연산자도 가능합니다. 

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT CHECK (나이 > 0),
)

 

4. PRIMARY KEY 제약조건 

 - NOT NULL, UNIQUE 제약조건도 자동으로 부여됩니다. 

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    이름 VARCHAR(100),
    나이 INT CHECK (나이 > 0),
)

 

5. AUTO_INCREMENT 제약조건

CREATE TABLE new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    이름 VARCHAR(100),
    나이 INT,
)

 

6. CONSTRAINTS() 문법으로 제약조건주기

CREATE TABLE new_table (
    id INT,
    이름 VARCHAR(100),
    나이 INT,
    CONSTRAINT 제약조건작명 PRIMARY KEY (id),
    CONSTRAINT 제약조건작명2 CHECK(나이 > 10)
)

 

참고 : 이미 있는 컬럼에 제약을 추가하려면 

ALTER TABLE 테이블명 MODIFY 컬럼명 int NOT NULL;

 

테이블 2개 합쳐서 출력은 INNER JOIN

SELECT 출력할컬럼들
FROM 테이블1 INNER JOIN 테이블2
ON 조건문

 

출력하고 싶은 테이블이 3개 이상인 경우

SELECT *
FROM 테이블1 
INNER JOIN 테이블2 ON 조건1
INNER JOIN 테이블3 ON 조건2

 

테이블2개 이상을 출력할 때 WHERE / ON 조건문을 넣지 않으면

자동적으로 CROSS JOIN이 됨.

SELECT 프로그램, 가격, 강사, 출신대학
FROM program CROSS JOIN teacher

 

공통점이 없는 행도 함께 출력해보고 싶으면 LEFT JOIN, RIGHT JOIN, OUTER JOIN

-왼쪽 테이블 전체 + 공통된 행 출력하고 싶으면 LEFT JOIN

-오른쪽 테이블 전체 + 공통된 행 출력하고 싶으면 RIGHT JOIN

 

IS NULL 조건은 NULL을 담고있는 행만 필터링

SELECT * 
FROM program 
  LEFT JOIN teacher
    ON program.강사id = teacher.id
WHERE 컬럼명 IS NULL

 

LEFT JOIN과 RIGHT JOIN 결과를 합해서 출력하고 싶으면 FULL JOIN : MySQL 에선 사용 불가

 

 

데이터 넣거나 복사하려면 INSERT INTO

INSERT INTO product (id, 상품명, 가격) VALUES (1, '김치', 500)

 

다른 테이블에 있던 데이터 복사도 가능

INSERT INTO product SELECT 컬럼명 FROM 테이블명

 

테이블 복사문법 2 : 얘는 테이블을 아예 새로 생성해준다는게 차이점

SELECT * INTO 새로운테이블명
FROM 기존테이블명
WHERE 조건식

 

MySQL에는 SELECT INTO 문법이 없어서 이렇게 써야함 

CREATE TABLE 새로운테이블명 SELECT * FROM 기존테이블명

 

 

특정 행의 데이터를 수정하고 싶으면 UPDATE 문법

UPDATE 테이블명 
SET 컬럼1 = 값, 컬럼2 = 값
WHERE 조건식

 

삭제하려면 DELETE FROM

DELETE FROM 테이블명 WHERE 조건식

다른 테이블에서 Foreign key로 사용중인 데이터는 삭제가 안됨

 

응용 : JOIN & UPDATE

UPDATE A INNER JOIN B 
  ON 쪼인조건

SET 수정할내용
WHERE 조건식

 

응용 : JOIN & DELETE

DELETE A 
FROM A INNER JOIN B 
  ON 쪼인조건

WHERE 조건식

DELETE A 이러면 조건식에 맞는 행이 A 테이블에서 삭제

DELETE B 이러면 조건식에 맞는 행이 B 테이블에서 삭제

DELETE A, B 이러면 조건식에 맞는 행이 A, B 테이블에서 전부 삭제

 

SELECT 결과들을 합치려면 UNION : 컬럼 갯수 맞아야 UNION 으로 합쳐줌, 테이블 간의 중복데이터는 제거해줌

SELECT * FROM insert_test.stock
UNION
SELECT * FROM insert_test.stock2;

UNION ALL쓰면 테이블 간의 중복데이터는 남겨줌

JOIN & UNION 차이

 

 

 

 

 

/강의 출처: 코딩애플/