???? SQL (Structured Query Language)

1
???? SQL (Structured Query Language)
2
SQL (Structured Query Language) ?????????????????
?????????????????????????? ???????????????????????
?????????????????????? ??????????????????????????
???????????? (DBMS) ??????????????????????? SQL
???? ORACLE, DB2, MS-SQL, MS-Access ???????????
?? SQL ??????????????????????????????????????
???? ???? C/C, VisualBasic ??? Java
??????????????? SQL 1. ?????????????????
????? 2. ??????????????????????????
?????????????? ???????? ???????????
?????????????? 3. ???????????????????????
???????????
3
??????????????????? SQL 1.
???????????????(Data Definition Language DDL)
???????????????????????????????????
????????????????????????? Attribute??
????????????? ??????????????????????????
???????????????? ?????? CREATE,DROP,ALTER
4

• 2. ???????????????? (Data Manipulation Language
  DML) ????????????????????????????? ????? ??
  ??????????????????????????? ??????
  SELECT,INSERT,UPDATE,DELETE
• 3. ???????????????? (Data Control Language
  DCL) ?????????????????????????????????????????
  ???? ?????? ???????????????????
  ???????????????????????????????????
• ?????? GRANT,REVOKE

5
??????????????? (Data Definition Language
DDL) ???????????????????????????????
?????? CREATE ?????????????????????? -
CREATE TABLE ?????????? - CREATE INDEX
?????????? - CREATE VIEW ???????? DROP
??????????????????? - DROP TABLE
??????? - DROP INDEX ??????? - DROP VIEW
????? ALTER ????????????????????????????????
??????????
6
????????????? CREATE TABLE ???????????? ???
CREATE TABLE ???????? ???
CREATE TABLE table_name ( field1 type
(size) NOT NULL , field2 type
(size) NOT NULL , ...
CONSTRAINT name PRIMARY KEY (primary1,
primary2 , ...) FOREIGN KEY (ref1, ref2 , ..)
REFERENCES foreigntable
(foreignfield1 , foreignfield2 , ...) )
7
????????
Create table Student ( ID integer PRIMARY KEY
NOT NULL, FName char(30),
LName char(30) )
8
(No Transcript)
9
?????????? DROP TABLE ???????????????????????
???????????? DROP TABLE tablename ????????
DROP TABLE Employee
10
??????????????????CREATE /DROP
INDEX ???????????????????????? - ?????
column ???? ???????? column ??????????????????????
? ???? ???????????? - ?????????????????????
???????????????? - ????????????????????????????
??
11
??????????????????CREATE /DROP
INDEX ????????????????????????????????

• CREATE INDEX Index-name
• ON table-name(attribute-name1,.)
• ex. CREATE INDEX PRO_INDEX
• ON PRODUCT(PRODUCT_NAME)
• DROP INDEX Index-name
• ex. DROP INDEX PRO_INDEX

12
???????? ????????????? ????????????????????????
CREATE INDEX NAME_IDX ON WORKER (WK_NAME)
???????? ????????????? ????????????????????????
CREATE INDEX CODE_IDX ON WORKER (WK_ID)
13
(No Transcript)
14
(No Transcript)
15
??????????? CREATE VIEW ?????????????????????
?????????????????????? ???????????????????????????
????????? ????????????????????????????????????????
??????????????????????? ?????????????????????????
???
CREATE VIEW view-name column_name1,
column_name2,... AS SELECT
attribute1,attribute2,.. FROM table-name
WHERE condition
???????? ????????? DROP VIEW view-name
16
ex. Create view my_view1 as select
pro_id , pro_name , pro_price from
product where supid 5

ex. DROP VIEW ex. DROP
VIEW my_view1
17
???????????????????????????? ALTER
TABLE ???????????????????????????????????????????
?????? 1. ADD ???????????? column ??????? 2.
MODIFY ??????????????????????? column 3. DROP
????????? column ???????????? A
LTER TABLE tablename COMMAND
COLUMNcolumn_name datatype CONSTRAINT COM
MAND ??? ADD,MODIFY,DROP
18

• Alter Table
• ADD Attribute name
• ex. ALTER TABLE Employee
• ADD Tel CHAR( 10)
• MODIFY Structure
• ex. ALTER TABLE Employee
• MODIFY ( EName CHAR(35) )
• DROP Attribute name
• ex. ALTER TABLE Employee
• DROP Tel CHAR( 10)

