Introduction to MySQL
This lesson is an extract from MySQL's own documentation and provides a tutorial introduction to MySQL by
showing how to use the mysql client program to create and use a
simple database. mysql (sometimes referred to as the "terminal
monitor'' or just "monitor'') is an interactive program that allows you
to connect to a MySQL server, run queries and view the results.
mysql may also be used in batch mode: you place your queries in
a file beforehand, then tell mysql to execute the contents of the
file. Both ways of using mysql are covered here.
To see a list of options provided by mysql , invoke it with the
--help option:
shell> mysql --help
This chapter assumes that mysql is installed on your machine,
and that a MySQL server is available to which you can connect.
If this is not true, contact your MySQL administrator. (If
you are the administrator, you will need to consult other sections
of this manual.)
The chapter describes the entire process of setting up and using a database.
If you are interested only in accessing an already-existing database, you may
want to skip over the sections that describe how to create the database and
the tables it contains.
Because this chapter is tutorial in nature, many details are necessarily left
out. Consult the relevant sections of the manual for more information on the
topics covered here.
Connecting to and disconnecting from the server
To connect to the server, you'll usually need to provide a MySQL
user name when you invoke mysql and, most likely, a password. If
the server runs on a machine other than the one where you log in, you'll also
need to specify a hostname. Contact your administrator to find out what connection
parameters you should use to connect (i.e., what host, user name and password
to use). Once you know the proper parameters, you should be able to connect
like this:
shell> mysql -h host -u user -p
Enter password: ********
The ******** represents your password; enter it when mysql
displays the Enter password: prompt.
If that works, you should see some introductory information followed by a mysql>
prompt:
shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 459 to server version: 3.22.20a-log
Type 'help' for help.
mysql>
The prompt tells you that mysql is ready for you to enter commands.
Some MySQL installations allow users to connect as the "anonymous''
(unnamed) user to the server running on the local host. If this is the case
on your machine, you should be able to connect to that server by invoking mysql
without any options:
shell> mysql
After you have connected successfully, you can disconnect any time by typing
QUIT at the mysql> prompt:
mysql> QUIT
Bye
You can also disconnect by typing control-D.
Most examples in the following sections assume you are connected to the server.
They indicate this by the mysql> prompt.
Entering queries
Make sure you are connected to the server, as discussed in the previous section.
Doing so will not in itself select any database to work with, but that's okay.
At this point, it's more important to find out a little about how to issue queries
than to jump right in creating tables, loading data into them and retrieving
data from them. This section describes the basic principles of entering commands,
using several queries you can try out to familiarize yourself with how mysql
works.
Here's a simple command that asks the server to tell you its version number
and the current date. Type it in as shown below following the mysql>
prompt and hit the RETURN key:
mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version() | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19 |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>
This query illustrates several things about mysql :
- A command normally consists of a SQL statement followed by a semicolon.
(There are some exceptions where a semicolon is not needed.
QUIT ,
mentioned earlier, is one of them. We'll get to others later.)
- When you issue a command,
mysql sends it to the server for
execution and displays the results, then prints another mysql>
to indicate that it is ready for another command.
mysql displays query output as a table (rows and columns).
The first row contains labels for the columns. The rows following are the
query results. Normally, column labels are the names of the columns you fetch
from database tables. If you're retrieving the value of an expression rather
than a table column (as in the example just shown), mysql labels
the column using the expression itself.
mysql shows how many rows were returned, and how long the query
took to execute, which gives you a rough idea of server performance. These
values are imprecise because they represent wall clock time (not CPU or machine
time), and because they are affected by factors such as server load and network
latency. (For brevity, the "rows in set'' line is not shown in the remaining
examples in this chapter.)
Keywords may be entered in any lettercase. The following queries are equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Here's another query. It demonstrates that you can use mysql as
a simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
| 0.707107 | 25 |
+-------------+---------+
The commands shown thus far have been relatively short, single-line statements.
You can even enter multiple statements on a single line. Just end each one with
a semicolon:
mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| version() |
+--------------+
| 3.22.20a-log |
+--------------+
+---------------------+
| NOW() |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+
A command need not be given all on a single line, so lengthy commands that
require several lines are not a problem. mysql determines where
your statement ends by looking for the terminating semicolon, not by looking
for the end of the input line. (In other words, mysql accepts free-format
input: it collects input lines but does not execute them until it sees the semicolon.)
Here's a simple multiple-line statement:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
In this example, notice how the prompt changes from mysql>
to -> after you enter the first line of a multiple-line query.
This is how mysql indicates that it hasn't seen a complete statement
and is waiting for the rest. The prompt is your friend, because it provides
valuable feedback. If you use that feedback, you will always be aware of what
mysql is waiting for.
If you decide you don't want to execute a command that you are in the process
of entering, cancel it by typing \c :
mysql> SELECT
-> USER()
-> \c
mysql>
Here, too, notice the prompt. It switches back to mysql> after
you type \c , providing feedback to indicate that mysql
is ready for a new command.
The following table shows each of the prompts you may see and summarizes what
they mean about the state that mysql is in:
Prompt
|
Meaning
|
mysql>
|
Ready for new command
|
->
|
Waiting for next line of multiple-line command
|
'>
|
Waiting for next line, collecting a string that begins with a single
quote (`'')
|
">
|
Waiting for next line, collecting a string that begins with a double
quote (`"')
|
Multiple-line statements commonly occur "by accident'' when you intend
to issue a command on a single line, but forget the terminating semicolon. In
this case, mysql waits for more input:
mysql> SELECT USER()
->
If this happens to you (you think you've entered a statement but the only response
is a -> prompt), most likely mysql is waiting for
the semicolon. If you don't notice what the prompt is telling you, you might
sit there for a while before realizing what you need to do. Enter a semicolon
to complete the statement, and mysql will execute it:
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
The '> and "> prompts occur during string collection.
In MySQL, you can write strings surrounded by either `''
or `"' characters (for example, 'hello' or "goodbye" ),
and mysql lets you enter strings that span multiple lines. When
you see a '> or "> prompt, it means that you've
entered a line containing a string that begins with a `'' or `"'
quote character, but have not yet entered the matching quote that terminates
the string. That's fine if you really are entering a multiple-line string, but
how likely is that? Not very. More often, the '> and ">
prompts indicate that you've inadvertantly left out a quote character. For example:
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
">
If you enter this SELECT statement, then hit RETURN and wait for
the result, nothing will happen. Instead of wondering, "why does this query
take so long?,'' notice the clue provided by the "> prompt.
It tells you that mysql expects to see the rest of an unterminated
string. (Do you see the error in the statement? The string "Smith
is missing the second quote.)
At this point, what do you do? The simplest thing is to cancel the command.
However, you cannot just type \c in this case, because mysql
interprets it as part of the string that it is collecting! Instead, enter the
closing quote character (so mysql knows you've finished the string),
then type \c :
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
"> "\c
mysql>
The prompt changes back to mysql> , indicating that mysql
is ready for a new command.
It's important to know what the '> and "> prompts
signify, because if you mistakenly enter an unterminated string, any further
lines you type will appear to be ignored by mysql -- including
a line containing QUIT ! This can be quite confusing, especially
if you don't know that you need to supply the terminating quote before you can
cancel the current command.
Extracted from the MySQL
Documentation
|