SQL - 기초

한영석·2022년 8월 29일
sql데이터 베이스
0

SQL

목록 보기
1/2
post-thumbnail

MySQL

1. Database를 사용해보자

  • Database 정의

    • Database란?

      • 여러사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합체

    • DBMS란?(Database Management System)

      • 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고 데이터베이스를 관리해주는 소프트웨어

    • 관계형 데이터베이스 란?(RDB:Relational Database)

      • 서로간에 관계가 있는 데이터 테이블들을 모아둔 데이터 저장공간

    • SQL 이란? (Structured Query Language)

      • 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어

SQL 구성

  • 데이터 정의 언어 (DDL : Data Definition Language)
    • CREATE, ALTER, DROP 등의 명령어
  • 데이터 조작 언어 (DML : Data Manipulation Language)
    • INSERT, UPDATE, DELELTE, SELECT 등의 명령어
  • 데이터 제어 언어 (DCL : Data Control Language)
    • GRANT, REVOKE, COMMIT, ROLLBACK 등의 명령어

Database 관리

  • root 계정으로 mysql에 접속
% mysql -u root -p
Enter password:
  • 현재 database 목록 확인
SHOW DATABASES;

  • Database 이름을 지정하여 생성
CREATE DATABASE dbname;
  • testdb 라는 이름의 데이터베이스 생성 및 확인
CREATE DATABASE testdb;

  • 해당 데이터베이스로 이동(사용)
USE dbname;
  • testdb로 이동
USE testdb;

  • Database 삭제
DROP DATABASE dbname;
  • testdb 삭제
DROP DATABASE testdb;

User 관리

  • User 조회
    • 사용자 정보는 mysql 에서 관리하므로 일단 mysql 데이터베이스로 이동 후 조회
use mysql;
SELECT host, user FROM user;

  • User 생성 - localhost
    • 현재 PC에서만 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'usernama'@'localhost' identified by 'password';
# 생성한다. 유저를 usernama의 유저를 localhost(현재 피씨)에서만 접속 가능하다 / 비밀번호는? by '이걸로..'
  • (생성예시) 현재 PC에서만 접속 가능한 사용자(noma, 1234) 생성
CREATE USER 'noma'@'localhost' identified by '1234';

  • User 생성 - %
    • 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'usernama'@'%' identified by 'password';
  • (생성예시) 외부에서 접속 가능한 사용자(noma, 5678) 생성
    • 호스트 정보가 다른 유저정보는 이름이 같아도 상관없다
CREATE USER 'noma'@'%' identified by '5678';


  • User 삭제
    • 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제
DROP USER 'usernama'@'localhost'
DROP USER 'usernama'@'%'
  • 외부에서 접근가능한 noma계정 삭제
DROP USER 'noma'@'%'

  • 현재 PC 에서 접근가능한 noma계정 삭제
DROP USER 'noma'@'localhost'

User 권한 관리

실습환경 만들기 1 - Database 만들기

  • 권한 관리를 실습하기 위한 Database (testdb) 생성
CREATE DATABASE testdb;

실습환경 만들기 2 - User 만들기

  • 권한 관리를 실습하기 위한 사용자 (ID : noma@localhost, password : 1234) 생성
CREATE USER 'noma'@'localhost' identified by '1234';

User 권한 확인

  • 사용자에게 부여된 모든 권한 목록을 확인
SHOW GRANTS FOR 'username'@'localhost';
  • 현재 PC에 접근가능한 noma의 권한 확인
SHOW GRANTS FOR 'noma'@'localhost';

  • 사용자에게 특정 데이터베이스의 모든 권한을 부여
GRANT ALL ON dbname.* to 'username'@'localhost';
  • 현재 PC에 접근가능한 noma 에게 testdb 의 모든 권한을 부여
GRANT ALL ON testdb.* to 'noma'@'localhost';
# 권한 확인 : SHOW GRANTS FOR 'noma'@'localhost';

  • 참고
    • 수정내용이 적용이 되지 않은 경우 새로고침
