1.1?Access 2003????

1
1.1?Access 2003????
1.1.1???Access 2003
1.1.2?Access 2003???
1.1.3?Access 2003??????????
1.1.4?Access 2003???????
1.1.5???????????
? ?
2
1.1.1 ??Access 2003
???Microsoft Access 2000??, Access
2003?????????? 1.???????? 2.???????? 3.????????
4.???????? 5.?????? 6.?????? 7.??Web?????? 8.????
?????
3
1.1.2 Access 2003???
??Access 2003??Office 2003?????,??,Access
2003??????????Office 2003????????Office
2003???????Windows XP?Windows NT/2003
Server/Workstation?????????????
????Access 2003???????????,????????Access
2003????????
?Office 2003??????,???Windows?????????????????????
,?????????????????Microsoft Office?????????????
?????Microsoft Office??Microsoft Office Access
2003?????Access 2003 ?
4
1.1.3 Access 2003??????????

• Access 2003??????Access 2003?????,?Access
  2003?????????????????????????????
• ???,?????????????????????Access
  2003??????????????Access 2003??????????????????,??
  ?????????????????????????????
• ??????????????????????????????

5
1.1.4 Access 2003???????

• Access 2003?????????????????????(????????),???
  ????(????????),??????(??????)?

6
1.1.5 ??????????

• ?????????,??????????????
• ?????????,??????????????
• ??????????????????
• ?????????????????????????????????????

7

• Access 2003????????

8

• ??,?????????,????????????,?????????????
  ??????????????????????,?????????????????????????
  ???,??,?????????????,??????????????????????,????
  ????????????????

9
4????????

• ???????,????????????????????????????????
  ?,????????????????,???????????????????????????????
  ?????,???????????????????????????????,????????????
  ???????,?????????????????,????????????
• ??????????????????????????????????????
  ??????????????????????????????????,?????????,?????
  ?????,?????????????????

10
5??????????

• ??????,????????????????????????????????
  ??????????????????,???????????????????????????????
  ??????????????????????????????????????????

11
6???????????????

• ???????????????????????????????,??????
  ??????(DBMS)??,??????????????????(DBA,Dada Base
  Administrator)???????,????????????????????????????
  ?

12
7?????????

• ???????,????????????????????????????????
  ??,???????????????,?????????,???????????????????,?
  ???????????????????????????????????,???????,??????
  ????????????????????,?????????????????
• ??,???????????????,????????????????1-1
  ??,????????????,????????

13
?1-1 ???????
????? ?? ?? ?? ???? ???? ???? ???? ???? ????
28 1001 ??? A4-63g ? ?85.00 2 ?170.00 2002-3-11 ???
29 1002 ??? A4-73g ? ?95.00 1 ?95.00 2002-3-11 ???
30 1001 ??? A4-63g ? ?85.00 2 ?170.00 2002-3-11 ??
31 4101 ??? 3.5 ? ?13.20 5 ?66.00 2002-3-12 ??
32 ?85.00 ?? 1.44 ? ?4.30 3 ?85.00 2002-3-12 ???
33 2 2 2 2 ?0.88 8 2 2002-3-13 ???
34 ?170.00 ?170.00 ?170.00 ?170.00 ?4.30 3 ?170.00 2002-3-13 ??
35 2002-3-11 2002-3-11 2002-3-11 2002-3-11 2002-3-11 1 2002-3-11 2002-3-11 ???
14

• ??????????????????,?????????????,??????
  ??????????????????????????,?????????????????????,?
  ???????????????????,??????????1-2???

15
?1-2 ???????????????
?? ?? ?? ???? ?????? ??????
1001 ??? A4-63g ? 4 ?340.00
1002 ??? A4-73g ? 1 ?95.00
3101 ?? 1.44 ? 6 ?25.80
4101 ??? 3.5 ? 6 ?79.20
6501 T?? BNC ? 8 ?7.04
16

• ???????????????????????????????????????????
  ????????????????,????????????,???????????????,????
  ??1-3????????

17
?1-3 ???????????????
???? ?????? ??????
?? 10 ?248.90
??? 4 ?278.20
??? 11 ?19.94
18

• ???????,??????????????????,?????????????????
  ????????????????????????,???????????????????,?????
  ??????????????????????????,???????????,???????????
  ??????????,??,????????????????????????????

