Bits And Bytes
In the Internet age, information is no longer represented in filing cabinets; instead, it’s stored as digital ones and zeroes in databases, data storage “containers” that impose a certain structure on information, so as to simplify the task of managing and using it.
Of course, putting data into a database is only half the battle - the other half involves using it effectively. Which brings us to the point of this article - using SQL, or Structured Query Language, to communicate with a database in order to extract the data you require from it.
Over the next few pages, I’ll be explaining some of the basic SQL commands to create and enter information into a database, together with examples that should make things clearer. In case you’ve never used a database before, or the thought of learning another language scares you, don’t worry - SQL is considerably simpler than most programming languages, and you should have no trouble picking it up.
Onwards!
The Tools
Before we can get started, you need to get a database up and running - your SQL commands will be interpreted by this, and appropriate results generated.
Now, there are a number of database engines out there; you’ve probably heard of Oracle (for large, data-intensive Web applications) and Microsoft Access (for desktops and servers). Unfortunately, though both these come with powerful capabilities, neither of them is free - a fact which displeases many of those used to open-source software.
However, there is a database engine out there that is both free and effective, and will serve our purpose for this tutorial - it’s called mySQL and you can download it from http://www.mysql.com/ (it’s available for both UNIX and Windows platforms). Once you’ve downloaded and installed it (or had your network administrator do it for you), you’re ready to begin your first foray into the world of SQL.
Turntables And Records
Every database is composed of one or more “tables” - these tables, which structure data into rows and columns, are what lend organization to the data.
Here’s an example of what a typical table looks like:
+-----------+-------+---------+---------+--------------------------+
| member_id | fname | lname | tel | email |
+-----------+-------+---------+---------+--------------------------+
| 1 | John | Doe | 1234567 | [email protected] |
| 2 | Jane | Doe | 8373728 | [email protected] |
| 3 | Steve | Klingon | 7449373 | [email protected] |
| 4 | Santa | Claus | 9999999 | [email protected] |
+-----------+-------+---------+---------+--------------------------+
As you can see, a table divides data into rows, with a new entry (or “record”) on every row. If you think of a table as a filing cabinet, you’ll see that every file in the cabinet corresponds to one row in the table.
The data in each row is further broken down into cells (or “fields”), each of which contains a value for a particular attribute of the data. For example, if you consider the record for the user “Steve Klingon”, you’ll see that the record is clearly divided into separate fields for phone number, email address, and first and last names.
The rows within a table are not arranged in any particular order - they can be sorted alphabetically, by id, by member name, or by any other criteria you choose to specify. Therefore, it becomes necessary to have some method of identifying a specific record in a table. In the example above, each record is identified by a “member id”, which is a number unique to each row or record; this unique field is referred to as the “primary key” for that table.
Relationships
You should note at this point that mySQL is a “relational database management system”, or RDBMS. A relational database is typically composed of multiple tables, which contain inter-related pieces of information. SQL allows you to combine the data from these tables in different ways, thereby allowing you to create and analyze new relationships between your data.
What we have in the example above is a single table. While this is fine by itself, it’s when you add more tables, and relate the information between them, that you truly start to see the power inherent in this system. Consider the following example, which adds two more tables - the second contains a list of movies available for rental, while the third links the movies with the members via their primary keys.
+-----------+-------+---------+---------+--------------------------+
| member_id | fname | lname | tel | email |
+-----------+-------+---------+---------+--------------------------+
| 1 | John | Doe | 1234567 | [email protected] |
| 2 | Jane | Doe | 8373728 | [email protected] |
| 3 | Steve | Klingon | 7449373 | [email protected] |
| 4 | Santa | Claus | 9999999 | [email protected] |
+-----------+-------+---------+---------+--------------------------+
+----------+-------------------------------+------------------+
| video_id | title | director |
+----------+-------------------------------+------------------+
| 1 | Star Wars: The Phantom Menace | George Lucas |
| 2 | ET | Steven Spielberg |
| 3 | Charlie's Angels | McG |
| 4 | Any Given Sunday | Oliver Stone |
| 5 | Hollow Man | Paul Verhoeven |
| 6 | Woman On Top | Fina Torres |
+----------+-------------------------------+------------------+
+-----------+----------+
| member_id | video_id |
+-----------+----------+
| 2 | 6 |
| 4 | 2 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+-----------+----------+
If you take a close look at the third table, you’ll see that it links each member with the video(s) he/she has rented. Thus we see that Jane Doe (member #2) has rented “Woman On Top” (video #6), while John Doe (member #1) has decided to spend the week-end on the couch with “Star Wars” (video #1), “ET” (video #2) and “Charlie’s Angels” (video #3).
In other words, the third table has set up a relationship between the first and second table - this is the fundamental concept behind a relational database system. Once one or more relationships are set up, it is possible to extract a subset of the data (a “data slice”) in order to answer specific questions.
Building Blocks
If you’ve understood the concept, it’s now time to get down to brass tacks.
Start up your mySQL client
$ mysql
and (assuming everything is set up properly), you should see a prompt which looks something like this.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 80 to server version: 3.23.24-beta-debug
Type 'help;' or '\h' for help.
mysql>
This is the mySQL command prompt - you’ll be using this to enter all your SQL statements. Note that all mySQL commands end with a semi-colon, and can be entered in either upper-case or lower-case.
Since all tables are stored in a database, the first command you need to know is the CREATE DATABASE command, which looks like this:
CREATE DATABASE <database_name>
Go on - try it out by creating a database called “library”
mysql> CREATE DATABASE library;
Query OK, 1 row affected (0.05 sec)
You can view all available databases with the SHOW DATABASES command.
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| library |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
Once you have a list, you can select the database you wish to use with the USE command, which looks like this:
USE <database_name>
For the moment, we’ll restrict our activities to the database you just created.
mysql> USE library;
Database changed
Once you’ve selected a database, you can view available tables in it wit the SHOW TABLES command.
mysql> SHOW TABLES;
Empty set (0.00 sec)
Since this is a new database, there are no tables in it yet. Let’s fix that.
Not Your Type?
The SQL command used to create a new table in a database typically looks like this:
CREATE TABLE <table_name> (<field_name_1> <field_type_1> <modifiers>,
<field_name_2> <field_type_2> <modifiers>, ... , <field_name_n> <field_type_n> <modifiers>)
The table name cannot contain spaces, slashes or periods; other than this, anything is fair game. Each table (and the date it contains) is stored as a set of three files in your main MySQL directory.
Here’s a sample command to create the “members” table in the example you saw a few pages back:
mysql> CREATE TABLE members (member_id int(11) NOT NULL auto_increment,
fname varchar(50) NOT NULL, lname varchar(50) NOT NULL, tel varchar(15),
email varchar(50) NOT NULL, PRIMARY KEY (member_id));
Query OK, 0 rows affected (0.05 sec)
Note that each field name is followed by a “type” - this identifies the type of data that will be allowed in that field. MySQL offers a number of different data types, some of which are summarized below:
INT - a numeric type which can accept values in the range of -2147483648 to 2147483647
For numeric fields like INT, you can optionally specify the size of the field in parentheses after the field type; MySQL will automatically “pad” values which are shorter than the specified width with spaces. If you add the optional ZEROFILL modifier, these spaces are replaced with zeroes. You can also restrict entries to positive values only with the UNSIGNED modifier.
For example, INT(15) UNSIGNED ZEROFILL would create an INT field which only allowed positive values and padded values containing less than fifteen digits with zeroes.
DECIMAL - a numeric type with support for floating-point or decimal numbers
You can specify the number of digits displayed both before and after the decimal point in parentheses. For example, DECIMAL (6,3) would create a numeric type with support for values in the range -999.999 to 999.999
DOUBLE - a numeric type for double-precision floating-point numbers. If you don’t know what these are, chances are you won’t be using it much.
DATE - a date field in the YYYY-MM-DD format
TIME - a time field in the HH:MM:SS format
DATETIME - a combined date/time type in the YYYY-MM-DD HH:MM:SS format
YEAR - a field specifically for year displays in the range 1901 to 2155, in either YYYY or YY formats
TIMESTAMP - a timestamp type, in YYYYMMDDHHMMSS format
The TIMESTAMP type is particularly handy for situations where you would like to record the date and time of an entry into the table. This field is automatically set to the current date and time if no value is specified when inserting records, or if the value is specified as NOW().
You can specify a size for the TIMESTAMP field, depending on whether you would like a “full” or “abbreviated” timestamp.
CHAR - a string type with a maximum size of 255 characters and a fixed length
VARCHAR - a string type with a maximum size of 255 characters and a variable length
You can specify the size of a CHAR and VARCHAR field in parentheses. If values entered are longer than the specified size, they are automatically truncated.
The difference between CHAR and VARCHAR is that CHAR values are always “fixed” to a specific size, regardless of the number of characters entered. For example, CHAR(200) would create a character field which would “pad” values with spaces to a size of 200 characters. In contrast, VARCHAR values are stored as they are entered, with no additional padding.
TEXT - a string type with a maximum size of 65535 characters
BLOB - a binary type for variable data
ENUM - a string type which can accept one value from a list of previously-defined possible values
For example, ENUM(“huey”, “dewey”, “louie”) would create an ENUM field which could contain either of the specified three values, or a null value.
An ENUM type can have up to 65535 elements.
SET - a string type which can accept zero or more values from a set of previously-defined possible values
For example, SET(“huey”, “dewey”, “louie”) would create a SET field which could contain one of more of the specified three values, or a null value.
A SET type can have up to 64 elements
Changing Things Around
There are a few additional constraints (“modifiers”) you can put on your table, so as to increase the consistency of the data that will be entered into it.
You can specify whether the field is allowed to be empty, or must necessarily be filled with data by placing the NULL and NOT NULL modifiers after each field definition, like this:
CREATE TABLE members (
member_id int(11) NOT NULL auto_increment,
fname varchar(50) NOT NULL,
lname varchar(50) NOT NULL,
tel varchar(15),
email varchar(50) NOT NULL,
PRIMARY KEY (member_id)
);
You can specify a primary key for the table with the PRIMARY KEY definition, which is followed by the name of the column designated as the primary key.
You can specify that values entered into a field must be “unique” - that is, not duplicated - with the UNIQUE modifier.
The AUTO_INCREMENT modifier, which is only available for numeric fields, indicates that MySQL should automatically generate a number for that field (by incrementing the previous value by 1).
You can alter a table definition with the ALTER TABLE command, which looks like this:
ALTER TABLE <table_name> ADD <new_field_name> <new_field_type>
or, if you simply want to modify an existing column
ALTER TABLE <table_name> MODIFY <old_field_name> <new_field_type> <modifiers>
And just as you can create a table, you can delete a table with the DROP TABLE command; it looks like this:
DROP TABLE <table_name>
For example,
mysql> DROP TABLE members;
Query OK, 0 rows affected (0.00 sec)
This will immediately wipe out the specified table, together with all the data it contains - so use it with care!
Garbage In, Garbage Out
Once you’ve created a table, it’s time to begin entering data into it - and the SQL command to accomplish this is the INSERT command. The syntax of the INSERT command is as follows:
INSERT into <table_name (field_name_1, field_name2, field_name_n)
VALUES (value_1, value_2, value_n)
Here’s an example:
mysql> INSERT INTO members (member_id, fname, lname, tel, email) VALUES (NULL,
'John', 'Doe', '1234567', '[email protected]');
Query OK, 1 row affected (0.06 sec)
I could also use the abbreviated form of the INSERT statement, in which field names are left unspecified.
mysql> INSERT INTO members VALUES (NULL, 'John', 'Doe', '1234567', '[email protected]');
Query OK, 1 row affected (0.06 sec)
The flip side: by specifying field names in the INSERT statement, I have the flexibility of inserting values in any order I please. The statements below are equivalent.
mysql> INSERT INTO members (member_id, fname, lname, tel, email) VALUES (NULL,
'John', 'Doe', '1234567', '[email protected]');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO members (fname, lname, email, tel, member_id)
VALUES ('John', 'Doe', '[email protected]', '1234567', NULL);
Query OK, 1 row affected (0.00 sec)
Fields which are not specified will automatically be set to their default values.
Termination With Extreme Prejudice
Just as you INSERT records into a table, you can also DELETE records with the DELETE command, which looks like this:
DELETE FROM <table_name>
For example, the command
mysql> DELETE FROM members;
Query OK, 0 rows affected (0.06 sec)
would delete all the records from the “members” table.
You can select a specific subset of rows to be deleted by adding the WHERE clause to the DELETE statement. The following example would only delete those records which had a member id of 16;
mysql> DELETE FROM members WHERE member_id = 16;
Query OK, 1 row affected (0.06 sec)
Old Data For New
And finally, there’s an UPDATE command designed to help you change existing values in a table; it looks like this:
UPDATE <table_name> SET <field_name> = <new_value>
The above command would act on all values in the field <field_name>, changing them all to <new_value>. If you’d like to alter the value in a single field only, you can use the WHERE clause, as with the DELETE command.
Using this information, I could update John Doe’s email address in the table:
mysql> UPDATE members SET email = '[email protected]' WHERE member_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
You can alter multiple fields by separating them with commas.
mysql> UPDATE members SET email = '[email protected]', lname = 'Doe The First
WHERE member_id = 2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
And your table will now look something like this:
+-----------+-------+---------------+---------+--------------------------+
| member_id | fname | lname | tel | email |
+-----------+-------+---------------+---------+--------------------------+
| 1 | John | Doe The First | 1234567 | [email protected] |
| 2 | Jane | Doe | 8373728 | [email protected] |
| 3 | Steve | Klingon | 7449373 | [email protected] |
| 4 | Santa | Claus | 9999999 | [email protected] |
+-----------+-------+---------------+---------+--------------------------+
4 rows in set (0.00 sec)
And that’s about it for the first part of this article. Next time, I’ll be showing you how to get your data out of the table with a variety of SELECT statements - so make sure you come back for that!
Note: All examples in this article have been tested on mySQL 3.22. Examples are illustrative only, and are not meant for a production environment. YMMV!
This article was first published on 21 Dec 2000.