Now that we know how to put data into the database, we need to learn how to get it out. When trying to access the data in a table, we use the MySQL SELECT statement. SELECT statments can be very simple or very complex. Below are the basics of the SELECT statement.
SELECT * FROM tblName; //selects all rows from the table
SELECT fieldName1 FROM tblName; //selects only 1 column of information from a table
SELECT fieldName1, fieldName2 FROM tblName; //selects 2 distinct columns of info from a table
The SELECT command can be modified using the following commands:
WHERE - allows field values to be compared to data
SELECT * from tblName WHERE gender="M"; //selects all rows from the table where the gender column value is "M"
AND, OR, NOT - used to create complex WHERE conditions
SELECT * from tblName WHERE gender="M" OR age<=50; //selects all rows from the table where the gender is male and the age is less than or equal to 50
SELECT * from tblName WHERE gender="M" OR age<50; //selects all rows that are male in addition to all rows that have an age under 50
SELECT * from tblName WHERE NOT gender="M"; //selects all rows that are not male
ORDER BY - choose the field to set the order
SELECT * from tblName ORDER BY lastName; //returns all rows from the table in alphabetical order by the values stored in the lastName column
ASC, DESC - orders ascending or descending based on the ORDER BY command
SELECT * from tblName ORDER BY lastName DESC; //returns all rows from the table in reverse alphabetical order by the values stored in the lastName column
SELECT * from tblName ORDER BY lastName ASC, firstName DESC; //returns all rows, first ordered by lastName, and ordered 2nd by firstNames in reverse
LIMIT - limit the number of results that can be returned
SELECT * from tblName LIMIT 3; //returns the first three rows from the full table
DISTINCT - identifies distinct values within a column.
SELECT DISTINCT zipCode from tblName; //if the zipCode values are 19444, 19555, 19444, 19446, this query will only return 19444, 19555, 19446.
COUNT, SUM, AVG - performs numerical operations for a given column
SELECT COUNT(lastName) from tblName; //returns the number of entries in the table
SELECT AVG(grade) from tblName; //returns the average of all values in the grade column
SELECT SUM(rsvp) from tblName; //returns the sum of all values in the rsvp column
All of these query modifiers can be used in the same statement, but must be used in the correct order:
SELECT [COUNT|SUM|AVG][DISTINCT] colList from tblName WHERE [NOT] clause [AND|OR secondClause] ORDER BY colName [ASC|DESC] [LIMIT value]