question about show databases;

Hi everyone. I just installed MySQL on my home PC and I tested out the
show databases query, and it lists two:

information_schema
bbdatabank

The second one is one I created myself, but I don't understand what the
first one is. It is not listed in my 'data' folder, but mysql and test
*are*, yet they aren't listed when I type the show command. Why is that?
How can I get them listed so that I can use them, and what is the
information_schema?

Thanks!
John

Re: question about show databases;

>Hi everyone. I just installed MySQL on my home PC and I tested out the
>show databases query, and it lists two:
>
>information_schema
>bbdatabank
>
>The second one is one I created myself, but I don't understand what the
>first one is. It is not listed in my 'data' folder, but mysql and test
>*are*, yet they aren't listed when I type the show command. Why is that?

Unless you have the "show databases" privilege, you can only see
databases you have some privileges on.

>How can I get them listed so that I can use them, and what is the
>information_schema?

Use a username and a password with privileges to use them (e.g. the
root account set up when you installed MySQL).

information_schema is a database giving information on the databases
and stuff in them. Another way of getting a list of databases is
"select schema_name from information_schema.schemata". It is
automatically generated from the databases, so if you create or
delete tables or columns in them, it's automatically up to date.
On the other hand, it tends to be a bit slow if you list a whole
table.

Some of the information in information_schema is sensitive to privileges.
You may not see the rows for things you don't have access to.

Gordon L. Burditt

Re: question about show databases;

Gordon Burditt wrote:

>
> Use a username and a password with privileges to use them (e.g. the
> root account set up when you installed MySQL).
>
> information_schema is a database giving information on the databases
> and stuff in them. Another way of getting a list of databases is
> "select schema_name from information_schema.schemata". It is
> automatically generated from the databases, so if you create or
> delete tables or columns in them, it's automatically up to date.
> On the other hand, it tends to be a bit slow if you list a whole
> table.

Thank you! I realized that I wasn't seeing mysql or test because I
wasn't signed in as the root account. As for the other question, thanks
for explaining. I'll just leave it alone! :)

Re: question about show databases;

>> information_schema is a database giving information on the databases
>> and stuff in them. Another way of getting a list of databases is
>> "select schema_name from information_schema.schemata". It is
>> automatically generated from the databases, so if you create or
>> delete tables or columns in them, it's automatically up to date.
>> On the other hand, it tends to be a bit slow if you list a whole
>> table.
>
>Thank you! I realized that I wasn't seeing mysql or test because I
>wasn't signed in as the root account. As for the other question, thanks
>for explaining. I'll just leave it alone! :)

information_schema does have its uses. For example, it's easy to
list all the tables with a 'stamp' column in them. And information_schema
is a bit less MySQL-specific than various "SHOW" commands, and you
can put a WHERE clause on the queries.

It *is* a really good idea to leave information_schema alone when
using mysqldump or restoring the output of mysqldump.

Gordon L. Burditt