📌 Subquery

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문 (메인쿼리가 서브쿼리를 포함하는 종속적인 관계)

• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가

🚫 주의사항

• 서브쿼리는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
• 서브쿼리 에서는 order by 사용불가

📌 subquery 종류

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

📌 Nested Subquery
Single Row - 하나의 열을 검색하는 서브쿼리 Multiple Row - 하나 이상의 열을 검색하는 서브쿼리 Multiple Column - 하나 이상의 행을 검색하는 서브쿼리

💻 실습

SCALAR SUBQUERY 문법

select column1, (select column2 from table2 where condition)
from table1
where condition

🚫 결과는 하나의 Column 이어야 함

INLINE VIEW 문법

select a.column, b.column
from table1 a, (select column1, column2 from table2) b
where condition

🚫 메인쿼리에서는 인라인 뷰에서 조회한 Column 만 사용가능

NESTED SUBQUERY 문법

- single row
//서브쿼리가 비교연사자와 사용되는 경우

select column_name from tablename
where column_name = (select column_name from tablename where condition)
order by column_name

🚫 서브쿼리의 검색 결과는 한 개의 결과값을 가져야함 (두개 이상인 경우 에러) 🚫 괄호 없으면 에러 발생

-Multiple Row - IN
//서브쿼리 결과 중에 포함되는 경우

