Search | Directories | Reference Tools
UW Home > UWIN > Computing and Networking > Web > Web Publishing > Using MySQL 

Using SQL (Structured Query Language) with the Command-Line MySQL Client

Summary

Once you know the basics of administering your MySQL server, you can begin the important part: using MySQL to store your data. For this you will use SQL (Structured Query Language). SQL is a special language that allows programmers to issue instructions or 'queries' to databases. This article will show you how to issue basic queries to create, modify, or retrieve information in MySQL databases using the command-line MySQL client.

Note: This article assumes that you have followed the instructions for installing and starting MySQL on the UW servers.

Included on this page:

Invoking the MySQL Client

You can issue SQL queries to your database using the command-line MySQL client, which you used during the installation of MySQL when you set user/host grant permissions in the "mysql" database. To invoke the client again, follow these intructions:

  1. Log in to your Homer or Dante account with Tera Term or another terminal emulator.

  2. Press the O key for Other, then press the W key to drop into the Web development environment, and cd into your mysql directory:

    cd mysql
  3. Start the MySQL client and log in as user root:
    ./bin/mysql -u root -p

You will be prompted for the root password, and then you should see something like the following:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51 to server version: 3.22.32
Type 'help' for help.
mysql>

You are now ready to issue SQL commands.

Creating and Using a Database

The first query we'll issue will create a database. After entering the following SQL command, you should see an acknowledgement from the MySQL client similar to the reply in italics printed below:

mysql> CREATE DATABASE foo;
Query OK, 1 row affected (0.00 sec)

Tip: Names of databases, tables, and fields in MySQL are case sensitive. They can include the underscore character, but not spaces or hyphens.

We can verify that the database has been created by asking:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| foo      |
| mysql    |
| test     |
+----------+

3 rows in set (0.01 sec)

Indeed, the new database is there, along with the mysql database that stores users and hosts, and an empty test database that was created during installation.

To work with your new database, you need to tell MySQL you want to use it:

mysql> USE foo;
Database changed

Creating and Listing a Table; Listing the Fields in a Table

You can now ask to see the tables in your database:

mysql> SHOW TABLES;
Empty set (0.00 sec)

As expected, there aren't any, because you haven't created any yet. You can do so now:

mysql> CREATE TABLE Friends (name char(20), age integer);
Query OK, 0 rows affected (0.05 sec)

This creates a table with two fields: a "name" field, which is an array of twenty characters, and an "age" field, which is an integer. Now when you ask to see your tables, you should see something like:

mysql> SHOW TABLES;
+--------------------+
| Tables in foo      |
+--------------------+
| Friends            |
+--------------------+
1 row in set (0.01 sec)

You can also view the columns in your new table:

mysql> SHOW COLUMNS FROM Friends;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.07 sec)

Putting Data into a Table

To insert a record into your table, type the following:

mysql> INSERT INTO Friends (name, age) values ('Tom', 20);
Query OK, 1 row affected (0.03 sec)

Now you can view your record:

mysql> SELECT * FROM Friends;
+------+------+
| name | age  |
+------+------+
| Tom  |   20 |
+------+------+
1 row in set (0.01 sec)

References

This article covers only the most basic of SQL commands. MySQL's Tutorial Introduction provides a good starting point for using the command-line MySQL client. The following specific links may be of most use for getting started:

Entering Queries
Creating and Using a Database
Getting Information about Databases and Tables
Examples of Common Queries.

Tip: The command-line MySQL client is just one of many tools that can be used to manipulate MySQL databases. Visit the following links to learn more about the alternatives:

The convenient web-based PHP utility phpMyAdmin can be used to create databases and manipulate data without the need for SQL.
The popular database software Microsoft Acess can be used to enter data into tables in an intuitive spreadsheet-like environment.
The scripting languages PHP and Perl can be used to write dynamic Web pages that modify MySQL databases.