ebook46-23

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 T @@ 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

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, http://www.microsoft.com/sql/ SQL Server 7.0 SQL Server 7.0 120 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 X3.135-1986 ANSJ X3.135-1992 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

23 537 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

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

23 539 A c c e s s Jet SQL ANSI SQL Jet SQL * A N S I S Q L % 23.4.1 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 23.4.2 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

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 23-2 23-1 SELECT SQL 23-2 Access 23-1 SQL 7) 23-3 1) 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 23-4 4) 23-3 23-1 23-2 23-4 23-3 ZIP

23 541 23.4.3 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

542 Access 2) GROU BY 23-6 3) 1997 $ 50 23-7 4) 23-6 23-5 Access 23-7 23-6 23.4.4 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 1 2 3 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

23 543 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-89 23-8 Jet SQL A N D W H E R E 23-9 S Q L J O I N W H E R E 23-9 23-8 WHERE 23.4.5 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

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 2 3-10 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 23-10 SQL UNION 2 3-11 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 29. 1 23-11 23-10 UNION

23 545 23.4.6 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 2 3-12 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 23-12 SQL U N I O N I N S E L E C T S E L E C T I N 23-13 C u s t o m e r I D I NS E L E C T 23-14 2 3-12 S Q L 23-13 SQL 23-14 23-12 23-13

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 10. 10 SQL 23.6 IN V B A A c c e s s Jet SQL IN

23 547 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 23.6.1 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

548 Access 23.6.2 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 23.6.3 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

23 549 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 23.6.4 ANSI Jet SQL Jet SQL ANSI SQL-92 200 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 23.6.5 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 23.6.6 SQL Jet SQL A c c e s s 23-1 ANSI SQL Jet SQL Jet

550 Access A c c e s s Jet SQL 23-1 Access 2000 23-1 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 2 3-1 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 23.6.7 ANSI SQL Access 2 3-2 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

23 551 23-2 ANSI SQL Jet SQL I s N u l l N u l l - 1 0 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 23.6.8 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

552 Access ( ) 2 LIKE 0 9 Jet ANSI SQL 23-4 23-4 Jet SQL S Q L Jet / 23.6.9 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 23-15 5) S Q L S Q L 23-16 S Q L 6) 2, 155 23-17

23 553 S Q L D I S T I N C T R O W 23-15 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 23-16 23-15 SQL 3) 598 23-18 4) 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 23-17 23-16 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

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 23-19 S E L E C T 23-18 Jet SQL DISTINCTROW 23-19 23.6.10 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 23-5 23-5 Jet SQL ANSI SQL

23 555 23.7 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.

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 557 A c t i v e X A D O M E Q SQL Server 23-20 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 23-20 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 0-13-355702-2,1995) Prof.Noam Chomsky M E Q O R M