The SQL statement
USE may be employed to specify a schema as the default one for the current connection to the NuoSQL client.
The name of the schema to set as default.
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.,
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.
Suppose you have a database which contains a few schemas, one of which is called
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
To do this, you need only execute the
USE statement and specify the schema name,
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.,
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.