FLUSH PRIVILEGES;
  • 사용자에게 특정 데이터베이스의 모든 권한을 삭제
REVOKE ALL ON dbname.* from 'username'@'localhost';
  • 현재 PC에 접근가능한 noma 에게 testdb 의 모든 권한을 삭제
REVOKE ALL ON testdb.* from 'noma'@'localhost';
# 권한 확인 : SHOW GRANTS FOR 'noma'@'localhost';

2. Table을 사용해보자

실습환경 만들기

  • zerobase 라는 이름의 데이터베이스 생성
create database zerobase default character set utf8mb4;

Table 생성

  • 데이터베이스 안에서 실제 데이터가 저장되는 형태이고, 행(Row)과 열(Column)로 구성된 데이터 모음

  • Table 생성 문법

create table tablename
(
	columnname datatype,
    columnname datatype,
    ...
)
  • Table 생성 예제
    id(int)와 name(varchar(16))칼럼을 가지는 mytable 이라는 이름의 테이블 생성
create table mytable(id int, name varchar(16));

  • Table 목록 확인 문법
show tables;

  • Table 정보 확인 예제
  • mytable 테이블 정보 확인
desc mytable;

Table 변경

  • Table 이름 변경 문법
alter table tablename
rename new_tablename
  • Table 이름 변경 예제
    • mytable 이름을 person 으로 변경
alter table mytable rename person;

  • Table Column 추가 문법
alter table tablename
add column columnname datatype;
  • Table Column 추가 예제
    • person 테이블에 agee(double) 컬럼 추가
alter table person add column agee double;

  • Table Column 변경 문법 - DataType
alter table tablename
modify column columnname datatype;
  • Table Column 변경 예제 - DataType
    • person 테이블의 agee 컬럼의 데이터 타입을 int로 변경
alter table person
modify column agee int;

  • Table Column 변경 문법 - Name
alter table tablename
change column old_columnname new_columnname new_datatype;
  • Table Column 변경 예제 - Name
    • person 테이블의 agee 컬럼 이름을 age 로 변경
alter table person
change column agee age int;

  • Table Column 삭제 문법
alter table tablename
drop column columnname;
  • Table Column 삭제 예제
    • person 테이블의 age 컬럼을 삭제
alter table person
drop column age;

Table 삭제

drop table tablename;
  • Table 삭제 예제
    • person 테이블 삭제
drop table person;

3. INSERT

  • SELECT(선택), INSERT(삽입), UPDATE(수정), DELETE(삭제)

실습환경 만들기

실습할 데이터베이스로 이동

  • zerobase 사용(이동)
USE zerobase;

실습할 테이블 만들기

  • person 테이블 생성(칼럼 정보는 코드 참고)
CREATE TABLE person
(
	id int,
    name varchar(16),
    age int,
    sex CHAR
);

실습할 테이블 정보 확인

DESC person;

INSERT 문법 - 데이터를 추가하는 명령어

  • 입력한 컬럼이름의 순서와 값의 순서가 일치하도록 주의
INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT 예제 - 데이터를 추가하는 명령어

  • ID 값이 1인 이효리, 43세, 여자(F) 데이터 추가
  • 입력한 컬럼이름의 순서와 값의 순서가 일치하도록 주의
INSERT INTO person (id, name, age, sex)
VALUES (1, '이효리', 43, 'F');

INSERT 문법 - 모든 컬럼을 추가하는 경우

  • 모든 컬럼값을 추가하는 경우에는 다음과 같이 컬럼 이름을 지정하지 않아도 되지만, 입력하는 값의 순서가 테이블의 컬럼 순서와 일치하도록 주의
INSERT INTO tablename
VALUES (value1, value2, ...);

