MySQL_명령어 기초 정리

sql에 대한 대략적인 개념을 익혔으니 명령어 위주로 정리를 해보고자 한다. 개념은 아래의 링크를 통해서 확인하자.

 

2020.12.11 - [전산학/데이터베이스(DATABASE)] - MySQL_설치 기초

2020.12.13 - [전산학/데이터베이스(DATABASE)] - MySQL_기초 정리

 

 

기본적으로 SQL의 특징은 크게 3가지로 나뉜다. SQL 문 끝에는 항상 세미콜론을 써준다. 그리고 SQL은 공백이나 개행이 자유롭기 때문에 띄어쓰기든 탭이든 엔터든 모두 같은 한칸 띄어쓰기로 판단한다. 마지막으로는 대소문자는 자유롭다. 그러나 가독성을 위해 예약어는 대문자로 적어주는 것이 관례임을 참고하자.

 

 

굵은 색의 명령어를 본 후에 무슨내용인지 생각을 하자. 그 후에 아래의 설명을 확인하자. 전체 글을 다 읽을 수 있다면 그대는 SQL의 기초는 마쳤다고 할 수 있다.

 

 

CREATE DATABASE hanpy;

hanpy라는 데이터베이스를 만드는 명령어 이다. 

 

 

 

Primary key(기본키) : 데이터베이스에는 기본키가 존재한다. 기본키란 테이블에서 하나의 row를 고유하게 식별할 수 있도록 해주는 column이다. 예를들어 회원 정보가 있다면, 회원들을 각각을 식별가능하게 만드는 인위적인 키 값이라고 할 수 있다.

 

기본키는 Natual Key와 Surrogate Key로 2가지 종류로 나눌 수 있다. Natual Key는 회원정보에서 각각의 회원들은 email이 다를 것이다. 즉, 이메일을 기본키로 지정할 경우에 Natual Key라고 할 수 있다. Surrogate Key는 Natual Key와 다르게 이메일 같은 어떤 속성을 직접 나타내는 컬럼이 아니고, 주로 각각의 회원을 1부터 순차적으로 정가하는 숫자로 기본키를 지정하는 경우가 Surrogate key라고 한다.

 

 

SELECT

테이블의 데이터를 조회할 떄 사용하는 명령어이다.

 

 

SELECT * FROM member WHERE age = 28;

• SELECT 문에서 특정 row를 선별해서 조회할 때는 WHERE 절을 사용한다

WHERE는 원하는 row를 뽑기 위해 사용할 수 있는 값이다. member라는 테이블에서 age라는 컬럼의 값이 28인 row를 모두 가져오라는 말이다. * 인 위치에는 조회할 컬럼들을 적어주면 되는데 여기서는 쓰인 *은 모든 컬럼을 뽑아달라는 이야기 이다.

 

 

SELECT * FROM member WHERE age != 28;

SELECT * FROM member WHERE age <> 28;

위의 2개의 명령어는 같은 말이다. 28살이 아닌 사람을 모두 불러온다.

 

 

SELECT * FROM member WHERE age in (28, 30);

in을 활용하여 특정 조건을 여러개 주기가 가능하다. 28과 30살만 뽑는다.

 

 

SELECT * FROM member WHERE age BETWEEN 20 AND 29;

• 특정 구간을 조건으로 걸고 싶을 때는, BETWEEN a AND b(a부터 b까지) 구문을 사용한다.

BETWEEN을 사용하여 사이 값들을 조회가 가능하다. 20 이상 29 이하 나이를 조회한다.

 

 

SELECT * FROM member WHERE age NOT BETWEEN 20 AND 29;

• 특정 구간을 포함시키지 않을려면 NOT BETWEEN a AND b를 사용한다.

BETWEEN 앞에 NOT을 붙여서 20~29가 아닌 row를 조회가 가능하다.

 

 

SELECT * FROM member WHERE date_type > '2021-03-16';

WHERE를 통해서 date타입인 컬럼을 시간으로 조회가 가능하다. 따라서 2021-03-16 이후에 데이터를 모두 추출하는 명령어 이다.

 

 

SELECT * FROM member WHERE date_type BETWEEN '2021-01-01' AND '2021-12-31';

이러한 명령어는 2021년도의 데이터를 모두 뽑는 의미이다.

 

 

SELECT * FROM member WHERE address LIKE '서울%';

LIKE는 LIKE뒤에 적혀진 값들이 포함된 데이터를 뽑으라는 의미이다.

• '서울%'의 의미는 서울로 시작하고 뒤에는 임의의 길이인 모든 데이터를 뽑으라는 의미이다.
• '%hanpy%'라는 의미는 hanpy라는 단어가 포함된 모든 데이터를 뽑으라는 의미가 된다.
• LIKE를 사용해서 문자열 패턴이 매칭되는 컬럼의 값을 가진 row들을 조회하려고 한다면, %(임의의 길이를 가진 문자열), _(한 자리의 문자)의 의미를 알자

 

문자열 매칭 시 주의사항

• LIKE : 문자열 패턴 매칭 조건을 걸기 위해 사용되는 키워드
• % : 임의의 길의를 가진 문자열(0자도 포함)
• _ : 한 자리의 문자

우리는 위와 같이 특정 문자열을 매칭 시켜 찾는 방법을 이해했다. 그렇다면 문자열에 %이나 _ 같은 기호들이 포함된 값들을 찾고 싶을 때는 어떻게 해야할까??? 아래의 내용을 통해 관련 내용을 이해해 보자.

 

예를 들면 % 앞에 역슬래쉬(backslash) 기호를 '\%'와 같이 붙여주면 %가 문자로서의 '%'로 해석이 된다. 따라서 정리해 보면 아래와 같다.

• `'`작은따옴표 이스케이핑 => `\'`
• `_`언더바 이스케이핑 => `\_`
• `""`큰따옴표 이스케이핑 => `\"`

참고해서 헷갈리지 않도록 하자.

 

 

SELECT * FROM member WHERE address LIKE '서_%'

'서_%'에서 언더바(_)의 의미는 문자 하나를 의미한다. 따라서 '서'로 시작하고 그후에 한글자가 포함뒨 모든 데이터를 뽑는다.

 

 

 

위에서 간단히 맛만 본 DATE 타입 컬럼을 다루는 함수들에 대해 집중적으로 알아보자. DATE 타입이란 'YYYY-MM-DD' 형태로 이루어진 값들이다. 시간적 개념이 포함되어 있기 때문에 시간을 기준으로 데이터를 쪼개서 추출을 한다.

 

 

SELECT * FROM member WHERE YEAR(data_type) = '2021';

데이터 타입을 기준으로 YEAR를 사용하여 2021년도의 데이터만 뽑는 방법이다.

 

 

SELECT * FROM member WHERE MONTH(data_type) IN (6, 7, 8);

• 조건을 걸 때 OR이 너무 많아질 때, 그 대신에 IN을 사용할 수 있다.

MM인 월 기준으로 6, 7, 8월인 날짜만 뽑는 방법이다.

 

 

SELECT * FROM member WHERE DAYOFMONTH(data_type) BETWEEN 1 AND 15;

DAYOFMONTH 함수는 날짜에서 일만 뽑아내는 함수다. 이때 between을 사용해서 1일에서 15일 사이의 값만 뽑아낸다.

 

 

SELECT email, data_type, DATEDIFF(data_type, '2021-01-01') FROM member;

DATEDIFF(날짜 a, 날짜 b) 는 a와 b의 일수 차이를 알려준다. 즉, '2021-01-01' 기준으로 몇 일 이후인지를 알 수 있다.

이렇게 조회를 할 때는 원래의 컬럼이 아니더라도 조회가 출력되는 값 옆에 붙여서 계산된 새로은 columns을 출력해 준다. 즉, 출력된 컬럼은 email, data_type, DATEDIFF(~) 이렇게 3개의 열이 출력된다.

 

 

SELECT email, data_type, CURDATE(), DATEDIFF(data_type, CURDATE()) FROM member;

총 4개의 컬럼이 출력값으로 나올 것이다. 그리고 CURDATE()는 오늘 날짜이다. 따라서 오늘 날짜를 기입할 필요없이 자동으로 CURDATE()를 사용하면 오늘 날짜가 뜨게 된다.

 

 

SELECT email, data_type, CURDATE(), DATEDIFF(CURDATE(), birthday) / 365 FROM member;

DATEDIFF부분을 보면 생일이 적혀있는 birthday 컬럼이 있다고 생각을 우선해보자. 현재 날짜에서 생일까지의 차이를 일수로 나타낸 값에 365를 나누게 되면 당연히 현재의 나이가 출력되게 된다.

 

 

SELECT email, data_type, DATE_ADD(date_type, INTERVAL 300 DAY) FROM member;

DATE_ADD(date_type, INTERVAL 300 DAY)의 뜻은 date_type 컬럼 값에 300일을 더한 날짜를 나타낸다.

반대로 DATE_ADD말고 DATE_SUB()를 사용하면 지정한 숫자만큼을 빼는 함수가 된다.

 

 

 

 

