안경호 SQLite 활용

[운영_경호]

1. SQLite 변수 타입

2. SQLite 사용 명령어

3. SQLite에서 제공되는 함수

4. SQLite 제약조건

5. 학생-과목-등록 테이블 생성, 삽입, 조인

1. SQLite 변수 타입

  1) INTEGER

- Signed integer. 1,2,3,4,6,8 바이트로 저장.

  2) REAL

- 부동 소수점 값, 8바이트 IEEE 부동 소수점으로 저장.

  3) TEXT

- Text String.

  4) BLOB

- Binary Large OBject 입력데이터 그대로 저장.

  5) NULL

2. SQLite 사용 명령어

1) 새로운 구조, 정의를 작성

CREATE TABLE	새로운 테이블 작성
CREATE VIEW	새로운 뷰 작성
CREATE TRIGGER	새로운 트리거 작성
CREATE INDEX	새로운 인덱스 작성

2) 기존의 구조, 정의를 작성

DROP TABLE	기존 테이블을 삭제
DROP VIEW	기존의 뷰를 삭제
DROP TRIGGER	기존의 트리거를 삭제
DROP INDEX	기존의 인덱스를 삭제

3) 테이블, 정보 조작

INSERT	테이블에 새로운 행 삽입
UPDATE	테이블의 행 또는 값 갱신
DELETE	테이블에서 행 삭제
SELECT	테이블 또는 뷰에서 행 선택

4) 트랜젝션 조작

BEGIN	트랜젝션 시작
END	트랜젝션 종료
COMMIT	트랜젝션 인증
ROLLBACK	트랜젝션 내의 쿼리 중지

5) 그 외의 조작

COPY	파일 또는 테이블간 값 복사
EXPLAIN	도움말의 실행계획 표시
PRAGMA	SQLite 동작 명령
VACUUM	데이터베이스의 청소 및 분석
ATTACH DATABASE	현재 DB 접속에 대해 기존 DB 추가
DETACH DATABASE	추가 DB 연결을 끊음

3. SQLite에서 제공되는 함수

1) 산수 함수

ABS(X)	X의 절대값 반환
MAX(X,Y[....])	인수의 최대값 반환
MIN(X,Y[....])	인수의 최소값 반환
RANDOM(*)	난수를 반환
ROUND(X,[.Y])	X을 소수점 이하 Y번째에서 사사오입

2) 문자 처리 함수

LENGTH(X)	인수 문자열의 길이를 반환
LOWER(X)	인수 문자열을 소문자로 변환
UPPER(X)	인수 문자열을 대분자로 변환
SUBSTR(X,Y,Z)	X문자열을 Y, Z로 지정한 범위의 문자열을 반환 -SELECT substr('abcd',2,2) -결과 : bc

3) 날짜 처리 함수

DATE(...)	-SELECT date('2006-07-13', '3 days'); -결과 : 2006-07-16
TIME(...)	변수형식 결과를 시간으로 변환
DATETIME(...)	-SELECT datetime('now'); -결과 : 2006-07-13 08:00:00
JULIANDAY(...)	변수형식 결과를 율리우스 일로 변환

4) 조건 함수

COALESCE(X,Y[...])	NULL이외의 최초 인수 반환
IFNULL(X,Y)	NULL이외의 인수를 반환
NULLIF(X,Y)	인수가 다른경우 X반환, 같을경우 NULL 반환

5) 집계 함수

AVG(X)	지정한 항목(값)의 평균 반환
COUNT(X)	지정한 항목의 행수를 반환
MAX(X)	지정한 항목의 최대값 반환
MIN(X)	지정한 항목의 최소값 반환
SUM(X)	지정한 항목의 합계를 반환

6) 그 외의 함수

TYPEOF(X)	인수 형을 반환
LAST_INSERT_ROWID()	접속 중 최후에 기입한 rowid를 반환
SQLITE_VERSION(*)	SQLite 버전을 반환

4. SQL 제약조건

  1) 외래키 제약조건

- 외래키 사용법

EX) CREATE TABLE 테이블명(

열정의 references 외부테이블(열명)

on {delete|update} 무결성_액션

[not] deferrable [initially {deferred|immediate}, ] );

- 무결성 액션에 사용되는 명령어

    ～ set null

부모 키가 삭제, 변경 시 그런 값을 갖는 행이 더 이상 존재하지 않으면, 그 값을

참조하던 자식의 모든 행의 외부 키를 NULL로 변경한다.

    ～ set default

부모 키가 삭제, 변경 시 그런 값을 갖는 행이 더 이상 존재하지 않으면, 그 값을

참조하던 자식의 모든 행의 외부 키를 그 열의 디폴트 값으로 변경한다.

    ～ cascade

부모 키가 변경될 때는 그것을 참조하는 모든 자식의 외부 키도 함께 변경한다.

부모 키의 행이 삭제될 때는 그것을 참조하는 외부 키를 갖는 식의 모든 행도 함께

