이연걸

이연걸

끊임없이 연구하는 개발자가 되자

[SQL] SQL 기초

Updated:

개요

SQL에 대한 기본적인 내용과 사용 방법들을 정리한다. MySQL Workbench를 이용하였다.

SQL의 분류

SQL은 다음과 같이 3가지 분류로 나누어진다.

  • DDL (Data Definition Language, 데이터 정의어)
  • DML (Data Manipulation Language, 데이터 조작어)
  • DCL (Data Control Language, 데이터 제어어)

  1. DDL (데이터 정의어)

    데이터 정의 언어로 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터 베이스 개체를 생성/삭제/변경하는 역할이다.

    명령어 기능
    CREATE SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
    ALTER TABLE에 대한 정의 변경
    DROP SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

  2. DML (데이터 조작어)

    데이터 조작 언어로 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용하는 언어이다. DML 구문이 사용되는 대상은 테이블의 행이고 DML이 사용되기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 한다.

    명령어 기능
    SELECT 테이블에서 조건에 맞는 튜플 검색
    INSERT 테이블에 새로운 튜플 삽입
    DELETE 테이블에서 조건에 맞는 튜플 삭제
    UPDATE 테이블에서 조건에 맞는 튜플의 내용 변경

  3. DCL (데이터 제어어)

    데이터 제어 언어로 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문이다.

    명령어 기능
    COMMIT 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장, 작업이 정상적으로 완료되었음을 알림
    ROLLBACK 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
    GRANT 데이터베이스 사용자에게 사용자 권한 부여
    REVOKE 데이터베이스 사용자의 사용권한 취소

SQL 기초

SHOW DATABASES

  • 현재 서버에 어떤 DB가 있는지 보기

image

USE

  • 사용할 데이터베이스 지정
  • 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행

image

SHOW TABLES

  • 데이터베이스 world의 테이블 이름 보기

    image

SHOW TABLE STATUS

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

image

DESCRIBE (DESC)

  • city 테이블에 무슨 열이 있는지 확인
    • DESCRIBE city;
    • DESC city;

image

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

image

image

SELECT

  • <SELECT… FROM>
  • 요구하는 데이터를 가져오는 구문
  • 일반적으로 가장 많이 사용되는 구문
  • 데이터베이스 내 테이블에서 원하는 정보를 추출
  • SELECT의 구문 형식
SELECT select_expr
		[FROM table_referneces]
		[WHERE where_condition]
		[GROUP BY {col_name | expr | position}]
		[HAVING where_condition]
		[ORDER BY {col_name | expr | position}]

image

  • SELECT 열 이름
    • 테이블에서 필요로 하는 열만 가져오기 가능
    • 여러 개의 열을 가져오고 싶을 때는 콤마로 구분
    • 열 이름의 순서는 출력하고 싶은 순서대로 배열 가능

image

SELECT FROM WHERE

기본적인 WHERE절

  • 조회하는 결과에 특정한 조건으로 원하는 데이터만 보고 싶을 때 사용
  • SELECT 필드이름 FROM 테이블이름 WHERE 조건식;
  • 조건이 없을 경우 테이블의 크기가 클수록 찾는 시간과 노력이 증가

image-20210225193304625

관계 연산자의 사용

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

MySQL 함수 및 연산자

image

한국에 있는 도시들 보기

image

미국에 있는 도시들 보기

image

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

image

BETWEEN

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

SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000

IN

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

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

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

image

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 = 'Seuol');

서울이라는 도시의 CountryCode(‘KOR’)의 도시들을 SELECT

ANY

  • 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능

  • SOME은 ANY와 동일한 의미로 사용
  • = ANY 구문은 IN과 동일한 의미
SELECT *
FROM city
WHERE Population > ANY ( SELECT Population
                   	 FROM city
                     WHERE District = 'New York');

서브쿼리에서 나오는 결과 중 하나라도 만족하면 출력을 한다.

ALL

  • 서브쿼리의 여러 개의 결과를 모두 만족시켜야 함
SELECT *
FROM city
WHERE Population > ALL ( SELECT Population
                         FROM city
                         WHERE District = 'New York')

