Creating and using database in mysql

In order to know a list of mysql database in the system issue following command where we are connecting as administrative root user with password test and into host localhost. Here we are working on console, not into mysql prompt.

D:\xampp\mysql\bin>mysql -h localhost -u root -ptest  -e "show databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arju               |
| email_master_list  |
| magento            |
| mysql              |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+

We could do the same job into mysql prompt as,

D:\xampp\mysql\bin>mysql -h localhost -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arju               |
| email_master_list  |
| magento            |
| mysql              |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
8 rows in set (0.02 sec)

Note that you must login as administrative privilege to see all databases. If you don't do that you may be see all the databases. Like below.

D:\xampp\mysql\bin>mysql -h localhost -sN -e "show databases;"
information_schema
test

Only two databases are shown here.

Now let's try to create a new database named test2. Only administrative privileged user can create the database. So if you don't login as administrative user and issuing create database command the following error will happen.

D:\xampp\mysql\bin>mysql -h localhost -e "create database test2;"
ERROR 1044 (42000) at line 1: Access denied for user ''@'localhost' to database 'test2'

From console try to create database test2 by,
D:\xampp\mysql\bin>mysql -h localhost -u root -ptest -e "create database test2;"

Notice that with password should be specified as -pPassword, i.e adjacent with -p. There is no space between and p and password.

Now check the existing database by,

D:\xampp\mysql\bin>mysql -h localhost  -u root -ptest -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arju               |
| email_master_list  |
| magento            |
| mysql              |
| phpmyadmin         |
| test               |
| test2              |
| webauth            |
+--------------------+

In the mysql prompt you can create mysql database by,

D:\xampp\mysql\bin>mysql -h localhost  -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test3;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| arju               |
| email_master_list  |
| magento            |
| mysql              |
| phpmyadmin         |
| test               |
| test2              |
| test3              |
| webauth            |
+--------------------+
10 rows in set (0.00 sec)

In order to connect to a database issue connect database_name or use database_name statement. Ensure the database name by command select database() as shown below.

mysql> connect test3;
Connection id:    17
Current database: test3

mysql> select database();
+------------+
| database() |
+------------+
| test3      |
+------------+
1 row in set (0.00 sec)

mysql> use test2;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| test2      |
+------------+
1 row in set (0.00 sec)

Related Documents
http://arjudba.blogspot.com/2009/05/issuing-mysql-commands.html
http://arjudba.blogspot.com/2009/05/connecting-to-and-disconnecting-from.html