Information: Adding | Updating | Retrieving | Removing | PHP and MySQL
Retrieve Information - Page 82-91

082 The simplest way:
   SELECT * FROM tablename

082 Retrieving specific information
   SELECT columnname, columnname, columnname, ... FROM tablename
   example:  SELECT lastName, firstName FROM Member

083 Performing mathematical operations on columns selected
   SELECT col1 + col2 FROM tablename
   example:  SELECT price, price*1.08 FROM Member

083 changing the name of a column
   note: The AS clause tells MySQL to give the name priceWithTax to the second column retrieved.
   example:  SELECT price, price*1.08 AS priceWthTax FROM Member

Information That Can Be Selected (Page 83)
SQL Format Description of Information
AVG (columnname) Returns the average of all values in columnname
COUNT (columnname) Returns the number of rows in which columnname is not blank
MAX (columnname) Returns the largest value in columnname
MIN (columnname) Returns the smallest value in columnname
SUM (columnname) Returns the sum of all values in columnname
example:  SELECT MAX(price) from Pet



Retrieving data in a specific order (Page 84)
ORDER BY to sort information, use the phrase
ORDER BY columnname the data is sorted by columnname in ascending order
example: Database Selected: petcatalog
              Query: select * from pet order by petType
You can sort in descending order by adding the word DESC before the columnname
example:  SELECT * from Member ORDER BY DESC lastName
 
GROUP BY to group information, use the following phrase
GROUP BY columnname the rows that have thje same value of columnname are grouped together
example:  SELECT * from Pet GROUP BY petType
 
You can use GROUP BY and ORDER BY in the same query.



Retrieving data from a specific source (Pages 84-85)
SQL Format Description of Information
WHERE allows you to request information with certain characteristics
examples: names of members from the state of California
                names of pets that are cats
LIMIT allows you to limit the number of rows from which data is retrieved
example: you can request data from the first three rows of a table.
DISTINCT allows you to request information from only one row of identical rows
example: from the Login table, request loginNames but no duplicate
              names, limiting the response to one record per member.
 
the WHERE clause of the SELECT query allows you to make complicated selections
example: members with last names that start with the letter B and who live in the state of Texas
format:   WHERE expression AND|OR expression AND|OR expression ...
expression specifies a value to compare with the values stored in the database.
    only rows containing a match for the expression are displayed.
    using AND means rows will only be displayed if both expressions are true.
    using OR means rows will be displayed if one of the expressions are true.
 
LIMIT specifies how many rows to return
format: LIMIT startnumber, numberofrows
           startnumber: the first row you want to retrieve
           numberofrows: the number of rows you want to retrieve
example: SELECT * FROM member WHERE state="TX" LIMIT 3
        to prevent the query from returning identical records, add the word DISTINCT immediately after SELECT



Expressions for the WHERE clause (Pages 85-86)
Expression Example Result
column = value zip = "12345" only rows with zip as 12345
column > value zip > "50000" when zip is 50001 or higher
column >= value zip >= "50000" when zip is 50000 or higher
column < value zip < "50000" when zip is 49999 or lower
column <= value zip <= "50000" when zip is 50000 or lower
column BETWEEN value1 AND value2 zip BETWEEN "20000" AND "30000" only rows with zip as greater than 19999 but less than 30001
column IN (value1, value2, ...) zip IN ("90210", "07044") only rows with zip as 90210 or 07044
column NOT IN (value1, value2, ...) zip NOT IN ("90201", "07044") all zip codes except 90210 or 07044
column LIKE value zip LIKE "9%" all zip codes that begins with a 9
column NOT LIKE value zip NOT LIKE "9%" all zip codes that do not begin with a 9
note:         you can combine any expression with ANDs or ORs
example:   SELECT lastName, firstName FROM member
                         WHERE lastName LIKE "B%"
                               AND city = "Dallas"
                               AND (phone LIKE "%8%" OR fax LIKE "%8%")

Combining information from tables (Page 87-89)
SQL   DESCRIPTION
UNION rows retrieved from one or more tables are stored together, if you selected 3 rows from one table and 2 rows from another table, you will display 5 rows
JOIN tables are combined side by side, adding more columns if needed, not extra rows, unless they are needed.
 
format for UNION: SELECT query UNION ALL SELECT query ...
 
format for inner JOIN: SELECT columnnamelist FROM table1, table2
      WHERE table1.col2 = table2.col2
 
format for outer JOIN: SELECT columnnamelist FROM table1 LEFT JOIN table2
      ON table1.col1 = table2.col2
  SELECT columnnamelist FROM table1 RIGHT JOIN table2
      ON table1.col1 = table2.col2

UNION Examples (Page 88)
   
UNION SELECT lastName, firstName FROM Member UNION ALL
      SELECT lastName, firstName FROM OldMember
rules: all selected queries must select the same number of columns
         the columns selected in queries must contain the same type of data
