Transcript ECA 236 - Just Us Two Photography

ECA 236
Open Source Server Side Scripting
MySQL – Selecting Data
Open Source Server Side Scripting
sitename
the
users table contains the following columns
users
user_id
MEDIUMINT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY
first_name
VARCHAR(15) NOT NULL
last_name
VARCHAR(30) NOT NULL
email
VARCHAR(40)
password
CHAR(16) NOT NULL
registration_date
DATETIME NOT NULL
ECA 236
Open Source Server Side Scripting
2
selecting records
SELECT
the
most frequently used of all SQL statements
SELECT returns rows of information based upon a
set of selection criteria
 the
simplest SELECT statement is
SELECT * FROM users;
which returns all the records in the table
 the * means ALL
ECA 236
Open Source Server Side Scripting
3
selecting records
cont …
specify which
columns to return, each column
separated by a comma
SELECT user_id, first_name, last_name FROM users;
advantages of specifying columns
performance
order
functions
ECA 236
Open Source Server Side Scripting
4
conditionals
WHERE
used
with a variety of operators to retrieve specific
records
 equality
operator
SELECT email FROM users WHERE last_name = ‘Lennon’;
 AND
operator
SELECT email FROM users WHERE last_name = ‘Lennon’ AND
first_name = ‘John’;
ECA 236
Open Source Server Side Scripting
5
common operators
Operator
Explanation
=
equals
<
less than
>
greater than
<=
less than or equal to
>=
greater than or equal to
!=
not equal to
IS NOT NULL
has a value
IS NULL
BETWEEN
NOT BETWEEN
IN
NOT IN
OR ( also || )
AND ( also && )
NOT ( also ! )
ECA 236
does not have a value
within a range
outside a range
is included in set of listed values
is not included in set of listed values
where one of two conditionals is TRUE
where both conditionals are TRUE
where the condition is not TRUE
Open Source Server Side Scripting
6
WHERE examples
 Select
the records for every user who was
registered during a range of dates
SELECT * FROM users WHERE ( registration_date > ‘2003-10-31
00:00:00’ ) AND ( registration_date < ‘2003-11-09 00:00:00’ );
 To
SELECT a particular day, set the dates
greater than midnight on one day, and less
than midnight on the following day
SELECT * FROM users WHERE ( registration_date > ‘2003-11-08
00:00:00 ‘) AND ( registration_date < ‘2003-11-09 00:00:00’ );
ECA 236
Open Source Server Side Scripting
7
WHERE examples
cont …
 Since
registration_date is a DATETIME data
type, we use the time. If it was simply a DATE
data type, we could test to see if the value is
equal to a specific day
SELECT * FROM users WHERE ( registration_date = ‘2003-11-08’ );
ECA 236
Open Source Server Side Scripting
8
WHERE examples
cont …
 Select
the first name of all users whose last
name is Starr
SELECT first_name FROM users WHERE last_name = ‘Starr’;
 Select
everything from every record in users
that does not have an email address
SELECT * FROM users WHERE email IS NULL;
 NULL
means there is no value. If a record has
an empty string, it is not NULL
SELECT * FROM users WHERE email = ‘ ’;
ECA 236
Open Source Server Side Scripting
9
WHERE examples
cont …
 Select
the record where the password is
‘legsDiamond’ - remember, passwords are
case sensitive
SELECT * FROM users WHERE password = PASSWORD( ‘legsDiamond’ );
 Select
all the records from users where
first_name is in a list of provided values
SELECT * FROM users WHERE first_name IN ( ‘Leland’,
‘Laura’, ‘Josie’, ‘Bob’ );
ECA 236
Open Source Server Side Scripting
10
strings
using numbers, dates, and
NULL in a conditional
is fairly straightforward
to check for string equality use the = operator
to check for a close, but not equal match, other
operators and symbols are available
EG, to select all last names beginning with
“Smith” we need a more flexible operator
ECA 236
Open Source Server Side Scripting
11
LIKE
NOT LIKE
used primarily
with strings
use two wildcard characters

