[7주차] SQL

Database 사용

Database 정의

Database란

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

DBMS란

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

관계형 데이터베이스란

RDB : Relational Database

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

SQL이란

Structured Query Language

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

SQL 구성

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

Database 관리

• root 계정으로 mysql 접속
  mysql -u root -p

• database 목록 확인
  SHOW DATABASE;

• Database 이름을 지정하여 생성
  CREATE DATABSE dbname;

CREATE DATABSE testdb; : testdb라는 이름의 데이터베이스 생성 및 확인

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

• testdb 데이터베이스 삭제
  DROP DATABSE testdb;

User 관리

• 사용자 정보는 mysql에서 관리하므로 일단 mysql 데이터베이스로 이동후 조회

use mysql;
SELECT host, user FROM user;

• 현재 PC에서만 접속 가능한 사용자를 비밀번호와 함께 생성
  CREATE USER 'username'@'localhost' identified by 'password';
  CREATE USER 'noma'@'localhost' identified by '1234';

• 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성
  CREATE USER 'username'@'%' identified by 'password';
  CREATE USER 'noma'@'%' identified by '1234';

• 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제
  DROP USER 'username'@'localhost'
  DROP USER 'username'@'%'

User 권한 관리

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

• 사용자에게 부여된 모든 권한 목록을 확인
  SHOW GRANTS FOR 'username'@'localhost';

• 현재 PC에 접근가능한 noma의 권한 확인
  SHOW GRANTS FOR 'noma'@'localhost';

• 사용자에게 특정 데이터베이스의 모든 권한을 부여
  GRANT ALL ON dbname.* to 'username'@'localhost';

• 사용자에게 특정 데이터베이스의 모든 권한을 삭제
  REVOKE ALL ON dbname.* from 'username'@'localhost';

• 현재 PC에 접근가능한 noma에게 testdb의 모든 권한을 삭제
  REVOKE ALL ON testdb.* from 'noma'@'localhost';

• (참고) 수정내용이 적용이 되지 않은 경우 새로고침
  FLUSH PRIVILEGES;

Table 사용

실습할 데이터베이스 생성

• zerobase라는 이름의 데이터베이스 생성
  CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;

Table 생성 문법

CREATE TABLE tablename
(
columnname datatype,
columnname datatype,
...
)

• id(int)와 name(varchar(16)) 칼럼을 가지는 mytable 이라는 이름의 테이블 생성

CREATE TABLE mytable(id int, name varchar(16));

• mytable 테이블 정보 확인
  DESC mytable;

Table 변경

Table 이름 변경 문법

ALTER TABLE tablename
RENAME new_tablename

• mytable 이름을 person으로 변경
  ALTER TABLE mytable RENAME person;

Table column 추가 문법

ALTER TABLE tablename
ADD COLUMN columnname datatype;

• person 테이블에 agee(double) 컬럼 추가
  ALTER TABLE person ADD COLUMN agee double;

Table column 변경 문법 - DataType

ALTER TABLE tablename
MODIFY COLUMN columnname 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;

• person 테이블의 agee 컬럼 이름을 age로 변경
  ALTER TABLE person
  CHANGE COLUMN agee age int;

Table column 삭제 문법

ALTER TABLE tablename
DROP COLUMN columnname;

• person 테이블의 age 컬럼을 삭제
  ALTER TABLE person
  DROP COLUMN age;

TABLE 삭제

DROP TABLE tablename;

• person 테이블 삭제
  DROP TABLE person;

INSERT

INSERT - 데이터 추가

INSERT INTO tablename(column1, column2)
VALUES (value1, value2,...);

• 입력한 컬럼 이름의 순서와 값의 순서가 일치하도록 주의

• ID값이 1인 이효리, 43세, 여자(F) 데이터 추가
  INSERT INTO person (id,name,age,sex)
  VALUES (1,'이효리',43,'F');

INSERT INTO tablename
VALUES (values1, value2,...);

• 모든 컬럼값을 추가하는 경우에는 다음과 같이 컬럼 이름을 지정하지 않아도 되지만, 입력하는 값의 순서가 테이블의 컬럼 순서와 일치핟록 주의

• ID값이 2인 이상순, 48세, 남자(M) 데이터 추가
  INSERT INTO person
  VALUES(2,'이상순',48,'M')

SELECT - 데이터 조회

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

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

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

WHERE - 조건

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

WHERE문법 - SELECT

• 테이블 내에서 조건을 만족하는 데이터 조회
  SELECT column1, column2,...
  FROM tablename
  WHERE condition;

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

UPDATE - 데이터 수정

UPDATE tablename
SET column1 = value1, column2 = value2,...
WHERE condtion;

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

DELETE - 데이터 삭제

DELETE FROM tablename
WHERE condition;

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

ORDER BY

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

• ASC(Ascending) : 오름차순으로 정렬

• DESC(Descending) : 내림차순으로 정렬
  SELECT column1, column2,...)
  FROM tablename
  ORDER BY column1, column2,... ASC | DESC;

• celeb 테이블에서 이름과 나이를 나이순으로 조회
  SELECT age, name
  FROM celeb
  ORDER BY age ASC;

• celeb 테이블에서 이름과 나이를 역순(내림차순)으로 조회
  SELECT age,name
  FROM celeb
  ORDER BY age DESC;

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

• 나이가 같은경우 이름순으로 정렬
  

• celeb 테이블에서 이름과 나이를 나이의 역순으로 정렬한 뒤 이름순으로 정렬하여 조회
  SELECT age, name
  FROM celeb
  ORDER BY age DESC, name ASC;
  

Comparison Operators

비교연산자

• A=B : 같은

• A>B : 초과

• A<B : 미만

• A>=B : 이상

• A<=B : 이하

• A<>B : 같지 않은

• A!=B : 같지 않은

• 나이가 29세인 데이터 검색
  SELECT name,age FROM celeb WHERE age=29 ORDER BY age;

• 나이가 29세가 아닌 데이터 검색
  SELECT name,age FROM celeb WHERE age!=29 ORDER BY age;

• 나이가 29세 보다 큰 데이터 검색
  SELECT name,age FROM celeb WHERE age>29 ORDER BY age;

• 나이가 29세 보다 크거나 같은 데이터 검색
  SELECT name,age FROM celeb WHERE age>=29 ORDER BY age;

• 나이가 29세 보다 작거나 같은 데이터 검색
  SELECT name,age FROM celeb WHERE age<=29 ORDER BY age;

• 나이가 29세 보다 크거나 작은(같지 않은) 데이터 검색
  SELECT name,age FROM celeb WHERE age<>29 ORDER BY age;

• celeb 테이블에서 나이(age)가 30세 이하인 데이터의 이름과 나이를 나이순으로 조회하세요.
  

• celeb 테이블에서 이름이 ‘아이유’인 데이터의 이름과 나이, 성별, 직업, 소속사를 조회하세요.
  

• celeb 테이블에서 소속사가 YG엔터테이먼트가 아닌 데이터의 이름, 직업, 소속사를 소속사 순으로 정렬하여 조회하세요.
  

• celeb 테이블에서 성별이 남자인 데이터의 이름, 성별, 소속사 정보를 나이의 역순으로 정렬한 뒤 소속사 순으로 정렬하여 조회하세요.
  

• celeb 테이블에서 나이가 50세 미만인 데이터의 성별, 소속사, 이름, 나이를 성별 순으로 정렬한 뒤 소속사 역순으로 정렬하고 마지막으로 이름 순으로 정렬하여 조회하세요.
  

Logical Operators

논리연산자

• AND : 조건을 모두 만족하는 경우 TRUE
• OR : 하나의 조건이라도 만족하는 경우 TRUE
• NOT : 조건을 만족하지 않는 경우 TRUE
• BETWEEN : 조건값이 범위 사이에 있으면 TRUE
• IN : 조건값이 목록에 있으면 TRUE
• LIKE : 조건값이 패턴에 맞으면 TRUE

AND 문법

SELECT column1, column2,...
FROM tablename
WHERE condtion1 AND condition2 AND condition3...;

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

• 성별이 남자이고 나이가 40세 보다 큰 데이터를 이름의 역순으로 정렬하여 검색
  SELECT * FROM celeb WHERE sex='M' AND age>40 ORDER BY name DESC;
  

