SQL - 심화

한영석·2022년 9월 11일
0

SQL

목록 보기
2/2

SQL - 심화

AWS RDS

AWS RDS 가입

AWS RDS(Amazone Relational Database Service)

  • AWS 에서 제공하는 관계형 데이터베이스 서비스
  • Cloud 상에 Database 를 구축해 봅시다.

AWS 회원가입

  • AWS에 회원가입하기

AWS RDS 생성

  1. 회원가입 후 로그인

  2. 왼쪽상단의 서비스 클릭

  3. 모든 서비스 클릭

  4. 모든 서비스 보기 클릭

  5. 데이터베이스의 RDS 클릭

  6. 데이터베이스 생성 클릭

  7. 데이터베이스 옵션 설정

  • 생성방식 : 표준생성

  • 표준유형 : MySQL

  • 탬플릿 : 프리티어 선택

  • 인스턴스 구성

  • 스토리지 구성

    • 스토리지 자동 조정 비활성화(활성화시 유료 전환 될수있음.)

  • 연결

    • 기본으로 적용
    • 퍼블릭 액세스 연결 설정
    • 기존 설정과 디폴트로 보안그룹 설정

  • 암호인증

    • 추 후 공부를더해서 다른 암호인증 방법도 배워두자(일반적인 암호인증은 보안 취약)

  • 추가구성

    • 자동 백업 비활성화(용량관리하기위해..)
    • 나머진 기본 설정
    • 삭제 방지 활성화 설정

  • 최종으로 데이터베이스 생성 클릭

AWS RDS 외부 접속 설정

  • 만들어놓은 데이터베이스 클릭

  • 연결 및 보안 항목에서 vpc 보안그룹 클릭

  • 보안그룹 id클릭하여 정보 페이지로 이동

  • 인바운드 큐칙을 편집하기 위해 선택후 규칙 편집 클릭

  • 규칙 추가 클릭하여 추가하기

    • 유형 - MYSQL/Aurora
    • 소스 - AnywhereIPv4

AWS RDS 접속

  • 다시 서비스 - DB RDS 페이지로 이동

  • 연결 및 보안 항목에서 엔드포인트 항목 사용 예정

  • 외부에서 원격으로 접속하기위한 코드

    • P는 대문자 P를 사용해야함
    • 터미널 사용하여 접속
mysql -h "엔드포인트" -P 포트 -u 마스터 사용자 이름 -p
  • 일시적 중지 방법
    • 사용안할 시 중지하기
    • 중지시 7일간 중지 후 재시작됨

  • 재시작 방법

    • DB식별자 클릭하여 접속

  • 작업에서 시작클릭

SQL File 실습환경 만들기

SQL File 실행 및 생성 방법

  • workspace 생성
    • sql_ws 폴더를 만들고 그하위에서 실습하기(터미널로 실행)
Document % mkdir sql_ws 
(cd 명령어를 이용해서 문서폴더인 document로 이동후 sql_ws 폴더 만들기)
Document % cd sql_ws
(만든 폴더인 sql_ws 폴더로 경로 이동하기)
sql_ws %
(준비완료)
  • sql_ws 폴더 하위에서 다음을 실행
    • vscode 터미널에서 작성중(현재 폴더를 VSCode 로 열라는 뜻)
sql_ws % code .
(현재경로인 sql_ws에서 code . 을 입력하면 현재경로에서 vscode를 실행하라는 뜻 )

  • VSCode 의 sql_ws 폴더에서 새파일을 선택

  • 생성할 파일 이름을 정하고, 파일 확장자를 sql로 입력하면 비어있는 SQL 파일이 생성됩니다.

  • SQL File

    • SQL 쿼리를 모아놓은 파일

SQL File 실행 1 - 로그인 이후

  • 경찰서 정보를 테이블로 만들기

  • sql_ws 폴더 위치로 이동하여 zerobase 에 접속

    • 터미널로 실행
cd sql_ws
mysql -u root -p zerobase (-p이후 한칸 띄어쓰면 데이터베이스에 직접 접속한다는 뜻)
  • SQL File 을 실행
    • 터미널에서 실행(vscode 자체 터미널)
mysql> source test01.sql (터미널에서 sql로 접속하면 mysql> 으로 변함.)
Query OK, 0 rows affected (0.05 sec)  <- 완료됬다는 매시지
  • 결과 확인

