MySQL 101 – The basics

MySQL is one of the most widely used relational database management systems (RDBMS). MySQL is used to manage databases in a wide variety of applications including the integrated web solution known as LAMP (Linux Apache MySQL Perl/PHP/Python). Database management is accomplished in MySQL using Structured Query Language (SQL). Because it scales well, MySQL works for projects of almost any size and complexity. Strong security features and the abundance of tools to control it make MySQL attractive to users who want to protect their data and still have easy access to and easy use of it.

Tutorial

This guide has been created to serve as a reference for most basic commands in SQL. Below, we have used a random test database to show you the basic principles of operating MySQL directly from the Command Line Interface (CLI). If you master the commands below, you will be well on your way to understanding how to effectively manage a database using MySQL.

Connecting to MySQL

To get started, establish an active SSH session on your server. We will connect using the root user of our MySQL instance for convenience. To follow along, you will need to know your MySQL root password to initiate your session.

mysql -u root -p

Password:


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5975
Server version: 5.1.73 Source distribution


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

The appearance of the mysql> prompt after you enter the command to initiate the session indicates you are connected to the MySQL instance on your server and can execute commands.

View databases

Our first command is SHOW DATABASES. When you enter this command, a list of all the active databases on this MySQL instance will print to the screen.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| radius |
+--------------------+
3 rows in set (0.02 sec)

Create a database

To use MySQL for your own projects you will need your own databases. Whether you want to start from scratch, or import a schema or data from an existing database you will need first to create the databases you want to use in this instance of MySQL. Go ahead and create the database “mynewdb” with the following command:

mysql> create database mynewdb;
Query OK, 1 row affected (0.00 sec)

Your database “mynewdb” is now ready to use. Now, you can create tables in it or import the contents of an existing database or schema.

Use a database

The USE command will enable you to select a specific database to work on. For instance, you may use mynewdb by entering

[root@server ~]# mysql -u root -p mynewdb < radius.sql
Enter password:

Populating a database

You have two basic options for populating mynewdb: Manually create tables and insert data into those tables or import the contents of an existing database or schema structure into mynewdb. Because importing is more straightforward, we will start there.

Import data

To import data from an existing database, you must execute the command outside of MySQL, directly in the SSH command line. MySQL database backups are text files that are typically created with the .sql file extension name so that they may be easily identified as database backups. For our example, we'll import the contents of the radius.sql file into the mynewdb database.

mysql> use radius
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

The IMPORT command populated mynewdb by creating a copy of all of the tables and records that were found in the original database. MySQL accomplished this duplication by running every SQL command that had been run to populate the original database.

Delete a database

When a database has reached the end of its useful life deleting it is quite a straightforward process. Simply do as we show below to remove a database from your MySQL instance.

mysql> drop database mynewdb;
Query OK, 35 rows affected (0.05 sec)

Be careful with this command. Once a database is deleted the data in it cannot be recovered from within MySQL. Of course, if you have retained a backup you can always create a new database and import the contents of the old one.

Create a table

MySQL databases are built using tables. The creation a MySQL table is a complex task. Tables are so customizable that they really need to be designed to meet your specific needs or the needs of your application. To keep this guide brief we'll show you how we created a specific table we'll be using in our further examples below.

One of the tables in our sample database was created like so:

