> SQL 기초

1. Database 사용

1) Database 관리

(1) my sql 접속

: mysql -u root -p비밀번호

(2) Database 생성/ 삭제

• create database mydb;
• drop database mydb;

(3) Database 확인 / 이동

• show databases;
• use mydb;

2) User 관리

(1) User 조회

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

(2) User 생성

• 현재 PC에서 접근 가능한 계정 사용자
  : localhost
  : CREATE USER '사용자이름'@'localhost' identified by'비밀번호'

• 외부에서 접속 가능한 사용자
  : localhost 대신 %

(3) User 삭제

• DROP USER '사용자이름'@'localhost'
• DROP USER '사용자이름'@'%'

2) User 권한 관리

(1) 권한 관리를 실습하기 위한 Database (testdb) 생성

• Create database test db;
• show databases;

(2) 권한 확인

• use mysql;
• 사용자 생성
  : CREATE USER '사용자이름'@'localhost' identified by'비밀번호';
• 사용자 추가 확인
  : select host, user from user;

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

• 현재 PC에 접근 가능한 사용자이름의 권한 확인
• show grants for '사용자이름'@'localhost';

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

• grant all on DB이름.* to '사용자이름'@'localhost';

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

• revoke all on DB이름.* from '사용자이름'@'localhost';

(6) 참고 : 수정 내용이 적용이 되지 않은 경우 새로고침

• flush privileges;

---

2. Table 사용

1) 실습할 데이터베이스 생성

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

2) Table 생성

• Table = row + column

(1) Table 생성 예제

• my table 생성
  : id
  : name

(2) Table 목록 확인

• show tables;

(3) Table 구조, 상세정보 확인

• desc 테이블네임;
• varchar(16) : str 의미
• Null : 빈 칸을 둘 것 인가 Yes
• Default : 빈 칸 값 디폴트 값

(4) Table 이름 변경

• 이름 변경
  : alter
  (Alter)
• alter table 테이블네임 rename 변경이름;

alter table mytable rename person;

(5) Table 컬럼 추가

• alter table 테이블 네임
  ADD column 컬럼네임 타입;
• agee 컬럼 이름에 double 타입에 컬럼 추가

alter table person add column agee double;

(6) Table 컬럼 수정

• DataType 변경
  : alter table 테이블네임
  modify column 컬럼네임 변경할데이터타입;

alter table person modify column agee int;

• Table column 이름 변경
  : alter table 테이블네임
  change column 기존컬럼네임 새로운컬럼네임 데이터타입;

alter table person change column agee age int;

: change column 문법은 컬럼 네임, 데이터 타입 모두 변경할 수 있음

• Table column 삭제
  : alter table 테이블네임
  drop column 컬럼네임;

alter table person drop column age;

(7) Table 삭제

• Drop table 테이블네임;
• show tables;
  : 삭제 됐는지 테이블 확인

drop table animal;

---

3. INSERT, SELECT, UPDATE, DELETE

• SQL 조작 언어
• 실습 환경
  

1) INSERT : 데이터 추가

(1) 데이터를 추가하는 명령어

• insert into tablename (column1, column2, ...)
  values (value1, value2, ...);
• 컬럼, 벨류 값 순서가 같아야 함
  column1 = value1
  column2 = value2 ...
• 벨류 값 문자열은 ' ' 따옴표

insert into person (id, name, age, sex)
    -> values (1, '이효리', 43, 'F');

(2) 모든 컬럼 값을 추가하는 경우

• insert into tablename
  values (value1, value2, ...);
• 컬럼 이름을 지정하지 않아도 됨
• 입력하는 값의 순서가 테이블 컬럼 순서와 일치해야 함

insert into person
    -> values (2, '이상순', 48, 'M');

2) SELECT

(1) 테이블 내의 특정 컬럼에 대한 데이터 조회

• select colunm1, column2, ...
  from tablename;
