X path

Manual MySql & XML



















Preparado por :Peter Santis
Info2000 consultores










































Creating and Using a Database

Once you know how to enter commands, you are ready to access a database.
Supposethat you have several pets in your home (your menagerie) and you would like to keep track of various types of information
about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer
different sorts of questions about your animals by retrieving data from the tables. This section shows you how to:

• Create a database

• Createa table

• Load data into the table

• Retrieve data from the table in various ways

• Use multiple tables

The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations in which a similar type of
database might be used. For example, a database like this could be used by a farmer to keep track of livestock, or by a veterinarian
to keep trackof patient records. A menagerie distribution containing some of the queries and sample data used in the following sections
can be obtained from the MySQL Web site. It is available in both compressed tar file and Zip formats at http://
dev.mysql.com/doc/.
Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;

+----------+
| Database |+----------+
| mysql |
| test |
| tmp |
+----------+

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things
out.
The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases
that you have no privileges for if you do not have the SHOW DATABASES privilege. SeeSection 12.5.6.15, “SHOW

DATABASES
Syntax”.
If the test database exists, try to access it:
mysql> USE test
Database changed
Note that USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does
no harm.) The USE statement is special in another way, too: it must be given on a single line.
You can use the test database (if you haveaccess to it) for the examples that follow, but anything you create in that database can
be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to
Tutorial

use a database of your own. Suppose that you want to call yours menagerie. The administrator needs to execute a command like
this:
mysql> GRANT ALL ON menagerie.* TO'your_mysql_name'@'your_client_host';
where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host from which you
connect to the server.

Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to
create it yourself:
mysql> CREATE DATABASE menagerie;Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie,
not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does
not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety
of reasons,the recommended best practice is always to use the same lettercase that was used when the database was created.)

Note

If you get an error such as ERROR 1044 (42000): ACCESS DENIED FOR USER 'MONTY'@'LOCALHOST' TO
DATABASE 'MENAGERIE' when attempting to create a database, this means that your user account does not have the
necessary privileges to do so. Discuss this with the administrator...