[MySQL] DDL, DML 기본 명령어

Mysql을 사용할 때 데이터베이스, 테이블 관련 그리고 데이터 관련 명령어들을 정리했습니다.

명령어에서 대소문자 구분은 하지 않지만, 가독성을 위해 대문자로 표기한 명령어들이 있습니다.

1. Database 관련 명령어

1) database 생성

데이터가 저장되는 테이블들을 모아놓은 하나의 논리적 공간은 database라고 합니다. ( DBMS를 뜻하는 것이 아닙니다. )

# CREATE database 데이터베이스명;

2) database 사용 선언

어떤 database를 사용할 것인지 선언합니다.

# USE 데이터베이스명;

3) database 목록 조회

# SHOW databases;

4) database 삭제

# DROP database 테이터베이스명;

2. 테이블 관련 명령어

테이블 추가

테이블을 생성하는 명령어의 기본구조는 CREATE TABLE 테이블명 ( 컬럼, 컬럼, 컬럼 ); 입니다.

아래는 id , name , model_number , series 컬럼을 갖는 products 테이블을 생성하는 예제입니다.

# CREATE TABLE products (
           id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           name VARCHAR(50) NOT NULL,
           model_number VARCHAR(15) NOT NULL,
           series VARCHAR(30) NOT NULL
   );

컬럼을 명시하는 괄호( ) 안에는 필드명 / 자료형 / 제약조건의 형식을 갖습니다.

필드명은 자신이 직접 작성해주면 되며, 일반적으로 snake case를 사용합니다.

자료형의 종류는 다음과 같은 것들이 있습니다. ( 참고 )

• INT       : 정수
• DOUBLE : 실수
• VARCHAR(정수) : 문자열
• DATE      : YYYY-MM-DD format
• TIME      : HH:MM:SS format.
• ENUM    : enum으로 명시된 값만 입력될 수 있음

제약 조건의 종류는 다음과 같은 것들이 있습니다. ( 참고 )

• NOT NULL      : 반드시 입력해야 하는 컬럼
• AUTO_INCREMENT  : 자동으로 숫자가 증가하도록 만듦 ( 주로 PK 컬럼에서 사용합니다. )
• PRIMARY KEY  : 기본 키로 지정
• DEFAULT        : 기본 값을 설정
• UNIQUE         : 중복되지 않도록 합니다.

테이블 조회

1) DESC

desc는 스키마( 테이블의 컬럼명과 컬럼들의 제약조건 정보 )를 볼 수 있는 명령어입니다.

# DESC 테이블명;

2) SHOW

database의 모든 테이블 조회

# SHOW tables;

테이블 삭제

1) DROP

drop은 테이블을 통째로 삭제하는 명령어입니다.

# DROP table 테이블명;

2) TRUNCATE

이와 달리 truncate는 테이블 정의는 남겨놓고 테이블에 존재하는 모든 데이터를 삭제하는 명령어입니다.

# TRUNCATE table 테이블명;

테이블 정의 수정

테이블을 정의한 뒤 Alter 명령어를 통해 스키마를 수정할 수 있습니다.

만약 테이블이 비어있다면 DROP을 한 후 다시 생성하면 되지만, 이미 데이터가 있을 경우에는 곤란하므로 이럴 경우 유용합니다.

1) 컬럼 추가

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 ADD 컬럼명 데이터타입; 입니다.

아래는 products 테이블에 문자열 타입의 newColumn 컬럼을 추가하는 예제입니다.

기존의 데이터들은 newColumn 컬럼이 없었는데 갑자기 생긴 것이므로 newCoulumn의 값으로 NULL이 할당됩니다.

# ALTER TABLE products ADD newColumn varchar(20);

2) 컬럼 타입 수정

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입; 입니다.

아래는 varchar(20)이였던 newColumn 컬럼의 자료형을 Integer로 수정하는 예제입니다.

자료형이 변경되었으므로 기존의 데이터들도 자료형이 바뀌는데, 바뀔 수 없다면 에러가 발생합니다.

