DB

RDBMS 특징 :

모든 데이터를 2차원 테이블로 표현
테이블은 row(record, tuple)과 column(field, item)으로 일어진 기본 데이터 저장 단위
상호관련성을 가진 테이블의 집합
만들거나 이용하기도 비교적 쉽고, 확장이 매우 용이하다.

SQL 문법

.: sqlite3 프로그램의 기능을 실행하는 것
;: 세미콜론까지가 하나의 명령(Query)으로 간주
SQL 문법은 소문자로 작성해도 된다. (단, 대문자를 권장)
하나의 DB에는 여러개의 table이 존재한다.

CRUD_DB

DB생성

$ sqlite3 tutorial.sqlite3 # db 파일 생성
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
sqlite> .databases   # 데이타 설정
main: C:\Users\student\Desktop\TIL2\05_db\00_sql\tutorial.sqlite3
sqlite> .tables
sqlite> .mode csv #모드변경 (CSV)
sqlite> .import hellodb.csv examples #  불러오기
sqlite> .tables 
examples
sqlite> SELECT * FROM examples; # select문은 특정한 테이블을 반환한다.
1,"길동","홍",600,"충청도",010-2424-1232

---- 보기 편하게 바꾸기 ----
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM examples; #; 붙이기
id          first_name  last_name   age         country     phone
----------  ----------  ----------  ----------  ----------  -------------
1           길동          홍           600         충청도         010-2424-1232

--- Table 생성 ---

Table 생성

CREATE TABLE table이름(column1 데이터 형식 데이터 이름)

sqlite> CREATE TABLE classmates ( 
   ...> id INTEGER PRIMARY KEY, # 컬럼 이름, 형식이름, 종류
   ...> name TEXT );
sqlite> .tables
classmates  examples #튜토리얼 DB에 들어가 있는 테이블들

TABLE 및 SCHEMA 조회

테이블 목록 조회
- .tables
- .schema

Table 삭제

sqlite> DROP TABLE classmates;
sqlite> .tables
examples

DATA 추가

특정 TABLE에 새로운 행에 데이터를 추가할 수 있습니다.
INSERT INTO table(coulmn1, coulmn2, …)
- VALUES(value1, value2)

sqlite> INSERT INTO classmates (name, age)
   ...> VALUES('홍길동', 23);
sqlite> SELECT * FROM classmates;
name        age         adress
----------  ----------  ----------
홍길동         23

sqlite> INSERT INTO classmates # 모든영역에 데이터 넣을 때는 뒷부분 생략가능
   ...> VALUES('홍길동', 30, '서울대구대전부산찍고');
sqlite> SELECT * FROM classmates;
name        age         adress
----------  ----------  ----------
홍길동         23
홍길동         30          서울대구대전부산찍고

숨겨진 id 찾기

sqlite> SELECT rowid, * FROM classmates;
rowid       name        age         adress
----------  ----------  ----------  ----------
1           홍길동         23
2           홍길동         30          서울대구대전부산찍고

SQLite는 따로 PRIMARY KEY 속성의 컬럼을 작성하지 않으면 값이 자동으로 증가하는 PK 옵션을 가진 rowid컬럼을 정의합니다.

rowid는 64bit 정수 타입의 유일한 식별 값이다.
INTEGER
단, id INTEGER PRIMARY KEY : rowid 값을 대체

주소가 꼭 필요한 정보라면, 공백으로 비워두면 안된다. (NOT NULL 무결성의 원칙을 어김)

classmate 다시 만들기!

sqlite> DROP TABLE classmates;
sqlite> .tables
examples
sqlite> CREATE TABLE classmates (
   ...> id INTEGER PRIMARY KEY,
   ...> name TEXT NOT NULL,
   ...> age INT NOT NULL,
   ...> address TEXT NOT NULL);
sqlite> .tables
classmates  examples
sqlite> INSERT INTO classmates (name, age)
   ...> VALUES ( '홍길동', 23);
Error: NOT NULL constraint failed: classmates.address

:warning: rowid는 자동으로 작성 되었는데, 직접 id 컬럼을 만든 후에는 입력할 컬럼을 명시하지 않으면 자동으로 입력되지 않으므로, rowid로 자동으로 생성하는 것을 권장합니다.

sqlite> CREATE TABLE classmates (
   ...> name TEXT NOT NULL,
   ...> age INT NOT NULL,
   ...> address TEXT NOT NULL );
sqlite> .tables
classmates  examples
sqlite> INSERT INTO classmates VALUES ('홍길동', 23, '서울'), ('김철수', 30, '대
전'), ('박나래', 23, '광주'), ('이요셉', 33, '구미');

DATA 조회

sqlite> SELECT rowid, name FROM classmates;
rowid       name
----------  ----------
1           홍길동
2           김철수
3           박나래
4           이요셉

LIMIT num;

sqlite> SELECT rowid, name FROM classmates LIMIT 2;
rowid       name
----------  ----------
1           홍길동
2           김철수

LIMIT num OFFSET num;
- 특정 위치에서 부터 limit만큼 가져오겠다.

sqlite> SELECT rowid, name FROM classmates LIMIT 1 OFFSET 3; #OFFSET 이후부터 불로오기
rowid       name
----------  ----------
4           이요셉

WHERE num;

sqlite> SELECT rowid, name FROM classmates WHERE address='서울';
rowid       name
----------  ----------
1           홍길동
  

SELECT DISTINCT column FROM table;

중복 없이 가져오기.

sqlite> SELECT DISTINCT age FROM classmates;
age
----------
23
30
33
  

DATA 삭제

특정 table에 특정한 레코드를 삭제할 수 있습니다.
- DELETE FROM table
- WHERE condition;
무엇을 기준으로 삭제할까?
- PRIMARY KEY값(rowid)을 기준으로 하는 것이 정확하다!

sqlite> DELETE FROM classmates WHERE rowid=4;
sqlite> SELECT * FROM classmates;
name        age         address
----------  ----------  ----------
홍길동         23          서울
김철수         30          대전
박나래         23          광주
#요셉이 사라짐

추가하기

sqlite> INSERT INTO classmates VALUES ('최정수', 45, '서울');
sqlite> SELECT rowid, * FROM classmates;
rowid       name        age         address
----------  ----------  ----------  ----------
1           홍길동         23          서울
2           김철수         30          대전
3           박나래         23          광주
4           최정수         45          서울

rowid가 4번으로 추가되는 것을 볼 수 있다.

TABLE 만들기

sqlite> CREATE TABLE tests(
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT NOT NULL,
   ...> adress TEXT NOT NULL);
sqlite> INSERT INTO tests (id, name, address) VALUES (1, '홍길동', '서울'), (2, '김철순', ('대
전'));
Error: table tests has no column named address
sqlite> INSERT INTO tests (id, name, adress) VALUES (1, '홍길동', '서울'), (2, '김철순', ('대전
')); ;
sqlite> SELECT * FROM tests;
id          name        adress
----------  ----------  ----------
1           홍길동         서울
2           김철순         대전
sqlite> DELETE FROM tests WHERE id=2;
sqlite> INSERT INTO tests (name, adress) VALUES ('최철순', '대구');
sqlite> SELECT * FROM tests;
id          name        adress
----------  ----------  ----------
1           홍길동         서울
3           최철순         대구

rowid 의 최댓값은 64비트 8바이트 실수의 최대값

9,223,372,036,854,775,807 (922경)

INSERT INTO 를 한다면

AUTOINCREMENT 가 없을 때: 중간에 없는 ID를 재사용 하므로 에러가 나지 않을 것.
AUTOINCREMENT 가 있을 때 : 최대 레코드를 넘어서기 때문에 에러가 발생.

DATA 수정

UPDATE

sqlite> UPDATE classmates
   ...> SET name='홍길동', address='제주'
   ...> WHERE rowid=4;
sqlite> SELECT rowid, * FROM classmates;
rowid       name        age         address
----------  ----------  ----------  ----------
1           홍길동         23          서울
2           김철수         30          대전
3           박나래         23          광주
4           홍길동         45          제주

WHERE 심화

데이터 불러오기

sqlite> .mode csv
sqlite> .import users.csv users
sqlite> .tables
classmates  examples    tests       users
sqlite> .header on
sqlite> SELECT * FROM users;

Q. age가 30이상인 사람들 불러오기

sqlite> SELECT * FROM users WHERE age>=30;

Q. age가 30이상인 사람들의 이름 불러오기

sqlite> SELECT first_name FROM users WHERE age>=30;

Q. age가 30이상이고 성이 김인 사람의 성과 나이만 가져온다면?

sqlite> SELECT last_name, age FROM users WHERE age>=30 and last_name='김';

COUNT

Q. users 테이블의 레코드 총 개수는?

SELECT COUNT(*) FROM users;
COUNT(*)
1000

AVG
- 기본적으로 숫자(INTEGER)일때만 가능하다.
- AVG(), SUM(), MIN(), MAX()

Q. 30살 이상인 사람들의 평균나이는?

SELECT AVG(age) FROM users WHERE age>=30;
AVG(age)
35.1763285024155

Q. users에서 계좌 잔액(balance)이 가장 높은 사람과 액수는?

sqlite> SELECT first_name, MAX(balance) FROM users;

Q. users에서 30살 이상인 사람의 계좌 평균 잔액은 ?

SELECT AVG(balance) FROM users WHERE age>=30;
AVG(balance)
153541.425120773

LIKE (와일드카드)
- 정확한 값에 대한 비교가 아닌, 패턴을 확인하여 해당하는 값을 반환한다.
  - 2_3_% / 2_%3_% 서로 다른 조건 20035

Q. users에서 20대인 사람은?

sqlite> SELECT * FROM users WHERE age LIKE '2%';

Q. users에서 지역번호가 02인 사람은?

sqlite> SELECT * FROM users WHERE phone LIKE '02-%';

Q. users에서 이름이 ‘준’으로 끝나는 사람만?

sqlite> SELECT * FROM users WHERE first_name LIKE '%준';

Q. users에서 중간 번호가 5114인 사람만?

sqlite> SELECT * FROM users WHERE phone LIKE '%-5114-%';

ORDER (정렬)

ORDER BY column1, column2 [ASC/DESC]

ASC : 오름차순
DESC : 내림차순

Q. users에서 나이순으로 오름차순 정렬하여 상위 10개만 뽑아보면?

sqlite> SELECT * FROM users ORDER BY age ASC LIMIT 10;

Q. users에서 나이순, 성 순으로 오름차순 정렬하여 상위 10개만 뽑아보면?

sqlite> SELECT * FROM users ORDER BY age, last_name LIMIT 10;

Q. users에서 계좌잔액순으로 내림차순 정렬하여 해당하는 사람의 성과 이름을 10개만 뽑아보면?

SELECT last_name, first_name FROM users ORDER BY balance DESC LIMIT 10;

ALTER
- 특정 테이블의 이름을 바꾼다.
  - ALTER TABLE exist_table
  - RENAME TO new_table;
```
sqlite> ALTER TABLE articles RENAME TO news;
```

새로운 컬럼 추가

ALTER TABLE table_name ADD COLUMN created_at DATETIME NOT NULL;

기존 데이터에 NOT NULL 조건으로 인해 NULL 값으로 새로운 컬럼이 추가 될 수 없으므로 아래와 같은 에러가 발생한다.

NOT NULL 값을 없애거나 기존값()

새로운 ADD 컬럼의 NOT NULL 조건을 없애기

DB

Related Posts

cloud computing 27 Feb 2020

Open Stack 이란? 27 Feb 2020

ServiceWorker 06 Feb 2020