가장 큰 값인 뉴욕의 인구보다 큰 도시를 출력하게 된다.

ORDER BY

  • 결과가 출력되는 순서를 조절하는 구문
  • 기본적으로 오름차순(ASCENDING) 정렬
  • 내림차순(DESCENDING)으로 정렬
    • 열 이름 뒤에 DESC 적어줄 것
  • ASC(오름차순)는 default이므로 생략 가능
SELECT *
FROM city
ORDER BY Population DESC

인구 수가 가장 높은 순(내림차순)으로 보여준다.

  • ORDER BY 구문을 혼합해 사용하는 구문도 가능
SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC

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

image

국가 면적 크기로 내림차순하여 나라 보기 (country table)

image

DISTINCT

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

LIMIT

  • 출력 개수를 제한
  • 상위의 N개만 출력하는 ‘LIMIT N’ 구문
  • 서버의 처리량을 많이 사용해 서버의 전반적인 성능을 나쁘게 하는 악성 쿼리문 개선할 때 사용
SELECT *
FROM city
ORDER BY Population DESC
LIMIT 10

GROUP BY

  • 그룹으로 묶어주는 역할
  • 집계 함수(Aggregate Function)을 함께 사용
    • AVG(): 평균
    • MIN(): 최소값
    • MAX(): 최대값
    • COUNT(): 행의 개수
    • COUNT(DISTINCT): 중복 제외된 행의 개수
    • STDEV(): 표준 편차
    • VARIANCE(): 분산
  • 효율적인 데이터 그룹화(Grouping)
  • 읽기 좋게 하기 위해 별칭(Alias) 사용
SELECT CountryCode, MAX(Population) AS 'Max'
From city
GROUP BY CountryCode

CountryCode를 묶는 데 Population이 가장 큰 것만 보여달라는 의미.

도시는 몇개인가?

image

도시들의 평균 인구수는?

image

HAVING

  • WHERE과 비슷한 개념으로 조건 제한
  • 집계 함수에 대해서 조건 제한하는 편리한 개념
  • HAVING절은 반드시 GROUP BY절 다음에 나와야 함
SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000

ROLLUP

  • 총합 또는 중간합계가 필요한 경우 사용
  • GROUP BY절과 함께 WITH ROLLUP문 사용
