데이터베이스
10. SQL (Structured Query Language)
개요
SQL이란
-
관계형 DBMS를 연구할 때
관계형 대수와관계형 해석을 기반으로 개발된 데이터 언어 -
관계형 데이터베이스의
표준 언어- 모든 주요 제품에서 채택되고 있는 상황
SQL 특징
- 데이터 정의어, 데이터 조작어, 데이터 제어어등 모든 기능을 제공
- 터미널을 통한
대화형질의어와 고급 프로그래밍 언어내에 SQL이 삽입된 형태로 사용이 가능
SQL 데이터 정의어 (DDL)
- 테이블을 생성, 정의하거나 새로운 애트리뷰트를 추가, 제거할 수 있다.
- 조건을 넣어 제약을 줄 수 있다.
- 뷰 생성, 제거 또는 인덱스 생성 제거를 할 수 있다.
EX)
create table 수강(
학번 integer not null,
과목코드 char(5) not null,
점수 integer,
성적 char(2),
primary key(학번,과목코드),
foreign key(학번) references 학생(학번)
on delete cascade
on update cascade,
foreign key(과목코드) references 과목(과목코드)
on delete cascade
on update cascade,
check(성적 >=0 and 성적 <=100);
)- 어떤 애트리뷰트에 null을 허용하지 않으려면 not null을 명시
- primary key는 기본키를 명세
- 하나의 투플을 유일 식별하게 할 수 있도록 도와줌
- 중복된 값이 존재할 수 없다.
- foreigin key는 외래키를 명세
- 참조 무결성이 유지 되어야
- on delete, update 될 때 취할 동작을 명시해줌
- 이 동작엔 cascade, set null, set default, no action이 가능하다/
- check는 제약조건임
- 여기선 한 애트리뷰트가 가질 수 있는 값들의 범위를 지정
밑에서 사용하는 테이블들은 앞 글에 있다.
SQL 데이터 조작어 (DML)
데이터 검색
기본적 구조
select ${가져올 칼럼} from ${테이블} where 조건;ex) 컴퓨터과 학생의 학번과 성명 검색
select 학번, 성명 from 학생 where 학과 = '컴퓨터';- 정확하게 하기 위해 테이블과 col 이름을 .으로 연결시키는 형태를 취하는 것이 원칙임
- select 학생.학번,학생.성명 from 학생 where 학생.학과 = '컴퓨터';
SQL과 이론적인 관계형 데이터 모델의 차이점
- sql은 결과에서 같은 투플을 자동적으로 제거해주지 않는다.
- 중복을 제거하지 않음
- 그래서 select 결과 테이블은 이론상의 투플 집합은 아니다.
- 중복이 있으니
투플의 유일성을 만족하지 않는다.
- 중복이 있으니
- 이를 위해 select에
disticnt를 명시적으로 작성해야한다.- 이러면 중복이 없어진다.
기본 구조
select [ALL|Distinct] ${가져올 칼럼} from ${테이블} where 조건 group by ${col} having 조건 order by ${col} [ASC|DESC];테이블의 열 전체 검색
select * from 학생;- *를 사용하면 모든 열을 가져올 수 있다.
Distinct를 이용한 검색/ 학생테이블에 어떤 학과가 있는지 검색해라
select distinct 학과 from 학생조건 검색 / 과목 테이블에서 학점이 3이고 학년이 4인 과목코드와 과목명을 검색해라
select 과목코드,과목명 from 과목 where 학점 = 3 and 학년 = 4;- where의 조건식에는 비교연산자와 불리언연산자를 사용할 수 있다.
Like를 이용한 검색 / 과목코드가 'co'로 시작하는 과목의 과목코드와 과목명을 검색해라
select 과목코드,과목명 from 과목 where 과목코드 like 'CO%';- wildcard형태
- %는 패턴에 일치하는 모든 문자
- a% : a로 시작하는 모든 값
- %a : a로 끝나는 모든 값
- %a% : a가 있는 모든 값
- _는 패턴에 일치하는 하나의 문자
- _a : 두번째 자리가 a인 두자리 문자의 모든 값
- %는 패턴에 일치하는 모든 문자
NULL을 사용한 검색 / 강의 테이블에서 강의실이 NULL인 교수번호와 과목코드를 검색하라
select 교수번호, 과목코드 from 강의 where 강의실 is null;범위 검색 / 수강테이블에서 점수가 80에서 90사이의 열을 검색해라
select * from 수강 where 점수 between 80 and 90;order by를 이용한 검색
-
정렬된 값을 원한다면 order by를 사용해 asc(오름), Desc(내림)으로 볼 수 있다.
-
수강 테이블에서 점수가 80이상인 학생의 학번과 성적을 검색하되, 학번은 오름차순, 같은학번에 대해서 성적은 내림차순으로 검색해라
select 학번, 성적 from 수강 where 성적 >= 80 order by 학번 asc, 성적 desc;- 첫번째 학번은 1차 정렬값
- 두번째 성적은 2차 정렬값
계산된 열 검색(이름 변경)
- 열 이름을 변경하고 싶은 경우에 사용한다.
- 수강테이블에서 과목코드 'co123'에 수강한 학생의 학번과 점수에 2점을 더한 값을 검색하되, 학번의 열 이름은 'DB수강학번', 점수의 열 이름은 '수정점수', 그 사이에는 '최종 점수 ='라는 내용을 '내용'이라는 열 이름으로 디스플레이해라
select 학번 as DB수강학번, '최종점수=' as 내용, 점수 +2 as 수정점수 from 수강 where 과목코드 = 'co123';조인 검색
-
두개 이상의 테이블로부터 데이터를 조인하는 복수테이블 질의
-
과목코드 'co423'에 수강한 학생의 성명,학과,성적을 검색하라
select 성명, 학과, 성적 from 학생, 수강 where 학생.학번 = 수강.학번 and 과목코드 = 'co423';- col이름이 겹친다면 테이블이름을 붙여서 표현해야한다
- 자연 조인은 from절에 관련된 테이블들을 여러개 명세하게 된다
- where 절에는 어떤 조건으로 조인하느냐를 작성
- 조인 프레디킷이라고 명칭
- row 이름을 단순화하기 위해 from에서 별칭을 사용할 수 있다.
- 명확한 row 참조를 가능하게 한다.
- ex) select 성명 from 학생 s,수강 e where s.학번 = e.학번;
- 명확한 row 참조를 가능하게 한다.
자기 자신의 테이블에 조인하는 검색 (셀프 조인)
- 같은 학과 학생들의 학번을 쌍으로 검색해라, 단 첫번째 학번은 오름차순으로 정렬해라
select s1.학번, s2.학번 from 학생 s1, 학생 s2 where s1.학과 = s2.학과 order by s1.학번;- 무조건 별칭을 써야한다.
집계 함수(aggregate function)를 이용한 검색
- 학생 테이블에서 학생 수를 구해라
select count(*) as 학생수 from 학생;- 과목 'co123'에 대한 점수의 평균을 구해라
select avg(점수) as 평균 from 수강 where 과목코드 = 'co123';group by를 이용한 검색
- 과목별 점수의 평균을 구해라
select 과목코드, avg(점수) as 평균 from 수강 group by 과목코드;having을 사용한 검색
- 2명 이상 수강한 과목에 대한 점수의 평균을 구해라
select 과목코드, avg(점수) as 평균 from 수강 group by 과목코드 having count(*) >=2;중첩질의문(nested query)를 사용한 검색
- 값을 검색 후, 이를 다시 비교 조건에서 사용하는 질의에 사용가능하다.
- select from where 형태를 취한다
- sub query라고도 한다.
- 소괄호안에 표현한다.
- 중첩 질의문의 결과는 아래가 있다.
- 한개의 단일 값이 반환되는 경우
- 한개의 애트리뷰트로 이루어진 테이블이 반환되는 경우
- 여러 애트리뷰트들로 이루어진 테이블이 반환되는 경우
한개의 단일 값이 반환되는 경우
- 학생 김철수와 같은 학과에 속하는 학생의 성명을 검색해라
select 성명 from 학생 where 학과 = (select 학과 from 학생 where 성명 = '김철수');- 이 서브쿼리에서 반환되는 값은 컴퓨터 하나이다.
한개의 애트리뷰트로 이루어진 테이블이 반환되는 경우
- 바깥 질의의 where에는 in any all exists같은 연산자가 사용된다.
- 과목코드 co123을 수강한 학생의 성명을 검색해라
select 성명 from 학생 where 학번 in (select 학번 from 수강 where 과목코드 = 'co123');-
반환되는 결과는 여러개이다.
- 이때 where에는 =가 아닌 in이 사용되어야 한다.
- 한 애트리뷰트가 질의문의 결과 값들의 집합에 속하는지 테스트를 한다.
과목코드 'co123'에 수강하지 않은 학생의 성명을 검색해라
select 성명 from 학생 where 학번 not in (select 학번 from 수강 where 과목코드 = 'co123')all any some과 같은 키워드를 where 조건식에 사용할 수 있다.
수강테이블에서 학번 20181234의 점수보다 좋은 점수를 받은 학생의 학번과 과목코드를 검색해라
select 학번, 과목코드 from 수강 where 점수 > all (select 점수 from 수강 where 학번 = 20181234);- all은 왼쪽의 애트리뷰트 값이 오른쪽 값을 모두 만족해야하는 and의 의미
- any는 하나이상 만족해도 되는 or의 의미
- all과 any는 비교연산자와 결합하여 사용됨
- =any는 in과 같은 의미
- <>all은 not in과 같은 의미
여러 애트리뷰트들로 이루어진 테이블이 반환되는 경우
- exists 연산자를 사용해 중첩 질의문의 결과가 빈 테이블인지 검사한다.
- 중첩 질의문의 결과에 적어도 하나의 레코드가 있으면 참이 된다.
- 아니면 거짓이 된다
과목 'co123'에 수강한 학생의 성명을 검색해라
select 성명 from 학생 where exists (select * from 수강 where 수강.학번 = 학생.학번 and 과목코드 = 'co123');- 서브 쿼리에 from 절에 보면 학생 테이블이 업ㅅ다 하지만 이 질의가 가능하다.
Union이 관련된 검색
- 두 select 문의 결과 테이블이 합병가능해야한다.
- 합병 가능의 조건?
컴퓨터과 학생이거나 EE123에 수강한 학생의 학번을 검색하라
select 학번 from 학생 where 학과 = 컴퓨터 union select 학번 from 수강 where 과목코드 = EE123;- 합집합과 같으므로 결과에서 중복되는 투플들은 자동으로 제거된다.
삽입 insert
기본 단일 삽입
insert into table(도메인들) values(값들);
insert into table values (값들);한번에 여래개의 투플 삽입
insert into 테이블A(넣을 속성) select 속성 from 테이블 B where 조건삭제 delete
기본 단일 삭제
delete from table where 삭제할 투플의 조건복수 레코드 삭제
delete from 테이블;- 이러면 테이블의 모든 행이 삭제
중첩 질의문을 이용한 삭제
delete from table where 조건 in (서브 쿼리)갱신 update
단일 레코드 변경
update 테이블 set 바꿀 속성 where 바꿀 행- 이때 바꿀 속성은 학과 = '컴퓨터'와 같이 지정하면 된다.
복수 레코드 변경
update 테이블 set 바꿀 속성 where 바꿀 행들- 이 때 바꿀 속성은 점수 = 점수 +2 처럼 여러 행에 적용될 수 있게 하면 된다.
중첩 질의문을 이용한 변경
update 테이블 set 바꿀 속성 where 바꿀 테이블 in (서브쿼리)복잡하니 예를 들자면
컴퓨터과 학생의 점수를 2점씩 증가시켜라 라는 쿼리에는
update 수강 set 점수 = 점수 + 2 where 학번 in (select 학번 from 학생 where 학과 = 컴퓨터)이렇게 사용해야한다.
즉 서브쿼리로 바꿀 행들을 가져오고 이후 갱신하면 된다.
SQL 뷰 (view)
- 하나 이상의 기본 테이블로부터 유도되어 만들어지는 가상테이블
뷰 생성
ex) 학생 테이블의 컴퓨터과 학생들로 구성된 학생 뷰를 생성해라
create view 학생뷰(학번,성명,전화번호)
as select 학번,성명,전화번호
from 학생
where 학과 = '컴퓨터'
with check option;- 뷰 생성은 create view 뷰이름 as 가져올 행들(select문들)로 이루어진다
- with check option은 제약 조건을 주는 것이다
- 뷰에 갱신,삽입시 학과 = 컴퓨터를 위반하면 실행이 거절되게 한다.
집계함수를 사용해 뷰 생성
create view 학과별통계(학과,학생수)
as select 학과, count(*)
from 학생
group by 학과;- count는 집계함수로부터 유도되기때문에 열의 이름을 상속받을 수 없다
두개 이상의 테이블을 조인해서 뷰 정의
create view 우수학생(성명,학과,점수)
as select 학생.성명,학생.학과,수강.점수
from 학생,수강
where 학생.학번 = 수강.학번 and 수강.점수>=90;뷰의 제거
일반적 제거
drop view 뷰이름 {RESTRICT | CASCADE}- Restrict
- 이 뷰에 종속적인 뷰가 정의되지 않았을때만 뷰를 삭제
- CASCADE
- 이 뷰에 종속적인 모든 뷰나 제약조건을 삭제
ex)
- drop view 학과별통계 cascade
뷰의 장점
- 관련된 데이터만 이용 가능
- 즁요하고 적합한 데이터만으로 구성 가능
- 민감한 데이터에 대한 접근 금지가 가능
- 보안성 증가
- 데이터베이스 복잡성 해소
- 복잡한 데이터베이스 구조를 숨길 수 있다
- 복잡한 질의를 단순화 할 수 있다
- 권한 부여를 단순화함
- 해당 뷰에만 권한을 부여하면 된다
뷰의 단점
- 데이터의 정의를 변경할 수 없다
- 삽입 삭제 갱신연산에 많은 제한이 있다.
삽입 SQL
- 삽입 SQL은
이중모드특성을 가진다- 터미널에서 사용가능한 모든 SQL은 응용 프로그램에서도 사용할 수 있다.
- 삽입 SQL을 포함하는 응용 프로그램의 특징
- EXEC SQL을 붙여 다른 명령문과 구분 가능
- 호스트 변수는 BEGIN DECLARE와 END DECLARE SECTION을 사용하여 선언
- SQL문에서 사용되는
호스트변수는 :를 붙인다. - 호스트변수와 DB 필드의 이름은 같아도 된다.
- 호스트변수와 대응하는 SQL 열의 데이터 타입은 일치해야한다.
- SQLSTATE라는 호스트 변수를 포함해 SQL이 실행되면 실행상태가 변수에 전달된다.
- 성공, 실패, 오류
EX
호스트 변수는 사용되기 전에 BEGIN DECLARE SECTION에서 선언되어야 한다.
EXEC SQL BEGIN DECLARE SECITON;
int sno;
char sname[21];
char dept[7];
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
sno = 20181234
EXEC SQL select 학번,성명,학과
into :sno, :sname. :sdept
fron 학생
where 학번 = :sno;
IF (SQLSTATE = '00000')
...
else
...단일 레코드 검색
c 언어를 기반으로
int main(){
EXEC SQL BEGIN DECLARE SECITON;
int sno;
char sname[21];
char dept[7];
EXEC SQL END DECLARE SECTION;
sno = 20181234
EXEC SQL CONNECT TO 데이터베이스 이름 USER 유저 이름
EXEC SQL select 학번,성명,학과
into :sno, :sname. :sdept
fron 학생
where 학번 = :sno;
printf("출력 %s %s %s",sno,sname,sdept);
return(0);
}- where 절을 만족하는 레코드가 오직 하나만 있을 때 값이
호스트 변수에 저장된다.
단일 레코드 갱신
학번 20181234 학생이 수강한 학생의 점수를 호스트 변수 x만큼 증가시켜라
EXEC SQL UPDATE 수강
SET 점수 = 점수 + :x
where 학번 = 20181234복수 레코드 검색
- 여러개의 레코드가 검색되는 삽입 SQL문
- 집합속에 레코드를 하나씩 접근하기 위해
커서를 사용- DECLARE CURSOR 문은 뒤에 나오는 SELECT 문을 연결
- SELECT은 cURSOR가 OPEN 될 때 실행
- 커서는 각 투플을 가리키는 포인터
- CURSOR가 OPEN될 때 SELECT이 실행
- FETCH문은 커서를 다음 투플로 이동
- DO ENd내에서 반복 실행
- 레코드가 더 없으면 루프 종료
- CLOSE에서 커서 닫음
복수 레코드 검색 예
EXEC SQL DECLARE cur CURSOR FOR //커서 정의
select 학번, 성명, 학과
from 학생
where 학과 = :sdept;
EXEC SQL OPEN cur; // select 실행
DO // 반복
EXEC SQL FETCH cur INTO :sno,:sname.:sdept; // 다음 레코드 가져옴
...
ENDl
EXEC SQL CLOSE cur; // 커서 종료커서를 이용해 데이터 조작
- 커서가 가리키는 특정 레코드를 변경하거나 삭제가 가능
변경
EXEC SQL UPDATE 학생
SET 학과 =:sdept
WHERE CURRENT OF cur;- current of를 이용해 현재 위치를 가져오자
삭제
EXEC SQL DELETE FROM 학생
WHERE current of cur;동적 sQL
- PREPARE와 EXECUTE를 이용해 명령
PREPARE
- 주어진 SQL문을 예비 컴파일해 목적 코들르 생성하고 stSQL에 저장
EXECUTE
- 저장되어 있는 stSQL의 SQL 목적 코드를 실행
정리