[SQL] SQL 첫 걸음

SQL 음........................... 진짜 완전 처음 만나봐요. 다음 강의에 전공 필수 강의라서 미리 공부 좀 해서 대비를 해두려고 합니다. 할 것들은 많은데 실력은 멈춰있고 머리는 다른 생각을 하고 있네요. 생활 패턴을 돌리는 노력이 필요할 것 같아요. 오늘 포스팅도 잘 봐주셨으면 좋겠습니다.

---

SQL (Structured Quary Language) 
※ SQL은 대소문자를 문자열을 제외하고는 대소문자를 구별하지는 않지만 둘의 ASCII코드 값이 다르므로 대소문자에 대한 규칙을 정하여 사용하는 것이 좋다. 

MySQL : 가장 널리 사용되고 있는 관계형 데이터베이스 관리 시스템(RDBMS : Relational DataBase Manegement System) 
-> 오픈소스, 다중 사용자와 다중 스레드 지원 
여러 프로그래밍 언어를 위한 다양한 API 제공 
다양한 운영체제 지원, PHP와 함께 웹 개발에 자주 사용 

SQL은 DML, DDL, DCL 3가지로 분류된다. 

DML (Data Manipulation Language) 
- 데이터 조작 언어 -> 데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어 
- DML 구문이 사용되는 대상은 테이블의 행 
- DML 사용하기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 함 
- SQL 문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당 
- 트랜잭션(Transaction)이 발생하는 SQL도 DML에 속함 
- 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것 
- 취소가능 

DDL (Data Definition Language) 
- 데이터 정의 언어 -> 데이터베이스, 테이블, 뷰 ,인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할 
- CREATE,DROP,ALTER 구문 
- DDL은 트랜잭션 발생시키지 않음 
- ROLLBACK이나 COMMIT 사용 불가 
- DDL문은 실행 즉시 MySQL에 적용 

DCL (Data Control Language) 
- 데이터 제어 언어 -> 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문 
- GRANT/REVOKE 

SHOW DATABASES : 현재 서버에 어떤 DB가 있는지 보기 
->실행 번개버튼 or Ctrl + Enter 

USE : 사용할 데이터베이스 지정 
- 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행 
-> USE database_name  // ex) use world : world라는 이름을 가진 데이터베이스를 사용하게 해줘! 라는 의미 
- Workbench에서 직접 선택해 사용가능 
- [Navigator] -> [SCHEMAS] -> 데이터베이스 선택 

SHOW TABLES : 데이터베이스의 테이블 이름 보기 / table에 s 붙음 

SHOW TABLE STATUS : 테이블 정보 조회 / table에 s 안붙음 

DESCRIBE(DESC) : 테이블에 무슨 열이 있는지 확인 
ex) DESCRIBE city / DESC city

 

SELECT  :요구하는 데이터를 가져오는 구문

- 일반적으로 가장 많이 사용되는 구문

- 데이터베이스 내 테이블에서 원하는 정보를 추출

- SELECT의 구문 형식

SELECT select_expr
    [FROM table_references] [WHERE where_condition]
    [GROUP BY {col_name | expr | position}]
    [HAVING where_conditon]
    [ORDER BY{col_name | expr | position}]

- SELECT 열이름

테이블에서 필요로 하는 열만 가져오기 가능

여러 개의 열을 가져오고 싶을 때는 콤마로 구분

열 이름의 순서는 출력하고 싶은 순서대로 배열 가능

- SELECT FROM WHERE

- 조회하는 결과에 특정한 조건으로 원하는 데이터만 보고 싶을 때 사용

- 조건이 없을 경우 테이블의 크기가 클수록 찾는 시간과 노력이 증가 ->FROM만 사용한 경우

SELECT 필드이름 FROM 테이블이름 WHERE 조건식;

- BETWEEN

- 데이터가 숫자로 구성되어 있어 연속적인 값은 BETWEEN ~ AND ~ 사용가능

ex) WHERE Population BETWEEN 1000000 AND 7000000

-> 인구수 100만에서 700만 사이의 값들 출력

 

- IN

- 이산적인(Discrete)값의 조건에서는 IN() 사용 가능 / 이산적 : 서로 단절되는 연속되지 않는 값.

ex) WHERE Name IN('Seoul', 'Tokyo', 'New York')

-> 해당 이름에 해당하는 값 출력

 

- LIKE

- 문자열의 내용 검색하기 위해 LIKE 연산자 사용

- 문자 뒤에 % - 무엇이든(%) 허용

ex ) WHERE CountryCode LIKE('K%')  -> K로 시작하는 나라코드에 해당하는 값 출력