• SELECT + FROM 외우기 !

select name, age from person; 

(2) 테이블에 모든 데이터

• select * from tablename;

select * from person;

• person은 테이블네임

• ' * ' : 전부
  : person 테이블에 모든 데이터를 보겠다

• desc person; 과의 차이점
  

3) WHERE

• SQL 문에 조건을 추가

• SELECT 뿐만 아니라 UPDATE 와 DELETE 에도 사용

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

• SELECT 컬럼1, 컬러2, ...
  FROM 테이블 네임
  WHERE 조건;

• Ex. person 테이블에서 성별이 여자인 데이터 조회/ 필터링

select * from person where sex='F';

4) UPDATE

• 데이터를 수정하는 명령어
• UPDATE tablename
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

update person set age=23 where name='이효리';

5) DELETE

• 데이터를 삭제하는 명령어
• DELETE FROM 테이블 네임
  WHERE 조건;

delete from person where name='이상순';

• 명령어 후, select * from person; 데이터 확인하기
  

---

4. ORDER BY

1) 실습 환경만들기

• 데이터베이스 : zerobase

use zerobase;

2) celeb 테이블 만들기

(1) 실습할 테이블 생성

• NOT NULL AUTO_INCREMENT PRIMARY KEY

• NOT NULL DEFAULT ' ',
  

• 테이블 생성 후 꼭 확인 하기 !

desc celeb;

(2) 실습할 데이터 추가

• INSERT INTO ... VALUES ( , , , )

(3) 데이터 확인

• select * from celeb;
• 수정할 부부 : update 사용하기
  

3) order by

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

(1) ORDER BY 문법

• ACE(ascending) : 오름차순

• DESC(descending) : 내림차순

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블네임
  ORDER BY 컬럼1, 컬럼2, ... ASC | DESC ;

• SELECT FROM WHERE 다음에 ORDER BY

(2) 예제1

• celeb 테이블에서 이름과 나이를 오름차순 정리

SELECT age, name
FROM celeb
ORDER BY age ASC;

• = 디폴트 값이 오름차순이기 때문에 ASC는 생략 가능

= select age, name from celeb order by age; 

(2) 예제2

• celeb 테이블에서 이름과 나이를 내림차순 정리

SELECT age, name
FROM celeb
ORDER BY age DESC;

-------------------------

select age, name from celeb order by age desc;

(3) 예제3 : 여러 조건 정렬

• celeb 테이블에서 이름과 나이를 1.나이, 2. 이름 순으로 정렬
• 디폴트 값이 오름차순 : ASC를 생략

SELECT age, name
FROM  celeb
ORDER BY age, name;

-------------------------

select age, name from celeb order by age, name;

(4) 예제4 : 여러 조건 정렬

• celeb 테이블에서 이름과 나이를 1. 나이의 역순(내림차순)으로 정렬한 뒤, 2. 이름 순으로 오름차순 정렬

SELECT age, name
FROM celeb
ORDER BY age DESC, name ASC;

---

5. Comparision Operators : 비교 연산자

1) 비교 연산자

2) 예제

(1) A = B

• 나이가 29세인 데이터 검색

select name, age from celeb where age=29 order by age;

(2) A != B

• 나이가 29가 아닌 데이터 검색

select name, age from celeb where age!=29 order by age;

(3) A > B

• 나이가 29세보다 큰 데이터 검색 (내림차순)

select age, name from celeb where age>29 order by age desc;

(4)

---

6. Logical Operations : 논리 연산자

• 논리 연산자
  

1) AND : 조건 모두 만족해야 TRUE

• SELECT
  FROM
  WHERE ____ AND ____ AND ____ ... ;
  

(1) 예제1

• 나이가 29세이고 성별에 여성인 데이터 검색

SELECT * FROM celeb WHERE age=29 AND sex='F';

(2) 예제2

• 성별이 남자이고 나이가 40세 보다 큰 데이터를 이름의 역순으로 정렬하여 검색