SQL File 실행 2 - 외부에서 바로 실행

  • mysql 실행시 외부 경로에있는 파일을 지정하여 불러오기
mysql -u username -p database < /pasth/filename.sql
( 꺽쇠 사용 안될시 아래의 문법으로 사용 가능)
cmd /c 'mysql.exe  -u username -p database < filename.sql'
  • mysql 종료
exit (종료하는 명령어)

  • 서울특별시 관서별 5대 범죄 현황 정보를 테이블로 만들기

  • zerobase Database 에 접속하면서 SQL File 을 싱행

mysql -u root -p zerobase < test02.sql
( 꺽쇠 사용 안될시 아래의 문법으로 사용 가능)
cmd /c 'mysql.exe  -u root -p zerobase < test02.sql'
  • 결과 확인

Database Backup

  • SQL File 로 Database 를 백업하기
mysqldump -u username -p dbname > backup.sql          # 특정 데이터베이스 백업
mysqldump -u username -p dbname -r backup.sql (위방법이안된다면 이렇게 시도하면된다.)
mysqldump -u username -p --all-database > backup.sql  # 모든 데이터베이스 백업

Database Backup 예제

  • zerobase Database Backup
mysqldump -u root -p zerobase > zerobase.sql
(위 방법이 안되면 아래의 방법으로)
mysqldump -u root -p zerobase -r zerobase.sql
  • VSCode 에서 zerobase.sql 확인

Database Restore

  • 데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전 가능
    (SQL File 을 실행하는 방법과 동일함)

Database Restore 예제

  • AWS RDS(database-1) 서비스가 사용가능한 상태에서 접속
mysql -h "엔드포인트" -P 포트번호 -u username -p

  • zerobase Database 이동

  • AWS RDS(database-1) 의 zerobase Database 를 복원

source zerobase.sql;
  • 결과 확인

Table Backup

  • Table 단위로도 백업 가능
mysqldump -u username -p dbname tablename -r backup.sql

Table Backup 예제

  • Local Database 에서 celeb Table 을 백업
mysqldump -u root -p zerobase celeb -r celeb.sql
  • VSCode 에서 celeb.sql 확인

Table Restore

  • Table 을 백업한 SQL File 을 실행하여, 해당 테이블을 복구하거나 이전 가능
    (SQL File 을 실행하는 방법과 동일함)

Table Restore 예제

  • AWS RDS (database-1) 의 zerobase 에서 celeb 테이블을 삭제
mysql -h "엔드포인트" -P 포트번호 -u admin -p zerobase
...
drop table celeb; (테이블 삭제)
  • AWS RDS (database-1) 의 zerobase 에서 celeb 테이블을 복구
mysql -h "엔드포인트" -P 포트번호 -u admin -p zerobase
...
source celeb.sql;
  • 결과 확인

Table Schema Backup

  • 데이터를 제외하고 테이블 생성 쿼리만 백업
mysqldump -d -u username -p dbname tablename > backup.sql # 특정 Table Schema Backup
mysqldump -d -u username -p dbname > backup.sql           # 모든 Table Schema Backup

Table Schema Backup 예제

  • Local Database 에서 snl_show Table Schema 백업
mysqldump -d -u root -p zerobase snl_show > snl.sql
  • VSCode 에서 snl.sql 확인 (Data 는 포함하지 X)

Python with MySQL

Python with MySQL 실습환경 만들기

Jupyter Notebook 실행

  • VSCode 혹은 Web Browser 를 통해 실행하고, ds_study 환경에서 작업

  • 실행 위치

    • Jupyter notebook 과 mysql 은 모두 sql_ws 폴더에서 시작

  • MySQL

    • AWS RDS 로 생성한 database-1 을 모두 사용

  • 데이터 삭제

    • AWS RDS(database-1) zerobase 의 police_station 테이블의 데이터를 모두 삭제
(시작은 터미널에서 시작 / 중간중간 오류로 변경된부분이 쪼금 있음)
mysqldump --set-gtid-purged=off -h "엔드포인트" -P 포트번호 -u admin -p zerobase polistation -r backup_police.sql
(패스워드 입력 부분)
(여기부터 mysql 시작부분)
use zerbase;
delete from police_station;
  • python.ipynb 파일 생성
    • VSCode 의 경우 새파일을 열고 해당 파일명을 입력
    • 오른쪽 상단에서 ds_study 를 선택 후 진행