INSERT 예제 - 모든 컬럼을 추가하는 경우

  • ID 값이 2인 이상순, 48세, 남자(M) 데이터 추가
  • 입력한 컬럼이름의 순서와 값의 순서가 일치하도록 주의
INSERT INTO person
VALUES (2, '이상순', 48, 'M');

SELECT, WHERE

SELECT 문법 - 데이터 조회

  • 테이블 내의 특정 칼럼에 대한 데이터를 조회
SELECT column1, column2, ...
FROM tablename;

SELECT 예제 - 데이터 조회

  • person 테이블 내의 이름, 나이, 성별 데이터를 조회
SELECT name, age, sex FROM person;

SELECT 문법 - 모든 컬럼

  • 테이블 내의 모든 칼럼에 대한 데이터를 조회
SELECT * 
FROM tablename;

SELECT 예제 - 모든 컬럼

  • person 테이블 내의 모든 칼럼에 대한 데이터를 조회
SELECT * 
FROM person;

WHERE 문법 - SELECT

  • WHERE - 조건 : SQL 문에 조건을 추가하며 SELECT 뿐만 아니라 UPDATE 와 DELETE 에도 사용

  • 테이블 내에서 조건을 만족하는 데이터 조회

SELECT column1, column2, ...
FROM tablename
WHERE condition;

WHERE 예제 - SELECT

  • person 테이블 내에서 성별이 여자인 데이터 조회
SELECT *
FROM person
WHERE sex='F';

UPDATE, DELETE

UPDATE 문법 - 데이터 수정

  • 데이터 수정
UPDATE tablename # 수정할꺼야!
SET column1 = value1, column2 = value2, ... # 이렇게 변경할꺼야!
WHERE condition; # 이조건에 만족하는 데이터에 한해서!

UPDATE 예제 - 데이터 수정

  • 이효리 나이를 23세로 수정
UPDATE person
SET sge = 23
WHERE name='이효리';

DELETE 문법 - 데이터 삭제

  • 데이터 삭제
DELETE FROM tablename # 삭제할꺼야!
WHERE condition; # 이조건에 만족하는 데이터에 한해서!

DELETE 예제 - 데이터 삭제

  • 이상순 데이터 삭제
DELETE FROM person
WHERE name = '이상순';

4. ORDER BY

실습환경만들기

  • zerobase 사용(이동)
use zerobase;

실습할 테이블 정보

  • celeb 테이블 구경하기(자세한 내용은 기초 이후에)
    • int : 숫자
    • varchar(32) : 문자열
    • data : 날짜
    • char : 문자열 한글자

실습할 테이블 생성

create table celeb
(
    id int not null auto_increment primary key,
    name varchar(32) not null default '',
    brithday date,
    age int,
    sex char(1),
    job_title varchar(32),
    agency varchar(32)
);

실습할 테이블 정보 확인

desc celeb;

실습할 데이터 추가

insert into celeb values (1, '아이유', '1993-05-16', 29, 'F', '가수, 텔런트', 'EDAM엔터테이먼트');
insert into celeb values (2, '이미주', '1994-09-23', 28, 'F', '가수', '울림엔터테이먼트');
insert into celeb values (3, '송강', '1994-04-23', 28, 'M', '텔런트', '나무엑터스');
insert into celeb values (4, '강동원', '1981-01-18', 41, 'M', '영화배우, 텔런트', 'YG엔터테이먼트');
insert into celeb values (5, '유재석', '1972-08-14', 50, 'M', 'MC, 개그맨', '안테나');
insert into celeb values (6, '차승원', '1970-06-07', 48, 'M', '영화배우, 모델', 'YG엔터테이먼트');
insert into celeb values (7, '이수현', '1999-05-04', 23, 'F', '가수', 'YG엔터테이먼트');

  • 데이터 확인하기

ORDER BY

  • select 문에서 데이터를 특정 컬럼을 기준으로 오름차순 혹은 내림차순 정렬

