MySQL 기초사용법

[MySQL이란?]
MySQL은 가장 널리 사용되고 있는 관계형 데이터베이스 관리 시스템(RDBMS)
오픈소스, 다중 사용자와 다중 스레드를 지원.
다양한 언어를 위한 API 지원
다양한 운영체제에서 사용가능, 특히 PHP와 자주 함께 사용.
상업적으로 사용할 때는 상업용 라이센스 구입 필요

[SQL 명령어는 어떻게 분류가 되는가]
1. DML-데이터 조작(manipulation) 언어
데이터를 선택, 삽입, 수정, 삭제하는데 쓰는 언어
2. DDL-데이터 정의(definition) 언어
데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할
3. DCL-데이터 컨트롤(control) 언어
사용자에게 어떤 권한을 부여하거나 빼앗을 때 쓰는 언어

SHOW DATABASES
->현재 서버에 데이터베이스를 보여달라고 하는 명령어

USE world
->world라는 데이터베이스(여기서는 샘플)를 사용하게 해달라는 명령어

SHOW TABLES
->테이블을 보여달라고 하는 명령어

SHOW TABLES STATUS
->테이블 속성값들을 보여달라는 명령어

DESCRIBE city
->city라는 테이블에 어떤 컬럼이 있고, 컬럼의 정보들이 어떤지를 보여줌
줄여서 DESC라고도 쓸 수 있음


[SELECT구문]
SELECT * FROM city;
->city 테이블에 있는 전체 data를 보여달라는 명령어.

SELECT Name, Population FROM city;
->city 테이블에서 이름과 인구를 보여달라는 명령어.
SELECT 뒤에 오는건 column정보여야 함

SELECT *
FROM city
WHERE Population >= 8000000;
->열 중 인구가 800만 이상인 데이터만 보여줘!

[사용가능한 관계연산자]
OR, AND, NOT
= <= >= < > <> != 등

ex)
SELECT *
FROM city
WHERE Population < 8000000
AND Population 7000000 ;

한국에 있는 도시들을 보려면?
SELECT *
FROM city
WHERE CountryCode = 'KOR';

SELECT *
FROM city
WHERE Population BETWEEN 7000000 AND 8000000;
-> 말그대로

SELECT *
FROM city
WHERE Name IN('Seoul', 'New York', 'Tokyo');
->이산적인 값의 선택범위를 지정할때는 IN구문을 사용

SELECT *
FROM city
WHERE CountryCode LIKE 'KO_';
-> 국가코드가 KO?인 것을 찾음. 언더바는 아무글자나 한글자라는 뜻

SELECT *
FROM city
WHERE CountryCode LIKE 'KO%';
-> 국가코드가 KO....인 것을 찾음. 퍼센트기호는 아무글자나 몇개든지 라는 뜻.

[SubQuery]
하나의 쿼리문 안에 쿼리문이 들어가있는 구조
SELECT *
FROM city
WHERE CountryCode=(SELECT CountryCode
                                          FROM city
                                          WHERE Name='Seoul');
->city테이블에서 이름이 서울인 것들의 컨트리코드를 찾는다.
그 결과와 컨트리코드가 같은 애들을 시티 테이블에서 찾아 모든 데이터를 선택한다.
이 코드에서 서브쿼리의 결과값이 여러개가 나오도록 하면 에러가 발생한다. 서브쿼리 결과값이 여러개인 상황을 컨트롤하려면 ANY 와 SOME 구문을 사용해야 한다. ANY와 SOME은 여러 개의 결과 중 하나만 만족해도 추출한다는 뜻이다. 다음 구문을 보자
SELECT *
FROM city
WHERE Population > ANY (SELECT Population
                                               FROM city
                                               WHERE District='New York');
ANY, SOME이 하나라도 만족하면 추출하는 거였다면, ALL은 모든 결과값에 대해서 만족해야 추출한다는 뜻이다.

ORDER BY로 결과가 출력되는 순서를 조절할 수 있다.
SELECT *
FROM city
ORDER BY Population DESC;
->인구 내림차순으로 시티테이블에서 데이터 보여줌
내림차순은 DESC, 오름차순은 ASC 또는 생략.
정렬된 데이터들 안에서 부차적인 정렬을 시킬수도 있음.
SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC;
->이러면 우선 국가코드 오름차순을 정렬하고, 국가코드가 같으면 인구수 내림차순으로 한다는 뜻.


DISTINCT는 중복된 것은 1개씩만 보여주면서 출력한다는 구문.
SELECT CountryCode
FROM city
->이렇게하면 같은 국가 소속인 도시들이 많기 때문에, CountryCode가 중복되는 값이 굉장히 많은 상태로 출력될것이다. 이때 다음 구문을 보자.
SELECT DISTINCT CountryCode
FROM city
->이렇게하면 중복된 결과값들은 제외하고 보여준다.

LIMIT은 출력 개수를 제한하는 기능이다. 서버의 성능을 악화시키는 악성 쿼리문을 개선할 때 흔히 사용되는 기능이다.
SELECT *
FROM CITY
ORDER BY Population DESC
LIMIT 10;
->이렇게하면 인구수 내림차순으로, 10개만! 즉 인구 상위 10개 도시의 데이터를 추출하게 된다.

GROUP BY는 그룹으로 묶어주는 역할. 예제 코드를 보자
SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
->city테이블에서 CountryCode를 Select했을 때, 겹치는 값이 굉장히 많을 것이다. 이때, 그룹바이로 컨트리코드를 선택하고 셀렉트에 MAX(Population)을 적어주면 같은 컨트리코드를 갖는 인구값중 최대값이 컨트리코드와 함께 추출된다. 이렇듯 그룹바이는 집계함수와 함께 사용된다.
-집계함수들
AVG(): 평균
MIN(): 최소값
MAX(): 최대값
COUNT(): 행의 개수
COUNT(DISTINCT): 중복 제외된 행의 개수
STDEV(): 표준편차
VARIANCE(): 분산
집계함수로 산출된 값의 별칭을 지정해줄 수있다.
SELECT CountryCode, MAX(Population) AS 'Maximum'
FROM city
GROUP BY CountryCode

HAVING은 집계 함수에 대해 조건을 제안하는 기능을 한다.
SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000
->WHERE과 비슷한 기능이지만 결과값이 집계함수일 때, HAVING을 써주어야 한다. HAVING은 반드시 GROUP BY 절 다음에 나와야 한다.

ROLLUP은 총합 또는 중간합계가 필요할 때 GROUP BY절과 함께 사용.(잘 이해 못했음)
SELECT CountryCode, SUM(Population)
FROM city
GROUP BY CountryCode WITH ROLLUP

JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현하는 기능이다. 아무렇게나 묶을 순 없고, 두 데이터에 일치하는 부분이 있어야 한다.
SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code

[MySQL의 내장함수]
LENGTH(): 문자열 길이 반환함수
CONCAT(): 문자열들을 전달받아 하나의 문자열로 반환
LOCATE(): 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 찾아서 해당 위치를 반환. 없으면 0을 반환. MySQL은 시작 인덱스가 1임에 주의!
LEFT(): 문자열의 왼쪽부터 지정한 개수만큼 문자를 반환
RIGHT(): ...
LOWER(): 문자열의 문자를 모두 소문자로 변경
UPPER(): ...
REPLACE(): 문자열에서 특정 문자열을 대체 문자열로 교체
('대상문자열', '타겟문자열', '바꿔넣을문자열')
FORMAT(): 숫자타입의 데이터를 세자리마다 쉼표를 쓰는 형식으로 반환.(대상숫자, 소숫점 아래 표시할 자릿수)
FLOOR(): 내림
CEIL(): 올림
ROUND(): 반올림
SQRT(): 양의 제곱근
POW(): 밑과 지수를 전달받아 거듭제곱 계산
EXP(), LOG(): 로그값계산
SIN(), COS(), TAN() : 삼각함수
ABS(): 절댓값을 반환
RAND():0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성.
NOW(): 현재 날짜와 시간을 반환
의미없는것같아서 필기 중단

테이블 생성하기
CREATE TABLE city2 AS SELECT * FROM city;
->select로 추출한 결과를 새로운 테이블 변수에 넣는것이다.

DB생성하기
CREATE DATABASE gydb;
USE gydb;

또는 GUI로 만드는 법도 있는데 좌측 테이블에서 우클릭, Create Table을 눌러서 속성을 선택하고 apply를 하면 기능을 코드로 변환해서 실행해줌

테이블 직접생성하기
CREATE TABLE test2 (
  id INT NOT NULL PRIMARY KEY,
  col1 INT NULL
  col2 FLOAT NULL,
  col3 VARCHAR(45) NULL
);
->이렇게 테이블을 직접 생성하는것도 가능.

테이블 수정기능
ALTER TABLE test2
ADD col4 INT NULL;
->ALTER TABLE로 테이블을 수정하겠다고 알리고
ADD로 새로운 컬럼을 추가함

ALTER TABLE test2
MODIFY col4 VARCHAR(20) NULL;
->자료형 조정

ALTER TABLE test2
DROP col4
->column 삭제

INDEX: 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용.
인덱스가 있는 테이블은 처리속도가 느려질 수 있어서 수정할 일이 별로 없고 검색이 자주 일어나는 테이블에서 사용하는것이 좋음.

CREATE INDEX Col1Idx
ON testtable (col1)
->testtable의 col1에서 인덱스를 생성함

SHOW INDEX FROM testtable
->인덱스를 보여주는 키. 어떤 인덱스가 생성되어있는지 보여줌

CREATE UNIQUE INDEX Col2Idx
ON testtable (col2)
->중복되지 않는 인덱스 생성

FULLTEXT INDEX는 문자열 검색을 할 때 빠르게 찾을 수 있도록 해주는 인덱스.

ALTER TABLE test2
DROP INDEX col3Idx;
또는
DROP INDEX col3Idx ON test2;
->인덱스 삭제

VIEW는 DB에 존재하는 가상 테이블이다. 행과 열을 가지고 있지만, 데이터를 저장하지 않고 그저 보여주는 역할만을 수행함.
한번 정의된 뷰는 변경할 수 없으며, 자신만의 인덱스를 가질 수 없고, 삽입수정에 제한이 있다.

CREATE VIEW testview AS
SELECT Col1, Col2, Col3
FROM test;
->1,2,3column을 갖는 테스트뷰 생성

ALTER VIEW로 수정, DROP VIEW로 삭제