Python with MySQL

Python with MySQL

  • Python 으로 MySQL 접속 후 사용하는 방법

Install MySQL Driver

  • Python 에서 MySQL 을 사용하기 위해서는 먼저 MySQL Driver 를 설치
pip install mysql-connector-python

  • 설치확인
import mysql.connector

Create Connection

  • MySQL 에 접속하기 위한 코드
mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>"
)

Create Connection 예제 1 (로컬과 외부 DB 접속방법)

  • Local Database 연결
import mysql.connector

local = mysql.connector.connect(
	host = "localhost",
    user = "root",
    password = "**********"
)

  • AWS RDS (database-1) 연결
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "**********"
)

Close Database

import mysql.connector

local = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

local.close()

Close Database 예제 1

Close Database 예제 2

Connect to Database

  • 특정 Database 에 접속하기 위한 코드
import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = "<port>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

Create Connection 예제 1

  • Local MySQL 의 zerobase 연결

Create Connection 예제 2

  • AWS RDS (database-1) 의 zerobase 에 연결

Execute SQL

  • Query 를 실행하기 위한 코드
import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.corsor()
mycursor.execute(<query>)

Execute SQL 예제 1

  • 테이블 생성
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = "port",
    user = "admin",
    password = "**********",
    database = "zerobase"
)

cur = remote.corsor()
cur.execute("create table sql_file (id int, filename varchar(16))")

remote.close()

  • 결과 확인

Execute SQL 예제 2

  • 테이블 삭제
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = "port",
    user = "admin",
    password = "**********",
    database = "zerobase"
)

cur = remote.corsor()
cur.execute("drop table sql_file")

remote.close()

  • 결과 확인

Execute SQL File 1

  • SQL File 을 실행하기 위한 코드
import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.corsor()

sql = open("<filename>.sql").read()
mycursor.execute(sql)

Execute SQL File 예제 1

  • test03.sql 생성

  • test03.sql 실행

  • 결과 확인

Execute SQL File 2

  • SQL File 내에 Query 가 여러개 존재하는 경우
mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.corsor()

sql = open("<filename>.sql").read()
mycursor.execute(sql, multi = True)

Execute SQL File 예제 2

  • test04.sql 생성

  • test04.sql 실행(에러)

    • 여러개의 쿼리가 들어있는데 입력된 문법은 하나의 쿼리만 입력하는 코드라서 에러가 발생했다.
    • 아래의 사진처럼 변경해주면 된다.

  • 결과 확인

Fetch All

mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
	print(data)

Fetch All 예제

  • sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

  • 참고, 검색결과를 Pandas 로 읽기

Python with CSV

  • CSV 에 있는 데이터를 Python 으로 INSERT

Read CSV

  • 제공받은 police_station.csv 를 Pandas 로 읽어와서 데이터를 확인

Zerobase 에 연결

Cursor 만들기

  • 읽어올 양이 많은 경우 cursor 생성 시 buffer 설정

INSERT 문 만들기

데이터 입력

  • commit() 은 database 에 적용하기 위한 명령

  • 결과 확인

  • 검색결과를 Pandas 로 읽기

  • csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정 (특히 우리나라 사이트에서 제공받은 csv 파일들.)

Python with CSV 예제

  • crime_status 테이블에 2020_crime.csv 데이터를 입력하는 코드를 작성

AWS RDS(database-1) zerobase 에 접속

2020_crime.csv 데이터(encoding='euc-kr') 읽어오기

INSERT 쿼리 작성

데이터를 crime_status 테이블에 INSERT

crime_status 테이블의 데이터 조회

조회한 결과를 Pandas 로 변환해서 확인

PRIMARY KEY, FOREIGN KEY

PRIMARY KEY

실습환경 만들기

  • zerobase 사용 (이동)
use zerobase;

PRIMARY KEY(기본키)

  • 테이블의 각 레코드를 식별
  • 중복되지 않은 고유값을 포함
  • NULL 값을 포함할 수 없음
  • 테이블 당 하나의 기본키를 가짐

PRIMARY KEY 생성 문법 1

create table tablename
(
	column1 datatype not NULL,
	column2 datatype not NULL,
    ...
    constraint constraint_name
    primary key (column1, column2, ...)
);