- 한 글자와 매치하기 위해서는 '_' 사용

ex ) WHERE CountryCode LIKE('KO_') -> KO로 시작하는 나라코드에 해당하는 값 출력

 

- Sub Query (서브 쿼리)

- 쿼리문 안에 또 쿼리문이 들어 있는 것

- 서브 쿼리의 결과가 둘 이상이 되면 에러 발생

SELECT *
FROM city
WHERE CountryCode = (    SELECT CountryCode
                       FROM city
                       WHERE NAME = 'Seoul'    );

-> 괄호 안의 SELECT만 실행하면 KOR을 출력하지만 전체를 실행하면 국가코드가 KOR인 도시들을 모두 출력한다.

 

- ANY

- 서브쿼리의 여러 개의 결과 중 한 가지만 만족해도 가능

- SOME은 ANY와 동일한 의미로 사용

- =ANY 구문은 IN과 동일한 의미

SELECT *
FROM city
WHERE Population > ANY (    SELECT Population
                            FROM city
                            WHERE District = 'New York'    );

-> 서브 쿼리만 실행하면 뉴욕주안에 있는 도시들의 인구수가 나오는데 전체 쿼리문을 실행하면 서브 쿼리 실행 시 나오는 값 모두가 아닌 그 중 한가지만 만족하면 조건이 충족되어 그 값보다 인구수가 더 큰 도시들이 출력된다.

 

- ALL

- 서브 쿼리의 여러개의 결과를 모두 만족 시켜야 함

SELECT *
FROM city
WHERE Population > ALL (    SELECT Population
                            FROM city
                            WHERE District = 'New York'    );

-> 위에서 사용한 코드에서 ANY를 ALL로 바꾸면 뉴욕주안에 있는 모든 도시들의 인구 수보다 큰 인구 수를 가진 도시들만 출력한다.

 

- ORDER BY

- 결과가 출력되는 순서를 조절하는 구문

- 기본적으로 오름차순(ASCENDING) 정렬

- 내림차순(DESCENDING)으로 정렬 (열 이름 뒤에 DESC 적어줄 것)

- ASC(오름차순)는 default이므로 생략 가능

- ORDER BY 구문을 혼합해 사용하는 구문도 가능

SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC

-> CountryCode는 오름차순으로 정렬되고, 같은 CountryCode안에 있는 값들은 내림차순으로 정렬된다.

 

- DISTINCT

- 중복된 것은 1개씩만 보여주면서 출력

- 테이블의 크기가 클수록 효율적

SELECT DISTINCT CountryCode
FROM city;

- LIMIT

- 출력 개수를 제한

- 상위의 N개만 출력하는'LIMIT N' 구문

-> 하지만 워크벤치에서는 자체적으로 LIMIT를 설정하는 기능이 있기 때문에 워크벤치가 알아서 제한을 둔다.

- 서버의 처리량을 많이 사용해 서버의 전반적인 성능을 나쁘게 하는 악성 쿼리문을 개선할 때 사용

SELECT *
FROM city
ORDER BY Population DESC
LIMIT 10

- GROUP BY

- 그룹으로 묶어주는 역할

- 집계 함수(Aggregate Function)를 함께 사용

AVG()	평균
MIN()	최소값
MAX()	최대값
COUNT()	행의 개수
COUNT(DISTINCT)	중복 제외된 행의 개수
STDEV()	표준 편차
VARIANCE()	분산

ex)

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 8000000

-> 각자 CountryCode에서 가장 큰 인구수를 가진 도시의 값들 중에서 인구수가 800만이 초과되는 값들만 출력된다.

 

- ROLLUP

- 총합 또는 중간합계가 필요할 경우 사용

- 순열(Permutation)의 형태로 모든 집계결과를 보여준다.

- GROUP BY절과 함꼐 WITH ROLLUP문 사용

ex)

SELECT CountryCode, Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP

출력

ROLLUP 출력

출력을 보면 각 CountryCode에 해당하는 city들의 인구수가 나오고 마지막에는 그 CountryCode에 속하는 City들의 인구 의 합을 출력한다.

 

- JOIN

- 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현

ex)

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
/*city의 CountryCode와 country의 Code가 같은 것들을 조합하라는 뜻*/
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode
/*city의 CountryCode와 country의 Code가 같은 것들 + contrylanguage가 같은 것들까지 조합하라는 뜻*/

- MySQL 내장합수

- 사용자의 편의를 위해 다양한 기능의 내장 함수를 미리 정의하여 제공

- 대표적인 내장 함수의 종류

문자열함수

수학함수

날짜와 시간 함수

- LENGTH

