Transcript 슬라이드 1

The Korean Statistical Society
SQL 실습
안 정 용
전북대학교 수학∙통계정보과학부
SQL Workshop
1/30
SQL 개요
The Korean Statistical Society
• SQL(Structured Query Language) 이란?
•
DBMS 표준 언어
•
•
이용자(응용 프로그램)와 DBMS 사이의 중간 다리 역할 담당
데이터 정의, 제어, 조작 등의 기능 포함
SQL
SQL Workshop
Database
Management
System
•
고객정보 테이블에서 직업이 ‘학생’인 고객 검색
•
Select * from Customer_Info
where job=‘학생’
2/30
The Korean Statistical Society
• SQL 문장
· 데이터 정의문
- CREATE
- DROP
- ALTER
· 데이터 제어문
- GRANT
- DENY
- REVOKE
· 데이터 조작문
-
SQL Workshop
SELECT
INSERT
UPDATE
DELETE
· 데이터베이스 객체 생성, 삭제, 갱신
- CREATE DATABASE ‘데이터베이스_이름’
· 데이터베이스 객체 이용권한 부여
- GRANT CREATE TABLE
TO ‘사용자 계정’
· 데이터 검색, 삽입, 갱신, 삭제
- SELECT *
FROM ‘테이블_이름’
3/30
데이터 정의문
The Korean Statistical Society
• 데이터베이스 생성 및 삭제
· 데이터베이스 생성
예 : CREATE DATABASE TestDB
- TestDB 이름을 가진 데이터베이스 생성
- 데이터베이스의 크기, 저장될 파일 이름, 파일의 증가 단위 등을 지정할 수 있음
- 시스템에서 기본적으로 제공하는 것을 사용
· 데이터베이스 삭제
예 : DROP DATABASE TestDB
- TestDB 이름을 가진 데이터베이스 삭제
SQL Workshop
4/30
The Korean Statistical Society
• 테이블 생성 및 삭제
· 테이블 생성
CREATE TABLE
(
필드_이름
필드_이름
…
필드_이름
)
테이블_이름
데이터_타입
데이터_타입
[제약조건] ,
[제약조건] ,
데이터_타입
[제약조건]
- 데이터_타입
SQL Workshop
구분
데이터 타입
크기(byte)
구분
데이터 타입
크기(byte)
4
2
1
1
날짜
datetime
smalldatetime
정수
int
smallint
tinyint
bit
문자
char, varchar
nchar, nvarchar
text
0~8000
0~4000
0~2GB
고정소수
numeric
decimal
2~17
이진
binary
image
0~8000
0~2GB
부동소수
float
real
8
4
화폐
money
smallmoney
8
4
8
4
5/30
The Korean Statistical Society
- 제약조건
구분
제약조건
기본 키 설정
primary key
외래 키 설정
foreign key references 참조_테이블_이름(참조_필드)
널 값 허용하지 않을 때
not null
동일한 데이터 입력 방지
unique
기본 값 설정
default ‘기본_값’
- 예 : 고객테이블
SQL Workshop
속성
속성명
데이터 타입
길이
제약사항
고객번호
CID
char
6
PK
고객이름
Name
char
10
not null
주소
Address
char
20
전화번호
Tel
char
15
생년월일
Bday
datetime
주민등록번호
SID
char
14
직업
Job
char
10
성별
Sex
char
1
결혼여부
Married
bit
not null
not null
not null
6/30
The Korean Statistical Society
- 예 : 고객테이블 생성
CREATE TABLE Customer_Info
(
CID
char(6)
PRIMARY
KEY,
Name
char(10)
not null,
Address char(20)
,
Tel
char(15)
,
Bday
datetime
not null,
SID
char(14)
not null,
Job
char(10)
,
Sex
char(1)
not null,
Married
bit
)
SQL Workshop
CREATE TABLE Customer_Info
(
CID
char(6)
not null,
Name
char(10)
not null,
Address char(20)
,
Tel
char(15)
,
Bday
datetime
not null,
SID
char(14)
not null,
Job
char(10)
,
Sex
char(1)
not null,
Married
bit,
PRIMARY KEY (CID)
)
7/30
The Korean Statistical Society
· 테이블 변경 : 필드 추가/제거, 데이터 타입 또는 크기 변경
예 : ALTER TABLE Customer_Info
ALTER COLUMN NAME char(20)
- Customer_Info 테이블의 Name 필드의 크기를 20으로 변경
· 테이블 제거
예 : DROP TABLE Customer_Info
- Customer_Info 테이블 제거
※ 기본 키나 외래 키가 포함되면 삭제 순서를 고려
- Customer_OnlineInfo, Customer_Info에서 삭제 순서는?
DROP TABLE Customer_OnlineInfo
DROP TABLE Customer_Info
SQL Workshop
8/30
실습 환경
The Korean Statistical Society
• 실습 환경
•
•
Server Computer, DBMS : COMPSTAT 서버 이용(전북대학교)
Client 환경 : Web browser 이용
• 테이블 구성
SQL Workshop
9/30
The Korean Statistical Society
• 예제 데이터
- Table : Sales
- Table : Customer_Info
CID
Name
Address
100001
100002
100003
100004
100005
100006
:
김민주
정영미
최소영
박진재
이순석
박진희
:
서울시..
경기도..
제주도..
강원도..
충남..
서울시..
:
Tel
Bday
423-8627 75-7-1
321-4568 77-2-1
624-2172 80-1-25
543-9467 60-5-2
254-9124 82-6-1
459-2187 75-4-5
:
:
SID
Job
Sex
Married
75..
77..
80..
60..
82..
75..
:
공무원
회사원
학생
공무원
회사원
회사원
:
1
1
1
0
0
1
:
0
1
0
1
0
0
:
- Table : Customer_OnlineInfo
CID
LID
LPW
EMail
Jday
100001
100002
100003
100005
100006
100008
:
champ
gimme
mac001
black
soyoung
jini
:
cham999
sweet
pluto
white007
soso
movie
:
[email protected]
[email protected]
[email protected]
[email protected]
2000/2/1
2000/3/4
2000/4/1
2000/1/10
2000/2/2
2000/7/2
:
[email protected]
:
SaleID PID
1
2
3
4
5
6
7
8
9
:
1240
1220
1230
1240
1250
3010
1210
1240
1230
:
SID
CID
SDay
Amount
211
211
211
211
211
211
211
211
211
:
100001
100001
100006
100002
100006
100011
100010
100002
100010
:
2000/3/3
2000/4/1
2000/4/4
2000/4/5
2000/4/6
2000/4/9
2000/4/11
2000/4/11
2000/4/12
:
18000
13000
5000
18000
10000
30000
21000
18000
5000
:
• 실습 사이트
•
SQL Workshop
compstat.chonbuk.ac.kr/dbbook
10/30
데이터 조작문
The Korean Statistical Society
• 데이터 검색 : SELECT 문
· 여러 테이블을 조합해서 사용자가 원하는 필드와 레코드를 추출
· 일반적인 형식
SELECT [DISTINCT] 필드_리스트
FROM 테이블_이름
[WHERE 조건]
[GROUP BY 필드_리스트]
[HAVING 조건]
[ORDER BY 필드_리스트 [ASC|DESC] ]
- SELECT
: 검색하고자 하는 필드 이름 명시
- FROM
: 검색 테이블 이름 명시
- WHERE
: 검색할 데이터의 검색 조건 지정
- GROUP BY : 그룹화(분할) 하고자 하는 필드 이름 명시
- HAVING
: GROUP BY에서 명세된 그룹에 대한 검색 조건 명시
- ORDER BY : 정렬하고자 하는 기준 필드 이름 명시
SQL Workshop
11/30
The Korean Statistical Society
(1) 테이블의 일부 또는 전체 필드 검색
- 예 : 테이블 Customer_Info 에서 고객번호, 이름, 직업 필드의 데이터 검색
SELECT CID, Name, Job
FROM Customer_Info
- 예 : 테이블 Customer_OnlineInfo 에서 전체 필드의 데이터 검색
SELECT ( all ) *
FROM Customer_OnlineInfo
또는
SELECT CID, LID, LPW, Email, Jday
FROM Customer_OnlineInfo
SQL Workshop
12/30
The Korean Statistical Society
(2) 레코드의 중복 제거 : DISTINCT
- 예 : 제품 구입을 한 고객 모두 검색
Sales 테이블의 고객번호들을 중복 없이 검색
SELECT DISTINCT CID
FROM Sales
(3) 레코드의 조건 검색 : WHERE
- 예1 : Customer_Info 테이블에서 직업이 ‘회사원’인 고객의 고객번호(CID), 이름(Name),
직업(job), 결혼여부(married) 검색
SELECT CID, Name, Job, Married
FROM Customer_Info
WHERE Job = '회사원'
SQL Workshop
13/30
The Korean Statistical Society
- 예2 : Customer_OnlineInfo 테이블에서 전자우편주소(Email)가 널 값이 아닌 레코드 검색
SELECT *
FROM Customer_OnlineInfo
WHERE Email is not NULL
- 예3 : Customer_Score 테이블에서 Score가 100 이상이고 200 이하인 레코드 검색
SELECT *
FROM Customer_Score
WHERE Score>=100 and Score<=200
※ 비교, 논리 연산자
SQL Workshop
비교연산자
의미
논리연산자
의미
=
같다
AND
논리곱
>
크다
OR
논리합
<
작다
NOT
부정
>=
크거나 같다
<=
작거나 같다
<>
같지 않다
14/30
The Korean Statistical Society
- 예4 : LIKE 연산자 이용
· 특정 패턴의 문자열 검색
SELECT *
FROM Customer_Info
WHERE Name LIKE '김%'
SELECT *
FROM Customer_OnlineInfo
WHERE Email LIKE '%hotmail%'
- 예5 : IN 연산자 이용
· 검색하고자 하는 값의 집합 이용
SELECT CID, Name, Job
FROM Customer_Info
WHERE Job IN ('회사원', '공무원', '학생')
SQL Workshop
15/30
The Korean Statistical Society
(4) 데이터의 그룹화 : GROUP BY
- 특정 필드의 값에 따라 데이터 그룹화, 일반적으로 SQL 함수와 같이 사용
- 예 : Customer_Info 테이블에서 직업별 고객 빈도수 검색
SELECT
Job, count(*) as freq
FROM
Customer_Info
GROUP BY Job
(5) 데이터 그룹화의 조건검색 : HAVING
- 각 그룹에 대한 조건 검색
- HAVING 절 다음에는 SELECT 다음에 명시된 필드(또는 함수)에 대해서만 조건을 줄 수
있음
- 예 : Sales 테이블에서 2001년 4월의 고객별 구매회수가 20회 이상인 고객들만 검색
SELECT
CID, count(*) as freq
FROM
Sales
WHERE
year(SDay)=2001 and month(SDay)=4
GROUP BY CID
HAVING count(*) >= 20
SQL Workshop
16/30
The Korean Statistical Society
(6) 출력 순서 명시 : ORDER BY
- 질의 결과의 레코드 정렬
- 예 : Customer_Info 테이블에서 고객들을 생년월일 순으로 검색
SELECT
CID, Name, Bday
FROM
Customer_Info
ORDER BY Bday ( ASC )
(7) 상위 레코드 선택 : SELECT TOP
- 예 : Customer_OnlineInfo 테이블에서 2000년 이후 가입한 고객 중 먼저 가입한 5명 검색
SELECT TOP 5 *
FROM
Customer_OnlineInfo
WHERE
year(Jday) >= 2000
ORDER BY Jday
SQL Workshop
17/30
The Korean Statistical Society
(8) 여러 테이블에서 데이터 검색 : JOIN
- 관계형 데이터베이스 설계 시에 데이터의 중복을 피하고 데이터의 정확성을 높이기 위해
개체와 관계를 여러 테이블로 나누어 설계
- 따라서 여러 테이블을 조합해야 원하는 데이터 검색할 수 있음
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
SQL Workshop
18/30
The Korean Statistical Society
① INNER JOIN
- 기본적인 조인형태, 조인하고자 하는 테이블을 연결하는 필드의 값이 같은 레코드 선택
- 예 : 테이블 Customer_Info, Customer_OnlineInfo 에서 고객번호, 이름, Login ID,
전자우편주소를 고객번호 순으로 검색
SELECT
FROM
INNER JOIN
ON
ORDER BY
또는
SELECT
FROM
WHERE
ORDER BY
또는
SELECT
FROM
WHERE
ORDER BY
SQL Workshop
Customer_Info.CID, Name, LID, EMail
Customer_Info
Customer_OnlineInfo
Customer_Info.CID= Customer_OnlineInfo.CID
Customer_Info.CID
Customer_Info.CID, Name, LID, EMail
Customer_Info, Customer_OnlineInfo
Customer_Info.CID= Customer_OnlineInfo.CID
Customer_Info.CID
C.CID, Name, LID, EMail
Customer_Info AS C, Customer_OnlineInfo AS CO
C.CID= CO.CID
C.CID
19/30
The Korean Statistical Society
② OUTER JOIN
- 조인할 때 기준테이블을 설정하고, 기준테이블의 모든 레코드와 다른 테이블에서
조인 조건을 만족하는 레코드 선택
- 기준테이블의 위치에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN 등이 있음
- 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( LEFT JOIN )
SELECT
FROM
LEFT JOIN
ON
또는
SELECT
FROM
WHERE
C.CID, Name, LID, EMail
Customer_Info AS C
Customer_OnlineInfo AS CO
C.CID= CO.CID
C.CID, Name, LID, EMail
Customer_Info AS C, Customer_OnlineInfo AS CO
C.CID *= CO.CID
- 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( RIGHT JOIN )
SELECT
FROM
WHERE
SQL Workshop
C.CID, Name, LID, EMail
Customer_OnlineInfo AS CO, Customer_Info AS C
CO.CID =* C.CID
20/30
The Korean Statistical Society
(9) 중첩 질의
- WHERE 절에 완전한 하나의 SELECT 질의문이 포함되어 있는 형태
- 어떤 테이블내에 존재하는 레코드를 검색한 다음 이를 비교 조건에서 사용
- 예 : Customer_Info 테이블에도 존재하고, Customer_Score 테이블에도 존재하는
고객(온라인 고객이면서 구매 실적이 있는 고객)의 명단 검색
SELECT CID, Name
FROM Customer_Info as C
WHERE EXISTS ( SELECT *
FROM Customer_OnlineInfo as CO
WHERE C.CID = CO.CID )
AND EXISTS ( SELECT *
FROM Customer_Score as SC
WHERE C.CID = SC.CID )
SQL Workshop
21/30
The Korean Statistical Society
• 데이터 삽입 : INSERT 문
· 테이블에 새로운 레코드 삽입
· 일반적인 형식
INSERT (INTO) [ ( 필드_이름 [ , … n ] ) ]
VALUES ( 데이터 [ , … n ] )
- 예1 : Customer_Info 테이블에 고객 번호, 고객 이름, 생년월일, 주민등록번호, 성별이
(100005, '김철수', '82-6-1', '820601-1450512', 1) 인 레코드 삽입
INSERT INTO Customer_Info (CID, Name, Bday, SID, Sex)
VALUES ('100005', '김철수', 82-6-1, '820601-1450512', 1)
- 예2 : Customer_Info 테이블에서 남자 고객만 Customer_Male 테이블로 복사
INSERT INTO Customer_Male
( SELECT CID, Name, Address, Tel, Sex
FROM Customer_Info
WHERE Sex=0 )
SQL Workshop
22/30
The Korean Statistical Society
• 데이터 갱신 및 삭제 : UPDATE 문, DELETE 문
- 예1 : Customer_Info 테이블에서 고객번호가 100005인 고객의 전화번호를 '254-4567',
직업을 '회사원'으로 수정
UPDATE Customer_Info
SET
Tel='254-4567', Job='회사원'
WHERE CID='100005'
- 예2 : Customer_Info 테이블에서 고객번호가 100005인 레코드를 삭제
DELETE Customer_Info
WHERE CID='100005'
SQL Workshop
23/30
SQL 함수
The Korean Statistical Society
• 스칼라(Scalar) 함수
(1) 수학 함수
함 수
의 미
ABS(numeric_expr)
절대값
ACOS,ASIN,ATAN,ATN2,COS,
COT,SIN,TAN(float_expr)
삼각함수 관련 값
CEILING, FLOOR(numeric_expr)
ROUND(numeric_expr, length)
올림, 내림
반올림
EXP(float_expr)
LOG, LOG10(float_expr)
지수
자연로그, 로그 값
RAND([seed])
0에서 1사이의 난수
POWER(numeric_expr, length)
SQRT(float_expr)
승
자연로그 값
- 예 : 0과 1사이의 난수 생성
SELECT RAND() as RN
SQL Workshop
24/30
The Korean Statistical Society
(2) 문자열 함수
함
수
의
미
LEN(string_expr)
문자열의 길이(즉 문자의 개수)
LOWER, UPPER(char_expr)
소문자, 대문자로 변환
LTRIM, RTRIM(char_expr)
문자열 좌측, 우측 공백 제거
REPLACE(expr1,expr2, expr3)
expr1에서 expr2 문자열을 찾아 expr3 문자열로 바꿔준다
LEFT,RIGHT(str_expr, int_expr)
문자열의 왼쪽, 오른쪽에서부터 정해진 자리수 만큼의 부분
STR(float_expr[,length[,decimal]])
숫자 값을 문자열로 변환
- 예 : Customer_OnlineInfo 테이블에서 LPW 필드의 길이 검색
SELECT CID, LEN(LPW) as LengthofPassword
FROM Customer_OnlineInfo
SQL Workshop
25/30
The Korean Statistical Society
(3) 날짜 함수
함
수
의
미
DATEADD(datepart, number, date)
datapart 자리에 숫자를 더한다
DATEDIFF(datepart, date1, date2)
두 날짜간의 차이를 나타낸다
GETDATE()
시스템의 날짜와 시각을 반환한다
YEAR, MONTH, DAY(date)
날짜에서 년, 월, 일을 반환한다
- 예 : Customer_Info 테이블에서 고객들의 나이를 내림차순으로 검색
SELECT
CID, Name, year(GETDATE())-year(Bday) as Age
FROM
Customer_Info
ORDER BY Age DESC
SQL Workshop
26/30
The Korean Statistical Society
• 통계 관련 함수
(1) 집계 함수
함
수
의
미
AVG
SUM
평균값
합계
COUNT
COUNT(*)
전체 레코드 수
NULL 값을 포함한 전체 레코드 수
MIN
MAX
최소값
최대값
STDEV
STDEVP
표본 표준편차
모집단 표준편차
VAR
VARP
분산
모집단 분산
- 예 : Customer_Info 테이블의 전체 레코드 수, 고객 나이의 표준편차를 계산
SELECT
COUNT(*) as countAll,
STDEV( year(GETDATE())-year(Bday)) as sampleSTDEV
STDEVP( year(GETDATE())-year(Bday)) as popSTDEV
FROM Customer_Info
SQL Workshop
27/30
The Korean Statistical Society
(2) 다차원 분석 연산자 : ROLLUP, CUBE, COMPUTE
- GROUP BY 와 함께 데이터에 대한 2차적 요약
- 예 : Customer_Info 테이블과 Sales 테이블을 JOIN 하여 고객별, 제품별 평균 구매액 산출
SELECT
FROM
INNER JOIN
ON
GROUP BY
ORDER BY
SQL Workshop
Name, PID, AVG(Amount) as averAmount
Customer_Info as C
Sales as S
C.CID=S.CID
Name, PID WITH CUBE
Name
28/30
SQL 활용 예
The Korean Statistical Society
• ASP : 고객정보 입력
<html>
<%
userId
Password
userName
:
email
= Trim(Request("userid"))
= Trim(Request("password"))
= Trim(Request("username"))
:
= Trim(Request("email"))
사용자 입력 데이터
sql= " Insert into db_Users Values ( '"&userId&"' ,'"&Password&"', '"&userName&"', … ,
'"&email&"' )"
Set DBConn = Server.CreateObject("ADODB.CONNECTION")
DBConn.Open Session("Dsn")
DB 연결
SQL 문장
DBConn.Execute sql
Dbconn.Close
Set Dbconn=Nothing
SQL 문 실행
%>
</html>
SQL Workshop
29/30
The Korean Statistical Society
• ASP : 로그인
<html>
<%
사용자 입력 데이터
userID = Trim(Request.Form("UserID"))
userPW = Trim(Request.Form("Password"))
sql = "Select * FROM db_Users "
sql = sql & " WHERE LID = '"&userID&"' and LPW='"&userPW&"' "
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, Session("Dsn")
if not rs.EOF then
Response.Redirect "../start.asp"
else
Response.redirect "error.asp"
end if
SQL 문장
DB 연결 및 SQL 문 실행
‘사용자가 존재하면
‘사용자가 존재하지 않으면
' 사용자가 존재하지 않으면
%>
</html>
SQL Workshop
30/30