MySQL database operations (create, select, delete)

MySQL Create Database

After logging into the MySQL service, we can use the create command to create a database. The syntax is as follows:

CREATE DATABASE database name;

The following command simply demonstrates the process of creating a database named RUNOOB:

[root@host]# mysql -u root -p 
Enter password:****** # Log in to the terminal mysql> create DATABASE RUNOOB;

Create a database using mysqladmin

As a normal user, you may need specific permissions to create or delete MySQL databases.

So we log in as the root user here. The root user has the highest permissions and can use the mysql mysqladmin command to create a database.

The following command simply demonstrates the process of creating a database named RUNOOB:

[root@host]# mysqladmin -u root -p create RUNOOB
Enter password:******

After the above command is executed successfully, the MySQL database RUNOOB will be created.

Creating a database using PHP script

PHP uses the mysqli_query function to create or delete a MySQL database.

This function takes two parameters and returns TRUE if the execution is successful, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

Examples

The following example demonstrates how to create a database using PHP:

<?php
$dbhost = 'localhost'; // mysql server host address$dbuser = 'root'; // mysql username$dbpass = '123456'; // mysql username and password$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(!$conn)
{
 die('Connection error: ' . mysqli_error($conn));
}
echo 'Connection successful<br />';
$sql = 'CREATE DATABASE RUNOOB';
$retval = mysqli_query($conn,$sql);
if(!$retval )
{
 die('Failed to create database: ' . mysqli_error($conn));
}
echo "Database RUNOOB created successfully\n";
mysqli_close($conn);
?>

After successful execution, the following results are returned:

If the database already exists, after execution, the following results are returned:

MySQL Select Database

After you connect to the MySQL database, there may be multiple databases that you can operate, so you need to select the database you want to operate.

Select MySQL database from the command prompt window

Selecting a specific database is easy from the mysql> prompt. You can use SQL commands to select a specific database.

Examples

The following example selects the database RUNOOB:

[root@host]# mysql -u root -p
Enter password:******
mysql> use RUNOOB;
Database changed
mysql>

After executing the above command, you have successfully selected the RUNOOB database. All subsequent operations will be performed in the RUNOOB database.

Note: All database names, table names, and table fields are case sensitive. So you need to enter the correct name when using SQL commands.

Select MySQL database using PHP script

PHP provides the function mysqli_select_db to select a database. The function returns TRUE if the execution is successful, otherwise it returns FALSE.

grammar

mysqli_select_db(connection,dbname);

Examples

The following example shows how to use the mysqli_select_db function to select a database:

<?php
$dbhost = 'localhost'; // mysql server host address$dbuser = 'root'; // mysql username$dbpass = '123456'; // mysql username and password$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(!$conn)
{
 die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connection successful';
mysqli_select_db($conn, 'RUNOOB' );
mysqli_close($conn);
?>

MySQL delete database

Log in to the MySQL server as a normal user. You may need specific permissions to create or delete a MySQL database, so we use the root user to log in here. The root user has the highest permissions.

When deleting a database, you must be very careful because all data will disappear after executing the deletion command.

The drop command deletes the database

drop command format:

drop database <database name>;

For example, to delete a database named RUNOOB:

mysql> drop database RUNOOB;

Deleting a database using mysqladmin

You can also use the mysql mysqladmin command to execute the delete command in the terminal.

The following example deletes the database RUNOOB (the database has been created in the previous chapter):

[root@host]# mysqladmin -u root -p drop RUNOOB
Enter password:******

After executing the above command to delete the database, a prompt box will appear to confirm whether to delete the database:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'RUNOOB' database [y/N] y
Database "RUNOOB" dropped

Deleting a database using a PHP script

PHP uses the mysqli_query function to create or delete a MySQL database.

This function takes two parameters and returns TRUE if the execution is successful, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

Examples

The following example demonstrates using the PHP mysqli_query function to delete a database:

<?php
$dbhost = 'localhost'; // mysql server host address$dbuser = 'root'; // mysql username$dbpass = '123456'; // mysql username and password$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(!$conn)
{
 die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connection successful<br />';
$sql = 'DROP DATABASE RUNOOB';
$retval = mysqli_query( $conn, $sql );
if(!$retval )
{
 die('Failed to delete database: ' . mysqli_error($conn));
}
echo "Database RUNOOB deleted successfully\n";
mysqli_close($conn);
?>

After successful execution, the results are:

Note: When using PHP script to delete a database, there will be no confirmation message to confirm whether to delete. The specified database will be deleted directly, so you must be particularly careful when deleting the database.

The above is the details of MySQL database operations (create, select, delete). For more information about MySQL database operations, please pay attention to other related articles on 123WORDPRESS.COM!