7. SQL(Structured Query Language)
이장의주요내용 SQL 개요 SQL 에서기본적인검색질의 SELECT... FROM... WHERE 절 SQL 로데이타베이스의내용을변경하는방법 Insert, Update, Delete 문 릴레이션스키마를명시하는 SQL 데이터정의언어 Create 문 제약조건명시 스키마변경 2
SQL(Structured Query Language) 관계데이타베이스를기반으로한포괄적인표준언어 SQL 기능 데이터정의어 (DDL) : 스키마 ( 테이블, 뷰 ) 를정의하고제약조건을명시 DCL: 보안과권한관리, 트리거등 TCL(Transaction Control Language): commit, rollback 등 데이터조작어 (DML) : select, insert, delete, update S-Q-L 이라발음, sequel 이라발음되기도함 IBM의 System R 프로젝트에서개발된 Sequel(Structured English QUEry Language) 가전신 3
SQL 발전 ANSI와 ISO에서표준버전 ANSI 1986인 SQL-86(SQL1) 을발표 기능이더욱확장된 SQL-92 (SQL2) 개발 세번째수정된 SQL-1999 (SQL3) 개발 그외 SQL:2003 개발 기존기능을몇가지변경하고 XML 기능을추가 SQL:2008에는 SQL내에더많은객체데이타베이스기능을포함 2011년은 Temporal 데이타베이스에대한지원향상 4
SQL select 문장을이용한질의작성 select 문장은 select 절, from 절, where 절과같은 3 가 지절로구성 select <attribute names> from <tables> where <conditions> select 절은결과릴레이션이가져야할애트리뷰트들을지정 from 절은이 SQL 문장의입력으로사용할테이블을지정 where절은질의를통해검색되는투플들을명시하는조건식을지정 < 선택사항 > 5
Customer 그림 6.1 BigHit 비디오예제테이블의스키마와내용 accountid lastname firstname street city state zipcode balance 101 Block Jane 345 Randolph Circle Apopka FL 30458- $0.00 102 Hamilton Cherry 3230 Dade St. Dade City FL 30555- $3.00 103 Harrison Katherine 103 Landis Hall Bratt FL 30457- $31.00 104 Breaux Carroll 76 Main St. Apopka FL 30458- $35.00 106 Morehouse Anita 9501 Lafayette St. Houma LA 44099- $0.00 111 Doe Jane 123 Main St. Apopka FL 30458- $0.00 201 Greaves Joseph 14325 N. Bankside St. Godfrey IL 43580- $0.00 444 Doe Jane Cawthon Dorm, room 142 Tallahassee FL 32306. $10.55 Videotape videoid dateacquired movieid storeid 101 1/25/98 101 3 111 2/5/97 123 3 112 12/31/95 123 5 113 4/5/98 123 5 114 4/5/98 189 5 123 3/25/86 123 3 145 5/12/95 145 5 77564 4/29/91 189 3 90987 3/25/99 450 3 99787 10/10/97 987 5 Movie movieid title genre length rating 101 The Thirty-Nine Steps mystery 101 R 123 Annie Hall romantic comedy 110 R 145 Lady and the Tramp animated comedy 93 PG 189 Animal House comedy 87 PG-13 450 Elizabeth costume drama 123 PG-13 553 Stagecoach western 130 R 987 Duck Soup comedy 99 PG-13 6
Rental accountid videoid daterented datedue cost 103 101 1/3/99 1/4/99 $1.59 101 113 2/22/99 2/25/99 $3.00 101 114 2/22/99 2/25/99 $3.00 103 123 12/1/98 12/31/98 $10.99 101 145 2/14/99 2/16/99 $1.99 101 90987 1/1/99 1/8/99 $2.99 101 99787 1/1/99 1/4/99 $3.49 Employee ssn lastname firstname 145-09-0967 Uno Jane 245-11-4554 Toulouse Jie 376-77-0099 Threat Ayisha 479-98-0098 Fortune Bruce 588-99-0093 Fivozinsky Bruce PreviousRental accountid videoid daterented datereturned cost 101 101 12/9/98 12/10/98 $2.49 101 112 1/13/98 1/4/98 $1.99 101 113 1/15/99 1/15/99 $0.99 102 113 12/1/98 12/3/98 $2.49 111 101 12/4/98 12/6/98 $2.49 111 99787 1/1/99 1/4/99 $3.95 201 113 12/9/98 12/14/98 $3.99 201 77564 1/14/99 1/24/99 $3.35 TimeCard ssn date starttime endtime storeid pai d 145-09- 0967 245-11- 4554 376. 77-0099 145-09- 0967 376. 77-0099 376. 77-0099 01/14/ 99 01/14/ 99 02/23/ 99 01/16/ 99 01/03/ 99 01/03/ 99 8:15 12:00 3 no 8:15 12:00 3 no 14:00 22:00 5 no 8:15 12:00 3 no 10:00 14:00 5 no 15:00 19:00 5 no 7
간단한 SQL 질의예 1 질의 : Doe 라는성을가진고객을찾아라. Select * from Customer where lastname= Doe accountid firstname lastname street city State zipcode balance 111 Jane Doe 123 Main St. Apopka FL 34331 0.00 444 Jane Doe Cawthon Dorm, room 142 Tallahassee FL 32306 10.55 표 6.1. select * from Customer where lastname= Doe 의결과 select 절에서별표 (*) 는입력테이블의모든애트리뷰트를명세 문자열은작은따옴표 ( ) 로둘러싸표시 만일 Where 절이없으면, From 절에명시한릴레이션의모든튜플이질의결과로검색됨 8
간단한 SQL 질의예 2 검색될튜퓰이만족해야하는불리언조건 (AND) 와비교연산자 (=, >) 를명시한예 질의 : 01-03-1999 날짜이후에직원 376-77-0099 의근무일지를모두검색하라. select * from TimeCard where ssn= 376-77-0099 and date> 01-mar-1999 표 6.2 select TimeCard Where ssn= 376-77-0099 and date > 01-mar-1999 의결과 9
간단한 SQL 질의예 3 선택된애트리뷰트만을가지는릴레이션을생성하는예 질의 : Customer 테이블에서모든고객의성과이름을검색하라. select lastname, firstname from Customer LastName Morehouse Block Breaux Hamilton Harrison Doe Greaves Doe firstname Anita Jane Carroll Cherry Catherine Jane Joseph Jane 표 6.3 project Customer onto (firstname, lastname) 의결과 10
간단한 SQL 질의예 4 결과에서중복된투플을삭제한예 select distinct lastname, firstname from Customer lastname Morehouse Block Breaux Hamilton Harrison Doe Greaves firstname Anita Jane Carroll Cherry Catherine Jane Joseph 11
간단한 SQL 질의예 5: 조인질의 두개의테이블들을이용한질의예 질의 : 모든직원들의정보와근무시간표를검색하여라. select * from Employee, TimeCard ( 입력테이블 ) where Employee.ssn = TimeCard.ssn ( 조인조건 or 선택조건 ) select * 또는 from Employee join TimeCard on Employee.ssn = TimeCard.ssn ( 조인된테이블 ) 조인연산을위해명시적인연산자 ->join 키워드 조인조건은 where 절대신에 on 절로표시 12
간단한 SQL 질의예 5: 조인질의결과 Employee.ssn lastname firstname TimeCard.ssn Date start Time storeid paid end Time 145-09-0967 Uno Jane 145-09-0967 01/14/99 8:15 3 no 12:00 145-09-0967 Uno Jane 145-09-0967 01/16/99 8:15 3 no 12:00 245-11-4554 Toulouse Jie 245-11-4554 01/14/99 8:15 3 no 12:00 376-77-0099 Threat Ayisha 376-77-0099 02/23/99 14:00 5 no 22:00 376-77-0099 Threat Ayisha 376-77-0099 01/03/99 10:00 5 no 14:00 376-77-0099 Threat Ayisha 376-77-0099 01/03/99 15:00 5 no 19:00 13
부분문자열패턴비교 1/2 Like 비교연산자 셀랙션조건으로패턴매칭 (pattern matching) 을위해사용 퍼센트 (%) : 임의의가변길이문자열을대표 언더스코어 (_) : 한문자를대표 패턴은대소문자를구분함 Not Like : 일치하지않는문자열에대한검색 질의 : 장르가 comedy 로끝나는모든영화들을검색하여라 select * from Movie where genre like %comedy 14
부분문자열패턴비교 2/2 질의 : SSN 의가운데두가지가 44 인모든직원을검색하여라. select * from Employee where ssn like _-44-15
select 질의결과정렬 1/2 Order by 절 출력테이블의튜플들이특정순서로반환될수있도록지정 기본값은오름차순정렬 (asc) 질의 : 모든고객정보를검색하여, 고객의성과이름을기 준으로오름차순으로정렬하여라. select * from order by Customer lastname, firstname 16
select 질의결과정렬 2/2 질의 : 모든고객정보를검색하여, accountid의값에따라내림차순으로정렬하여라. select * from order by Customer accountid desc 질의 : 모든고객정보를검색하여, lastname 은내림차순, zipcode 는오름차순으로정렬하여라. select * from order by Customer lastname desc, zipcode asc 17
SQL 질의에서표현식 : 산술적용 표현식으로문자열, 숫자, 애트리뷰트값, 산술식, 함수호출이올수있음 select from where lastname, firstname, Employee.ssn, date, (endtime-starttime) * 24 as hoursworked Employee, TimeCard Employee.ssn = TimeCard.ssn lastname firstname ssn date hoursworked Uno Jane 145-09-0967 1/14/99 3.75 Uno Jane 145-09-0967 1/16/99 3.75 Toulouse Jie 245-11-4554 1/14/99 3.75 Threat Ayisha 376-77-0099 2/23/99 8 Threat Ayisha 376-77-0099 1/3/99 4 Threat Ayisha 376-77-0099 2/23/99 4 * as 절을이용질의결과내에있는애트리뷰트에새로운이름부여 18
SQL 질의에서표현식 : 집단화함수 집단함수 : 여러튜플의정보를요약하여하나의튜플로요약하는데사용 count, avg, sum, min, max,. select 절이나 having 절에사용 질의 : 고객번호 101 인고객이현재대여횟수를검색하여라. select from count(*) Rental where accountid =101 질의 : 고객의성 (last name) 의개수를검색하여라. select from count (distinct lastname) Customer 질의 : 직원들의평균근무시간을계산하여라. select avg((endtime-starttime) *24) as hoursworked from TimeCard 19
group by 절과 having 절 Group by 절 같은컬럼값에대하여튜플들의그룹을구성 그룹핑애트리뷰트 (grouping attribute) 를명시하기위한절 Select 절은그룹핑애트리뷰트와튜플들의각그룹에적용할집단함수들만포함 Having 절 그룹에적용되는셀렉션연산 having 절의조건을만족하는그룹만질의결과테이블에반영 having 절에나타날수있는애트리뷰트들은위의 Select 절에서가능한애트리뷰트들과동일 20
group by 절예 질의 : 이전대여기록에서각비디오에대한평균대여비용과대여수를검색하여라. select from group by videoid, avg(cost) as averagecost, count(*) as numrentals PreviousRental videoid accountid videoid daterented Cost 101 101 12/9/98 $2.49 111 101 12/4/98 $2.49 101 112 1/13/98. $1.99 101 113 1/15/99. $0.99 102 113 12/1/98. $2.49 201 113 12/9/98. $3.99 201 77564 1/14/99. $3.35 111 99787 1/1/99. $2.49 1 videoid 값으로 PreviousRental 을그룹화 2 집단함수 avg 와 count 를적용 videoid averagecost numrentals 101 $2.49 2 112 $1.99 1 113 $2.49 3 77564 $3.35 1 99787 $3.95 1 21
group by 절과 having 절예 -1/2 질의 : 두번이상대여된모든비디오에대한제목, 장르, 평균대여비용, 총대여비를검색하여라. select title, genre, count(*) as numrentals, avg(cost) as average, sum(cost) as sum from Movie, Videotape, PreviousRental where Movie.movieId = Videotape.movieId and Videotape.videoId = PreviousRental.videoId group by Movie.movieId, title, genre having count(*) >=2 1 WHERE 절을적용한후 Movie. movieid title genre videoid dateacquired Videotape.movieId.. accountid Previous. videoid 101 The Thirty.. mystery 101 1/25/98 101 101 101 $2.49 101 The Thirty.. mystery 101 1/25/98 101 111 101 $2.49 123 Annie Hall romantic 112 12/31/95 123 101 123 $1.99 123 Annie Hall romantic 113 4/5/98 123 101 123 $0.99 123 Annie Hall romantic 113 4/5/98 123 102 123 $2.49 123 Annie Hall romantic 113 4/5/98 123 201 123 $3.99 189 Animal House comedy 77564 4/29/91 189 201 189 $3.35 987 Duck Soup comedy 99787 10/10/97 987 111 987 $3.95 cost 22
group by 절과 having 절예 -2/2 2 Having 절조건을적용한후 Movie. movieid title genre videoid dateacquired Videotape.movieId.. accountid Previous. videoid 101 The Thirty.. mystery 101 1/25/98 101 101 101 $2.49 101 The Thirty.. mystery 101 1/25/98 101 111 101 $2.49 123 Annie Hall romantic 112 12/31/95 123 101 123 $1.99 123 Annie Hall romantic 113 4/5/98 123 101 123 $0.99 123 Annie Hall romantic 113 4/5/98 123 102 123 $2.49 123 Annie Hall romantic 113 4/5/98 123 201 123 $3.99 cost 3 count, avg, sum 함수를적용한후 title genre numrentals average Sum The Thirty Mystery 2 $2.49 $4.98 Annie Hall romantic 4 $2.37 $9.46 23
중첩질의 (Nested Query) 중첩질의 where 절에또다른 select 문장이있는질의 비교연산자 IN을사용 ( 어떤원소가주어진집합에있는지검사 ) 질의 : 1998년 12월동안비디오테이프를대여했던모든고객정보를검색하여라. 중첩질의표현 select * from Customer where accountid in (select accountid from PreviousRental where daterented >= dec/1/1998 and daterented< 1/1/99 ) 단일 SQL 질의표현 select * from Customer, PreviousRental where Customer.accountId=PreviousRental.accountId and daterented >= dec/1/1998 and daterented< 1/1/99 ) 24
중첩질의 : Exists 함수 Exists Exists 다음에오는 Select 문장의실행결과가존재하는지검사 Exists 함수의결과 ( 참 : 한튜플이라도있을경우, 거짓 : 한튜플도갖지못할경우 ) 질의 : 이전대여에서어떠한비디오테이프도대여하지않은고객정보를검색하여라. select * from Customer C where not exists (select * from PreviousRental P where C.accountId = P.accountId) 그외중첩 Select 문에사용되는연산자 All, unique, contains, not 25
집합연산자 : Union, Intersect, Except 집합연산 합집합 (union), 교집합 (intersect), 차집합 (Except) 호환성이있는, 즉열의수가같고열들이순서대로같은타입을가지는경우어떠한두개의테이블에대해서사용가능 중복된튜플들이결과에서제거됨 질의 : 이전대여와현재대여정보를모두검색하여라. select from union select from *, Rental as sourcetable Rental *, PreviousRental as sourcetable PreviousRental 26
집합연산자 : Union 질의결과예 accountid videoid daterented datedue cost sourcetable 101 101 12/9/98 12/10/98 $2.49 PreviousRental 101 112 1/13/98 1/4/98 $1.99 PreviousRental 101 113 1/15/99 1/15/99 $0.99 PreviousRental 102 113 12/1/98 12/3/98 $2.49 PreviousRental 111 101 12/4/98 12/6/98 $2.49 PreviousRental 111 77564 1/1/99 1/4/99 $3.95 PreviousRental 201 113 12/9/98 12/14/98 $3.99 PreviousRental 201 77564 1/14/99 1/24/99 $3.35 PreviousRental 101 90987 1/1/99 1/8/99 $2.99 Rental 101 99787 1/1/99 1/4/99 $3.49 Rental 103 101 1/3/99 1/4/99 $1.59 Rental 27
SQL 로데이터베이스내용변경 : insert 문 INSERT, DELETE, UPDATE SQL 에서데이타베이스내용을갱신하기위해사용되는명령 Insert 문 각애트리뷰트에대해지정된값을가지고테이블에새로행을추가할때사용 애트리뷰트값들의순서는 CREATE TABLE 명령에서명시한애트리뷰트들의순서와같아야함 INSERT INTO <table name>[(<attribute names>)] VALUES (<values>) or INSERT INTO <table name>[(<attribute names>)] select statement 28
Insert 문예 1/2 예 1 : insert into Customer values (555, Yu Jia, 540 Magnolia Hall, Tallahassee, FL, 32306, 0.00) 예 2 : Insert 명령에서명시한값에대응하는애트리뷰트이름들을명시적으로나타내는경우 insert into Customer (firstname, lastname, accountid) values ( Jia, Yu, 555) 누락된애트리뷰트에대해서는기본값 (default value) 이입력 정의된기본값이없는경우 null 값이입력 non-null이면서기본값이정의되지않은경우에는입력불가 29
Insert 문예 2/2 예 3: 한질의의결과로검색되는다수의튜플을생성된릴레이션에삽입하는경우 insert into select from where group by PayStatement (ssn, hourlyrate, numhours, amountpaid, datepaid) TimeCard.ssn, hourlyrate, sum((endtime-starttime)*24) as hoursworked, sum((endtime-starttime)*24* hourlyrate) as amountpaid, today TimeCard, HourlyEmployee TimeCard.ssn=HourlyEmployee.ssn and paid = false TimeCard.ssn, hourlyrate 30
SQL 로데이터베이스내용변경 : update 문 선택된하나이상의튜플에서애트리뷰트값들을수정하기위해 Update 명령사용 UPDATE SET where <table name> <attribute name> = <value>, <selection condition> 예 1 : 급여지급작업이종료되었을때, 지급사실을기록하기위하여 TimeCard 내용을변경하여라. update set where TimeCard paid = true paid = false 31
Update 문예 예 2 : 직원번호가 145-09-0967 인직원의시간당급여를 10% 인상하여라. update HourlyEmployee set hourlyrate = hourlyrate * 1.1 where ssn = 145-09-0967 32
SQL 로데이터베이스내용변경 : delete 문 한테이블에서튜플들을삭제하기위해 delete 문사용 DELETE FROM WHERE <table name> <selection condition> 예 : 시간제직원이아닌직원에대해정보를 TimeCard 테이블에서모두삭제하여라. delete from TimeCard where not exists (select * from HourlyEmployee where TimeCard.ssn = HourlyEmployee.ssn) 33
SQL 을이용한스키마생성 Create Table 명령 테이블이름과테이블이가지는애트리뷰트들을지정 각애트리뷰트는이름과데이터타입을가짐 초기제약조건들을명시 create table Customer ( accountid int, 정수형 lastname varchar(32), 가변길이의텍스트필드 firstname varchar(32), 가변길이의텍스트필드 street varchar(100), 가변길이의텍스트필드 city varchar(32), 가변길이의텍스트필드 state char(2), 문자형 zipcode varchar(9), 가변길이의텍스트필드 balance real 부동소수점형 ) 34
SQL 에서애트리뷰트데이터타입 산술형정수형 integer, int, smallint, long 부동소수점형 형식정의형 float, real, double precision decimal(i,j), dec(i,j) 문자형고정길이 char(n), character(n) 가변길이 varchar(n), char varying(n), character varying(n) 비트형고정길이 bit(n) 가변길이 bit varying(n) 날짜관련타입 date, time, datetime, timestamp, time with time zone, interval 대용량데이타타입 문자열 long varchar(n), clob(character large object), text 바이너리 blob(binary large object) 35
키와외래키제약조건 - 1/2 create table Store ( storeid int primary key, / 주키명세 / street varchar(100), city varchar(32), state char(2), zipcode varchar(9) manager int references Employee) / 외래키명세 / create table Movie ( movieid varchar(10) primary key, title varchar(100) unique, / 후보키명세 / genre varchar(32), rating varchar(5), accountid int, ) 36
키와외래키제약조건 - 2/2 create table Rental ( accountid int, primary key (accountid, videoid), foreign key (accountid) references Customer(accountId) on delete cascade on update cascade, foreign key (videoid) references Videotape(videoId) on delete set null on update cascade ) 참조무결성제약조건이위반된경우에취할다른동작을명시 예 ) 만일참조되는 Customer 의 accountid 가삭제 / 갱신할경우 Cascade : 참조하는튜플이나값을연쇄적으로삭제 / 변경 On delete : 참조하는모든튜플들을삭제 On update : 참조하는모든 Rental 튜플의 accountid 를새로운값으로변경 Set Null : 참조하는 Rental 테이블의 accountid 값을 Null 로명시 Set Default : 참조하는 Rental 테이블의 accountld 값을 default 값으로명시 37
애트리뷰트제약조건과기본값명시 애트리뷰트제약조건 not null 제약조건 : 어떤애트리뷰트에널값을허용하지않음 널이아닌값을요구하는애트리뷰트와기본키애트리뷰트에명시 Default < 값 > : 애트리뷰트에명시적으로값을주지않으면, 새로운튜플을삽입할때이애트리뷰트의디폴트값이들어감 create table VideoTape ( videoid varchar(10) primary key, movieid varchar(10) not null default 000-00-000 references Movie, storeid int references Store ) 38
도메인제약조건명시 Check 절 애트리뷰트나도메인의값을제한할수있음. create table Movie ( movieid varchar(10) primary key, title varchar(100) unique, genre varchar(32), rating varchar(5), check (rating in ( G, PG, PG-13, R, NC-17 ) ) create table Rental ( accountid int, videoid varchar(10), daterented datetime, datedue datetime, check (daterented <= datedue)) 39
SQL 을이용한스키마변경 : Drop 문 Drop 문 테이블, 도메인또는제약조건과같이이름을가진스키마요소들을제거하는데사용 DROP <table or Schema name> RESTRICT CASCADE Cascade : 테이블삭제시, 해당테이블뿐만아니라, 이테이블을참조하는제약조건, 뷰, 여러다른요소들을자동적으로제거 Restrict : 뷰, 제약조건, 다른요소들에의해참조되지않을경우에제거가능 40
SQL 을이용한스키마변경 : Alter 문 Alter 문 : 기본테이블이나다른이름을가진스키마요소들의정의를변경 예 1: phone 애트리뷰트추가 alter table customer add phone int; 예 2 : zipcode 애트리뷰트제거 alter table customer drop zipcode cascade; 예 3 : movieid의 default절의제거 alter table VideoTape alter movieid drop default; 예 4 : movieid 의새로운디폴트절정의 alter table VideoTape alter movieid set default 1111 ; 41
스키마와카탈로그 (catalog) 스키마 하나의응용 ( 사용자 ) 에속하는테이블과기타구성요소들을그룹화하기위해사용 스키마이름, 스키마소유자나허가권자, 스키마원소 ( 테이블, 제약조건, 뷰, 도메인, 등 ) 에대한기술자 (descriptor) 를포함 Create Schema 문을이용하여스키마생성 CREATE SCHEMA BigHitVideo AUTHORIZATION kclee ; 카탈로그 ( 또는 데이타베이스 라는용어를사용 ) 한 SQL 환경에서있는스키마들의집합 Information_Schema를포함 모든스키마들과모든구성요소에대한 description 정보를제공 같은카탈로그안에있는릴레이션들간의제약조건정의가능 같은카탈로그안에있는스키마들은도메인공유도가능 42