To create and drop database in MySQL using command line or php code; 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.
How to Create and Drop MySQL Database using PHP Script and Command Line
There are two simple ways to create and drop database in mysql using php code or cmd; as shown below
- Create & Drop MySQL Database Using CMD
- Using PHP Script For Create & Drop MySQL Database
Create & Drop MySQL Database Using Command Line or CMD
Now you will learn, To create MySQL database and users. Just follow the few steps :-
- LogIn As Root User
- Create User
- LogIn With New Created User
- Create Database
- Use Database
- Create Table and Insert Data
- Drop MySQL Database
- Drop MySQL Table
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);
?>
Recommended Post
MySQL CONNECT TO DATABASE WITH COMMAND LINERecommended Tutorials
- To Remove Elements or Values from Array PHP
- PHP remove duplicates from multidimensional array
- Remove Duplicate Elements or Values from Array PHP
- How to Convert String to Array in PHP
- Array Push and POP in PHP | PHP Tutorial
- PHP Search Multidimensional Array [key and value and return key]
- Get month name from date in mysql
- mysql get first day of the current month
- mysql last day of previous month
- Get month number from month name in mysql
- mysql get day of week number from date
- Mysql Get Data Of Current Date, Week, Month, YEAR
- Get year of week in MySQL
