Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

Using MySQL - Data and Structures

Workshop Requirements

You should have completed the first workshop in this series.

You should also have access to the MySQL command line client software. You can either ask your systems adminstrator for access to this client, alternatively you could install a local copy of MySQL - see Setting Up a Server for details.

You should also have full permissions on a database (or the ability to create a new one), again check with your administrator if necessary.

Finally you should have printed out and filled in the Data Collection Form........ from the experience of others it IS easier than doing this 'on the fly'.

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. If you are running MySQL yourself, then skip ahead to the section on Creating a Database in Part 6 - then come back to this point.

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.

Database Name	vworksDB
Valid MySQL Username	vworks
Password	mypass

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>

In the previous MySQL Virtual Workshop we looked at issuing SQL SELECT statements containing conditions to retrieve data from a table. The natural progression is to start constructing other statements which INSERT, UPDATE and DELETE records from the table. However, before we attempt this we need to create a table in our database.

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.

Datatype	Description	Example:
INT	Numeric entry	id INT
VARCHAR(n)	Text string of characters up to n with a maximim of 255 characters	name VARCHAR(20)
CHAR(n)	Text string with specific number (n) of characters. If the number of characters is less than 'n' then is padded by spaces (spaces are removed when data is retrieved). 255 Maximum.	address CHAR(30)
TEXT	Holds between 255 - 65535 characters	philosophy TEXT
DATE	The date stored in the format YYYY-MM-DD	dob DATE
TIME	The time stored in the format HH:MM:SS	tob TIME

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 Data 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)

Now we have the same entry twice, which is okay as we will learn how to delete records in the next Virtual Workshop.

Mini Exercise

You should now enter the second example and all of your own data into the 'cds' TABLE using the INSERT statement. Try alternating between explicitly declaring the fields/values and offering the '?' as the 6th field when inserting the data. Also revist the SELECT command used in Part One, querying the data you have just entered.

TOP TIP: The 'Up Arrow' key allows you to retrieve previously typed statements, which speeds up the process of entering the data enormously