Executing SQL Statements

Now let's look at how the mysql program is used to execute a SQL statement on a MySQL database.

Selecting a Database to Use

Your username and password give you access to one or more specific databases. For instance, if you are using a MySQL server provided by your web host, your username will give you access only to the database that is included with your web space. Other customers have their own individual passwords.

When you are connected to MySQL, issue the show databases command to see which databases are available to you. The SHOW DATABASES command is specific to MySQL but is executed through the mysql program like all other SQL commands.

 mysql> Show Databases; +--------------------+ | Database          | +--------------------+ | mysql             | | test              | | yourdb            | +--------------------+ 3 rows in set (0.00 sec) 

To select the database named yourdb, use the \u command along with the database name.

 mysql> \u yourdb Database changed 

Built-In Commands

Commands that begin with a backslash are internal commands for the mysql monitor program, not part of the SQL command set.

If you attempt to connect to a database that does not exist, or if you mistype the name, you will see an error message.

 mysql> \u wrongdb ERROR 1049 (42000): Unknown database 'wrongdb' 

You can also specify the database to connect to by using the - -database or -D switch to the mysql program.

 $ mysql - -user=yourname - -database=yourdb - -password 

In fact, the mysql program enables you to put a database name after all the switches have been given without prefixing it with - -database or -D. The following is, therefore, equivalent to the previous command:

 $ mysql - -user=yourname - -password yourdb 

Showing Connection Status

The \s command returns the status of the current database connection, including the current database name and the connection parameters.

If you are using several databases at the same time, issuing \s is a handy way to find out which one you are working on at any given time. The following output shows a connection to a MySQL 4.1 server running on a local Windows machine:

 mysql> \s - - - - - - -  C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql.exe  Ver 14.7 Distrib 4.1.14, for Win32 (ia32) Connection id:          94 Current database:       mysqlin10 Current user:           root@localhost SSL:                    Not in use Using delimiter:        ; Server version:         4.1.14-nt Protocol version:       10 Connection:             localhost via TCP/IP Server characterset:    latin1 Db     characterset:    latin1 Client characterset:    latin1 Conn.  characterset:    latin1 TCP port:               3306 Uptime:                 35 days 5 hours 34 min 51 sec Threads: 1  Questions:  542843 Slow queries: 18 Opens: 546 Flush tables: 1 Open tables: 0 Queries per second avg: 0.178 - - - - - - - - 


To force a reconnect to the database server, issue the \r command.

Entering a SQL Command

Although you will not learn specific SQL commands until the next lesson, here you should understand how to execute SQL statements through mysql.

Because SQL commands can span many lines, you must use a semicolon to tell MySQL that a command is finished.

Look back to the SHOW DATABASES command, in the section "Selecting a Database to Use." You'll see that it required a semicolon at the end of the command. If this had been omitted, the text on your screen would look like this instead:

 mysql> SHOW DATABASES     -> 

When you press the Enter key and a command has not been terminated, the prompt changes from mysql> to ->, indicating that you are continuing a command from the previous line of input. You can enter a semicolon at any time to terminate the statement, and your SQL command can span as many lines as necessary before being terminated.

Consider the following SQL query (you do not need to understand how it works yet), entered in mysql. The result of the query from the sample tables used in the book appears directly underneath the query.

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE customer_code = 'PRESINC'     -> ORDER BY last_name; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Abraham    | Lincoln   | | Richard    | Nixon     | | Franklin   | Roosevelt | | Theodore   | Roosevelt | +------------+-----------+ 4 rows in set (0.00 sec) 

The mysql program enables you to continue entering the query one line at a time; it sends the query to the MySQL server for execution only when you enter the semicolon to terminate the statement.

The query results are displayed in tabular format, just as you saw for SHOW DATABASES. The only difference this time is that there are two columns in the table. This format will quickly become familiarit is the default output format for all SQL queries executed through mysql.

Underneath the query results is shown the total number of records retrieved by the query and the total execution time required to produce the results. In this example, the execution time appears to be zeroin fact, the time taken by the MySQL server was less than one hundredth of a second. Only when you begin to create complex queries will you notice a significant execution time.

When No Records Are Retrieved

If your query returns no rows, MySQL returns no data and no error message. The following example shows the response produced:

 mysql> SELECT *     -> FROM emptytable; Empty set (0.00 sec) 

Instead of the row count, mysql shows the message Empty set. The execution time is still displayed.

Query Output Formats

Instead of using a semicolon terminator symbol, the mysql client provides two built-in commands that send your query to the MySQL server. The \g command immediately executes the query being entered and displays the results onscreen.

However, if you use \G instead of \g, the query results are displayed in a vertical format instead of the usual tabular layout. This format produces one row of output for each column in the database, as shown:

 mysql> SELECT * FROM products     -> \G *************************** 1. row ***************************   code: MINI   name: Small product weight: 1.50  price: 5.99 *************************** 2. row ***************************   code: MIDI   name: Medium product weight: 4.50  price: 9.99 *************************** 3. row ***************************   code: MAXI   name: Large product weight: 8.00  price: 15.99 3 rows in set (0.02 sec) 

Vertical Format

The vertical query format is very useful when you want to view a single row of data from a table. The tabular layout might produce output that is too wide for your screen if the table contains many columns.

You can also change the default output format to vertical by starting mysql with the - -vertical switch. Then simply terminating a query with a semicolon will produce output in this format.

Other Formats

Other format options that can be selected with switches when mysql is invoked are - -html and - -xml. These produce HTML table and XML data output, respectively.

Editing a SQL Command

MySQL provides the capability to easily edit the last SQL statement you entered so that you can make a small change or correction to your query without needing to retype it in full.

Use the \e command to open an editor containing the current query. The actual editor used depends on your system environment; this is usually vi or vim, unless you specify otherwise.

Changing Editors

To change the query editor, set the name of the program you want to use in the EDITOR environment variable.

After you edit and save the query, you are returned to the mysql client with the prompt showing ->. It is as if you had just entered the query at the command line. To execute the query again, enter a semicolon or use the \g or \G commands.

Most modern systems also support command-line editing. You can use the left and right cursor keys to move along the line currently being entered to change text as you type. You can also use the up and down keys to cycle through the command history and retrieve commands that you entered earlier in the session.

Starting Over

If you want to start again with a new SQL statement without running the command being entered, issue the \c command to clear the current command and return to the mysql> prompt.

Capturing Output from MySQL

If you want to divert the output of a SQL query to a log file, use the \T command along with a filename. For instance, to write the output of a query to query.txt, do the following:

 mysql> \T output.txt Logging to file 'query.txt' 

Any queries subsequently executed in mysql will be appended to query.txt and will be displayed onscreen. The retrieved data and the query itself are both written to the filein fact, the log file records exactly what is displayed onscreen.

To stop writing to a log file, the \t command cancels any previously issued \T commands.

Exiting the mysql Program

To exit the mysql program, use the \q command. Alternatively, you can type quit or exit to leave the program.

If you want to run a single host command without exiting mysql, use the \! command. The following example runs the system command pwd on a UNIX/Linux system to determine the path of the current working directory:

 mysql> \! pwd /home/chris/public_html 

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net