[MySQL] 기본 문법

---

해당 DataBase는 MySQL 공식사이트에서 제공하는 world.sql파일로 연습한 내용이다.

아래 사이트에서 world.sql 파일을 받고 db에 추가하면 된다.

dev.mysql.com/doc/world-setup/en/

MySQL :: Setting Up the world Database

본 포스팅은 상업적 의도가 없는 공부 목적으로 업로드 하였습니다.

이수안컴퓨터연구소 유튜브 채널의

MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database 을 보고 정리하였습니다.

링크: www.youtube.com/watch?v=vgIc4ctNFbc&list=PLAsbL2NvnmK7HZ4wyxdFkvqMTtu6Mr9gY&index=5&ab_channel=%EC%9D%B4%EC%88%98%EC%95%88%EC%BB%B4%ED%93%A8%ED%84%B0%EC%97%B0%EA%B5%AC%EC%86%8C

---

SHOW DATABASE

현재 서버에 어떤 DB가 있는지 알 수 있다. 

SHOW DATABASES

 

---

HOLD Up!

MySQL에서 구문을 실행할때 한 줄(세미콜론으로 구분)으로 실행할지 전체 구문을 실행할지 나뉜다.

전체 구문 실행 시: CRTL + ENTER

한 줄 실행 시: CTRL + SHIFT + ENTER

상단 아이콘의 번개모양(드래그 한, 줄 구문 실행), 번개i표시(전체구문실행) 으로 실행 할 수 있다.

---

USE

• 사용할 데이터 베이스를 지정한다.
• 지정해 놓은 후 다시 USE문을 사용 하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 해당DB에서 수행한다.
• Workbench 에서 Schemas에서 직접 선택 가능

USE DB이름

 

SHOW TABLE STATUS

데이터베이스 world의 테이블 정보 조회

SHOW TABLE STATUS;

 

DESCRIBE(DESC)

특정 테이블에 무슨 열이 있는지 확인한다.

DESCRIBE Table이름
DESC Table이름

 

#PROBLEM.1

country 테이블과 countrylanguage 테이블 정보 보기

DESCRIBE city;
DESC countrylanguage;

 

 

SELECT

• <SELECT ... FROM>
• 요구하는 데이터를 가져오는 구문
• 일반적으로 가장 많이 사용되는 구문
• 데이터베이스 내 테이블에서 원하는 정보를 추출
• SELECT의 구문형식

SELECT select_expr
	[FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}]

 

SELECT * FROM city

city테이블에 있는 전체 데이터들을 보여달라는 구문이다.

* 는 (해당 부분에서) 전체(ALL) 라는 의미를 가지고 있는 기호이다.

 

만약 Name의 컬럼만 보고 싶으면 다음과 같이 입력하면 된다.

SELECT Name FROM city;

또한 여러컬럼을 보고싶으면 아래처럼 입력하고 입력 순서에 따라서 데이터가 출력된다.

SELECT Population, Name FROM city;

 

WHERE

• SELECT절에서 특정 조건을 줘야할 때 사용한다.
• 조회하는 결과에 특정한 조건으로 원하는 데이터만 보고 싶을 때 사용한다.

SELECT 필드이름 FROM 테이블이름 WHERE 조건식;

• 조건이 없을 경우 테이블의 크기가 클수록 찾는 시간이 증가한다. 

 

관계 연산자의 이용

• OR 연산자
• AND 연산자
• 조건연산자 (=, <, >, <=, >=, <>, != 등)
• 관계 연산자(AND, OR, NOT 등)
• 연산자의 조합으로 데이터를 효율적으로 추출함

조건과 관계연산자를 조합해서 데이터를 내가 원하는 조건에 따라 만족하는 데이터를 추출할 때 사용한다.

 

#PROBLEM.2

한국에 있는 도시들 보기, 미국에 있는 도시들 보기, 한국에 있는 도시들 중에 인구수가 1,000,000이상인 도시 보기

먼저, DESC로 해당 컬럼의 요소들이 무엇이 있는지 파악한다. 

DESC city;

한국에 있는 도시들 보기

SELECT *
FROM city
WHERE CountryCode = 'KOR';

미국에 있는 도시들 보기

SELECT *
FROM city
WHERE CountryCode = 'USA';

한국에 있는 도시들 중에 인구수가 1,000,000이상인 도시 보기

SELECT *
FROM city
WHERE CountryCode = 'KOR'
AND Population >= 1000000;

 

BETWEEN

데이터가 숫자로 구성되어 있어 연속적인 값은 BETWEEN ... AND 사용 가능

SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000;

 

IN

이산적인 값의 조건에서는 IN() 사용 가능

SELECT *
FROM city
WHERE Name IN('seoul', 'New York', 'Tokyo');

 

#PROBLEM.3

한국, 미국, 일본의 도시들을 볼 때 어떻게 할까?

SELECT *
FROM city
WHERE CountryCode IN('KOR', 'USA', 'JPN');

 

LIKE

•  문자열의 내용 검색하기 위해 LIKE 연산자 사용한다
• 문자 뒤에 % -무엇이든(%) 허용
• 한 글자와 매치하기 위해서는 '_' 사용한다.
• LIKE 질의 라고 한다

SELECT *
FROM city
WHERE CountryCode LIKE 'KO_';

찾고자 하는 데이터 값이 생각나지 않는 경우

SELECT *
FROM city
WHERE Name LIKE 'Tel %'

 

Sub Query

• 커리문 안에 또 커리문이 들어 있는 것
• 서브 쿼리의 결과가 둘 이상이 되면 에러 발생

