USE

The SQL statement USE may be employed to specify a schema as the default one for the current connection to the NuoSQL client.

Syntax

USE schema_name

schema_name: The name of the schema to set as default.

Description

A NuoDB database may have more than one schema, each containing tables, views, indexes, etc. When executing an SQL statement, you might specify a table within a schema by naming the schema and then the table, separated by a dot (e.g., bookstore.books). However, if you intend to execute several SQL statements involving tables and other objects within a schema, you can first execute the USE statement to set the default schema. Then you need only specify the table names in each SQL that follows. To execute an SQL statement on another schema, you would either execute the USE statement again for that schema, or utilize the emphatic schema.table method within the SQL statements.

Please note that when executing the USE statement, if the schema doesn’t exist, the SQL statement will still accept the value given for the schema on the assumption that it is correct and that you will be creating objects for it soon. If you then create an object such as a table, before ending the session or before executing the USE statement again for another schema, the CREATE SCHEMA statement will be executed behind the scenes for you.

Examples

Suppose you have a database which contains a few schemas, one of which is called sports. It contains tables with information related to sports teams. Suppose further that you are about to execute several SQL statements to insert and select data from this schema. Therefore, you decide to make sports the default schema. By this method, the table names won’t have to be qualified by the schema_name.

To do this, you need only execute the USE statement and specify the schema name, sports. After that, the sports schema will be assumed for each SQL statement in which you don’t include a schema name as a prefix of the table name (e.g., sports.baseball).

If you want to create a schema in preparation for creating tables and other objects, but are not yet ready to create them, you should use the CREATE SCHEMA statement.

USE sports;

SELECT * FROM hockey
WHERE number > 40;

 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------
  8    64    LANE MACDERMID  Forward  Bruins
  9    63    BRAD MARCHAND   Forward  Bruins
 12    91    MARC SAVARD     Forward  Bruins

 SELECT title, author FROM bookstore.books
 WHERE author LIKE '%Greene'
 LIMIT 2;

       TITLE          AUTHOR
 ----------------- -------------

 The Comedians     Graham Greene
 End of the Affair Graham Greene

Consider another situation: Suppose you execute the USE statement and specify a schema that doesn’t exist. You won’t receive an error message and a new schema won’t be created yet. However, if you create a table or some other object while this unknown schema is set to the default one, the system will then create a schema to hold the objects.

USE birdwatchers;

SHOW SCHEMAS
    Found 4 schemas
        BOOKSTORE
        SPORTS
        SYSTEM
        USER

CREATE TABLE birds
(bird_species STRING, common_name STRING);

SHOW SCHEMAS;
    Found 5 schemas
        BIRDWATCHERS (current)
        BOOKSTORE
        SPORTS
        SYSTEM
        USER

Notice in the examples here, no error message was returned by specifying the birdwatchers schema, even though it didn’t exist. Notice also that the birdwatchers schema didn’t show in the results of SHOW SCHEMAS statement until a table was created.