PRIMARY KEY 생성 예제 1 - 1

create table person
(
	pid int NOT NULL,
	name varchar(16),
    age int,
    sex char,
    primary key (pid)
);

PRIMARY KEY 생성 예제 1 - 2

  • 여러개의 칼럼을 기본키로 설정하는 경우
create table animal
(
	name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,    
    primary key (name, type)
);

PRIMARY KEY 삭제 문법

  • primary key는 테이블당 하나만 있기 때문에 별도의 이름을 입력하지않아도 삭제될 수 있다
alter table tablename
drop primary key;

PRIMARY KEY 삭제 예제 1

  • 하나의 칼럼이 기본키로 설정된 경우
alter table person
drop primary key;

PRIMARY KEY 삭제 예제 2

  • 여러개의 칼럼이 기본키로 설정된 경우(삭제하는 방법은 동일)
alter table animal
drop primary key;

PRIMARY KEY 생성 문법 2

alter table tablename
add primary key (column1, column2, ...);

PRIMARY KEY 생성 예제 2 - 1

alter table person
add primary key (pid);

PRIMARY KEY 생성 예제 2 - 2

  • 여러개의 칼럼을 기본키로 설정하는 경우
alter table animal
add constraint PK_animal primary key (name, type);
# constraint PK_animal 이 부분은 생략 가능

FOREIGN KEY

FOREIGN KEY 생성 문법