# ALTER TABLE products MODIFY newColumn Integer;

3) 컬럼명 수정

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 CHANAGE 컬럼명 데이터타입; 입니다.

아래는 newColumn 컬럼명을 newnewColumn 컬렴명으로 수정하는 예제입니다.

기존의 정의를 유지하려면 이와 같이 새로운 컬럼의 이름만 작성하면 됩니다.

# ALTER TABLE products CHANGE newColumn newnewColumn varchar(50);

4) 컬럼 삭제

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 DROP 컬럼명; 입니다.

아래는 newColumn 컬럼명을 삭제하는 예제입니다.

# ALTER TABLE products DROP newnewColumn;

3. 데이터 관련 명령어

데이터 추가

데이터를 추가하는 명령어의 기본구조는 INSERT INTO 테이블명 (컬럼, 컬럼, ... ) VALUES (값, 값, ...); 입니다.

1) 컬럼 명시

아래는 products 테이블의 name, model_number, series 컬럼에 데이터를 2개 추가하는 예제입니다.

# INSERT INTO products (name, model_number, series) VALUES
           ("Eric", "0000", "Artists"),
           ("victolee93", "1234", "Student");

2) 컬럼 생략

컬럼명을 생략해서 데이터를 추가할 수도 있습니다.

# INSERT INTO products VALUES ("Eric", "0000", "Artists");

3) 기본값으로 추가

특정 컬럼에 DEFAULT 제약조건이 명시되어 있다면, 기본 값으로 데이터를 추가할 수 있습니다.

# INSERT INTO products VALUES ("Eric", DEFAULT, "Artists");

데이터 조회

데이터를 조회하는 명령어의 기본구조는 SELECT 컬럼, 컬럼, … FROM 테이블명; 입니다.

1) 모든 컬럼 조회

아래는 products 테이블의 모든 컬럼들에 대한 데이터를 조회하는 예제입니다.

# SELECT * FROM products;

• *는 와일드 카드로서 모든 컬럼을 의미합니다.

2) 조건을 추가하여 조회

어떤 조건으로 데이터를 조회하는 명령어의 기본구조는 SELECT 컬럼, 컬럼, … FROM 테이블명 WHERE 조건; 입니다.

아래는 products 테이블에서 series 컬럼 값이 Artist인 데이터만 조회하는 예제입니다.

# SELECT * FROM products WHERE series="Artists";

3) 조건을 추가하여 조회 - LIKE

특정 문자열이 포함된 데이터만 조회할 수 있는데, 이를 유사검색( LIKE 검색 )이라 합니다.

아래는 series 컬럼에서 ti가 포함된 데이터를 조회하는 예제입니다.

# SELECT * FROM products WHERE series LIKE '%ti%';

• '%ti%'는 ti 양 옆으로 문자가 없거나, 어떤 문자든 상관없이 ti 문자열만 포함되어 있는 데이터를 검색하라는 의미입니다.
• % 대신 _를 사용할 수 있습니다.
• %는 글자수에 제한이 없지만 _는 1글자를 의미합니다.
• 예를 들어, ti___ 는 "ti로 시작하는 3글자"인 조건을 의미합니다.

4) 데이터 정렬

특정 컬럼으로 데이터를 정렬하는 명령어의 기본구조는 SELECT 컬럼, 컬럼, … FROM 테이블명 ORDER BY 컬럼 ASC/DESC; 입니다.

아래는 products 테이블에서 name 컬럼을 기준으로 오름차순으로 정렬하는 예제입니다.

ASC는 오름차순이며, DESC는 내림차순입니다.

아무것도 명시하지 않을 경우, ORDER BY는 기본 값으로 ASC가 적용됩니다.

# SELECT * FROM products ORDER BY name ASC;

여러 컬럼에 대해 순차적으로 정렬 기준을 삼을 수 있습니다.

아래는 products 테이블에서 name 컬럼을 내림차순으로, 정렬된 값이 같을 경우 다음 정렬 기준으로 series 컬럼을 오름차순으로 정렬하는 예제입니다.

# SELECT * FROM products ORDER BY name DESC, series;

• NULL 값이 포함되어 있는 경우 MySQL에서는 NULL 값을 가장 작은 값으로 취급합니다.
• 정렬할 때 문자열은 사전식으로 이루어지며, 정수, 날짜 타입은 대소관계로 이루어집니다.
• 숫자가 문자열로 되었을 때 주의가 필요합니다.

5) 조회 데이터 개수 건너뛰기/사이즈 조절

검색되는 데이터의 개수를 제한하고 싶을 경우 LIMIT을 사용합니다.

기본구조는 SELECT 컬럼, 컬럼, … FROM 테이블명 LIMIT 정수;

아래는 products 테이블을 조회할 때 상위 2개의 데이터만 조회하는 예제입니다.

# SELECT * FROM products LIMIT 2;

몇 개의 데이터를 건너 뛴 후, 개수를 제한해서 검색할 수도 있습니다.

아래는 products 테이블에서 5개의 데이터를 건너뛴 뒤, 상위 2개의 데이터를 조회하는 예제입니다.

# SELECT * FROM products LIMIT 5,2;

검색되는 데이터의 개수를 점프( Skip )하고 싶을 경우 OFFSET을 사용합니다.

기본구조는 SELECT 컬럼, 컬럼, … FROM 테이블명 OFFSET 정수;

아래는 처음 2개의 데이터를 건너뛴 후 모든 데이터를 조회하는 예제입니다.

# SELECT * FROM products OFFSET 2;

• offset과 limit을 이용하여, 게시판의 게시글 개수를 페이징 할 수 있습니다.

데이터 수정

데이터를 수정하는 명령어의 기본구조는 UPDATE 테이블명 SET 필드=값; 입니다.

아래는 products 테이블에서 id가 4인 데이터의 name을 hihi로, model_number를 3으로 수정하는 예제입니다.

# UPDATE products SET name='hihi', model_number = 3 WHERE id = 4;

• MySQL에서는 SET 내부가 한 번에 업데이트가 되는 것이 아니라, 순차적으로 업데이트 됩니다.

• 조건( WHERE )을 사용하지 않으면 테이블의 모든 데이터가 변경됩니다.

• 따라서 UPDATE 쿼리는 WHERE가 항상 따라다닌다고 기억할만큼 주의가 필요합니다.

데이터 삭제

데이터를 삭제하는 명령어의 기본구조는 DELETE FROM 테이블명 조건; 입니다.

아래는 products 테이블의 id가 1인 데이터를 삭제

# DELETE FROM products WHERE id = 1;

• 조건( WHERE )을 사용하지 않으면 테이블의 모든 데이터가 삭제됩니다.

• TRUNCATE 쿼리와 같습니다.

• 따라서 DELETE 쿼리도 WHERE가 항상 따라다닌다고 기억할만큼 주의가 필요합니다.

4. SELECT 명령어 유의사항

해당 글에서 모든 문법을 다룬 것은 아니지만, 전체 쿼리를 보았을 때 작성 순서는 아래와 같습니다.

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. LIMIT

( 뒤에서 GROUP BY와 HAVING에 대해 다루겠지만, 궁금하시면 여기를 참고해주세요. )

예를 들어, 다음과 같이 쿼리를 작성할 수 있습니다.

# SELECT * FROM products WHERE (id<5) AND (name like '%hi%') ORDER BY name DESC LIMIT 3;

수행 순서는 아래와 같습니다.

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY
6. SELECT
7. LIMIT

이상으로 SQL의 모든 문법을 살펴본 것은 아니지만, 기본이 되는 쿼리들에 대해 알아보았습니다.

기본이라고 생각했는데도 상당히 많네요.... 근데 이만큼의 분량도 전체 문법에 비하면 그리 많은 수준은 아닙니다.

이후의 글들에서 조금씩 알아가보도록 하겠습니다.