SELECT email, date_type, UNIX_TIMESTAMP(date_type) FROM member;

• Unix Timestamp는 1970년 1월 1일을 기준으로 특정 날짜, 시간까지 경과된 초로 그 날짜, 시간을 나타내는 표현법이다. 유닉스 계열의 운영체제에서 시간을 나타나기 위해 주로 사용되는 Unix TImestamp는 데이터베이스 뿐만 아니라 그 자체로도 IT 분야에서 중요한 개념이다. 만약 Unix Timestamp로 보이는 값을 만나면 MySQL에서는 FROM_UNIXTIME(Unix Timestamp 값) 함수를 써서 사람이 해석할 수 있는 형식의 날짜, 시간으로 변환할 수 있다.

위의 출력값은 대략 아래와 같을 것이다.

email	date_type	UNIX_TIMESTAMP(date_type)
hanpy2222@gmail.com	2020-03-16	162425000

위의 큰 숫자는 1970년 1월 1일 기준으로 몇 초가 지났는지를 나타내는 값이다. 따라서 위와 같이 큰 숫자가 포함되어 있다면 Unux timestamp값이 아닌지를 의심해 보고 아래와 같이 DATETIME 형식으로 변환을 해보면 된다.

 

 

SELECT email, date_type, FROM_UNIXTIME(UNIX_TIMESTAMP(date_type)) FROM member;

위의 명령어에 대한 출력값은 대략 아래와 같을 것이다.

email	date_type	FROM_UNIXTIME(UNIX_TIMESTAMP(date_type))
hanpy2222@gmail.com	2020-03-16	2020-03-16 00:00:00

 

위와 같은 방식으로 큰 숫자가를 DATETIME 형식으로 변환해 보면 된다. 

 

 

 

 

 

어느정도 기초는 끝났다. 이제 조건문을 통해 조금 더 심화된 내용을 확인해 보자.

 

```SQL
SELECT * FROM member
WHERE gender = 'm';
    AND address LIKE '서울$'
    AND age BETWEEN 25 and 29;
```
AND는 여러 조건의 컬럼을 만족 시킬 때 사용한다. 따라서 gender가 m이고 address 컬럼이 서울로 시작하고 age가 25이상 29 이하의 값을 도출한다.


```sql
SELECT * FROM member
WHERE MONTH(date_type) BETWEEN 3 AND 5
    OR MONTH(date_type) BETWEEN 9 AND 11;
```
OR는 앞뒤 조건 중 하나라도 만족할 경우 전부 구할 수 있다. 위 조건은 봄과 가을인 데이터만 불러오는 것이다.


```sql
SELECT * FROM member
WHERE (gender = 'm' AND height >= 180)
    OR (gender = 'f' AND height >= 170);
```

남자는 키가 180이 넘는 사람을 뽑고 여자이고 키가 170이 넘는 사람들만 뽑는다.



```SQL
SELECT * FROM member WHERE id = 1 OR id = 2
SELECT * FROM member WHERE id = 1 OR 2
```
위 두개의 명령어의 차이점을 알아야 한다. 이때 주의 할 점이 아래의 방식 처럼 적으면 MySQL에서 0 이외의 숫자는 True로 간주하여 2 부분이 항상 True가 되서 항상 True가 되어 모든 row을 출력한다..



```sql
SELECT * FROM menber
WHERE gender = 'f' OR age < 30 AND height > 180;
```
OR 보다는 AND가 우선순위임을 반드시 알아야 한다. 따라서 gender = 'f'인 사랑도 출력되므로 키가 180 이하의 사람도 출력된다. 그렇다고 각각의 우선순위를 모두 외울 필요는 없다. 왜냐하면 ()를 씌워주면 된기 때문이다.. 


```sql
SELECT * FROM menber
WHERE (gender = 'f' OR age < 30) AND height > 180;
```

AND와 OR은 우선순위 때문에 서로 꼬이는 경우가 많이 발생한다. 따라서 이러한 방식으로 괄호를 씌워줘서 헷갈리는 것들을 방지하자.

 

 

 

SELECT * FROM hanpy WHERE sentence LIKE BINARY '%g%'

SQL의 원칙은 대소문자를 구분하지 않는다고 했다. 그렇다면, 대문자나 특정 소문자를 구분해서 찾고 싶다면 어떻게 해야할까? LIKE BINARY를 사용하면 된다.