- 전달받은 문자열의 길이를 반환

코드

SELECT LENGTH('asdadadasdsadsa')

SELECT CONCAT('I ', 'love ', 'you.')

출력

CONCAT 출력

 

- LOCATE()

- 문자열 내에서 찾는 문자열이 처음으로 나타나는 위치를 찾아서 해당 위치를 반환

- 찾는 문자열이 문자열 내에 존재하지 않으면 0을 반환

- MySQL에서는 문자열의 시작 인덱스를 1부터 계산

코드

SELECT LOCATE('abc', 'asdasdfabcasdasdas')

출력

LOCATE 출력

 

- LEFT(), RIGHT()

- LEFT() : 문자열의 왼쪽부터 지정한 개수만큼의 문자를 반환

- RIGHT() : 문자열의 오른쪽부터 지정한 개수만큼의 문자를 반환

 

- LOWER,() UPPER()

- LOWER() : 문자열의 문자를 모두 소문자로 변경

- UPPER()  : 문자열의 문자를 모두 대문자로 변경

 

- REPLACE()

- 문자열에서 특정 문자열을 대체 문자열로 교체

 

- TRIM()

- 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거

- TRIM() 함수에서 사용할 수 있는 지정자

BOTH	전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거(기본 설정)
LEADING	전달받은 문자열 앞에 존재하는 특정 문자를 제거
TRAILING	전달받은 문자열 뒤에 존재하는 특정문자를 제거

- 만약 지정자를 명시하지 않으면, 자동으로 BOTH로 설정

- 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거

 

- FORMAT()

- 숫자 타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환

- 반환되는 데이터의 형식은 문자열 타입

- 두 번째 인수는 반올림할 소수 부분의 자릿수

 

- FLOOR(), CEIL(), ROUND()

- FLOOR() : 내립

- CEIL() : 올림

- ROUND() : 반올림

 

- SQRT(), POW(), EXP(), LOG()

- SQRT() : 양의 제곱근(루트)

- POW() : 첫 번째 인수로는 밑수를 전달하고, 두 번째 인수로는 지수를 전달하여 거듭제곱 계산

- EXP() : 인수로 지수를 전달받아 e의 거듭제곱을 계산

- LOG() : 자연로그 값을 계산

 

- SIN(), COS(), TAN()

- SIN() : 사인값 반환

- COS() : 코사인값 반환

- TAN() : 탄젠트값 반환

 

- ABS(), RAND()

- ABS() : 절대값을 반환- RAND() : 0.0보다 크거나 같고 1.0보다 작은 하나의 실수를 무작위로 생성

 

- NOW(), CURDATE(), CURTIME()

- NOW() : 현재 날짜와 시간을 반환,  반환되는 값은 'YYYY-MM -DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 형태로 반환

- CURDATE() : 현재 날짜를 반환, 이때 반환되는 값은 'YYYY-MM-DD' 또는 YYYYMMDD 형태로 반환- CURTIME() : 현재 시각을 반환, 이때 반환되는 값은 ' HH:MM:SS' 또는 HHMMSS 형태로 반환

 

- DATE(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

- DATE() : 전달받은 값에 해당하는 날짜 정보를 반환

- MONTH() : 월에 해당하는 값을 반환하며, 0부터 12사이의 값을 가짐

- DAY() : 일에 해당하는 값을 반환하며, 0부터 31의 값을 가짐- HOUR() : 시간에 해당하는 값을 반환하며, 0부터 23의 값을 가짐- MINUTE() : 분에 해당하는 값을 반환하며, 0부터 59의 값을 가짐- SECOND() : 초에 해당하는 값을 반환하며, 0부터 59의 값을 가짐

 

- MONTHNAME(), DAYNAME()

- MONTHNAME() : 월에 해당하는 이름을 반환- DAYNAME() : 일에 해당하는 이름을 반환

 

- DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

- DAYOFWEEK() : 일자가 해당 주에서 몇 번째 날인지를 반환, 1부터 7사이의 값을 반환(일요일 = 1, 토요일 = 7)

- DAYOFMONTH() : 일자가 해당 월에서 몇 번째 날인지를 반환, 0부터 31까지의 값을 반환

- DAYOFYEAR() : 일자가 해당 연도에서 몇 번째 날인지를 반환, 1부터 366 사이의 값을 반환

 

- DATE_ FORMAT()

- 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환 (자바의 형식지정자와 비슷한듯!)

 

---

SQL 고급

 

- CREATE TABLE AS SELECT

ex ) CREATE TABLE city2 AS SELECT * FROM city; -> city의 내용이 city2에 복사됨

 

