210105 TUE 수업 - MySQL 문법(1)

* CRUD (Create, Read, Update, Delete)

 

[ Database ]

 

* About Database

- DB vs. DBMS 

: DB는 데이터를 통합하여 관리하는 데이터의 집합 자체

: DBMS는 가공되지 않은 굉장히 많은 정보들을 효율적으로 원하는 정보만 가지고 올 수 있게 해주는 미들웨어 시스템

: 액셀의 행과 열인 시트는 DB의 테이블

 

* RDBMS
- Relational ~ : 데이터 테이블 사이에 키값으로 관계 O --> 따라서 시스템이 복잡

===> 미리 데이터의 모양을 지정, 따라서 데이터를 가져올 때 빠르다!

- Oracle, Mysql, ....

 

* NoSQL

- 데이터 테이블 사이에 관계 X --> 복잡성은 줄고, 많은 데이터 저장 가능

===> 모양이 없어 데이터를 빠르게 넣을 수 있으나, 가져올 때는 느리다!

===> insert를 많이 하는 경우

- 문자열 데이터가 그대로 저장되는 것

- MongoDB

- 빅데이터는 사람의 행동 하나하나를 저장해야하기 때문에 insert가 많아....

- 2013년부터 급상승

 

*Database Ranking

- Oracle DB

: 점유율은 1%이나, ...
: 가격이 굉장히 비싸서 금융사나 통신사에서 많이 씀

: 성능이 굉장히 뛰어남, 데이터 가져올 때 그걸 학습해서 스스로 성능 향상

- MySQL

: Oracle하고 쿼리문이 굉장히 비슷

: 가장 많이 사용

- MSSQL

- PostgreSQL
==>이 네 가지 DBMS가 RDBMS, 오래전부터 써왔기 때문에 과거에 많은 신뢰성을 받음

- MongoDB

 

* About MySQL
- 오픈소스(코드공개 - 무료이나 장점으로는 개발자들이 참여해서 서비스 향상노력)

(cf. 오라클 : 닫힌 소스)

- 다중 사용자와 다중 스레드 지원

: (다중사용자) A라는 사용자와 B라는 사용자가 함께 접속하여 사용가능

: (다중스레드) 내가 A라는 일을 하면서 B라는 일을 동시에 처리

- 다양한 운영체제에 다양한 프로그래밍 언어 지원

: (OS) 컴퓨터의 물리적 장비에 내리는 명령에 따라 자원을 효율적으로 배분을 해주는 역할

: OS에 따라 프로그램이 따로따로 개발이 되어야하는데, MySQL은 macOS, 리눅스 등에서도 사용가능

- 표준SQL

- (라이센스)어떤 장비나 MySQL을 넣어 패키지 형태로 판매를 하면 라이센스 비용을 지불해야하나, 배포시 소스 공개시 무료

 

* Server & Client Architecture

- Client가 브라우져를 통해 서버에 데이터를 요청(url에 담겨있는 정보)

- Web Server에서 html 문자열 코드로 Server response함

- 브라우져는 이 문자열 코드를 다운로드 받고, 이를 해석해서 Client에게 화면결과로 보여줌

==> 데이터베이스는 이 웹서버를 데이터베이스서버, 브라우져는 sql tool(work bench), sql쿼리문에 따라 결과 출력하는 구조

- 지금은 우리가 클라이언트와 서버 역할을 동시에 하나, 원래는 서버를 따로 둠

 

* RDBMS
- 데이터 분류, 정렬, 탐색속도 빠름

- 오래 사용된 만큼 신뢰성이 높음

- 스키마수정이 어려움

: 처음 항목들에 대해 정의를 함, 즉 테이블에 대한 구조가 스키마, 이 스키마를 처음에 설정하고 데이터를 넣어줘야 함

- My SQL Server -> Database(데이터들의 집합) -> Table(하나의 시트같은 것) -> Row(한줄한줄의 데이터) -> Value

: Table과 Table 사이에 관계를 가지고 데이터를 출력할 수 있으나 DB사이에는 X

cf. NoSQL에서는 table의 개념이 collection인데 이것들은 서로 관계를 가지고 있지 않아 합쳐서 데이터 출력 불가

 

