3 basic sql structured query language n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
3. Basic SQL (Structured Query Language) PowerPoint Presentation
Download Presentation
3. Basic SQL (Structured Query Language)

Loading in 2 Seconds...

play fullscreen
1 / 58

3. Basic SQL (Structured Query Language) - PowerPoint PPT Presentation


  • 267 Views
  • Uploaded on

3. Basic SQL (Structured Query Language). 서울시립대학교 전자전기컴퓨터공학부 김한준. SQL (Structured Query Language). Structured Query Language IBM's System R project : Sequel 현재 가장 널리 쓰이는 relational query language. Relational algebra 나 calculus 는 확실한 이론적 배경을 제공하나 상용으로 쓰이기에는 적절치 않음 .

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

3. Basic SQL (Structured Query Language)


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
3 basic sql structured query language

3. Basic SQL (Structured Query Language)

서울시립대학교

전자전기컴퓨터공학부

김한준

sql structured query language
SQL (Structured Query Language)
  • Structured Query Language
  • IBM's System R project : Sequel
  • 현재 가장 널리 쓰이는 relational query language.
  • Relational algebra나 calculus는 확실한 이론적 배경을 제공하나 상용으로 쓰이기에는 적절치 않음.
    • Relational Algebra : Procedural
    • Relational Calculus : First order logic
  • SQL is declarative.
slide3
입문 예제
  • 테이블 생성 ,입력 후 name = “DATA MINING”인 정보를 출력하여라.
    • Create table book (
      • Isbnvarchar(40),
      • Name varchar(100)
      • );
    • Insert into book values(“0-324-32136-7”, “DATA MINING”);
    • Insert into book values(“1-323-45732-1”, “ALGORITHMS”);
    • Select * from book where name = “DATA MINING”;
  • 실행 결과
slide5
DDL언어
  • 데이터베이스 내에 객체를 생성하고 변경하고 삭제하기 위해 사용되며 DDL언어는 6가지 유형이 있다.
    • CREATE
      • 데이터베이스 내의 모든 객체를 생성할 때 사용함.
        • 문법
    • ALTER
      • 이미생성된 객체의 구조를 변경할 때 사용됨.
        • 문법
    • DROP
      • 생성되어 있는 객체를 삭제할 때 사용됨.
        • 문법
    • RENAME
      • 기존의 파일명을 다른 이름으로 변경할 때 사용되는 명령어.
        • 문법

CREATE TABLE 테이블명(

컬럼명1 데이터타입,

컬럼명2 데이터타입,

);

ALTER TABLE 테이블명

ADD(추가하고자하는컬럼, 데이터 타입)

DROP 테이블명

ALTER TABLE 테이블명 RENAME COLUMN

현재컬럼명 TO 새로운컬럼명

create
연습: CREATE절
  • Student table 만들기
  • Employee table 만들기
  • [우측란에는 실행결과를 보여줌]
  • 보충설명

create table student(

Id int,

Name varchar(30),

Address varchar(100),

mobilnumbervarchar(50)

);

create table student(

empidint,

Name varchar(30),

Address varchar(100),

Salary int

);

alter
연습 : ALTER절
  • 이미 만들어진 Student table에 grade컬럼 삽입

alter table student ADD(

Grade int);

<Alter실행 전>

<Alter실행 후>

dml data manipulation language
DML(Data manipulation language)
  • 테이블에 새로운 행을 추가, 변경, 삭제 하기 위해서 사용되며 DML언어는 3가지 유형이 있다.
    • INSERT
      • 테이블에 데이터를 저장할 때 사용.
        • 문법
    • UPDATE
      • 테이블에 저장되어 있는 데이터 변경시 사용.
        • 문법
    • DELETE
      • 테이블에 저장되어 있는 데이터를 삭제할 때 사용.
        • 문법

INSERT INTO 테이블 [(컬럼1,컬럼2…)]

VALUES (컬럼1의값, 컬럼2의 값…);

테이블 리스트에 있는 컬럼 개수와 VALUES절의 값 개수는 같아야 함.

[ ]부분은 생략 가능.

UPDATE 테이블

SET 컬럼1 = 변경될값1[,컬럼2 = 변경될값2]

DELETE [FROM] 테이블

SET 컬럼1 = 변결될값1

