Creating Database

using mysql binary:

User should have Create_priv to create database in MySQL. Generally when creating your first database, you will use the root account, unless you already have created a user with the specified privilege.

Here is how we will create our first database

We use CREATE DATABASE statement to create a database as shown below

To check if our statement was successful and our database was created, we will use SHOW DATABASE command.

Now let's check how we can do the exact same thing from a PHP script

using php script:

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
    $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
    // PDO can throw exceptions rather than Fatal errors, so let's change the error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE testDB";
    // We can execute the above statement using exec() method from the connection class
    $connection->exec($sql);
    echo "testDB was created successfully";
    }
    catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Dropping Database

Sometimes there is a absolute need to drop a database, perhaps you didn't like the name of it or you don't use it anymore and want to save some disk space. Whatever be the reason, let's check how to drop a database

Drop Database using mysql binary:

User should have Drop_priv to Drop database in MySQL.

Here is how we dropped one of our test database

The syntax for Dropping the database is

DROP DATABASE database_name;

Please remember that dropping cannot be undone. Take precautions when using this statement.

Now that we have seen how to Create, Drop  a database, if you would like to work with it from MySQL client, you would have to select it. Using PDO, database is automatically selected, however with some connectors, you have to specify which database you wish to work with.

Selecting database

We use a USE database_name statement to select the database we wish to work with as shown below

In the next part of this tutorial we will learn about various supported datatypes  in MySQL