• BINARY란 ‘이진의, 과 1로 된’이라는 뜻을 가진다. 쉽게 말하면 문자를 이진법으로 바꿔서 찾으라는 말이된다. 따라서, BINARY를 붙여준다는 것은 해당 문자가 0과 1이 정확히 일치하는 것을 찾으라는 뜻으로 소문자와 대문자는 같은 알파벳이긴 하지만, 컴퓨터에서 0과 1의 조합으로 저장될 때 다른 값으로 저장되는 것이 이용한 탐색 방법이다. 따라서 BINARY를 붙이는 건 단지 알파벳 비교 뿐만 아니라 대소문자 구분까지 할 수 있도록 0과 1을 보는 수준까지 문자열 비교를 하라는 뜻이 된다.

 

 

 

이제 SQL 실력이 증가하고 있음이 느껴질 것이다. 이제 정렬에 대해 알아보자. 정렬이란 row들을 특정 컬럼을 기준으로 순서대로 출력함을 의미한다.

 

```sql
SELECT * FROM member
ORDER BY height;
```

- ORDER BY 뒤에는 정렬할 기준을 적어주면 된다.

- height 컬럼은 키를 나타낸다. 따라서 키가 작은 값부터 큰값으로 정렬이 진행된다.(오름차순)



```sql
SELECT * FROM member
ORDER BY height ASC;
```

- ORDER BY height 뒤에 ASC나 DESC로 오름차순인지 내림차순인지를 지정할 수 있다.

- 기본적으로 오름차순 정렬이 디폴트이므로 ASC를 생략해도 동일하게 오름차순으로 정렬이 진행된다.


```SQL
SELECT * FROM member
ORDER BY height DESC;
```
DESC는 descending의 약어로 내려가는, 내림차순의 이라는 의미이다.

좀 더 업그래이드 해서 조건을 적용해서 정렬을 진행해 보자.

```sql
SELECT * FROM member
WHERE gender = 'm'
AND weight >=70
ORDER BY height ASC;
```
문법상 WHERE은 ORDER BY 앞에 나와야 함을 기억하자.
남자와 몸무게가 70이상인 row들 중에 오른차순으로 한다.



```sql
SELECT * FROM member
ORDER BY YEAR(type_date) DESC, email ASC;
```
- 위와 같이 여러 컬럼들에 정렬을 적용하기가 가능하다.
- 우선은 연도별로 내림차순 정렬 후에, 중복되는 나머지 부분은 이메일 기준으로 오름차순으로 정렬함을 의미한다.
- 즉, ORDER BY 뒤에 여러개의 정렬기준을 적을 수 있다. 먼저 쓴 컬럼을 우선으로 정렬이 차례대로 수행된다.



주의 : 숫자값이 담긴 컬럼을 정렬 기준으로 할 때는 그 컬럼의 데이터 타입이 숫자형(INT)인지, 문자열형(TEXT)인지에 따라 출력값이 달라지는 것을 인지하자.

• int타입은 숫자의 대소를 기준으로 정렬
• TEXT는 한 문자 한 문자씩 그  문자의 순서를 배교해서 정렬된다. 예를들어 '323242' '81'이 있다면, 가장먼저 앞자기 숫자인 3과 8을 비교해서 실제는 323242가 81보다 크지만, 결과는 323242를 더 작은 값으로 인식한다.
• TEXT 타입인 컬럼에 있는 숫자값들을, 그냥 INT 등의 숫자형 타입으로 보고 정렬하는 방법은, 그 컬럼의 값들의 데이터 타입을 일시적으로 변경해주면 되는데, 이때 사용하는 것이 CAST 이다.(CAST는 ‘틀을 만들다', '틀을 정하다'라는 뜻을 가진 영어 단어)

```sql
SELECT * FROM member
ORDER BY CAST(data_column AS signed) ASC;
```
- CAST(컬럼이름 AS signed)라는 표현식은 바로 특정 컬럼에 존재하는 값들의 데이터 타입을 일시적으로 signed라는 데이터 타입으로 변환하라는 뜻이다.
- signed는 양수와 음수를 포함한 모든 정수를 나타낼 수 있는 데이터 타입이다.
- 결과 값을 보면 data의 컬럼 값들이 일시적으로 signed 타입으로 변환 해석 되어 INT와 같은 결과가 나온다.

정리하면 어떤 컬럼의 숫자값들이 TEXT 같은 문자열 타입으로 저장돼있지만, 정렬 기준으로 쓸 때는 숫자형으로 사용하고 싶다면 CAST 함수를 쓰면 된다. 

 만약 문자열 타입으로 저장된 숫자값에 소수점이 포함되어 있다면 signed 자레에 대신 decimal(소수점이 있는 수를 나타내는 타입)을 적어주고 사용하면 된다.


추가 내용은 매주 토요일마다 업데이트 할 예정이다.