• celeb 테이블에서 성별이 남자이고 소속사가 YG엔터테이먼트인 데이터를 소속사 순으로 정렬하여 조회하세요.

• celeb 테이블에서 나이가 30세 이상이고 성별이 남자인 데이터를 나이순으로 정렬하여 조회하세요.

OR 문법

하나의 조건이라도 만족하는 경우 TRUE

SELECT column1, column2,...
FROM tablename
WHERE condition1 OR condition2 OR condition3...;

• 나이가 25세보다 작거나 30세보다 큰 데이터 검색
  SELECT * FROM celeb WHERE age<25 OR age>30 ORDER BY age;
  

• 나이가 29세보다 작고 여자이거나, 나이가 30세보다 크고 남자인 데이터를 나이와 성별 순으로 정렬하여 검색
  SELECT FROM WHERE celeb age<29 AND sex='F';
  SELECT FROM WHERE celeb age>30 AND sex='M';
  => SELECT * FROM celeb WHERE (age<29 AND sex='F') OR (age>30 AND sex='M') ORDER BY age,sex;
  

• YG엔터테인먼트 소속이거나 나무엑터스 소속인 연예인 중, 나이가 30세 보다 작은 데이터를 검색
  SELECT * FROM celeb WHERE (agency='YG엔터테인먼트' OR agency='나무엑터스') AND age<30;

*괄호를 안쳐주면 AND가 OR보다 먼저 연산되어서 결과가 달라짐

• Celeb 테이블에서 소속사가 YG 엔터테이먼트 이거나 안테나인 데이터를 소속사 순으로 정렬하여 조회하세요.
  

• Celeb 테이블에서 남자이면서 YG엔터테이먼트 소속이거나, 나이가 30세 보다 작은 데이터를 나이, 소속사 순으로 정렬하여 검색하세요.
  

• Celeb 테이블에서 아이디가 홀수면서 성별이 남자거나, 아이디가 짝수면서 소속사가 YG엔터테이먼트인 데이터를 나이순으로 정렬하여 검색하세요.
  

NOT 문법

• 조건은 만족하지 않는 경우 TRUE

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

• 성별이 여자가 아닌 데이터 검색
  SELECT * FROM celeb WHERE NOT sex='F';

• 소속사가 YG엔터테이먼트이면서 남자가 아니거나 직업이 가수이면서 소속사가 YG엔터테이먼트가 아닌 데이터 검색
  

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

• Celeb 테이블에서 소속사가 YG엔터테이먼트가 아니고 나이가 40세 이하인 데이터를 이름순으로 정렬하여 조회하세요.
  

• Celeb 테이블에서 성별이 남자가 아니거나 나이가 30세 이상인 데이터를 나이의 역순으로 정렬하여 조회하세요.
  

• Celeb 테이블에서 직업이 가수가 아니면서 성별이 여자이거나, 나이가 40보다 작지 않으면서 아이디가 홀수인 데이터를 조회하세요.
  

BETWEEN 문법

• 조건값이 범위 사이에 있으면 TRUE
  SELECT column1, column2,...
  FROM tablename
  WHERE column1 BETWEEN value1 AND value2;

• 나이가 20세에서 40세 사이의 데이터 검색
  SELECT * FROM celeb WHERE age BETWEEN 20 AND 40;

SELECT * FROM celeb WHERE age>=20 AND age<=40;

• 생년월일이 1980년에서 1995년 사이가 아니면서 여자이거나, 소속사가 YG엔터테인먼트이면서 나이가 20세에서 45세 사이가 아닌 데이터 검색
  

• Celeb 테이블에서 나이가 30세에서 60세 사이이고 성별이 남자인 데이터를 나이순으로 정렬하여 조회하세요.
  

• Celeb 테이블에서 나이가 30세에서 60세 사이가 아니거나 YG엔터테이먼트 소속인 데이터를 나이의 역순으로 정렬하여 조회하세요.
  

• Celeb 테이블에서 아이디가 1 에서 5사이의 값이면서 성별이 여자이거나,
  아이디가 홀수이면서 성별이 남자이면서 나이가 20세에서 30세 사이인 데이터를 조회하세요.
  

IN 문법

• 목록 안에 조건이 존재하는 경우 TRUE
  SELECT column1, column2,...
  FROM tablename
  WHERE column IN (value1, value2,...);

