SQLite3로 가볍게 배우는 데이터베이스 2

• SQLite 설치

DB Browser for SQLite는 윈도우, 맥, 리눅스 등 환경 지원함.

https://sqlitebrowser.org/dl/

Downloads - DB Browser for SQLite

 

✔ 서버? 클라이언트?

SQLite는 설치 과정이 없다고 했는데, 위 site에서 설치를 했다.

하지만 DB를 다뤄본 사람이라면 의문이 들 수 있다.

 

데이터베이스 클라이언트는 설치하였는데, 데이터베이스 서버는?

SQLite는 서버에 해당하는 프로세스가 없다.

SQLite Server라는 이름의 프로세스도 작업관리자에서 찾을 수 없다.

애초에 서버-클라이언트 구조가 아니다. SQLite는 데이터베이스를 파일에 기록하기만 한다.

 

• 데이터베이스 생성

File -> new database 선택 -> DB toolbar에 있는 New Database 버튼을 클릭한다.

단축키는 Ctrl + N

1)Choose a filename to save under* 창이 열리면 파일 이름을 입력하고 저장 버튼을 클릭한다.

2) 테이블 정의

Edit table definition 창이 나타난다.

Table에 Person을 입력하고, Fields 탭에서 Add누르기

NAME, Type, 나머지 옵션을 설정해준다.

 

옵션들 

NN : not Null

PK(primary key) : 기본 키 설정

AI(autoincrement) : 자동 증가

 

예시

CREATE TABLE "Person" (

"ID" INTEGER NOT NULL,

"Name" TEXT NOT NULL,

"Birthday" TEXT,

PRIMARY KEY("ID" AUTOINCREMENT)

);

 

✔ 왜 단수형을 사용할까?

1. 단순하고 편리하다.
2. 테이블 간의 관계를 나타내는 테이블명을 지을 때 자연스러움
3. 단수/복수의 변환을 고민하지 않아도 됨.
4. ORM(Object Relation Mapping)을 이용할 때 좋음

검색 : database table name plural or singular

 

A04

• 추가 삭제 갱신 조회
  
  1 .삽입

- - 행추가

INSERT INTO Person (ID, NAME, Birthday)

VALUES (1, '이혜리', '1994-06-09');

- - 컬럼을 순서대로 넣으면 컬럼명 생략 및 아래와 같이 쓸 수 있음

INSERT INTO Person

VALUES (1, '이혜리', '1994-06-09');

SQL문의 키워드는 대소문자 가리지 않고, 줄바꿈도 하지 않아도 됨.

SQL 문장을 하나만 입력했을 때는 행의 끝에 세미콜론을 찍지 않아도 된다.

insert into Person values (1, '이혜리', '1994-06-09')

 

• F5 실행

여러분 실행해도 여러 행이 들어가지 않는다.

ID 필드에 PRIMARY KEY(PK) 제약 조건을 설정했기 때문이다.

이미 있는 ID로 행을 삽입하려고 시도하면 다음과 같은 오류 발생

Execution finished with errors.

Result: UNIQUE constraint failed: Person.ID

At line 1:

INSERT INTO Person

VALUES (1, '이혜리', '1994-06-09');

Browse Data 탭에 가면 행이 추가된 것을 확인할 수 있음.

 

2. 삭제

행을 삭제할 때는 DELETE문을 사용

DELETE FROM Person;

행은 모두 지워졌지만 구조는 변함이 없음.

 

3. 갱신

UPDATE 문을 사용

UPDATE [테이블명] SET [column명] = 'UPDATE할 내용'

- - 행을 다시 insert

INSERT INTO Person VALUES (1, '이혜리', '1994-06-09');

- - NAME을 UPDATE해보자

UPDATE Person SET Name = '혜리';

 

4. 조회

조회할 때 SELECT

다음 문장은 Person 테이블의 모든 행과 열을 조회

SELECT * FROM Person;

---

 

 

A05 추가, 삭제, 갱신, 조회 (2)

• 여러 행을 INSERT하기.

VALUES 절에 여러 행의 값을 적어서 한 번에 삽입가능.

INSERT INTO Person (NAME, BRITHDAY)

VALUES ('박소진', '1986-5-21'), ('김소', '1999-5-22');

 

• 열(column) 값의 자동 증가(AUTOINCREMENT)

