Chapter 1. Using the mysql Client Program

Chapter 1. Using the mysql Client Program

Section 1.1.  Introduction

Section 1.2.  Setting Up a MySQL User Account

Section 1.3.  Creating a Database and a Sample Table

Section 1.4.  Starting and Terminating mysql

Section 1.5.  Specifying Connection Parameters by Using Option Files

Section 1.6.  Protecting Option Files

Section 1.7.  Mixing Command-Line and Option File Parameters

Section 1.8.  What to Do if mysql Cannot Be Found

Section 1.9.  Setting Environment Variables

Section 1.10.  Issuing Queries

Section 1.11.  Selecting a Database

Section 1.12.  Canceling a Partially Entered Query

Section 1.13.  Repeating and Editing Queries

Section 1.14.  Using Auto-Completion for Database and Table Names

Section 1.15.  Using SQL Variables in Queries

Section 1.16.  Telling mysql to Read Queries from a File

Section 1.17.  Telling mysql to Read Queries from Other Programs

Section 1.18.  Specifying Queries on the Command Line

Section 1.19.  Using Copy and Paste as a mysql Input Source

Section 1.20.  Preventing Query Output from Scrolling off the Screen

Section 1.21.  Sending Query Output to a File or to a Program

Section 1.22.  Selecting Tabular or Tab-Delimited Query Output Format

Section 1.23.  Specifying Arbitrary Output Column Delimiters

Section 1.24.  Producing HTML Output

Section 1.25.  Producing XML Output

Section 1.26.  Suppressing Column Headings in Query Output

Section 1.27.  Numbering Query Output Lines

Section 1.28.  Making Long Output Lines More Readable

Section 1.29.  Controlling mysql's Verbosity Level

Section 1.30.  Logging Interactive mysql Sessions

Section 1.31.  Creating mysql Scripts from Previously Executed Queries

Section 1.32.  Using mysql as a Calculator

Section 1.33.  Using mysql in Shell Scripts

1.1 Introduction

The MySQL database system uses a client-server architecture that centers around the server, mysqld . The server is the program that actually manipulates databases. Client programs don't do that directly; rather, they communicate your intent to the server by means of queries written in Structured Query Language (SQL). The client program or programs are installed locally on the machine from which you wish to access MySQL, but the server can be installed anywhere , as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL queries to it to have database operations performed, and receiving the query results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts for a query, sends it to the MySQL server for execution, and displays the results. This capability makes mysql useful in its own right, but it's also a valuable tool to help you with your MySQL programming activities. It's often convenient to be able to quickly review the structure of a table that you're accessing from within a script, to try a query before using it in a program to make sure it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used non-interactively, for example, to read queries from a file or from other programs. This allows you to use it from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql 's capabilities so that you can use it more effectively. Of course, to try out for yourself the recipes and examples shown in this book, you'll need a MySQL user account and a database to work with. The first two sections of the chapter describe how to use mysql to set these up. For demonstration purposes, the examples assume that you'll use MySQL as follows :

• The MySQL server is running on the local host.

• Your MySQL username and password are cbuser and cbpass .

• Your database is named cookbook .

For your own experimentation, you can violate any of these assumptions. Your server need not be running locally, and you need not use the username, password, or database name that are used in this book. Naturally, if you don't use MySQL in the manner just described, you'll need to change the examples to use values that are appropriate for your system. Even if you do use different names, I recommend that you at least create a database specifically for trying the recipes shown here, rather than one you're using currently for other purposes. Otherwise, the names of your existing tables may conflict with those used in the examples, and you'll have to make modifications to the examples that are unnecessary when you use a separate database.