insert update
연습 :INSERT절, UPDATE절

Student table 에 Jane의 정보 입력(INSERT)

INSERT INTO STUDETN

VALUES (22, “Jane”,”LA”,”000-222-0987”,3);

<INSERT실행 전>

  • Student table의Tedy의 Grade정보 변경(UPDATE)

<INSERT실행 후>

UPDATE STUDENT

SET GRADE = 2

WHERE ID = 11;

<UPDATE실행 전>

WHERE절은 조건절로 생략하면 전체 행이 변경된다.

<UPDATE실행 후>

delete
연습: DELETE절
  • Student table의 Jane의 정보 삭제

DELETE STUDENT

WHERE ID = 22;

<UPDATE실행 전>

WHERE절은 조건절로 생략하면 전체 행이 삭제된다.

<UPDATE실행 후>

dql data query language
이론 : DQL(Data Query language)
  • SELECT
    • 데이터베이스 내의 테이블로부터 데이터를 조회할 때 사용.
      • 문법
  • DISTINCT 키워드
    • 중복되는 값은 하나만 출력.
  • ORDER BY
    • 지정된 컬럼을 기준으로 분류하여 출력
      • 문법
  • WHERE
    • FROM절에 의해 검색된 데이터 중에서 조건에 맞는 ROW들만 제한하여 검색

SELECT [ DISTINCT]{*, 컬럼…}

FROM 테이블명

[WHERE 조건]

SELECT:원하는 컬럼을 선택

FROM:원하는 데이터가 저장된 테이블명을 기술

WHERE:조회되는 행을 선택

SELECT [ DISTINCT]{*, 컬럼…}

FROM 테이블명

ORDER BY [컬럼명1],[컬럼명2]..[ASC/DESC]

select
연습: SELECT절
  • Student table 모든 정보 출력
  • Student table의 name과 grade컬럼 출력

SELECT *

FROM STUDENT;

SELECT name, grade

FROM STUDENT;

distinct
연습: DISTINCT절

SELECT DISTINCT Grade

FROM STUDENT;

Grade값 중 중복이 되는 3은 한번만 출력되게 된다.

<DISTINCT 실행 후>

order by
연습: ORDER BY절
  • [우측란에는 실행결과를 보여줌]

SELECT *

FROM STUDENT

ORDER BY Grade DESC;

Student의 모든 값을 Grade의 내림 차순으로 출력,

ORDER BY는 default값이 오름차순임

<내림차순 정렬 후>

order by1
연습: ORDER BY절
  • [우측란에는 실행결과를 보여줌]

SELECT *

FROM STUDENT

ORDER BY Grade;

Student의 모든 값을 Grade의 오름 차순으로 출력,

ORDER BY는 default값이 오름차순임

<오름차순 정렬 후>

where
연습: WHERE 절

SELECT *

FROM STUDENT

WHERE Grade = 2;

Student의 Grade가 2인 모든 값을 출력

select1
Select 문의 기본 구조
  • SQL은 집합과 수정 및 강화된 관계형 연산에 기초를 두고 있다.
  • 전형적인 SQL 질의는 다음과 같은 형식을 갖는다.

select A1, A2, , An

from r1, r2, , rm

where P

- Ai 는 애트리뷰트이다.

- ri는 릴레이션이다.

- P는 술어이다.

  • 이 질의는 다음 관계형 대수 표현식과 동등하다

A1, A2, , An (P(r1  r2   rm))

  • SQL 질의의 결과는 릴레이션이다.
select2
Select 문의 기본 구조
  • WHERE clause

SELECT loan#

FROM loan

WHERE amount > 1200

 loan# (amount>1200(loan))

    • AND, OR, NOT, () 등을 이용해 조건 구성
select select 1 3
Select 문의 기본 구조: Select 절 (1/3)
  • select절은 관계형 대수의 추출 연산에 대응한다. 질의의 결과로 바라는 애트리뷰트를 나열하는데 사용한다.
  • loan 릴레이션내의 모든 지점명을 찾아라.

select branch-name

from loan

순수 관계형 대수 구문에서는 이 질의는 다음과 같다.

branch-name (loan)

  • select 절의 *는 “모든 애트리뷰트”를 의미한다.

select *

from loan

