MySQL Database Commands

MySQL has four database commands as show, create, drop and use. You can execute these commands in command-line and in PHP scripts. However since many PHP applications use only one database, it’s rare to find these commands in PHP scripts.

Show Databases

Login to MySQL in command-line as the root user or any other privileged user and enter following command in MySQL prompt. You will be able to see a list of databases allowed for logged in user.

show databases;

You can try this command in PHP as below. Replace mysqli_connect() details with the details of a MySQL user in your computer.

<?php

$link = mysqli_connect('localhost', 'robin', 'robin123');

$result = mysqli_query($link, 'show databases');

while ($row = mysqli_fetch_array($result)) {

    echo $row[0] . '<br />';

}

?>

Create Database

To create a new database, log in as a user who has privileges to create databases and use following command. Type the database name you want in place of my_database.

create database my_database;

In PHP, you can create a database as below.

<?php

$link = mysqli_connect('localhost', 'root', 'root123');

$result = mysqli_query($link, 'create database my_database');

if ($result) {
    echo 'Database was created successfully';
} else {
    echo mysqli_error($link);
}

?>

Drop Database

Use below command to delete a database. Replace my_database with the name of the database you want to delete.

drop database my_database;

You can achieve the same result in PHP by passing the command to mysqli_query() as below.

<?php

$link = mysqli_connect('localhost', 'root', 'root123');

$result = mysqli_query($link, 'drop database my_database');

if ($result) {
    echo 'Database was dropped successfully';
} else {
    echo mysqli_error($link);
}

?>

Use Database

In MySQL, before executing any table level commands, you need to choose a database. You can select the database my_database to work on using following command.

use my_database;

In PHP, you can simply pass the database to be used as the forth parameter of mysqli_connect(). After connecting, if you want to change the database, use mysqli_select_db().