Create & Drop Database MySQL Using Command Line & PHP

In this tutorial we are going to show you how to create MySQL database and how to delete/drop MySQL database using the command line interface and PHP Script. We will take two example of MySQL create and drop databases.

Table of Contents

  • Create & Drop MySQL Database Using CMD
  • Using PHP Script For Create & Drop MySQL Database

Create & Drop MySQL Database Using CMD

Create MySQL Users & Database

Now you will learn, To create MySQL database and users. Just follow the few steps :-

LogIn As Root User

On the command line, log in to MySQL as root user

mysql -u root -p

Next step, type mysql root password, and then press Enter.

Create User

To create a database user, type the following command. Replace username with your username, and replace password with your password :-

GRANT ALL PRIVILEGES ON . TO 'username'@'localhost' IDENTIFIED BY 'password';

After created a new user just type \q to exit the mysql program.

LogIn With New Created User

To log into MySQL as a user you just created, type the following command. Replace the user name you created in step 3 with the name of :-

mysql -u username -p

Next step, type user’s password, and then press Enter.

Create Database

To create a database, use the below following command. Replace dbname with the name of the database you want to create :-

CREATE DATABASE dbname;

Use Databse

To work with new databases, use the following command. Replace Dbname with the name of the database that you created in the preview phase: –

USE dbname;

Create Table and Insert Data

Now you can work with new databases. For example, the following commands demonstrate how to create a base table for example, and how to insert some data in it :-

CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Test data' );

Drop MySQL Database

Now you can use the following command for deleting MySQL database from the mysql> prompt. Replace database name with your database name :-

DROP DATABASE dbname;

Drop MySQL Table

Now you can use the following command for deleting MySQL table from datbase from the mysql> prompt. Replace table name with your database table name :-

DROP TABLE  tablename;

Using PHP Script For Create & Drop MySQL Database

There are two methods available for create or drop MySQL database.

  • First is MySQLi
  • Second one is PDO

Create Database

Syntax :-

CREATE DATABASE database_name

The CREATE DATABASE statement is used to create a database in MySQL.

Example of CREATE DATABASE using MySQLi

In this MySQL CREATE DATABASE examples create a new database named “testDB”. This example is (MySQLi Object-oriented)

<?php
$host= "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($host, $username, $password);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// Create database
$sql = "CREATE DATABASE testDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Example of (MySQLi Procedural)

In this MySQL CREATE DATABASE examples create a new database named “testDB”. This example is (MySQLi Procedural)

<?php
$host= "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($host, $username, $password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Create database
$sql = "CREATE DATABASE testDB";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example CREATE DATABASE using PDO

<?php
$host= "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$host", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE testDBPDO";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Database created successfully<br>";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

DROP Database

The DROP DATABASE statement delete all tables in the database and deletes the database permanently.

Syntax :-

DROP DATABASE database_name

The DROP DATABASE statement is used to drop a database in MySQL.

Example of DROP DATABASE using (MySQLi Procedural)

<?php
    $host = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $conn = mysqli_connect($host, $username, $password);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'DROP DATABASE testDB';
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not delete database testDB: ' . mysql_error());
   }
   
   echo "Database deleted successfully\n";
   
   mysql_close($conn);
?>
Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *