데이타베이스시스템 2011.03 충북대학교경영정보학과조완섭 (wscho@chungbuk.ac.kr)
Chap. 4 SQL 질의어 C4 2
목차 - SQL2에서데이터정의, 제약조건및스키마변경 - SQL에서의기본질의 - 더복잡한 SQL 질의들 - SQL에서삽입, 삭제, 갱신구문 - SQL 뷰 - 주장으로추가적인제약조건명시 - SQL의부가적인기능들 Ch4 3
SQL 개요 SQL 은포괄적인데이터베이스언어로써데이터정의와질의및갱신을위한명령문들을가짐 또한, SQL 은뷰를정의하고, 보안과권한관리를명시하고, 무결성제약조건을정의하고, 트랜잭션제어를명시하는명령어들도제공함 그리고, Java, COBOL, 또는 C/C++ 와같은범용프로그래밍 언어로작성된프로그램에 SQL 문들을삽입하기위한기능들도있음 SQL 은여러 DBMS 제조사가채택한표준데이터베이스언어임 SQL-86, SQL-92, SQL-99, SQL-03, SQL-06 등으로발전 Ch4 4
예제데이타베이스스키마 5 Ch5
6 Database Ch5 Systems
데이터정의, 제약조건및스키마변경 SQL2에서스키마와카탈로그개념 CREATE TABLE 명령과 SQL2의데이타타입및제약조건 DROP SCHEMA와 DROP TABLE명령 ALTER TABLE 명령 Ch4 7
CREATE SCHEMA SQL2 에서스키마와카탈로그개념 Database는생성하는명령어 하나의데이터베이스안에는여러테이블, 뷰, 인덱스등을생성할수있음 Create Schema 명령은데이터뿐아니라메타데이터를생성하기위한공간을마련함 예 : JSMITH라는사용자소유의스키마 COMPANY를생성하는명령예 ) CREATE SCHEMA COMPANY AUTHORIZATION JSMITH ; 8
CREATE TABLE 명령과 SQL2 의데이타타입및제약조건 CREATE TABLE 새로운릴레이션 (table) 을생성하는명령어 릴레이션의이름과함께각애트리뷰트와그의데이터유형을기술함 데이터유형 : INTEGER, FLOAT, DECIMAL( i, j ), CHAR(n), VARCHAR(n) 등 예 ) CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9) MGRSTARTDATE CHAR(9) ) ; SQL 은애트리뷰트값으로 NULL 을허용하지만 NOT NULL 제약조건을 지정한애트리뷰트의값으로 NULL 값을허용하지않음 9
CREATE TABLE 명령과 SQL 의데이타타입및제약조건 (cont.) CREATE TABLE 명령에서 PRIMARY KEY 절은릴레이션의기본키를구성하는하나이상의애트리뷰트들을명시하고, UNIQUE 절은대체키 ( 또는보조키 ) 를명시하며 ( 예, DeptName), FOREIGN KEY 절에서는참조무결성을지정함 외래키 (foreign key) 를정의할때참조무결성을위반할때취할동작 ON DELETE나 ON UPDATE 명령시의미에따라 SET NULL, CASCADE, SET DEFAULT 를지정할수있음 10
(a) CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10, 2), SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE( SSN ), FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL, MGRSTARTDATE DATE, [ 그림 ] SQL2 데이터정의 (a) COMPANY 스키마를정의하는 SQL2 문장들 Ch5 11
PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCE EMPLOYEE (SSN)) ; CREATE TABLE DEPT_LOCATONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATON), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE PROJECT ( PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION VARCHAR(15), DNUM INT NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME), FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) ; CREATE TABLE WORKS_ON ( ESSN CHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3, 1) NOT NULL, [ 그림 ] SQL2 데이터정의 (a) COMPANY 스키마를정의하는 SQL2 문장들 (cont.) 12
PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER)) ; CREATE TABLE DEPENDENT ( ESSN CHAR(9) NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR, BDATE DATE, REATIONSHIP VARCHAR(8), PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN)) ; (b) CREATE TABLE EMPLOYEE (.... DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) PEFERENCES EMPLOYEE (SSN) ON DELETE SET NULL ON UPDATE CASCADE, [ 그림 ] SQL2 데이터정의 (a) COMPANY 스키마를정의하는 SQL2 문장들 (cont.) (b) 디폴트애트리뷰트값과참조트리거된동작 (referential triggered action) 명시 13
CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER) CREATE TABLE DEPARTMENT (........ ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; MGRSSN CHAR(9) NOT NULL DEFAULT 888665555 CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER), CONSTRAINT DEPTSK UNIQUE (DNAME), CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) CREATE TABLE DEPT_LOCATIONS (.... PRIMARY KEY (DNUMBER, DLOCATION ), ON DELETE SET DEFAULT ON UPDATE CASCADE ) ; FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ON DELETE CASCADE ON UPDATE CASCADE ) ; [ 그림 ] SQL2 데이터정의 (b) 디폴트애트리뷰트의값과참조트리거된동작 (referential triggered action) 명시 (cont.) 14
DROP SCHEMA 스키마 (database) 를제거하는명령 (catalog 까지삭제함 ) CASCADE 선택사항은제거되는스키마에포함된모든테이블들, 도메인들, 기타요소까지연쇄적으로제거하도록함 RESTRICT 선택사항은비어있는스키마를제거할때사용 예 ) DROP SCHEMA COMPANY CASCADE ; DROP TABLE DROP SCHEMA 와 DROP TABLE 명령 기본릴레이션을제거하는명령어 ( 카탈로그에서도그정보를삭제함 ) RESTRICT 선택사항은임의의제약조건들 ( 예 : 다른릴레이션에있는외래키에서참조하는경우 ) 이나뷰들에서참조되지않는테이블만을제거하는데사용됨 CASCADE 선택사항은테이블뿐만아니라테이블을참조하는모든제약조건과뷰까지스키마에서자동적으로제거함 예 ) DROP TABLE DEPENDENT CASCADE ; 15
ALTER TABLE 명령 ALTER TABLE 기본테이블의정의를변경하는명령어 ALTER TABLE에는열 ( 애트리뷰트 ) 의추가 / 제거, 열정의의변경, 테이블제약조건들의추가 / 제거등이있음예 ) ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) ; 각 EMPLOYEE 튜플에대해서새로운애트리뷰트 JOB 의값을입력해야함 ; 이는디폴트절을지정하거나 UPDATE 명령을사용하여이루어짐 만약디폴트절을명시하지않는다면, 명령이실행된직후에릴레이션의모든튜플에서추가된애트리뷰트의값은 NULL로지정됨 ; 따라서이러한경우에는열에대하여 NOT NULL 제약조건을사용할수없음 기존열을삭제할경우 CASCADE 선택사항은열과함께열을참조하는모든제약조건들과뷰들을스키마로부터연쇄적으로제거함 만약 RESTRICT를선택한다면, 그열을참조하는뷰들과제약조건들이없는경우에만열을제거함 16
SQL 에서의기본질의 SELECT 문 DB에서정보를검색하는기본문장으로실행결과는또다른테이블 ( 릴레이션이됨 ); relation is a closed set under SQL SQL 이론적인관계모델 (set of tuples) 과는달리 SQL의테이블 ( 릴레이션 ) 은동일한값을갖는튜플들을하나이상가질수있음 따라서 SQL 테이블은튜플들의집합이아니라튜플들의다중집합 (multiset or bag) 으로정의됨 사용자는키제약조건이나 DISTINCT 선택사항을사용하여 SQL의결과릴레이션들을집합으로제한할수도있음 17