Chapter 22 – Database: SQL, MySQL,
DBI and ADO.NET
Outline
22.1
22.2
22.3
22.4
Introduction
Relational Database Model
Relational Database Overview: Books.mdb Database
SQL (Structured Query Language)
22.4.1 Basic SELECT Query
22.4.2 WHERE Clause
22.4.3 ORDER BY Clause
22.4.4 Merging Data from Multiple Tables: INNER JOIN
22.4.5 Joining Data from Tables Authors, AuthorISBN,
Titles and Publishers
22.4.6 INSERT Statement
22.4.7 UPDATE Statement
22.4.8 DELETE Statement
 2004 Prentice Hall, Inc. All rights reserved.
Chapter 22 – Database: SQL, MySQL,
DBI and ADO.NET
22.5
22.6
22.7
22.8
MySQL
Introduction to DBI
22.6.1 Perl Database Interface
22.6.2 PHP dbx module
22.6.3 Python DB-API
ADO.NET Object Model
Web Resources
 2004 Prentice Hall, Inc. All rights reserved.
Objectives
• In this lesson, you will learn:
– To understand the relational database model.
– To be able to write database queries using SQL (Structured
Query Language).
– To understand the MySQL database server.
– To learn various database interfaces.
– To understand ADO.NET’s object model.
 2004 Prentice Hall, Inc. All rights reserved.
22.1 Introduction
• Database
– Integrated collection of data
– Database management system (DBMS)
• Store and organize data consistent with database’s format
• Relational database
– SQL (Structured Query Language)
• Queries
• Manipulate data
 2004 Prentice Hall, Inc. All rights reserved.
22.2 Relational Database Model
• Composed of tables
• Row
– Number column
– Primary key
• Reference data in the table
• A column or set of columns in table contains unique data
 2004 Prentice Hall, Inc. All rights reserved.
22.2 Relational Database Model
Row
number
name
department
salary
location
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Angeles
35761
Myers
611
1400
Orlando
47132
Neumann
413
9000
New Jersey
78321
Stephens
611
8500
Orlando
Primary key
Fig. 22.1
Column
Relational database structure of an Employee table.
 2004 Prentice Hall, Inc. All rights reserved.
22.2 Relational Database Model
Fig. 22.2
department
location
413
New Jersey
611
Orlando
642
Los Angeles
Table formed by selecting department and location data from the Employee table.
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
• Primary key uniquely identifies each row
– Rule of Entity Integrity
• Composite primary key
• Lines connecting tables
– Relationships
• One-to-many relationship
• Foreign key
– Join multiple tables
– Rule of Referential Integrity
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
F ie ld
authorID
D e s c rip tio n
A u th o r’s ID n u m b er in th e d atab ase. In th e B o o k s . m d b
d atab ase, th is I n t e g e r co lu m n is d efin ed as autoincrem ent. F o r each n ew ro w in serted in th is tab le, th e
d atab ase in cre m en ts th e a u t h o r I D v alu e, en su rin g th at
each ro w h as a u n iq u e a u t h o r I D . T h is co lu m n rep resen ts
th e tab le’s p rim ary k ey.
f i r s t N a m e A u th o r’s first n am e (a S t r i n g ).
l a s t N a m e A u th o r’s last n am e (a S t r i n g ).
F ig . 2 2 .3 A u t h o r s ta b le fro m B o o k s . m d b .
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
authorID firstName
1
H arv ey
2
P au l
3
T em
4
K ate
5
S ean
6
T ed
7
P rav een
8
D av id
9
C h eryl
10
M arin a
11
B en
12
Jo n ath an
F ig . 2 2 .4 D a ta fro m th e
Books.mdb.
 2004 Prentice Hall, Inc. All rights reserved.