ORDER BY 문법

  • ASC(Ascending) : 오름차순으로 정렬
  • DESC(Descending) : 내림차순으로 정렬
select column1, column2, ...
from tablename
order by column1, column2, ... asc | desc; 
# asc | desc 둘중하나를 선택하여 정렬하기

ORDER BY 예제

  • celeb 테이블에서 이름과 나이를 나이순으로 조회
select age, name
from celeb
order by age asc;
# 기본값이 오름차순이라 asc는 생략가능

ORDER BY 예제2

  • celeb 테이블에서 이름과 나이를 나이의 역순(내림차순)으로 조회
select age, name
from celeb
order by age desc;

ORDER BY 예제3

  • celeb 테이블에서 이름과 나이를 나이와 이름순으로 정렬하여 조회(ASC 를 생략해도 기본은 오름차순 정렬)
select age, name
from celeb
order by age, name;

ORDER BY 예제4

  • celeb 테이블에서 이름과 나이를 나이의 역순으로 정렬한 뒤 이름순으로 정렬하여 조회
select age, name
from celeb
order by age desc, name asc;

5. Comparison Operators

  • 실습할 데이터베이스로 이동
    • zerobase 사용(이동)
use zerobase;

  • 실습할 테이블 정보 확인
desc celeb;

  • 데이터 확인

Comparison Operators(비교연산자)

Comparison Operators 예제 1 - (A = B)

  • 나이가 29세인 데이터 검색
select name, age from celeb where age=29 order by age;

Comparison Operators 예제 2 - (A != B)

  • 나이가 29세가 아닌 데이터 검색
select name, age from celeb where age!=29 order by age;

Comparison Operators 예제 3 - (A > B)

  • 나이가 29세 보다 큰 데이터 검색
select name, age from celeb where age>29 order by age;

Comparison Operators 예제 4 - (A < B)

  • 나이가 29세 보다 작은 데이터 검색
select name, age from celeb where age<29 order by age;

Comparison Operators 예제 5 - (A >= B)

  • 나이가 29세 보다 크거나 같은 데이터 검색
select name, age from celeb where age>=29 order by age;

Comparison Operators 예제 6 - (A <= B)

  • 나이가 29세 보다 작거나 같은 데이터 검색
select name, age from celeb where age<=29 order by age;

Comparison Operators 예제 7 - (A <> B)

  • 나이가 29세 크거나 작은 (같지 않은) 데이터 검색
select name, age from celeb where age<>29 order by age;

6. Logical Operations

실습환경 만들기

  • zerobase 사용(이동)
    • 앞에서 만들었던 데이터 베이스인 zerobase 활용
use zerobase;

  • 실습할 테이블 정보확인
    • 만약 해당 데이터가 검색되지 않는다면 앞단계의 실습환경 만들기 참고
select * from celeb;

Logical Operations (논리 연산자)

AND 문법

  • 조건을 모두 만족하는 경우 TRUE
select column1, column2, ...
from tablename
where condition1 AND condition2 AND condition3 ...;

AND 예제 1

  • 나이가 29세이고 성별이 여성인 데이터 검색
select * 
from celeb
where age=29 AND sex='F';

AND 예제 2

  • 성별이 남자이고 나이가 40세 보다 큰 데이터를 이름의 역순으로 정렬하여 검색
select * 
from celeb
where age>40 AND sex='M'
order by name; 
# 역순이면 name desc;

OR 문법

  • 하나의 조건이라도 만족하는 경우 TRUE
select column1, column2, ...
from tablename
where condition1 OR condition2 OR condition3 ...;

OR 예제 1

  • 나이가 25세 보다 작거나 30세 보다 큰 데이터 검색
select *
from celeb
where age<25 OR age>30
order by age;

OR 예제 2

  • 나이가 29세 보다 작고 여자이거나, 나이가 30세 보다 크고 남자인 데이터를 나이와 성별 순으로 정렬하여 검색
