MySQL Beginners Guide

19 October 2011
A comprehensive training white paper desinged to get you started with MySQL, including installation and use.

MySQL

Training material
By: Justin Klein Keane
Business Process Solutions
September 26, 2001

Table of Contents

  1. Setup and Connection
  2. MySQL Security
  3. MySQL Usage
  4. MySQL Databases
  5. MySQL Tables
  6. MySQL Queries
  7. Conclusion

Setup and Connection:

To install MySQL you will first need to download the appropriate installs from www.mysql.com. To install on a Red Hat or Mandrake Linux server simply download the latest RPM and install it using:

rmp -install mysql-VersionNumber-i386.rpm

where 'VersionNumber' is the number of the release you downloaded. Simply use the .rpm download file as a guide for this.

Once MySQL is installed you will need to check and see if it is running. MySQL is a database server, so it offers connections in the same way a web or a mail server would. To check to see if the server 'mysqld' is running issue:

ps -aux | grep mysql

To check your running processes and see if the server is in fact up and running. If you find the process go ahead and connect to your server to test if the installation is working. Use:

mysql -u root -p

This will initiate a connection to the local MySQL server and will prompt you for a password. The default password is nothing, so just hit enter. If your connection is successful your command prompt should change from the default to:

mysql>

If this is the case your server is up and running. The next step in installation is to take care of some security concerns.

MySQL Security

MySQL runs as a server daemon and all the normal rules for server security apply to MySQL. Under most circumstances only the machine hosting the MySQL server should be allowed to connect. By default MySQL will allow remote connections, however. Unless remote users of MySQL must connect without first gaining a local shell, or a remote web server will be calling MySQL, you should protect your server with any acceptable firewalling mechanisms. This may be as simple as changing your inetd or xinetd to refuse connections, alternatively you can use ipchains or iptables to deny access. On a Mandrake Linux server the simplest solution is to install Bastille firewall and simply alter the firewalling rules to deny connections to the MySQL port (3306).

Another major step in securing a MySQL installation is to change the default root password. Because this account is installed with full access to all tables and databases in MySQL it is critical that the password be changed. In order to change your root password take the following steps:

  1. Log into MySQL using 'mysql -u root -p'
  2. Change into the mysql database using 'use mysql;'
  3. update the user table (which contains user usernames and passwords) by issuing:
    UPDATE user set password = PASSWORD("newpassword") where user = 'root';
    FLUSH PRIVILEGES;
    
  4. This will update your root password and reset all privileges on the server. Log out using
    mysql>exit;
    

Now you can log back in normally using 'mysql -u root -p' and enter your new root password. Note that in step three above we used the MySQL function password(). This is because all passwords are stored in the user table in encrypted format. The password() function encrypts the new password in acceptable format so that you can use your new password properly.

MySQL Usage

You will note that MySQL is used from a command prompt. This is advantageous in the same way that any Unix command prompt is. Using the MySQL command line you can review your MySQL history by pressing the up and down arrows. This will review previously issued commands. You can also use command completion by typing a piece of a command and pressing the right '?' arrow key.

All MySQL commands are terminated in a C style by ending statements with a semicolon(;). This means that statements can span several lines, including new line returns, but will not execute until a semicolon (;) and the return key are pressed. Thus the following is a valid MySQL statement:

mysql>select *
       ->from test_table;

Be sure to terminate all statements with a semicolon in order for execution to occur.

MySQL Databases

After logging into MySQL you should examine all the databases available. There are two databases installed by default. To view them type:

mysql>show databases;

Typically you should see:

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

The first database (mysql) is the native mysql database used to store users, passwords, permissions and other material used by the server. DO NOT alter this database. Only authorized administrators should be permitted to interact with the mysql database. The second database (test) is a test database that can be deleted at your discretion. In order to create a new database simply issue:

mysql> create database foo;

Then list the databases as described above and make sure your new database is listed. If you find this database you issued the command correctly. To remove our new database all we have to do is drop it. Issue the command:

mysql> drop database foo;

and you should get the result:

Query OK, 0 rows affected (0.00 sec)

Note that MySQL never prompts the user to make sure they are certain of their command. Use the 'drop' command with extreme caution. Once a database is dropped it is irretrievable.

MySQL Tables

Creating tables in MySQL is done in much the same manner as with any SQL database. Let us once again create a sample database using:

mysql> create database sample;

Then let us switch into our new database. In order to work with any of the contents in a database we must first connect to that database. The syntax for database connectivity is:

mysql> use sample;

