slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
전북대학교 수학 ∙통계정보과학부 PowerPoint Presentation
Download Presentation
전북대학교 수학 ∙통계정보과학부

Loading in 2 Seconds...

play fullscreen
1 / 30

전북대학교 수학 ∙통계정보과학부 - PowerPoint PPT Presentation


  • 268 Views
  • Uploaded on

전북대학교 수학 ∙통계정보과학부. SQL 실습. 안 정 용. DBMS 표준 언어 이용자 ( 응용 프로그램 ) 와 DBMS 사이의 중간 다리 역할 담당 데이터 정의 , 제어 , 조작 등의 기능 포함. Database Management System. 고객정보 테이블에서 직업이 ‘ 학생 ’ 인 고객 검색 Select * from Customer_Info where job= ‘ 학생 ’. SQL. SQL 개요. SQL (Structured Query Language) 이란 ?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '전북대학교 수학 ∙통계정보과학부' - garrison-munoz


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

DBMS 표준 언어

  • 이용자(응용 프로그램)와 DBMS 사이의 중간 다리 역할 담당
  • 데이터 정의, 제어, 조작 등의 기능 포함

Database

Management

System

  • 고객정보 테이블에서 직업이 ‘학생’인 고객 검색
  • Select * from Customer_Info
  • where job=‘학생’

SQL

SQL 개요

  • SQL(Structured Query Language)이란?
slide3

·데이터 정의문

- CREATE

- DROP

- ALTER

·데이터 제어문

- GRANT

- DENY

- REVOKE

·데이터 조작문

- SELECT

- INSERT

- UPDATE

- DELETE

·데이터베이스 객체 생성, 삭제, 갱신

- CREATE DATABASE ‘데이터베이스_이름’

·데이터베이스 객체 이용권한 부여

- GRANT CREATE TABLE

TO ‘사용자 계정’

·데이터 검색, 삽입, 갱신, 삭제

- SELECT *

FROM ‘테이블_이름’

  • SQL 문장
slide4

데이터 정의문

  • 데이터베이스 생성 및 삭제

·데이터베이스 생성

예 : CREATE DATABASE TestDB

- TestDB 이름을 가진 데이터베이스 생성

- 데이터베이스의 크기, 저장될 파일 이름, 파일의 증가 단위 등을 지정할 수 있음

- 시스템에서 기본적으로 제공하는 것을 사용

·데이터베이스 삭제

예 : DROP DATABASE TestDB

- TestDB 이름을 가진 데이터베이스 삭제

slide5

테이블 생성 및 삭제

·테이블 생성

CREATE TABLE테이블_이름

(

필드_이름 데이터_타입 [제약조건] ,

필드_이름 데이터_타입 [제약조건] ,

필드_이름 데이터_타입 [제약조건]

)

- 데이터_타입

slide6

- 제약조건

- 예 : 고객테이블

slide7

- 예 : 고객테이블 생성

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

)

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)

)

slide8

·테이블 변경 : 필드 추가/제거, 데이터 타입 또는 크기 변경

예 : 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

slide9

실습 환경

  • 실습 환경
  • Server Computer, DBMS : COMPSTAT 서버 이용(전북대학교)
  • Client 환경 : Web browser 이용
  • 테이블 구성
slide10

예제 데이터

- Table : Sales

- Table : Customer_Info

- Table : Customer_OnlineInfo

  • 실습 사이트
  • compstat.chonbuk.ac.kr/dbbook
slide11

데이터 조작문

  • 데이터 검색 : SELECT 문

·여러 테이블을 조합해서 사용자가 원하는 필드와 레코드를 추출

·일반적인 형식

SELECT [DISTINCT] 필드_리스트

FROM테이블_이름

[WHERE 조건]

[GROUP BY 필드_리스트]

[HAVING 조건]

[ORDER BY 필드_리스트 [ASC|DESC] ]

- SELECT : 검색하고자 하는 필드 이름 명시

- FROM : 검색 테이블 이름 명시

- WHERE : 검색할 데이터의 검색 조건 지정

- GROUP BY : 그룹화(분할) 하고자 하는 필드 이름 명시

- HAVING : GROUP BY에서 명세된 그룹에 대한 검색 조건 명시

- ORDER BY : 정렬하고자 하는 기준 필드 이름 명시

slide12