SELECT *
FROM city
WHERE CountryCode = (	SELECT CountryCode
			FROM city
			WHERE Name = 'seoul');

이 쿼리 안의 쿼리를 실행시키면

SELECT CountryCode
FROM city
WHERE Name = 'seoul';

쿼리안의 결과는 KOR이며

전체 코드는 KOR인 city을 다 보여달라는 뜻이므로 결과는 다음과 같다

SELECT *
FROM city
WHERE CountryCode = (	SELECT CountryCode
			FROM city
			WHERE Name = 'seoul');

 

ANY(SOME)

• 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능
• SOME은 ANY와 동일한 의미로 사용
• = ANY 구문은 IN 과 동일한 의미

SELECT *
FROM city
WHERE Population > ANY (SELECT Population
			FROM city
			WHERE District = 'New York');

NewYork주의 도시가 6개 6개의 population 어떤것이든 만족하면 출력하라.

 

ALL

서비쿼리의 여러 개의 결과를 모두 만족 시켜야 한다. 

여기서, 위의 ANY 문을 사용해서 population값을 출력했다면 가장 작은 population을 만족해도 출력이 되지만, 

ALL은 population값을 출력할 때 모든 경우를 만족해야하므로 population값이 가장 큰 것을 만족해야 출력할 수 있다.

SELECT *
FROM city
WHERE Population > ALL (SELECT Population
			FROM city
			WHERE District = 'New York');

 

ORDER BY

• 결과가 출력되는 순서를 조절하는 구문
• 기본적으로 오름차순(ASCENDING)정렬 (ASC는 default 이므로 생략가능)
• 내림차순(DESCENDING)으로 정렬 (적용시 열 이름 뒤에 적어야 함)

SELECT *
FROM city
ORDER BY Population DESC;

SELECT *
FROM city
ORDER BY Population;

오름차순시 생략 가능

 

ORDER BY 구문을 혼합해 사용하는 구문도 가능하다.

SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC;

 

#PROBLEM.4

인구수로 내림차순하여 한국에 있는 도시 보기

SELECT *
FROM city
WHERE CountryCode = 'KOR'
ORDER BY Population DESC;

 

국가면적 크키로 내림차순하기 (Country table)

DESC country;

찾기

SurfaceArea

SELECT *
FROM country
ORDER BY SurfaceArea DESC;

 

DISTINCT

• 중복된 것을 1개씩만 보여주면서 출력
• 테이블의 크기가 클수록 효율적

EX)

SELECT CountryCode
FROM city;

중복 있음

SELECT DISTINCT CountryCode
FROM city;

중복 제거됨

LIMIT

• 출력 개수를 제한
• 상위의 N개만 출력하는 'LIMIT N' 구문
• 서버의 처리량을 많이 사용해 서버의 전반적인 성능을 나쁘게 하는 악성 쿼리문 개선할 때 사용

SELECT * 
FROM city
ORDER BY Population DESC
LIMIT 10;

MySQL에서 LIMIT을 안걸고 질의를 해도 DB에 수많은 데이터가 로드되는 것을 방지하기 위해

default로 LIMIT이 걸려있다.

 

GROUP BY

• 그룹으로 묶어주는 역할
• 집계함수 (Aggregate Function)를 함께 사용
  • AVG() : 평균
  • MIN(): 최소값
  • MAX() : 최대값
  • COUNT() : 행의 개수
  • COUNT(DISTINCT) : 중복 제외된 행의 개수
  • STDEV() : 표준 편차
  • VARIANCE() : 분산
• 효율적인 데이터 그룹화(GROUPING)

SELECT CountryCode, AVG(Population)
FROM city
GROUP BY CountryCode;

• 읽기 좋게 하기 위해 별칭(Alias) 사용

SELECT CountryCode, AVG(Population) AS 'Average'
FROM city
GROUP BY CountryCode;

 

#PROBLEM.5

전체 도시는 몇개인가?

SELECT COUNT(*)
FROM city

전체 도시들의 평균 인구수는?

SELECT AVG(Population)
FROM city

 

HAVING

• WHERE과 비슷한 개념으로 조건 제한
• 집계 함수에 대해서 조건 제한하는 편리한 개념
• HAVING절은 반드시 GROUP BY절 다음에 나와야함

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000;

 

ROLLUP

• 총합 또는 중간합계가 필요한 경우 사용
• GROUP BY절과 함께 WITH ROLLUP문 사용
• 각각의 순서에 따라서 큐브(combination형태로보는 것)와 다르게 permutation형태로 각각의 모든 집계결과를 보여줌

SELECT CountryCode, Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP;

중간에 보면 2332100의 데이터가 추가된 것을 볼 수 있다 

위의 186800 + 1780000 + 127800 + 237500을 전부 더해서 나온 결과임을 확인 할 수 있다.

 

JOIN

JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현

그런데 아래의 코드를 보면 counrty와 city를 합치려는데 이 때 조건이 있다.

city 테이블의 countrycode(국가코드) 컬럼과 country 테이블의 code컬럼이 같은 항목을 기준으로 JOIN을 하라는 의미이다.

코드는 다음과 같으며, 

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code;

world.sql은 다음 항목들로 구성되어있다.

 

왜

JOIN country ON city.CountryCode = country.Code;

해당 쿼리문을 사용하였는지 비교하자면 

둘이 비교해보자

SELECT Code from country;

SELECT CountryCode from city;

동일한 항목이 있으며 이 항목들끼리 합쳐서 JOIN 쿼리를 수행하면 아래와 같은 결과를 얻을 수 있다. 

 

 

#PROBLEM.6

city, country, countrylanguage 테이블 3개를 JOIN 하기

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode;