[ Pobierz całość w formacie PDF ]
SELECT StaffID, Position, Wage
FROM Staff
WHERE NOT (Position = Devel OR Wage
Which gives us:
+---------+---------------+------+
| StaffID | StaffPosition | Wage |
+---------+---------------+------+
| 12351 | Manager | 180 |
| 12347 | Manager | 180 |
+---------+---------------+------+
The following table lists the precedence of SQL s logical operators.
Table B-2. Logical Operator Precedence
Perl Training Australia (http://perltraining.com.au/) 101
Appendix B. Introduction to SQL
Evaluated Logical Operator
First NOT
Second AND
Third OR
IN and BETWEEN
Sometimes it s easier to construct a list of things that something might be in rather than string
together a list ofORconditions. For example the following two statements produce the same results:
SELECT FirstName, LastName, City
FROM Staff
WHERE StaffID = 12345 OR
StaffID = 12347 OR
StaffID = 12349 OR
StaffID = 12351 ;
SELECT FirstName, LastName, City
FROM Staff
WHERE StaffID in ( 12345 , 12347 , 12349 , 12351 );
# Producing:
+-----------+----------+-----------+
| FirstName | LastName | City |
+-----------+----------+-----------+
| Jack | Sprat | Melbourne |
| Betty | Jones | Melton |
| Sam | Smith | Sydney |
| Peter | Pope | Sydney |
+-----------+----------+-----------+
Likewise we can get theStaffIDs of staff who are making between 79000 and 169000 with either of
the following two statements:
SELECT StaffID FROM Staff WHERE Wage > 79 AND Wage
SELECT StaffID FROM Staff WHERE Wage BETWEEN 79 AND 169;
# Producing:
+---------+
| StaffID |
+---------+
| 12345 |
| 12346 |
| 12349 |
+---------+
Fuzzy Comparisons (using LIKE)
Sometimes we want to get all the information for a less well defined set of people, such as everyone
whose first name starts withJor whose last name starts withS. Perhaps we want all cities our
employees live in which start withM. We can do these searching usingLIKE.
SELECT StaffID, FirstName, LastName, City FROM Staff WHERE City Like M% ;
# which gives us:
102 Perl Training Australia (http://perltraining.com.au/)
Appendix B. Introduction to SQL
+---------+-----------+----------+-----------+
| StaffID | FirstName | LastName | City |
+---------+-----------+----------+-----------+
| 12345 | Jack | Sprat | Melbourne |
| 12346 | John | Doe | Melbourne |
| 12347 | Betty | Jones | Melton |
+---------+-----------+----------+-----------+
The%(percent sign) is used to represent the rest of the string. In Perl this equates to.*in regular
expressions although%will match any possible character including newlines.
Joins
When we introduced relational databases, we discussed how relational databases allow us to join
tables together to determine who had participated in which course. There are many types of joins but
we ll only cover inner joins in this section.
In our database, we have a Staff table and a Projects table. To join these tables to determine who is
working on which project we write the following:
SELECT FirstName, LastName, ProjectName, Allocation
FROM Staff, Projects
WHERE Staff.StaffID = Projects.StaffID;
This would give us the following results:
+-----------+----------+-------------+------------+
| FirstName | LastName | ProjectName | Allocation |
+-----------+----------+-------------+------------+
| Jack | Sprat | ABC | 50 |
| John | Doe | ABC | 45 |
| Betty | Jones | ABC | 100 |
| Peter | Pope | ABC | 70 |
| John | Doe | NMO | 60 |
| Jack | Sprat | XYZ | 50 |
| Sam | Smith | XYZ | 50 |
| Ann | Smith | XYZ | 100 |
| Peter | Pope | XYZ | 30 |
+-----------+----------+-------------+------------+
Note that we had to specify whichStaffIDwe were comparing with which. SQL requires that
whenever we do a join that results in duplicate column names, we must explicitly name them in our
statements. For example, had we wished to include theStaffIDin our selection we would have had
to write one of the two (equivalent) statements:
SELECT Staff.StaffID, FirstName, LastName, ProjectName, Allocation
FROM Staff, Projects
WHERE Staff.StaffID = Projects.StaffID;
SELECT Projects.StaffID, FirstName, LastName, ProjectName, Allocation
FROM Staff, Projects
WHERE Staff.StaffID = Projects.StaffID;
# Producing:
+---------+-----------+----------+-------------+------------+
| StaffID | FirstName | LastName | ProjectName | Allocation |
+---------+-----------+----------+-------------+------------+
| 12345 | Jack | Sprat | ABC | 50 |
| 12346 | John | Doe | ABC | 45 |
| 12347 | Betty | Jones | ABC | 100 |
Perl Training Australia (http://perltraining.com.au/) 103
Appendix B. Introduction to SQL
| 12351 | Peter | Pope | ABC | 70 |
| 12346 | John | Doe | NMO | 60 |
| 12345 | Jack | Sprat | XYZ | 50 |
| 12349 | Sam | Smith | XYZ | 50 |
| 12350 | Ann | Smith | XYZ | 100 |
| 12351 | Peter | Pope | XYZ | 30 |
+---------+-----------+----------+-------------+------------+
Writing the full name of the table for each non-unique column name can make our SQL quite long
and harder to read (especially when you wish to do a few comparisons), so many databases offer an
alias feature, whereby column names can be given shorter aliases. This shortcut allows you to neaten
your SQL and specify (for clarity) where you expect each column to come from even when they are
unique.
SELECT s.StaffID, FirstName, LastName, ProjectName
FROM Staff s, Projects p
WHERE s.StaffID = p.StaffID AND
Allocation = 100;
# This gives us:
+---------+-----------+----------+-------------+
| StaffID | FirstName | LastName | ProjectName |
+---------+-----------+----------+-------------+
| 12347 | Betty | Jones | ABC |
| 12350 | Ann | Smith | XYZ |
+---------+-----------+----------+-------------+
Some databases offer extended SELECT statements that allow joins to be automatically performed
on like-named fields across tables. These can simplify the writing of SELECT statements
considerably.
Primary and Foreign keys
Until now we ve assumed thatStaffIDs are unique per staff member and that theStaffIDs in our
other tables match theStaffIDs in theStafftable. This behaviour suggests a primary and foreign
key relationship. By this we mean that in theStafftable theStaffIDcolumn uniquely identifies
each record, so that even if we have two staff members with the same first and last names we know
that they are different people.StaffIDis the primary key of theStafftable.
As we reference the primary key of theStafftable in ourStaffPhonetables we can say that the
StaffPhoneStaffIDcolumn contains a foreign key from theStafftable. TheStaffIDcolumn does
not uniquely identify the records in theStaffPhonetable as it is possible for staff members to have
zero, one or more phone numbers listed here.
The primary key of theStaffPhonetable is created by referencing both theStaffIDand the
PhoneNumbercolumn. While eachPhoneNumberis currently distinct it may not be safe to assume that
to always be the case.
Specifying primary and foreign keys when constructing database tables allows the database to ensure
that no duplicate records are created and that referential integrity is maintained. Referential integrity
means that if we delete a staff member from the database we should also delete their entries in each
other table which uses theStaffIDas a foreign key. Likewise, we could not insert a record into
StaffPhoneunless the person already existed inStaff.
Some databases do this better than others, and some don t support a concept of foreign keys at all.
104 Perl Training Australia (http://perltraining.com.au/)
Appendix B. Introduction to SQL
Ordering data
Just like Perl hashes, databases guarantee no internal order. So while you may get data out in
approximately the same order that you inserted it, this may not always be the case. Very commonly,
we ll want to fetch our records in a particular order, not just the order the database would normally
return them in. As a result, SQL allows you to use aORDER BYinstruction to specify ordering. For
example consider the output of the following command:
[ Pobierz całość w formacie PDF ]