Transcript slides - Plymouth

18 – Structured Query Language
Mark Dixon, SoCCE
SOFT 131
Page 1
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
using SQL
• Objectives,
by end of this week’s sessions, you should be able to:
– Use SQL in your programs to create more
complex record-sets
Mark Dixon, SoCCE
SOFT 131
Page 2
Example: People Database
Person
PersonID Surname
Forenames Gender Phone
eMail
1 Dixon
Mark
Yes 01752 232556 [email protected]
2 Smith
John
Yes 01752 111111 [email protected]
3 Jones
Sally
No 01752 888888 [email protected]
4 Bloggs
Fred
5 Anderson Genny
6 Smith
Mark Dixon, SoCCE
Bob
Yes 01752 123123 [email protected]
No 01752 987987 [email protected]
Yes 01752 898898 [email protected]
SOFT 131
Page 3
Example: People v1
• Display Surname of all people in list box:
Option Explicit
Const cs = "Provider … "
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Person", cs
lstPeople.Clear
Do Until rs.EOF
lstPeople.AddItem rs.Fields("Surname").Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 4
Example: People v2
• Display Surname of Male people in list box:
Option Explicit
Const cs = "Provider …"
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Person", cs
Me.lstPeople.Clear
Do Until rs.EOF
If rs.Fields("Gender").Value = True Then
lstPeople.AddItem rs.Fields("Surname").Value
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 5
Example: People v3
• Display Surname of Male people in list box:
Option Explicit
Const cs = "Provider …"
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Person WHERE Gender = True", cs
Me.lstPeople.Clear
SQL statement
Do Until rs.EOF
lstPeople.AddItem rs.Fields("Surname").Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 6
SQL: Queries
• main purpose of databases:
– get information back out: searching
• Structured Query Language
– dedicated to interacting with databases
• 3rd Generation Language (such as VB, C++)
– code describes how to do task
• 4th Generation Language (such as SQL)
– code describes what to do (not how to do it)
Mark Dixon, SoCCE
SOFT 131
Page 7
SQL: SELECT statement
• SELECT statement
– used to get data
– can be embedded in VB, via rs.Open:
rs.Open "Person", cs
rs.Open "SELECT * FROM [Person]", cs
all fields
Mark Dixon, SoCCE
SOFT 131
Page 8
SQL: WHERE & ORDER BY
• WHERE clause
– used to restrict data
SELECT * FROM [People] WHERE [age]>=18;
• ORDER BY clause
– used to change order of data
SELECT * FROM [People] ORDER BY [Surname];
Mark Dixon, SoCCE
SOFT 131
Page 9
SQL: strings (text data)
• Possible confusion:
SELECT * FROM Person WHERE Surname = Smith
this will look for field called Smith - gives error
need single (SQL) quotes to signify literal text
SELECT * FROM Person WHERE Surname = 'Smith'
Mark Dixon, SoCCE
SOFT 131
Page 10
SQL & MS access queries
• MS Access
– Queries: select data from database
– really SQL select statements
– can use queries to test SQL code
MS Access: People.mdb
Mark Dixon, SoCCE
SOFT 131
Page 11
People Database (with Hobbies)
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
SOFT 131
PersonID
1
1
1
2
2
1
Page 12
SQL: Joining tables
Two tables
SELECT *
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
Matching records
ID
Surname
Forenames
Phone
email
1
Dixon
Mark
01752 232556
[email protected]
1
Archery
1
1
Dixon
Mark
01752 232556
[email protected]
2
Herpetology
1
1
Dixon
Mark
01752 232556
[email protected]
3
Music
1
1
Dixon
Mark
01752 232556
[email protected]
6
Hitting people with swords
1
2
Smith
John
01752 111111
[email protected]
4
Football
2
2
Smith
John
01752 111111
[email protected]
5
Rugby
2
Mark Dixon, SoCCE
HobbyID
SOFT 131
Description
PersonID
Page 13
SQL: Joining tables
SELECT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
1 Dixon
1 Dixon
1 Dixon
2 Smith
2 Smith
Mark Dixon, SoCCE
SOFT 131
Page 14
SQL: DISTINCT records
SELECT DISTINCT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
2 Smith
Mark Dixon, SoCCE
SOFT 131
Page 15
Example: People v4
• User controls what
is displayed:
Option Explicit
Const cs = "Provider …"
Private Sub optAll_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Person", cs
Me.lstPeople.Clear
Do Until rs.EOF
Me.lstPeople.AddItem rs.Fields("Surname").Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Private Sub optMale_Click()
‘ You fill in this code.
End Sub
Private Sub optFemale_Click()
‘ You fill in this code.
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 16
Example: People v5
• User controls what is displayed:
– V4 has 38 lines
– do same with 23
Option Explicit
Const cs = "Provider …"
Private Sub optAll_Click()
‘ You fill in this code.
End Sub
Private Sub optMale_Click()
‘ You fill in this code.
End Sub
Private Sub optFemale_Click()
‘ You fill in this code.
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 17