select * from celeb where sex='M' and age>40 order by name desc;

2) OR : 조건 하나라도 만족하면 TRUE

• SELECT
  FROM
  WHERE ____ OR ____ OR ____ ... ;

(1) 예제1

• 나이가 25세보다 작거나 30세보다 큰 데이터 검색

select * from celeb where age<25 or age>30;

(2) 예제2 : AND + OR

• AND 조건 먼저 처리하고 OR 처리함
• 선 조건은 괄호
• 나이가 29세 보다 작고 여자이거나, 나이가 30세보다 크고 남자인 데이터를 나이와 성별 순으로 정렬하여 검색

select * from celeb
where (age<29 and sex='F') or (age>30 and sex='M')
order by age, sex;

(3) 예제3

• YG ENT. 소속이거나 나무엑터스 소속인 연예인 중, 나이가 30세보다 작은 데이터를 검색
• 괄호 주의

select * from celeb
where (agency='YG엔터테이먼트' or agency='나무엑터스') and age<30
order by age, sex;

3) NOT : 조건을 만족하지 않는 경우 TRUE

• SELECT
  FROM
  WHERE NOT ____조건____ ;

(1) 예제1

• 성별이 여자가 아닌 데이터 검색

SELECT * FROM celeb WHERE NOT sex='F';

(2) 예제2

• 소속사가 YG ent. 면서 남자가 아니거나,
  직업이 가수이면서 YG ent.가 아닌 데이터 검색

select * from celeb 
where (agency='YG엔터테이먼트' and not sex='M') or (job_title='가수' and not agency='YG엔터테이먼트');

(3) 예제3

• 생일이 1990년 이후이면서 여자가 아니거나,
  생일 1979년 이전이면서 소속사가 안테나가 아닌 데이터 검색

select * from celeb
where (birthday > 19891231 and not sex='F') or (birthday<19800101 and not agency = '안테나');

4) BETWEEN : 조건 값이 범위 사이에 있으면 TRUE

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블네임
  WHERE 컬럼 BETWEEN ____ AND ____ ;

(1) 예제1

• 나이가 20세~40세 사이 데이터 검색

select * from celeb
where age between 20 and 40;

• and 연산자를 이용한 같은 쿼리문

select * from celeb where age>=20 and age<=40;

(2) 예제2

• 생년월일이 1980년에서 1995년 사이가 아니면서 여자이거나,
  소속사가 YG Ent. 이면서 나이가 20세~45세가 아닌 데이터 검색

select * from celeb
where (not birthday between 19800101 and 19951231 and sex='F')
      or (agency='YG엔터테이먼트' and not age between 20 and 45);

5) IN : 조건 값이 목록에 있으면 TRUE

• SELECT
  FROM
  WHERE 컬럼명 IN (벨류1, 벨류2, 벨류3, ...) ;

(1) 예제1

• 나이가 28세, 48세 중 하나인 데이터 검색

SELECT * FROM celeb
WHERE age IN (28, 48);

(2) 예제2

• 소속사가 나무엑터스, 안테나, 울림엔터테이먼트가 아니면서,
  성별이 여자거나 나이가 45세 이상인 데이터 검색

select * from celeb
where not agency in ('나무엑터스', '안테나', '울림엔터테이먼트')
    and (sex='F' or age>=45);

6) LIKE : 조건 값이 패턴에 맞으면 TRUE

• SELECT
  FROM
  WHERE 컬럼 LIKE 패턴 ;

(1) 예제1

• 소속사 이름이 YG 엔터테이먼트인 데이터를 검색

SELECT *
FROM celeb
WHERE agency LIKE 'YG엔터테이먼트' ; 

(2) 예제2

• YG로 시작하는 소속사 이름을 가진 데이터를 검색
• YG%

select * from celeb where agency like 'YG%' ;

(3) 예제3