select column_name from tablename
where column_name in (select column_name from tablename where condition)
order by column_name
`
-Multiple Row - EXISTS
//서브쿼리 결과에 값이 있으면 반환

select column_name from tablename
where exists (select column_name from tablename where condition)
order by column_name

-Multiple Row - ANY
//서브쿼리 결과 중에 최소한 하나라도 만족하는 경우 (비교연산자 사용)

select column_name from tablename
where column_name = any (select column_name from tablename where condition)
order by column_name

-Multiple Row - ALL
//서브쿼리 결과를 모두 만족하는 경우(비교 연산자 사용)

select column_name from tablename
where column_name = all (select column_name from tablename where condition)
order by column_name

-Multi Column Subquery - 연관 서브쿼리
//서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우

select column_name from tablename a
where (a.column1, a.column2, ...) (select b.column1, b.column2, ... from tablename b where a.column = b.column)
order by column_name

🧸 review

• 공공데이터 csv 파일로 연습하기 5대범죄 발생 검거 현황 5대범죄 발생 장소별 현황

• 서울시 살인이 가장 많이 발생한 장소와 살인 발생 건수 조회

  select ANY_VALUE(장소) 장소, max(발생건수) 발생건수,
  (select sum(건수) from 지역별_5대범죄 where 죄종='살인' and 발생검거='발생') 총발생건수
  from 장소별_5대범죄 
  where 범죄명='살인'

  ❗️sql_mode=only_full_group_by 에러 group by 절에 포함되지 않는 column 을 select 할 경우 발생하는 에러
  해결방안 ANY_VALUE(column)함수 사용

• 지역별로 가장 많이 발생한 범죄 건수와 범죄 유형을 inline view 를 조회
  (발생건수가 큰 순으로 정렬하여 5개 조회)

  select 지역, 죄종 유형, 건수 발생건수 
  from 지역별_5대범죄 a,
  (select 지역 지역구분, max(건수) 발생건수 from 지역별_5대범죄 where 발생검거='발생' group by 지역구분) b 
  where a.지역 = b.지역구분 and a.건수=b.발생건수
  order by 건수 desc
  limit 5;

• 발생 건수가 가장 큰 범죄 유형과 지역을 nested subquery 를 사용하여 조회

  select 지역, 죄종 범죄유형 
  from 지역별_5대범죄 
  where 건수 in (select max(건수) from 지역별_5대범죄 where 발생검거='발생');

• 발생 건수가 2000 이상인 지역과 범죄유형을 중복없이 nested subquery 를 사용하여 조회

  select distinct(지역),죄종 범죄유형
  from 지역별_5대범죄 a
  where exists 
  (select 지역, 죄종 from 지역별_5대범죄 b where a.지역 = b.지역 and 발생검거='발생' and a.죄종=b.죄종 and 건수 >=2000);

  실습확인

📌 Concat

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

📌 Alias

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

📌 Distinct

검색한 결과의 중복 제거

📌 Limit

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

💻 실습

CONCAT 문법

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

ALIAS 문법

select column as alias
from tablename

DISTINCT 문법

select distinct column1, column2, ...
from tablename

LIMIT 문법

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

🧸 review

• 놀면뭐하니 table 에서 이름이 세글자인 연예인 정보를 3개만 검색
  column_name = information (이름 : name , 직업 : job_title)

  select concat('이름 :',name,' 직업 :',job_title) as information
  from 놀면뭐하니
  where name like '___'
  limit 3;

• 놀면뭐하니 에 출연한 연예인 중 핑계고 에 출연한 연예인 정보를 검색
  column_name = 신상정보/출연정보/소속사 정보
  (나이 :age(성별)) (시즌-에피소드/방송날짜)

  select concat('이름:',a.name,' 나이:',a.age,'(',a.sex,')') as 신상정보, 
  concat(b.season,'-',b.episode,' / ',b.broadcast_date) as 출연자정보, 
  a.agency 소속사정보
  from 놀면뭐하니 a, 핑계고 b
  where a.name=b.guest;

• 핑계고 table 에서 시즌 종류를 검색하여 시즌순으로 정렬

  select distinct season from 핑계고
  order by season;

• 놀면뭐하니 table 에서 남자 연예인 중 나이가 가장 많은 3명 조회

  select name, age, sex from 놀면뭐하니
  where sex = 'M'
  order by age desc
  limit 5;

• 핑계고에 출연한 연예인의 정보를 최신 방송날짜 순으로 2개만 검색
  column_name = 핑계고_방송정보
  (핑계고 시즌season 에피소드episode 게스트 guest)

  select concat('핑계고 ','시즌',season,' 에피소드',episode,' 게스트 ',guest) 핑계고_방송정보
  from 핑계고
  order by broadcast_date desc
  limit 2;

  실습확인

📌 &nbsp Scalar Functions

💻 실습

UCASE 문법

select ucase('string')

LCASE 문법

select lcase('string')

MID 문법

select mid('string', start_position, lenth)

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

LENGTH 문법

select length('string') //공백도 포함

-null 은 null

ROUND 문법

select round(number, decimal_place)

number : 반올림할 대상
decimal_place : 반올림할 소수점 위치(option)
// 반올림할 위치를 지정하지 않을 경우, (0)에서 반올림

NOW 문법

select now()

FORMAT 문법

select format(number, decimal_place)

number : 포맷을 적용 할 문자 혹은 숫자
decimal_place : 표시할 소수점 위치
// 소수점을 표시하지 않을 경우 0

🧸 review

• cafe table

create table cafe
(
    type varchar(8),
    menu varchar(32),
    price int,
    ranking int
);

• cafe table 에서 종류는 대문자, 메뉴는 소문자로 조회

  select ucase(type) type,lcase(menu) menu
  from cafe;

• cafe table 에서 6위 메뉴의 마지막 단어 조회

  select menu, mid(menu,-4,4) from cafe
  where ranking = 6;

• cafe table 에서 메뉴 이름의 평균 길이 조회

  select truncate(avg(length(menu)),1) avg from cafe;

• cafe table 에서 가격을 천단위에 콤마를 넣어 조회

  select menu, format(price,0) price from cafe;

  실습확인

📌 &nbsp Aggregate Functions (집계함수)

📌 Group By

그룹화하여 데이터를 조회

📌 Having

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

💻 실습

COUNT 문법

select count(column) from tablename
where condition

SUM 문법

select sum(column) from tablename
where condition

AVG 문법

select avg(column) from tablename
where condition

MIN 문법

select min(column) from tablename
where condition

MAX 문법

select max(column) from tablename
where condition

GROUP BY 문법

select column1, column2, ...
from table
where condition
group by column1, column2, ...

HAVING 문법

select column1, column2, ...
from tablename
where condition
group by column1, column2, ...
having condition //집계함수 포함
group by column1, column2, ...

🧸 review

• 공공데이터 csv 파일로 연습하기 서울시 공공자전거 대여소별 이용정보(월별)
  

• 판다스로 데이터 살펴보기 + 데이터 insert 80분 소요(참고하세요!)

❗️csv 파일 인코딩 알아보는 방법

import chardet
filename = "서울특별시 공공자전거 대여소별 이용정보(월별).csv" # 파일명
with open(filename,'rb')as f:
    result = chardet.detect(f.readline())
    print(result['encoding'])

• 자치구 별로 공공자전거 대여건수가 가장 많은 대여소 순으로 5개 검색하고 큰 순서대로 정렬하여 확인

  select 자치구, max(대여건수) max from 공공자전거
  group by 자치구
  order by max(대여건수) desc
  limit 5;

• 자치구 별로 공공자전거 대여건수가 가장 적은 대여소 순으로 5개 검색하고 작은 순서대로 정렬하여 확인

  select 자치구, min(대여건수) min from 공공자전거
  group by 자치구
  order by min(대여건수)
  limit 5;

• 자치구 별로 공공자전거 평균 대여건수를 5개 검색하고, 큰 순서대로 정렬 (소수점x)

  select 자치구, truncate(avg(대여건수),-1) avg from 공공자전거
  group by 자치구
  order by avg(대여건수) desc
  limit 5;

• '4글자'인 자치구 별 대여건수의 평균이 1500건 이상인 경우를 검색

  select 자치구, truncate(avg(대여건수),-1) avg from 공공자전거
  where 자치구 like '____'
  group by 자치구
  having avg(대여건수) >=1500;

  실험확인

📌 &nbsp Primary Key (기본키)

• 테이블의 각 레코드를 식별

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

📌 &nbsp Foreign Key (외래키)

• 한 테이블을 다른 테이블과 연결해주는 역할

• 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

💻 실습

Primary Key 문법

//create table 에서 Primary key를 설정
create table tablename
(
    column1 datatype not null,
    column2 datatype not null,
    ...
    constraint constraint_name
    primary key (column1, column2, ...)
)

// 테이블 생성 후 Primary Key 추가
alter table tablename
add primary key (column1, column2, ...) 

Primary Key 삭제 문법

alter table tablename
drop primary key(column) 

Foreign Key 문법

//create table 에서 foreign key를 설정
create table tablename
(
    column1 datatype not null,
    column2 datatype not null,
    column3 datatype,
    column4 datatype,
    ...
    constraint constraint_name //생략가능
      primary key (column1, column2, ...),
    constraint constraint_name //생략가능
      foreign key (column3, column4, ...) references ref_tablename(ref_column)
)

// table 생성 후 foreign key 추가
alter table tablename
add foreign key(column) references ref_tablename(ref_column)

자동 생성된 CONSTRAINT 를 확인

show create table tablename

Foreign Key 삭제 문법

alter table tablename
drop foreign key constraint_name

🧸 review

• orders table

create table orders
(
  order_id int not null primary key,
  user_id int,
  product varchar(32)
);

• users table

  create table users
  (
  id int not null primary key,
  pw varchar(16) not null,
  name varchar(16) not null,
  email varchar(32)
  );

• orders 테이블의 primary key 삭제

  alter table orders
  drop primary key;

  orders 테이블의 foreign key 삭제

  alter table orders
  drop foreign key orders_ibfk_1; // 자동으로 생성된 constraint_name

• orders 테이블의 user_id 를 users 테이블의 id 와 연결된 foreign key 등록

  alter table orders
  add foreign key (user_id) references users (id);

• orders 테이블의 order_id 를 primary key 로 등록

  alter table orders
  add primary key(order_id);

  실습확인

orders	users

constraint_name 확인

install mysql driver

# mysql driver 설치
pip install mysql-connector-python

# 설치확인

import.mysql.connector

Python with MYSQL

create connection

MYSQL에 접속하기 위한 코드

<connection name> = mysql.connector.connect(
        host = "<hostname>",
        user = "<username>",
        password = "<password>",
        database = "<databasename>" #특정 데이터베이스 설정
)

close connection

<connection name>.close()

execute SQL

Query 를 실행하기 위한 코드

<cursorname> = <connection name>.cursor()
<cursorname>.execute(<query>)

execute SQL File

SQL File 을 실행하기 위한 코드

<cursorname> = <connection name>.cursor()

sql = open("<filename>.sql").read()
<cursorname>.execute(sql) # SQL File 내에 Query 가 여러개 존재하는 경우 multi=True 추가

fetchall()

<cursorname> = <connection name>.cursor() # 읽어올 데이터 양이 많은 경우 buffered=True
mycursor.execute(<query>)

result = <cursorname>.fetchall()
for data in result:
    print(data)

➕ 검색결과 pandas 로 읽기

import pandas as pd

df = pd.DataFrame(result)
df.head()

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

• mysql 에 접속한 상태

1️⃣ read csv

import pandas as pd

df = pd.read_csv("<csvfilename>") # csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정
df.head()

2️⃣ cursor 만들기

<cursorname> = <connection name>.cursor(buffered=True)

3️⃣ inser문 만들기

sql = "insert into <tablename> values (%s, %s,...)"

4️⃣ 데이터 입력

for i, row in df.iterrows():
    <cursorname>.execute(sql, tuple(row))
    print(tuple(row))
    <connection name>.commit() # commit() 은 database 에 적용하기 위한 명령

5️⃣ 결과확인

<cursorname>.execute("select * from <tablename>")

result = <cursorname>.fetchall()
for row in result:
    print(row)

➕ pandas 로 결과 읽기

df = pd.DataFrame(result)
df.head()

🧸 review

• 공공데이터 csv 파일로 연습하기 기상청 기상자료개방포털 https://buly.kr/A42pJWU

❌ encoding='utf-8' 로 하면 한글이 깨지고, encoding='cp949' & 'euc-kr'은 인식을 못하는 문제가 발생함 😭

[-해결방안 정리해서 작성하기-]

-- 이번파일은 데이터가 크지 않아서 csv 파일을 수정해서 사용함.

• AWS RDS(database-1) review 에 접속

  conn = mysql.connector.connect(
    host = "database-1.ct6emoc66tc9.ap-southeast-2.rds.amazonaws.com",
     port = "3306",
    user = "admin",
    password = "***********",
    database = "review"
  )

• Temperature Table 생성하기

  sql = "create table temperature (날짜 date, 지역 varchar(8), 평균기온 float, 최저기온 float, 최고기온 float)"
  cursor = conn.cursor(buffered=True)
  

cursor.execute(sql)

- 데이터를 pandas로 읽어오기
```python
df = pd.read_csv("temperature.csv", encoding='utf-8')
df.head(2)

