CODEWITHSUNDEEP
mysqldatabasemysql-climysqlcommandmysql-command-line-client
Leahcim
Leahcim

Reputation: 42049

How to select a MySQL database through CLI?

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?

Upvotes: 137

Views: 252919

Answers (8)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 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

Upvotes: 4

saonnet
saonnet

Reputation: 110

USE database_name;

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

USE gregs_list;

Upvotes: 3

codeforester
codeforester

Reputation: 43089

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

Upvotes: 44

Paul Truesdell
Paul Truesdell

Reputation: 329

Hope this helps.

use [YOUR_DB_NAME];

Upvotes: 15

Andrew Moore
Andrew Moore

Reputation: 95444

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

Upvotes: 197

Lalit Dashora
Lalit Dashora

Reputation: 485

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 !

Upvotes: 9

Nick T
Nick T

Reputation: 26767

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.

Upvotes: 13

skaz
skaz

Reputation: 22640

Switch to a database.

mysql> use [db name];

MySQL Commands

Upvotes: 26

Related Questions