SELECT CountryCode, Name, Sum(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP

image

JOIN

  • JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현
SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code

image

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

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

image

MySQL 내장함수

  • 사용자의 편의를 위해 다양한 기능의 내장 함수를 미리 정의하여 제공
  • 대표적인 내장 함수의 종류
    • 문자열 함수
    • 수학 함수
    • 날짜와 시간 함수

LENGTH()

  • 전달받은 문자열의 길이를 반환
SELECT LENGTH('123456789')

CONCAT()

  • 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환
  • 전달받은 문자열 중 하나라도 NULL이 존재하면 NULL을 반환
SELECT CONCAT('My', 'sql Op', 'en Source')

My sql Open Source

LOCATE()

  • 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 찾아서 해당 위치를 반환
  • 찾는 문자열이 문자열 내에 존재하지 않으면 0을 반환
  • MySQL에서는 문자열의 시작 인덱스를 1부터 계산
SELECT LOCATE('abc', 'abababababcbacbac')

9

LEFT(), RIGHT()

  • LEFT(): 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환
  • RIGHT(): 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환
SELECT LEFT('MySQL is an open source relational database management system', 5)
SELECT RIGHT('MySQL is an open source relational database management system', 6)

MySQL
system

LOWER(), UPPER()

  • LOWER(): 문자열의 문자를 모두 소문자로 변경
  • UPPER(): 문자열의 문자를 모두 대문자로 변경
SELECT 
LOWER('MySQL is an open source relational database management system', 5),
UPPER('MySQL is an open source relational database management system', 6);

mysql is an open source relational database management system
MYSQL IS AN OPEN SOURCE RELATIONAL DATABASE MANAGEMENT SYSTEM

REPLACE()

  • 문자열에서 특정 문자열을 대체 문자열로 교체
SELECT REPLACE('MSSQL', 'MS', 'My')

MySQL

TRIM()

  • 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거
  • TRIM() 함수에서 사용할 수 있는 지정자
    • BOTH: 잔달받은 문자열의 양 끝에 존재하는 특정 문자를 제거 (기본 설정)
    • LEADING: 전달받은 문자열 앞에 존재하는 특정 문자를 제거
    • TRAILING: 전달받은 문자열 뒤에 존재하는 특정 문자를 제거
  • 만약 지정자를 명시하지 않으면, 자동으로 BOTH로 설정
  • 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거
SELECT TRIM('         MySQL          '),
TRIM(LEADING '#' FROM '###MySQL##'),
TRIM(TRAILING '#' FROM '###MySQL##');

MySQL
MySQL##
###MySQL

FORMAT()

  • 숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 ‘#,###,###.##’형식으로 변환
  • 반환되는 데이터의 형식은 문자열 타입
  • 두 번째 인수는 반올림할 소수 부분의 자릿수
SELECT FORMAT(123123123123.123123213, 6);

123,123,123,123.123123

FLOOR(), CEIL(), ROUND()

  • FLOOR(): 내림

  • CEIL(): 올림

  • ROUND(): 반올림

SELECT FLOOR(10,95), CEIL(10.95), ROUND(10.95)

10 11 11

SQRT(). POW(), EXP(), LOG()

  • SQRT(): 양의 제곱근
  • POW(): 첫 번째 인수로는 밑수를 전달하고, 두 번째 인수로는 지수를 전달하여 거듭제곱 계산
  • EXP(): 인수로 지수를 전달받아, $e$의 거듭제곱을 계산
  • LOG(): 자연로그 값을 계산
SELECT SQRT(4), POW(2,3), EXP(3), LOG(3)

3 8 20.085536923187668 1.0986122886681098

SIN(), COS(), TAN()

  • SIN(): 사인값 반환
  • COS(): 코사인값 반환
  • TAN(): 탄젠트값 반환
SELECT SIN(PI()/2), COS(PI()), TAN(PI()/4);

1 -1 0.9999999999999

ABS(), RAND()

  • ABS(X): 절대값을 반환
  • RAND(): 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성
SELECT ABS(-3), RAND(), ROUND(RAND()*100, 0);

3 0.4209238292929 45

NOW(), CURDATE(), CURTIME()

  • NOW(): 현재 날짜와 시간을 반환, 반환되는 값은 ‘YYYY-MM-DD HH:MM:SS’ 또는 YYYYMMDDHHMMSS 형태로 반환
  • CURDATE(): 현재 날짜를 반환, 이때 반환되는 값은 ‘YYYY-MM-DD’ 또는 YYYYMMDD 형태로 반환
  • CURTIME(): 현재 시각을 반환, 이때 반환되는 값은 ‘HH:MM:SS’ 또는 HHMMSS 형태로 반환
SELECT NOW(), CURDATE(), CURTIME();

DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

  • DATE(): 절달받은 값에 해당하는 날짜 정보를 반환
  • MONTH(), DAY(), HOUR(), MINUTE(), SECOND(): 각각 월, 일, 시간, 분, 초에 해당하는 값을 반환
SELECT
NOW(),
DATE(NOW()),
MONTH(NOW()),
DAY(NOW()),
HOUR(NOW()),
MINUTE(NOW()),
SECOND(NOW())

MONTHNAME(), DAYNAME()

  • MONTHNAME(): 월에 해당하는 이름을 반환
  • DAYNAME(): 요일에 해당하는 이름을 반환
SELECT
NOW(),
MOTHNAME(NOW()),
DAYNAME(NOW());

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

  • DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR(): 각각 해당 리자가 해당 주, 월, 연도에서 몇 번째 날인지 반환
SELECT
NOW(),
DAYOFWEEK(NOW())
DAYOFMONTH(NOW())
DAYOFYEAR(NOW());

DATE_FORMAT()

  • 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환
  • 참고 자료
SELECT
DATE_FORMAT(NOW(), '%D %y %a %d %m %n %j')

image

참고자료

MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database

Categories:

Updated:

Leave a comment

You may also enjoy