mysql creates, selects and uses databases

Suppose you have many pets in your home (your "zoo") and you want to track various types of information about them. You can save your data by creating tables and loading them with the required information. You can then retrieve data from the tables to answer questions about different types of animals. This section shows how to do all these things:

· Create a database

· Create a database table

· Load data into a database table

· Retrieve data from a table in various ways

· Use multiple tables

The zoo database is simple (by design), but it's not hard to imagine it as a real-world situation where a similar database might be used. For example, a farmer could use such a database to track livestock, or a veterinarian could use it to track sick animal records. The zoo distribution containing some queries and sample data that will be used in later chapters is available from the MySQL website. There are tar compression format (http://downloads.mysql.com/docs/menagerie-db.tar.gz) and Zip compression format (http://downloads.mysql.com/docs/menagerie-db.zip).

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES; 
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

Maybe the list of databases on your machine is different, but there is most likely a mysql and test database. mysql is required because it describes user access rights, and the test database is often used as a workspace for users to try their skills.

Please note that without SHOW DATABASES permission, you cannot see all databases.

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 a statement with a semicolon if you like; that's okay.) The USE statement also has another special feature: it must be given on a single line.

You can use the test database in the later examples (if you have access to it), but anything you create in that database can be deleted by others who access it, so you should ask your MySQL administrator for permission to use it yourself of a database. Suppose you want to call your menagerie, the administrator needs to execute such a command:

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 where the connected server is located.

If the administrator created the database for you when setting permissions, you can start using it. Otherwise, you need to create the database yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always access the database as menagerie, not Use Menagerie, MENAGERIE or some other variable. The same goes for table names. (Under Windows, this restriction does not apply, although you must use the same case to reference the database and the table in a given query. However, for several reasons, as a best practice, always use the same case as the database was created with. Same case as .)

Creating a database does not mean selecting it and using it, you have to do it explicitly. To make the menagerie the current database, use this command:

mysql> USE menagerie Database changed

The database only needs to be created once, but it must be selected before use each time you start a mysql session. You can do this by executing a USE statement based on the above example. You can also select the database through the command line when calling mysql. You only need to specify the database name after providing the connection parameters. For example:

shell> mysql -h host -u user -p menagerie Enter password: ********

Note that the menagerie in the command line just shown is not your password. If you want to provide a password on the command line after the -p option, you cannot insert spaces (for example, as in -pmypassword, not -p mypassword). However, it is not recommended to enter the password at the command line, as this will expose the password to other users logged in on the machine.