the result set will contain rows from the first query, than rows from the second query
if you do not want duplicates, do not include the word ALL
 
If you want to use ORDER BY for the entire UNIONed table, look at the next example
example: (SELECT lastName FROM Member UNION ALL
      SELECT lastName FROM OldMember) ORDER BY lastName

JOIN Examples (Page 89)
SQL DESCRIPTION
JOIN combining tables side by side is a JOIN. Tables are combined by matching data in a column, the columns that they have in common.
  If one table has memberID and height, and the other table has memberID and weight,
the JOIN results in a table with four columns, memberID (from the first table), height
memberID (from the second table), weight.
  The two types of JOINs are an inner join and an outer join.
The difference between the inner join and an outer join is the number of rows displayed.

The results of a table produced by an inner join contains only the rows that existed in both tables.
The results of a table produced by an outer join contains all rows that existed in one table with blanks in columns for the rows that did not exists from the other table.

example: if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain one row, the row for Sally.
an outer join would contain two rows, a row for Joe and a row for Sally,
even though the row for Jow would have a blank field for weight.
  The results table for the outer join contains all the rows for one table
If any rows for that table do not exist in the second table,
the columns for the second table are empty.

The contents of the result table are determined by which table contributes all its rows,
requiring the second table to match it.

Two kinds of outer joins control which table sets the rows and which to match,
a LEFT JOIN or a RIGHT JOIN.

More examples (Page 89)
SQL DESCRIPTION
format for an inner join SELECT columnnamelist FROM table1, table2
      WHERE table1.col2 = table2.col2
format for the outer joins SELECT columnnamelist FROM table1 LEFT JOIN table2
      ON table1.col1 = table2.col2

SELECT columnnamelist FROM table1 RIGHT JOIN table2
      ON table1.col1 = table2.col2




Consider the following example of two tables within the petCatalog database. (Page 90-91)
The first table is Pet.

Database: PetCatalog, Table: Pet,
Columns: petName and petType
petName petType
Unicorn Horse
Pegasus Horse
Lion Cat
    The second table is Color or petColor.

Database: PetCatalog, Table: PetColor,
Columns: petName and petColor
petName petColor
Unicorn white
Unicorn silver
Fish gold

You want the following information in the result table, petName, petType, and petColor.
This requires an inner join with the following query:
SELECT * FROM Pet, PetColor WHERE pet.petName = petColor.petName
    or
SELECT pet.petName, pet.petType, petColor.petName, petColor.petColor FROM Pet, PetColor
       WHERE pet.petName = petColor.petName

You get the following result table with 4 columns, petName (from Pet), petType, petName (from PetColor), and petColor.

petName petType petName petColor
Unicorn Horse Unicorn white
Unicorn Horse Unicorn silver

Here is the link to localhost/mysql_send.php to try the query above.      Database: petCatagory

Notice that only Unicorn appears in the results table, because Unicorn was in both tables.


Suppose you do a left outer join with the following query:
SELECT * FROM Pet LEFT JOIN PetColor ON pet.petName = petColor.petName
   or
SELECT pet.petName, pet.petType, petColor.petName, petColor.petColor FROM Pet LEFT JOIN PetColor ON pet.petName = petColor.petName

This table has four rows (or more rows if you have more data in the real tables)
It has the same first two rows as the inner join, but it has two additional rows, rows that are in the petType table on the left but not in the petColor table. Notice the columns from table petColor are blank for the last two rows.

Here is the link to localhost/mysql_send.php to try the query above.      Database: petCatagory

petName petType petName petColor
Unicorn Horse Unicorn white
Unicorn Horse Unicorn silver
Pegasus Horse <Null> <Null>
Lion Cat <Null> <Null>




For a third example, suppose you do a right outer join with the following query:
SELECT * FROM Pet RIGHT JOIN PetColor ON pet.petName = petColor.petName
   or
SELECT pet.petName, pet.petType, petColor.petName, petColor.petColor FROM Pet RIGHT JOIN PetColor ON pet.petName = petColor.petName

Here is the link to localhost/mysql_send.php to try the query above.      Database: petCatagory

You get the following results table, with the same four columns, but with different rows.

petName petType petName petColor
Unicorn Horse Unicorn white
Unicorn Horse Unicorn silver
<Null> <Null> Fish gold

Notice that these results contain all the rows for the petColor table on the right, which does not have a row for Fish.


To find rows in a table that has no match in another table.
There are two tables, Members and Login. You want to find member names that have never looged into the site.
Use the following Query/SQL statement:

SELECT loginName FROM Member LEFT JOIN Login
       ON Member.loginName=Login.loginName
       WHERE Login.loginName IS NULL


This query will give you a list of all login names from the Member table that are not found in the Login table.
This site is for the private use of my students. (2008)

Information: Adding | Updating | Retrieving | Removing | PHP and MySQL