[SQL 문법] MySQL 데이터베이스 기본 문법 정리

• USE
  • 사용할 데이터베이스 지정
• SHOW TABLES
  • 데이터베이스 world의 테이블 이름 보기

# 사용할 데이터베이스지정
USE world

# 테이블 이름 보기
SHOW TABLES

# 테이블의 상태, 정보(버전, row의 개수 등)를 보여줘
SHOW TABLE STATUS;

 

• DESCRIBE (DESC)
  • 테이블에 무슨 열이 있는지 확인

# city 테이블에 무슨 열이 있는지, type은 뭔지 확인

DESCRIBE city;
DESC city;

 

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

DESC country;
DESC countrylanguage;

---

• SELECT ... FROM
  • 요구하는 데이터를 가져오는 구문
  • 데이터베이스 내 테이블에서 원하는 정보 추출

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY

# city 테이블에 있는 전체 데이터 가져오기
SELECT * FROM city

# city 테이블에서 Name, Population 열만 가져오기
SELECT Name, Population FROM city

 

• SELECT FROM ... WHERE
  • 특정한 조건으로 조회하는 결과 중 원하는 테이터만 보고 싶을 때
  • SELECT 필드이름 FROM 테이블 이름 WHERE 조건식

SELECT *
FROM city
WHERE Population >= 8000000;

# 또는

SELECT *
FROM city
WHERE Population < 8000000
AND Population >= 7000000;

# 또는

SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000;

 

Q. 한국에 있는 도시들 보기

Q. 미국에 있는 도시들 보기

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

# 1번
SELECT * FROM city
WHERE CountryCode = 'KOR';

# 2번
SELECT * FROM city
WHERE CountryCode = 'USA';

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

 

• IN
  • 이산적인(Discrete) 값의 조건에서는 사용 가능
  • (예) city 테이블에서 Name열 값이 () 안에 속하는 도시들만 뽑기

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

 

Q. 한국, 미국, 일본의 도시들 보기

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

---

• LIKE
  • 문자열의 내용 검색할 때. 문자열이 포함되는 거 뽑을 때
  • 한 글자와 매칭할 때: '_'
  • 뒤에 여러 글자가 올 때: '%'
  • (예) '주소' 열에서 '강원도' 지역만

SELECT * 
FROM city
WHERE CountryCode LIKE 'KO_'; 	# KO로 시작하는 3글자짜리 CountryCode

SELECT * 
FROM city
WHERE Name LIKE 'Tel %'; 	# 'Tel'로 시작하는 Name

 

• 서브 쿼리
  • 쿼리문 안에 쿼리문이 들어가있는 것
  • 서브 쿼리의 결과가 2개 이상이면 에러 발생

# Name이 'Seoul'인 도시 이름은 기억나지만, 'Seoul'이라는 도시가 어떤 CountryCode를 가지는 지는 모를 때
SELECT * 
FROM city
WHERE CountryCode = (	SELECT CountryCode
                        FROM city
                        WHERE NAME = 'SEOUL'	)

 

• ANY
  • 서브쿼리의 여러 결과 중 하나만 만족해도 가능
  • SOME은 ANY와 동일한 의미
  • =ANY는 IN과 동일한 의미

뉴욕에 해당하는 도시가 6개인데, 6개의 POPULATION보다 만족하기만 하면 출력

• ALL
  • 서브쿼리의 여러 결과를 모두 만족해야 함

=> 6개의 CITY 중 가장 인구가 많은 도시보다 인구많은 곳을 출력

• ORDER BY
  • 결과가 출력되는 순서를 조절
  • 기본적으로 오름차순 정렬ASC (생략 가능)
  • 내림차순으로 정렬하려면 '열' 이름 뒤에 DESC

CountryCode는 오름차순 정렬, Population은 내림차순 정렬

 

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

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

Q. 국가 면적 크기로 내림차순하여 나라 보기

SELECT *
FROM country
ORDER BY SurfaceArea DESC;

---

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

# city 테이블에 있는 CountryCode 중복하지 않고 보여주기

SELECT DISTINCT CountryCode
FROM city;

 

• LIMIT
  • 출력 개수를 제한
  • 상위 N개만 출력하는 'LIMIT N' 구문
  • 서버의 처리량을 많이 사용하는 악성 쿼리문을 개선할 때 사용

SELECT *
FROM city
ORDER BY Population DESC
LIMIT 10

 

• GROUP BY
  • 그룹으로 묶어주는 역할
  • 집계 함수를 함께 사용
    • AVG() : 평균
    • MIN() : 최소값
    • MAX() : 최대값
    • COUNT() : 행의 개수
    • COUNT(DISTINCT) : 중복되지 않는 행의 개수
    • STDEV() : 표준 편차
    • VARIANCE() : 분산
  • 읽기 좋기 위해 별칭(AS) 사용

# CountryCode로 묶어서 보여주고, 그런데 Population이 가장 큰 것만 알려줘

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode


# CountryCode로 묶어서 보여주고, 그런데 Population의 평균값을 'AVERAGE'열로 보여줘

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

'Average' 열로 표현

 

Q. 도시는 몇 개인가?

Q. 도시들의 평균 인구수는?

SELECT COUNT(*)
FROM city


SELECT AVG(Population)
FROM city

 

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

---

Q. city, country, countlanguage 테이블을 조인하고, 한국에 대한 정보만 뷰 생성하기

# USE world

CREATE VIEW allView AS
SELECT city.Name, country.SurfaceArea, city.population, countrylanguage.Language
FROM city
JOIN country ON city.CountryCode = country.code
JOIN countrylanguage ON city.CountryCode = countrylanguage.countryCode
WHERE city.CountryCode = 'KOR' ;

SELECT * FROM allView;

 

• UPDATE
  • 기존에 입력되어 있는 값 변경하는 구문
  • WHERE절 생략 가능하나 테이블의 전체 행의 내용 변경

UPDATE test
SET col1=1, col2 = 1.0, col3 = 'test'
WHERE id=1; 		#where 조건을 안 쓰면 이 테이블에 대한 모든 값이 다 바뀜

SELECT * FROM test;

id=1인 행의 col1, col2, col3이 각각 1, 1(1.0), test로 바뀐 것을 알 수 있음

• DELETE ( 복구 가능)
  • 행 단위로 삭제하는 구문
  • DELETE FROM 테이블이름 WHERE 조건;
  • 데이터는 지워지지만 테이블 용량은 줄어들지 않음 => 다시 복구(ROLLBACK)할 수 있으므로; 휴지통처럼;
  • 원하는 데이터만 지울 수 있음

DELETE FROM test
WHERE id=1; 	# where 안쓰면 다 날아감

SELECT * FROM test;

• TRUNCATE (복구 불가능)
  • 용량이 줄어들고, 인덱스 등도 모두 삭제
  • 삭제후 복구 불가
  • 테이블은 삭제하지 않고, 데이터만 삭제
  • 전체 다 삭제

TRUNCATE TABLE test;

SELECT * FROM test;

 

---

참고: 유튜브_이수안컴퓨터연구소_MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database

 

참고문헌

- SQL 함수 연산자, https://dev.mysql.com/doc/refman/8.0/en/functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators

- SQL 날짜 및 시간 함수, https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions