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

A07~08 테이블 변경, 드롭하기

ALTER TABLE 구문을 사용하여 테이블 구조 변경하기.

칼럼(column)을 추가하거나 삭제할 수 있고, 컬럼명을 바꿀 수도 있음.

 

• 컬럼 추가

Person 테이블에 New라는 이름의 컬럼을 추가해보기

 

• COLUMN 앞에 ADD*

ALTER TABLE Person ADD COLUMN New INTEGER;

다른 방법으로 SQL문으로 짜지 않고 SQLite Toolbar에서

Database Structure 탭에서 Person 테이블을 선택하고 Modify Table 버튼을 클릭한 다음,

Edit table definition 창에서 Add 버튼을 클릭해 필드를 추가해도 된다.

 

새 컬럼에 값 넣어보기.

UPDATE Person SET NEW = 164 WHERE NAME = '민아';

UPDATE Person SET NEW = 167 WHERE NAME = '소진';

UPDATE Person SET NEW = 170.3 WHERE NAME = '유라';

INSERT INTO문이 아니라 UPDTAE문으로 한 것은 이미 들어 있는 행에 대해 데이터만 넣는 것이여서 이다.

UPDATE 이전의 상태는 NULL이였다.

 

• 컬럼명 변경

ALTER TABLE 문으로 컬럼명을 바꿀 수도 있다.

NEW 컬럼을 Height로 변경해보자.

 

• 컬럼 앞에 RENAME

ALTER TABLE Person RENAME COLUMN NEW TO Height;

 

✔ SQL 구문 다이어그램

데이터베이스 관리자로서 업무를 하는 사람이 아니라면 평상시 SELECT 문을 자주 사용하고,

ALTER TABLE 문은 알고 있더라도 자주 사용하지 않으므로 막상 사용하려고 하면 기억나지 않는다.

아래의 site를 참고하여 주어-목적어-동사 처럼 나타낸 것을 참고하여 문장을 만들어보자.

https://sqlite.org/lang_altertable.html

ALTER TABLE

 

✔ 연습 문제

1. 'Person' 테이블에 'Weight' 컬럼 추가하기

ALTER TABLE Person ADD COLUMN Weight

 

2. 'Person' 테이블에 아무 컬럼이나 추가했다가 삭제하기

위에 Person 테이블에 Weight 컬럼 삭제하기

ALTER TABLE Person DROP COLUMN Weight

 

✔DROP TABLE문으로 테이블을 드롭(삭제) 할 수 있다.

DROP TABLE Person;

위 명령을 실행하면 데이터베이스 스키마와 디스크 파일에서

테이블이 삭제된다.

 

이 명령은 실행하면 되돌릴 수 없다.

 

테이블 드롭하고 기존 데이터 다시 넣기.

CREATE TABLE "Person" (

"ID" INTEGER NOT NULL,

"Name" TEXT NOT NULL,

"Birthday" TEXT,

"Height" INTEGER,

"Weight" INTEGER,

PRIMARY KEY("ID" AUTOINCREMENT)

);

INSERT INTO Person VALUES

(1, '혜리', '1994-06-09', NULL, 50),

(2, '소진', '1986-05-21', 167, NULL),

(3, '유라', '1992-11-06', 170.3, 54),

(4, '민아', NULL, 164, 46);

---

 

A09 컬럼 별명, 뷰

 

✔ 컬럼 별명

• 이름, 생일

조회 결과에서 컬럼명이 다름 이름으로 보이게 할 수 있음.

8장에서 생성한 Person 테이블에는 Name과 Birthday라는 컬럼이 있다.

이것을 조회할 때 이름과 생일 이라는 컬럼명으로 나타낼 수 있음

SELECT

Name AS "이름",

Birthday AS "생일"

FROM Person;

 

다음과 같이 AS 키워드를 생략해도 됨.

SELECT

NAME "이름",

Birthday "생일"

FROM Person;

 

• BMI 계산하기

Person 테이블에서 체중(kg)을 키(m)의 제곱으로 나눈 값인 체질량지수(BMI)를 계산해보자.

SELECT

Name,

Height,

Weight,

round(weight / (height * height * 0.0001), 1) BMI

FROM Person;

 

🔴round() 함수

round() 함수는 소수점 이하에 대하여 반올림 수행 다음은 소수점 둘째 자리에서 반올림

SELECT round(123.4567, 2);

 

✔ 뷰(View) 생성

뷰는 SELECT 문을 미리 만들어서 이름을 붙여둔 것이라 할 수 있음.

다음은 Person 테이블에서 Birthday와 Birthday의 년, 월, 일에 해당하는 값을 조회하는 뷰를 생성

이거 - 도 있기 때문에 index 고려를 잘 해서 뷰를 생성해야한다.

CREATE VIEW BirthdayView

AS

SELECT

Name,

Birthday bdate,

substr(Brithday,1,4) YYYY,

substr(Brithday,6,2) MM,

substr(Brithday,9,2) DD

FROM Person;

 

🔴 substr()

문자열의 일부를 반환한다.

substr([컬럼명], 시작인덱스, 글자수)

SELECT

substr('abcdefg', 3), -- 셋째 자리부터 끝까지

substr('abcdefg', 3, 2) -- 셋째 자리부터 두 글자

 

뷰 조회하기 - 테이블과 동일한 방법으로 조회하면 됨.

SELECT * FROM BirthdayView;

 

뷰 삭제하기 - DROP VIEW를 사용함.

DROP VIEW BirthdayView;

---

 

A10 조건절

Java나 python같은 프로그래밍 언어를 접해봤다면 if-then-else의 조건분기에 익숙할 것이다.

SQL문에서도 그와 비슷한 것을 사용할 수 있음.

단, SQL은 절차형 프로그래밍 언어와 작동 방식이 다르므로 SQL의 CASE가 절차형 프로그래밍 언어의 조건분기와 똑같지 않다.

 

다음과 같이 조회 CREATE VIEW - SELECT

- - VIEW 만들기

CREATE VIEW BirthdayView

AS

SELECT

Name,

Birthday bdate,

substr(Birthday,1,4) YYYY,

substr(Birthday,5,2) MM,

substr(Birthday,8,2) DD

FROM Person

- - 조회

SELECT * FROM BirthdayView;

 

✔ CASE를 사용한 조건절

형식

CASE

WHEN 조건 THEN 값

WHEN 조건 THEN 값

...

[ELSE값]

END

BirthdayView 뷰의 MM 컬럼에는 생월을 나타내는 두 자리 숫자가 0으로 빈자리를 채운(Zero-padded)형식이 들어 있는데

 

다음 예에서는 그 값을 가지고 영어의 달 이름 약어를 나타나게 했음.

SELECT

Name,

bdate,

MM,

CASE

WHEN MM = '01' THEN 'Jan.'

WHEN MM = '02' THEN 'Feb.'

WHEN MM = '03' THEN 'Mar.'

WHEN MM = '04' THEN 'Apr.'

WHEN MM = '05' THEN 'May.'

WHEN MM = '06' THEN 'Jun.'

WHEN MM = '07' THEN 'Jul.'

WHEN MM = '08' THEN 'Aug.'

WHEN MM = '09' THEN 'Sep.'

WHEN MM = '10' THEN 'Oct.'

WHEN MM = '11' THEN 'Nov.'

WHEN MM = '12' THEN 'Dec.'

END Month

FROM BirthdayView;

---

 

A11 날짜와 시간

strftime()은 date 값을 포맷에 맞추어 반환함.

아래는 현재 시간을 조회하는 명령어

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now') 현재시간;

시간이 맞지 않음을 알 수 있는데. 위와 같이 조회하면 세계표준시가 조회됨.

 

이번에는 세 번째 인자에 'localtime'을 넣어 다시 조회해 보자.

SELECT staftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') 현지시간;

 

✔ 나이 구하기

혜리의 만나이를 구해보자

SELECT

Birthday "생일",

strftime('%Y', 'now') - substr(Birthday, 1, 4) - (strftime('%m-%d', 'now') < substr(Birthday, 6)) "나이"

FROM Person

WHERE Name = '혜리';

혜리는 6월 9일 생이므로,

strftime('%m-%d', 'now') < substr(Birthday,6) 이 부분은 5월에 실행하는지 7월에 실행하는지에 따라 결과가 달라진다.

같은 6월에 실행해도 생일이 지났는지에 따라 결과가 달라짐.

 

예시

SELECT

'05-01' < '06-09' AS "5월 1일에 실행한 경우",

'07-01' < '06-09' AS "7월 1일에 실행한 경우"

- - 위는 1(true) 아래는 0(false)이 나옴

---

 

 

 

 

 

 

 

 

 

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