create table tablename
(
	colimn1 datatype NOT NULL,
    colimn2 datatype NOT NULL,
    colimn3 datatype,
    colimn4 datatype,
    ...
    constraint constraint_name
    primary key (colimn1, colimn2, ...),
    constraint constraint_name
    foreign key (colimn3, colimn4, ...) references REF_tablename(REF_colimn)

FOREIGN KEY 생성 예제 1 - 1

  • create table 에서 foreign key 를 지정하는 경우
create table orders
(
	oid int NOT NULL,
    order_no varchar(16),
    pid int,
    primary key (oid),
    constraint FK_person
    foreign key (pid) references person(pid)
);

FOREIGN KEY 생성 예제 1 - 2

  • create table 에서 foreign key 를 지정하는 경우 constraint 를 생략할 수 있다
create table job
(
	jid int NOT NULL,
    name varchar(16),
    pid int,
    primary key (jid),
    foreign key (pid) references person(pid)
);

CONSTRAINT 확인 문법

  • 자동 생성된 CONSTRAINT 를 확인하는 방법
show create table tablename;

CONSTRAINT 확인 예제

show create table job;

FOREIGN KEY 삭제 문법

alter table tablename
drop foreign key FK_constraint;

FOREIGN KEY 삭제 예제

alter table orders
drop foreign key FK_person;

FOREIGN KEY 삭제 문법 2

  • table 이 생성된 이후에도 alter table 을 통해 foreign key 를 지정할 수 있다
alter table tablename
add foreign key (column) references REF_tablename(REF_column);

FOREIGN KEY 삭제 예제 2

alter table orders
add foreign key (pid) references person(pid);

Aggregate Functions(집계함수)

  • 실습환경 만들기
    • AWS RDS (database-1) zerobase 에 접속
mysql -h "엔드포인트" -P 포트번호 -u zero -p zerobase

  • Aggregate Functions(집계함수)
    • 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수

count

  • 총 갯수를 계산해 주는 함수

count 문법

select count(column)
from tablename
where condition;

count 예제 1

  • police_station 테이블에서 데이터는 모두 몇 개인가?
select count(*)
from police_station;

count 예제 2

  • crime_status 테이블에서 경찰서는 총 몇군데인가?
select count(distinct police_station)
from crime_status;

count 예제 3

  • crime_type 은 총 몇가지 인가?
select count(distinct crime_type)
from crime_status;

SUM

  • 숫자 칼럼의 합계를 계산해주는 함수

SUM 문법

select sum(column)
from tablename
where condition;

SUM 예제 1

  • 범죄 총 발생 건수는? 
select sum(case_number)
from crime_status
where status_type = '발생';

AVG

  • 숫자 칼럼의 평균을 계산해주는 함수

AVG 문법

select AVG(column)
from tablename
where condition;

AVG 예제 1

  • 평균 폭력 검거 건수는? 
select avg(case_number)
from crime_status
where crime_type like '폭력' and status_type = '검거';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where crime_type like '폭력' and status_type = '검거';

AVG 예제 2

  • 중부경찰서 범죄 평균 발생 건수는? 
select avg(case_number)
from crime_status
where police_station like '중부' and status_type = '발생';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where crime_type like '중부' and status_type = '발생';

MIN

  • 숫자 칼럼 중 가장 작은 값을 찾아주는 함수

MIN 문법

select MIN(column)
from tablename
where condition;

MIN 예제 1

  • 강도 발생 건수가 가장 적은 경우 몇 건인가?
select MIN(case_number)
from crime_status
where crime_type like '강도' and status_type = '발생';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where crime_type like '강도' and status_type = '발생';

MIN 예제 2

  • 중부경찰서에서 가장 낮은 검거 건수는?
select MIN(case_number)
from crime_status
where police_station like '중부' and status_type = '검거';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where police_station like '중부' and status_type = '검거';

MAX

  • 숫자 칼럼 중 가장 큰 값을 찾아주는 함수

MAX 문법

select MAX(column)
from tablename
where condition;

MAX 예제 1

  • 살인이 가장 많이 검거된 건수는?
select MAX(case_number)
from crime_status
where crime_type like '살인' and status_type like '검거';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where crime_type like '살인' and status_type like '검거';

MAX 예제 2

  • 강남경찰서에서 가장 많이 발생한 범죄 건수는?
select MAX(case_number)
from crime_status
where police_station like '강남' and status_type = '발생';

  • 확인
select police_station, crime_type, status_type, case_number
from crime_status
where police_station like '강남' and status_type = '발생';

Group By

  • 그룹화하여 데이터를 조회

Group By 문법

select column1, column2, ...
from tablename
where condition
group by column1, column2, ...
order by column1, column2, ...;

Group By 예제 1

  • crime_status 에서 경찰서별로 그룹화하여 경찰서 이름을 조회
select poloce_station
from crime_status
group by poloce_station
order by poloce_station
limit 5;

  • 경찰서 종류를 검색 - distinct 를 사용할 경우 (order by 를 사용할 수 없음)
select distinct poloce_station
from crime_status
limit 5;

Group By 예제 2

  • 경찰서 별로 총 발생 범죄 건수를 검색
select poloce_station, sum(case_number) 발생건수
from crime_status
where status_type like '발생'
group by poloce_station
order by 발생건수 desc
limit 5;

Group By 예제 3

  • 경찰서 별로 평균 범죄 검거 건수를 검색
select poloce_station, avg(case_number) 평균검거건수
from crime_status
where status_type like '검거'
group by poloce_station
order by 평균검거건수 desc
limit 5;

Group By 예제 4

  • 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
select poloce_station, status_type, avg(case_number)
from crime_status
group by poloce_station, status_type
limit 6;

HAVING

  • 조건에 집계함수가 포함되는 경우 where 대신 HAVING 사용

HAVING 문법

select column1, column2, ...
from tablename
where condition
group by column1, column2, ...
HAVING condition (Aggregate Functions)
order by column1, column2, ...;

HAVING 예제 1

  • 경찰서 별로 발생한 범죄 건수의 합이 4000건보다 큰 경우를 검색
select poloce_station, sum(case_number) count
from crime_status
where status_type like '발생'
group by poloce_station
HAVING count > 4000;

  • 확인
select sum(case_number)
from crime_status
where status_type like '발생' and poloce_station = '영등포';

HAVING 예제 2

  • 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우를 검색
select poloce_station, avg(case_number)
from crime_status
where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생'
group by poloce_station
HAVING avg(case_number) >= 2000;

  • 확인
select avg(case_number)
from crime_status
where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생' and poloce_station like '영등포';

  • 확인 2
select *
from crime_status
where (crime_type like '폭력' or crime_type like '절도') and status_type like '발생' and poloce_station like '영등포';

Scalar Functions

  • 실습환경은 AWS 에서 시작

  • 데이터 확인

    • sandwich 데이터 확인 (50 rows)

Scalar Functions

  • 입력값을 기준으로 단일 값을 반환하는 함수

UCASE

  • 영문을 대문자로 반환하는 함수

UCASE 문법

select ucase(string);

UCASE 예제 1

  • 다음 문장을 모두 대문자로 조회
select ucase('This Is ucase Test.');

UCASE 예제 2

  • $15 가 넘는 메뉴를 대문자로 조회
select ucase(menu)
from sandwich
where price > 15;

LCASE

  • 영문을 소문자로 반환하는 함수

LCASE 문법

select lcase(string);

LCASE 예제 1

  • 다음 문장을 모두 대문자로 조회
select lcase('This Is LCASE Test.');

LCASE 예제 2

  • $5 가 안되는 메뉴를 소문자로 조회
select lcase(menu)
from sandwich
where price < 5;

MID

  • 문자열 부분을 반환하는 함수

MID 문법

select mid(string, start_position, lenth);
  • string : 원본 문자열
  • start_position : 문자열 반환 시작 위치.(첫글자는 1, 마지막 글자는 -1)
  • lenth : 반환할 문자열 길이

MID 예제 1

  • 1번 위치에서 4글자 조회
select mid('This is mid test.', 1, 4);

MID 예제 2

  • 6번 위치에서 5글자 조회
select mid('This is mid test.', 6, 5);

MID 예제 3

  • -4번 위치(뒤에서 4번째 위치) 에서 4글자 조회
select mid('This is mid test.', -4, 4);

MID 예제 4

  • -8번 위치(뒤에서 8번째 위치) 에서 3글자 조회
select mid('This is mid test.', -8, 3);

MID 예제 5

  • 11위 카페이름 중 두번째 단어만 조회 - 6번 위치에서 4글자
select mid(cafe, 6, 4)
from sandwich
where ranking = 11;

LENGTH

  • 문자열의 길이를 반환하는 함수

LENGTH 문법

select length(string);

LENGTH 예제 1

  • 다음 문장의 길이를 조회
select length('This is len test.');

LENGTH 예제 2

  • 문자가 없는 경우 길이도 0
select length('');

LENGTH 예제 3

  • 공백의 경우도 문자이므로 길이가 1
select length(' ');

LENGTH 예제 4

  • NULL 의 경우 길이가 없으므로 NULL
select length('NULL');

LENGTH 예제 5

  • sandwich 테이블에서 Top3의 주소 길이를 검색
select length('address'), address
from sandwich
where ranking <= 3;

ROUND

  • 지정한 자리에서 숫자를 반올림 해주는 함수

ROUND 문법

select round(number, decimals_place);
  • number : 반올림할 대상
  • decimals_place : 반올림할 소수점 위치 (Option)

ROUND 예제 1

  • 반올림할 위치를 지정하지 않을 경우, 소수점 자리 (0) 에서 반올림
select round(315.625);

ROUND 예제 2

  • 소수점 첫번째 위치는 0
select round(315.625, 0);

ROUND 예제 3

  • 소수점 첫번째 위치는 1
select round(315.625, 1);

ROUND 예제 4

  • 소수점 첫번째 위치는 2
select round(315.625, 2);

ROUND 예제 5

  • 일단위 위치는 -1
select round(315.625, -1);

ROUND 예제 6

  • 일단위 위치는 -2
select round(315.625, -2);

ROUND 예제 7

  • sandwich 테이블에서 소수점 자리는 반올림해서 1달러 단위까지만 표시 (최하위 3개만 표시)
select ranking, price, round(price)
from sandwich
order by ranking desc
limit 3;

NOW

  • 현재 날짜 및 시간을 반환하는 함수

NOW 문법

select now();

NOW 예제 1

  • 반올림할 위치를 지정하지 않을 경우, 소수점 자리 (0) 에서 반올림
select now();

FORMAT

  • 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

FORMAT 문법

select format(number, decimals_place);
  • number : 포맷을 적용할 문자 혹은 숫자
  • decimals_place : 표시할 소수점 위치(반올림해서 잘라준다)

FORMAT 예제 1

  • 소수점을 표시하지 않을 경우 0
select format(12345.6789, 0);

FORMAT 예제 2

  • 소수점 두자리까지 표시할 경우 2
select format(12345.6789, 2);

FORMAT 예제 3

  • 소수점 열자리까지 표시
select format(12345.6789, 10);

FORMAT 예제 4

  • oil_price 테이블에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우 천원단위에 콤마를 넣어서 조회
select format(가격, 0) 
from oil_price
where round(가격, -3) >= 2000;

FORMAT 예제 5

  • 소수점을 표시하지 않을 경우 0
select format(12345.6789, 0);

SQL Subquery

About Subquery

  • 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문을 말한다.
  • 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
    • 서브쿼리는 메인쿼리의 칼럼 사용 가능
    • 메인쿼리는 서브쿼리의 칼럼 사용 불가

Subquery 사용시 주의사항

  • Subquery는 괄호로 묶어서 사용
  • 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
  • Subquery 에서는 order by 를 사용x (정렬 불가)

Subquery 종류

  • 스칼라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용
  • 인라인 뷰 (Inline View) - FROM 절에 사용
  • 중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

스칼라 서브쿼리 (Scalar Subquery)

  • SELECT 절에서 사용하는 서브쿼리, 결과는 하나의 Column 이어야 한다
select column1, (select column2 from table2 where condition)
from table1
where condition;

스칼라 서브쿼리 (Scalar Subquery) 예제

  • 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회
select case_number, 
	(select avg(case_number) 
    from crime_status 
    where crime_type like '강도' and status_type like '검거') avg
from crime_status
where police_station like '은평' and crime_type like '강도' and status_type like '검거';

인라인 뷰 (Inline View)

  • FROM 절에 사용하는 서브쿼리, 메인쿼리에서는 인라인 뷰에서 조회한 Column 만 사용가능하다
select a.column, b.column
from table a, (select colmun1, colmun2 from table2) b
where condition;

인라인 뷰 (Inline View) 예제

  • 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
select c.police_station, c.crime_type, c.case_number
from crime_status c, 
	(select police_station, max(case_number) count
    from crime_status 
    where status_type like '발생'
    group by police_station) m
where c.police_station = m.police_station
	and c.case_number = m.count;

중첩 서브쿼리 (Nested Subquery)

  • WHERE 절에서 사용하는 서브쿼리
    • Single Row - 하나의 열을 검색하는 서브쿼리
    • Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
    • Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

Single Row 문법

  • 서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한개의 결과값을 가져와야 한다. (두개 이상인 경우 에러)
select column_names
from table_name
where column_names = (select column_name
					  from table_name
                      where condition)
order by column_name;

Single Row 예제

select name
from celeb
where name = (select host
			  from snl_show
              where id = 1);

Multiple Row - IN 문법

  • 서브쿼리 결과 중에 포함 될때
select column_names
from table_name
where column_names IN (select column_name
					   from table_name
                       where condition)
order by column_name;

Multiple Row - IN 예제

  • SNL 에 출연한 영화배우를 조회
select host
from snl_show
where host in (select name
			  from celeb
              where job_title like '%영화배우%');

Multiple Row - EXISTS 문법

  • 서브쿼리 결과에 값이 있으면 반환
select column_names
from table_name
where exists (select column_name
			  from table_name
              where condition)
order by column_names;

Multiple Row - EXISTS 예제

  • 범죄 검서 혹은 발생 건수가 2000건 보다 큰 경찰서 조회
select name
from police_station p
where exists (select police_station
			  from crime_status c
              where p.name = c.reference and case_number > 2000);

Multiple Row - ANY 문법

  • 서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)
select column_names
from table_name
where column_name =  ANY (select column_name
                          from table_name
                          where condition)
order by column_names;

Multiple Row - ANY 예제

  • SNL 에 출연한 적이 있는 연예인 이름 조회
select name
from celeb
where name = ANY (select host
			  	  from snl_show);

Multiple Row - ALL 문법

  • 서브쿼리 결과 중에 모두 만족하면 (비교연산자 사용)
select column_names
from table_name
where column_name =  ALL (select column_name
                          from table_name
                          where condition)
order by column_names;

Multiple Row - ALL 예제

select name
from celeb
where name = ALL (select host
			  	  from snl_show
                  where id = 1);

Multiple Column - 연관 서브쿼리 문법

  • 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
select column_names
from tablename a
where (a.column1, a.column2, ...) IN (select b.column1, b.column2, ...
					   				  from tablename b
                       				  where a.column_name = b.column_name)
order by column_names;

Multiple Column 예제

  • 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회
select name, sex, agency
from celeb
where (sex, agency) in (select sex, agency
			  			from celeb
              			where name = '강동원');

profile
한영석
코딩공부중
이전 포스트

SQL - 기초

0개의 댓글