How to select a MySQL database through CLI?

Question

I've managed to get into MySQL using the command line terminal, but when I tried to enter some SQL, it said 'no database selected'

how do I select a database? my database name is: photogallery

What code do I use to select it?

Answer 1

For example, you can select apple database with login as shown below:

mysql -u john -p apple

Or:

mysql -u john -p apple

Or:

mysql -u john -p -D apple

Or:

mysql -u john -p --database=apple

Or:

mysql -u john -p --database apple

And, you can select apple database after login as shown below:

mysql> use apple

Or:

mysql> USE apple

Or:

mysql> \u apple

Or:

mysql> connect apple

Or:

mysql> CONNECT apple

Or:

mysql> \r apple

Be careful, you cannot select apple database with \U because there is the error as shown below:

mysql> \U apple

ERROR: Unknown command '\U'.

And, you cannot select apple database with \R because \R(prompt) is used to change MySQL prompt as shown below. *My answer exlains how to change the default prompt mysql> :

mysql> \R apple
PROMPT set to 'apple'
apple

Or on Windows, you can set the database under [mysql] in my.ini as shown below. *My answer explains [mysql] and my answer explains where my.ini is located on Windows:

# "my.ini"

[mysql]
...
database='apple'

Then, you can select apple database with login by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below. *--defaults-file= or --defaults-extra-file= must be the 1st option otherwise there is the error:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p

Answer 2

Use USE. This will enable you to select the database.

USE photogallery;

12.8.4: USE Syntax

You can also specify the database you want when connecting:

$ mysql -u user -p photogallery

Answer 3

Hope this helps.

use [YOUR_DB_NAME];

Answer 4

Switch to a database.

mysql> use [db name];

MySQL Commands

Answer 5

Use the following steps to select the database:

mysql -u username -p

it will prompt for password, Please enter password. Now list all the databases

show databases;

select the database which you want to select using the command:

use databaseName;

select data from any table:

select * from tableName limit 10;

You can select your database using the command use photogallery; Thanks !

Answer 6

Alternatively, you can give the "full location" to the database in your queries a la:

SELECT photo_id FROM [my database name].photogallery;

If using one more often than others, use USE. Even if you do, you can still use the database.table syntax.

Answer 7

USE database_name;

eg. if your database's name is gregs_list, then it will be like this >>

USE gregs_list;

Answer 8

While invoking the mysql CLI, you can specify the database name through the -D option. From mysql --help:

-D, --database=name Database to use.

I use this command:

mysql -h <db_host> -u <user> -D <db_name> -p