- 행(row), 튜플, 레코드 <-> 열(column), 필드, 속성 (cf. NoSQL에선 이 용어 안쓰고 다른 용어 씀)

- 하나의 셀은 value

- Table -> Stroage Engine

: MylSAM - select가 빠름

: InnoDB - transaction 지원

- Key : row데이터 하나하나를 식별할 수 있는 식별자

- Relationship :  1:1(고객:고객의 주소값), 1:n (고객:상품 여러개), n:n

(cf. NoSQL에는 X)

 

- Schema : 데이터베이스의 구조 디자인

: 테이블 안에 컬럼 하나하나, 테이블들간 관계를 나타내는 관계

- Query : DBMS에 명령을 내리는 문법

 

* NoSQL

- 확장하기 좋음

- 스키마, Join 없음

- JSON 포멧 사용

- selecte는 RDMBS보다 느리지만, insert가 빨라 대용량 DB에 많이 사용

- 트랜잭션 지원X (동시수정에 대한 신뢰성 지원X)

 

* MySQL 사용하기

- workbench가 MySQL서버에 접속

 

* 데이터 베이스 모델링 (Model - Add Diagram)

- 테이블의 구조를 미리 계획해서 작성

- 개념적 모델링 -> 논리적 모델링 -> 물리적 모델링 절차로 실행

- EER 다이어그램 작성

- 물리적 모델링시, 컬럼별 데이터의 모양 지정(INT, VARCHAR, DATETIME 등)

===> why? 지정해주지 않으면 불확실하므로 저장공간을 최대한 크게 잡을 수 밖에 없으므로 비효율적

- Key : 컬럼 하나를 구별할 수 있는 유니크한 식별자

 

*DataBase -> forward-Engineer

- 데이터베이스 만들기 위한 옵션 선택

- 실행쿼리에서 visible을 지워준다 (5.8버전 이상에서 사용가능한데, 안그럼 신택스 오류)

 

* 쿼리실행

- use 데이터베이스; //데이터베이스 선택하기

- show tables;

- desc items; //description

 

* Reverse Engineer Database

- 데이터베이스 스키마 모델링 보여줌

- view : 가상의 테이블 정도로 생각하면 됨...

 

* DML : DB안의 데이터 조작시 사용

- 데이터 조작어

- SELECT, INSERT, UPDATE, DELETE 예약어 사용

 

* DDL : 데이터가 담긴 테이블이나 데이터베이스 조작시 사용
- 데이터 정의어

- 데이터베이스를 만들거나, 인코딩을 수정하거나, 테이블을 만들거나, 가상의 뷰같은 것 만들거나

- CREATE, DROP, ALTER, TRUNCATE...

 

* DCL

- 데이터 제어어

- GRUNT, REVOKE, DENY

- 서버에 여러명의 사용자가 접속을 할 수 있는데, ex) 1번 사용자에게는 1번 데이터베이스 사용권한만, 2번 사용자에게는 1,2번 모두의 데이터베이스 사용권한을 주는 등...

 

* 인코딩, 디코딩

- 사람 : 쿼리(코드)

- 컴퓨터 : 전류가 흐르면 1, 그렇지 않으면 0...

ex) 사람 : a - 컴퓨터 : 0001

===> 이렇게 바꿔주는 문법이 필요하며, 이렇게 변환해주는 과정을 [인코딩]이라 한다.

===> 반대 과정은 [디코딩]이다.

- 인코딩 방식 : ascii, euc-kr, utf-8

- 디코딩시 같은 방식으로 해줘야 함

- euc-kr은 ascii에서 한글 +

- utf-8은 전세계 모든 나라 언어를 모두 표현할 수 있다. cf. utf-8mb4 : 이모티콘 +

- 그럼 무조건 utf-8 쓰면 되는 거 아닌가? 왜 굳이 나누어져 있는가?

==> ascii에서 문자 하나만 쓰고자 하면 4byte, euc-kr은 8byte, utf-8은 16byte를 써야하므로 컴퓨터사양이 너무 안좋으면 ascii를 쓰면 됨. 그러나 하드웨어가 가격대비 성능이 너무 좋아져서 utf-8을 무조건 쓴다고 생각하면 되겠다.

 

* 쿼리문 연습

 

// 컬럼명 사용시 대문자와 소문자의 구분이 없다.

// text 데이터 사용시에도 대소문자 구분 없다.

// 코드 작성시 SELECT * FROM sth과 같이 예약어는 대문자로 써주는 것이 정석이다.

// 직접 커멘드라인 띄워서 쓸 땐 세미클론 써야하므로 늘 쓰도록 한다 (workbench가 따로 붙여주는 환경이지만)

 

use ssac;
show tables;
desc items;

use world;
# SELETE FROM : 데이터를 조회
# CRUD 중 R(read)에 해당하는 기능을 수행
select * from world.country;
#여기서 world를 생략하려면 앞에 use world;를 해주어야 한다.

select database();

select code, name
from country; 

select database();

select * from countrylanguage;
select language, IsOfficial from countrylanguage;

select * from city;
select id, name, population from city;

# alias - JOIN이나 SubQuery에서 유용
SELECT code AS country_code, name AS country_name
FROM country;

#데이터 베이스, 테이블, 컬럼 확인
show databases;
show tables;
desc city;	#모양확인

#MySQL 주석
/*
SELECT *
FROM city;
*/
# 멀티라인 주석달기
# 여기서 #은 workbench에서의 주석임



# 연산자 : 산술, 비교, 논리
# 산술 : +, -, *, /, %(나머지), DIV(몫)
SELECT 10 *3 FROM dual;
SELECT 3 % 3;

#인도밀도(인구수/국가면적) 출력하기
SELECT code, name, surfacearea, population
	, population / surfacearea AS pps
FROM country;

#국가별 1인당 GNP 출력하기
SELECT * FROM country;
SELECT code, name, gnp, population
,	population/GNP AS ppg
FROM country; 

#비교연산자
SELECT 1 = 1; -- TRUE이므로 1 출력
SELECT 1 != 1; -- FALSE이므로 0 출력
SELECT 1 > 1; -- FALSE - 0

#비교연산자 예제
SELECT code, name, continent, continent = "asia" AS is_asia
FROM country;

#비교연산자 예제2
SELECT * FROM country;
SELECT code, name, indepYear, indepYear >= 1900 AS indepAfter1900
FROM country;

#논리 연산자 : and, or
SELECT (1 = 1) AND (2 != 2); -- false - 0
SELECT (1 = 1 ) OR (2 != 2); -- true - 
# world 데이터 베이스에서 국가코드, 국가이름, 인구수, 기대수명, 인구수가 5천만 이상이고, 
#기대수명이 70세 이상이면 1(True)을 출력하는 쿼리를 작성하세요.
SELECT code, name, population, lifeexpectancy
	, (population >= 50000000) AND (lifeexpectancy >= 70) AS pal
FROM country;

#country 테이블에서 국가코드, 국가이름, 대륙을 출력
#아시아국가 이거나 유럽국가인 경우 1(True)을 출력 컬럼을 추가
SELECT * FROM country;
SELECT code, name, continent
	, (continent = "asia") OR (continent = "europe") AS AOE
FROM country;
-- ()를 안붙여도 같은 결과가 나오는데, 비교연산자가 논리연산자보다 우선순위이므로
-- 연산자 우선순위에 의해 비교연산자가 먼저 연산되기 때문임
-- 같은 종류의 연산자의 경우 왼쪽->오른쪽으로 순서대로 연산

# WHERE - 특정한 조건으로 데이터를 필터링해서 검색하는데 사용하는 문법
# 조건식에 비교연산, 논리연산 등을 사용

#비교연산
#인구가 1억이상인 국가를 출력
SELECT code, name, population
FROM country
WHERE population >= 100000000;

#논리연산
#인구가 7000만 ~ 1억인 국가를 출력
SELECT code, name, population
FROM country
WHERE population >=7000000 AND population <= 100000000;

# BETWEEN A and B : 이퀄연산자가 포함
SELECT code, name, population
FROM country
WHERE population BETWEEN 700 * 10000 AND 1000 * 100000;

#아시아와 아프리카 대륙의 국가 데이터를 출력
SELECT code, name, continent, population
FROM country
WHERE (continent = "asia") OR (continent = "africa");

# IN, NOT IN
SELECT code, name, continent, population
FROM country
-- WHERE continent in ("asia", "africa");
WHERE continent not in ("asia", "africa");	-- 아시아, 아프리카 제외

#비교, 논리 연산자로 같은 결과 내오기
SELECT code, name, continent, population
FROM country
WHERE (continent != "asia") AND (continent != "africa"); -- 가독성 위해 ()쳐주기

# LIKE : 특정 문자열이 포함된 데이터를 출력
SELECT code, name, population
FROM country
-- WHERE code like "K%";	-- 제일 앞 K이 오고, 어떤 문자열이 온다
-- WHERE code like "%K";	-- 앞에 어떤 문자열이 오고, K가 온다
WHERE code like "%K%"; -- K가 어디 들어있든 상관없이 K가 들어있는 것 모두 출력 

# ORDER BY : 정렬
# 오름차순으로 인구수 순으로 국가데이터를 출력
SELECT code, name, population
FROM country
-- ORDER BY population ASC;	-- 오름차순 정렬시 ASC생략 가능
ORDER BY population DESC;	-- 내림차순

# 인구수가 7천만 ~ 1억 사이의 국가를 출력
# 인구수 순으로 내림차순해서 출력
SELECT code, name, population
FROM country
WHERE population BETWEEN 7000 * 10000 AND 10000 * 10000
ORDER BY population DESC;
-- 구문 쓸 때 순서지켜야 한다!

# 여러 컬럼에 sorting 조건을 주어서 출력하는 방법
SELECT  countrycode, name, population
FROM city
ORDER BY countrycode DESC, population ASC;

# LIMIT : 조회하는 데이터의 수를 제한할 때 사용
# 인구가 0을 초과하는 국가들 중에서
# 국가의 크기가 가장 큰 국가 상위 5개 국가를 출력하세요.
SELECT code, name, surfacearea, population
FROM country
WHERE population > 0
ORDER BY surfacearea DESC
LIMIT 5;

# 6위에서 8위까지 출력하기 
SELECT code, name, surfacearea, population
FROM country
WHERE population > 0
ORDER BY surfacearea DESC
-- LIMIT 5, 3;	 #5개를 스킵하고 그 다음 3개의 데이터를 출력
LIMIT 3 OFFSET 5;

#DISTINCT : 중복 데이터를 제거해서 출력하는 예약어
#대륙이름을 출력
SELECT DISTINCT continent, region
FROM country;

#Quiz 1. 한국 ( 국가코드 : KOR ) 도시중 인구가 100만이 넘는 도시를 조회하여 인구수 순으로
#내림차순으로 출력하세요.
# 출력 컬럼 : 도시이름(name), 도시 인구수(population)
SELECT name, population
FROM city
WHERE countrycode = "KOR" AND population >= 1000000
ORDER BY population DESC;

#Quiz 2. 도시 인구가 800만 ~ 1000만 사이인 도시의 데이터를 국가코드 순으로 오름차순 하세요.
SELECT countrycode, name
		, population -- 코드 수정이 쉽게 이런식으로 작성
FROM city
WHERE population BETWEEN 800 * 10000 AND 1000 * 10000
ORDER BY countrycode ASC;

#Quiz 3. 1940 ~ 1950년도 사이에 독립한 국가들중 GNP가 10만이 넘는 국가를 GNP의 내림차순으로 출력하세요.
# 출력 컬럼 : 국가코드(code), 국가이름(name), 대륙(continent), GNP(gnp)
SELECT code, name, continent, GNP
FROM country
WHERE (indepyear BETWEEN 1940 AND 1950)
	AND (GNP >= 10 * 10000)
ORDER BY GNP DESC;

#Quiz 4. 스페인어(Spanish), 영어(English), 한국어(Korean) 중에 95% 이상 사용하는 국가코드, 언어, 비율을 출력하세요.
# 출력 컬럼 : 국가코드(countrycode), 언어(language), 비율(percentage)
SELECT countrycode, language, percentage
FROM countrylanguage
WHERE language IN ('Spanish', 'English','Korea')
	AND Percentage >= 95
ORDER BY percentage DESC;

# + 문제 GBR 국가의 인구가 120만일 때 언어별로 사용하는 인구를 출력
SELECT countrycode, language, percentage
		, round(120 *10000 * (percentage /100))
        AS population
FROM countrylanguage 
WHERE countrycode = "GBR";

#Quiz 5. 국가 코드가 "K"로 시작하는 국가 중에 기대수명(lifeexpectancy)이 70세 이상인 국가를 기대수명의 내림차순 순으로 출력하세요.
# 출력 컬럼 : 국가코드(code), 국가이름(name), 대륙(continent), 기대수명(lifeexpectancy)
SELECT code, name, continent, lifeexpectancy
FROM country
WHERE code LIKE "K%" AND lifeexpectancy >= 70
ORDER BY lifeexpectancy DESC;

#USA 미국 국가의 도시 중에서 인구가 가장 많은 도시를 찾아서 : 인구수
#해당 도시에서 사용하는 언어의 인구수를 출력
#(조건 : 도시의 언어사용 비율은 국가의 언어사용 비율과 동일함)
SELECT countrycode, name, population
FROM city WHERE countrycode = 'USA'
ORDER BY population DESC
LIMIT 1;
#뉴욕의 인구 8008278
SELECT language, percentage
	, round(8008278 * (percentage / 100)) AS population
FROM countrylanguage
WHERE countrycode = 'USA'
ORDER BY percentage DESC;



# sakila 데이터 베이스로 이동 sql> USE sakila;
use sakila;
select database();
#Quiz 6. film_text 테이블에서 title이 ICE가 들어가고 description에 Drama가 들어간 데이터를 출력하세요.
# 출력 컬럼 : 필름아이디(film_id), 제목(title), 설명(description)
SELECT film_id, title, description
FROM film_text
WHERE title LIKE "%ICE%" AND description LIKE "%DRAMA%";

#Quiz 7. actor 테이블에서 이름(first_name)의 가장 앞글자가 "A", 성(last_name)의 가장 마지막 글자가 "N"으로 끝나는 배우의 데이터를 출력하세요.
# 출력 컬럼 : 배우아이디(actor_id), 이름(first_name), 성(last_name)
SELECT actor_id, first_name, last_name
FROM actor
WHERE first_name LIKE 'A%' AND last_name LIKE '%N'; 
#Quiz 8. film 테이블에서 rating이 "R" 등급인 film 데이터를 상영시간(length)이 가장 긴 상 위 10개의 film을 상영시간의 내림차순순으로 출력하세요.
# 출력 컬럼 : 필름아이디(film_id), 필름제목(title), 필름내용(description), 대여기간(rental_duration), 렌탈비율 (rental_rate), 상영시간(length), 등급(rating)
SELECT film_id, title, description, rental_duration
		, rental_rate, length, rating
FROM film
WHERE rating = "R"
ORDER BY length DESC
LIMIT 10;
# +문제 film 테이블에서 어떤 등급이 있는 지 출력하세요.
SELECT DISTINCT rating
FROM film;

#Quiz 9. 상영시간(length)이 60분 ~ 120분인 필름 데이터에서 영화설명(description)에 
#robot 들어있는 영화를 상영시간(length)이 짧은순으로 오름차순하여 정렬하고, 11위에서 13위까지의 영화를 출력하세요.
# 출력 컬럼 : 필름아이디(film_id), 필름제목(title), 필름내용(description), 상영시간(length)
SELECT film_id, title, description, length 
FROM film
WHERE (length BETWEEN 60 AND 120) AND (description LIKE "%robot%")
ORDER BY length
LIMIT 3, 10; -- LIMIT 3 OFFSET 10

#Quiz 10. film_list view에서 카테고리(category)가 sci-fi, anmation, drama가 아니고 
#배우(actors) 가 "ed chase" 또는 "kevin bloom" 이 포함된 영화리스트에서 상영시간이 긴 순서대로 5개의 영화 리스트를 출력하세요.
# * view는 테이블이라고 생각하면 됩니다. 3일차에 학습합니다.
SELECT title, description, category, length, actors FROM film_list
WHERE category NOT IN ('sci-fi', 'animation', 'drama')
	AND (actors LIKE '%ed chase%' 
		OR actors LIKE 'kevin bloom')
ORDER BY length DESC 
LIMIT 5;

SELECT * FROM film_list;
# 출력 컬럼 : 제목(title), 설명(description), 카테고리(category), 상영시간(length), 배우(actors)