• 나이가 28세, 48세 중 하나인 데이터 검색
  SELECT * FROM celeb WHERE age IN (28,48);

SELECT * FROM celeb WHERE age=28 OR age=48;

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

• celeb 테이블에서 아이유, 이미주, 유재석, 송강 중에 소속사가 ‘나무엑터스’인 데이터를 조회하세요.
  

• celeb 테이블에서 소속사가 안테나, YG엔터테이먼트 중 하나가 아니고, 성별이 여자인 데이터를 조회하세요.
  

• celeb 테이블에서 아이유, 송강, 강동원, 차승원 중에 YG엔터테이먼트 소속이 아니거나 나이가 40세에서 50세 사이인 사람을 찾아보세요.
  

LIKE 문법

• 조건값이 패턴에 맞으면 TRUE
  SELECT column1, column2,...
  FROM tablename
  WHERE column LIKE pattern;

• 소속사 이름이 'YG엔터테인먼트'인 데이터를 검색
  SELECT * FROM celeb WHERE agency LIKE 'YG엔터테인먼트';

SELECT * FROM celeb WHERE agency='YG엔터테인먼트';

• 'YG'로 시작하는 소속사 이름을 가진 데이터를 검색
  SELECT * FROM celeb WHERE agency LIKE 'YG%';

• '엔터테인먼트'로 끝나는 소속사 이름을 가진 데이터를 검색
  SELECT * FROM celeb WHERE agency LIKE '%엔터테인먼트';

• 직업명에 '가수'가 포함된 데이터를 검색
  SELECT * FROM celeb WHERE job_title LIKE '%가수%';

• 소속사 이름의 두번째 글자가 G인 데이터를 검색
  SELECT * FROM celeb WHERE agency LIKE '_G%';

• 직업명이 '가'로 시작하고 최소 2글자 이상인 데이터 검색
  SELECT * FROM celeb WHERE jobtitle LIKE '가%';

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

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

• 영화배우와 탤런트를 병행하는 연예인 검색
  SELECT * FROM celeb
  WHERE job_title LIKE '%영화배우%' AND job_title LIKE '%탤런트%';

• 직업이 하나 이상인 연예인 중 영화배우 혹은 탤런트가 아닌 연예인 검색
  

• celeb 테이블에서 직업중 가수가 포함되어 있고 성이 이씨인 데이터 조회하세요.
  

• celeb 테이블에서 성별이 남자 이거나 직업명이 ‘탤런트’ 로 끝나면서 최소 5글자 이상인 데이터를 조회하세요.
  

• celeb 테이블에서 이름이 두글자인 데이터를 조회하세요.
  

• celeb 테이블에서 나이가 30세 이상 50세 이하면서 개그맨이 아닌 데이터를 조회하세요.
  

• and job_title not like '%개그맨%';

• celeb 테이블에서 아이유, 이미주, 유재석, 송강 중에 소속사 이름이 ‘나무’로 시작하는 데이터를 조회하세요.
  

• celeb 테이블에서 아이유, 이미주, 송강, 이수현 중에 가수만 직업으로 가졌거나, 가수를 병행하지 않고 탤런트를 하는 사람을 찾으세요.
  <강의>
  

<내가 쓴 답>

UNION

• 여러 개의 SQL문을 합쳐서 하나의 SQL 문으로 만들어 주는 방법(주의. 칼럼의 개수가 같아야 함)

UNION 문법

• UNION : 중복된 값을 제거하여 알려준다.

• UNION ALL : 중복된 값도 모두 보여준다.

• test1 의 모든 데이터와 test2의 모든 데이터를 중복된 값을 포함하여 검색
  SELECT FROM test1
  UNION ALL
  SELECT FROM test2;

• test1 의 모든 데이터와 test2의 모든 데이터를 중복된 값을 제거하여 검색
  SELECT FROM test1
  UNION
  SELECT FROM test2;
  

• 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테인먼트인 데이터를 검색하는 쿼리를 union all로 실행
  

• 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테인먼트이니 데이터를 검색하는 쿼리를 union으로 실행
  

• 가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와, 1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 union으로 실행