테이블을 생성할 때 ID 컬럼에 대하여 AUTOINCREMENT 속성을 설정했으므로,

위와 같이 ID열을 생성하고 INSERT하면 자동으로 ID값이 채워진다.

 

• 널(NULL)

Person 테이블을 정의할 때 Birthday 컬럼은 널값을 허용했으므로, 다음과 같이 실행하더라도 문제가 없다.

INSERT INTO Person (Name)

VALUES ('민아');

위에서 언급했던 ID는 AUTOINCREMENT 속성을 설정했으므로 명시해주지 않았고.

Birthday는 NULL값을 허용해주었으므로 이는 NULL값으로 생성이 되어도 문제가 되지 않는다.

 

✔원하는 열만 조회하기.

SELECT NAME FROM Person;

 

✔원하는 순서로 조회하기

특정 컬름을 기준으로 행을 정렬하여 조회해보기

아래 코드는 Person 테이블의 Name 컬럼을 나열하되, Name을 기준으로 오름차순 정렬한다.

 

• ORDER BY의 default 값은 오름차순

SELECT Name FROM Person ORDER BY Name;

 

DESC 키워드를 추가하여 역순으로 조회할 수 있다.

SELECT Name FROM Person ORDER BY Name DESC;

ORDER BY정렬을 했다고 데이터가 영구적으로 순서가 바뀌진 않음.

SELECT를 할 때만 정렬해서 보여준 것.

 

• SELECT ... WHERE

NAME이 '박소진'인 행을 찾아보기

SELECT * FROM Person WHERE NAME = '박소진';

SELECT \*을 하여 WHERE조건으로 검색한 튜플을 다 가져오자.

 

이번에는 Brithday가 NULL인 행을 제외하고 조회하기.

SELECT * FROM Person WHERE Brithday IS NOT NULL;

 

✔ WHERE 절은 UPDATE, DELECT 문에서도 사용가능.

Name이 '박소진'인 행에 대해, '소진'으로 변경하기

UPDATE Person SET Name = '소진' WHERE Name '박소진'

UPDATE 대상 테이블

SET 튜플 = '바꿀 내용'

WHERE 튜플 = '기존 내용'

 

✔LIKE

WHERE 절에 = 대신에 LIKE를 사용해서, 패턴과 일치하는 문자열을 찾을 수 있음.

SELECT * FROM Person WHERE Birthday LIKE '1986%';

LIKE를 사용하는 경우 '%'를 사용하는 것으로 충분하다,

 

🔴 SQL에서 사용할 수 있는 정규 표현식

기본 패턴

^ : 문자열의 시작을 나타냄

$ : 문자열의 끝을 나타냄

. : 어떤 문자 하나를 나타냄

* : 앞의 패턴이 0번 이상 반복

+ : 앞에 패턴이 1번 이상 반복

? : 앞에 패턴이 0번 또는 1번이상 반복

 

✔ 연습 문제

Person 테이블을 나이가 많은 사람이 먼저 오도록 정렬해서 조회하기.

생일을 알 수 없는 사람은 제외한다.

SELECT * FROM Person WHERE Birthday IS NOT NULL ORDER BY Birthday;

 

Person 테이블에서 Name이 김으로 시작하는 행을 찾아서, 그 값을 유라로 바꿔 보기.

UPDATE Person

SET Name = '유라'

WHERE Name Like '김%';

---

✔변경사항 저장하기

File -> Write changes 메뉴를 선택하거나

DB Toolbar의 Write Changes 버튼을 클릭하여, 지금까지 작업한 내용을 데이터 베이스 파일에 기록하자.

DB Browser for SQLite를 종료했다가 다시 실행하더라도 데이터가 보존되어 있다.

 

✔ 변경사항 되돌리기

새로운 행을 추가하고 결과를 확인하기.

INSERT INTO Person (Name, Birthday)

VALUES ('지선', '1989-10-17');

SELECT * FROM Person;

- - 좀더 추가하기

INSERT INTO Person (Name, Birthday) VALUES('지인', '1992-03-13');

이제 File --> Revert Changes 메뉴를 선택하거나 DB Toolbar의 Revert Changes 버튼클릭 조회

---

 

참고자료 : 
위키독스 SQLite로 가볍게 배우는 데이터베이스
https://wikidocs.net/book/1530