Is it important to write database name before table name?

Question

I have a database named DELTASTORE in mysql in my cpanel. There are tables like ADMIN,CATAGORY,PRODUCT,ORDER. I have inserted some values in each table.
If I run sql

SELECT * FROM ADMIN

it works nicely.
But if I run sql

SELECT * FROM ORDER

it doesn't work! Instead of that, if I run sql

SELECT * FROM DELTASTORE.ORDER

then it works correctly.
Why does that occur?
Is it important to write database name before table name and give a dot between them all the time?

Answer 1

To leave out the database prefix, you have to set a default database, with

USE databasename

When writing programs to access the database, the API provides a way to do this. For instance, in PHP PDO you specify the default database in the DSN:

mysql:host=hostname;dbname=defaultDB

In MySQLi it's an argument to mysqli_connect(). In the obsolete mysql extension you use mysql_use_database(). There are similar methods in other programming languages.

Additionally, since ORDER is a MySQL keyword, you either have to put it in backticks:

SELECT * FROM `ORDER`

or prefix it with a database:

SELECT * FROM DELTASTORE.ORDER

It's usually best to avoid using MySQL reserved words as table or column names, to prevent problems like this. See Syntax error due to using a reserved word as a table or column name in MySQL