lastName
D eitel
D eitel
N ieto
S tein b u h ler
S an try
L in
S ad h u
M cP h ie
Y aeg er
Z latk in a
W ied erm an n
L ip eri
A u t h o r s ta b le o f
22.3 Relational Database Overview:
Books.mdb Database
F ie ld
publisherID
D e s c rip tio n
T h e p u b lish er’s ID n u m b er in th e d atab ase. T h is
au to -in crem en ted I n t e g e r is th e tab le’s p rim ary
k ey.
p u b l i s h e r N a m e T h e n am e o f th e p u b lish er (a S t r i n g ).
F ig . 2 2 .5 P u b l i s h e r s ta b le fro m B o o k s . m d b .
publisherID publisherName
1
P ren tice H all
2
P ren tice H all P T G
F ig . 2 2 .6 D a ta fro m th e P u b l i s h e r s ta b le o f B o o k s . m d b .
F ie ld
authorID
D e s c rip tio n
T h e au th o r’s ID n u m b er, w h ich allo w s th e d atab ase
to asso ciate each b o o k w ith a sp ecific au th o r. T h e
in teg er ID n u m b er in th is co lu m n m u st also ap p ear
in th e A u t h o r s tab le.
isbn
T h e IS B N n u m b er fo r a b o o k (a S t r i n g ).
F ig . 2 2 .7 A u t h o r I S B N ta b le fro m B o o k s . m d b .
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
authorID
isbn
authorID
isbn
1
0 130 895 725
2
0 139 163 050
1
0 132 261 197
2
0 130 284 19x
1
0 130 895 717
2
0 130 161 438
1
0 135 289 106
2
0 130 856 118
1
0 139 163 050
2
0 130 125 075
1
0 130 284 19x
2
0 138 993 947
1
0 130 161 438
2
0 130 852 473
1
0 130 856 118
2
0 130 829 277
1
0 130 125 07 5
2
0 134 569 555
1
0 138 993 947
2
0 130 829 293
1
0 130 852 473
2
0 130 284 173
1
0 130 829 277
2
0 130 284 181
1
0 134 569 555
2
0 130 895 601
1
0 130 829 293
3
0 130 284 19x
1
0 130 284 173
3
0 130 161 438
1
0 130 284 181
3
0 130 856 118
1
0 130 895 601
3
0 134 569 555
2
0 130 895 725
3
0 130 829 29 3
2
0 132 261 197
3
0 130 284 173
2
0 130 895 717
3
0 130 284 181
2
0 135 289 106
4
0 130 895 601
F ig . 2 2 .8
D a ta fro m A u t h o r I S B N ta b le in B o o k s . m d b .
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
F ie ld
isbn
title
editionNumber
copyright
description
publisherID
D e s c rip tio n
IS B N n u m b er o f th e b o o k (a S t r i n g ).
T itle o f th e b o o k (a S t r i n g ).
E d itio n n u m b er o f th e b o o k (a S t r i n g ).
C o p yrig h t year o f th e b o o k (an I n t e g e r ).
D escrip tio n o f th e b o o k (a S t r i n g ).
P u b lish er’s ID n u m b er (an I n t e g e r ). T h is v alu e
m u st co rresp o n d to an ID n u m b er in th e
P u b l i s h e r s tab le.
imageFile
N am e o f th e file co n tain in g th e b oo k’s co v er im ag e
(a S t r i n g ).
price
S u g g ested retail p rice o f th e b o o k (a real n u m b er).
[N o te: T h e p rices sh o w n in th is d atabase are fo r
ex am p le p u rp o ses o n ly.]
F ig . 2 2 .9 T i t l e s ta b le fro m B o o k s . m d b .
 2004 Prentice Hall, Inc. All rights reserved.
22.3 Relational Database Overview:
Books.mdb Database
isbn
0 130 923 613
0 130 622 214
0 130 341 517
0 130 649 341
0 130 895 601
0 130 308 978
0 130 293 636
0 130 895 636
0 130 895 512
F ig . 2 2 .1 0
title
editionNumber
P yth o n H o w to P rog ram
1
C # H o w to P ro g ram
1
Java H o w to P rog ram
4
T h e C o m p lete Jav a T rain ing 4
C ou rse
A d v an ced Java 2 Platfo rm
1
H o w to P rog ram
In ternet and W o rld W id e
2
W eb H o w to P ro g ram
V isu al B asic .N E T H o w to
2
P rog ram
T h e C o m p lete C + + T rain ing 3
C ou rse
T h e C o m p lete e-B u sin ess & 1
e-C o m m erce P ro g ram m in g
T rainin g C ou rse
publisherID
price
1
1
1
2
copyright
2 002
2 002
2 002
2 002
1
2 002
$ 69 .95
1
2 002
$ 69 .95
1
2 002
$ 69 .95
2
2 001
$ 109 .95
2
2 001
$ 109 .95
P o rtio n o f th e d a ta fro m th e T i t l e s ta b le o f B o o k s . m d b .
 2004 Prentice Hall, Inc. All rights reserved.
$ 69 .95
$ 69 .95
$ 69 .95
$ 109 .95
22.3 Relational Database Overview:
Books.mdb Database
Fig. 22.11
Table relationships in Books.mdb.
 2004 Prentice Hall, Inc. All rights reserved.
22.4 SQL (Structured Query Language)
S Q L k e yw o rd
SELECT
FROM
WHERE
INNER JOIN
GROUP BY
ORDER BY
INSERT
UPDATE
DELETE
CREATE
DROP
COUNT
F ig . 2 2 .1 2
D e s c rip tio n
S elects (retriev es) co lu m n s fro m o n e o r m o re tab les.
S p ecifies tab les fro m w h ich to g et co lu m n s o r d elete
ro w s. R eq u ired in ev ery S E L E C T an d D E L E T E
statem en t.
S p ecifies criteria th at d eterm in e th e ro w s to b e
retriev ed .
Jo in s ro w s fro m m u ltip le tab les to p ro d u ce a sin g le
set o f ro w s.
S p ecifies criteria fo r g ro u p in g ro w s.
S p ecifies criteria fo r o rd erin g ro w s.
In serts d ata in to a sp ecified tab le.
U p d ates d ata in a sp ec ified tab le.
D eletes d ata fro m a sp ecified tab le.
C reates a n ew tab le.
D eletes an ex istin g tab le.
R etu rn s th e n u m b er o f reco rd s th at satisfy g iv en
search criteria.
S Q L k e yw o rd s .
 2004 Prentice Hall, Inc. All rights reserved.
22.4.1 Basic SELECT Query
• SELECT * FROM tableName
– SELECT * FROM Authors
– SELECT authorID, lastName FROM Authors
 2004 Prentice Hall, Inc. All rights reserved.
22.4.1 Basic SELECT Query
authorID
1
2
3
4
5
6
F ig . 2 2 .1 3
lastName
authorID
lastName
D eitel
7
S adhu
D eitel
8
M cP hie
N ieto
9
Y aeger
S teinbuhler
10
Z latkina
S antry
11
W iederm ann
L in
12
L iperi
a u t h o r I D a n d l a s t N a m e fro m th e A u t h o r s ta b le .
 2004 Prentice Hall, Inc. All rights reserved.
22.4.2 WHERE Clause
• Specify selection criteria for query
– SELECT columnName1, columnName2, … FROM tableName
WHERE criteria
• SELECT title, editionNumber, copyright FROM
Titles WHERE copyright > 1999
– LIKE
• Pattern matching
– Asterisk ( * )
• SELECT authorID, firstName,
FROM Authors WHERE lastName
– Question mark ( ? )
• SELECT authorID, firstName,
FROM Authors WHERE lastName
 2004 Prentice Hall, Inc. All rights reserved.
lastName
LIKE ‘D*’
lastName
LIKE ‘?I*’
22.4.2 WHERE Clause
Title
In tern et an d W o rld W id e W eb H o w to
P ro g ram
Jav a H o w to P ro g ram
T h e C o m p lete Jav a T rain in g C o u rse
T h e C o m p lete e-B u sin ess & e-C o m m erce
P ro g ram m in g T rain in g C o u rse
T h e C o m p lete In tern et & W o rld W id e W eb
P ro g ram m in g T rain in g C o u rse
T h e C o m p lete P erl T rain in g C o u rse
T h e C o m p lete X M L P ro g ra m m in g T rain in g
C o u rse
C H o w to P ro g ra m
C + + H o w to P ro g ram
T h e C o m p lete C + + T rain in g C o u rse
e-B u sin ess an d e-C o m m erce H o w to P ro g ra m
In tern et an d W o rld W id e W eb H o w to
P ro g ram
T h e C o m p lete In tern et an d W o rld W id e W eb
P ro g ram m in g T rain in g C o u rse
 2004 Prentice Hall, Inc. All rights reserved.
editionNumber
2
copyright
2002
4
4
1
2002
2002
2001
2
2001
1
1
2001
2001
3
3
3
1
1
2001
2001
2001
2001
2000
1
2000
22.4.2 WHERE Clause
Jav a H o w to P ro g ram (Jav a 2 )
3
2000
T h e C o m p lete Jav a 2 T rain in g C o u rse
3
2000
X M L H o w to P ro g ram
1
2001
P erl H o w to P ro g ram
1
2001
A d v an ced Jav a 2 P latfo rm H o w to
1
2002
P ro g ram
e-B u sin ess an d e-C o m m erce fo r M an ag ers 1
2000
W ireless In tern et an d M o b ile B u sin ess
1
2001
H o w to P ro g ra m
C # H o w T o P ro g ram
1
2002
P yth o n H o w to P ro g ram
1
2002
V isu al B asic .N E T H o w to P ro g ram
2
2002
F ig . 2 2 .1 4
T itle s w ith c o p yrig h ts a fte r 1 9 9 9 fro m ta b le T i t l e s .
 2004 Prentice Hall, Inc. All rights reserved.
22.4.2 WHERE Clause
authorID
1
2
F ig . 2 2 .1 5
authorID
3
6
11
12
F ig . 2 2 .1 6
firstName
lastName
H arvey
D eitel
P aul
D eitel
A u th o rs fro m th e A u t h o r s ta b le w h o se la st n a m e s sta rt w ith D .
firstName
lastName
T em
N ieto
T ed
L in
B en
W ied erm an n
Jo n ath an
L ip eri
A u th o rs fro m ta b le A u t h o r s w h o s e la s t n a m e s c o n ta in i a s th e ir
s e c o n d le tte r.
 2004 Prentice Hall, Inc. All rights reserved.
22.4.3 ORDER BY Clause
• Arranged in ascending or descending order
– SELECT columnName1, columnName2, … FROM
tableName ORDER BY column ASC
• SELECT authorID, firstName, lastName FROM
Authors ORDER BY lastName ASC
– SELECT columnName1, columnName2, … FROM
tableName ORDER BY column DESC
• SELECT authorID, firstName, lastName FROM
Authors ORDER BY lastName DESC
 2004 Prentice Hall, Inc. All rights reserved.
22.4.3 ORDER BY Clause
authorID
2
1
6
12
8
3
7
5
4
11
9
10
F ig . 2 2 .1 7
firstName lastName
P au l
D eitel
H arv ey
D eitel
T ed
L in
Jo n ath an
L ip eri
D av id
M cP h ie
T em
N ieto
P rav een
S ad h u
S ean
S an try
K ate
S tein b u h ler
B en
W ied erm an n
C h eryl
Y aeg er
M arin a
Z latk in a
A u th o rs fro m ta b le A u t h o r s in a s c e n d in g o rd e r b y
lastName.
 2004 Prentice Hall, Inc. All rights reserved.
22.4.3 ORDER BY Clause
authorID
10
9
11
4
5
7
3
8
12
6
2
1
F ig . 2 2 .1 8
firstName lastName
M arin a
Z latk in a
C h eryl
Y aeg er
B en
W ied erm an n
K ate
S tein b u h ler
S ean
S an try
P rav een
S ad h u
T em
N ieto
D av id
M cP h ie
Jo n ath an
L ip eri
T ed
L in
P au l
D eitel
H arv ey
D eitel
A u th o rs fro m ta b le A u t h o r s in d e s c e n d in g o rd e r b y
lastName.
 2004 Prentice Hall, Inc. All rights reserved.
22.4.3 ORDER BY Clause
authorID
1
2
6
12
8
3
7
5
4
11
9
10
F ig . 2 2 .1 9
firstName lastName
H arv ey
D eitel
P au l
D eitel
T ed
L in
Jo n ath an
L ip eri
D av id
M cP h ie
T em
N ieto
P rav een
S ad h u
S ean
S an try
K ate
S tein b u h ler
B en
W ied erm an n
C h eryl
Y aeg er
M arin a
Z latk in a
A u th o rs fro m ta b le A u t h o r s in a s c e n d in g o rd e r b y
lastName and by firstName.
 2004 Prentice Hall, Inc. All rights reserved.
22.4.3 ORDER BY Clause
isbn
0 1 3 08 9 56 0 1
0 1 3 11 8 04 3 6
0 1 3 08 9 57 2 5
0 1 3 22 6 11 9 7
0 1 3 06 2 22 1 4
0 1 3 52 8 91 0 6
0 1 3 11 7 33 4 0
0 1 3 08 9 57 1 7
0 1 3 02 8 41 9 X
0 1 3 03 0 89 7 8
0 1 3 01 6 14 3 8
title
editionNumber
A d v an ced Jav a 2 P latfo rm 1
H o w to P ro g ra m
C H o w to P ro g ra m
1
C H o w to P ro g ra m
3
C H o w to P ro g ra m
2
C # H o w T o P ro g ram
1
C + + H o w to P ro g ram
2
C + + H o w to P ro g ram
1
C + + H o w to P ro g ram
3
e-B u sin ess an d e1
C o m m erce H o w to
P ro g ram
In tern et an d W o rld W id e 2
W eb H o w to P ro g ram
In tern et an d W o rld W id e 1
W eb H o w to P ro g ram
 2004 Prentice Hall, Inc. All rights reserved.
copyright
2002
price
1992
2001
1994
2002
1998
1994
2001
2001
$ 6 9 .9 5
$ 6 9 .9 5
$ 4 9 .9 5
$ 6 9 .9 5
$ 4 9 .9 5
$ 6 9 .9 5
$ 6 9 .9 5
$ 6 9 .9 5
2002
$ 6 9 .9 5
2000
$ 6 9 .9 5
$ 6 9 .9 5
22.4.3 ORDER BY Clause
0 1 3 03 4 15 1 7
0 1 3 63 2 58 9 0
0 1 3 02 8 41 8 1
0 1 3 09 2 36 1 3
0 1 3 02 9 36 3 6
0 1 3 45 6 95 5 5
0 1 3 06 2 22 6 5
0 1 3 02 8 41 7 3
F ig . 2 2 .2 0
Jav a H o w to P ro g ram 4
2002
$ 6 9 .9 5
Jav a H o w to P ro g ram 1
1998
$ 6 9 .9 5
P erl H o w to P ro g ram
1
2001
$ 6 9 .9 5
P yth o n H o w to
1
2002
$ 6 9 .9 5
P ro g ram
V isu al B asic .N E T
2
2002
$ 6 9 .9 5
H o w to P ro g ra m
V isu al B asic 6 H o w to 1
1999
$ 6 9 .9 5
P ro g ram
W ireless In tern et an d 1
2001
$ 6 9 .9 5
M o b ile B u sin ess H o w
to P ro g ram
X M L H o w to P ro g ram 1
2001
$ 6 9 .9 5
B o o k s fro m ta b le T i t l e s w h o s e title s e n d w ith H o w
t o P r o g r a m in a s c e n d in g o rd e r b y t i t l e .
 2004 Prentice Hall, Inc. All rights reserved.
22.4.4 Merging Data from Multiple Tables:
INNER JOIN
• Normalize databases
– Ensure database does not store data redundantly
– SELECT columnName1, columnName2, … FROM table1
INNER JOIN table2 ON table1, columnName =
table2.columnName
 2004 Prentice Hall, Inc. All rights reserved.
22.4.4 Merging Data from Multiple Tables:
INNER JOIN
firstName lastName isbn
firstName lastName isbn
H arv ey
D eitel
0 130 895 601 H arv ey
D eitel
0 130 856 118
H arv ey
D eitel
0 130 284 181 H arv ey
D eitel
0 130 161 438
H arv ey
D eitel
0 130 284 173 H arv ey
D eitel
0 130 284 19x
H arv ey
D eitel
0 130 829 293 H arv ey
D eitel
0 139 163 050
H arv ey
D eitel
0 134 569 555 H arv ey
D eitel
0 135 289 106
H arv ey
D eitel
0 130 829 277 H arv ey
D eitel
0 130 895 717
H arv ey
D eitel
0 130 852 473 H arv ey
D eitel
0 132 261 197
H arv ey
D eitel
0 138 993 947 H arv ey
D eitel
0 130 895 725
H arv ey
D eitel
0 130 125 075 P aul
D eitel
0 130 895 601
P aul
D eitel
0 130 284 181 P aul
D eitel
0 135 289 106
P aul
D eitel
0 130 284 173 P aul
D eitel
0 130 895 717
P aul
D eitel
0 130 829 293 P aul
D eitel
0 132 261 197
P aul
D eitel
0 134 569 555 P aul
D eitel
0 130 895 725
P aul
D eitel
0 130 829 277 T em
N ieto
0 130 284 181
P aul
D eitel
0 130 852 473 T em
N ieto
0 130 284 173
P aul
D eitel
0 138 993 947 T em
N ieto
0 130 829 293
P aul
D eitel
0 130 125 075 T em
N ieto
0 134 569 555
P aul
D eitel
0 130 856 118 T em
N ieto
0 130 856 118
P aul
D eitel
0 130 161 438 T em
N ieto
0 130 161 438
P aul
D eitel
0 130 284 19x T em
N ieto
0 130 284 19x
P aul
D eitel
0 139 163 050 S ean
S antry
0 130 895 601
F ig . 2 2.2 1 A u th o rs fro m tab le A u t h o r s a n d IS B N n u m b e rs of th e au th o rs ’
b o o ks , so rted in asc e n d in g o rd er b y l a s t N a m e a n d f i r s t N a m e .
 2004 Prentice Hall, Inc. All rights reserved.
1
SELECT Titles.title, Titles.isbn, Authors.firstName,
2
Authors.lastName, Titles.copyright,
3
Publishers.publisherName
4
5
6
FROM
( Publishers INNER JOIN Titles
ON Publishers.publisherID = Titles.publisherID )
7
INNER JOIN
8
( Authors INNER JOIN AuthorISBN
9
10
11
Outline
Fig. 22.22
(1 of 1)
ON Authors.authorID = AuthorISBN.authorID )
ON Titles.isbn = AuthorISBN.isbn
ORDER BY Titles.title
 2004 Prentice Hall, Inc.
All rights reserved.
22.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
Title
isbn
0 1 3 08 9 56 0 1
firstName
P au l
lastName
D eitel
copyright
2002
publisherName
P ren tice H all
A d v an ced Jav a 2 P latfo rm
H o w to P ro g ra m
A d v an ced Jav a 2 P latfo rm
H o w to P ro g ra m
A d v an ced Jav a 2 P latfo rm
H o w to P ro g ra m
C H o w to P ro g ra m
C H o w to P ro g ra m
C H o w to P ro g ra m
C H o w to P ro g ra m
C H o w to P ro g ra m
C H o w to P ro g ra m
C # H o w T o P ro g ram
C # H o w T o P ro g ram
C # H o w T o P ro g ram
C # H o w T o P ro g ram
C # H o w T o P ro g ram
C + + H o w to P ro g ram
C + + H o w to P ro g ram
0 1 3 08 9 56 0 1
H arv ey
D eitel
2002
P ren tice H all
0 1 3 08 9 56 0 1
S ean
S an try
2002
P ren tice H all
0 1 3 11 8 04 3 6
0 1 3 11 8 04 3 6
0 1 3 22 6 11 9 7
0 1 3 22 6 11 9 7
0 1 3 08 9 57 2 5
0 1 3 08 9 57 2 5
0 1 3 06 2 22 1 4
0 1 3 06 2 22 1 4
0 1 3 06 2 22 1 4
0 1 3 06 2 22 1 4
0 1 3 06 2 22 1 4
0 1 3 08 9 57 1 7
0 1 3 08 9 57 1 7
H arv ey
P au l
H arv ey
P au l
H arv ey
P au l
T em
P au l
C h eryl
M arin a
H arv ey
P au l
H arv ey
D eitel
D eitel
D eitel
D eitel
D eitel
D eitel
N ieto
D eitel
Y aeg er
Z latk in a
D eitel
D eitel
D eitel
1992
1992
1994
1994
2001
2001
2002
2002
2002
2002
2002
2001
2001
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
 2004 Prentice Hall, Inc. All rights reserved.
H all
H all
H all
H all
H all
H all
H all
H all
H all
H all
H all
H all
H all
22.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
C + + H o w to P ro g ram
0 1 3 11 7 33 4 0
P au l
D eitel
C + + H o w to P ro g ram
0 1 3 11 7 33 4 0
H arv ey
D eitel
C + + H o w to P ro g ram
0 1 3 52 8 91 0 6
H arv ey
D eitel
C + + H o w to P ro g ram
0 1 3 52 8 91 0 6
P au l
D eitel
e-B u sin ess an d e-C o m m erce 0 1 3 03 2 36 4 0
H arv ey
D eitel
fo r M an ag ers
e-B u sin ess an d e-C o m m erce 0 1 3 03 2 36 4 0
K ate
S tein b u h ler
fo r M an ag ers
e-B u sin ess an d e-C o m m erce 0 1 3 03 2 36 4 0
P au l
D eitel
fo r M an ag ers
e-B u sin ess an d e-C o m m erce 0 1 3 02 8 41 9 X
H arv ey
D eitel
H o w to P ro g ra m
e-B u sin ess an d e-C o m m erce 0 1 3 02 8 41 9 X
P au l
D eitel
H o w to P ro g ra m
e-B u sin ess an d e-C o m m erce 0 1 3 02 8 41 9 X
T em
N ieto
H o w to P ro g ra m
F ig . 2 2 .2 3
P o rtio n o f th e re s u lt s e t p ro d u c e d b y th e q u e ry in F ig .
 2004 Prentice Hall, Inc. All rights reserved.
