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
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 |
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. |
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 |
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%") |
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 | 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 |
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. |
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 |
The first table is Pet.
|
The second table is Color or petColor.
|
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.
Information: Adding | Updating | Retrieving | Removing | PHP and MySQL