[mysql] 자주 사용하는 기본 쿼리문 정리

CREATE

CREATE DATABASE 데이터베이스이름

USE 데이터베이스이름

CREATE TABLE 테이블이름
(
    필드이름1 필드타입1,
    필드이름2 필드타입2,
    ...
)

제약 조건(constraint)

• NOT NULL: 해당 필드는 NULL 값을 저장할 수 없게 됩니다.
• UNIQUE: 해당 필드는 서로 다른 값을 가져야만 합니다.
• PRIMARY KEY: 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
• FOREIGN KEY: 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
• DEFAULT: 해당 필드의 기본값을 설정합니다.

ALTER

ALTER 문을 사용하여 데이터베이스와 테이블의 내용을 수정할 수 있습니다.

ALTER DATABASE 데이터베이스이름 CHARACTER SET=문자집합이름
ALTER DATABASE 데이터베이스이름 COLLATE=콜레이션이름

ALTER TABLE 테이블이름 ADD 필드이름 필드타입

ALTER TABLE 테이블이름 DROP 필드이름

ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입

DROP

DROP 문을 사용하여 데이터베이스와 테이블을 삭제할 수 있습니다.

DROP DATABASE 데이터베이스이름

DROP TABLE 테이블이름
TRUNCATE TABLE 테이블이름

DROP DATABASE IF EXISTS 데이터베이스이름
DROP TABLE IF EXISTS 테이블이름

INSERT

INSERT INTO 문을 사용하여 테이블에 새로운 레코드를 추가할 수 있습니다.

INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
   VALUES (데이터값1, 데이터값2, 데이터값3, ...)

INSERT INTO 테이블이름
   VALUES (데이터값1, 데이터값2, 데이터값3, ...)

UPDATE

UPDATE 문을 사용하여 레코드의 내용을 수정할 수 있습니다.

UPDATE 테이블이름
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ...
WHERE 필드이름=데이터값

DELETE

DELETE 문을 사용하여 테이블의 레코드를 삭제할 수 있습니다.

DELETE FROM 테이블이름
WHERE 필드이름=데이터값

SELECT

SELECT 문을 사용하여 테이블의 레코드를 선택할 수 있습니다.

SELECT 필드이름
FROM 테이블이름
[WHERE 조건]

SELECT *
FROM 테이블이름

SELECT DISTINCT 필드이름
FROM 테이블이름

SELECT 필드이름
FROM 테이블이름
ORDER BY 필드이름, 필드이름, ...

SELECT 필드이름 AS 별칭
FROM 테이블이름
 
SELECT 필드이름
FROM 테이블이름 AS 별칭

연산자 우선순위(operator precedence)

우선순위

연산자

1	INTERVAL
2	BINARY, COLLATE
3	!
4	- (단항 연산자), ~ (비트 연산자)
5	^
6	*, /, DIV, %, MOD
7	- (이항 연산자), +
8	<<, >>
9	&
10	|
11	= (관계 연산자), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
12	BETWEEN, CASE, WHEN, THEN, ELSE
13	NOT
14	AND, &&
15	XOR
16	OR, ||
17	= (대입 연산자), :=

CASE

CASE 연산자는 값을 서로 비교하거나, 표현식의 논리값에 따라 다른 값을 반환합니다.

1. 값을 비교하는 경우
CASE value
  WHEN [compare_value] THEN result
  [WHEN [compare_value] THEN result] ...
  [ELSE result]
END

2. 조건의 논리값 비교
CASE
  WHEN [condition] THEN result 
  [WHEN [condition] THEN result] ...
  [ELSE result]
END

IF

IF() 함수는 첫 번째 인수로 전달받은 표현식의 논리값에 따라 다른 값을 반환합니다.

IF(expr1, expr2, expr3)

IFNULL

IFNULL() 함수는 첫 번째 인수로 전달받은 값이 NULL인지 아닌지를 검사하여 다른 값을 반환합니다.

IFNULL(expr1, expr2)

NULLIF()

NULLIF() 함수는 인수로 전달받은 두 값이 서로 같은지를 검사하여 다른 값을 반환합니다.

NULLIF(expr1, expr2)

CASE
  WHEN expr1 = expr2 
  THEN NULL 
  ELSE expr1 
END

LIKE

LIKE 연산자는 특정 패턴을 포함하는 데이터만을 검색하기 위해 사용합니다.

다음 예제는 Reservation 테이블에서 '장'으로 시작하는 이름(Name)으로 예약한 레코드를 선택하는 예제입니다.

