How to select database mysql

MySQL Select Database() – A Quick Guide

In this tutorial, you will learn to create a database, select a database, and then use it.

What is Select in MysQL?

Select database(); basically displays the database which is currently in use.

There are multiple databases available in the MySQL server and workbench. So, to work with a particular database, we need to select it. In MySQL for selecting a database, a USE statement is used.

Creating a database in MySQL client

To create a database in Mysql run this command –

Output

Alternatively, you can follow the tutorial for creating mysql Databases here.

Selecting a database in the client

First, we will log into MySQL client by entering the following command-

Then, enter the password for your particular MySQL client-

Now when we run Select database(); it will show the current database which is in use.

Ouptut-

But here it is showing NULL, which means that we have not selected any database. For this, we will now be using USE statement.

Output-

Selecting a database when logging into MySQL client

We can directly select a database to work with at the time of logging into the MySQL client by using the following command-

After logging in into the MySQL client write the following command-

Output-

In the output, the sys database is currently in use.

Selecting a database in MySQL workbench

You can set up a new connection in MySQL workbench by clicking on the plus sign next to MySQL connection. Then a dialog box will pop up. In that, specify the name of the connection in Connection Name: and below in the Default Schema: specify the database which you want to use and then click on OK.

MySQL Connection MySQL connection

Conclusion

In this tutorial, we studied Selecting a database using MySQL client and MySQL workbench.

How do I 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?

7 Answers 7

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

You can also specify the database you want when connecting:

I use this command:

Switch to a database.

Hope this helps.

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

Use the following steps to select the database:

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

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

select data from any table:

Not the answer you’re looking for? Browse other questions tagged mysql or ask your own question.

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Selecting a MySQL Database Using USE Statement

Summary: in this tutorial, you will learn how to select a database in the mysql program and MySQL Workbench by using the USE statement.

Selecting a MySQL database using the mysql client tool

When you log in to a MySQL database server using the mysql client tool without specifying a database name, MySQL server will set the current database to NULL.

First, log in to MySQL using the root user account:

MySQL will prompt you for a password:

It’ll return the following:

It means the current database is not set. If you issue a statement, MySQL will issue an error. For example:

To select a database to work with, you use the USE statement:

For example, the following statement uses the USE statement to set the current database to classicmodels:

If you see the following message, it means that you have changed the database to classicmodels successfully:

To verify it, you can use the select database() statement:

It’ll return something like:

If the classicmodels database doesn’t exist, you’ll get the following error after executing the USE statement:

In this case, you need to find which databases are available on your server by using the show databases statement:

The output may look like the following:

Selecting a database when you login

After entering the password and logging in successfully, you can check the current database:

Selecting a database in MySQL Workbench

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:

Once logged in, you can select another database by issuing the USE statement or use the Set As Default Schema feature provided by MySQL Workbench:

In this tutorial, you have learned various ways to select a MySQL database via the mysql program and MySQL Workbench application.

Chapter 4 Creating and Using a Database

Table of Contents

Once you know how to enter SQL statements, you are ready to access a database.

Suppose that you have several pets in your home (your menagerie) and you would like to keep track of various types of information about them. You can do so by creating tables to hold your data and loading them with the desired information. Then you can answer different sorts of questions about your animals by retrieving data from the tables. This section shows you how to perform the following operations:

Create a database

Load data into the table

Retrieve data from the table in various ways

Use multiple tables

The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations in which a similar type of database might be used. For example, a database like this could be used by a farmer to keep track of livestock, or by a veterinarian to keep track of patient records. A menagerie distribution containing some of the queries and sample data used in the following sections can be obtained from the MySQL website. It is available in both compressed tar file and Zip formats at https://dev.mysql.com/doc/.

Use the SHOW statement to find out what databases currently exist on the server:

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege. See SHOW DATABASES Statement.

If the test database exists, try to access it:

where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host from which you connect to the server.

MySQL Basic Database Administration Commands – Part I

Database is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of 100 pages, if you have to search for all the employees whose salary were less than 10k, just think how much difficult it would have been, then.

In today’s world you just can’t escape Database. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.

MySQL Administration Guide – Part I

Database is oftenly termed as back-end process, as because it is neither visible to end user nor End User interacts directly with the database. They works on front-end process viz., PHP, VB, ASP.NET, etc. and ask the front end to deal with database in back-end.

There are several database server and client available like Oracle, MySQL, MySQLi, MariaDB, MongoDB etc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.

Lets start with simple queries on database. We will be using MySQL which comes bundled with most of the Linux distributions by default, you could install it manually from repository, if it is not installed by default in your case.

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.

Install MySQL Database

Use “yum” or “apt” package manager to install MySQL Database.

Start MySQL

Start MySQL database service as:

Well installing a MySQL database will take you to the configuration where you are asked to setup admin password, etc. Once finished installing and starting the server go to your MySQL prompt.

Replace root with your configured username and enter password when prompted, if the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.

Now carrying out queries at this prompt is very educative and fun.

Create a database tecmint

Note: It reports that the query was correct, means database is created. You can verify your newly created database as.

Note: Notice your database in the above output.

Select Database

Now you need to select the database to work upon it.

Create Tables in MySQL

Here we will be creating a table say “minttec” with three fields as:

Note: The above query says OK which means table was created without any error. To verify the table run the below query.

Things are going fine till now. Yup! You can view the columns you created in the table “minttec” as:

It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.

OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.

Now, verify it in your table.

Add Column in MySQL

Now we will add a column to the right say a column ‘country‘ to the right of email.

Verify the above column insertion query.

Insert Values in Field

What about inserting values to the field?

How about inserting more than 1 value at a time in the above table.

Verify the above insertion.

Delete Values in Field

Let’s say the third entry in the above output is invalid and we need to delete the third entry.

Verify the above operation.

Update Values in Field

The id (=4) needs to be edited.

Verify the above query.

Note: The above query, as performed is not a good idea. It will change the id to ‘4‘ where ever the first name is ‘tecmint’. It is always a good idea to use more than one column with where clause to get minimal error, as:

Delete Column in MySQL

Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.

Verify the table.

Rename Table in MySQL

Don’t you think our table name “minttec” is not very much relevant. How about changing it to tecmint_table.

List all Tables

See all the tables under the current database.

The table has been renamed. Now take a backup of the above MySQL database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.

It is always a good idea to maintain Backup of MySQL databases. Restoring the backed up MySQL Data is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.

But, wait first we will delete the database to verify if our restore is perfect.

Delete a Database

Check for database ‘tecmint’ on your database server.

Great! The database is lost, but we need not to worry, we are having the backup.

Restore a Database

To restore lost database, run the following command.

OOPS! An error, hey we have not create the database tecmint. So go to your mysql prompt and create a database ‘tecmint‘.

Now time to run restore command at your shell prompt (strictly).

Verify your database.

Verify the contents of database.

Verify the contents of your restored table.

This is not the end definitely, we will cover the concept of primary key, foreign key, multiple tables and running queries using simple PHP script in the next part of the article.

Don’t forget to tell us, how you felt while going through the article. Your comments are highly appreciated. Stay Healthy and Tuned, remain connected to Tecmint.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.