MySQL Server : Open Source Database : Create Database

• Create Database
• MySQL Utility Statement
• Example for CREATE DATABASE
• MYSQLADMIN command for creating DATABASE

It helps to create a database with the given name. To use this statement, you need the CREATE privilege for the database.
CREATE SCHEMA is a synonym for CREATE DATABASE.
The CREATE DATABASE statement creates only a directory under the MySQL data directory and the db.opt file.

You can also use the mysqladmin program to create databases.

Syntax for CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

• DESCRIBE
• EXPLAIN
• HELP
• USE

DESCRIBE

DESCRIBE provides information about the columns in a table. It is a shortcut for SHOW COLUMNS FROM. As of MySQL 5.0.1, these statements also display information for views.

EXPLAIN

The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MySQL executes a SELECT statement:

EXPLAIN tbl_name
	Or: 
	EXPLAIN [EXTENDED] SELECT select_options

HELP ‘search_string’

• The HELP statement returns online information from the MySQL Reference manual.
• The HELP statement searches the help tables for the given search string and displays the result of the search. The search string is not case sensitive.
• The HELP statement understands several types of search strings:
• At the most general level, use contents to retrieve a list of the top-level help categories:

HELP ‘contents’

For a list of topics in a given help category, such as Data Types, use the category name:

HELP ‘data types’

For help on a specific help topic, such as the ASCII() function or the CREATE TABLE statement, use the associated keyword or keywords:

HELP ‘ascii’

HELP ‘create table’

USE db_name

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued:

mysql> USE test /* here test database will become current database*/
Database changed
mysql>

CREATE DATABASE on MySQL Server

mysql> CREATE DATABASE javaskool_inventory; /* helps to create database */
Query OK, 1 row affected (0.09 sec)

mysql> USE javaskool_inventory;    /* helps to make current database */
Database changed
mysql> DROP DATABASE javaskool_inventory; /* helps to drop database */
Query OK, 0 rows affected (1.14 sec)

mysql>

How to display CREATE DATABASE create_specification

mysql> CREATE DATABASE javaskool_inventory
    -> DEFAULT CHARACTER SET latin1
    -> DEFAULT COLLATE latin1_bin;
Query OK, 1 row affected (0.04 sec)

mysql> SHOW CREATE DATABASE javaskool_inventory;
+---------------------+---------------------------------------------------------
------------------------------------------+
| Database            | Create Database
                                          |
+---------------------+---------------------------------------------------------
------------------------------------------+
| javaskool_inventory | CREATE DATABASE `javaskool_inventory` /*!40100 DEFAULT C
HARACTER SET latin1 COLLATE latin1_bin */ |
+---------------------+---------------------------------------------------------
------------------------------------------+
1 row in set (0.00 sec)

mysql>

How to list all DATABASEs created on MySQL server

mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.62 sec)

mysql>

Syntax

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

Creating Database using mysqladmin command

c:\Program Files (x86)\MySQL5.5\bin>mysqladmin -u root -p create <b>javaskool_inventory</b>
Enter password: *****

c:\Program Files (x86)\MySQL5.5\bin>

Changing ROOT password

c:\Program Files (x86)\MySQL5.5\bin>mysqladmin -u root -padmin  password admin123

c:\Program Files (x86)\MySQL5.5\bin>

Here admin is old password and admin123 is new password.

Checking whether mysql server is running

c:\Program Files (x86)\MySQL5.5\bin>mysqladmin -u root -p ping
Enter password: *****
mysqld is alive

c:\Program Files (x86)\MySQL5.5\bin>

Shutting down mysql server

c:\Program Files (x86)\MySQL5.5\bin>mysqladmin -u root -p shutdown
Enter password: *****

c:\Program Files (x86)\MySQL5.5\bin>

Checking whether mysql server is running

c:\Program Files (x86)\MySQL5.5\bin>mysqladmin -u root -p version
Enter password: *****
mysqladmin  Ver 8.42 Distrib 5.5.25, for Win64 on x86
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.25
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 1 day 5 hours 40 min 29 sec

Threads: 4  Questions: 33  Slow queries: 0  Opens: 36  Flush tables: 1  Open tab
les: 1  Queries per second avg: 0.000

c:\Program Files (x86)\MySQL5.5\bin>