mysql> CREATE TABLE `radgroupreply` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '=',
`value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `groupname` (`groupname`(32))
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

Show tables within the database

Tables are associated with the database in which they are created. To see a list of all the tables in the database with which you are working use the SHOW TABLES command:

mysql> show tables;
+------------------------+
| Tables_in_radius |
+------------------------+
| batch_history |
| billing_history |
| billing_merchant |
| billing_paypal |
| billing_plans |
| billing_plans_profiles |
| billing_rates |
| cui |
| dictionary |
| hotspots |
| invoice |
| invoice_items |
| invoice_status |
| invoice_type |
| nas |
| node |
| operators |
| operators_acl |
| operators_acl_files |
| payment |
| payment_type |
| proxys |
| radacct |
| radcheck |
| radgroupcheck |
| radgroupreply |
| radhuntgroup |
| radippool |
| radpostauth |
| radreply |
| radusergroup |
| realms |
| userbillinfo |
| userinfo |
| wimax |
+------------------------+
35 rows in set (0.01 sec)

If you are following along with our guide here, you should see the newly created table in your list.

Delete a table

Occasionally, you realize you don’t need a table anymore. Simply follow the command below to eliminate the unwanted table.

mysql> drop table radgroupreply;
Query OK, 0 rows affected (0.01 sec)

As with the delete database command, you should use this command with caution as it is not easy to recover from the mistake of using it when you don’t mean to do so.

Show the structure of a table

During the lifetime of your database, you will likely want to add (INSERT) or modify (UPDATE) fields. To use both the INSERT and UPDATE commands effectively you need to know what is already in the database. To list the existing columns, rows, and all their associated parameters use the DESCRIBE command:

mysql> desc wimax;
+----------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| authdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| spi | varchar(16) | NO | MUL | | |
| mipkey | varchar(400) | NO | | | |
| lifetime | int(12) | YES | | NULL | |
+----------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

Select rows from a table

Use the SELECT command to view a particular portion of your data in a table (or all of it). This command is highly customizable. We'll show you a few variants below.

Select everything

If you wish to generate a list of everything from the table enter the following.

mysql> SELECT * FROM radgroupreply;
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
+----+-----------+-----------------------+----+-------+

Select everything where groupname is default

Groupname is the title of a column or field in our table. Use of the command

mysql> SELECT * FROM radgroupreply WHERE groupname="default";
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
+----+-----------+-----------------------+----+-------+
1 row in set (0.00 sec)

will return the whole row where the value of the entry in the column groupname matches the "default" value.

Select everything where value is smaller or equal to 15

The following will return the whole row where the value field is populated with a value less than or equal to 15 (<=). mysql> SELECT * FROM radgroupreply WHERE value<=15;

+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 4 | test3 | This is a third test | = | 12 |
+----+-----------+-----------------------+----+-------+
3 rows in set (0.00 sec)

You may construct similar commands with other operators such as <, >, <=, >= and =.

Select the data from only 1 field for all rows

In this example, we will select only one field from the table in question.

mysql> SELECT value FROM radgroupreply;
+-------+
| value |
+-------+
| 15 |
| 5 |
| 29 |
| 12 |
+-------+
4 rows in set (0.00 sec)

The data is displayed in a single column rather than being formatted like the whole table.

Select the data from only multiple fields for all rows

Here we will select multiple fields from the table in question. You'll see that the data is displayed in two columns.

mysql> SELECT groupname,value FROM radgroupreply;
+-----------+-------+
| groupname | value |
+-----------+-------+
| default | 15 |
| test1 | 5 |
| test2 | 29 |
| test3 | 12 |
+-----------+-------+
4 rows in set (0.00 sec)

Following the pattern established above, you can select the data from as many or as few of the fields in a table as you wish.

INSERT Rows in a Table

The INSERT command enables you to insert rows into a specific table. Inserts can be performed in a few different manners. The method you choose will depend on the mechanics of your code. We will show you two common methods to give you a general sense for how it is done.

First insert method

Our first example of how to use INSERT is based on our previous examples of how to use SELECT. This command creates a row consistent with the same table format used above:

mysql> INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES ('my group is the best', 'this attribute rocks', '=', '12');
Query OK, 1 row affected (0.00 sec)

To check that the row has been inserted, we can perform a select on radgroupreply. The command and results should appear as follows:

mysql> SELECT * FROM radgroupreply;
+----+----------------------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+----------------------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
| 5 | my group is the best | this attribute rocks | = | 12 |
+----+----------------------+-----------------------+----+-------+
5 rows in set (0.00 sec)
 
Second insert method

A new method of inserting data into MySQL tables has gained popularity recently. This method achieves the same thing as the first but the formatting of the query is quite a bit different, as you can see below:

mysql> INSERT INTO radgroupreply SET groupname='This other group rocks', attribute='Second type of insert method', op='+', value='11';
Query OK, 1 row affected (0.00 sec)

As with the previous INSERT method, to confirm the insertion worked properly, perform a select on radgroupreply as follows:

mysql> select * from radgroupreply;
+----+------------------------+------------------------------+----+-------+
| id | groupname | attribute | op | value |
+----+------------------------+------------------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 5 |
| 3 | test2 | This is a second test | = | 29 |
| 4 | test3 | This is a third test | = | 12 |
| 5 | my group is the best | this attribute rocks | = | 12 |
| 6 | This other group rocks | Second type of insert method | + | 11 |
+----+------------------------+------------------------------+----+-------+
6 rows in set (0.01 sec)

and confirm the new row is now in the table.

The UPDATE Command to Update Rows / Fields in a Table

Using UPDATE you can change the contents of existing rows in a table. This command is quite versatile and will become handy in any SQL project you work on. We'll show you a couple of basic examples of how to use the UPDATE command to get you started.

Update a single value from a row

The smallest change you can make to a table is to update a single value from a specific row. To change just one value you need a unique identifier for the row you wish to change. Every row in a given table has a unique row id that is recorded in the id column. To make sure you update the right row use the WHERE statement combined with the desired value from the id column as in the example below.

mysql> UPDATE radgroupreply SET value='9' WHERE groupname='test1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Our example should have changed the value for the groupname “test 1” to “9”. We can confirm that change using the following SELECT command:

mysql> SELECT value FROM radgroupreply WHERE groupname="test1";
+-------+
| value |
+-------+
| 9 |
+-------+
1 row in set (0.01 sec)

Update multiple values in multiple rows

You can also update multiple rows at the same time. We have provided a simple example of such an UPDATE command in which we change the value fields to “0” for all the rows in which the value of the op field is “=".

mysql> UPDATE radgroupreply SET value="0" WHERE op="=";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Again, we will use the SELECT command to check whether the desired changes occurred. Given that several rows were affected, it will be easier to figure out if the changes have been made if we print the whole table like so.

mysql> SELECT * FROM radgroupreply;
+----+------------------------+------------------------------+----+-------+
| id | groupname | attribute | op | value |
+----+------------------------+------------------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 0 |
| 3 | test2 | This is a second test | = | 0 |
| 4 | test3 | This is a third test | = | 0 |
| 5 | my group is the best | this attribute rocks | = | 0 |
| 6 | This other group rocks | Second type of insert method | + | 11 |
+----+------------------------+------------------------------+----+-------+
6 rows in set (0.00 sec)

The results of this latest SELECT should show that the value fields have been updated to "0" for all rows where the op field was "=".

DELETE rows in a table

There are times when you need to delete data from your tables. Such deletions can be done one at a time or in bulk.

Deleting a single row

The safest way to delete a specific single row is to specify that row in your DELETE command based on the id column. The following command is an example of this approach:

mysql> DELETE FROM radgroupreply WHERE id="6";
Query OK, 1 row affected (0.00 sec)

If you print the full table, using the command below, you should see that the row with id value “6” is no longer present in the table.

mysql> SELECT * FROM radgroupreply;
+----+----------------------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+----------------------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
| 2 | test1 | This is a test | = | 0 |
| 3 | test2 | This is a second test | = | 0 |
| 4 | test3 | This is a third test | = | 0 |
| 5 | my group is the best | this attribute rocks | = | 0 |
+----+----------------------+-----------------------+----+-------+
5 rows in set (0.00 sec)

Deleting a multiple rows

You will often need to remove multiple fields from a table for housekeeping reasons. MySQL tables can become quite large and it's often wise to clean them up for manageability and performance purposes.

Below we'll show you how to delete multiple rows based on a common factor. For this example, we'll delete ALL the rows for which the amount in the value column is below 15. Given the data in our table, that should mean that all rows other than the first row will be deleted when you enter this command:

mysql> DELETE FROM radgroupreply WHERE value<"15";
Query OK, 4 rows affected (0.00 sec)

With the following SELECT command, we can confirm that all rows have been deleted other than the one where the value column indicates a value of 15 or above.

mysql> SELECT * FROM radgroupreply;
+----+-----------+-----------------------+----+-------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+-------+
| 1 | default | Acct-Interim-Interval | := | 15 |
+----+-----------+-----------------------+----+-------+
1 row in set (0.00 sec)

Conclusion

Now you know all the basic commands needed to administer MySQL data. There are myriad variations on the above commands that will afford you greater and greater control over the behavior and content of MySQL databases. The best way to learn these variations and the efficiency that comes of using them is to take what you have learned in this tutorial and play with it.