Welcome to my set of tutorials on MySQL. While there are loads of tutorials on MySQL (usually in combination with a scripting language) on the Net aimed at building applications etc.

Part 2 - Structures and Data

CREATE-ing tables and INSERTing data.

Requirements

You should have completed the Part 1 of this tutortial in this series.

Important information to understand before beginning.

A common problem when learning how to the MySQL Command Line Client is getting the syntax exactly right. There are several conventions which are used throughout the MySQL virtual workshops when displaying commands that you will have to type.

  $ - This preceding a command signifies that the command should be typed at the command line of your system. The '$' should not be included in any statement you type.

mysql> - This preceding a command signifies the command should be typed at the MySQL client Command Line Interface. Again ignore this and only type what follows.

There are also several other command prompts that you may encounter within the mysql client.

 -> This is a continuation line allowing you to enter a long statement over several lines. A statement is never executed until you issue a semi colon at the end of it.

You may at some point require to type either an apostrophe (') or a double quote (") as part of your data entry. However as these are used as part of the SQL syntax the statement would fail. This is signified by either:

 '> MySQL is asking you for another apostrophe        Or

"> MySQL is asking you for another double quote.

To successsfully execute a statement containing either of these characters they must be entered in pairs.

e.g. Keith's should be typed with two single apostrophes Keith''s and "Hello" should be typed ""Hello"" with two sets of double quotes.

The final convention that we use in this series is the 'Syntactic Example'. As each new element of SQL is introduced, the syntax will be given before an actual example to try. The syntactic command will contain generic references with angular brackets that will not work if typed. eg:

SELECT <field> FROM <table>

This means that the <field> and <table> parts of the statement should contain the actual names of a FIELD and TABLE.

Connecting To Your Database

In order to issue MySQL data manipulation statements, we must specify a username, a password and a database to use. This information will have been provided by your systems adminsistrator.

Either way you should now have in your possession a database name, a valid password for that database and a password. For the purposes of these workshops the following example values will be used.

In order to log into MySQL correctly we must pass information to the MySQL client program when we start it. This is done with the following commands and syntax.

$ mysql -u <mysql_username> -p <password> <database name>

The name of the program (mysql) is followed by the User flag (-u), the Password (-p) and finally the name of the database. So to start our MySQL session you could enter (but don't just yet):

$ mysql -u vworks -p mypass vworksDB

However is it a REALLY bad idea to ever enter a password anywhere in plain text, so if we omit the password but still leave the password flag (-p), you will be prompted for it (again do not try this yet).

$ mysql -u vworks -p vworksDB
Enter password:

You could also omit the name of the database as well (wait 'til I say so).

$ mysql -u vworks -p
Enter password:

...and once logged in elect to 'use' a specific database. You could choose to do this if your user has privileges on different databases.

mysql> use vworksDB
Database changed

...but we wont do that either. Okay, you can now log in to the database using the second example above.

$ mysql -u vworks -p examples
Enter password:

You should now be looking at the MySQL Command Line Interface that is awaiting any statement you wish to type.

mysql>

Creating a Table

Datatypes

Creating a table requires you to have an understanding of MySQL datatypes (similar if not identical to other database datatypes) so that you can define the fields in the table. The datatypes below are some of the basic datatypes and are meant as a simple introduction. If you are planning a production database you should investigate the datatypes thoroughly.

The syntax for defining a field and datatype (as used in the examples) is 'fieldname datatype'.

So lets get started with making a database table. You should have completed the Dats Collection form so we will create a table and begin to enter some of the data into your database.

CREATE TABLE......

The syntax for creating a table is as follows:

mysql> CREATE TABLE <table_name> (
field1 datatype, 
field2 datatype, 
etc......
);

When split over multiple lines within the MySQL command line client the continuation indictor will be used.

mysql> CREATE TABLE <table_name> (
-> field1 datatype,
-> field2 datatype,
-> etc......);

REMINDER: the command statement will not be executed until you use a semi-colon. Thus for the command to create a table based on the information in the data collection form you should enter the following (omitting the 'No. of Tracks' which we will add later):

mysql> CREATE TABLE cds (
-> cdID INT(3) auto_increment primary key,
-> artist VARCHAR(20),
-> title VARCHAR(30),
-> year INT(4),
-> label VARCHAR(20),
-> bought DATE );
Query OK, 0 rows affected (0.00 sec)

When entering these commands over several lines it is possible you may make a mistake and would want to cancel the command. This can be done by typing the '\c' command:

mysql> CREATE TABLE cds (
-> cdID IN(3)
-> \c
mysql>

You can check that you have created the table correctly by issuing a 'describe' command.

mysql> describe cds; 
+--------+-------------+------+-----+---------+----------------+ 
| Field  | Type        | Null | Key | Default | Extra          | 
+--------+-------------+------+-----+---------+----------------+ 
| cdID   | int(3)      |      | PRI | NULL    | auto_increment | 
| artist | varchar(20) | YES  |     | NULL    |                | 
| title  | varchar(30) | YES  |     | NULL    |                | 
| year   | int(4)      | YES  |     | NULL    |                | 
| label  | varchar(20) | YES  |     | NULL    |                | 
| bought | date        | YES  |     | NULL    |                | 
+--------+-------------+------+-----+---------+----------------+ 
6 rows in set (0.01 sec)

You will have noticed that when we created the first 'cdID' field we added a few other instuctions. These were 'auto_increment' and 'primary key'. These are used in the context of relational databases to identify the unique COLUMN in the TABLE (primary key) and to ensure that there cannot be duplicate numbers in the COLUMN by automatically numbering this FIELD (auto_increment).

Now that the table has been created we are ready to start inserting data.

INSERTing data

The SQL statement to INSERT data is again fairly straight forward once you get used to the syntax. The basic syntax for the INSERT statement is as follows.

INSERT INTO <table_name>
VALUES (
value1, 
value2, 
etc......
)

Which will work fine if you are inserting data into every field in a row. However if you only want to insert data into certain fields (columns) you will need to specify only these fields as part of the statement otherwise the number of data items will not match the number of available fields and MySQL will return an error.

INSERT INTO <table_name> (
column1, 
column2,
....
) VALUES (
value1, 
value2,
....
)

To illustrate this point we will try to insert the first example from the Data Collection form into the 'cds' table.

mysql> INSERT INTO cds 
-> VALUES (    
-> 'jamiroquai',
-> 'A Funk Odyssey',
-> '2001',
-> 'Sony Soho2',
-> '2001-09-13');
ERROR 1136: Column count doesn't match value count at row 1

As you can see we get an error telling us that the number of values we have tried to insert (5) does that match the number of columns that there are (6). This is because we have omitted the 'cdID' field which is used as the unique indentifer and thus gets generated automatically. In order for this to work we we would have to specify which 5 fields we want to insert our values into.

mysql> INSERT INTO cds (  
-> artist, title, year, label, bought
-> ) VALUES (
-> 'jamiroquai',
-> 'A Funk Odyssey',
-> '2001',
-> 'Sony Soho2',
-> '2001-09-01');
Query OK, 1 row affected (0.02 sec)

Although MySQL tells us that this has worked, we can check for ourselves by issuing a SELECT query.

mysql> SELECT * FROM cds; 
+------+------------+----------------+------+------------+------------+
| cdID | artist     | title          | year | label      | bought     | 
+------+------------+----------------+------+------------+------------+
|    1 | jamiroquai | A Funk Odyssey | 2001 | Sony Soho2 | 2001-09-01 |
+------+------------+----------------+------+------------+------------+ 
1 row in set (0.00 sec) 

Also notice that the 'cdID' field has a value of '1' that has been generated automatically by the 'auto_increment' property.

Although we could enter all our data by explicitly declaring the fields then the values, there is another way we could do it. Our first attempt at the INSERT SQL statement failed because we were trying to add 5 values to a database that has 6 fields (columns) and we couldn't specify a 6th value for the 'cdID' field as that value is an automatically generated number. What we can do in the statement is acknowledge that a field exists, but admit we don't know what the value will be. This is done by substituting a question mark for a real value. If we enter the statement again.

 
mysql> INSERT INTO cds VALUES (
-> '?',
-> 'jamiroquai',
-> 'A Funk Odyssey',
-> '2001',
-> 'Sony Soho2',
-> '2001-09-01');
Query OK, 1 row affected (0.00 sec)

This time the INSERT statement succeeds. Another SELECT statement confirms this.

mysql> SELECT * FROM cds; 
+------+------------+----------------+------+------------+------------+
| cdID | artist     | title          | year | label      | bought     | 
+------+------------+----------------+------+------------+------------+ 
|    1 | jamiroquai | A Funk Odyssey | 2001 | Sony Soho2 | 2001-09-01 |
|    2 | jamiroquai | A Funk Odyssey | 2001 | Sony Soho2 | 2001-09-01 |
+------+------------+----------------+------+------------+------------+ 
1 row in set (0.00 sec)