SQL 기초

0.  Column Types

Integer Types : TYNYINT, SMALLINT, MEDIUMINT, INT, BIGINT

Floating Types : FLOAT, DECIMAL, DOUBLE

Date & Time Types : DATE, TIME, YEAR, DATETIME, TIMESTAMP

String Types: CHAR, VARCHAR, TEXT, ENUM, SET

Binary Types: BYTE, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

 

INTEGER Types와 UNSIGNED

모든 Integer Types는 속성으로 Unsigned를 갖는다.

int 칼럼에 양수만 사용하고 싶을 때나 사용 가능한 양수 범위를 넓히고 싶을 때 사용한다. 

 

예를 들어 int로 생성한 id 칼럼에는 음수가 사용될 가능성이 없다.

이 때 unsigned 속성을 지정해주면 (ex. id INT UNSIGNED NOT NULL AUTO_INCREMENT)

int에서 음수만큼의 범위가 양수로 옮겨간다.

 

- int의 범위 : -2147483648 ~ 2147483647

- unsinged int의 범위 : 0 ~ 4294967295

 

CHAR VS VARCHAR

Varchar(100)은 세 글자만 저장하면 3글자만큼의 자리만 차지한다.

Char(100)은 세 글자만 저장해도 무조건 100만큼의 자리를 차지한다.  Varchar보다 속도가 조금 더 빠르다.

데이터값이 고정이라면 CHAR, 아니라면 VARCHAR를 쓰는 것이 낫다.

 

DATETIME VS TIMESTAMP

DATETIME은 시간 정보 자체가 중요한 정보를 저장할 때 사용(ex. 생일, 예약 일시)

TIMESTAMP는 어떤 이벤트가 일어난 시점을 기록할 때 사용(ex. created_at)

 

DECIMAL VS FLOAT

FLOAT은 소수점 자리가 길어지면 반올림한다 => 정확도 보장 x, 빠른 연산 o

DECIMAL은 지정한 소수점까지는 항상 정확하게 보여준다 => 정확도 보장 o, 빠른 연산 x

DECIMAL은 DECIMAL(전체 숫자 개수, 소수점 이하 숫자 개수)로 사용한다.

(ex. DECIMAL(5, 2)로 지정한 칼럼에는 -999.99 ~ 999.99 사이의 숫자를 넣을 수 있다.

DECIMAL은 주로 돈 정보에 사용한다. 

 

ENUM

ENUM을 사용하면 미리 정의한 값만 칼럼에 넣을 수 있음.

CREATE TABLE shoes (
   id INT NOT NULL AUTO_INCREMENT,
   size ENUM('s', 'm', 'l') NOT NULL
)

-- size 칼럼에는 's', 'm', 'l' 값 중 하나만 넣을 수 있음.
-- INSERT INTO shoes(size) VALUES('xl')은 오류가 남

한 번 정해진 ENUM 값을 변경하는 것은 비용이 큰 작업이다.

ENUM 값은 변하지 않는다고 가정해야 하는데, 실제로 변경되지 않는 데이터는 잘 없다.

또한 데이터에 대한 메타데이터는 칼럼 정의에, 데이터는 데이터베이스에 필드에 저장되는 것이 원칙인데

ENUM을 사용하면 데이터가 메타데이터에 들어가버린다. 

ENUM보다는 One to One 관계의 정규화를 권장한다.

 

SET

리스트를 저장할 수 있게 해주는 타입. 

자료구조 SET과 비슷하다. 중복된 값은 넣지 않는다.

칼럼 정의 시 미리 정해둔 값만 저장할 수 있다.

CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+

64개 이상 값을 저장할 수 없고 값 자체에 comma가 있으면 안 된다.

SET보다는 One to Many 정규화를 추천.

 

1. CREATE - Table 만들기

CREATE TABLE employees(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    salary INTEGER NOT NULL
);

CREATE TABLE postings(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    contents varchar(200),
    created_at datetime DEFAULT current_timestamp,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

cf) Constraints : column 만들 때 둘 수 있는 제약조건

- CHECK : 새로운 행 생성 시 특정 제약에 맞는 형식인지 확인함

- DEFAULT : 값이 없으면 디폴트 값 생성

- NOT NULL : 값이 있어야 함

- PRIMARY KEY : 데이터베이스에서 검색할 때 주요 key로 사용함

- UNIQUE : 이 column의 모든 row는 고유한 값만 가질 수 있음

2. INSERT - 데이터 넣기

INSERT INTO employees(name, salary) VALUES ("Sue", 100);

3. SELECT - row 선택하기

모든 row 선택

SELECT * FROM employees

특정 column 선택

SELECT salary FROM employees

특정 row 선택

SELECT * FROM employees WHERE id = 2;
SELECT * FROM employees WHERE name = "Sue";
SELECT * FROM employees WHERE salary > 50;
SELECT * FROM employees WHERE salary > 50 AND name = "Sue";
SELECT * FROM employees WHERE salary IN ("100", "80");

cf) wildcard character %

%는 0개 또는 1개 이상의 문자를 뜻함

하단 예시 : u가 들어간 모든 row를 선택

SELECT * FROM employees WHERE name LIKE "%u%";

cf) 터미널에서 보기 쉽게 나타내기

“.mode columns”와 “.headers yes” 커맨드 사용하면 보기 쉽게 나옴

4. function 사용하기

function 예 : AVERAGE, COUNT, MAX, MIN, SUM

SELECT AVG(salary) FROM employees;
SELECT SUM(salary) FROM employees WHERE salary > 50;

5. UPDATE - 이미 입력한 row 값 변경하기

UPDATE employees
    SET salary =  2000
    WHERE name = "Sue";

6. DELETE - 이미 입력한 값 삭제하기

DELETE FROM employees WHERE name = "Sue";

7. Join - Table 연결하기

• 아이디만 있는 테이블을 association table이라고 함
• foreign key : 두 테이블을 연결하는 key. 다른 테이블의 primary key를 담고 있는 필드.
• JOIN : 두 테이블 연결해서 query 넣을 수 있게 해줌. Inner Join, Left Join, Right Join 등
• Inner Join 예 : 테이블 간 매치 없는 row는 무시함
• 참고 : https://sue-is-programming.tistory.com/216

CREATE TABLE Assignment(
Employees_id INTEGER NOT NULL,
Projects_id INTEGER NOT NULL,
FOREIGN KEY (Employees_id) REFERENCES Employees (id)
FOREIGN KEY (Projects_id) REFERENCES Projects (id)
);

SELECT name, Projects_id
FROM Employees JOIN Assignment
ON Assignment.Employees_id = Employees.id

8. Union

SELECT 문으로 얻은 결과를 다른 SELECT 문으로 얻은 결과와 합칠 때 사용.

각 SELECT문들의 필드는 이름, 순서, 개수, 타입이 같아야 함.

선택할 필드의 이름이 같지 않다면 AS를 사용하여 같게 만들어야 한다.

CREATE TABLE customers(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   customer_city VARCHAR(100) NOT NULL,
   country VARCHAR(100) NOT NULL
);

CREATE TABLE companies(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    company_city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL
);

INSERT INTO customers(name, customer_city, country) VALUES ('snoopy', 'Seoul', 'Korea');
INSERT INTO customers(name, customer_city, country) VALUES ('garfield', 'New York', 'USA');
INSERT INTO customers(name, customer_city, country) VALUES ('cheddar', 'LA', 'USA');
INSERT INTO customers(name, customer_city, country) VALUES ('badook', 'London', 'UK');

INSERT INTO companies(name, company_city, country) VALUES ('INC1', 'Seoul', 'Korea');
INSERT INTO companies(name, company_city, country) VALUES ('INC2', 'Berlin', 'Germany');
INSERT INTO companies(name, company_city, country) VALUES ('INC3', 'London', 'UK');
INSERT INTO companies(name, company_city, country) VALUES ('INC4', 'Incheon', 'Korea');

UNION : 중복되는 결과는 생략하고 보여줌

SELECT country from customers
UNION
SELECT country from companies;
--Korea, USA, UK, Germany

UNION ALL : 중복되는 결과도 보여줌

SELECT country from customers
UNION
SELECT country from companies;
--Korea, USA, USA, UK, Korea, Germany, UK, Korea

9. Sub query

WHERE 절에 조건 넣을 때 괄호로 쿼리를 추가할 수 있다.

이 때 SELECT, INSERT, UPDATE, DELETE문을 넣을 수 있는데 이것을 sub query라고 한다.

sub query는 단독으로 실행할 수 있다.

1개의 결과를 내는 sub query를 사용하는 것이 일반적이지만,

여러 개의 결과를 내는 sub query를 사용해야 한다면 IN 연산자를 같이 써야 한다.

SELECT * FROM users
WHERE height (SELECT MAX(height) FROM users);

SELECT EXISTS (SELECT name FROM users WHERE age >= 80); 
-- 있으면 1, 없으면 0 반환

10. Indexing

자주 사용하는 테이블의 column에 인덱스 부여 -> 데이터베이스에서 데이터 빠르게 가져올 수 있음

인덱스 사용하면 테이블 업데이트하는 데 더 오랜 시간 걸림 -> 꼭 필요한 곳에만 사용

참고 : https://sue-is-programming.tistory.com/242

CREATE INDEX name_index ON employees (name);

11. SQL Vulnerabilities

SQL Injection

악성 유저가 인풋에 SQL 코드를 넣어서 사이트의 보안 장치를 무력화하는 것

 SELECT * FROM users
 WHERE username = username AND password = password;

username 인풋에 Sue” –라고 넣는 경우 : –” AND password = “12345”; 부분이 코멘트 처리됨 -> 비밀번호 없이도 Sue 계정에 접근할 수 있게 됨

 SELECT * FROM users
 WHERE username = "Sue"--" AND password = "12345";

- 방지하기 : SQL에서 escape sequence 사용해서 인풋은 항상 plain text로만 인식하게 만들기, django 등 SQL을 다루는 프로그램에서 자체 escape sequence 사용하기

 

Race Conditions

데이터베이스에 동시에 여러 개 query가 들어오는 경우를 말함

동시에 들어오는 query를 막는 장치가 없는 경우 : 1000원 들어있는 은행계좌인데 웹에서 1000원 요청, 모바일에서 1000원 요청 -> 2000원 인출 가능해짐

- 방지하기 : 한 query가 완료되기 전까지 다른 요청 못 들어오게 데이터베이스를 잠그기, query 실행중이라고 표시하기

 

 

 

참고

MySQL 공식문서