{"id":7384,"date":"2018-09-02T18:56:46","date_gmt":"2018-09-03T01:56:46","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7384"},"modified":"2021-07-25T23:43:06","modified_gmt":"2021-07-26T06:43:06","slug":"selecting-a-mysql-database-using-use-statement","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/selecting-a-mysql-database-using-use-statement\/","title":{"rendered":"Selecting a MySQL Database Using USE Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to select a database in the\u00a0mysql<\/code> program and MySQL Workbench by using the USE<\/code> statement.<\/p>\n\n\n\n

Selecting a MySQL database using the mysql client tool<\/h2>\n\n\n\n

When you log in to a MySQL database server using the mysql<\/code> client tool without specifying a database name, MySQL server will set the current database to NULL.<\/p>\n\n\n\n

First, log in to MySQL using the root<\/code> user account:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL will prompt you for a password:<\/p>\n\n\n
Enter password: <\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
To log in, you need to provide the correct password of the root<\/code> user account and press Enter<\/code>. To display the current database, you use the following statement:<\/p>\n\n\n
SELECT<\/span> database<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll return the following:<\/p>\n\n\n
+------------+\r\n| database() |\r\n+------------+\r\n| NULL       |\r\n+------------+\r\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
It means the current database is not set. If you issue a statement, MySQL will issue an error. For example:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
ERROR 1046 (3D000): No database selected<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
To select a database to work with, you use the USE<\/code> statement:<\/p>\n\n\n
USE<\/span> database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement uses the USE statement to set the current database to classicmodels:<\/p>\n\n\n
USE<\/span> classicmodels;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you see the following message, it means that you have changed the database to classicmodels<\/code> successfully:<\/p>\n\n\n
Database changed<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
To verify it, you can use the select database() statement:<\/p>\n\n\n
SELECT<\/span> database<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll return something like:<\/p>\n\n\n
+---------------+\r\n| database()    |\r\n+---------------+\r\n| classicmodels |\r\n+---------------+\r\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
If the classicmodels database doesn’t exist, you’ll get the following error after executing the USE<\/code> statement:<\/p>\n\n\n
ERROR 1049 (42000): Unknown database 'classicmodels'<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, you need to find which databases are available on your server by using the show databases statement:<\/p>\n\n\n
SHOW DATABASES;<\/code><\/span><\/pre>\n\n\n
The output may look like the following:<\/p>\n\n\n
+--------------------+\r\n| Database           |\r\n+--------------------+\r\n| information_schema |\r\n| mysql              |\r\n| performance_schema |\r\n| sys                |\r\n+--------------------+\r\n4 rows in set (0.02 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Selecting a database when you login<\/h2>\n\n\n\n
If you know which database you want to work with before you log in, you can use the -D<\/code> flag. For example, the following command connects to the classicmodels<\/code> database with the user account root<\/code>:<\/p>\n\n\n
mysql -u root -D classicmodels -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this command, we specify the database classicmodels<\/code> after the -D<\/code> flag. <\/p>\n\n\n\n
After entering the password and logging in successfully, you can check the current database:<\/p>\n\n\n
SELECT<\/span> database<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------+\n| database()    |\n+---------------+\n| classicmodels |\n+---------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Selecting a database in MySQL Workbench<\/h2>\n\n\n\n
If you connect to a MySQL Server via the MySQL Workbench application, you can select a database when you create the database connection as shown in the following screenshot:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Once logged in, you can select another database by issuing the USE<\/code> statement or use the Set As Default Schema<\/code> feature provided by MySQL Workbench:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned various ways to select a MySQL database via the mysql<\/code> program and MySQL Workbench application.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t