• 데이터를 temperature 테이블에 insert 하기

  sql = "insert into temperature values(%s,%s,%s,%s,%s)"
  cursor = conn.cursor(buffered=True)
  

for i, row in df.iterrows(): cursor.execute(sql, tuple(row)) print(tuple(row)) conn.commit()

- temperature 테이블의 데이터 조회하기
```python
cursor.execute("select*from temperature")

result = cursor.fetchall()
for row in result:
    print(row)

• 조회된 데이터를 Pandas 로 변환하여 출력하기

  df = pd.DataFrame(result)
  df.head()

  실습확인

📌 SQL File

SQL 쿼리를 모아놓은 파일

📌 Database Restore

데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전 할 수 있다

📌 Table Restore

Table 을 백업한 SQL File 을 실행하여 해당 테이블을 복구하거나 이전 할 수 있다

💻 실습

SQL File 실행 - 로그인 이후

mysql> source </path/filename.sql>
mysql> \. </path/filename.sql> // source 대신 \. 가능
mysql> \. <filename.sql> // 현재 폴더에 파일이 있는 경우

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

% mysql -u root -p <database> < </path/filename.sql>

Database Backup

% mysqldump -u username -p dbname > backup.sql // 특정 database backup
% mysqldump -u username -p --all-databases > backup.sql // 모든 database backup

Database Restore

// SQL File 실행하는 방법과 동일

Table Backup

//table 단위로도 백업 가능
% mysqldump -u username -p dbname tablename > backup.sql

Table Restore

// SQL File 실행하는 방법과 동일

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

🧸 review

• AWS RDS(database-1) review 데이터베이스 백업하기

  % mysqldump --set-gtid-purged=OFF-h "database-1.ct6emoc66tc9.ap-southeast-2.rds.amazonaws.com" -P "3306" -u admin -p review > bk_review.sql

• AWS RDS (database-1)에 admin 계정으로 로그인하기

  % mysql -h "database-1.ct6emoc66tc9.ap-southeast-2.rds.amazonaws.com" -P "3306" -u admin -p

• Database(testdb) 생성하기 (DEFAULT CHRACTER SET utf8mb4)

  create database testdb;

• 사용자 (hyerin@%) 에게 testdb 의 모든 권한을 부여하기

  grant all on testdb.* to 'hyerin'@'%';

• 앞서 백업한 review 의 백업 파일을 testdb 에서 실행하기

  use testdb;
  \. bk_review.sql

• 놀면뭐하니 테이블에 데이터를 INSERT 하기 위한 SQL 파일 생성하기 ```sql
• -놀면뭐하니insert.sql