19
???????????????? (Data Manipulation
LanguageDML) ????????????????????????????????
?????? SELECT ??????????????????????????? I
NSERT ??????????????????????????? UPDATE
?????????????????????????????? DELETE
?????????????????????????
20
?????? SELECT ??????????????????????????????
???????????????????????? ??????????????????????
?????????????????????????????????????? ???
???????????????????????????????? ??????????????
??????????? 3 ???????? ??? SELECT..FRO
M..WHERE
21
(No Transcript)
22
DISTINCT ???????????????????????????????
??????? FROM ?????????????????????????????????
??? WHERE ????????????????????????????????????
?? ORDER BY ????????????????????????
?????? DESC ????????????????????????? ASC ???
?????????????????????? GROUP BY ?????????????????
???????????????????? HAVING ?????????????????
??????????????? GROUP BY ????????
23
??????????????????? column ????????????? SELEC
T FROM lt tablegt WHERE ltconditiongt ??
????????????????? column SELECT ltcolumngt
FROM lt tablegt ?????????????????????? S
ELECT FROM lt table1gt lttable2gt
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
Mathematics Operator ???? operator
???????????????? ?????? ??????????? , -, ,
/ ???????? SELECT ID,FName,LName,Salary0.
05 AS Commision FROM Employee
ID
FName
LName
Commision
1 John Smith 650
2 Mary Jones 750
3 Andy Brown 900
4 Jane Wright 1,000
5 John Jones 1,050
30
WHERE Condition

• Mathematics Operator
• , - , , /
• ex. Select pro_name ,
• pro_price amount
• as sum
• from product

31
Comparison Operator ???? Operator
?????????????????????? ?????????????????
?????? ??????? lt ???????? gt
??????? lt ??????????????????? gt
?????????????????? ltgt ??????????

32
WHERE Condition

• Comparison Operator , gt , lt , gt ,lt ,ltgt
• ex. Select pro_id ,
• pro_name
• from product
• where
• (price gt5 ) and
• (amount gt20)

33

• Logical Operator in, between , like, not

SELECT pro_id, pro_name,sup_id FROM product WHERE
sup_id not in ( 1,2)
34
(No Transcript)
35
WHERE Condition
SELECT pro_id, pro_name , pro_price FROM
product WHERE pro_price between 5 and 20
36
WHERE Condition
SELECT pro_id, pro_name FROM product WHERE
pro_name like "p"
37
????????????????????????????
38
(No Transcript)
39
(No Transcript)
40
???????????????????? Subqueries ???? Nested
query ?????????????????????????????? SELECT ????
?????? SELECT ??????????? Nested
query ????????? SELECT ?????????????????????
?? ???????????????????? ?????? SELECT ???????
?????? ???????? SELECT
. FROM.. WHERE (SELECT
.. FROM.. WHERE. )
41
???????? ?????????????????????????????????????????
? Andy Employee
Name Department
John Marketing Mary Sales Peter Sales
Andy Marketing Anne Marketing
42
(No Transcript)
43
?????? Aggregate Function ??????????
Function ??????????????????????????? ??????????
Function ????? ?????? COUNT ???????????????
????????? SUM ?????????????? AVG ????????
?????????? MIN ?????????????????? MAX ??
????????????????
44
(No Transcript)
45
(No Transcript)
46
???????????????????
47
?????? GROUP BY ????????????????????????????
????????????? ????????????????????????????????????
???????????????
48
(No Transcript)
49
(No Transcript)
50
?????? HAVING ?????? HAVING ????????????
GROUP BY ???? ???HAVING ??????????????????????????
???????? GROUP BY ????????????????????????????????
???? HAVING
51
???????????? JOIN ?????????????????? 2
????????????????????????????????????????? ??
2 ??? ??? 1. INNER JOIN 2. OUTER JOIN
INNER JOIN ?????????????????? 2
????????????????Attribute ???? column
??????(match)???????? ????????
??? SELECT..FROM table1 INNER JOIN
table2 ON table1.column table2.column
52
(No Transcript)
53
INNER JOIN SELECT TASTER_RESULT.TASTERID,
MYPRODUCT.PNAME, TASTER_RESULT.COMMENT FROM
TASTER_RESULT INNER JOIN MYPRODUCT ON
TASTER_RESULT.PID MYPRODUCT.PID ??????? ???
TASTERID PNAME COMMENT
1 MALEE JUICE RICH FLAVOR
2 MALEE
JUICE TOO SWEET 3
BALL ICE CREAM SMOOTH TASTS
54
OUTER JOIN ???????????????????????????? 2
????? ??? record ????????????? record
??????????????Attribute ???? column ?????? ?? 2
??? ??? 1. RIGHT JOIN ?????????????????????
???????????? JOIN ????????????????????????????????
?????????? ???????????????????????? ?????????
NULL ??????? ???????? ??? SELECT
FROM table1 RIGHT JOIN
table2 ON table1.column table2.column
55
(No Transcript)
56
SELECT BOOK.NAME, BORROWING.CUSID,
BORROWING.DATE-IN FROM BORROWING RIG
HT JOIN BOOK ON BORROWING.BID
BOOK.ID ??????? ???
BNAME CUDID DATE-IN
ENGLISH 10 5 OCT 2001 MATH
NULL NULL COMPUTER 7 3 JUL
2001 COMPUTER 7 1 AUG 2001 ENGINEER NULL
NULL THAI NULL NULL
57
2. LIFT JOIN ??????????????????????????????????
JOIN ?????????????????????????????????????????
???????????????????????? ????????? NULL ???????
???????? ??? SELECT FROM
table1 LEFT JOIN table2 ON table1.column
table2.column
???????? SELECT BOOK.NAME, BORROWING.CUSID,
BORROWING.DATE-IN FROM BOOK LEFT
JOIN BORROWING ON BORROWING.BID
BOOK.ID ????????????????????????? RIGHT JOIN
58
?????? INSERT ?????????????????????????
INSERT INTO table-name VALUE (var1 , var2 ,
.) ex. Insert into product value
(10,book,30, 5 )
59
?????? UPDATE ?????????????????????????????
?? UPDATE table-name SET
column-name1value1,column-name2value2,... WH
ERE condition ?????? DELETE ?????
????????????????????????????? DELETE
table-name WHERE condition
60
(No Transcript)
61
???????????????? (Data Control Language
DCL) ????????????????????????????????????????????
????????????????????????????????????????????????
??????????????????(DBA) ??????????????????????
?????????????????????????????? ???????????????????
??????????????????????????????????????????????????
??? ???????????????? ?????? GRANT
??????????????????????????? REVOKE ???????????
???????
62
?????? GRANT ??????????????????????????????
???????????????????? ?????????????????????????????
????????????????????????????? ??????????????????
????????????????? (SELECT) ???????? (INSERT)
????? (DELETE) ??????????????? (UPDATE) ??????
?????? ??? GRANT ltprivilege listgt
ON table-name or view-name
TO ltuser listgt ??????
ltprivilege listgt ??? ??????????????????????
???? SELECT, INSERT, DELETE ????
UPDATE table or view name ???
?????????????????????????????? ??? ltuser listgt
??? ??????????? ???????????????????????????????
63
????????1 ????????????????????????????????????????
??????????????????? (WORKER) GRANT SELECT
ON WORKER TO WICHAI,PISAMAI ???????? 2
??????????????????????????????????????????????????
? GRANT SELECT ON BRANCH TO
PUBLIC ???????? 3 ????????????????????????????
??????????????? WORKER ?????????? GRANT
ALL ON WORKER TO WANPEN
64
???????? 4 ???????????????????????????????????????
??????????????(WORKER) ???????????????????????????
?????????????????????? ????????? WITH GRANT
OPTION ???????????????????????????????????????????
? GRANT SELECT ON WORKER TO SOMSAK WITH
GRANT OPTION ?????????????????????????????????
????????????????????????? GRANT SELECT ON WORKER
TO WISAN ???????? 5 ??????????????????????
??????????????? WORKER GRANT ALL
PRIVILEGES ON WORKER TO MANAGER WITH
GRANT OPTION
65
?????? REVOKE ?????????????????????????????
???????????????????????????????? GRANT
?????????????? ??? REVOKE ltprivilege listgt
ON table-name or
view-name FROM ltuser
listgt ???????? ???????????????????????????
1-3 REVOKE SELECT ON WORKER FROM
WICHAI,PISAMAI REVOKE SELECT ON BRANCH FROM
PUBLIC REVOKE ALL ON WORKER FROM WANPEN
66
??????????????? 4 ????????? GRANT ?????????????
?????????? REVOKE ?????????????????????????????
???????????????? REVOKE SELECT ON WORKER
FROM SOMSAK ??????????????????????????
? ???????????????????????????????????????????????
????????????