• '엔터테이먼트'로 끝나는 소속사 데이터 검색
• %엔터테이먼트

select * from celeb where agency like '%엔터테이먼트' ;

(4) 예제4

• 직업명에 '가수'가 포함된 데이터를 검색
• %가수%

select * from celeb where job_title like '%가수%' ;

(5) 예제5

• 소속사 이름의 두번째 글자가 G인 데이터 검색
• _G%

select * from celeb where agency like '_G%'

(6) 예제6

• 직업명이 '가'로 시작하고 최소 2글자 이상인 데이터 검색
• 가_% : 최소 두글자를 확보한 후 뒤에 %

select * from celeb
where job_title like '가_%'

(7) 예제7

• 직업이 하나 이상인 연예인 중 영화배우 혹은 가수가 아닌 연예인 검색
• % , %

select * from celeb
where job_title like '%,%' ;

---

7. Union

1) 실습환경 만들기

• test1, test2 table 생성
  

2) UNION 문법

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

• 주의 ! 컬럼의 개수가 같아야함 !

• SELECT문 1
  UNION | UNION ALL
  SELECT문 2
  

(1) UNION : 중복된 값을 제거하여 보여준다

• 1 2 3 5 6
• 중복된 값 3을 한 번만

SELECT * FROM test1
UNION
SELECT * FROM test2;

(2) UNION ALL : 중복된 값도 모두 보여준다

• 1 2 3 5 6 3

select * from test1
union all
select * from test2;

3) 예제

(1) 성별에 여자인 데이터를 검색하는 쿼리와 소속사가 YG Ent.인 데이터를 검색하는 쿼리를 UNION ALL로 실행

select name, sex, agency from celeb where sex='F'
union all
select name, sex, agency from celeb where agency='YG엔터테이먼트' ;

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

select * from celeb
where job_title like '%가수%'
union
select * from celeb
where job_title like '%배우%';

---

8. Join

1) 실습환경 만들기

(1) snl_show 테이블 생성

(2) 테이블에 데이터 추가

• insert into snl_show values(1, 8, 7, '2020-09-05', '강동원');
  

2) JOIN 기본

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

• 실습 환경
  

3) INNER JOIN

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

(1) INNER JOIN 문법

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블A
  INNER JOIN 테이블B
  ON 테이블A.column = 테이블B.column
  WHERE 조건;

• ON : 조인 기준

(2) 예제1

• snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN

• 컬럼명이 겹치지 않을 경우, 테이블명 생략 가능
  : select celeb.id, name, snl_show.id, host

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
INNER JOIN snl_show
ON celeb.name = snl_show.host;

• select * from celeb inner join snl_show
  

4) LEFT JOIN

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

(1) LEFT JOIN 문법

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블A
  LEFT JOIN 테이블B
  ON 테이블A.column = 테이블B.column
  WHERE 조건;

• 테이블A = LEFT 테이블
  테이블B = RIGHT 테이블

(2) 예제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;

5) RIGHT JOIN

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

(1) RIGHT JOIN 문법

• SELECT 컬럼1, 컬럼2, ... # 가져올 컬럼
  FROM 테이블A
  RIGHT JOIN 테이블B
  ON 테이블A.column = 테이블B.column
  WHERE 조건;

(2) 예제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;

6) FULL OUTER JOIN

• MYSQL 에서는 지원하지 않는 문법

(1) FULL OUTER JOIN 문법

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블A
  FULL OUTER JOIN 테이블B
  ON 테이블A.column = 테이블B.column
  WHERE 조건;

(2) FULL JOIN 결과와 같은 퀴리문

• LEFT JOIN 과 RIGHT JOIN 사용 후, UNION 사용

• UNION : 중복된 부분 제거

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;

7) SELF JOIN

• 가장 많이 사용되는 JOIN
• INNER JOIN 과 같은 결과 : 공통된 부분 가져오기