select3
Select 문
  • String Operations
    • % : matches any substring
    • _ : matches any char
  • Ordering the Display of Tuples

SELECT DISTINCT c_name

FROM borrower B, loan L

WHERE B.loan# = L.loan# AND b_name = “P”

ORDER BY c_name [DESC]

select select 2 3
Select 문의 기본 구조: Select 절 (2/3)
  • SQL은 질의 결과와 함께 릴레이션내의 중복을 허용한다.
  • 중복을 제거하려면 select다음에 키워드 distinct를 기입한다.

loan 릴레이션내의 모든 지점명을 찾아 중복은 제거하라.

select distinct branch-name

from loan

  • 키워드 all은 중복이 제거되지 않도록 한다.

select all branch-name

from loan

select select 3 3
Select 문의 기본 구조: Select 절 (3/3)
  • select 절에는 연산자 +,-,* 및 /를 내포한 산술 표현식과 상수 또는 tuple의 애트리뷰트 상의 연산을 내포할 수 있다.
  • 질의:

select branch-name, loan-number, amount* 100

from loan

  • 위의 질의는 애트리뷰트amount에 100이 곱해진 것을 제외하고는 loan 릴레이션과 같은 릴레이션을 돌려준다.
select where 1 2
Select 문의 기본 구조: where 절 (1/2)
  • where 절은 관계형 대수의 선택 술어에 대응한다. from 절에 나타나는 릴레이션의애트리뷰트를 내포하는 술어로 구성된다.
  • 대출액이 1,200불을 초과하는 perryridge지점에서 이루어진 대출의 대출 번호를 찾아라.

select loan-number

from loan

where branch-name = “Perryridge” and amount >1200

  • SQL은 논리 연산자 and, or 및 not을 사용한다. SQL은 비교 연산자에 오퍼랜드로서 산술 표현식의 사용을 허용한다.
select where 2 2
Select 문의 기본 구조: where 절 (2/2)
  • SQL에는 어떤 값보다 작거나 같고 다른 값보다 크거나 같음을 나타내는 where절을 단순히 하기 위해 between비교 연산자를 포함한다.
  • 대출액이 90,000불에서 100,000불 사이인 대출의 대출 번호를 찾아라.

select loan-number

from loan

where amount between90000and100000

select from
Select 문의 기본 구조: from 절
  • from절은 관계형 대수의 카티전 곱 연산에 대응한다. 표현식의 계산에서 검색될 릴레이션들을 나열한다.
  • 카티전 곱 borrower  loan을 찾아라.

select *

from borrower, loan

  • Perryridge지점에 대출이 있는 모든 고객명과 대출 번호를 찾아라.

select distinct customer-name, borrower.loan-number

from borrower, loan

whereborrower.loan-number = loan.loan-number and

branch-name = “Perryridge”

select from1
Select 문의 기본 구조: from 절

SELECT DISTINCT c_name, (borrower).loan#

FROM borrower, loan

WHERE borrower.loan# = loan.loan# AND b_name = “P”

c_name, loan# (b_name = “P”(borrowerloan))

  • If n(borrower)=100,000 and n(loan)=100,000

then Cartesian product produces 100,0002 tuples

  • but c_name, loan# ((b_name = “P” loan)  borrower) will produce less tuples
  • procedural language의 문제 : 질의 방식에 따라 효율 차이가 큼 Users must be smart!
select4
Select 문

SELECT DISTINCT c_name, (borrower).loan#

FROM borrower, loan

WHERE borrower.loan# = loan.loan# AND b_name = “P”

c_name, loan# (b_name = “P”(borrowerloan))

  • If n(borrower)=100,000 and n(loan)=100,000

then Cartesian product produces 100,0002 tuples

  • but c_name, loan# ((b_name = “P” loan)  borrower) will produce less tuples
  • procedural language의 문제 : 질의 방식에 따라 효율 차이가 큼 Users must be smart!
select5
Select 문의 기본 구조: 재명명연산
  • 릴레이션과애트리뷰트의재명명을 위한 SQL 기법은 as절로 이루어진다.

old-name asnew-name

  • Perryridge지점에 대출이 있는 모든 고객명과 대출 번호를 찾아라; 열 이름 loan-number를 loan-id로 대치하라.

select distinct customer-name, borrower.loan-number as loan-id

from borrower, loan

