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.