[SQL] SQL 기본 문법

SQL (Structured Query Language)

• 관계형 데이터베이스 관리시스템의 데이터 관리를 위해 설계된 특수 목적으로 된 프로그래밍 언어
• 데이터베이스 스키마 생성 및 수정
• 자료의 검색 및 관리
• 데이터베이스 객제 접근 조정 관리

 

- DDL (Data Definition Language) : 데이터 정의 언어

• 관계형 데이터베이스 구조(테이블, 스키마)를 정의하기 위한 명령어
• CREATE, DROP, ALTER

 

- DML (Data Manipulation Language) : 데이터 조작 언어

• 데이터를 저장, 조회, 수정, 삭제 등을 하기 위한 명령어
• INSERT, SELECT, UPDATE, DELETE

 

- DCL (Data Control Language) : 데이터 제어 언어

• 데이터베이스 사용자의 권한 제어를 위해 사용하는 명령어
• GRANT, REVOKE, COMMIT, ROLLBACK

 

DDL

- CREATE : 데이터베이스에서 테이블 생성

-- CREATE TABLE 테이블 이름
CREATE TABLE classmates (
  -- 컬럼명, 타입, PK/NOT NULL
  -- pk를 지정해주지 않으면 자동으로 생성됨 (rowid)
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INT
);
-- ;까지가 하나의 명령어!

• SQLite는 따로 PRIMARY KEY 컬럼을 작성하지 않으면 값이 자동으로 증가하는 PK 옵션을 가진 rowid 컬럼을 정의
• 스키마 조회

-- .schema 테이블 이름
sqlite> .schema classmates        
CREATE TABLE classmates (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,    
  age INT
);

 

- DROP : 데이터베이스에서 테이블 제거

-- DROP TABLE 테이블 이름
sqlite> DROP TABLE classmates;

 

DML

- 현재 테이블 스키마

sqlite> .schema classmates 
CREATE TABLE classmates (
  name TEXT,
  age INTEGER,
  address TEXT
);

 

- INSERT : 테이블에 단일 행 삽입

INSERT INTO classmates (name, age) VALUES ('홍길동', 23);

-- 모든 컬럼에 값이 다 있다면 컬럼을 명시하지 않아도 된다.
-- INSERT INTO classmates (name, age, address) VALUES ('홍길동', 23, '서울');
INSERT INTO classmates VALUES ('홍길동', 23, '서울');

 

- 현재 테이블 스키마

sqlite> .schema classmates
CREATE TABLE classmates (
  name TEXT NOT NULL,
  age INT NOT NULL,
  address TEXT NOT NULL
);

 

- SELECT : 테이블에서 데이터를 조회

• 모든 컬럼 조회

-- SELECT * FROM table;
sqlite> SELECT * FROM classmates;        
name  age  address
----  ---  -------
홍길동   30   서울
김철수   30   대전
이싸피   26   광주
박삼성   29   구미     
최전자   28   부산

• rowid 포함 조회

sqlite> SELECT rowid, * FROM classmates;
rowid  name  age  address
-----  ----  ---  -------
1      홍길동   30   서울
2      김철수   30   대전
3      이싸피   26   광주     
4      박삼성   29   구미
5      최전자   28   부산

• 특정 컬럼 조회

-- SELECT column1, column2 FROM table;
sqlite> SELECT name, age FROM classmates;
name  age
----  ---
홍길동   30
김철수   30
이싸피   26
박삼성   29
최전자   28

• LIMIT : 쿼리에서 반환되는 행 수를 제한

-- SELECT column1, column2 FROM table LIMIT num;
sqlite> SELECT name, age FROM classmates LIMIT 2;
name  age
----  ---
홍길동   30
김철수   30

• OFFSET : 특정 행부터 시작해서 조회

-- SELECT column1, column2 FROM table LIMIT num OFFSET num;
-- 이 때 num은 맨 위가 0
sqlite> SELECT name, age FROM classmates LIMIT 2 OFFSET 3; 
name  age
----  ---
박삼성   29
최전자   28

• WHERE : 쿼리에서 반환된 행에 대한 특정 검색 조건을 지정 (= : 같다, != : 같지 않다)

-- SELECT column1, column2 FROM table WHERE column=value;
sqlite> SELECT name, age FROM classmates WHERE address='서울';
name  age
----  ---
홍길동   30

-- 여러 개의 조건을 사용할 때는 and 사용 (나이가 30 이상이고 성이 김씨인 경우)
SELECT age, last_name FROM users WHERE age >= 30 and last_name='김';

• SELECT DISTINCT : 조회 결과에서 중복 행을 제거

-- SELECT DISTINCT column FROM table;
sqlite> SELECT DISTINCT * FROM classmates;                     
name  age  address
----  ---  -------
홍길동   30   서울
김철수   30   대전
이싸피   26   광주
박삼성   29   구미
최전자   28   부산

• ORDER BY : 해당 컬럼 기준으로 정렬 (내림차순 : DECS)

SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC;
-- 여러 기준으로 정렬 시 ',' 사용
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME, DATETIME DESC;

 

- DELETE : 테이블에서 행을 제거

-- DELETE FROM table WHERE condition;
sqlite> DELETE FROM classmates WHERE name='김철수';

sqlite> SELECT * FROM classmates;                              
name  age  address
----  ---  -------
홍길동   30   서울
이싸피   26   광주
박삼성   29   구미
최전자   28   부산

• 중복 불가능한 (UNIQUE) 값인 rowid를 기준으로 주로 삭제
• 만약 마지막 행의 값을 삭제한 후 다시 데이터를 추가하면 어떻게 될까?
  → 삭제 되었던 행의 rowid를 재사용
• 재사용 없이 사용하지 않은 다음 행 값을 사용하게 하려면 어떻게 해야할까?
  → AUTOINCREMENT

CREATE TABLE classmates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ...
)

 

- UPDATE : 기존 행의 데이터를 수정

-- UPDATE table SET column1=value1, column2=value2, ... WHERE condition;
sqlite> UPDATE classmates SET name='라춘식', address='대한민국' WHERE name='홍길동';

--1      홍길동   30   서울   
--1      라춘식   30   대한민국

• 중복 불가능한 (UNIQUE) 값인 rowid를 기준으로 주로 수정

 

SQLite aggregate functions

- COUNT : 그룹의 항목 수

• 중복제거할 경우 DISTINCT 사용

< 아래 함수들은 기본적으로 해당 컬럼이 숫자(INTEGER)일 때만 사용 가능>

- AVG : 값 집합의 평균 값을 계산

- MAX : 그룹에 있는 모든 값의 최대값을 가져옴

- MIN : 그룹에 있는 모든 값의 최소값을 가져옴

- SUM : 모든 값의 합을 계산

SELECT COUNT(column) FROM table;
SELECT AVG(column) FROM table;
SELECT MAX(column) FROM table;
SELECT MIN(column) FROM table;
SELECT SUM(column) FROM table;

 

LIKE

- LIKE는 두 가지 와일드 카드와 함께 동작

• _ (언더 스코어) : 반드시 이 자리에 한 개의 문자가 존재해야 함
• % (퍼센트) : 이 자리에 문자열이 있을 수도, 없을 수도 있음 (0개 이상)

-- 지역번호가 02인 사람만 가져올 때 --
SELECT * FROM users WHERE phone LIKE '02-%';

-- 핸드폰 중간 번호가 반드시 4자리면서 511로 시작되는 사람들 --
SELECT * FROM users WHERE phone LIKE '%-511_-%';

 

그 외

- GROUP BY : 해당 컬럼을 기준으로 그룹화

- HAVING : 그룹화한 이후에 적용, 그룹화된 결과에 조건식 적용

SELECT NAME, COUNT(NAME) AS count
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME;