Basic Queries

Objectives

Practice

More Info.

• Presentaiton
• Lecture Code
• Sitepoint XML Tutorial
• W3 Schools XML Tutorial
• XML Validator

This lesson will teach you to...

• Represent data using XML
• format XML appropriately according to valid structure rules
• Display XML data using CSS

Retrieving Data from a Table

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]