|
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;