Display Data
The SELECT query is one of the most powerful options available in MySQL. At its simplest, a SELECT query can specify some information to display literally in the client window:
SELECT -- SYNTAX
SELECT "Hi Mom!";
Using SELECT with some extra phrases, referring to table data, you can retrieve data into an application. This basic SELECT query format specifies the fields to display followed by the FROM keyword and the table name:
SELECT -- SYNTAX
SELECT columnName FROM tableName;
This example displays what's in the address column for all of the rows of the mail table:
Example of SELECT
SELECT address FROM mail_table;
You can display more than one column's information at a time. This example displays what's in the name and address columns for all of the rows of the mail table:
Example of SELECT from MULTIPLE COLUMNS
SELECT name, address FROM mail_table;
The columns for each row are displayed in the order you specified in the SELECT statement. You can also use a wildcard character (*) to select all of the table's columns:
SELECT from ALL COLUMNS -- SYNTAX
SELECT * FROM tableName;
This example displays all of the rows of the table. Each row includes the value of all columns. The columns are displayed in the order they were defined when the table was created.
Calculate Data
You can also use SELECT without the FROM keyword to test MySQL functions and expressions. When you do not specify a table to select data from, MySQL will evaluate the expression in the SELECT statement and display the result. For example, this query displays the sum of several numbers:
Example of CALCULATION with FUNCTION
SELECT 3 + 12 + 33;
When you include the FROM keyword to specify a table, you can also combine the table's fields into functions and expressions. For example, this SELECT query calculates twice the population of each country in the country table:
Example of SELECT with CALCULATION and TABLE DATA
SELECT 2 * population FROM country_table;
Functions, usually shown as a special keyword followed by parentheses, cause a math or text formatting change to be enacted on whatever is in the parentheses. This SELECT query displays the name and address of each row in the mail table, converting it to uppercase:
Example of SELECT with FUNCTION and TABLE DATA
SELECT UPPER(name), UPPER(address) From mail_table;
Where Clause
By default, when you use a SELECT query, all of the rows of the table are returned. You can add a WHERE clause to your SELECT queries to select one or more specific rows. You can select rows by performing comparisons with one or more of the tables' fields.
WHERE -- SYNTAX
SELECT columnName FROM tableName WHERE conditionStatement;
Example of WHERE
SELECT * from mail_table WHERE lastname = "Smith";
This example looks for a specific value within the name field of the mail table and displays only the rows that match. If the name field happens to be the primary key, only one row will be displayed, but otherwise the row of every "Smith" would be shown.
MySQL uses case-insensitive matching with text fields. Aside from differences in case, the = operator will only match if the name is exactly as specified. You can also use other operators, such as less than (<) and greater than (>). Numbers are compared numerically, and text values (strings) compared alphabetically. If the values you are comparing are different types, MySQL converts them to a compatible type if possible.
With text fields, you can use the LIKE keyword to find partial matches. LIKE allows you to use wildcard characters. The first wildcard, _, matches a single character. The following example finds "Smith", "Smitt", "Smits", or any other starting with "Smit" plus one more character:
Example of WHERE with UNDERSCORE WILDCARD
SELECT * FROM mail WHERE lastname LIKE "John Smit_";
The other wildcard, %, can represent any number of characters or no characters. You can use this at the beginning and ending of a word to find the word anywhere in the column. The example below will find the name "Smith", "Smithson", "Nesmith", "Hammersmithstein", or any other name containing "Smith":
Example of WHERE with PERCENT WILDCARD
SELECT * FROM mail WHERE lastname LIKE "%Smith%";
Other operators
= is equal to > is greater than < is less than >= is greater than or equal to <= is less than or equal to <> or != is not equal to IS NULL is the NULL value IS NOT NULL is not the NULL Value <=> is equal to, allows NULL Values LIKE Match text string with wildcards NOT LIKE only text strings that do not match
Multiple Where
Often, in a large database, specifying a single condition would still return a huge number of rows. You can combine several conditions in a WHERE clause to make your search more specific. The logical operators AND, OR, and NOT are used to combine conditions.
The OR operator allows you to make a search more general:
SELECT with WHERE...OR -- SYNTAX
SELECT columnName FROM tableName WHERE conditionStatement OR conditionStatement;
The following example searches records from the mail table and displays only the rows where the lastname field contains either the value Smith or the value West:
Example of SELECT with WHERE...OR
SELECT * FROM mail_table WHERE lastname = "Smith" OR lastname = "West";
The AND operator allows you to make a search more specific. The following example searches records from the mail table and only displays each row if it meets two conditions: the lastname field must contain a value that includes the letters "Smith" AND the state field must contain the value "CA":
Example of SELECT with WHERE...AND
SELECT * from mail WHERE lastname LIKE "%SMITH%" AND state ="CA";
If you have not used AND and OR with computer languages before, they may be confusing. Remember that using OR will allow more rows to match the query, and using AND will allow less rows to match.
Finally the NOT operator inverts a condition. If you use NOT LIKE or != (not equal), rows that do not match the condition are returned. You can use NOT to make any existing condition into its opposite. The following command means you want to display only the records where the state field has states other than CA:
Example of SELECT with WHERE...!=
SELECT * from mail WHERE state != "CA";
You can combine any number of conditions with AND, OR, and NOT to create complex conditions. When you use AND and OR together, often the meaning is ambiguous. You can enclose conditions in parentheses to ensure that they are considered first, before combining the result with the other conditions.
Limit
In a large database, a select query can return a large number of rows. You can add the LIMIT clause to request that only a specified number of rows should be returned. In a basic LIMIT clause, you simply specify the number of rows. The following example displays the first ten rows of the mail table:
Example of LIMIT
SELECT * FROM mail_table LIMIT 10;
If the table contains less than ten rows, all of the rows will be returned.
You can optionally specify an offset number first, followed by a comma and then the limit number. Think of the offset number as how many matching rows the query will skip at first. If you do not specify an offset, zero is used by default. The following example skips the first four rows that match, then displays the next ten rows that match:
Example of LIMIT with OFFSET
SELECT * FROM mail WHERE state="CA" LIMIT 4, 10;
Why would you use an offset? Consider how the following queries will generate three displays-- three groups of ten rows in the table:
SELECT * FROM mail LIMIT 0, 10; SELECT * FROM mail LIMIT 10, 10; SELECT * FROM mail LIMIT 20, 10;
You can use LIMIT offsets like this to display data as 10 rows per webpage, with links to "next 10 records" and "previous 10 records". LIMIT in general is also useful if you simply want to control the numbers of rows that will be displayed. Exactly which rows are displayed depends on the order of the primary key, or the order you specify in an ORDER BY clause, described in the next section.
You can combine LIMIT with a WHERE clause to display a subset of the number of rows that match the condition of the WHERE clause. LIMIT can also be combined with the other SELECT options described in this chapter.
Order By
One of the most powerful features of a database management system like MySQL is the ability to sort large amounts of data by different fields. You can use the ORDER BY clause in a SELECT statement to control the order of the rows returned:
ORDER BY SYNTAX
SELECT columnName FROM tableName ORDER BY columnName;
For example, suppose you defined a table to store famous quotations. This table would include text fields for the quotation (a column named quotes), and the author (a column named author), and an auto-increment column (called num) to act as a primary key. You could use a simple ORDER BY clause to list the quotations sorted by the author's name:
Example of ORDER BY
SELECT * FROM quote_table ORDER BY author;
You can optionally follow an ORDER BY column with the keyword DESC for descending order (Z-to-A if alphabetical, high-to-low if numeric), or ASC, the default for ascending.
What if there are several quotes from one author, and you don't want to display them in the normal order? If you specify more than one column name in the ORDER BY clause, the table will first be sorted by the first column, and then the second column, and so on. The following example would return a list of quotations sorted alphanumerically by author and then by the quotes field:
Example of ORDER BY MULTIPLE COLUMNS
SELECT * FROM quote_table ORDER BY author, quotes;
The second column to sort by will only be effective if there are multiple rows for some values of the author column. If all of the values in this column are unique, the other columns in ORDER BY have no effect.
If you specify one or more columns with SELECT, you can use numbers as shorthand in ORDER BY to refer to the columns being selected. This example orders by the second column (in this case, the author column):
Example of ORDER BY with COLUMN NUMBER
SELECT quote, author FROM quote_table ORDER BY 2;
Group By
Sometimes you want to look at groups of rows displayed as a single row of data. Or, rather than display an entire column of values, many of which repeat, you might want to query "Show me all the values in this column, but show each value only once, ignoring all the duplicates." You can use the GROUP BY clause in a SELECT query to combine all of the rows for a particular value into a single row:
GROUP BY SYNTAX
SELECT columnName FROM tableName GROUP BY columnName;
For example, this query displays a list of author names from the quotes table, but will only show each author name once:
Example of GROUP BY SYNTAX
SELECT author FROM quote_table GROUP BY author;
The GROUP BY clause is similar to ORDER BY, but it only returns a single row for each unique value of the grouping field. In this case, because you are displaying only the author field, it will display a list of authors. Even if the same author name is included in several rows of the database, it will be listed only once in the query with GROUP BY.
If you SELECT several fields (columns) that are not mentioned in the GROUP clause, it still groups according to the GROUP clause, it simply displays more fields per row. The following query will display a single quotation for each author, regardless of how many times the author appears in the database:
SELECT quote, author FROM quote_table GROUP BY author;
You can optionally use an ORDER BY clause after the GROUP BY clause. This will control the order in which the rows are pulled from the database before they are grouped together as specified in the GROUP BY clause.
While GROUP BY in its simple form is useful for listing the unique values of a column in a MySQL table, you can actually gather statistics on each group using several MySQL functions, like COUNT() with a column name in parentheses.
Joins
MySQL lets you use DOT SYNTAX to concisely mention a column in a table. With dot syntax, what's on the right side of the dot is part of (belongs to) what's on the left. In this example, we refer to the "player" column in a table named "steeler_table":
Specifying a column in BASIC DOT SYNTAX
steeler_table.player
Many times you will need to display data from more than one table at a time. In effect, your display will look like a new table pieced together with information from several tables. Using the dot syntax below you can display that information:
SELECT FROM MULTIPLE TABLES-- SYNTAX
SELECT table1Name.column1Name, table2Name.column2Name FROM table1Name, table2Name;
The following query displays the entire "player" column from a table named "steeler_table", beside the entire "player" column from a table named "eagles_table".
Example of SELECT FROM MULTIPLE TABLES
SELECT steeler_table.player, eagles_table.player FROM steeler_table, eagles_table;
In previous queries, we have only identified the column name that we were trying to select. Since we are using multiple tables, and since both tables have a column with the name "player", we must identify what table that column is in. In dot syntax, we do this by listing the tablename, then a dot, then the column name.
The statement above would show rows that are every possible combination of one Steeler player with one Eagle player. It would be more useful if we could cross reference tables, in effect specifying which column is a foreign key for the other table. For this, we use a JOIN.
SYNTAX of INNER JOIN
SELECT table1Name.column1Name, table2Name.column2Name
FROM table1Name INNER JOIN table2Name
ON table1Name.column3Name = table2Name.column4Name;
For example, let's say we have a Steelers table about the players, and it assigns each player a player_ID number. And we have a Touchdown table with one row per touchdown and it mentions the date and the player_ID number of the player who made the touchdown but it doesn#39;t mention the player's name. We want to display the name of each player that scored, and the date of their touchdowns. We could use an INNER JOIN, mentioning with an "ON" clause that the player_id from the first table must match the player_id from the second table:
Example of INNER JOIN
SELECT steeler_table.player, touchdown_table.date_of_td
FROM steeler_table INNER JOIN touchdown_table
ON steeler_table.player_id = touchdown_table.player_id ;
The statement above would NOT show those rows that do not cross-reference the other table. For example, it would NOT show players who never scored a touchdown. But sometimes you DO want to show all rows in one of the two tables, regardless of whether they cross-reference or not. For this, we use a LEFT or RIGHT JOIN. In general, it is the same as an INNER JOIN. The important difference is that all rows of the first table (the one before the keyword LEFT JOIN, called the 'left table') will be displayed, and only those rows in the second table (the one after the keyword LEFT JOIN, called the 'right table') that match will be displayed.
SYNTAX of LEFT JOIN
SELECT table1Name.column1Name, table2Name.column2Name
FROM table1Name LEFT JOIN table2Name
ON table1Name.column3Name = table2Name.column4Name;
For example, let's say we want to display each player's name (whether they scored or not), and the date of their touchdowns. We could use a LEFT JOIN:
Example of LEFT JOIN
SELECT steeler_table.player, touchdown_table.date_of_td
FROM steeler_table LEFT JOIN touchdown_table
ON steeler_table.player_id = touchdown_table.player_id ;
Of course, we can add in WHERE clauses (if we only want to see touchdowns for one player, perhaps), GROUP BY, ORDER BY, etc.