create table tb_student(
hakno char(4) primary key
, uname varchar2(10) not null
, phone varchar2(15) not null
, address varchar2(20)
)
--과목테이블
create table tb_gwamok(
gcode char(4) primary key
, gname varchar2(10) not null
, hakjum number default 0
)
select * from tb_student;
select * from tb_gwamok;
insert into tb_student(hakno,uname,phone,address)
values('1001','홍길동','555-8798','서울')
insert into tb_student(hakno,uname,phone,address)
values('1002','민들레','444-8745','서울');
insert into tb_student(hakno,uname,phone,address)
values('1003','개나리','333-8965','제주');
insert into tb_student(hakno,uname,phone,address)
values('1004','무궁화','222-4125','대전');
insert into tb_student(hakno,uname,phone,address)
values('1005','라일락','111-8963','서울');
select * from tb_student
insert into tb_gwamok(gcode,gname,hakjum)
values('P01','자바',3);
insert into tb_gwamok(gcode,gname,hakjum)
values('D01','포토샵',2);
insert into tb_gwamok(gcode,gname,hakjum)
values('C01','바리스타',1);
insert into tb_gwamok(gcode,gname,hakjum)
values('P02','오라클',2);
insert into tb_gwamok(gcode,gname,hakjum)
values('P03','JSP',5);
select * from tb_gwamok
create table tb_sugang(
sno number primary key
, hakno char(4) not null
, gcode char(4) not null
, sdt date default sysdate --현재 시스템 날짜
)
create sequence sugang_sno
insert into tb_sugang(sno,hakno,gcode)
values(sugang_sno.nextval,'1001','P01')
insert into tb_sugang(sno,hakno,gcode)
values(sugang_sno.nextval,'1001','P02');
insert into tb_sugang(sno,hakno,gcode)
values(sugang_sno.nextval,'1001','P03');
insert into tb_sugang(sno,hakno,gcode)
values(sugang_sno.nextval,'1002','D01');
insert into tb_sugang(sno,hakno,gcode)
values(sugang_sno.nextval,'1003','P01');
delete from tb_sugang
select * from tb_sugang
insert into tb_sugang(sno,hakno,gcode)
values(1,'1001','P01')
select * from tb_sugang
1) 학번 1001 입력하면 학생 정보 출력
select *
from tb_student
where hakno='1001'
2) 과목이름 입력하면 과목정보 출력
select *
from tb_gwamok
where gname='오라클'
select *
from tb_gwamok
where gname like '자%' --비슷한 유형
like '%자'
like '%자%'
3) 학번 1001 입력하면 수강정보 출력
select *
from tb_sugang
where hakno='1001'
================================================
*조인형식
select 칼럼명
from 테이블명 join 테이블명
on 조건절
select *
from T1 join T2
on T1.x=T2.y
select T1.*,T2.*,T3.*
from T1 join T2
on T1.x=T2.y join T3
on T2.y=T3.z
==============================================
4)수강신청을 한 학생의 학번과 이름 출력
select tb_sugang.*, tb_student.*
from tb_sugang join tb_student
on tb_sugang.hakno=tb_student.hakno
where tb_sugang.hakno='1001'
select AA.*, BB.uname
from tb_sugang AA join tb_student BB
on AA.hakno=BB.hakno
where AA.hakno='1001'
5)수강신청을 한 학생들의 이름, 수강과목이름, 학점을 출력
select tb_sugang.*, tb_student.uname, tb_gwamok.gname,tb_gwamok.hakjum
from tb_sugang join tb_student
on tb_sugang.hakno=tb_student.hakno join tb_gwamok
on tb_sugang.gcode=tb_gwamok.gcode
where tb_sugang.hakno='1001'
=============================================================
select tb_sugang.*, tb_student.uname, tb_gwamok.gname,tb_gwamok.hakjum
from tb_sugang join tb_student
on tb_sugang.hakno=tb_student.hakno join tb_gwamok
on tb_sugang.gcode=tb_gwamok.gcode
================================================================
1)과목코드 P01를 입력하면 학번,이름,과목이름,학점 출력
select tb_sugang.hakno,tb_student.uname,tb_gwamok.gname,tb_gwamok.hakjum
from tb_sugang join tb_student
on tb_sugang.hakno=tb_student.hakno join tb_gwamok
on tb_sugang.gcode=tb_gwamok.gcode
where tb_sugang.gcode='P01'
2)학번 1001이면서 과목코드 P01를 수강신청했는지 수강정보 출력
select AA.*, BB.uname,CC.gname
from tb_sugang AA join tb_student BB
on AA.hakno=BB.hakno join tb_gwamok CC
on AA.gcode=CC.gcode
where AA.hakno='1001' and AA.gcode=upper('p01')
--upper()대문자로 변경/lower()소문자로 변경
select count(*) from tb_sugang --레코드갯수
select sum(hakjum),avg(hakjum),min(hakjum),max(hakjum)
from tb_gwamok --집계함수
select *
from tb_sugang
order by sno desc
정렬:오름차순 asc, 내림차순 desc
형식)동일한 칼럼의 내용끼리 묶어주는 쿼리문
select 칼럼명 or 집계함수 from 테이블명 group by 칼럼명
group by절과 * 는 절대 같이 사용할수 없다.
대부분의 group by절은 집계함수와 많이 사용된다.
*수강신청을 한 각각의 학생들이 몇과목을 신청했는지?
select hakno, count(hakno) as 과목수
from tb_sugang
group by hakno
order by count(hakno) asc
3)학번 1001를 입력하면 총 몇학점인지 출력
select SU.hakno,sum(hakjum)
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
having sum(hakjum)<5
select AA.*, BB.uname
from (
select SU.hakno,sum(hakjum)
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
) AA join tb_student BB
on AA.hakno=BB.hakno
where AA.hakno='1001'
select KK.uname,EE.*
from (
select hakno,sum(hakjum)
from (
select *
from tb_sugang AA join tb_gwamok BB
on AA.gcode=BB.gcode
) DD
group by hakno
) EE join tb_student KK
on EE.hakno=KK.hakno
================================================================
4)각 과목당 수강신청 인원수 출력
select AA.*, BB.gname
from (
select gcode,count(gcode)
from tb_sugang SU
group by gcode
) AA join tb_gwamok BB
on AA.gcode=BB.gcode
5)각 과목당 수강신청 인원수가 1명인 수강정보 출력
select gcode, count(gcode)
from tb_sugang
group by gcode
having count(gcode)=1
6) 수강신청한 학생들 중 총학점의 평균보다 낮게 신청한 학생들의 정보 출력
이영례=========================================================
select AA.*,BB.uname
from (
select SU.hakno,sum(hakjum)
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
having sum(hakjum)< ( select avg(hap)
from
( select sum(hakjum) as hap
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
) --총학점누적
) --총학점에 대한 평균값을 구하고
) AA join tb_student BB
on AA.hakno=BB.hakno
================================================
select *
from tb_gwamok
where hakjum > (select avg(hakjum) from tb_gwamok)
where <
select avg(hap)
from (
select sum(hakjum) as hap
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
) AA
===================================================
혜선=====================================================
select AA.*, ST.uname
from ( select SU.hakno, sum(GW.hakjum) hak
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
) AA join tb_student ST
on ST.hakno=AA.hakno
where AA.hak < ( select avg(BB.hak) avghak
from ( select sum(GW.hakjum) hak
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
) BB
)
승현========================================================
select uname,s.*
from ( select s.hakno,sum(hakjum) from tb_sugang s,tb_gwamok g
where s.gcode=g.gcode
group by s.hakno
having sum(hakjum)<(
( select sum(hakjum) from tb_sugang s,tb_gwamok g
where s.gcode=g.gcode) / ( select count(distinct hakno)
from tb_sugang)
)
) s,tb_student
where s.hakno=tb_student.hakno
현국========================================================
select SU.hakno, sum(GW.hakjum), ST.uname
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode join tb_student ST
on SU.hakno=ST.hakno
group by SU.hakno, ST.uname
having sum(GW.hakjum) < ( select avg(hap)
from
( select SU.hakno, sum(hakjum) as hap
from tb_sugang SU join tb_gwamok GW
on SU.gcode=GW.gcode
group by SU.hakno
)
)
=======================================================================
distinct 동일칼럼에서 중복한 값이 있을경우 대표로 1개만 출력
select hakno from tb_sugang
select distinct(hakno) from tb_sugang
=====================================================================
select count(*) from zipcode
select top 10 * from zipcode
1) 동이름 '구로'로 시작되는 레코드 갯수
select count(dong) from zipcode where dong like '구로%'
2) 동이름 '사서함'을 끝나는 레코드 갯수
select count(*) from zipcode where dong like '%사서함'
3) 우편번호가 '100'으로 시작하고, 동이름에 '중앙'이 들어가는 레코드 갯수
select count(*) from zipcode where zipcode like '100%' and dong like '%중앙%'
4) 번지에 데이터가 입력되지 않은 레코드 검색
select count(*) from zipcode where bunji is null --is not null
select count(*) from zipcode where bunji=''
5) 서울시중에서 영등포구,구로구,마포구만 검색
select count(*)
from zipcode
where sido='서울'
and (gugun='영등포구' or gugun='구로구' or gugun='마포구')
select count(*)
from zipcode
where sido='서울'
and gugun in ('영등포구','구로구','마포구')
select * from zipcode where sido='경기' and gugun in ('영등포구','구로구','마포구')
6)서울시의 각각의 구 중에서 구이름, 동갯수를 내림차순으로 출력
select AA.gugun, count(AA.gugun)
from(
select gugun,dong
from zipcode
where sido='서울'
group by gugun, dong
) AA
group by AA.gugun
order by count(AA.gugun) desc
select gugun, count(distinct(dong))
from zipcode
where sido='서울'
group by gugun order by count(distinct(dong)) desc
7)전국의 시도별로 각각의 시, 구, 군이 몇개씩 존재하는지 내림차순으로 출력
select AA.sido, AA.gugun, count(AA.gugun)
from(
select sido, gugun,dong
from zipcode
group by sido, gugun,dong
) AA
group by AA.sido, AA.gugun
order by AA.sido, count(AA.gugun) desc
8)각 도별 시중에서 동갯수가 50이하의 레코드의 도이름, 시이름, 동갯수 내림차순 출력
select AA.sido, AA.gugun, count(AA.gugun)
from(
select sido, gugun,dong
from zipcode
group by sido, gugun,dong
) AA
group by AA.sido, AA.gugun
having count(AA.gugun)<50
order by count(AA.gugun) desc
select * from zipcode
==============================================================
* SQL injection
[제약조건]
- 데이터의 무결성을 위해 강제성을 테이블에 부여한다.
- 자료의 무결성을 테이블 설계단계에서 강제하여 지킴으로써
보다 효율적으로 데이터 관리의 중요한 역할을 함.(강제성)
데이터의 결점이 없는 상태를 무결성이라 한다.
1. default 제약조건
사용자가 값을 입력하지 않으면 해당 컬럼이 정의한 기본값으로
자동으로 넣어준다.
예) 등록일(현재시스템의 날짜)
regdt date default sysdate 오라클 방식
regdt datetime default getdate() MS-SQL 방식
2. primary key 기본키
테이블의 내용에서 각각의 레코드를 대표하는 유일한 칼럼은
반드시 존재해야 한다.
왜? 해당 레코드만 꼭 집어서 수정,삭제,조회하기 위해
즉, where조건절에서 걸릴수 있는 대표적인 칼럼을 미리 강제한다.
- primary key는 테이블당 1개씩 반드시 존재해야 한다.
- 조회가 자주 일어나는 칼럼이 primary key의 대상이 된다.
- primary key 로 설정된 칼럼은 빨리 조회할 수 있는 기능이 추가된다
- primary key는 null값을 넣을수 없다.
- 유일성 보장, not null 속성을 포함
- index (조회를 빠르게 하는 기능)가 해당 컬럼에 적용
- 오라클(넌클러스터드 인덱스),MS-SQL(클러스터드 인덱스)
3. unique
- 중복된 컬럼내용을 허용하지 않는다
- 유일성 보장
- 단 1회 null 값 허용
- 테이블당 여러 칼럼 가능
예) jumin varchar2(13) unique
4. not null
- null값(빈값)을 허용안함(해당 컬럼에 무조건 데이터 입력해야함)
예)아이디, 비번, 주민번호.....취미(X)
upw varchar2(8) not null --비번
habbit varchar2(8) --취미
5. check
- 데이터의 범위를 제한시키는 제약조건)
예) marry varchar2(10) check(marry in ('기혼','미혼'))
kor number(3) check(kor between 0 and 100) --0~100
6. 참조무결성 Foreign Key 제약조건
- 부모테이블의 PK를 참조하는 자식테이블의 해당 칼럼
이때 자식테이블의 FK는 부모테이블의 PK와 자료형이 일치되야 한다.
create table student(
hakno char(4) primary key
,regdt date default sysdate
)
create table sugang(
sno number primary key
,hakno char(4) references student(hakno)
on delete cascade
)
--hakno char(4) foreign key references student(hakno) MS-SQL문법
drop table sugang
insert into student(hakno) values('1001');
insert into student(hakno) values('1002');
select * from student
insert into sugang(sno,hakno) values(1,'1001'); --잘된 예
insert into sugang(sno,hakno) values(1,'1004'); --못된 예
=============================================================
부모테이블 자식테이블
-------------------------------------------------------------
입력 상관없음 반드시 부모테이블에 있는
데이터가 들어간다
수정 수정하려는 데이터가 자식테이블에서 부모테이블에 존재하는
참조하고 있는지 검증 다른 데이터로 변경가능
삭제 삭제하려는 데이터를 자식테이블이 제약없음
참조하고 있는지를 검증
-------------------------------------------------------------
[관계형 데이터베이스]
개념:데이터베이스내의 테이블은 최소한의 의미(주제)를 가지고
테이블을 나누어서 구성하되, 그 테이블에 있는 컬럼을 연결한 것이다.
(부:PK-자:FK)
* 주제별 테이블은 서로 관계가 형성되는 총3가지가 있다.
1) 1:1 두 테이블간 어떤 테이블의 PK라도 다른 테이블에 전이되어도 상관없다.
2) 1:N 두 테이블간의 관계를 위해서 교차테이블을 만들 필요가 없다.
단, 1관계에 있는 PK를 N관계에 있는 테이블에 컬럼으로 전이시켜
관계를 맺는다.
3) N:M 두 테이블간의 관계를 위해서 교차테이블을 반드시 만든다.
제4정규화작업
* 부모테이블(1관계)의 PK가 자식테이블(N관계)의 FK로 전이된다.
* FK로 두 테이블간의 데이터 무결성을 강제할 수 있다.
* JOIN작업으로 원하는 작업을 처리할 수 있다.
=================================================================
- 뷰 View
테이블 전체를 보여 줘서는 안되는 경우, 보여줄 컬럼들만 가져오는 뷰를 만들어서
사용한다. 사용의 편의성, 보안상의 이유
이미 존재하는 혹은 그 이상의 테이블에서 원하는 데이터만 정확히 가져올 수
있도록 미리 원하는 컬럼만 모아 가상적으로 만든 테이블이다. 가상테이블
- 트리거 Trigger
자동화와 연결, 연쇄반응, 목적은 무결성의 원칙을 유지
- 인덱스 index
데이터베이스내의 테이블에서 원하는 정보를 좀더 빨리 찾아줄수 있게 데이터의
위치정보를 모아놓은 데이터베이스내의 개체.
빠르게 찾자. 조회를 빠르게 하자.
DB성능튜닝에서의 핵심기술.
* 인덱스는 정렬되어 있다.
1)사전자체가 인덱스 : 클러스터드clustered 인덱스
2)책뒤에 인덱스 페이지를 따로 만든다 : 넌클러스터드nonclustered 인덱스
=========================================================
create table 테이블명
create view 뷰이름
create trigger 트리거이름
create index 인덱스명
=========================================================
drop table 테이블명
drop view 뷰이름
drop trigger 트리거이름
drop index 인덱스명
=========================================================
* 포인트point 쿼리
조회되는 데이터가 한두개
select * from tb_member where id='kim5'
* 범위range 쿼리
조회되는 데이터가 다수
select * from tb_member where dong like '구로%'
* 커버드covered 쿼리
조회의 대상과 조회의 결과가 컬럼이 일치되는 형태
select id from tb_member where id='kim5'
select id,pw from tb_member where id='kim5' and pw='1234'
========================================================
select * from zipcode
create table w_zipcode
as
select * from zipcode --테이블 복사
인덱스형식)
create index 인덱스이름
on 테이블명 (칼럼명)
create index idx_zip
on w_zipcode(zipcode)
create unique index idx_zip
on w_zipcode(zipcode)
drop index idx_zip
select * from w_zipcode
drop table w_zipcode
* 테이블복사
MS-SQL 형식
select * into 새테이블 from 원본테이블
select * into w_zipcode from zipcode where zipcode='000-000'
--테이블 구조복사
오라클 형식
create table 새테이블
as
select * from 원본테이블
create table w_zipcode
as
select * from zipcode
create index zip_index
on w_zipcode(dong)
select * from zipcode where dong='개포1동';
select * from w_zipcode where dong='개포1동';
select * from w_zipcode where zipcode='135-805';
select * from w_zipcode where dong like '개포1%'
select * from w_zipcode where dong like '%개포1%' --비추천
* 자동인덱스
primary key 제약조건에 의해 자동적으로 생성되는 인덱스
클러스터드 인덱스, 사전인덱스
* 수동인덱스
create index명령을 실행해서 만드는 인덱스
넌클러스터드 인덱스, 인덱스 페이지 추가
* 인덱스를 생성하는 것이 좋은 컬럼
1)where조건절 또는 join 조건 안에서 자주 사용되는 컬럼
2)패턴조회시 쿼리문에 %는 뒤에 붙여야 성능발휘
3)where절에 사용되는 컬럼에서는 함수를 가급적 쓰지 말것
where day(regdt)=12
* 인덱스를 사용하지 말아야 할 컬럼
1)좀처럼 검색되지 않는컬럼
2)전체중 상당부분을 가져오는 질의에 사용되는 컬럼
3)같은값이 많은 컬럼