insert into 놀면뭐하니 values (1,'유재석','1972-08-14',53,'M','MC,개그맨','안테나'); insert into 놀면뭐하니 values (2,'하하','1979-08-20',46,'M','래퍼,방송인','QUAN엔터테이먼트'); insert into 놀면뭐하니 values (3,'이이경','1989-01-08',36,'M','배우','상영이엔티'); insert into 놀면뭐하니 values (4,'주우재','1986-11-28',39,'M','모델,방송인','YG엔터테이먼트'); insert into 놀면뭐하니 values (5,'이미주','1994-09-23',31,'F','가수','안테나'); insert into 놀면뭐하니 values (6,'박진주','1988-12-24',37,'F','배우,가수','프레인TPC');

- SQL 파일을 실행하여 AWS RDS (database-1) review 의 놀면뭐하니 테이블에 데이터를 INSERT 하고 확인하기
```sql
use review;

\. 놀면뭐하니insert.sql //SQL 파일 실행

select * from 놀면뭐하니; //테이블 확인

• AWS RDS (database-1) review 의 놀면뭐하니 테이블을 SQL 파일로 백업하기

  % mysqldump --set-gtid-purged=OFF -h "database-1.ct6emoc66tc9.ap-southeast-2.rds.amazonaws.com" -P "3306" -u admin -p review 놀면뭐하니 > 놀면뭐하니backup.sql

• SQL 파일을 실행하여 AWS RDS (database-1) testdb 의 놀면뭐하니 테이블을 review 와 동일하게 만들고 확인하기

use testdb;

\. 놀면뭐하니bavkup.sql //SQL 파일 실행

select * from 놀면뭐하니;

실습확인

• 데이터베이스 review 와 testdb 동일하게 만들기

📌 JOIN

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

📌 JOIN 종류

INNER JOIN 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식
LEFT JOIN 두 개의 테이블에서 공통영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인방식
RIGHT JOIN 두 개의 테이블에서 공통영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인방식
FULL JOIN 두 개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른영역을 모두 포함하는 조인방식
SELF JOIN 자기 자신의 테이블에 대해서 1개 행씩 결합하는 조인방식
CROSS JOIN 조인 조건 없이 테이블끼리 결합하는 조인방식

💻 실습

INNER JOIN 문법

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

LEFT JOIN 문법

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

RIGHT JOIN 문법

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

FULL JOIN 문법

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

SELF JOIN 문법

select column1, column2, ...
from tableA, tableB, ...
where condition

CROSS JOIN 문법

select column1, column2, ...
from tableA
cross join tableB

🧸 review

• 핑계고 table

create table 핑계고
(
  id int not null auto_increment primary key,
  guest varchar(32) not null,
  broadcast_date date,
  episode int not null,
  season int not null
);

• 핑계고 table 에 데이터 insert

  insert into 핑계고 values (1,'이광수','2022-12-16',4,1);
  insert into 핑계고 values (2,'이동욱','2023-01-21',6,2);
  insert into 핑계고 values (3,'하하','2023-03-21',10,2);
  insert into 핑계고 values (4,'이미주','2023-05-24',13,2);
  insert into 핑계고 values (5,'조인성','2023-07-22',19,2);
  insert into 핑계고 values (6,'김종민','2023-09-07',23,2);
  insert into 핑계고 values (7,'공유','2023-09-29',26,2);
  insert into 핑계고 values (8,'차승원','2023-11-18',30,2);
  insert into 핑계고 values (9,'이서진','2024-01-01',35,3);
  insert into 핑계고 values (10,'아이유','2024-02-17',38,3);

• 핑계고에 출연한 놀면뭐하니 테이블의 출연자 중
  배우나 가수가 아닌 출연자의 아이디, 이름, 직업, 시즌, 에피소드 정보 검색

  select a.id 아이디, a.name 이름, a.job_title 직업, b.season 시즌, b.episode 에피소드
  from 놀면뭐하니 a, 핑계고 b
  where a.name = b.guest and not 
  (a.job_title like '%배우%' or a.job_title like '%가수%')

• 핑계고에 출연한 놀면뭐하니 출연자 중 작년 9월 5일 이전에 출연했거나
  소속사 이름이 '엔터테이먼트'로 끝나면서 배우나 개그맨이 아닌 출연자의 아이디, 이름, 직업, 소속사 정보 검색

  select a.id 아이디, a.name 이름, a.job_title 직업, a.agency 소속사
  from 놀면뭐하니 a, 핑계고 b
  where a.name = b.guest 
  and (b.broadcast_date <'2023-04-05' or a.agency like '%엔터테이먼트'
  and not(a.job_title like '%배우%' or a.job_title like '%개그맨%'));

  실습확인

📌 UNION

여러개의 SQL문을 합쳐서 하나의 SQL의 문으로 만들어주는 방법

🚫 칼럼의 개수가 같아야함

📌 UNION 문법

UNION : 중복된 값 제거 후 출력 UNION ALL : 중복된 값도 모두 출력

💻 실습

UNION 문법

select column1, column2, ... from tableA
UNION | UNION ALL
select column1, column2, ... from tableB

🧸 review

• 놀면뭐하니 table

• 직업이 가수인 데이터를 검색하는 쿼리와 직업이 배우인 데이터를 검색하는 쿼리를 중복을 제거한 후 합쳐서 실행

  select * from 놀면뭐하니 where job_title like '%가수%'
  union
  select * from 놀면뭐하니 where job_title like '%배우%';

• 성이 이씨인 데이터를 검색하는 쿼리와 1980년대생을 검색하는 쿼리를 중복을 포함한 후 합쳐서 실행

  select * from 놀면뭐하니 where name like '이%'
  union all
  select * from 놀면뭐하니 where birthday between '1980-01-01' and '1989-12-31';

  실습확인

📌 comparison operators(비교연산자)

📌 logical operators(논리연산자)

💻 실습

AND 문법 &nbsp 조건을 모두 만족하는 경우 TRUE

select column1, column2, ...
from tablename
where condition1 and condition2 and condition3

OR 문법 &nbsp 하나의 조건이라도 만족하는 경우 TRUE

select column1, column2, ...
from tablename
where condition1 or condition2 or condition3 ...

NOT 문법 &nbsp 조건을 만족하지 않는 경우 TRUE

select column1, column2, ...
from tablename
where not condition

BETWEEN 문법 &nbsp 조건값이 범위 사이에 있으면 TRUE

select column1, column2, ...
from tablename
where column1 between value1 and value2

IN 문법 &nbsp 조건값이 목록에 있으면 TRUE

select column1, column2, ...
from tablename
where column in (value1, value2, ...)

LIKE 문법 &nbsp 조건값이 패턴에 맞으면 TRUE

select column1, column2, ...
from tablename
where column like pettern

🧸 review

• 놀면뭐하니 테이블에서 직업 중 가수가 포함되어 있고, 성이 이씨인 데이터 조회

  select * from 놀면뭐하니
  where job_title like '%가수%' and name like '이%';

• 놀면뭐하니 테이블에서 성별이 남자이거나 직업명이 방송인으로 끝나면서 최소 5글자 이상인 데이터 조회

  select * from 놀면뭐하니
  where sex='M' or job_title like '%__방송인';

• 놀면뭐하니 테이블에서 이름이 두글자인 데이터 조회

  select * from 놀면뭐하니
  where name like '__';

• 놀면뭐하니 테이블에서 나이가 35세 이상 60세 이하이면서 개그맨이 아닌 데이터 조회

  select * from 놀면뭐하니
  where age between 35 and 60 and not job_title like '%개그맨%';

• 놀면뭐하니 테이블에서 이이경 이미주 유재석 주우재 중에 소속사 이름이 '상영'으로 시작하는 데이터 조회

  select * from 놀면뭐하니
  where name in ('이이경','이미주','유재석','주우재') 
  and agency like '상영%';

• 놀면뭐하니 테이블에서 이이경 이미주 박진주 하하 중에 가수만 직업으로 가졌거나 가수를 병행하지 않고 배우만 하는 데이터 조회

  select * from 놀면뭐하니
  where name in ('이이경','이미주','유재석','주우재') and 
  (job_title like '가수' or job_title like '배우');

  🚫 연산자 우선순위 주의하기 ( and 가 or 보다 우선순위) 🚫 복잡할때는 단계별로 진행하기

실습확인

📌 CRUD

대부분의 컴퓨터 소프트웨어가 가지는 기본적인 데이터처리 기능인 Create(생성) Read(읽기) Update(갱신) Delete(삭제)를 묶어서 일컫는 말이다

📌 Database 4가지 쿼리형식

📌 ORDER BY

SELECT 문에서 특정 칼럼을 기준으로 오름차순 혹은 내림차순 정렬하여 조회

• ASC(Ascending) : 오름차순으로 정렬 → default
• DESC(Descending) : 내림차순으로 정렬

💻 실습

INSERT 문법

데이터 추가

insert into tablename (column1, column2, ...)
values (value1, value2, ...)

🚫 칼럼의 이름과 값의 순서가 일치해야한다

SELECT 문법

데이터 조회

select column1, column2 ... //특정데이터조회
from tablename

select * from tablename //모든데이터조회

WHERE 문법

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

select column1, column2 ...
from tablename
where condition

UPDATE 문법

데이터 수정

update tablename
set column1 = value1, column2 = value2, ...
where condition

DELETE 문법

데이터 삭제

delete from tablename
where condition

ORDER BY 문법

데이터 정렬

select column1, column2, ...
from tablename
order by column1, column2, ... asc|desc

🧸 review

• table 생성 (놀면뭐하니)

  create table 놀면뭐하니
  (
   id int not null auto_increment primary key,
   name varchar(32) not null default'',
   birthday date,
   age int,
   sex char(1),
   job_title varchar(32),
   agency varchar(32)
  );

• 놀면뭐하니 table에 데이터 추가하기

  insert into 놀면뭐하니 values (1,'유재석','1972-08-14',53,'M','MC,개그맨','안테나');
  insert into 놀면뭐하니 values (2,'하하','1979-08-20',46,'M','래퍼,방송인','QUAN엔터테이먼트');
  insert into 놀면뭐하니 values (3,'이이경','1989-01-08',36,'M','배우','상영이엔티');
  insert into 놀면뭐하니 values (4,'주우재','1986-11-28',39,'M','모델,방송인','YG엔터테이먼트');
  insert into 놀면뭐하니 values (5,'이미주','1994-09-23',31,'F','가수','안테나');
  insert into 놀면뭐하니 values (6,'박진주','1988-12-24',37,'F','배우,가수','프레인TPC');

• 이름, 생년월일, 성별, 소속사 데이터를 소속사 순으로 정렬하기

  select name, birthday, sex, agency from 놀면뭐하니
  order by agency;

• 전체칼럼을 소속사, 이름 순으로 정렬하기

  select * from 놀면뭐하니
  order by agency, name;

• 이름, 나이, 직업, 소속사 데이터를 소속사 순, 나이 역순으로 정렬하기

  select name, age, job_title from 놀면뭐하니
  order by agency, age desc;

  실습확인

table 문법

table 생성

create table tablename (
    columnname datatype,
    columnname datatype,
    ...
);

table 목록확인

show tables;

table 정보확인

desc tablename;

table name 변경

alter table tablename
rename new_tablename;

table column 추가

alter table tablename
add column columnname datatype;

table column 변경

alter table tablename
modify column columnname datatype; //datatype

alter table tablename
change column old_columnname new_columnname new_datatype; //name

table column 삭제

alter table tablename
drop column columnname;

table 삭제

drop table tablename;

🧸 review

example table

create table example
(
   id int,
   name varchar(16)
);

• age(int) column 추가

  alter table example
  add column age int;

• old(int) column 추가

  alter table example
  add column old int;

• age(int) column 삭제

  alter table example
  drop column age;

• old(int) → sex(char) column 변경

  alter table example
  change column old sex char;

• animal table 삭제

  drop table animal;

  🚫 table 생성 전에 데이터베이스 설정하기

실습확인

📌 Database

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

📌 DBMS

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

📌 관계형 데이터베이스
(RDB:Relational Database)

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

📌 SQL

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

📌 SQL 구성

데이터 정의 언어 (DDL : Data Definition Language)

CREATE , ALTER , DROP 등의 명령어

데이터 조작 언어 (DML : Data Manipulation Language)

INSERT , UPDATE , DELETE , SELECT 등의 명령어

데이터 제어 언어 (DCL : Data Control Language)

GRANT , REVOKE , COMMIT , ROLLBACK 등의 명령어

💻 실습

Database 관리

root 계정으로 mysql 접속

% mysql -u root -p
Enter password:

Database 목록 확인

show databases;

Database 생성

create database <database>;

Database 사용

use <database>;

Database 삭제

drop database <database>;

user 조회

mysql 데이터베이스로 이동 후 조회

use mysql;
select host, user from user;

현재 PC에서만 접속 가능한 사용자 생성 (HYERIN,1234)

create user 'username'@'localhost' 
identified by 'password'

외부에서 접속 가능한 사용자 생성 (HYERIN,1234)

create user 'username'@'%'
identified by 'password'

🚫 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제

drop user 'username'@'localhost' //현재PC

drop user 'username'@'%' //외부

user 권한관리

사용자에게 부여된 모든 권한 목록 확인

show grants for 'username'@'localhost'

사용자에게 특정 데이터베이스의 모든 권한 부여

grant all on <database>.* to 'username'@'localhost'

사용자에게 특정 데이터베이스의 모든 권한 삭제

revoke all on <database>.* from 'username'@'localhost'

▶️ 수정내용이 적용되지 않는 경우 새로고침

flush privileges

🧸 review

• review 라는 데이터베이스 만들고 확인하기

  create database review;

• 외부에서도 접속가능한 사용자 생성하기(ID:hyerin, PW:1234)

  create user 'hyerin'@'%' identified by '1234';

• review의 모든권한을 hyerin에게 부여하고 확인하기

  grant all on review.* to 'hyerin'@'%';

• hyerin에게 부여한 review에 대한 권한을 삭제하고 확인하기

  revoke all on review.* from 'hyerin'@'%';

• 외부에서도 접속가능한 hyerin 삭제하고 확인하기

  drop user 'hyerin'@'%';

• review 라는 데이터베이스 삭제하고 확인하기

  drop database review;

  🚫 항상 눈으로 확인하는 습관 들이기

실습확인