SELECT in MySQL - choosing the rows you want

The SELECT command in MySQL allows you to examine rows in a table (or more than one table if you like). Good news - but sometimes there's an awful lot of data and you just want rows chosen by particular criteria, or just the first (so many) rows.

The WHERE clause on your select allows you to write a number of conditions which can be linked with AND and OR keywords, and there's a great deal of choice in the conditions. Here are some examples:

WHERE name = "Graham"
Will look for the string Graham (exactly) and nothing else in the field called name. Note that this selection is case sensitive if name is specified as being a binary field, and case insensitive otherwise.

WHERE name IN ("Graham","Lisa","Leah")
Will look for any one of the strings Graham Lisa or Leah (exactly in each case; same rule about case as in the example above).

WHERE name LIKE "%Graham%"
Will return any row that contains the word Graham within the name field. This is similar to file name matching (also known as globbing) except that the % metacharacter means "anything" and the _ character (that's an underscore) means any one character ... in place of * and ? in file name matching.

WHERE name RLIKE "^[[:graph:]]+$"
Will return any row with a single name in the name field - this is a regular expression which provides a huge flexibility in matching. Regular Expressions are notable in that you can ask a lot in a very obtuse string of just a few characters!
(written 2005-11-22, updated 2006-06-05)

Associated topics are indexed under

S152 - SQL Primer as Used in MySQL
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [270] NULL in MySQL - (2005-04-06)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

S157 - More MySQL commands
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)

Back to
.css - using PHP to make dynamic style sheets

Previous and next
or
Horse's mouth home

Forward to
10 years C# knowledge please

Some other Articles

What are DHCP and DNS?
Would you steal ... petrol? ... a training course?
We are about Open Source programming courses in the UK
10 years C# knowledge please
SELECT in MySQL - choosing the rows you want
.css - using PHP to make dynamic style sheets
Bowerhill, Melksham, 2006 Calendar
Good IT training cannot be cheap
Why is Tomcat called Tomcat?
I have a river to cross

3597 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Public Training Courses

Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??