ebook46-23

Size: px

Start display at page:

Download "ebook46-23"

锏常
7 years ago
Views:

1 23 Access 2000 S Q L A c c e s s S Q L S Q L S Q L S E L E C T S Q L S Q L A c c e s s S Q L S Q L I N A N S I Jet SQL S Q L S Q L 23.1 Access 2000 SQL S Q L A c c e s s Jet SQL S Q L U N I O N V B A S Q L S Q L A c c e s s S Q L Access 2000 Jet 4.0 A c t i v e X ( A D O ) 2. 1 A c t i v e X A D O X 2. 1 S Q L C R E ATE USER A LTER USER DROP USER C R E AT E G R O U P A LTER GROUP DROP GROUP G R A N T R E V O K E A LTER USER PA S S W O R D A LTER DATABASE PA S S W O R D SELECT QueryDef C R E AT E DROP VIEW C R E AT E DROP PROCEDURE Q u e r y D e f A c c e s s S Q L ON UPDAT E C A S C A D E ON DELETE CASCADE ADODB.Connection VBA BeginTr a n s C o m m i t Tr a n s R o l l B a c k Tr a n s B E G I N COMMIT TRANSACTION I D E N T I F Y C R E ATE TABLE S E T I D E N T I T Y S E L E C I D E N T I T Y WITH COMPRESSION U n i c o d e C O N S T R A I N T

2 536 Access P R I M A RY K E Y U N I O N R E F E R E N C E C H E C K A c c e s s S E L E C T C H E C K A LTER TA B L E C O L U M N Access 2000 Jet 4.0 SQL Access 2000 J e t Access Data Project(ADP) S Q L M i c r o s o f t MSDE SQL Server 7.0 Windows NT 2000 SQL Server 7.0 SQL S e r v e r Tr a n s a c t - S Q L M i c r o s o f t S Q L ADO2.1,ADOX 2.1 Microsoft Jet 4.0 OLE DB Jet SQL 18 MSDE ADO 27 OLE DB ADO SQL Server 7.0 Books Online SQL Server 7.0 Transact-SQL SQL Server Books Online O ffice 2000 C D - R O M S Q L Server 7.0 Books Online, SQL Server 7.0 SQL Server Books Online 23.2 Access SQL S Q L s e q u e l s e e k e l, e s s - c u e - e l l / S Q L S Q L S Q L U. S. S Q 1986 ANSI X ANSJ X S Q L - 92 A N S I American National Standards Institute S Q L S Q ANSI SQL S Q L Microsoft SQL Server Tr a n s a c t - S Q L IF...ELSE W H I L E A N S I ANSI SQL- R D B M S A c c e s s S Q L S Q S Q L A c c e s s R D B M S Microsoft SQL Server S Q L Jet SQL A c c e s s S Q L Tr a n s a c t - S Q L, M i c r o s o f t SQL Server ANSI SQL S Q L A c c e s s S Q L S Q L S Q L Jet SQL S Q L S E Q U E A c c e s s A c c e s A c c e s s 2000 Jet SQL V B A Jet SQL V I E W Q u e r y D e f

3 V B A S Q L S Q 23.3 SQL S Q L D Q L S E L E C T D M L I N S E RT D E L E T E U P D AT E T P L BEGIN TRANS[ACTION] COMMIT [TRANSACTION - W O R K ] ROLLBACKT [TRANSACTO N W O R K ] D M L D M L D M L Jet 4.0 SQL BEGIN TRANSACTION COMMIT TRANSACTION R O L L B A C K T R A N S A C T I O N D D L C R E ATE TA B L E C R E ATE VIEW D D L D D L Jet SQL CRAETE TABLE C R E ATE INDEX C C L U P D ATE WHERE CURRENT A D O J e t D C L S Q L G R A N T R E V O K E S Q L SELECT WHERE ORDER BY = < > J O I N Jet SQL J O I N M I N S Q L S Q C o l u m n _ l i s t 23.4 SQL SELECT S E L E C T A c c e s s Jet SQL S E L E C T S Q L Jet SQL Northwind Tr a d e r s S Q L S Q L S Q L S E L E C T SQL SELECT

