Structured Query Language I SQL1 (SA

1
Structured Query Language ISQL1(SAD-6)
2
Structured Query LanguageSQL

• ???????????????? (Query Language)
• ???????????????? ???????????????????????????????
  ?????????????????????????? ??????????? 2 ??????
  ???
• ???????????????????????? (Query By Example QBE)
• ????????????????????????????????????????
  (Structured Query Language SQL)

3
Query By Example QBE

• ?????????????????????????????? (Query By Example
  QBE)
• ???????????????????????????????
  ??????????????????????????????????????????????????
  ???????????????????????? ????????????????????? ?
  ??????????????????????????????????????????????????
  ??????????? (Column) ???????????????? (Field)
  ???????????????????????? ?????????????????????????
  ??????????????????????????? ??????????????????????
  ?????????????????????????????????????? QBE
  ???????????????????????

4
Structured Query Language SQL

• ????????????????????????????????????????
  (Structured Query Language SQL)
• ??????????????????????????????????????????
  ??????????????????? SQL ??????????? ??? ??? ???
  ???????????????????????????????????????????????
  ??????????????????????????????????????????????????
  ?????????????????????????????? ???????????????????
  ?????????????????????????????????????????
  ???????? SQL ?????? DB2, SYBASE, INGRES, ORACLE,
  INFORMIX, SQL server, dBase, Paradox, MS Access
  ???????

SELECT EMPNAME, EMPUNM, DEPT, SALARY FROM EMPF
WHERE SALARY gt 25,000 ORDER BY DEPT
5
?????????????? SQL (SQL Reserved Keywords)/1
ALL FETCH ORDER
AND FLOAT PASCAL
ANY FOR PLI
AS FORTRAN PRECISION
ASC FOUND PRIVIEGES
AUTHORIZATION FROM PROCEDURE
AVG GO PUBLIC
BEGIN GOTO REAL
BETWEEN GRANT ROLLBACK
BY GROUP SCHEMA
6
?????????????? SQL (SQL Reserved Keywords)/2dk
CHAR HAVING SELECT
CHARACTER IN SELECT
CHECK INDICATOR SET
CLOSE INSERT SMALLINT
COBOL INT SOME
COMMIT INTEGER SQL
CONTINUE INTO SQLCODE
COUNT IS SQLERROR
CREATE LANGUAGE SUM
CURRENT LIKE TABLE
7
?????????????? SQL (SQL Reserved Keywords)/3
DEC MIN UNION
DECIMAL MODULE UNIQUE
DECLARE NOT UPDATE
DELETE NULL USER
DESC NUMERIC VALUES
DISTINCT OF VIEW
DOUBLE ON WHENEVER
END OPEN WHERE
ESCAPE OPTION WITH
EXEC OR WORK
?????? 6.1 ?????????????????????? SQL ??????????
ANSI 1986
8
?????????????????????? SQL

• ?????????????????????????????????????????? SQL
  ??????????????????????????????????????????????? 2
  ????? ???
• ?????????????????????????? (Data Definition
  Language DDL)
• ???????????????????????????????? (Data
  Manipulation Language DML)

9
Data Definition Language(DDL)

• ???????????????????????? ???????????
  ???????????????????????????? ??????????
  ????????????????????? ????????????????????????????
  ????????????????? ??????????? 6.2

?????? ????????
CREATE TABLE ???????????????????????????????????
CREATE INDEX ??????????
CREATE VIEW ?????????????????????????????????????
DROP TABLE ????????????????????????????????
DROP INDEX ?????????????????????????
DROP VIEW ?????????????????????
ALTER TABLE ?????????????????????????????????????????
???????? 6.1 ?????????????????????????? ??
??????????????? ??????????????????????
10
Data Definition Language(DDL)

• ????????????? (Create Table)

CREATE TABLE lt?????????gt (lt??????????? ?????????
?????????????gt , lt???????????
?????????????????????gt )
CREATE TABLE EMPLOYEE (Emp-Id CHAR(6), Name
CHAR(25), Dept CHAR(20), Salary INTEGER)
11
Data Definition Language(DDL)

• ???????????????

???????????? ????????
INTEGER ???????????????? 4 ???? ???????????????????? ????????????????????????? -2,147,483 ??? 2,147,647
SMALLINT ????????????????????(???? 2 ????) ???????????????????? ????????????????????????? -32,768 ??? 32,767
DECIMAL (p,q) ???????????????????? ?????? p ??? ?????????????????????????????????????? q ???????????????????????????
CHAR (n) ????????????????????????????????????????????? n ???
FLOAT ??????????????????????????? 1.256X109
?????? 6.2 ???????????????????????????????????????
? DB2
12
Data Definition Language(DDL)

• ??????????

DROP TABLE lt?????????????????????gt ???????? DROP
TABLE EMPLOYEE

• ???????????????????????

ALTER TAB LE lt?????????gt lt????????????????????
gt (lt??????????? ???????????????gt) ???????? ALT
ER TABLE EMPLOYEE MODIFY (Name
CHAR(30)) ALTER TABLE EMPLOYEE ADD
(Address CHAR(70))
13
Data Definition Language(DDL)

• ????????????????

CREATE UNIQUE INDEX lt?????????gt ON
lt????????????????????????gt (lt??????????????
1gt,lt?????????????? 2gt)
???????? CREATE INDEX Emp-Idx ON
EMPLOYEE (Emp-Id) CREATE UNIQUE INDEX
EmpInd ON EMPLOYEE (Emp-Id)
14
Data Definition Language(DDL)

• ?????????????

DROP INDEX lt?????????????????????gt
???????? 6 ???? ????????????????????? Emp-Idx
????????????????????????????????????? DROP
INDEX Emp-Idx
15
Data Manipulation Language(DML)

• ?????????????????????????? (Data Manipulation
  Language DML)
• ????????????????????????????????????????????????
  ??????????????????????????????????????????????????
  ???? ??????????????????????????????????
  ?????????????????????????? ???????????
  ??????????????????????? ??????????????????????????
  ????????????????????????????? SELECT, INSERT,
  DELECT ??? UPDATE ????????

16
Data Manipulation Language(DML)
?????? ????????
SELECT ????????????????????
INSERT ???????????????????????
DELETE ??????????????????????
UPDATE ????????????????????????
???????? 6.3 ?????????????????????????????????????
??????????
17
Data Manipulation Language(DML)

• ???????????????????
• ?? SQL ????????? SELECT ??????????????????
  ????????????????????????????????
  ???????????????????????????????????????? ??????
• SELECT lt?????????????????????????????gt
• FROM lt?????????gt
• WHERE lt????????gt
• ????????????????? SELECT ?????????????????????????
  ??????????????????????? ??????????????????????????
  ?????????????????????? (,) ???????????????????????
  ?????????????????????????????????????????????????
  ???????????????????????? ??????
• ????????????????? FROM ???????????????
  ???????????????????????????????
• ????????????????? WHERE ??????????????????????????
  ????????????????? ?????????????????????????

18
Data Manipulation Language(DML)

• ???????????????????? (BOOLEAN Data Type)
  ??????????????????????
• ???????????????????????????????????????????? 2
  ??? ??????????? (True) ???????? (False)
  ??????????????????????????????????????????????????
  ?????????? ???????????????????????????????????????
  ????????????????? (Relational Operator)

?????????????????????? ???????? ?????????????? ???????
??????? 55 ???? (True)
ltgt, !, ?????????? 3ltgt7 ???? (True)
lt ???????? 7lt4 ???? (True)
gt ??????? 4gt11 ???? (True)
lt ??????????????????? 9lt12 ???? (True)
gt ?????????????????? 100gt115 ???? (True)
???????? 6.4 ?????????????????????????????
19
Data Manipulation Language(DML)

• ??????????????????????????????????????????? ?
  ?????????? SELECT ???????????? 3 ??????????
  STUDENT (????????) ENROLLMENT (???????????????????
  ??) ??? CLASS (????-?????????)
• STUDENT (SID, Name, Major, GradeLevel, Age)
• ENROLLMENT (StudentNumber, ClassName,
  PositionNumber)
• CLASS (Name, Time, Room)
• ????????????????????? ? ?????????????????????????
  ?? 6.6 ????????????????????????????????????????
  ?????????????????? 6.5 (?) (?)

20
Data Manipulation Language(DML)
??????????? ????????
SID, Student Number ???????????? ?????????? 5 ??????
Name ????????? STUDENT ????????????
Major ???????????
GradeLevel ????????????????? ???????? 5 ???????????? FR (???????) SO (?????) JR (?????) SN (?????) ??? GR (?????)
Age ????
ClassName ???????? ?????????? 5 ?????
PositionNumber ??????????????????????????????
Name ??????? Class ?????????????????? 5 ??????
Time ???????????? DDDHH ????? D ?????? ?????? M, T, W, R, F ??? HH ??????? ???????????? MWF8 ?????????????????????????????????
Room ????????????? ?????????? 5 ??????
???????? 6.6 ?????????????????????????? ?
?????????? STUDENT, ENROLLMENT, ??? CLASS
21
Data Manipulation Language(DML)
SID (????????????) Name (????) Major (???????????) GradeLevel (??????) Age (????)
41100 JAMES HISTORY GR 20
41150 PIM ACCOUNTING SO 18
41200 BIRD MATH GR 49
41250 GARY HISTORY SN 49
41300 BIRD ACCOUNTING SN 40
41350 ROSSE MATH JR 19
41400 RUM ACCOUNTING FR 17
41450 JAMES HISTORY SN 23
?????? 6.5 (?) ??????????????? STUDENT
22
Data Manipulation Language(DML)
StudentNumber (????????????) ClassName (????????) PositionNumber (????????????????????????????)
41100 BD445 1
41150 BA200 1
41200 BD445 2
41200 CS250 1
41300 CS150 1
41400 BA200 2
41400 BF410 1
41400 CS250 2
41450 BA200 3
?????? 6.5 (?) ??????????????? ENROLLMENT
23
Data Manipulation Language(DML)
Name (????????) Time (?????????) Room (?????????)
BA200 M-F9 CB110
BD445 MWF3 CB213
BF410 MWF8 CB213
CS150 MWF3 HT304
CS250 MWF12 AT210
?????? 6.5 (?) ???????????????????????? CLASS
24
Data Manipulation Language(DML)

• ??????????????????????????????????
• ?????????????????? SELECT ??????????????????????
  ????? ????????????????????????????????????????????
  ?? SELECT ????????????????????????????????????????
  ?????????? FROM
• ??????????? 7 ??????????????????????? (SID)
  ???????????? (Name) ?????????????? (Major)
  ????????????????
• SELECT SID, Name, Major
• FROM STUDENT

25
Data Manipulation Language(DML)

• ???????? SQL ????????

SID (????????????) Name (????) Major (???????????)
41100 JAMES HISTORY
41150 PIM ACCOUNTING
41200 BIRD MATH
41250 GARY HISTORY
41300 BIRD ACCOUNTING
41350 ROSSE MATH
41400 RUM ACCOUNTING
41450 JAMES HISTORY
26
Data Manipulation Language(DML)

• ??????????? 8 ??????????? ??????????????????
• SELECT Major
• FROM STUDENT

Major (???????????)
HISTORY
ACCOUNTING
MATH
HISTORY
ACCOUNTING
MATH
ACCOUNTING
HISTORY
27
Data Manipulation Language(DML)

• ???????????????????????????????????????
  ?????????????????????????????????????????????
  ????? DISTINCT ???????????????????????????????????
  ????????
• SELECT DISTINCT Major
• FROM STUDENT

Major (???????????)
HISTORY
ACCOUNTING
MATH
28
Data Manipulation Language(DML)

• ?????????????????????????????
• ????????????????????????????????????????????????
  ??????????? SELECT ??????????????????????????
  WHERE
• ??????????? 9 ??????????????????????? ????
  ??????????? ?????? (GradeLevel) ??????? (Age)
  ??????????????????????????????????????? (MATH)
• SELECT SID, Name, Major, GradeLevel, Age
• FROM STUDENT
• WHERE MajorMATH

29
Data Manipulation Language(DML)

• ???????????????????????????? STUDENT
  ??????????????????????????????? WHERE
  ??????????????????????????????????????????????????
  ?? (Major) ?????????????? (MATH)
  ????????????????????

SID (????????????) Name (????) Major (???????????) GradeLevel (??????) Age (????)
41200 BIRD MATH GR 49
41350 ROSSE MATH JR 19
??????????????????????????????????????????
STUDENT ?????????????????????????? () ??????
?????? SELECT FROM
STUDENT WHERE MajorMATH
30
Data Manipulation Language(DML)

• ??????????? 10 ?????????????????????????????????
  ??????? ??????????? MATH
• SELECT Name, Age
• FROM STUDENT
• WHERE MajorMATH
• ?????????????????????????????????????????????????
  ????????????????????? ??????????????????? ??????

Name (????) Age (????)
BIRD 49
ROSSE 19
31
Data Manipulation Language(DML)

• ?????? AND ???? OR ???????????????????
• ???????????????????????????????????????????????
  ??????????????????????????????????????????????????
  ???????? AND ???? OR ???????????????
  ???????????????????? ???????? 6.7

????????????????????????? ???????? ???????? ???????
AND ??? ???????????????????????????? (5gt2) AND (0lt-7) False
OR ???? ???????????????????????????? (-9ltgt9) OR (23) True
NOT ???????????????? ??????????????????? NOT (23) True
???????? 6.7 ?????????????????????????????????????
???
32
Data Manipulation Language(DML)

• ??????????? 11 ?????????????????????????????????
  ?? ??????????? MATH ???????????????? 20 ??
• SELECT Name, Age
• FROM STUDENT
• WHERE MajorMATH AND Agegt20
• ??????????

Name (????) Age (????)
BIRD 49
33
Data Manipulation Language(DML)

• ??????????? 12 ???????????????????????
  ??????????????? ?????? ????????????????????????
  ACCOUNTING ????????????????? 4 (SN)
• SELECT SID, Name, Major, GradeLevel
• FROM STUDENT
• WHERE MajorACCOUNTING OR
• 
  GradeLevel SN
• ???????? WHERE ??????????????????? OR
  ???????????????????????????? ???????????
  ACCOUNTING ??????????? ????????????? 4
  ??????????????????????????????????????????????????
  ????????????????

34
Data Manipulation Language(DML)
SID (????????????) Name (????) Major (???????????) GradeLevel (??????)
41150 PIM ACCOUNTING SO
41250 GARY HISTORY SN
41300 BIRD ACCOUNTING SN
41400 RUM ACCOUNTING FR
41450 JAMES HISTORY SN

• ???????????????????????????????? 41250 ???????
  41450 ??????????????????????? ACCONTING ????????
  ?????????????????????????????????????????? 4
  ??????????????? OR ???????????????????????????????
  ???????????????????????????????

35
Data Manipulation Language(DML)

• ????????? AND ????????????????????????????
  ?????????
• SELECT SID, Name, GradeLevel, Major
• FROM STUDENT
• WHERE MajorACCOUNTING AND
• GradeLevelSN
• ???????????? ??????

SID (????????????) Name (????) GradeLevel (??????) Major (???????????)
41300 BIRD SN ACCOUNTING
36
Data Manipulation Language(DML)

• ?????? BETWEEN X AND Y
• ????????????????????????????????????????????????
  ??????????? 20 ??? 40 ??
• SELECT
• FROM STUDENT
• WHERE Agegt20 AND Agelt40

SID (????????????) Name (????) Major (???????????) GradeLevel (??????) Age (????)
41100 JAMES HISTORY GR 20
41300 BIRD ACCOUNTING SN 40
41450 JAMES HISTORY SN 23
37
Data Manipulation Language(DML)

• ????????????????????????????????????????????????
  ??????????? BETWEEN X AND Y ??????????????????????
  ??? X ?????? Y ??????
• SELECT
• FROM STUDENT
• WHERE Age BETWEEN 20 AND 40

38
Data Manipulation Language(DML)

• ?????? IN
• ??????????? IN ?????????????????????????????????
  ???????????????????????
• ??????????? 13 ??????????? ????????????
  ??????????????? ??? ?????????????????????????????
  ?????????? MATH ???? ACCOUNTING
• SELECT SID, Name, Major
• FROM STUDENT
• WHERE Major IN MATH, ACCOUNTING

39
Data Manipulation Language(DML)

• ?????????????????????????????

SID (????????????) Name (????) Major (???????????)
41150 JAMES ACCOUNTING
41200 BIRD MATH
41300 BIRD ACCOUNTING
41350 ROSSE MATH
41400 RUM ACCOUNTING
?????????????? WHERE ?????? IN
?????????????????????????????????? OR
?????? SELECT SID, Name, Major FROM
STUDENT WHERE MajorMATHOR
MajorACCOUNTING
40
Data Manipulation Language(DML)

• ?????? NOT IN
• NOT IN ?????????????????????????????????????????
  ??????????????????????
• ??????????? 14 ?????????????????????????????????
  ??????????????? ???? MATH ???? ACCOUNTING
• SELECT SID, Name, Major
• FROM STUDENT
• WHERE Major NOT IN MATH,
  ACCOUNTING

41
Data Manipulation Language(DML)

• ???????????????????

SID (????????????) Name (????) Major (???????????)
41100 JAMES HISTORY
41250 GARY HISTORY
41450 JAMES HISTORY
42
Data Manipulation Language(DML)

• ??????????????
• ????????????????????????????????????????????????
  ????????????? SELECT ?????????????????????????
  ORDER BY ?????????????????????????????????????????
  ??????????????????? ??????????????????????????????
  ?????????????????????? DESC ??????????????????
  ???????????????????????????????????????? ASC
  ?????????????????
• ????????????????????????? ORDER BY
  ??????????????????????????????????????????????????
  ????????????????? ORDER BY ??????????????????
  SELECT ???? ???????????????????? ORDER BY ??????
  ORDER BY ?????????????????????????????? SELECT
  ??????????????????????????????????????????????????
  ???? ORDER BY ??????? ??????

43
Structured Query Language SQL
SELECT lt?????????????????????????????gt FROM lt???
??????gt WHERE lt????????gt ORDER
BY lt??????????????????????????????????????????????
?????gt DESC

• ??????????? 15 ??????????? ???? ???????????
  ?????????? ???????? ???????????????????
  ACCOUNTING ??? ????????????????????????????
  ??????
• SELECT Name, Major, Age
• FROM STUDENT
• WHERE MajorACCOUNTING
• ORDER BY Name

44
Data Manipulation Language(DML)

• ???????????????????

Name (????) Major (???????????) Age (????)
BIRD ACCOUNTING 40
PIM ACCOUNTING 18
RUM ACCOUNTING 17
???????????? ORDER BY ???????????????????????????
??? ????????????????????????????????????
1,2,3.... SELECT Name, Major,
Age FROM STUDENT WHERE MajorACCOUNTING
ORDER BY 1
45
Structured Query Language SQL
????????????????????????????????????????????????
??????????????????? ??????????????????????????????
???? ORDER BY ????????????????????????????????????
?????????????????? ORDER BY ????
???????????????????????????? ? ??

• ??????????? 16 ??????????????? ???????????
  ??????? ?????????????????????????????????? (FR)
  ?? ??? (SO) ?????? 4 (SN) ?????????? ?????????
  ?????????????????????????????? ?????????????????
  ??????????????????????
• SELECT Name, Major, Age
• FROM STUDENT
• WHERE GradeLevel IN FR, SO, SN
• ORDER BY Major ASC, Age DESC

46
Data Manipulation Language(DML)

• ???????????????????

Name (????) Major (???????????) Age (????)
BIRD ACCOUNTING 40
PIM ACCOUNTING 18
RUM ACCOUNTING 17
GARY HISTORY 49
JAMES HISTORY 23
47
Data Manipulation Language(DML)

• ????????????????????????????????????????
• ???????????????????????????????????????
  ????????????????? INSERT ?????????????????????????
  ?????????? ????????????????????????????
• ????????????????????????????????????????????????
  ?????????????? INSERT ??????
• INSERT INTO lt?????????gt
• VALUES (lt????????????????????gt)

48
Data Manipulation Language(DML)

• ??????????? 17 ?????????????? 1
  ????????????????????????????? ????????? EMPLOYEE
  ???????????????????? ??????????? Emp-Id
  (???????????) ???? 410375 Name (????) ?????
  ?????? Dept (????) ??? ????? Salary
  (?????????) ??????? 25000
• INSERT INTO EMPLOYEE
• VALUES (41075, ????? ??????,?????, 25000 )

49
Data Manipulation Language(DML)

• ?????????????????????????????????? INSERT
  ?????????????????????????? (SELECT)
  ??????????????????????????????????????????????????
  
• INSERT INTO lt?????????gt
• VALUES
• lt???????????? SELECTgt

50
Data Manipulation Language(DML)

• ??????????? 18 ??????????????????????????????????
  ??????????????????? (Dept) ??????????? ????????
  EMPLOYEE ???????????? COMP-STAFF ????????????????
  ?????? 3 ??????? ??? ??????? Emp-Id, Name, ???
  Salary ??????????????????? INSERT
  ?????????????????????????? (SELECT)
• INSERT INTO COMP-STAFF
• VALUES
• SELECT Emp-Id, Name, Salary
• FROM EMPLOYEE
• WHERE Dept???????????

51
Data Manipulation Language(DML)

• ???????????????????????
• ????????????????????????????????????????????????
  ?????????????????????? ????????? UPDATE
  ?????????????????????????????????? ????????????
  SET ??????????????????????????????????????????????
  ???
• UPDATE lt???????????????????????????gt
• FROM lt???????????gtlt?????????gt
• WHERE lt????????gt
• ??????????? 19
• UPDATE EMPLOYEE
• FROM SalarySalary1.2
• WHERE Dept?????

52
Data Manipulation Language(DML)

• ?????????????????
• ?????????????????????????????????? DELETE
  ??????????????????????????????????????????????????
  ????????????????????????? WHERE
• DELETE lt?????????gt
• WHERE lt????????gt
• ??????????? 20 ??????????????????????????????????
  ?????????? EMPLOYEE ???????????????????????????
• DELETE EMPLOYEE
• WHERE Dept?????