SQL Commands

Getting Records

If you want to retrieve some records in a table, you use the SELECT statement. To use this, all you need to know the name of the table (in this case, Users):

SELECT * FROM Users

The * tells the database program to return all the fields. Therefore, this statement returns all the records and all the fields from the Users table. If you don't want all the fields, you simply list the ones you do want:

SELECT UserID, UserName FROM Users

Don't forget, if your field or table names have spaces (which they shouldn't!), you will need to surround the field names/table name with quotes:

SELECT 'UserID', 'UserName' FROM 'Users'

Conditions

If you don't want to have all the fields returned, you need to add the WHERE clause, specifying a condition or filter. For example,

SELECT UserID, UserName FROM Users WHERE UserName = 'James'

would return all the records where the UserName field contained the value James. Note that if you are searching for a string that contains a ' you need to convert it to ''.

You can also use the < (less than), > (greater than), or <> (not) conditions too. For example,

SELECT UserID, UserName FROM Users WHERE UserID < 10

would return all the records which had a UserID of less than 10 To use more than one condition, you can use the AND/OR statements. For example,

SELECT UserID, UserName FROM Users WHERE UserID < 10 OR UserName = 'James'

would return all the records that had a UserID of less than 10, or had a UserName of James.

Sorting

You can also tell the database what order you want the records to be retrieved in by using the ORDER BY clause. To use this, you simply specify the field name to sort by, and whether you want the data sorted in Ascending (ASC) or Descending (DESC) order. For example,

SELECT * FROM Users ORDER BY UserName DESC

returns all the records from the Users table, sorted by UserName, in descending order. As the ASC option is the default option, you don't actually need to specify it if you want the records to be sorted in ascending order;

SELECT * FROM Users ORDER BY UserName

will do exactly the same as

SELECT * FROM Users ORDER BY UserName ASC

You can also instruct the database to sort using multiple fields. For example,

SELECT * FROM Users ORDER BY UserName, UserID DESC

will return all the records from the Users table, sorted by UserName in ascending order first, and then by UserID in descending order.

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An expert is a man who has made all the mistakes that can be made in a very narrow field” - Niels Bohr