select *
from celeb
where (age<29 and sex='F') OR (age>30 and sex='M')
order by age, sex;

OR 예제 3

  • YG엔터테이먼트 소속이거나 나무엑터스 소속인 연예인 중, 나이가 30세 보다 작은 데이터 검색
    • 우선 처리해야할 조건을 괄호로 묶어주어 우선적으로 처리하게두어 에러를 방지한다.
select *
from celeb
where (agency = 'YG엔터테이먼트' or agency = '나무엑터스') and age<30;

NOT 문법

  • 조건을 만족하지 못하는 경우 TRUE
select column1, column2, ...
from tablename
where not condition;

NOT 예제 1

  • 성별이 여자가 아닌 데이터 검색
select *
from celeb
where not sex='F';

NOT 예제 2

  • 소속사가 YG엔터테이먼트이면서 남자가 아니거나 직업이 가수이면서 소속사가 YG엔터테이먼트가 아닌 데이터 검색
select *
from celeb
where (agency='YG엔터테이먼트' and not sex='M') or (job_title='가수' and not agency='YG엔터테이먼트');

NOT 예제 3

  • 생일이 1990년 이후이면서 여자가 아니거나, 생일이 1979년 이전이면서 소속사가 안테나가 아닌 데이터 검색
select *
from celeb
where (brithday>19891231 and not sex='F') or (brithday<19800101 and not agency='안테나');

BETWEEN 문법

  • 조건값이 범위 사이에 있으면 TRUE
select column1, column2, ...
from tablename
where column1 BETWEEN value1 AND value2;

BETWEEN 예제 1

  • 나이가 20세에서 40세 사이의 데이터 검색
select *
from celeb
where age BETWEEN 20 and 40;

BETWEEN 예제 2

  • 생년월일이 1980년에서 1995년 사이가 아니면서 여자이거나, 소속사가 YG엔터테이먼트이면서 나이가 20세에서 45세 사이가 아닌 데이터 검색
select *
from celeb
where not brithday between 19800101 AND 19951231 and sex='F' or agency='YG엔터테이먼트' and not age between 20 and 45;

IN

IN 문법

  • 목록 안에 조건이 존재하는 경우 TRUE
select column1, column2, ...
from tablename
where column IN (value1, value2, ...);
# column의 값이 (값)안에 값이 존재하면 True로 반환한다.

IN 예제 1

  • 나이가 28세, 48세 중 하나인 데이터 검색
select *
from celeb
where age IN (28,48);

IN 예제 2

  • 소속사가 나무엑터스, 안테나, 울림엔터테이먼트가 아니면서, 성별이 여자이거나 나이가 45세 이상인 데이터 검색
select *
from celeb
where not agency IN ('나무엑터스', '안테나', '울림엔터테이먼트')
and (sex='F' or age>=45);

LIKE

LIKE 문법

  • 조건값이 패턴에 맞으면 TRUE
select column1, column2, ...
from tablename
where column LIKE pettern;

LIKE 예제 1

  • 소속사 이름이 'YG엔터테이먼트'인 데이터 검색
select *
from celeb
where agency LIKE 'YG엔터테이먼트';

LIKE 예제 2

  • 'YG'로 시작하는 소속사 이름을 가진 데이터 검색
select *
from celeb
where agency LIKE 'YG%';

LIKE 예제 3

  • '엔터테이먼트'로 끝나는 소속사 이름을 가진 데이터 검색
select *
from celeb
where agency LIKE '%엔터테이먼트';

LIKE 예제 4

  • 직업명에 '가수'가 포함된 데이터를 검색
select *
from celeb
where job_title LIKE '%가수%';

LIKE 예제 5

  • 소속사 이름의 두번째 글자가 G인 데이터를 검색
select *
from celeb
where agency LIKE '_G%';
# 세번째 글자인 경우 __G 처럼 _로 표현

LIKE 예제 6

  • 직업명이 '가'로 시작하고 최소 2글자 이상인 데이터 검색