4 538 Access d y n a s e t R e c o r d s e t SELECT S e l e c t _ l i s t A c c e s s s e l e c t _ l i s t s e l e c t _ l i s t ALL D I S T I N C T D I S T I N C T R O W A L L D I S T I N C T J e t D I S T I N C T R O W S Q L D I N S T I N C T D I S T I N C T R O W D I S T I N C T Jet SQL TOP n [PERCENT] n n TO P P E R C E N T Jet SQL ANSI SQL TO P FROM table_name A c c e s s t a b l e - n a m e s s e l e c t _ l i s t t a b l e - n a m e WHERE Search_criteria S e a r c h _ c r i t e r i a L I K E > = W H E R E W H E R E S E L E C T ORDER BY column_criteria d y n a s e t s n a p s h o t Recordset S n a p s h o t R e c o r d s e t W H E R E ORDER BY A S C D E S C Jet SQL Jet SQL S Q L A c c e s s S Q L A c c e s s Jet SQL S Q L S Q L S Q S Q L C t r l + E n t e r J e t SQL Jet Recordset C u s t o m e r s Z I P 9 s e l e c t _ l i s t

5 A c c e s s Jet SQL ANSI SQL Jet SQL * A N S I S Q L % SQL S Q L ANSI SQL Jet SQL; S Q L Name,Address,city,zip Jet SQL [Company Name] ANSI SQL Server 7.0 Tr a n s a c t - S Q L Company Name C u s t o m e r. [Company Name] ANSI SQL Jet Access VBA S Q L ANSI SQL % - Jet SQL * L I K E J e t D O S J e t L I K E J e t / ANSI SQL Jet SQL Jet SQL ANSI SQL Jet SQL A c c e s s A c c e s s V B A / R D B M S ADO ADODB.Command A D O A c c e s s S Q L ANSI SQL L I K E not in A N S I S Q L = JET SQL < > A N S I JET SQL S Q L SQL SQL SQL Access S Q L A c c e s s S Q L A c c e s s S Q L A c c e s s S Q L S Q L A c c e s s 1) N o r t h w i n d. m d b 2) 3) S Q L S Q 1 S Q L

6 540 Access 4) S E L E C T S Q L 5) S Q L S Q L C t r l + E n t e r S Q L 23-1 SQL Access " SQL ' 6) A c c e s s S Q L SELECT SQL 23-2 Access 23-1 SQL 7) ) S Q L S Q 2) ORDER BY CompanyName Order BY PostalCode P o s t a l C o d e C o m p a n y N a m e Z I P 3) Z I P ) ZIP

7 SQL S Q L GROUP BY H AV I N G GROUP BY S e l e c t _ l i s t f i e l d - n a m e GUOUP BY S Q L As alias-name A c c e s s alias:aggregate_function(field name) GROUP BY group_criteria GROUP BY GROUP BY H AVING aggregate_criteria a g g r e g a t e _ f u n c t i o n H AV I N G a g g r e g a t e _ c r i t e r i a H AV I N G WHERE Search_criteria a g g r e g a t e _ c r i t e r i a a g g r e g a t e _ f u n c t i o n A c c e s s H AV I N G W H E R E W H E R E / R D B M S ANSI SQL As alias_name Microsoft SQL S e r v e r Sybase System 10+ IBM DB2 A s SELECT field_name a l i a s _ n a m e,... M S D B SQL Server 7 A s O D B C A c c e s s / A N S I A S V B A / R D B M S A D O A c c e s s S Q L / RDBMS AS GROUP BY ANSI SQL # S h i p R e g i o n ( ), 1997 Ship Region F r e i g h t A c c e s s SQL GROUP BY 23-5 SUM SQL 1) S Q L S Q L GROUP BY 23-5

8 542 Access 2) GROU BY ) 1997 $ ) Access SQL A c c e s s J O I N... O N J O I N table_name{inner LEFT RIGHT} JOIN join_table t a b l e _ n a m e s J O I N J O I N I N N E R L E F T R I G H T I N N E R L E F T R I G H T A c c e s s ON join_criteria j o i n _ t a b l e t a b l e _ n a m e s J o i n _ c r i t e r i a = Tr u e F a l s e Tr u e J O I N 1 J O I N