whereborrower.loan-number = loan-number and

branch-name = “Perryridge”

select string
Select 문의 기본 구조: string연산
  • SQL에는 문자열 비교를 위한 문자열-매칭 연산자를 내포한다. 패턴은 두 개의 특수 문자를 사용해 기술한다.

- %는 어떠한 부 문자열과 부합한다.

- _는 어떤 문자와 부합한다.

  • 거리명에 부 문자열 “Main”을 내포한 모든 고객명을 찾아라.

select customer-name

from customer

where customer-street like “%Main%’

select string1
Select 문의 기본 구조: string 연산
  • SELECT b_name, AVG(balance)

FROM account

WHERE b_name LIKE “P%”

GROUP BY b_name

HAVING AVG(balance) >= 50

select tuple
Select 문의 기본 구조: tuple출력의 순서화
  • Perryridge지점에 대출이 있는 모든 고객명을 알파벳 순서로 나열하라.

selectdistinct customer-name

from borrower, loan

whereborrower.loan-number = loan.loan-number andbranch-name = “Perryridge”

order by customer-name

  • 각 애트리뷰트에 대해 내림차순으로는 desc를 오름차순으로는 asc를 지정한다. 오름차순이 기본 값이다.
  • SQL은 order by요청을 받으면 정렬을 수행해야 한다. 많은 수의 tuple을 정렬하는데 비용이 많이 들어가므로, 필요할 때만 정렬하는 것이 바람직하다.
set operations
Set Operations
  • Union / Intersect / Except
    • automatically eliminates duplicates

(SELECT c_name

FROM depositor )

UNION

(SELECT c_name

FROM borrower )

aggregate functions
Aggregate Functions
  • Aggregate Functions are functions that take a collection of values as input and return a single value
    • AVG, MIN, MAX, SUM, COUNT
    • SELECT b_name, AVG(balance)

FROM account

SELECT b_name, AVG(balance)

FROM account

GROUP BY b_name

slide34
NULL 값 활용
  • NULL 값의 의미
    • undefined or unknown
    • SELECT loan#

FROM loan

WHERE amount IS NULL

IS NOT NULL

database modifications
Database Modifications
  • Delete/Insert/Update
    • DELETE FROM depositor

WHERE customer-name=“Smith”

    • DELETE FROM account

WHERE branch-name IN (Select … )

    • INSERT INTO account

VALUES (“Perryridge”, “A12”, 1200)

    • INSERT INTO account

SELECT branch-name, …

    • UPDATE account

SET balance = balance * 1.05 WHERE balance>10000

data definition
Data Definition
  • Domain Types
    • char(n), varchar(n), int, smallint, numeric(p,d), real, double, float(n), date, time
  • Schema Definition
    • CREATE TABLE student

(name char(15) not null,

s_id char(10) not null,

addrvarchar(40),

PRIMARY KEY (s_id))

embedded sql
Embedded SQL
  • Access to database from a general-purpose language is required
    • not all queries can be expressed in SQL : transitive closure
    • Non-declarative actions : formatting, interactions
  • Embedded SQL
    • SQL statements embedded within program written in host language
    • special preprocessor (precompiler)
    • EXEC SQL

… embedded SQL statements

END-EXEC

embedded sql1
Embedded SQL
  • CURSOR
    • EXEC SQL

DECLARE c CURSOR FOR

SELECT c_name, c_city

FROM customer

END-EXEC

    • EXEC SQL OPEN c END-EXEC
    • EXEC SQL FETCH c INTO :cn, :cc END-EXEC
    • EXEC SQL CLOSE c END-EXEC
  • Dynamic SQL
    • dynamically create an SQL statement (string) in the program
slide40
간단한예제
  • SELECT Statement

SELECT c_name

FROM deposit

SELECT DISTINCT b_name

FROM loan

SELECT [ALL] b_name

FROM loan

SELECT *

FROM loan

SELECT loan#, amount*100

FROM loan

slide41
관계형 스키마 예제
  • Employee (fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno)
  • Department (dname, dnumber, mgrssn, mgrstartdate)
  • dept_locations (dnumber, dlocation)
  • Project (pname, pnumber, plocation, dnum)
  • works_on (essn, pno, hours)
  • Dependent (essn, dependent_name, sex, bdate, relationship)
slide42
질의1: 기본
  • Retrieve the birthdate and address of the employee whose name is ‘John B.Smith’

SELECT bdate, address

FROM employee

WHERE fname = ‘John’ AND minit=‘B’ AND lname=‘Smith’

slide43
질의2:기본
  • Retrieve the name and address of all employees who work for the ‘Research’ department

SELECT fname, lname, address

FROM employee, department

WHERE dname=‘Research’ AND dnumber=dno

slide44
질의 3: 기본
  • For every project located in ‘Stafford’ list the project number, the controlling department number, and the department manager’s last name, address, and birthdate

SELECT pnumber, dnum, lname, address, bdate

FROM project, department, employee

WHERE dnum-dnumber AND mgrssn=ssn AND plocation=‘Staffod’

4 alisas
질의 4: alisas의 사용
  • For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor

SELECT e.fname, e.lname, s.fname, s.lname

FROM employee e s

WHERE e.superssn=s.ssn

5 tables as sets
질의 5: Tables as Sets
  • Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’ as a worker or as a manager of the department that controls the project

(SELECT pnumber

FROM project, department, employee

WHERE dnum=dnumber AND mgrssn=ssn AND lname=‘Smith’)

UNION

(SELECT pnumber

FROM project, works_on, employee

WHERE pnumber=pno AND essn=ssn AND lname=‘Smith’)

6 nested query
질의 6: nested query
  • Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee

SELECT e.fname, e.lname

FROM employee e

WHERE e.ssn IN (SELECT essn

FROM dependent

WHERE essn=e.ssn AND

e.fname=dependent_name AND

sex=e.sex)

7 exist function
질의 7: EXIST function
  • Retrieve the names of employees who have no dependents

SELECT fname, lname

FROM employee

WHERE NOT EXISTS (SELECT *

FROM dependent

WHERE ssn=essn)

slide49
질의 8: 집합 명시
  • Retrieve the social security number of all employees who work on project number 1,2, or 3

SELECT DISTINCT essn

FROM works_on

WHERE pno IN (1,2,3)

9 null
질의 9: NULL의 사용
  • Retrieve the names of all employees who do not have supervisors

SELECT fname, lname

FROM employee

WHERE superssn IS NULL

slide51
질의 10: 집계함수의 사용(1)
  • Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary

SELECT SUM(salary), MAX(salary), MIN(salary)

FROM employee

slide52
질의 11: 집계함수의 사용(2)
  • Retrieve the total number of employees in the company and the number of employees in the ‘Research’ department

SELECT COUNT(*) FROM employee

SELECT COUNT(*)

FROM employee, department

WHERE dno=dnumber AND dname=‘Research’

slide53
질의12: 집계함수의 사용(3)
  • For each department, retrieve the department number ,the number of employees in the department, and their average salary

SELECT dno, COUNT(*), AVG(salary)

FROM employee

GROUP BY dno

slide54
질의13: 집계함수의 사용(4)
  • For each project on which more than two employees work retrieve the project number, project name, and number of employees who work on that project

SELECT pnumber, pname, COUNT(*)

FROM project, works_on

WHERE pnumber=pno

GROUP BY pnumber, pname

HAVING COUNT(*) >2

slide55
질의 14: 집계함수의 사용(5)
  • For each project, retrieve the project number, project name, and number of employees from department 5 who work on that project

SELECT pnumber, pname, COUNT(*)

FROM project, works_on, employee

WHERE pnumber=pno AND ssn=essn AND dno=5

GROUP BY pnumber, pname

slide56
질의15: 문자열 검색
  • Retrieve all employees who were born during the 1950s

SELECT fname, lname

FROM employee

WHERE bdate LIKE ‘______5_’

slide57
질의16: 산술 연산
  • Find the salary lists to give all employees who work on the ‘Product X’ project a 10% raise

SELECT fname, lname, 1,1*salary

FROM employee, works_on, project

WHERE ssn=essn AND pno=pnumber AND pname=‘ProductX’

slide58
질의15: 정렬
  • List ordered by the employee’s department and within each department ordered alphabetically by name

SELECT dname, lname, fname, pname

FROM department, employee, works_on, project

WHERE dnumber=dno AND ssn=essn AND pno=pnumber

ORDERED BY dname, lname, fname