삭제한다. 이 옵션을 사용할 때는 주의를 요한다. 못해서 자식 테이블의 데이터가

무더기로 삭제될 수 있기 때문이다.

    ～ restrict

부모 키가 변경, 삭제되면 그것을 참조하는 외부 키를 갖는 자식의 행들이 고아가 되어

결함이 있는 데이터가 될 수 있으므로, 변경, 삭제를 제한한다.

    ～ no action

delete문의 실행이 완전히 끝날 때까지 기다렸다가 에러를 발생시키고 삭제 데이터를

롤백시킨다. 여러 행을 삭제할 경우 restrict 옵션에서는 바로 에러 처리가 되지만,

no action은 명령이 끝난 후 에러 처리되며 삭제 데이터를 복구한다.

- deferrable 절

   정의된 제약조건이 곧바로 적용 될 것인지 여부를 확인

EX) 기존 테이블 enrol에 student.sno를 참조하는 외래키 적용

CREATE TABLE student(

sno integer primary key,

name text);

-> 외래키 적용 전

CREATE TABLE enrol(

eno integer primary key,

sno_id integer,

name text);

-> 외래키 적용 후

CREATE TABLE enrol(

eno integer primary key,

sno_id integer references student(sno)

on delete restrict

deferrable initially deferred,

name text);

  2) 중첩 트리거

  3) ALTER TABLE

  4) RIGHT OUTER JOIN

  5) FULL JOIN

5. 학생-과목-등록 테이블 생성, 삽입, 조인

- student 테이블

학번 (sno)	이름 (sname)	학년 (year)	학과 (dept)
100	나수영	4	컴퓨터
200	이찬수	3	전기
300	정기태	1	컴퓨터
400	송병길	4	컴퓨터
500	박종화	2	산공

- course 테이블

과목번호 (cno)	과목이름 (cname)	학점 (credit)	학과 (dept)	담당교수 (prname)
C123	프로그래밍	3	컴퓨터	김성국
C312	자료구조	3	컴퓨터	황수관
C324	화일구조	3	컴퓨터	이규찬
C413	데이터베이스	3	컴퓨터	이일로
E412	반도체	3	전자	홍봉진

- enrol 테이블

학번 (sno)	과목번호 (cno)	성적 (grade)	중간성적 (midterm)	기말성적 (final)
100	C413	A	90	95
100	E412	A	95	95
200	C123	B	85	80
300	C312	A	90	95
300	C324	C	75	75
300	C413	A	95	90
400	C312	A	90	95
400	C324	A	95	90
400	C413	B	80	85
400	E4123	C	65	75
500	C312	B	85	80

1) 생성

- student 테이블 생성

create table student(

sno integer primary key,

sname text,

year integer,

dept text);

- course 테이블 생성 

create table course(

cno text primary key,

cname text,

credit integer,

dept text,

prname text);

- enrol 테이블 생성( student.sno와 course.cno를 참조하여 외래키 생성)

create table enrol(

sno integer references student(sno)

on delete restrict

deferrable initially deferred,

cno text references course(cno)

on delete restrict

deferrable initially deferred,

grade text,

midterm integer,

final integer);

2) 삽입

insert into student values('100','나수영','4','컴퓨터');

insert into student values('200','이찬수','3','전기');

insert into student values('300','정기태','1','컴퓨터');

insert into student values('400','송병길','4','컴퓨터');

insert into student values('500','박종화','2','산공');

insert into course values('C123','프로그래밍‘,’3‘,’컴퓨터‘,’김성국‘);

insert into course values('C312','자료구조‘,’3‘,’컴퓨터‘,’황수관‘);

insert into course values('C324','화일구조‘,’3‘,’컴퓨터‘,’이규찬‘);

insert into course values('C413','데이타베이스‘,’3‘,’컴퓨터‘,’이일로‘);

insert into course values('C412','반도체‘,’3‘,’전자‘,’홍봉진‘);

insert into enrol values('100', 'C413','A','90','95');

insert into enrol values('100', 'E412','A','95','95');

insert into enrol values('200', 'C123','B','85','80');

insert into enrol values('300', 'C312','A','90','95');

insert into enrol values('300', 'C324','C','75','75');

insert into enrol values('300', 'C413','A','95','90');

insert into enrol values('400', 'C312','A','90','95');

insert into enrol values('400', 'C324','A','95','90');

insert into enrol values('400', 'C413','B','80','85');

insert into enrol values('400', 'E412','C','65','75');

insert into enrol values('500', 'C312','B','85','80');

3) 조인

- 테이블 조인

select student.sno, enrol.sno

from student, enrol

where student.sno=enrol.sno;

- 내부 조인

select *

from student inner join enrol

on student.sno=enrol.sno;

- 교차 조인

select *

from student, enrol;

- 외부 조인

-> 외부 조인은 left join 만 사용 가능. left join이외의 외부 조인은 사용 불가.

select *

from student left outer join enrol

on student.sno=enrol.sno;

SQLite 명령어.hwp