?????????SQL??,?????????????????????,???????????????????????????

1
???????
2
????

• ??????????????????????????????????????
• ?????????SQL??,?????????????????????,?????????????
  ??????????????
• ????????ASP????SQL???

3
???????

• ???????????DBMS(Database Management System)
• ????????,?????????????????,???????????

4
?????????

• ??????????????
• (1)????????????????,???????????????,?????????
• (2)???????????,???????????????,???????????????????
  ??????,??????????????,?????????????
• (3)????????????,??????,???????????????,?????????,?
  ?????????,?????????????,??????,????,??????????

5
????????

• ??????????????
• (1)20??60??,???????????????
• (2)??20??70??,???????????
• (3)??20??80??,????????????,1970 ?, E. F. Codd
  ??????????,??,??????????????????????1979????Oracle
  V2???????????????
• (4)??90????,?????????????,?????????????,??????????
  ??

6
????????

• ???????????????????????????????????????????Access
  ?SQL Server?Oracle?DB2?MySQL??????????????????????
  ?

7
SQL????

• SQL(Structured Query Language,???????)????????????
  ,???????????,???????????SQL???SQL??????????
• (1)SQL???????????,?????????
• (2)SQL??????????(?????)?

8
SQL????
9
????

• ??????Access????,??Access2000/XP/2003,???????,??
  ????person.mdb,????5-2?????

10
??SQL??

• ???SQL????DQL?DML??????????????????
• ??(SELECT)???(INSERT)???(UPDATE)???(DELETE)?

11
???(??????)

• ?1SELECT FROM grade
• ?????grade???????????
• ?2SELECT ??,?? FROM grade
• ?????grade?????????????
• ?3 SELECT??,??,?????? as ??? FROM grade
• ?????grade???????????,???????????????????????

12
?????(??????)

• ?1SELECT FROM grade WHERE??gt60
• ?????????????60????????
• ?2SELECT FROM grade WHERE??300 OR ??300
• ???????????300?????????300???????
• ?3Like???????_???
• ?????????????????,?????????????? like
  '_?',??????????????????????,????
• ?4Like??????????
• ???? Like '?',???????????????,????,????
  ????????????????????,??????SQL?????,SELECT
  FROM ???? WHERE ?? Like '?'?

13
?????(????)

• ?1SELECT FROM grade ORDER BY??
  ???????
• ?????grade????????,?????????
• ?2SELECT FROM grade ORDER BY??,??
• ?????grade????????,????????,??????????????????
• ?3SELECT FROM grade ORDER BY?? DESC
  ???????
• ?????grade????????,?????????
• ?4SELECT TOP 5 FROM grade
• ?????grade???????????????

14
DML?????

• DML????????
• ????(INSERT INTO)?????(UPDATE)?????(DELETE)?

15
1. DELETE?????????

• ????DELETE FROM ??? WHERE ??
• ?DELETE FROM grade WHERE??0
• ?????????????????,????WHERE??,????????

16
UPDATE????????

• ????UPDATE ??? SET ???1??, ???2?? WHERE??
• ?1UPDATE grade SET ?? ??10
  ???grade?????????10?
• ?2UPDATE grade SET ?? 100 WHERE ?? like '?'
• ?????????????????????100?

17
INSERT INTO?????????(INTO????)

• ????1INSERT INTO ???VALUES (????)
• ????2INSERT INTO ???(???,???,) VALUES (????)
• ??????????????VALUES????????????,?????????????,??
  ???????????????
• ?1INSERT INTO grade(??, ??, ??) VALUES (1234,
  '??',70)
• ?2INSERT INTO grade VALUES (5678, '??',70,80,90)
• ??,?2???
• INSERT INTO grade(??, ??, ??, ??, ??) VALUES
  (5678, '??',70,80,90)
• ???????????,??????????,???????????

18
????

• ???????????????,???????????????
• ?????5?,???SUM???AVG???COUNT???MAX???MIN???

19
SUM??

• (1)SUM??,?????????????
• ?. SELECT SUM(??) AS Total FROM grade
• ????????????????,????????Total?

20
AVG??COUNT??MAX??MIN??

• (2)AVG??,??????????????
• ?. SELECT AVG(??) AS Average FROM grade
• ?????????????????,????????Average?
• (3)COUNT??,?????????????
• ?. SELECT COUNT() AS Counts FROM grade
• ????????????????
• (4)MAX??,??????????????
• ?. SELECT MAX(??) AS First FROM grade
• ??????????????????,????????First?
• (5)MIN??,?????????????
• ?. SELECT MIN(??) AS Last FROM grade
• ??????????????????,????????Last?

21
????

• ??????GROUP BY?HAVING????????????????????,????????
  ?????
• ????SELECT ??, AVG(??) AS ??? FROM grade GROUP
  BY ?????????????

22
????

• ??????????,?4??????
• (1)WHERE??????GROUP BY?????
• (2)HAVING??????????????????
• (3)SELECT????????????????????
• (4)HAVING????GROUP BY?????

23
????

• ??3?????(Student)????(Course)????(SC)??????5-14??
  ?

24
????

• ??????????????????????????SQL???
• SELECT b.sname FROM sc a, student b, course c
• WHERE a.cnoc.cno
• AND a.snob.sno and c.cname????
• ??sc a????sc?????a,??student
  b??student?????b??????????????????

25
??SQL?????????

• ?ASP???,??????SQL???????????????SQL???????SQL????
  ?
• ??SQL??????SELECT??,SQL??????INSERT???UPDATE???DE
  LETE???

26
??SQL????

• ??SQL???????5-01.asp???
• ????????????Select??????????If Request(SQL)
  ltgt Then???????,????????????person.mdb??????
• ??Function rstotab(rs)??????????????????????????
  ????,????????????

27
??5-1 ??????

• ?????????????,??????????????,?????????????????????
  ??????????
• ???mh.asp???

28
?????SQL???

• ???????????????SQL?????SQL Select from
  grade where ?? like Key
  ???Key???,????????????????????????
• (1)?????SQL??,SQL " Select from grade where ??
  like '?'",??????????,?????????,?SQL???,?????????
  ???
• (2)????????,???????????Key???????????,??????
  ???,?????????,???????????????,?????????????
  

29
??SQL????

• ????SQL????,???5-02.asp???
• ???SQL??????,?????,??????,???????????

30
????????

• ?????????????,??Access2000/XP??????lyb.mdb??????,?
  ?lyb???
• ????????Now(),???????????????????????
• l         input.htm,????????
• l         handle.asp,????
• l         display.asp,????

31
??

• ???????????????????????????SQL?????
• ????????????????,??????????????
• ??????????????????????ASP????????????????

32
????

• ?????
• 1. ??????????????___________?
• A) ??? B) ???
• C) ??? D) ???
• 2. ?????????????___________?
• A) ?? B) ??
• C) ? D) ??
• 3. ???SQL????, ??DML??___________?
• A) SELECT B) UPDATE
• C) SAVEPOINT D) GRANT
• 4. ??___________??????????
• A) COUNT B) MIN
• C) AVG D) NUM
• 5. ????????,????????___________?
• A) WHERE??????GROUP BY?????
• B) HAVING??????????????????
• C) SELECT????????????????????
• D) HAVING????GROUP BY?????

33
????

• 1. 90????,???___________,?????????????,???????????
  ?
• 2. ???????????????(SELECT)?___________?__________
  _???(DELETE)?
• 3. ????????????,????_____________________________
  ____?

34
???????????

• 1. ???????????
• 2. ???SQL??? ??SQL??????
• 3. ??????????? ?????????
• 4. ?????????, ????????????????????????????????????
  ????