(1) SELF JOIN 문법

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블A, 테이블B, ...
  WHERE 조건;

• FROM 합칠 여러 테이블
  WHERE 기준 조건

(2) 예제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;

(3) 예제2

• celeb 테이블의 연예인 중, snl_show host로 출연했고, 소속사가 안테나인 사람의 이름과 직업 선택

select celeb.name, celeb.job_title
from celeb, snl_show
where celeb.name = snl_show.host and celeb.agency='안테나';

(4) 예제3

• celeb 테이블의 연예인 중, snl_show에 host로 출연했고,
  배우는 아니면서 YG엔터테이먼트 소속이거나
  40세 이상이면서 YG엔터테이먼트 소속이 아닌
  연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보 검색

• STEP 1 : celeb 테이블의 연예인 중, 영화배우는 아니면서 YG 엔터 소속이거나

select * from celeb
where not job_title like '%배우%' and agency = 'YG엔터테이먼트' ;

• STEP 2 : celeb 테이블의 연예인 중, 40세 이상이면서 YG 엔터 소속이 아닌

select * from celeb
where age >= 40 and agency != 'YG엔터테이먼트';

• STEP 3 : celeb 테이블의 연예인 중, snl_show에 host로 출연했고

select celeb.id, celeb.name, snl_show.host
from celeb, snl_show
where celeb.name = snl_show.host ;

• STEP 4 : celeb 테이블의 연예인 중, snl_show에 host로 출연했고, 배우는 아니면서 YG엔터테이먼트 소속이거나

select celeb.id, celeb.name, snl_show.host
from celeb, snl_show
where celeb.id = snl_show.host
and not job_title like '%배우%' and agency = 'YG엔터테이먼트' ;

• STEP 5 : 모든 조건 합치기
• 괄호 주의 !

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엔터테이먼트')) ;

(5) 예제4

---

9. Concat, ALIAS, DISTINCT, LIMIT

1) CONCAT

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

(1) 문법

• SELECT CONCAT('string1', 'string2', ..) ;

(2) 예제1

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

(3) 예제2

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

2) ALIAS

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

(1) 문법

• SELECT 컬럼 as 컬럼 별칭
  FROM 테이블네임 ;

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블네임 as 테이블 별칭 ;

(2) 예제1

SELECT name as '이름' FROM celeb ;

(3) 예제2 : alias를 여러개

SELECT name as '이름', agency as '소속사' FROM celeb ;

(4) 예제3 : concat + alias 동시에 사용

• name 과 job_title 을 합쳐서, profile 이라는 별칭을 만들어서 검색

SELECT CONCAT (name, ':' , job_title) as profile
FROM celeb ;

(5) 예제4 : self join + 테이블 alias 사용

• snl_show 에 출연한 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 ;

(5) 예제4 : concat + 컬럼, 테이블 alias + self join

• as 생략 가능
  

3) DISTINCT

• 검색한 결과의 중복 제거

(1) 문법

• SELECT DISTINCT 컬럼1, 컬럼2, ...
  FROM 테이블네임 ;

(2) 예제1

• 연예인 소속사 종류를 검색 (중복 포함)

SELECT agency FROM celeb ;

• 연예인 소속사 종류를 검색 (중복 제외 : DISTINCT)

SELECT DISTINCT agency FROM celeb ;

(3) 예제2

• 가수 중에서, 성별과 직업을 검색 (중복 제외)

select distinct sex, job_title from celeb
where job_title like '%가수%' ;

4) LIMIT

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

(1) 문법

• SELECT 컬럼1, 컬럼2, ...
  FROM 테이블네임
  WHERE 조건
  LIMIT 숫자 ;

• 셀렉트 문 마지막에 넣으면 됨

(2) 예제1

• celeb 데이터 3개만 가져오기

select * from celeb limit 3;

(3) 예제2

• celeb 에서 나이가 적은 순으로 4명까지 검색

select * from celeb order by age limit 4;