Where 'sample' is the name of the database to which you wish to connect. Once we are in sample we can create our first table. To create a simple table named 'sample_table' with the fields 'sample_id' (an integer) and 'sample_data' (a variable character field with a maximum length of 255 characters) issue:

mysql> create table sample_table (sample_id int, sample_data varchar(255));

There are several data types used by MySQL. The following is a list of common data types available for table columns:

INT		An integer value with a maximum size of 4 bytes
BIGINT		An integer value with a maximum size of 8 bytes
CHAR(x)		A character value with the size of 'x' (maximum of 255)
VARCHAR(x)	A variable character value with a size of 'x' (maximum of 255)
TEXT		A string with the maximum size of 65535 bytes
LONGTEXT          A string with the maximum size of 4.2GB

In addition to the default data types we can add some modifiers to our columns as we create our new tables. Some common modifiers include:

AUTO_INCREMENT	This modifier automatically increments the column
NOT NULL          This restricts the field so it will not accept null inserts
PRIMARY KEY	This sets the column to server as the table's primary key
UNIQUE		This modifier forces unique data in the column

Note that at this time MySQL does NOT support enforce referential integrity. Thus, you must be careful to design your applications to enforce referential integrity rather than relying on MySQL for enforcement.

Using the above rules let us go ahead and delete the old 'sample_table' and create a new one using stricter guidelines. First drop the old table using:

mysql>drop sample_table from sample;

Again, this drop statement will not prompt you to confirm the deletion. Let us now create a new table and assign an auto incrementing primary key that is always filled and a simple field for varchar data. Issue the following command:

mysql>create table sample_table(sample_id int primary key auto_increment not null, sample_data varchar(255));

This will create a much more robust table using the same data types. In verify the information we have just entered, issue the following command:

mysql>show columns from sample_table;

You should see the following:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| sample_id   | int(11)      |      | PRI | NULL    | auto_increment |
| sample_data | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

You can use this method to check to design of any given table. Note that the sequence of commands issued in the 'create' statement are not strict in terms of their order. You may list primary key, auto_increment, and not null in any order. The only constraint is that you list the column name first, then the data type, then any modifiers in any order you wish.

MySQL Queries

MySQL queries are the method by which information is selected, retrieved, updated, inserted, and deleted from a table in a MySQL database. The most basic types of queries are select, insert, update and delete.

Insert Queries (or Insert Statements)

Insert statements are used to add data to an existing table in the database. The syntax for insert statements is:

Insert into tablename (fieldname) values ('data');

Where 'tablename' is the name of the table you wish to add data to, 'fieldname' is the name of the column into which you wish to add data, and 'data' is the actual material you wish to be inserted into the specified field. Using our sample_table from above, let us insert some material:

mysql> insert into sample_table (sample_data) values ('Some data');

This will insert new material into the database. Note that we set our sample_table to auto increment the primary key, so that even though we did not insert material into the column sample_id, MySQL will automatically put a integer value into the table. Note that if you wish to insert data into multiple fields the syntax is:

Insert into tablename (fieldname1, fielname2) values ('data_for_fieldname1', 'data_for_fieldname2);

Note that the order of material to be inserted should correspond with the order in which you listed the field names in your insert statement.

Select Queries (or Select Statements)

A select statement is simply coded using the following format:

Select column_name from table;

The 'column_name' refers to the field name from which you wish to select data. Note that if you wish to select all the data from a table you do not need to enter all the column names, simple use an asterisk (*). For instance, let us check to make sure our newly inserted material in sample_table is correct. Issue the following command:

mysql> select * from sample_table;

You should get the following result:

+-----------+-------------+
| sample_id | sample_data |
+-----------+-------------+
|         1 | Some data   |
+-----------+-------------+
1 row in set (0.00 sec)

If you only wanted one field's worth of data you can specify that field in the table. Let us insert some more data in the table so that we can issue more specific select statements. Issue the following statements to insert additional data in sample_table:

mysql> insert into sample_table (sample_data) values ('some more data');
mysql> insert into sample_table (sample_data) values ('Additional data');

now select all the data as shown above and you should have:

mysql> select * from sample_table;
+-----------+-----------------+
| sample_id | sample_data     |
+-----------+-----------------+
|         1 | Some data       |
|         2 | some more data  |
|         3 | Additional data |
+-----------+-----------------+
3 rows in set (0.00 sec)

We can now use some conditional statements in our selection. The syntax for adding conditions is:

Select fieldname from tablename where condition = constraint;

Where 'fieldname' is the field you wish to select from, 'tablename' is the table containing the data and 'condition = constraint' is a fieldname followed by an operator followed by a constraint. The common operators are:

>	greater than
<	less than
=	equal to
>=	greater than or equal to
<=	less than or equal to
!=	not equal to

Let us say for example we want to select all the data that is listed with a sample_id of greater than one. We can use the following select statement:

mysql>select * from sample_table where sample_id > 1;

Using conditional 'where' clauses we can restrict our select statements to return only selected pieces of data. We can even use rudimentary search functionality. For instance if we issue:

mysql> select * from sample_table where sample_data like '%ome%';

we should get in return:

+-----------+----------------+
| sample_id | sample_data    |
+-----------+----------------+
|         1 | Some data      |
|         2 | some more data |
+-----------+----------------+
2 rows in set (0.00 sec)

This statement bears some explaination. The 'like' operator is special in that it searches data in the column for material that is similar to the constraint. The percent sign is used as a wildcard. Thus, in the above statement MySQL will search the table for entries in sample_data that resemble anything followed by the letters 'ome' and then followed by anything. Since the entry in the table 'Additional data' (sample_id 3) does not contain the letters 'ome' anywhere, it is omitted from the returned results.

Update Queries (changing existing data)

Update queries are used to alter data already in the database. The syntax for a simple update query is as follows:

Update tablename set fieldname = newdata where condition = constraint

Where 'tablename' is the name of the table to be altered, 'fieldname' is the column name of the column you wish to alter, 'newdata' is the new material with which to overwrite the current data, and 'condition = constraint' is a condition (usually a fieldname) followed by and operator and then followed by a constraint. Let us put this into action and see if we can make more sense out of it. Our sample_table contains an entry containing 'some more data'. Let us change that entry so that it reads 'updated data'. Issue the following command:

mysql> update sample_table set sample_data = 'updated data' where sample_id = 2;

Note that the constraint is vitally important. Without the 'where' clause this statement will update all the rows in the table, changing all entries for the column sample_data to 'updated data'. Check to see if your new data has been added by issuing a 'select * from sample_table;' statement. You should receive:

mysql> select * from sample_table;
+-----------+-----------------+
| sample_id | sample_data     |
+-----------+-----------------+
|         1 | Some data       |
|         2 | updated data    |
|         3 | Additional data |
+-----------+-----------------+
3 rows in set (0.00 sec)

If you want to update more than one column at a time the syntax is:

Update tablename set fieldname1 = newdata1, fieldname2 = newdata2 where condition = constraint;

Using this method you can update tables with multiple columns.

Delete Queries (removing data from tables)



Delete statements should be used with extreme caution. Once removed, data cannot be recovered. For this reason you should be careful with the 'delete' command AND be sure to back up your databases regularly. The delete statement syntax is:

Delete from tablename where condition = constraint;

Where 'tablename' is the name of the table you wish to alter, and 'condition=constraint' is a condition (usually a fieldname) followed by an operator followed by a constraint. Note that you do not specify individual columns to delete. This statement will destroy an entire row of data. If you wish to eliminate individual column data from a particular row you should use an 'update' statement rather than a 'delete' statement. Let us put this into action by deleting our recently updated row with the sample_id of 2. Issue the following command:

mysql>delete from sample_table where sample_id = 2;

It is very important that you include the 'where sample_id = 2' portion of this statement or you will delete ALL of the data in sample_table. Check to see if the data has been removed by selecting all the data from the table, you should have:

mysql> select * from sample_table;
+-----------+-----------------+
| sample_id | sample_data     |
+-----------+-----------------+
|         1 | Some data       |
|         3 | Additional data |
+-----------+-----------------+
2 rows in set (0.00 sec)

Again, be very careful when using the delete statement as you are permanently removing records from your table.

Conclusion

Remember that MySQL is a free tool supported by a community of users. If you have questions or are troubleshooting your first and best resource is the internet. MySQL's official website (www.mysql.com) is the best starting place for searching for material. Although MySQL may not have many of the advanced options of more common database tools, it is free, and quickly gaining popularity and functionality. As with all open source tools, you should regularly check for updates and patches that could impact the security and functionality of your MySQL database server. MySQL is a robust tool capable of performing small and large operations. Several major organizations including Slashdot (www.slashdot.org) with over a million user hits a day, and the US National Aeronautics and Space Agency (NASA) use MySQL to meet their database needs. MySQL also offers a wealth of functionality to other open source tools, such as easy interoperability with PHP to make dynamic data driven websites. These and many other options are detailed on the MySQL website.