9 A c c e s s J O I N 23-8 N w i n d. m d b Orders,Order Details, P r o d u c t s C a t e g o r i e s J O I N 23-8 S Q L S Q L J O I N O N Jet SQL J O I ANSI SQL W H E R E 23-8 Jet SQL J O I N O N W H E R E S Q L J O I N W H E R E J O I N... O N B E T W E E N _ A N D L I K E < > J O I N O N = ANSI SQL Jet SQL A N D W H E R E 23-9 S Q L J O I N W H E R E WHERE S E L E C T N o r t h w i n d. m d b S Q L U N I O N U N I O N

10 544 Access S E L E C T UNION SELECT f i e l d _ l i s t f i e l d _ l i s t ORDER BY UNION SELECT S E L E C T UNION SELECT G R O U P BY H AV I N G S Q L C u s t o m e r s S u p p l i e r s U N I O N S Q L U N I O N Customer ID S u p p l i e r I D I SQL UNION S u p p l i e r s UNION S Q L SELECT Country FROM Customers UNION SELECT ' A L L ' FROM Customers ORDER BY Country; ( A l l ) A S C I I 40 A A S C I I 65 VBA ASC ASCII U N I O N A L L UNION

11 A c c e s s ANSI SQL Access 2000 S E L E C T S e l e c t W H E R E J a n, 1, 1997 J u n e, 30, 1997 Northwnd Tr a d e r s S Q L I N S E L E C T O r d e r s C u s t o m e r I D C u s t o m e r s C u s t u m e r I D C u s t o m e r I D SQL U N I O N I N S E L E C T S E L E C T I N C u s t o m e r I D I NS E L E C T S Q L SQL

12 546 Access 23.5 D M L A c c e s s Jet SQL T R A N S F O R M P I V O T D M L A c c e s s W H E R E S o u r c e _ t a b l e d e s t _ t a b l DELETE FROM table_n a m e [WHERE d e l e t e _ c r i t e r i a] W H E R E t a b l e _ n a m e * s e l e c t _ l i s t W H E R E S E T Jet SQL ANSI SQL VA L U E Jet SQL T R A N S F O R M P I V O T S Q L T R A N S F O R M P I V O T GROUP BY I N J O I N A c c e s s A c c e s s S Q L SQL 23.6 IN V B A A c c e s s Jet SQL IN

13 A c c e s s A c c e s S Q L A c c e s s F o x P r o d B A S E P a r a d o x S Q L I N I I N F o x p r o d B A S E P a r a d o x Access I N A c c e s s I N 1)...\ O ff i c e \ S a m p l e s N w i n d. m d b 2) N o r t h w i n d. m d b S Q L S Q 3) S Q L SELECT *INTO Customers IN "Nwind.mdb"FROM Customers; S E L E C T _ I N TO 4) N w i n d C u s t o m e r s O L E C u s t o m e r s N w i n d. m d b 5) S Q L DELETE * FROM Customers IN "Nwind.mdb" D E L E T E.... F R O M 6) N w i n d C u s t o m e r s Jet DDL IN DROP TABLE CREATE TABLE N w i n d C u s t o m e r s 1) N w i n d C u s t o m e r s 2) 3) S Q L S Q L I N S E RT INTO Customers SELECT *FROM Customers IN"Northwind.mdb" I N S E RT INTO N w i n d N o r t h w i n d. m d b 4) N o r t h w i n d. m d b C u s t o m e r s S Q L

14 548 Access IN I N d B A S E P a r a d o x Jet SQL IN"[drive:\] path" "database_type" \ A c c e s s " " D a t a b a s e _ t y p e Access 2000 ISAM DLL dbase Paradox 4.x dbase Paradox 5.x dbase 5 Paradox 7.0 Paradox 3.x A c c e s s dbase iii dbase 7.0 P a r a d o x 8. 0 B o r l a n d B D E 4 + B D E I n p r i s e Borland International B D E h t t p :// w w w. i n p r i s e. c o m F o x P r o Access 2000 O ffice Foxpro ODBC Vfpodbc.dll dbase D B a s e I N Jet DDL Jet 4.0 DDL S Q A c c e s s D D L A c c e s s Access 2000 SQL DDL C R E ATE TABLE table_name (field_name data_type [(field_size)] [, field_name data_type...]) [] Jet SQL T E X T f i e l d _ s i z e I N T E G E R f i e l d _ s i z e 50