19
8???????????????

• ?????????????????????????,??????????????,?????
  ???????????,???????????????????
• ?????,????????????,????????????????????
  ??????????????????,?????????????????????????????,?
  ????????????????

20
9????????????

• ?????????????????,??????????,??????????????,??
  ?????????????????????????????????,????????????????
  ???????,???????,????????????

21
1.1.2???????????

• ??????,????????????????,??????????????????,?
  ??????????????,??????????????????????
• ????????,??????????????????????,?????
  ?????????????????,??????????????????????,???????
  ???????????
• ????????????,????????????????????????
  ???,????????????????????,?????????????????????????
  ????,??????????(RDBS,Relation Data Base
  System)????????????????????????,Access???????

22

• ????????,??????????????????????????????,????
  ??????????????(???),??????????????????????????????
  ????????????????,??????????????????????(RDBMS,Rela
  tion Data Base Management System)?
• ?????????????????(RDBMS)?,?????????
  ??????????????????????????????????Access??????????
  ?????

23
1.1.2?????????????
1?????
2.????
3.???
4.???
24
1.????

• ?????????(Field)?,????????????????????
  ??(?????)?????????????????????????,???????????????
  ,???????????????????????????,????????????????????
  ????,????,????,???

25
2.????

• ?Access?,?????????(Record)????????????????????
  ???,??????????????????????

26
3.???

• ??????????????????????????????????????
  ?????,????????????????????,???????????????????????
  ?????,??????????????

27
4.???

• ??????????????????????????????,??????????????,
  ??????????,????????????????,??????????????????????
  ???

28

• Access????????????????,???????????????
  ??????????,??????.MDB?????????????????(??????????
  ??),???????????(????????????????VBA????)???,??Acce
  ss???????????????????????.MDB????????Access?????
  ???.MDB????,??????????????????????,?????????????
  ???????????????????1-1???Access????????

29
?1-1 Access???????
30
1.1.3????????SQL

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

31
1.1.3????????SQL
1.????
2.???????SQL??
32
1.????
?????????????????????,?????????????,?????????????,
??????????????????????????,????????????
(1)????
(2)????
(3)????
33
(1)????

• ????????????????????????????????????,??????
• SELECT ??? WHERE ??
• ??,?????????????????????????????????????????
• ??,???1-1??????????,??????2002/03/12?2002/03/14
  ??????????,?????????
• SELECT ??????? WHERE ????gt2002/03/12 AND
  ????lt02/03/14
• ?????????????????,??1-4???

34
(2)????

• ?????????????????????????????????,??????
• PROJECT ???(???1,???2,,???n)
• ??,?????????????,?????????1-5???

35
?1-5 ???????
?? ?? ?? ???? ???? ???? ???? ???? ??? ???
1001 ??? A4 63G ? 18.60 39.00 725.40 2002-3-11 ?? ?????
1002 ??? A4-73g ? 88.00 22.00 1,936.00 2002-3-11 ?? ?????
1007 ??? A4 70G ? 18.60 100.00 1,860.00 2002-3-11 ?? ?????
2102 ??? HP 6L ? 56.40 8.00 451.20 2002-3-11 ?? ?????
3101 ?? 1.44 ? 4.30 100.00 430.00 2002-3-11 ?? ????
3103 ?? 1.44 ? 1.72 100.00 172.00 2002-3-11 ?? ????????
4101 ??? 3.5 ? 13.20 180.00 2,376.00 2002-3-11 ?? ????????
4201 ??? 3.5CDR ? 78.00 20.00 2,376.00 2002-3-11 ?? ????
5103 ?? CAT5 ? 176.00 10.00 1,760.00 2002-3-11 ?? ????????
5501 ?? ?? ? 241.00 10.00 2,410.00 2002-3-11 ?? ????
6501 T?? BNC ? 0.88 100.00 88.00 2002-3-11 ?? ????
6601 ???? 50? ? 0.63 20.00 12.60 2002-3-11 ?? ????
36

• ????????,?????????????????????????
  ????????????????,?????????
• PROJECT???????(??,??,??,????,???)
• ?????????????????,??1-6???

37
?1-6 ???????
?? ?? ?? ???? ???
1001 ??? A4 63G 18.60 ?????
1002 ??? A4-73g 88.00 ?????
1007 ??? A4 70G 18.60 ?????
2102 ??? HP 6L 56.40 ?????
3101 ?? 1.44 4.30 ????
3103 ?? 1.44 1.72 ????????
4101 ??? 3.5 13.20 ????????
4201 ??? 3.5CDR 78.00 ????
5103 ?? CAT5 176.00 ????????
5501 ?? ?? 241.00 ????
6501 T?? BNC 0.88 ????
6601 ???? 50? 0.63 ????
38
(3)????

• ?????????????????????????????????,????????????,
  ??????
• JION ???1 AND ???2 AND???n WHERE ??
• ??,???????????????????????????????????????????
  ,???1-1??????1-6????,?????????????
• JION ??????? AND???????
• WHERE ???????!?????????!??
• ???????????????,????1-7???????

39
?1-7 ????????????
?? ?? ?? ???? ???? ????
1001 ??? A4-63g ? ?85.00 18.60
1002 ??? A4-73g ? ?95.00 88.00
3101 ?? 1.44 ? ?4.30 4.30
4101 ??? 3.5 ? ?13.20 13.20
6501 T?? BNC ? ?0.88 0.88
40
2.???????SQL??

• ???????(SQL,Structured Query Language)??????????
  ?????????????,???????????????????????,????????????
  ???????????????????????
• ??Access??,??????VBA(Visual Basic
  Application),??????SQL,????SQL????????VBA????
• SQL???????????????,?Access????????????????????????
  ??????????Access????????????SQL??,??????,?????????
  ???????????SQL???????

41
1.1.4 ??????????(RDBMS)

• ????????????????????????????????????????????????,?
  ?,??????????????????,??????????(DBMS,Data Base
  Management System)??????????????????????????(RDBMS
  ,Relation Data Base Management System)?Access??Mic
  rosoft??????????????????

42

• ????????????????????????????????????????,?????
  ???DBMS????????SQL??????????,?????????????????????
  ??????????,??????????????????,????????????????SQL?
  ????????????????????

43

• ????,?????????????????????????????,???????????
  ??Access RDBMS??????????????????????,?????????????
  ??????????????????????????,???????????????????????
  ?????,????????Access RDBMS???????????????

44
???????
1.RDBMS???
2.RDBMS???
45
1.RDBMS???

• RDBMS????????????????????????,????????????????
  ?,?????????????????????????RDBMS???????,??????????
  ????????,?????????????????????
• ???????,?????????,??????????????????,?
  ??????????????,?????????????????????ANSI/SPARC????
  1-2???ANSI/SPARC???

46
?1-2 ANSI/SPARC??
47
???,RDBMS???????????
(1)???????
(2)???????
(3)???????
(4)??????
48
(1)???????

• RDBMS???????(DDL,Data Description
  Language)????????????????????????????,????????????

49
(2)???????

• RDBMS?????????????????????????,?????????,??????
  ?????,???????????????????????????

50
(3)???????

• RDBMS???????????????????,?????????,????????,??
  ?????????????????????,????????????????????????????
  ?

51
(4)??????

• RDBMS?????????????,???????????????(???
  ???)??????????,???????????????????????????????????
  ??????????????????

52
2.RDBMS???

• ???????,RDBMS???????????????????RDBMS?????????
  ???,??????????

(1)????????
(2)????????
(3)????
53
(1)????????

• ????????????
• ? ??????????
• ???????????????????
• ? ??????????
• ???????????????????????????
• ? ??????????
• ?????????????,?????????????
• ? ???????????
• ????????????????,?????????????

54
(2)????????

• ? ??????
• ?????RDBMS????????
• ? ??????
• ?????????,???????,?????????????
• ? ??????
• ????????????,????????????,?????????????????????
• ? ???????
• ???????????????????,????????????,??????????
• ? ??????
• ?????????,??????????????
• ? ??????
• ???????RDBMS??????RDBMS????????

55
(3)????

• RDBMS???????????
• ? ??????
• ?????????????????,??????????
• ? ???????
• ??????????????????,??????????????????????,????????
  ?????
• ? ???????
• ?????????????????????????,??????????????????????

56

• ? ???????
• ?????????,???????????????????????
• ? ????
• ??????????????????????,???????????????????????????
  ??
• ? ??????
• ????????????????????????????,?????????
• ??,RDBMS??????????????????????????????????????
  ?