- CREATE DATABASE

- CREATE DATABASE 문은 새로운 데이터 베이스를 생성

- USE 문으로 새 데이터베이스를 사용

 

- CREATE TABLE

- GUI로 생성가능(SCHEMAS의 Tables 우클릭 -> Create Table 클릭 -> Column Name, Datatype 입력)

- 데이터 타입 -> 수안님 유튜브 49:28초에 나오는 링크 참조!

- 입력으로도 생성가능

ex) 

CREATE TABLE test2 (
    id INT NOT NULL PRIMARY KEY,
    col1 INT NULL,
    col2 FLOAT NULL,
    col3 VARCHAR(45) NULL
);
 

 

- ALTER TABLE

- ALTER TABLE 문과 함께 ADD 문을 사용하면, 테이블에 컬럼을 추가할 수 있음

- ALTER TABLE 문과 함께 MODIFY 문을 사용하면, 테이블의 컬럼 타입을 변경할 수 있음

- ALTER TABLE 문과 함께 DROP 문을 사용하면, 테이블에 컬럼을 제거할 수 있음

ex)

ALTER TABLE test2
ADD col4 INT NULL; /*추가*/
 
ALTER TABLE test2
MODIFY col4 VARCHAR(20) NULL; /*수정*/
 
ALTER TABLE test2
DROP col4; /*삭제*/
 

 

- 인덱스(INDEX)

- 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용

- 일반적으로 데이터를 검색할 때 순서대로 테이블 전체를 검색하므로 데이터가 많으면 많을수록 탐색하는 시간이 늘어남

- 검색과 질의를 할 때 테이블 전체를 읽지 않기 때문에 빠름

- 설정된 컬럼 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 함

- 인덱스가 있는 테이블은 처리 속도가 느려질 수 있으므로 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 좋음

 

- CREATE INDEX

- CREATE INDEX 문을 사용하여 인덱스를 생성

ex) 

CREATE INDEX Col1Idx
ON test(col1)
/*컬럼이름이 col1인 컬럼에 Col1Idx라는 인덱스 생성*/

 

- SHOW INDEX

- 인덱스 정보보기

 

- CREATE UNIQUE INDEX

- 중복 값을 허용하지 않는 인덱스

-> CREATE INDEX에서 CREATE와 INDEX사이에  UNIQUE를 넣어주면 된다.

 

- FULLTEXT INDEX

- FULLTEXT INDEX는 일반적인 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색

ex)

ALTER TABLE test
ADD FULLTEXT Col2Idx(col2);
/*FULLTEXT INDEX Col2Idx를 col2라는 컬럼에 생성*/
SHOW INDEX FROM test;

FULLTEXT INDEX 출력

출력을 보면  Index_type이 FULLTEXT인것을 확인할 수 있다.

- 문자열 검색이 일어났을 때 이 테이블이 문자열검색을 빠르게 할 수 있게 하는 인덱스이다.

 

- INDEX 삭제

- 방법 1. ALTER 문을 사용하여 테이블에 추가된 인덱스 삭제

/*ALTER를 사용해서 인덱스를 삭제하는 방법*/
ALTER TABLE test
DROP INDEX Col2idx;

/*DROP INDEX 사용*/
DROP INDEX Col2Idx ON test
 
 

 

- VIEW

- VIEW는 데이터베이스에 존재하는 일종의 가상 테이블

- 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하진 않음

- MySQL에서 VIEW는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만 수행

+ (다른 DBMS에는 수정, 삭제가 가능한 것도 있다)

- VIEW를 사용하면 여러 테이블이나 VIEW를 하나의 테이블처럼 볼 수 있음

VIEW 장점	특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줄 수 있음
	복잡한 쿼리를 단순화해서 사용
	쿼리 재사용 가능
VIEW 단점	한번 정의된 VIEW는 변경할 수 없음
	삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐
	자신만의 인덱스를 가질 수 없음

 

- CREATE VIEW

- CREATE VIEW 문을 이용하여 뷰 생성

ex)

CREATE VIEW testView AS
SELECT Col1, Col2
FROM test;
/*test라는 TABLE에 Co11, Col2를 가지는 VIEW인 testView를 만들어라*/

출력

CREATE VIEW 출력

 

- ALTER VIEW

- ALTER 문을 이용하여 뷰를 수정

ex)

ALTER VIEW testView AS
SELECT Col1, Col2, Col3
FROM test;

+) 추가적인 테이블 생성과 수정에 대한 내용은 유튜브 이수안컴퓨터연구소 -> 47분부터보자. 실습으로 하는게 훨씬 중요할 것 같아서 포스팅 x