Executing SQL Statements
Now let's look at how the mysql program is used to execute a SQL statement on a MariaDB 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 MariaDB 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 MariaDB, issue the show databases command to see which databases are available to you. The SHOW DATABASES command is specific to MariaDB but is executed through the MariaDB program like all other SQL commands.
MariaDB> Show Databases; +--------------------+ | Database | +--------------------+ | MariaDB | | test | | yourdb | +--------------------+ 3 rows in set (0.00 sec)
To select the database named yourdb, use the \u command along with the database name.
MariaDB> \u yourdb Database changed
|
If you attempt to connect to a database that does not exist, or if you mistype the name, you will see an error message.
MariaDB> \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 MariaDB program.
$ MariaDB - -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:
$ MariaDB - -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 MariaDB server running on a local Windows machine:
MariaDB> \s - - - - - - - C:\Program Files\MariaDB\MariaDB 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 - - - - - - - -
|
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 MariaDB 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:
MariaDB> SHOW DATABASES ->
When you press the Enter key and a command has not been terminated, the prompt changes from MariaDB> 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.
MariaDB> 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 MariaDB 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 MariaDB 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, MariaDB returns no data and no error message. The following example shows the response produced:
MariaDB> 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 MariaDB client provides two built-in commands that send your query to the MariaDB 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:
MariaDB> 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)
|
You can also change the default output format to vertical by starting MariaDB with the - -vertical switch. Then simply terminating a query with a semicolon will produce output in this format.
|
Editing a SQL Command
MariaDB 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.
|
After you edit and save the query, you are returned to the MariaDB 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.
|
Capturing Output from MariaDB
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:
MariaDB> \T output.txt Logging to file 'query.txt'
Any queries subsequently executed in MariaDB 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 MariaDB Program
To exit the MariaDB 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:
MariaDB> \! pwd /home/chris/public_html
  |