select *
from celeb
where job_title LIKE '가_%';
# 시작을 2글자로 시작하게 만들기위해 가_ 처럼 _(언더바)로 글자수를 표현

LIKE 예제 7

  • 직업명이 '가'로 시작하고 최소 5글자 이상인 데이터 검색
select *
from celeb
where job_title LIKE '가____%';

LIKE 예제 8

  • 직업명이 '영'으로 시작하고 '모델'로 끝나는 데이터 검색
select *
from celeb
where job_title LIKE '영%모델';

LIKE 예제 9

  • 영화배우와 텔런트를 병행하는 연예인 검색
select *
from celeb
where job_title LIKE '%영화배우%' and job_title LIKE '%텔런트%';

LIKE 예제 10

  • 직업이 하나 이상인 연예인 중 영화배우 혹은 텔런트가 아닌 연예인 검색
select *
from celeb
where job_title LIKE '%,%' and 
not(job_title LIKE ('%영화배우%') or job_title LIKE ('%텔런트%'));

UNION

UNION 실습환경 만들기

실습환경 만들기

  • zerobase 사용 (이동)
    • 익숙해지기위해 자주 이동해보자..
use zerobase;

기존 테스트용 데이터 확인

  • 데이터 확인
select *
from celeb;

테스트용 테이블 생성

create table test1
(
    no int
);

create table test2
(
    no int
);

테스트 데이터 추가

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);

insert into test2 values (5);
insert into test2 values (6);
insert into test2 values (3);

데이터 확인

select * from test1;

select * from test2;

UNION 문법

  • 여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어주는 방법
    (주의: 칼럼의 개수가 같아야함)
    • UNION : 중복된 값을 제거하여 알려준다.
    • UNION ALL : 중복된 값도 모두 보여준다.
select column1, column2, ...
from tableA
union | union all
select column1, column2, ...
from tableB;

UNION 예제 1 - UNION ALL

  • test1 의 모든 데이터와 test2 의 모든 데이터를 중복된 값을 포함하여 검색
select * from test1
union all
select * from test2;

UNION 예제 1 - UNION

  • test1 의 모든 데이터와 test2 의 모든 데이터를 중복된 값을 제거하여 검색
select * from test1
union
select * from test2;

UNION 예제 2 - UNION ALL

  • 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION ALL 로 실행
select name, sex, agency from celeb where sex='F'
union all
select name, sex, agency from celeb where agency='YG엔터테이먼트';

UNION 예제 3(에러)

  • 가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와 1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 union으로 실행
select name, job_title from celeb where job_title like '%가수%'
union
select name, birthday, age from celeb where age between '1980-01-01' and '1989-12-31';

  • 이처럼 가져오는 칼럼의 개수가 다르면 에러가 나옴 (칼럼의 종류는 상관없음.)

JOIN

실습환경 만들기

실습용 데이터베이스 이동하기

  • zerobase 사용(이동)
use zerobase;

  • 데이터 확인
select * from celeb;

새로운 테이블 추가

create table snl_show
(
	id int not null auto_increment primary key,
    season int not null,
    episode int not null,
    broadcast_date date,
    host varchar(32) not null
);

  • 테이블 정보 확인
desc snl_show;

추가한 테이블에 데이터 추가

insert into snl_show values (1, 8, 7, '2020-09-05', '강동원');
insert into snl_show values (2, 8, 8, '2020-09-12', '유재석');
insert into snl_show values (3, 8, 9, '2020-09-19', '차승원');
insert into snl_show values (4, 8, 10, '2020-09-26', '이수현');
insert into snl_show values (5, 9, 1, '2021-09-04', '이병헌');
insert into snl_show values (6, 9, 2, '2021-09-11', '하지원');
insert into snl_show values (7, 9, 3, '2021-09-18', '제시');
insert into snl_show values (8, 9, 4, '2021-09-25', '조정석');
insert into snl_show values (9, 9, 5, '2021-10-02', '조여정');
insert into snl_show values (10, 9, 6, '2021-10-09', '옥주현');
  • 추가한 데이터 확인
select * from snl_show;

JOIN 기본

  • JOIN : 두개 이상의 테이블을 결합하는 것

    • inner join : 공통된 부분을 가져오는 것
    • full outer join : 모든 부분을 가져오는 것
    • left join : 왼쪽 테이블의 공통되지 않은 것을 포함해서 가져오는 것
    • right join : 오른쪽 테이블의 공통되지 않은 것을 포함해서 가져오는 것

  • 두개의 실습 테이블 확인

INNER JOIN 문법

  • 두개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식

  • INNER JOIN 문법

select column1, column2, ...
from tableA
inner join tableB
on tableA.column = tableB.column
where codition;

INNER JOIN 예제 1

  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 inner join
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
inner join snl_show
on celeb.name = snl_show.host;

LEFT JOIN 문법

  • 두개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식

  • LEFT JOIN 문법

select column1, column2, ...
from tableA
left join tableB
on tableA.column = tableB.column
where codition;

LEFT JOIN 예제 1

  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 LEFT JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
left join snl_show
on celeb.name = snl_show.host;

  • 없는 데이터는 null로 보여진다.

RIGHT JOIN 문법

  • 두개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식

  • RIGHT JOIN 문법

select column1, column2, ...
from tableA
right join tableB
on tableA.column = tableB.column
where codition;

RIGHT JOIN 예제 1

  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 RIGHT JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
right join snl_show
on celeb.name = snl_show.host;

FULL OUTER JOIN 문법

  • 두개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른영역을 모두 포함하는 조인방식

  • FULL OUTER JOIN 문법

select column1, column2, ...
from tableA
full outer join tableB
on tableA.column = tableB.column
where codition;

FULL OUTER JOIN 예제 1

  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
full outer join snl_show
on celeb.name = snl_show.host;

  • MySQL 에서는 FULL OUTER JOIN을 지원하지않으므로 다음의 쿼리로 같은 결과를 만들 수 있다.
select column1, column2, ...
from tableA
left join tableB on tableA.column = tableB.column
union
select column1, column2, ...
from tableA
right join tableB on tableA.column = tableB.column
where condition;
  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
left join snl_show 
on celeb.name = snl_show.host
union
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb
right join snl_show 
on celeb.name = snl_show.host;

SELF JOIN

SELF JOIN 문법

select column1, column2, ...
from tableA, tableB, ...
where codition;

SELF JOIN 예제 1

  • snl_show 에 호스트로 출연한 celeb 을 기준으로 celeb 테이블과 snl_show 테이블을 SELF JOIN
select celeb.id, celeb.name, snl_show.id, snl_show.host
from celeb, snl_show
where celeb.name = snl_show.host;

SELF JOIN 예제 2

  • celeb 테이블의 연예인 중, snl_show 에 host 로 출연했고 소속사가 안테나인 사람의 이름과 직업을 검색 
select celeb.name, celeb.job_title
from celeb, snl_show
where celeb.name = snl_show.host and celeb.agency='안테나';

SELF JOIN 예제 3

  • celeb 테이블의 연예인 중, snl_show 에 host 로 출연했고 영화배우는 아니면서 YG엔터테이먼트 소속이거나 40세 이상이면서 YG엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색 
select celeb.name, celeb.age, celeb.job_title, celeb.agency,
		snl_show.season, snl_show.episode
from celeb, snl_show
where celeb.name = snl_show.host and 
((not job_title like '%영화배우%' and 
agency='YG엔터테이먼트') or (age>=40 and 
agency != 'YG엔터테이먼트'));

SELF JOIN 예제 4

  • snl_show 에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보 검색
    • 동일한 칼럼명이 존재하는 경우에만 테이블명을 암시
