SQLite로 배우는 SQL 표준의 기초

SQL (Structured Query Language)

구조적 질의 언어,

데이터베이스(DataBase)에서 데이터의 효율적인 관리를 하기 위해 만든 언어이다.

각종 서버는 거의 대부분 SQL 기반 DB를 사용한다.

 

• 데이터를 직접 다루는 언어이다 보니 보안에 매우 신중하고, 가장 보수적인 문법 규칙을 고수한다.
• 신중한 입력을 요구받다 보니 자연어에 가까운 명령어를 채택하였다.
• 언어 특성상 탐욕적 알고리즘을 채택하여 명령어 입력에 매우 신중해야 한다. (특히 DROP)

해킹 공격을 막기 위해 발전을 거듭하여 Secure Coding 기법이 발달하게 되었다.

 

SQL은 Oracle, MS, 등등 여러 기업에서 변조 문법을 추가한 버전(?)이 많아서

가장 먼저 SQL 표준인 ANSI 표준 문법을 배워야 한다.

 

SQL DB data 구조

추상적으로 구조를 간단하게 이해하면 이렇게 된다.

생활코딩이 만든 DB TABLE 구조 설명 슬라이드

행(行) == row == 가로줄

열(列) == column == 세로줄 (세로로  뻗은 기둥(column)을 생각하면 쉽다)

 

대문자? 소문자?

대부분의 SQL 에서 Windows 에서는 문법적으로 대소문자 구분을 하지 않는다.

 

하지만 실제 사용할 때 대소문자 구분을 해야 하는 이유는 바로 Linux나 Un*x 계열에서는 

table 과 database 명이 directory와 file 명이기 때문이다.

Windows 에서는 디렉토리와 파일에 접근할 때 대소문자 구분을 하지 않으나

Linux/Unix 는 구분하므로

select 나 insert 명령 시에 테이블의 대소문자를 구분하게 된다.

 

그래서 이름(name)은 소문자로,

질의(QUERY), 연산(OPERATE), 타입(TYPE) 등은 대문자로 표기한다.

 

데이터 타입 (data Type)

보기 쉽게 표로 정리해놓았다

 

테이블 만들기 1

CREATE TABLE <name> (id INTEGER PRIMARY KEY, age INTEGER);

<name>

id	age
. . .	. . .

id 에는 INTEGER 타입만 올 수 있다.

id 는 primary key 로서 각 행/레코드를 고유하게 식별하는 테이블의 필드가 된다.

쉽게 기준 값이 된다는 것이다.

순서 뿐만 아니라 아이디,주민번호 등 고유한 레코드에 활용 가능하다.

primary key 인 열은 NULL 값을 가질 수 없다. 

 

age 에는 INTERGER 타입만 올 수 있다. primary key 가 아니므로 값들은 서로 내용이 겹쳐도 된다.

 

테이블 만들기 2

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, weight REAL);

customers

id	name	age	weight
. . .	. . .	. . .	. . .

 

데이터 삽입

INSERT INTO customers VALUES (71, "Sans", 31);

customers

id	name	age
1~72	...	...
71	Sans	31

id 에 primary Key 가 있다면 자동으로 ++ 연산을 해주기 때문에 밑에 처럼 입력할 땐 넣지 않아도 된다.

INSERT INTO customers (name, age) VALUES ("Brian", 33);​

 

id	name	age
1~72	...	...
71	Sans	31

자동으로 71이 id 에 추가 된다.

 

데이터 쿼리

SELECT * FROM customers;​

전체를 SELECT 한 것이다.

TABLE 전체가 선택(출력값)됨.

C언어 return 이라고 생각하자.

 

customers

 

id	name	age
1	test1	34
2	test2	51
3	test3	18
4	test4	21
5	test5	52
6	test6	12
7	test7	11

SELECT * FROM customers WHERE age > 21;

WHERE 를 활용하여 조건식으로 필터링 가능하다.

 

customers

id	name	age
1	test1	34
2	test2	51
5	test5	52

 

SELECT * FROM customers WHERE age < 21 AND state = "NY";​

다중 조건은 AND , OR 연산자 활용

customers

id	name	state	age
1	test1	NY	34
2	test2	NY	51
5	test5	NY	52

 

 

SELECT * FROM customers WHERE plan IN ("free", "basic");

IN 연산자로 특정 인자 필터링

NOT IN 를 통해 여집합 SELECT 가능

 

customers

id	name	plan
1	test1	free
2	test2	basic
5	test5	none

 query 후(IN)

id	name	plan
1	test1	free
2	test2	basic

SELECT * FROM customers WHERE plan NOT IN ("free", "basic");

query 후(NOT IN)

id	name	plan
5	test5	none

 

SELECT name, age FROM customers;​

특정 열을 SELECT 할 수 있다.

customers

name

test1

test2

test5

 

SELECT * FROM customers WHERE age > 21 ORDER BY age DESC;​

ORDER BY 로 결과 정렬 하는 방법

ASC 오름차순 (보통 생략)

DESC 내림차순

 

SELECT name, CASE WHEN age > 18 THEN "adult" ELSE "minor" END "type" FROM customers;​

CASE 변환

 

 

데이터 집계

SELECT MAX(age) FROM customers;

집계 함수 기초 예문

 

SELECT gender, COUNT(*) FROM students GROUP BY gender;

데이터 그룹화

 

관련 테이블에 JOIN

INNER JOIN과 OUTER JOIN의 가장 두드러지는 차이점은 결과 테이블에서 나타난다.

INNER JOIN은 서로 관련된 두 테이블의 정보만 결합한다.

OUTER JOIN은 한 테이블과 다른 테이블이 서로 연관이 없는 정보도 유지한다.

 

SELECT customers.name, orders.item FROM customers JOIN orders ON customers.id = orders.customer_id;

Inner join 을 통해 TABLE 결합

말 그대로 2개 이상의 TABLE이 합쳐져 출력된다.

 

SELECT customers.name, orders.item FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id;

Outer join 을 통해 TABLE 통합

LEFT JOIN, RIGHT JOIN, FULL JOIN 중 가장 보편적으로 쓰이는 것은 LEFT JOIN 이다.

LEFT JOIN

RIGHT JOIN

 

 

데이터 업데이트 및 삭제

UPDATE customers SET age = 33 WHERE id = 73;

 

데이터 업데이트

 

DELETE FROM customers WHERE id = 73;

 

데이터 삭제

- 칸 아카데미 참고 -