15 f i e l d _ s i z e f i e l d _ s i z T E X T J e t T E X T M e m o ( ) Tr a n s a c e - S Q L Jet SQL CHARACTER VRAY I N G CHAR VA RY I N G, VA R C H A R N ATIONAL CHAR N ATIONAL# CHAR VA RY I N G Unicode CONSTRAINT index_name {PRIMARYKEY UNIQUE REFERENCES foreign_table [(foreign_field)] } P R I M A RY K E Y U N I Q U E REFERENCES foreign_table [foreign_field] f o r e i g n _ f i e l d [ f o r e i g n _ f i e l d ] CHECK e x p r e s s i o n A c c e s s E x p r e s s i o n S E L E C T C R E ATE [UNIQUE] INDEX index_name ON table_name (field_name [ASC DESC][,field_name [ASC DESC],...]) [WITH {PRIMARY DISALLOW NULL IGNORE N U L L }] WITH PRIMARY U N I Q U E DISALLOW NULL N U L L IGNORE NULL N u l l A LTER TA B L E ADD COLUMN field_name... DROP COLUMN field_name... A LTER COLUMN table_name (field_name data_type [field_size]) DROP INDEX index_name ON table_name t a b l e _ n a m e DROP TABLE table_name ANSI Jet SQL Jet SQL ANSI SQL S Q L P C R D B M S S Q L J e t S Q L Access VBA A c c e s s ANSI SQL Access SQL A c c e s s JET SQL ANSI Jet S Q L _ 92 ANSI SQL Jet SQL ANSI A N S I R D B M S S Q L Jet SQL A c c e s s S Q L S Q V B A Jet SQL 9 A c c e s s ANSI SQL A c c e s s SQL Jet SQL A c c e s s 23-1 ANSI SQL Jet SQL Jet

16 550 Access A c c e s s Jet SQL 23-1 Access Jet ANSI SQL C H A R [ A C T E R ] F L O AT I N T [ E G E R ] R E A L Jet SQL S Q L 23-3 ANSI SQL Jet SQL = <, <= > > =A c c e s s < > ANSI SQL = ANSI SQL Jet SQL I N W H E R E I N I N ANSI SQL Access Jet SQL ANSI SQL Jet Jet ANSI SQL ANSI SQL S U M DISTINCT field_name Jet S U M S U S Q L Jet SQL A n d O r Jet SQL A N D O R

17 ANSI SQL Jet SQL I s N u l l N u l l Jet IsNull ANSI SQL W H E R E IS NULL IS NOT NULL Jet SQL d i s t i n c t AV G DISTINCT field_name Jet S E L E C T D I S T I N C T R O W ANSI SQL Jet SQL Jet SQL ANSI SQL 23-3 J e t ANSI SQL Jet DDL V B A S Q L D I S T I N C T R O W P I V O T T R A N S F O R M Jet SQL Server 6.5 Tr a n s a c t - S Q L R O L L U P C U B E P I V O T T R A N S F O R M 23-3 ANSI SQL Jet SQL Jet Jet Byte 1 Jet Jet Date/Time Jet

18 552 Access ( ) 2 LIKE 0 9 Jet ANSI SQL Jet SQL S Q L Jet / Jet DISTINCTROW SQL DISTINCT SQL SELECT Jet SQL DISTINCTROW Jet D I S T I N C T R O W Jet DISTINCTROW SQL 1) N o r t h w i n d. m d b N o r t h w i n d. m d b 2) C u s t o m e r s O r d e r s Order Details P r o d u c t s Categories Access 3) C o m p a n y N a m e C u s t o m e r s F 4 4) C a t e g o r y N a m e C a t e g o r i e s ) S Q L S Q L S Q L 6) 2,

19 S Q L D I S T I N C T R O W ANSI SQL D I S T I N C T Jet SQL D I S T I N C T R O W 1) S Q L S Q L 2) S Q L S E L E C T D I S T I N C T R O W SQL 3) ) S Q L S Q L D I S T I N C T D I S T I N C T R O W 5) D I S T I N C T R O W 598 6) S Q L S Q L SQL SQL D I S T I N C T R O W Jet SQL A N S I S Q L D I S T I N C T R O W ANST SQL D I S T I N C T ANST SQL D I S T I N C T D I S T I N C T D I S T I N C T R O W D I S T I N C T R O W

