Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

5.2 MySQL Command Interpreter

The


MySQL command interpreter is
commonly used to create databases and tables in web database
applications and to test queries. Throughout the remainder of this
chapter we discuss the SQL statements for managing a database. All
these statements can be directly entered into the command interpreter
and executed. In later chapters, we'll show how to
include SQL statements in PHP scripts so that web applications can
get and change data in a database.

Once the MySQL server is running, the command interpreter can be
used. The command interpreter can be run using the following command
from the shell in a Unix or Mac OS X system, assuming
you've created a user hugh with a
password shhh:

% /usr/local/bin/mysql -uhugh -pshhh

The shell prompt is represented here as a percentage character,
%.

On a Microsoft Windows platform, you can access the command
interpreter by clicking on the Start menu, then the Run option, and
typing into the dialog box:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -uhugh -pshhh

Then, press the Enter key or click OK.

(For both Unix and Microsoft Windows environments,
we're assuming you've installed
MySQL in the default directory location using our instructions in
Appendix A through Appendix C.)

Running the command interpreter displays the output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.15-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

The command interpreter displays a mysql>
prompt and, after executing any command or statement, it redisplays
the prompt. For example, you might issue the statement:

mysql> SELECT NOW( );

This statement reports the time and date in the following output:

+---------------------+
| NOW( )               |
+---------------------+
| 2004-03-01 13:48:07 |
+---------------------+
1 row in set (0.00 sec)
mysql>

After running a statement, the interpreter redisplays the
mysql> prompt. We discuss the
SELECT statement later in this chapter.

As with all other SQL statements, the SELECT
statement ends in a semicolon. Almost all SQL command interpreters
permit any amount of whitespace (spaces, tabs, or carriage returns)
in SQL statements, and they check syntax and execute statements only
after encountering a semicolon that is followed by a press of the
Enter key.

We have used uppercase for the SQL statements throughout this book so
that it's clear what's an SQL
statement and what isn't. However, any mix of upper-
and lowercase is equivalent in SQL keywords. Be careful, though:
other parts of SQL statements such as database and table names are
case sensitive. You also need to be careful with values: for example,
Smith, SMITH, and
smith are all different.

On startup, the command interpreter encourages the use of the
help command. Typing help
produces a list of commands that are native to the MySQL interpreter
and that aren't part of SQL. All non-SQL commands
can be entered without the terminating semicolon, but the semicolon
can be included without causing an error.

The MySQL command interpreter provides a lot of flexibility and many
shortcuts:

To quit the interpreter, type quit.

The up- and down-arrow keys allow you to browse previously entered
commands and statements. On most platforms, the history of commands
and statements is kept when you quit the interpreter. When you run it
again, you can once again scroll up using the up arrow and execute
commands and statements that were entered in the previous session.

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.

If you're a Unix user, you can use a text editor to
create SQL statements by entering the command edit
in the interpreter. This invokes the editor defined by the
EDITOR shell environment variable. After you exit
the editor, the MySQL command interpreter reads, parses, and runs the
file created in the editor.

You can run single commands and SQL statements without waiting for a
MySQL command prompt. This is particularly useful for adding SQL
statements to startup scripts. For example, to run SELECT
now( ) from a Unix shell, enter the following command:

% /usr/local/mysql/bin/mysql -uhugh -pshhh -e "SELECT now( );"

You can create MySQL statements in a file using a text editor, and
then load and run them. For example, if you have statements stored in
the file statements.sql, type the following into
the command interpreter to load and run the statements:

mysql> source statements.sql

You can also include a directory path before the filename. This
feature is discussed in more detail in Chapter 15.

Sometimes, you'll find you've
mistyped a statement, forgotten a semicolon, or forgotten a quote
character. In most cases, to solve the problem you can type a
semicolon and press Enter: this causes MySQL to report an error and
you can then start again. If you're missing a
matching quote character, type it in, then a semicolon, and then
press Enter. If you're in a real mess, type
Control-C (by holding the Ctrl key and pressing C): this aborts the
command interpreter completely.