select snl_show.id, season, episode, name, job_title 
from celeb, snl_show
where name = host;

SELF JOIN 예제 5

  • snl_show 에 출연한 celeb 중, 에피소드 7, 9, 10 중에 출연했거나 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 작년 9월15일 이후에 출연했던 사람을 검색
select name, season, episode, broadcast_date, agency
from celeb, snl_show
where name = host and 
(episode in (7, 9, 10) or agency like 'YG______') and
broadcast_date > '2020-09-15';

Concat

실습환경 만들기

  • zerobase 사용(이동)
use zerobase;

  • 데이터 확인
select * from celeb;

select * from snl_show;

CONCAT

  • 여러 문자열을 하나로 합치거나 연결

concat 문법

select concat('string1', 'string2', ...);

concat 예제 1

select concat('concat', ' ','test');

concat 예제 2

select concat('이름 : ', name,) from celeb;

ALIAS

  • 칼럼이나 테이블 이름에 별칭 생성

alias 문법(column)

  • 컬럼 이름에 별칭 생성
select column as alias
from tablename;

alias 문법(table)

  • 테이블 이름에 별칭 생성
select column1, column2, ...
from tablename as alias;

alias 예제 1

  • name 을 이름으로 별칭을 만들어서 검색
select name as '이름'
from celeb;

alias 예제 2

  • name 은 이름으로, agency 는 소속사로 별칭을 만들어서 검색
select name as '이름', agency as '소속사'
from celeb;

alias 예제 3

  • name 과 job_title 을 합쳐서 profile 이라는 별칭을 만들어서 검색
select concat(name, ' : ', job_title) as profile
from celeb;

alias 예제 4

  • snl_korea 에 출연한 celeb 을 기준으로 두 테이블을 조인하여, celeb 테이블은 c, snl_show 테이블은 s 라는 별칭을 만들어서 출연한 시즌과 에피소드, 이름, 직업을 검색
select s.season, s.episode, c.name, c.job_title
from celeb as c, snl_show as s
where c.name = s.host;

alias 예제 5

  • snl_korea 에 출연한 celeb 을 기준으로 두 테이블을 조인하여, 다음과 같이 각 데이터의 별칭을 사용하여 검색
    • 시즌, 에피소드, 방송일을 합쳐서 '방송정보'
    • 이름, 직업을 합쳐서 '출연자정보'
select concat(s.season, '-', s.episode, '(', s.broadcast_date, ')') as '방송정보', 
concat(c.name, '(', c.job_title, ')') as '출연자정보'
from celeb as c, snl_show as s
where c.name = s.host;

  • as는 생략도 가능
select concat(s.season, '-', s.episode, '(', s.broadcast_date, ')') '방송정보', 
concat(c.name, '(', c.job_title, ')') '출연자정보'
from celeb c, snl_show s
where c.name = s.host;

DISTINCT

  • 검색한 결과의 중복 제거

DISTINCT 문법

select distinct column1, column2, ...
from tablename;

DISTINCT 예제 1

  • 연예인 소속사 종류를 검색(중복 포함)
select agency
from celeb;

  • 연예인 소속사 종류를 검색(중복 제외)
select distinct agency
from celeb;

  • 가수중에서, 성별, 직업별 종류를 검색(중복 포함)
select sex, job_title
from celeb;
where job_title like '%가수%';

  • 가수중에서, 성별, 직업별 종류를 검색(중복 제외)
select distinct sex, job_title
from celeb;
where job_title like '%가수%';

LIMIT

  • 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회

LIMIT 문법

select column1, column2, ...
from tablename
where condition
limit number;

LIMIT 예제 1

  • celeb 데이터 3개만 가져오기
select *
from celeb
limit 3;

LIMIT 예제 2

  • 나이가 가장 적은 연예인 4명을 검색
select *
from celeb
order by age
limit 4;

profile
한영석
코딩공부중
다음 포스트

SQL - 심화

0개의 댓글