(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

slide13

(2) 레코드의 중복 제거 : DISTINCT

- 예 : 제품 구입을 한 고객 모두 검색

Sales 테이블의 고객번호들을 중복 없이 검색

SELECT DISTINCT CID

FROMSales

(3) 레코드의 조건 검색 : WHERE

- 예1 : Customer_Info 테이블에서 직업이 ‘회사원’인 고객의 고객번호(CID), 이름(Name),

직업(job), 결혼여부(married) 검색

SELECT CID, Name, Job, Married

FROMCustomer_Info

WHERE Job = '회사원'

slide14

- 예2 : Customer_OnlineInfo 테이블에서 전자우편주소(Email)가 널 값이 아닌 레코드 검색

SELECT*

FROM Customer_OnlineInfo

WHEREEmail is not NULL

- 예3 : Customer_Score 테이블에서 Score가 100 이상이고 200 이하인 레코드 검색

SELECT*

FROM Customer_Score

WHEREScore>=100 and Score<=200

※ 비교, 논리 연산자

slide15

- 예4 : LIKE 연산자 이용

·특정 패턴의 문자열 검색

SELECT* SELECT *

FROM Customer_Info FROM Customer_OnlineInfo

WHEREName LIKE'김%'WHERE Email LIKE '%hotmail%'

- 예5 : IN 연산자 이용

·검색하고자 하는 값의 집합 이용

SELECTCID, Name, Job

FROM Customer_Info

WHEREJob IN ('회사원', '공무원', '학생')

slide16

(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회 이상인 고객들만 검색

SELECTCID, count(*) as freq

FROM Sales

WHERE year(SDay)=2001 and month(SDay)=4

GROUP BY CID

HAVING count(*) >= 20

slide17

(6) 출력 순서 명시 : ORDER BY

- 질의 결과의 레코드 정렬

- 예 : Customer_Info 테이블에서 고객들을 생년월일 순으로 검색

SELECT CID, Name, Bday

FROM Customer_Info

ORDER BY Bday ( ASC )

(7) 상위 레코드 선택 : SELECT TOP

- 예 : Customer_OnlineInfo 테이블에서 2000년 이후 가입한 고객 중 먼저 가입한 5명 검색

SELECTTOP5 *

FROM Customer_OnlineInfo

WHERE year(Jday) >= 2000

ORDER BY Jday

slide18

(8) 여러 테이블에서 데이터 검색 : JOIN

- 관계형 데이터베이스 설계 시에 데이터의 중복을 피하고 데이터의 정확성을 높이기 위해

개체와 관계를 여러 테이블로 나누어 설계

- 따라서 여러 테이블을 조합해야 원하는 데이터 검색할 수 있음

- INNER JOIN

- OUTER JOIN

- CROSS JOIN

slide19

① INNER JOIN

- 기본적인 조인형태, 조인하고자 하는 테이블을 연결하는 필드의 값이 같은 레코드 선택

- 예 : 테이블 Customer_Info, Customer_OnlineInfo 에서 고객번호, 이름, Login ID,

전자우편주소를 고객번호 순으로 검색

SELECT Customer_Info.CID, Name, LID, EMail

FROM Customer_Info

INNER JOIN Customer_OnlineInfo

ON Customer_Info.CID= Customer_OnlineInfo.CID

ORDER BY Customer_Info.CID

또는

SELECT Customer_Info.CID, Name, LID, EMail

FROM Customer_Info, Customer_OnlineInfo

WHERE Customer_Info.CID= Customer_OnlineInfo.CID

ORDER BY Customer_Info.CID

또는

SELECT C.CID, Name, LID, EMail

FROM Customer_Info AS C, Customer_OnlineInfo AS CO

WHERE C.CID= CO.CID

ORDER BY C.CID

slide20

② OUTER JOIN

- 조인할 때 기준테이블을 설정하고, 기준테이블의 모든 레코드와 다른 테이블에서

조인 조건을 만족하는 레코드 선택

- 기준테이블의 위치에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN 등이 있음

- 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( LEFT JOIN )

SELECTC.CID, Name, LID, EMail

FROMCustomer_Info ASC

LEFT JOINCustomer_OnlineInfo AS CO

ONC.CID= CO.CID

또는

SELECTC.CID, Name, LID, EMail

FROMCustomer_Info ASC, Customer_OnlineInfo AS CO

WHEREC.CID *= CO.CID

- 예 : 고객 정보에 온라인 정보를 추가해서 검색하고자 할 때 ( RIGHT JOIN )

SELECTC.CID, Name, LID, EMail

FROMCustomer_OnlineInfo ASCO, Customer_Info AS C

WHERECO.CID =* C.CID

slide21

(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 )

slide22

데이터 삽입 : 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

FROMCustomer_Info

WHERE Sex=0 )

slide23

데이터 갱신 및 삭제 : 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'

slide24

SQL 함수

  • 스칼라(Scalar) 함수

(1) 수학 함수

- 예 : 0과 1사이의 난수 생성

SELECT RAND() as RN

slide25

(2) 문자열 함수

- 예 : Customer_OnlineInfo 테이블에서 LPW 필드의 길이 검색

SELECT CID, LEN(LPW) as LengthofPassword

FROM Customer_OnlineInfo

slide26

(3) 날짜 함수

- 예 : Customer_Info 테이블에서 고객들의 나이를 내림차순으로 검색

SELECTCID, Name, year(GETDATE())-year(Bday) as Age

FROMCustomer_Info

ORDER BY Age DESC

slide27

통계 관련 함수

(1) 집계 함수

- 예 : Customer_Info 테이블의 전체 레코드 수, 고객 나이의 표준편차를 계산

SELECT COUNT(*) as countAll,

STDEV( year(GETDATE())-year(Bday)) as sampleSTDEV

STDEVP( year(GETDATE())-year(Bday)) as popSTDEV

FROM Customer_Info

slide28

(2) 다차원 분석 연산자 : ROLLUP, CUBE, COMPUTE

- GROUP BY 와 함께 데이터에 대한 2차적 요약

- 예 : Customer_Info 테이블과 Sales 테이블을 JOIN 하여 고객별, 제품별 평균 구매액 산출

SELECT Name, PID, AVG(Amount) as averAmount

FROM Customer_Info as C

INNER JOIN Sales as S

ON C.CID=S.CID

GROUP BY Name, PID WITH CUBE

ORDER BY Name

slide29

SQL 활용 예

  • ASP : 고객정보 입력

<html>

<%

userId = Trim(Request("userid"))

Password = Trim(Request("password"))

userName = Trim(Request("username"))

: :

email = Trim(Request("email"))

sql= " Insert into db_Users Values ( '"&userId&"' ,'"&Password&"', '"&userName&"', … , '"&email&"' )"

Set DBConn = Server.CreateObject("ADODB.CONNECTION")

DBConn.Open Session("Dsn")

DBConn.Execute sql

Dbconn.Close

Set Dbconn=Nothing

%>

</html>

사용자 입력 데이터

SQL 문장

DB 연결

SQL 문 실행

slide30

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

' 사용자가 존재하지 않으면

%>

</html>

사용자 입력 데이터

SQL 문장

DB 연결 및 SQL 문 실행