SELECT * FROM Reservation
WHERE Name LIKE '장%';

SELECT * FROM Reservation
WHERE Name NOT LIKE '장%';

%: 0개 이상의 문자를 대체함.

_: 1개의 문자를 대체함.

REGEXP

LIKE 연산자보다 더욱 복잡한 패턴을 검색하고 싶을 때는 REGEXP 연산자를 사용할 수 있습니다.

REGEXP 연산자는 정규 표현식을 토대로 하는 패턴 매칭 연산을 제공합니다.

패턴

설명

.	줄 바꿈 문자(\n)를 제외한 임의의 한 문자를 의미함.
*	해당 문자 패턴이 0번 이상 반복됨.
+	해당 문자 패턴이 1번 이상 반복됨.
^	문자열의 처음을 의미함.
$	문자열의 끝을 의미함.
|	선택을 의미함.(OR)
[...]	괄호([]) 안에 있는 어떠한 문자를 의미함.
[^...]	괄호([]) 안에 있지 않은 어떠한 문자를 의미함.
{n}	반복되는 횟수를 지정함.
{m,n}	반복되는 횟수의 최솟값과 최댓값을 지정함.

다음 예제는 Name 필드의 값이 '홍'으로 시작하거나, '산'으로 끝나는 레코드를 선택하는 예제입니다.

SELECT * FROM Reservation
WHERE Name REGEXP '^홍|산$';

SELECT * FROM Reservation
WHERE Name NOT REGEXP '^홍|산$';

BINARY

BINARY 연산자는 뒤에 오는 문자열을 바이너리 문자열로 변환합니다.

BINARY 연산자를 이용하면 문자가 아닌 바이트를 기준으로 하여 비교나 검색 작업을 수행할 수 있습니다.

SELECT BINARY 'a' = 'A', 
'a' = 'A';

CAST

CAST() 함수는 인수로 전달받은 값을 명시된 타입으로 변환하여 반환합니다.

이때 변환하고자 하는 타입을 AS 절을 이용하여 직접 명시할 수 있습니다.

CAST(expr AS type)

AS 절에서 사용할 수 있는 타입은 다음과 같습니다.

1. BINARY
2. CHAR
3. DATE
4. DATETIME
5. TIME
6. DECIMAL
7. JSON (MySQL 5.7.8부터 제공됨)
8. NCHAR
9. SIGNED [INTEGER]
10. 10. UNSIGNED [INTEGER]

CONVERT

CONVERT() 함수도 CAST() 함수처럼 인수로 전달받은 값을 명시된 타입으로 변환하여 반환합니다.

CONVERT() 함수는 두 번째 인수로 변환하고자 하는 타입을 직접 전달할 수 있습니다.

CONVERT(expr, type)

CONVERT(expr USING transcoding_name)

NOT NULL

NOT NULL 제약 조건을 설정하면, 해당 필드는 NULL 값을 저장할 수 없습니다.

즉, 이 제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 합니다.

NOT NULL 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

CREATE TABLE 테이블이름
(
    필드이름 필드타입 NOT NULL,
    ...
)

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 NOT NULL

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 NOT NULL

UNIQUE

UNIQUE 제약 조건을 설정하면, 해당 필드는 서로 다른 값을 가져야 합니다.

즉, 이 제약 조건이 설정된 필드는 중복된 값을 저장할 수 없습니다.

UNIQUE 제약 조건은 CREATE 문으로 테이블을 생성할 때나, 나중에 ALTER 문으로 추가할 수도 있습니다.

CREATE TABLE 테이블이름
(
  필드명 필드타입 UNIQUE,
  ...
)

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름] UNIQUE (필드이름)
)

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 UNIQUE

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] UNIQUE (필드이름)

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 UNIQUE

ALTER TABLE 테이블이름
MODIFY COLUMN [CONSTRAINT 제약조건이름] UNIQUE (필드이름)

PRIMARY KEY

PRIMARY KEY 제약 조건을 설정하면, 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가집니다.

따라서 이 제약 조건이 설정된 필드는 NULL 값을 가질 수 없으며, 또한 중복된 값을 가져서도 안 됩니다.

이러한 PRIMARY KEY 제약 조건을 기본 키라고 합니다.

UNIQUE는 한 테이블의 여러 필드에 설정할 수 있지만, PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정할 수 있습니다.

이러한 PRIMARY KEY 제약 조건은 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 합니다.

CREATE TABLE 테이블이름
(
  필드이름 필드타입 PRIMARY KEY,
  ...
)

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)
)

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 PRIMARY KEY

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY

ALTER TABLE 테이블이름
MODIFY COLUMN [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

FOREIGN KEY

FOREIGN KEY 제약 조건을 설정한 필드는 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 합니다.

외래 키가 설정된 테이블에 레코드를 입력하면, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력됩니다.

즉, FOREIGN KEY 제약 조건은 하나의 테이블을 다른 테이블에 의존하게 만듭니다.

FOREIGN KEY 제약 조건을 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 합니다.

CREATE TABLE 테이블이름
(
  필드이름 필드타입,
  ...,
  [CONSTRAINT 제약조건이름]
  FOREIGN KEY (필드이름)
  REFERENCES 테이블이름 (필드이름)
)

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름]
FOREIGN KEY (필드이름)
REFERENCES 테이블이름 (필드이름)

1. CASCADE: 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블에서도 삭제와 수정이 같이 이루어집니다.
2. SET NULL: 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 NULL로 변경됩니다.
3. NO ACTION: 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조하는 테이블의 데이터는 변경되지 않습니다.
4. SET DEFAULT: 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정됩니다.
5. RESTRICT: 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없습니다.

DEFAULT

DEFAULT 제약 조건은 해당 필드의 기본값을 설정할 수 있게 해줍니다.

만약 레코드를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장합니다.

CREATE TABLE 테이블이름
(
  필드이름 필드타입 DEFAULT 기본값,
  ...
)

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 DEFAULT 기본값

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 DEFAULT 기본값

ALTER TABLE 테이블이름
ALTER 필드이름 SET DEFAULT 기본값

INNER JOIN

INNER JOIN은 ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만을 가져옵니다.

첫번째테이블이름
INNER JOIN 두번째테이블이름
ON 조건

첫번째테이블이름
JOIN 두번째테이블이름
ON 조건

SELECT *
FROM 첫번째테이블이름, 두번째테이블이름
WHERE 조건

LEFT JOIN

LEFT JOIN은 첫 번째 테이블을 기준으로, 두 번째 테이블을 조합하는 JOIN입니다. 

이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블의 필드 값은 그대로 가져옵니다.

하지만 해당 레코드의 두 번째 테이블의 필드 값은 모두 NULL로 표시됩니다.

첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건

RIGHT JOIN

RIGHT JOIN은 LEFT 조인과는 반대로 두 번째 테이블을 기준으로, 첫 번째 테이블을 조합하는 JOIN입니다. 

이때 ON 절의 조건을 만족하지 않는 경우에는 두 번째 테이블의 필드 값은 그대로 가져옵니다.

하지만 해당 레코드의 첫 번째 테이블의 필드 값은 모두 NULL로 표시됩니다.

첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건

UNION

UNION은 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용합니다.

이때 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 같아야 하며, 필드의 순서 또한 같아야 합니다.

SELECT 필드이름
FROM 테이블이름
UNION
SELECT 필드이름
FROM 테이블이름

UNION ALL

위의 예제처럼 UNION은 DISTINCT 키워드를 따로 명시하지 않아도 기본적으로 중복되는 레코드를 제거합니다.

따라서 이렇게 중복되는 레코드까지 모두 출력하고 싶다면, ALL 키워드를 사용해야 합니다.

SELECT 필드이름
FROM 테이블이름
UNION ALL
SELECT 필드이름
FROM 테이블이름

SUBQUERY

서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미합니다.

서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 내부쿼리(inner query)라고도 부릅니다.

서브쿼리는 반드시 괄호(())로 감싸져 있어야만 합니다. 

MySQL에서 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있습니다.

이러한 서브쿼리는 또 다시 다른 서브쿼리 안에 포함될 수 있습니다.

1. 서브쿼리는 쿼리를 구조화시키므로, 쿼리의 각 부분을 명확히 구분할 수 있게 해줍니다.
2. 서브쿼리는 복잡한 JOIN이나 UNION과 같은 동작을 수행할 수 있는 또 다른 방법을 제공합니다.
3. 서브쿼리는 복잡한 JOIN이나 UNION 보다 좀 더 읽기 편합니다.

FROM 절의 서브쿼리

서브쿼리는 SELECT 문의 FROM 절에서도 사용할 수 있습니다.

이때 서브쿼리에 의해 선택된 결과 집합은 FROM 절에서 하나의 테이블로써 사용됩니다.

SELECT 문의 FROM 절에서 사용되는 모든 테이블에는 이름이 필요합니다.

따라서 FROM 절에서 사용되는 서브쿼리는 위의 문법처럼 반드시 이름을 정의해야 합니다.