Using SQL and Command-Line MySQL Client

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 setting up MySQL on the UW servers.

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 instructions:

1. Log into your web development server using a terminal emulator. If you’re not sure how to do this, click here for instructions.
2. Start the MySQL client and log in as user root by typing the following command:

~/mysql/bin/mysql -u root -p

Creating and Using a Database

1. Invoke the MySQL client as described previously in this document.
2. Type the following commands:
   

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

   • Note: Replace foo with the name of your desired database.
   • Tip: Names of databases, tables, and fields in MySQL are case sensitive. They can include the underscore character, but not spaces or hyphens.
3. Verify that the database has been created by typing the following command:
   

   mysql> SHOW DATABASES;

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.
• Scripting languages, including PHP, can be used to write dynamic Web pages that read and modify MySQL databases.