A working example

You should now know a few basics of SQL. Let's cement that by running over a possible SQL situation, and looking at how we can, by using some neat little keywords, make our SQL come to life.

Tetra Tech Inc. need a database system to allow them to track users to a secure section of their site. A username is needed, as is a password. Using the simple SQL you have picked up so far, you should be able to see their system reflected in the following code:

CREATE TABLE securelogons (Username CHAR(255), Password CHAR(10));
INSERT INTO securelogons VALUES ('TelRev', 'fifi');
INSERT INTO securelogons VALUES ('Petra', 'mojojojo');
INSERT INTO securelogons VALUES ('Alex', 'blackbyrd');

Those four lines create a very simple table to track usernames and passwords, and insert three initial records. Notice how the password field is only CHAR(10) - any passwords longer than ten characters long are cut down to ten characters.

9.3.12.1 Defining a key

With the data in the table we can manipulate it was we wish - selecting, updating, and deleting is now possible. However, here's a question for you - what happens if someone chooses the same username again? How would you distinguish between Person A and Person B?

If you remember the first table we created, usertable, the best way to do this is to have an internal field which does not really mean anything important, but it uniquely identifies each record in your database. Here is an example:

CREATE TABLE securelogons (ID INT AUTO_INCREMENT Primary Key, Username CHAR(255), Password CHAR(10));

The "ID INT" part you have seen before - it declares the first field as being named "ID" and being of type Integer (number). The AUTO_INCREMENT PRIMARY KEY part, however, is new - it instructs MySQL to insert a unique ID number for you. As you can somewhat guess by the code itself, it AUTOmatically_INCREMENTs the ID field with each new record - the first record you enter would have ID 1, the next 2, the next 3, etc.

Doing this ensures there is no chance of records which are absolute duplicates, because there will always be the unique ID field for each record automatically created for you by MySQL. The PRIMARY KEY part instructs MySQL to ruthlessly enforce ID as a unique number.

Once you have an auto-increment field, you need to modify your INSERT statements to instruct MySQL which fields you are providing data for. As seen previously, inserting data is as simple as calling INSERT INTO followed by a value for each field in the table. Now that MySQL will be filling in ID for us, we will no longer provide a value, and instead we need to tell MySQL what we are providing. This is done like so:

INSERT INTO securelogons (Username, Password) VALUES ('TelRev', 'fifi')

Here we list our two field names before the VALUES section of our INSERT statement. This tells MySQL that we are inserting data into the Username and Password fields, thereby implicitly also saying that we're not providing any data for the auto-incrementing ID field so MySQL should provide data for that itself.

It is possible to provide a value for an auto-increment column that will override what MySQL would otherwise have chosen, however it must not be a value that is been used before.

9.3.12.2 Ordering data

Through another special SELECT clause, ORDER BY, MySQL makes it easy to order your data in whichever way you want it. Take a look at these command combinations:

SELECT Username FROM securelogons ORDER BY Username;
SELECT Username FROM securelogons ORDER BY Username DESC LIMIT 3;
SELECT Username, Password FROM securelogons ORDER By Username ASC, Password DESC;
SELECT ID, Username, Password FROM securelogons WHERE Username = 'Alex' ORDER BY Password ASC;

Those four statements all use the new ORDER BY clause to force MySQL to sort its results in a certain order. The first line sorts by Username, alphabetically ascending, whereas the second line sorts by Username descending (DESC). Note that if you do not specify ASC for ascending or DESC for descending, MySQL assumes you want your data ascending - that is, A comes before Z, and 0 before 9.

In line three there is an example of a double sort - MySQL will sort by Username ascending then Password descending. In this situation, rows will only be sorted by Password if their Username matches. If we had three Johns and two Sallys, here's how that output would like:

As you can see, the list is first sorted by Username, then any duplicates have their Password sorted descending also. Note that there is a special ordering, RAND(), which returns your rows in a random order. You can use ORDER BY RAND() LIMIT 1 to pick out one random row from your database. Thus the following query will put out one random username from the table: SELECT Username FROM usertable ORDER BY RAND() LIMIT 1;.

It is important to note the order in which SELECT clauses come. WHERE must come first, followed by ORDER BY, then finally LIMIT. If you attempt any other placing, MySQL will complain, so you will need to try to remember the order.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.