1994
1994
1998
1998
2000
P ren tice
P ren tice
P ren tice
P ren tice
P ren tice
2000
P ren tice H all
2000
P ren tice H all
2001
P ren tice H all
2001
P ren tice H all
2001
P ren tice H all
2 2 .2 2 .
H all
H all
H all
H all
H all
22.4.6 INSERT Statement
• Inserts new row in table
– INSERT INTO tableName ( columnName1, columnName2,
…, columnNameN ) VALUES ( value1 , value2, …, valueN )
 2004 Prentice Hall, Inc. All rights reserved.
22.4.6 INSERT Statement
authorID
1
2
3
4
5
6
7
8
9
10
11
12
13
F ig . 2 2 .2 4
firstName
H arv ey
P au l
T em
K ate
S ean
T ed
P rav een
D av id
C h eryl
M arin a
B en
Jo n ath an
Sue
T a b le A u t h o r s a fte r a n
ro w .
 2004 Prentice Hall, Inc. All rights reserved.
lastName
D eitel
D eitel
N ieto
S tein b u h ler
S an try
L in
S ad h u
M cP h ie
Y aeg er
Z latk in a
W ied erm an n
L ip eri
S m ith
I N S E R T o p e ra tio n to a d d a
22.4.7 UPDATE Statement
• Modifies data in a table
– UPDATE tableName SET columnName1 = value1,
columnName2 = value2, …, columnNameN = valueN
WHERE criteria
 2004 Prentice Hall, Inc. All rights reserved.
22.4.7 UPDATE Statement
authorID
1
2
3
4
5
6
7
8
9
10
11
12
13
F ig . 2 2 .2 5
firstName
H arv ey
P au l
T em
K ate
S ean
T ed
P rav een
D av id
C h eryl
M arin a
B en
Jo n ath an
Sue
T a b le A u t h o r s a fte r a n
c h a n g e a ro w .
 2004 Prentice Hall, Inc. All rights reserved.
lastName
D eitel
D eitel
N ieto
S tein b u h ler
S an try
L in
S ad h u
M cP h ie
Y aeg er
Z latk in a
W ied erm an n
L ip eri
Jo n es
U P D A T E o p e ra tio n to
22.4.8 DELETE Statement
• Removes data from a table
– DELETE FROM tableName WHERE criteria
 2004 Prentice Hall, Inc. All rights reserved.
22.4.8 DELETE Statement
authorID
1
2
3
4
5
6
7
8
9
10
11
12
F ig . 2 2 .2 6
firstName
lastName
H arv ey
D eitel
P au l
D eitel
T em
N ieto
K ate
S tein b u h ler
S ean
S an try
T ed
L in
P rav een
S ad h u
D av id
M cP h ie
C h eryl
Y aeg er
M arin a
Z latk in a
B en
W ied erm an n
Jo n ath an
L ip eri
T a b le A u t h o r s a fte r a D E L E T E o p e ra tio n to re m o v e
a ro w .
 2004 Prentice Hall, Inc. All rights reserved.
22.5 MySQL
•
•
•
•
•
Multi-user and multi-threaded RDBMS server
Uses SQL to interact with and manipulate data
Supports various programming languages
Access tables from different databases
Handle large databases
 2004 Prentice Hall, Inc. All rights reserved.
22.6 Introduction to DBI
• Uniform access to all database systems
• Access relational databases
 2004 Prentice Hall, Inc. All rights reserved.
22.6.1 Perl Database Interface
• Access relational databases from Perl programs
• Database independent
• Handles
– Driver handles
– Database handles
– Statement handles
 2004 Prentice Hall, Inc. All rights reserved.
22.6.1 Perl Database Interface
D a ta o b je c t h a n d le D e s c rip tio n
D riv er h an d le
E n cap su lates th e d riv er fo r th e d atab ase; rarely u sed
in a P erl scrip t.
D atab ase h an d le
E n cap su lates a sp ecific co n n ectio n to a d atab ase; can
sen d S Q L state m en ts to a d atab ase.
S tatem en t h an d le
E n cap su lates sp ecific S Q L statem en ts an d th e resu lts
retu rn ed fro m th em .
F ig . 2 2 .2 7
D a ta o b je c t h a n d le s fo r P e rl D B I.
 2004 Prentice Hall, Inc. All rights reserved.
22.6.2 PHP dbx module
•
•
•
•
•
•
•
•
Seven functions that interface to database modules
MySQL
PostgreSQL
Microsoft SQL Server
Oracle
Sybase
FrontBase
ODBC (Open Database Connectivity)
 2004 Prentice Hall, Inc. All rights reserved.
22.6.2 PHP dbx module
d b x fu n ctio n
dbx_connect
dbx_close
dbx_error
D e scrip tio n
O pens a connection/database.
C loses an open connection/database.
R eports any error m essages fro m the last
function call in the m odule.
dbx_query
E xecutes a query and returns the resu lts.
dbx_sort
S orts a result by a custo m sort function.
dbx_compare
C o m pares tw o row s and sorts them .
d b x _ e s c a p e _ s t r i n g E scapes a string for use in an S Q L qu ery.
F ig . 2 2 .2 8
D a ta o b je cts fo r P H P d b x m o d u le s.
 2004 Prentice Hall, Inc. All rights reserved.
22.6.3 Python DB-API
• Consists of Connection data objects and Cursor
data objects
• Portable across several databases
 2004 Prentice Hall, Inc. All rights reserved.
22.6.3 Python DB-API
C o n n e c tio n d a ta o b je c t
close
commit
rollback
cursor
F ig . 2 2 .2 9
D e s c rip tio n
C lo ses th e co n n ectio n to th e d atab ase.
C o m m its (sav es) a tran sactio n (i.e., d atab ase u p d ate o p eratio n ).
E x its a p en d in g tran sactio n w ith o u t sav in g ch an g es.
R etu rn s a n ew C u r s o r o b ject o r th e cu rren t co n n ectio n .
C o n n e c t i o n d a ta o b je c ts fo r P yth o n D B -A P I.
C u rs o r d a ta o b je c t
rowcount
D e s c rip tio n
R etu rn s th e n u m b er o f ro w s affected b y th e last ex ecu te
m eth o d call.
close
C lo ses th e C u r s o r o b ject.
e x e c u t e ( o p era tio n )
E x ecu tes a d atab ase q u ery o r state m en t. R etu rn v alu es n o t
d efin ed .
e x e c u t e m a n y ( o p era tio n , E x ecu tes a d atab ase q u ery o r state m en t ag ain st a set o f
p a ra m eters )
p aram eters. R etu rn v alu es n o t d efin ed.
fetchone
R etu rn s th e n ex t ro w o f a q u ery resu lt.
f e t c h m a n y ( size )
R etu rn s a set o f ro w s— d efin ed in th e p aram eter— fro m a q u ery
resu lt.
fetchall
R etu rn s all th e ro w s o f a q u ery resu lt.
F ig . 2 2 .3 0
S o m e C u r s o r d a ta o b je c ts fo r P yth o n -A P I.
 2004 Prentice Hall, Inc. All rights reserved.
22.7 ADO.NET Object Model
• API
– Access database systems programmatically
– Created for the .NET framework
– Primary namespaces
• System.Data
• System.Data.OleDb
• System.Data.SqlClient
 2004 Prentice Hall, Inc. All rights reserved.
22.8 Web Resources
•
•
•
•
•
•
•
•
•
•
•
•
•
•
www.sql.org
www.mysql.com
www.microsoft.com/sql
www.microsoft.com/sql/downloads/default.asp
www.postgresql.org
www.interbase.com
www.maverick-dbms.org
www.devshed.com
www.cql.com
leap.sourceforge.net
www.voicenet.com/~gray/Home.html
msdn.microsoft.com/library/devprods/vs6/vstudio/mdac200/mda
c3sc7.htm
www.w3schools.com/sql
www.sqlmag.com
 2004 Prentice Hall, Inc. All rights reserved.
Descargar

Document