Flexible commands entry and SQL statements : PHP

The up and down arrow keys allow previously entered commands and statements to be browsed and used.
The interpreter has command completion. If you type the first few characters of a string that has previously been entered and press the Tab key, the interpreter automatically completes the command. For example, if wines is typed and the Tab key pressed, the command interpreter outputs winestore, assuming the word winestore has been previously used.

If there's more than one option that begins with the characters entered, or you wish the strings that match the characters to be displayed, press the Tab key twice to show all matches. You can then enter additional characters to remove any ambiguity and press the Tab key again for command completion.

Several common statements and commands are pre-stored, including most of the SQL keywords discussed in this chapter.
To use the default text editor to create SQL statements, enter the command edit in the interpreter. This invokes the editor defined by the EDITOR shell environment variable. When the editor is exited, the MySQL command interpreter reads, parses, and runs the file created in the editor.
When the interpreter is quit and run again later, the history of commands and statements is kept. It is still possible to scroll up using the up arrow and to execute commands and statements that were entered earlier.
You can run commands and SQL statements without actually launching the MySQL command interpreter. For example, to run SELECT now( ) from the Linux shell, enter the following command:
```
mysql -ppassword -e "SELECT now(  );"
```
This is particularly useful for adding SQL commands to shell or other scripts.

Managing Databases, Tables, and Indexes

The Data Definition Language (DDL) is the set of SQL statements used to manage a database. In this section, we use the MySQL command interpreter to create databases and tables using the online winestore as a case study. We also discuss the statements that delete, alter, and drop databases and tables, as well as statements for managing indexes.

Creating Databases

The CREATE DATABASE statement can create a new, empty database without any tables or data. The following statement creates a database called winestore:

mysql> CREATE DATABASE winestore;

To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Different command interpreters have different methods for using a database and these aren't part of the SQL standard. In the MySQL interpreter, you can issue the command:

mysql> use winestore

For the rest of this chapter, we omit the mysql> prompt from the command examples.