• 직업이 가수인 (가수를 포함하는) 데이터를 검색하는 쿼리와 직업이 텔런트인 (텔런트를 포함하는) 데이터를 검색하는 쿼리를 중복을 제거하여 합쳐서 실행하세요.
  

• 성이 이씨인 데이터를 검색하는 쿼리와 1970년대생을 검색하는 쿼리를 중복을 포함하여 합쳐서 실행하세요.
  

JOIN

JOIN 기본

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

• INNER JOIN
• FULL OUTER JOIN
• LEFT JOIN
• RIGHT JOIN

INNER JOIN

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

SELECT column1, column2,...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

• 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

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

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;

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

FULL OUTER JOIN

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

SELECT column1, column2,...
FROM tableA
FULL OUTER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

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

• MYSQL에서는 FULL JOIN을 지원하지 않으므로 다음의 쿼리로 같은 결과를 만들 수 있다.

SELF JOIN

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

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

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

• snl_show 에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보를 검색

• snl_show 에 출연한 celeb 중, 에피소드 7, 9, 10 중에 출연했거나
  소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 작년 9월15일 이후에 출연했던 사람을 검색

• snl_show 에 출연한 celeb 테이블의 연예인 중, 영화배우나 텔런트가 아닌 연예인의 아이디, 이름, 직업, 시즌, 에피소드 정보를 검색하세요.

• 마지막 줄
  and not (job_title like '%영화배우%' or job_title like '%탤런트%');

• snl_show 에 출연한 celeb 중, 작년 9월 15일 이후에 출연했거나
  소속사 이름이 ‘엔터테이먼트’ 로 끝나지 않으면서 영화배우나 개그맨이 아닌 연예인의 celeb 아이디, 이름, 직업, 소속사를 검색하세요.
  

CONCAT

CONCAT

• 여러 문자열을 하나로 합치거나 연결
  SELECT CONCAT('string1', 'string2',..);

SELECT CONCAT('이름:',name) FROM celeb;

ALIAS

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

SELECT column as alias
FROM tablename;

• name을 이름으로 별칭을 만들어서 검색
  

• name은 이름으로, agency는 소속사로 별칭을 만들어서 검색
  

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

• snl_korea에 출연한 celeb을 기준으로 두 테이블을 조인하여, celeb 테이블은 c, snl_show 테이블은 s 라는 별칭을 만들어서 출연한 시즌과 에피소드, 이름, 직업을 검색

• snl_korea에 출연한 celeb을 기준으로 두 테이블을 조인하여 다음과 같이 각 데이터의 별칭을 사용하여 검색

  - 시즌, 에피소드, 방송일을 합쳐서 '방송정보'
  - 이름, 직업을 합쳐서 '출연자 정보'

  

• as는 생략가능

• 이름이 3글자인 연예인 정보를 검색하여 다음과 같이 출력하세요
  

• 앞글자가 2글자이고, ‘엔터테이먼트’ 로 끝나는 소속사 연예인 중
  SNL 에 출연한 연예인의 신상정보(나이, 성별)와 출연정보(시즌-에피소드, 방송날짜), 소속사 정보를 방송날짜 최신순으로 정렬하여 다음과 같이 검색하세요.

DISTINCT

• 검색한 결과의 중복 제거

SELECT DISTINCT column1, column2,...
FROM tablename;

• 연예인 소속사 종류를 검색 - 중복 포함
  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 '%가수%';

• celeb 테이블에서 성별과 소속사 별 종류를 검색하여 성별, 소속사 순으로 정렬하세요.
  

LIMIT

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

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

celeb 데이터 3개만 가져오기
SELECT * FROM celeb LIMIT 3;

• 나이가 가장 적은 연예인 4명 검색
  

• celeb 테이블에서 남자 연예인 중 나이가 가장 많은 2명을 조회하세요.

• SNL에 출연한 연예인의 정보를 최신 방송날짜 순으로 2개만 검색하여 다음과 같이 출력하세요.

AWS RDS

Amazon Relational Database Service

• AWS에서 제공하는 관계형 데이터베이스 서비스
• Cloud상에 Database구축

AWS 홈페이지에서 RDS 데이터베이스 생성

->

터미널에서 MySQL RDS 접속

->

mysql -h "엔드포인트" -P 포트번호 -u 사용자이름 -p

->

과금될 수 있으므로 AWS RDS를 사용하지 않을 때는 중지시켜야 한다.

SQL FILE

SQL File

• SQL 쿼리를 모아놓은 파일

cd sql_ws
mysql -u root -p zerobase

• sql file 실행
  source test01.sql

• 결과 확인
  desc police_station;

• 외부에서 바로 실행
  sql_ws 에서
  mysql -u root -p zerobase < test02.sql

이후에 sql 접속하면
확인가능하다.

Database Backup

mysqldump -u root -p zerobase > zerobase.sql

vscode 에서 zerobase.sql확인

Database Restore

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

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

• AWS RDS (database-1)의 zerobase Database를 복원
  mysql > source zerobase.sql

Table Backup

• table 단위로도 백업할 수 있다.
  sql_ws % mysqldump -u username -p dbname tablename > backup.sql

• Local Database 에서 celeb Table 백업
  sql_ws % mysqldump -u root -p zerobase celeb > celeb.sql

Table Restore

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

AWS RDS의 zerobase로 연결
후
복구 : source celeb.sql

Table Schema Backup

• 데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.

• Local Database에서 snl_show Table Schema 백업
  sql_ws % mysqldump -d -u root -p zerobase snl_show > snl.sql

문제

• AWS RDS (database-1)의 zerobase 데이터베이스를 백업하세요.

mysqldump --set-gtid-purged=OFF -h "hostname" -P port번호 -u username -p databasename filename.sql

• AWS RDS (database-1)zerobase의 police_station 테이블을 SQL 파일로 백업하세요
  mysqldump --set-gtid-purged=OFF -h "hostname" -P port번호 -u username -p zerobase police_station > backup_police.sql

Python with MySQL

Install MySQL Driver

• python에서 MySQL Driver 설치
  pip install mysql-connector-python

• 설치확인
  import mysql.connector

Create Connection

• MySQL에 접속하기 위한 코드

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

• close database

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

mydb.close()

local.close()
remote.close()

Execute SQL File

• Query를 실행하기 위한 코드
  

      - 결과확인
      

• 테이블 삭제
  
  

• SQL File을 실행하기 위한 코드

remote = mysql.connector.connect(
	host = "database-1.ccgidvolmpk3.ap-northeast-2.rds.amazonaws.com",
    user = "admin",
    port = 3306,
    password = "ahrgoals",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

• SQL File내에 Query가 여러개 존재하는 경우
  
  
  여러개의 파일이 들어가있는것을 확인할 수 있다.

Fetch All

• sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)
• 참고, 검색결과를 Pandas로 읽기
  
  

Python with CSV

• csv에 있는 데이터를 Python으로 insert
  

• zerobase database에 연결

• cursor 만들기 : 읽어올 양이 많은 경우 buffer설정을 해준다.

cursor = conn.cursor(buffered=True)

• INSERT문 만들기

sql = "INSERT INTO police_station VALUES (%s,%s)"

• 데이터 입력

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

• 결과 확인
  

• 검색결과를 Pandas로 읽기
  

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

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

1. AWS RDS(database-1) zerobase에 접속

2. 2020_crime.csv 데이터 읽어오기

3. INSERT 쿼리 작성

sql = """INSERT INTO crime_status VALUES ("2020",%s,%s,%s,%s)"""
cursor = conn.cursor(buffered=True)

4. 데이터를 crime_status 테이블에 INSERT
   

5. crime_status 테이블의 데이터 조회

6. 조회한 결과를 pandas로 변환해서 확인

PRIMARY KEY, FOREIGN KEY

PRIMARY KEY (기본키)

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

primary key 생성문법

• 하나의 칼럼을 기본키로 설정하는 경우
  

• 여러개의 칼럼을 기본키로 설정하는 경우
  

• 존재하는 테이블에 하나의 칼럼을 기본키로 지정하는 경우
  

• 존재하는 테이블에 여러개의 칼럼을 기본키로 지정하는 경우
  

primary key 삭제문법

• 하나의 칼럼이 기본키로 설정된 경우
  

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

FOREIGN KEY (외래키)

• 한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

FOREIGN KEY 생성

• CREATE TABLE에서 FOREIGN KEY를 지정하는 경우
  

• CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 생략할 수 있다.
  

• TABLE이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY 지정가능
  

CONSTRAINT 확인

• 자동 생성된 CONSTRAINT를 확인하는 방법
  

FOREIGN KEY 삭제

Aggregate Functions

Aggregate Functions(집계함수)

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

COUNT : 총 갯수를 계산해주는 함수
SUM : 합계를 계산해 주는 함수
AVG : 평균을 계산해주는 함수
MIN : 가장 작은 값을 찾아주는 함수
MAX : 가장 큰 값을 찾아주는 함수
FIRST : 첫번째 결과값을 리턴하는 함수
LAST : 마지막 결과값을 리턴하는 함수

COUNT

SELECT COUNT (column)
FROM tablename
WHERE condition;

• police_station 테이블에서 데이터는 모두 몇 개?
  

• crime_status 테이블에서 경찰서는 총 몇군데?
  

• crime_type은 총 몇 가지?
  

SUM

SELECT SUM (column)
FROM tablename
WHERE condition;

• 범죄 총 발생건수는?
  

• 살인의 총 발생건수는?
  

• 중부 경찰서에서 검거된 총 범죄 건수는?
  

문제

• police_station에서 경찰서는 총 몇개이고, 각각 경찰서 이름은 무엇인지 확인하세요
  

• crime_status에서 status_type은 총 몇개이고, 각각 타입은 무엇인지 확인하세요
  

• 종로경찰서와 남대문경찰서의 강도 발생 건수의 합을 구하세요
  

• 폭력 범죄의 검거 건수의 합을 구하세요
  

AVG

SELECT AVG(column)
FROME tablename
WHERE condition;

• 평균 폭력 검거 건수는?
  

• 중부경찰서 범죄 평균 발생 건수
  

MIN

SELECT MIN(column)
FROM tablename
WHERE condition;

• 강도 발생 건수가 가장 적은 경우 몇 건?
  
• 중부경찰서에서 가장 낮은 검거 건수는?
  

MAX

SELECT MAX(column)
FROME tablename
WHERE condition;

• 살인이 가장 많이 검거된 건수는?
  

• 강남 경찰서에서 가장 많이 발생한 범죄 건수는?
  

문제

• 살인의 평균 발생 건수를 검색하고 확인
  
  

• 서초경찰서의 범죄 별 평균 검거 건수를 검색하고 확인
  

• 구로경찰서와 도봉경찰서의 평균 살인 검거 건수를 검색하고 확인
  
  

• 광진경찰서에서 가장 낮은 범죄 검거 건수를 검색하고 확인
  
  

• 성북경찰서에서 가장 낮은 범죄 발생 건수를 검색하고 확인
  

• 영등포경찰서에서 가장 높은 범죄 발생 건수를 검색하고 확인
  

• 절도 검거가 가장 많은 건수를 검색하고 확인
  

GROUP BY

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

SELECT column2, column2,...
FROM tablename
WHERE condition
GROUP BY column1, column2,...
ORDER BY column1, column2,...

• crime_status에서 경찰서별로 그룹화하여 경찰서 이름을 조회
  

• 경찰서 종류를 검색 - DISTINCT를 사용하는 경우 (ORDER BY를 사용할 수 없음)
  

• 경찰서 별로 총 발생 범죄 건수를 검색
  

• 경찰서 별로 평균 범죄 검거 건수를 검색
  

• 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
  

Having

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

• 경찰서 별로 발생한 범죄 건수의 합이 4000건보다 큰 경우를 검색
  

• 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000이상인 경우를 검색
  

문제

• 경찰서 별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인
  

• 경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색
  

• 경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색
  

• '대문'으로 끝나는 이름의 경찰서 별 범죄발생 건수의 평균이 500건 이상인 경우를 검색
  

SCALAR FUNCTIONS

SQL Subquery

About Subquery

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

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

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

• Subquery 사용시 주의

  • Subquery는 괄호로 묶어서 사용
  • 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
  • subquery에서는 order by를 사용할 수 없다.

• Subquery 종류

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

스칼라 서브쿼리(Scalar Subquery)

SELECT 절에서 사용하는 서브쿼리. 결과는 하나의 Column이어야 한다.

• 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회