Selecting a Database

1.11.1 Problem

You want to tell mysql which database to use.

1.11.2 Solution

Name the database on the mysql command line or issue a USE statement from within mysql.

1.11.3 Discussion

When you issue a query that refers to a table (as most queries do), you need to indicate which database the table is part of. One way to do so is to use a fully qualified table reference that begins with the database name. (For example, cookbook.limbs refers to the limbs table in the cookbook database.) As a convenience, MySQL also allows you to select a default (current) database so that you can refer to its tables without explicitly specifying the database name each time. You can specify the database on the command line when you start mysql:

% mysql cookbook

If you provide options on the command line such as connection parameters when you run mysql, they should precede the database name:

% mysql -h  host  -p -u  user  cookbook 

If you've already started a mysql session, you can select a database (or switch to a different one) by issuing a USE statement:

mysql> USE cookbook;
Database changed

If you've forgotten or are not sure which database is the current one (which can happen easily if you're using multiple databases and switching between them several times during the course of a mysql session), use the following statement:

mysql> SELECT DATABASE( );
+------------+
| DATABASE( ) |
+------------+
| cookbook |
+------------+

DATABASE( ) is a function that returns the name of the current database. If no database has been selected yet, the function returns an empty string:

mysql> SELECT DATABASE( );
+------------+
| DATABASE( ) |
+------------+
| |
+------------+

The STATUS command (and its synonym, s) also display the current database name, in additional to several other pieces of information:

mysql> s
--------------
Connection id: 5589
Current database: cookbook
Current user: cbuser@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.51-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 9 days 39 min 43 sec

Threads: 4 Questions: 42265 Slow queries: 0 Opens: 82 Flush tables: 1
Open tables: 52 Queries per second avg: 0.054
--------------

Temporarily Using a Table from Another Database

To use a table from another database temporarily, you can switch to that database and then switch back when you're done using the table. However, you can also use the table without switching databases by referring to the table using its fully qualified name. For example, to use the table other_tbl in another database other_db, you can refer to it as other_db.other_tbl.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net