20 554 Access L a s t _ N a m e F i r s t _ N a m e 10 L a s t _ N a m e S m i t h F i r s t _ N a m e L a s t _ N a m e F i r s t _ N a m e D I S T I N C T R O W 10 S m i t h F i r s t _ N a m e D I S T I N C T F i r s t _ N a m e A c c e s s 9 7 D I S T I N C T D I S T I N C T R O W A c c e s s 97 D I S T I N C T R O W A c c e s s D I S T I N C T D I S T I N C T R O W S E L E C T Jet SQL DISTINCTROW Iet SQL ANSI SQL Jet 4.0 ANSI SQL S Q L Access VBA Jet 4.0 A c c e s s ANSI SQL Jet SQL ANSI SQL

21 SQL S Q L S Q L S Q L S Q S Q L S Q L Q u e r y D e f S Q L Access VBA O p e n R e c o r d s e t s t r S o u r c e Access VBA S Q L S Q A D O D B. R e c o r d s e t A c c e s s A D O Access 2000 RunSQL( ) S Q L RunSQL( ) Access 2000 Jet SQL S Q L V B A 23.8 S Q L A c c e s s t a b l e _ l i s t f i e l d _ l i s t J e F R O M Jet S Q L S Q 23.9 SQL A c c e s s S Q L S Q L V B A A c c e s s S Q L V B A A c c e s s S Q L V B A Microsoft Office, Visual Basic Vi s i o S Q L M i c r o s o f t M D X S H A P E Microsoft OLAP O L A P i v o t Ta b l e SQL Server 7.0SQL Server 7.0 O A L P M i c r o s o f t Access OLAP S Q L V B A V B V B S c r i p t ( V B A ) D A P V B S c r i p t D A P 18 1.

22 556 Access V B A S Q L S E L E C T W H E R E W H E R E P r o d u c t, R e g i o n, S t a r t D a t e End Date W H E R E V B A A L L A L L W H E R E 2 9 V B A 2. Microsoft M i c r o s o f t M E Q SQL SELECT M E Q SQL Server 7.0 Access 2000 S Q L MEQ SQL Server 6.5 MEQ SQL Server 7.0 MSDE Jet 4.0 MEQ M E Q Who wrote the most books in 1998? Which authors had the highest royalties in 1997? S Q L SQL Server 7.0 P u b s M S D E S Q L M E Q h t t p :// w w w. m i c r o s o f t. c o m / s q l / 70 / g e n / e q. h t m M E Q h t t p :// w w w. m i r c r o s o f t. c o m / s q l / 70 / w h p p r s / e q p. h t m We b M E Q h t t p : / / b a c k o ff i c e. m i r c r o s o f t. c o m / s h o w c a s e / l i v e d e m o / e q d e m o / Microsoft SQL Server We b M E Q E x p e d i a What hotels near San Francisco have swimming pools? M E Q What hotels near San Francisco have pools? S Q L

23 A c t i v e X A D O M E Q SQL Server E x p e d i a 20 M i c r o s o f t N L P M E Q O ff i c e M E Q O ff i c e h t t p :// r e s e a r c h. m i c r o s o f t. c o m / n l p / N L P M E Q M E Q M E H o t e l P r o p e r t y hotels have pools hotels are in cities Microsoft Microsoft English Query Demo Expedia M E Q P r o f. Terry Halpin O R M Visio Corp. I n f o M o d e l e r I n f o M o d e l e r Visio Enterprise I n f o M o d e l e r 22 O R M O R h t t p :// w w w. v i s i o. c o m / p r o d u c t s / i t - s o l u t i o n s / d a t a b a s e / e n t e r p r i s e / i n d e x. h t m l I n f o M o d e l e r O R M H a l p i n Conceptual Schema & Relational Database Design,Second Edition(ISBN ,1995) Prof.Noam Chomsky M E Q O R M

幻灯片 1

幻灯片 1 沈阳工业大学 2014 年 6 月第 7 章数据库技术基础主要内容 : 7.1 数据库概述数据库基本概念数据模型逻辑数据模型数据库系统的产生和发展常用的数据库管理系统 7.2 Access 2010 数据库创建及维护创建 Access 2010 数据库创建