underscore _
 matches
any single character
 can be used in combination with itself
> LIKE ‘_ _ ‘

will match any two letter combination
percent sign
 matches
zero or more characters
case insensitive
ECA 236
Open Source Server Side Scripting
12
LIKE
 match
NOT LIKE
cont …
any user whose last name begins with
‘Smith’
SELECT * FROM users WHERE last_name LIKE ‘Smith%’;
 will
match ‘Smith’, ‘Smithberger’, ‘Smithsonian’,
‘smithy’, but not ‘Klingensmith’
 on
the other hand
SELECT * FROM users WHERE last_name LIKE ‘%Smith’;
 will
match ‘Klingensmith’
ECA 236
Open Source Server Side Scripting
13
LIKE
NOT LIKE
cont …
 Select
the first and last name of every user
whose email address is not from aol.com
SELECT first_name, last_name FROM users WHERE email
NOT LIKE ‘%@aol.com’;
 To
use a literal underscore or percent sign in a
query, escape them.
SELECT first_name FROM users WHERE email LIKE ‘sam\_%’;
ECA 236
Open Source Server Side Scripting
14
ORDER BY
used to
sort query results
if you do not specify ORDER BY, query will most
likely be returned by the primary key in ascending
order
you can use ORDER BY to sort by any column
SELECT last_name FROM users ORDER BY registration_date;
ECA 236
Open Source Server Side Scripting
15
ORDER BY
cont …
default order
when using ORDER BY is
ascending ( designated as ASC )
number
increase from small to large
dates go from older to most recent
string go from A to Z
to
reverse the order specify DESC
SELECT first_name, last_name FROM users ORDER BY
last_name DESC;
ECA 236
Open Source Server Side Scripting
16
ORDER BY
you
cont …
can ORDER BY multiple columns
return
the first and last names ordered by
registration date, then last name
SELECT first_name, last_name FROM users ORDER BY
registration_date, last_name;
if
a sorted column contains NULL values,
these will appear before columns containing
values
ECA 236
Open Source Server Side Scripting
17
ORDER BY
cont …
you
can use ORDER BY along with
WHERE to retrieve and order specific
records
Return
the entire record where any
registration date is after November 10, but
ORDER BY the user’s last name
SELECT * FROM users WHERE registration_date >
‘2003-11-01 00:00:00’ ORDER BY last_name;
ECA 236
Open Source Server Side Scripting
18
LIMIT
limits
the number of records returned
not part of SQL standard
not
available on all databases
LIMIT
the number of records returned to 3
SELECT * FROM users ORDER BY last_name LIMIT 3;
return 5
records beginning with the 11th
begins
at index 0
SELECT * FROM users ORDER BY last_name LIMIT 10, 5;
ECA 236
Open Source Server Side Scripting
19
LIMIT
cont …
Select
the third person who was entered
into the table
SELECT first_name, last_name FROM users ORDER BY
registration_date LIMIT 2, 1;
the
LIMIT n1, n2 clause is great for
returning multiple pages of query results
where you show the first 10 results, then
the next ten, etc.
ECA 236
Open Source Server Side Scripting
20
DELETE
DELETE eliminates a
record – it does not delete
a table or database
once you use DELETE to delete a record, there is
no way to retrieve
back
use
up your database before running DELETE
a WHERE statement with DELETE
otherwise
ECA 236
you will delete all data in the table
Open Source Server Side Scripting
21
DELETE
cont …
DELETE FROM users;
will
delete everything in the table while retaining its
structure
DELETE FROM users WHERE user_id = 33;
will
delete only the specified record
to avoid major errors, log into the mysql monitor with
the – – i–am–a–dummy parameter
 MySQL will
then not allow you to run UPDATE or DELETE
without a WHERE clause
ECA 236
Open Source Server Side Scripting
22
DROP
to
drop a table
DROP TABLE table_name;
to
drop a database
DROP DATABASE database_name;
ECA 236
Open Source Server Side Scripting
23