MySQL ALTER Command

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.

Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.

Syntax

Following is the syntax of ALTER command in MySQL −

ALTER TABLE table_name [alter_option ...];

Example

Let us begin with the creation of a table named CUSTOMERS.

CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR(20)
);

Now, execute the following query to display information about the columns in CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS;

Output

Following are the details of the columns of the CUSTOMERS table −

Dropping a Column

To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.

Example

In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP ID;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, there is no ID column present. Hence it is dropped.

Note: A DROP clause will not work if the column is the only one left in the table.

Adding a Column

To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.

Example

In the following query, we are adding a column named ID into an existing table CUSTOMERS.

ALTER TABLE CUSTOMERS ADD ID INT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.

Verification

Let us verify using the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the newly added ID column is inserted at the end of the table.

Repositioning a Column

If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.

Example

Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.

In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword −

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Example

Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Altering a Column Definition or a Name

In MySQL, to change a column”s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.

Example

In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause −

ALTER TABLE CUSTOMERS MODIFY NAME INT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Example

We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.

Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause −

ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Altering a Column”s Default Value

In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.

Example

In the following example, we are changing the default value of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to 1000.

Example

We can remove the default constraint from any column by using DROP clause along with the ALTER command.

Here, we are removing the default constraint of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to NULL.

Altering (Renaming) a Table

To rename a table, use the RENAME option of the ALTER TABLE statement.

Example

The following query renames the table named CUSTOMERS to BUYERS.

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM BUYERS;

The table has been renamed to BUYERS, as we can see from the columns within it.

Altering Table Using a Client Program

Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.

Syntax

Following are the syntaxes of Altering a table from MySQL Database in various programming languages −

$sql = "ALTER TABLE table_name";
$mysqli->query($sql);

sql = "ALTER TABLE table_name";
con.query(sql);

String sql = "ALTER TABLE table_name";
statement.execute(sql);

sql = "ALTER TABLE table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

$sql = "ALTER TABLE testalter_tbl  DROP i";
if ($mysqli->query($sql)) {
    printf("table altered successfully.
");
}
if ($mysqli->errno) {
    printf("table could not alter: %s
", $mysqli->error);
}
$mysqli->close();

table altered successfully.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",    user: "root",
    password: "Nr5a0204@123"
});
  //Connecting to MySQL
  con.connect(function (err){
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
  sql = "USE TUTORIALS"
  con.query(sql);

  //Altering a table
  sql = "ALTER TABLE testalter_tbl  DROP i";
    con.query(sql, function(err){
    if (err) throw err
    console.log("Altered table successfully...");
  });
});

Connected!
--------------------------
Altered table successfully...

import java.sql.*;
public class AlterTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Alter table statement...!
            String sql = "ALTER TABLE testalter_tbl  DROP i";
            statement.executeUpdate(sql);
            System.out.println("Table altered successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table altered successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''testalter_tbl''
# ALTER TABLE statement
alter_statement = ''testalter_tbl  DROP i''
#Creating a cursor object
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER TABLE {table_name} {alter_statement}")
print(f"Table ''{table_name}'' is altered successfully.")
cursorObj.close()
connection.close()

Table ''testalter_tbl'' is altered successfully.

As we learned in the previous tutorial about ”Database Export”, now we”ll learn how to import the exported data, or backup, into an existing MySQL database. This process is known as database import.

In MySQL, to import an existing dump or backup file into a database, we use the mysql command-line tool.

Importing Backup Data

We can import the backup data into an MySQL database using the mysql command-line tool. It takes the username, database name, and the backup file with the data.

Syntax

Following is the syntax of mysql command-line tool −

$ mysql -u username -p new_database_name < dumpfile_path

Where,

• username: This is the MySQL username to use when connecting to the MySQL server.

• new_database_name: The name of the database where you want to import the data.

• dumpfile_path: It is the path of the backup file. The data will be imported from this file.

• <: This symbol imports the data from the file named output_file_path.

Example

In this example, we will import the file named “data-dump.sql” that was generated in the previous tutorial (Database Export). The file contains a table named ”CUSTOMERS”.

Before doing that, let us login to MySQL server as a user to create a new databases −

$ mysql -u root -p

After logging in, it will bring you into MySQL command-line. Now, create a new database named testdb using the below query −

CREATE DATABASE testdb;

When we execute the above query, the output is obtained as follows −

Query OK, 1 row affected (0.01 sec)

To exit from the MySQL command-line, execute q. Now, from the normal command line, we can import the dump file ”data-dump.sql” using the following query.

Once we execute the below statement, we need to enter our MySQL server password.

$ mysql -u root -p testdb < data-dump.sql

If the above command is runs successfully, it won”t show any visible output. Instead, it imports the data. If any error occur during the execution, MySQL will display them to the terminal.

Verification

To verify whether the import was successful, execute the following query to login into MySQL command-line −

$ mysql -u root -p

Now, select the current database to ”testdb” using the following MySQL ”Use” query −

Use testdb;

Execute the following query to check whether the table named CUSTOMERS in “data-dump.sql” file has been imported or not −

Show Tables;

As we can see the output below, the CUSTOMERS table has been succesfully imported into the new database ”testdb”.

Let us also verify whether the records has been imported or not by executing the below query −

select * from customers;

The records are also successfully imported.

MySQL SHOW Databases Statement

To display the list of all databases present in a MySQL server, we need to use the SHOW DATABASES statement. It returns the result in a tabular form with one column. The databases in the result set will be sorted in alphabetical order.

Syntax

Following is the syntax to list all databases in MySQL sever −

SHOW DATABASES [LIKE ''pattern'' | WHERE expr]

Example

First of all, let us create a database with a name TUTORIALS using the following query −

CREATE DATABASE TUTORIALS;

Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases by using the following query −

SHOW DATABASES;

Output

The above query displayed all the databases present in the current MySQL server.

MySQL SHOW SCHEMAS Statement

We can also use the SHOW SCHEMAS statement to list out the databases in MySQL.

Syntax

Following is the syntax of the MySQL SHOW SCHEMAS statement −

SHOW SCHEMAS [LIKE ''pattern'' | WHERE expr]

Example

Lets try to verify the list of databases once again you can using the following query −

SHOW SCHEMAS;

Output

The output of the above query will be as shown below −

Showing Databases Using a Client Program

Besides fetching the list of all databases in current MySQL server with a MySQL query, we can also use a client program to perform the SHOW DATABASES operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SHOW Database_name";
$mysqli->query($sql);

sql= "SHOW {DATABASES | SCHEMAS} LIKE ''pattern'' | WHERE expr]";
con.query(sql);

String sql = "SHOW Database_name";
st.executeQuery(sql);

sql = "SHOW Database_name";
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
if ($result = $mysqli->query("SHOW DATABASES")) {
   printf("Show Database executed successfully..!");
   echo "Database list are: ";
   while($row = mysqli_fetch_array($result)){
       print_r($row);
   }
}
if ($mysqli->errno) {
    printf("Could not find database: %s
", $mysqli->error);
}
$mysqli->close();

Show Database executed successfully..!Database list are:
Array
(
    [0] => bank
    [Database] => bank
)
Array
(
    [0] => company
    [Database] => company
)
Array
(
    [0] => information_schema
    [Database] => information_schema
)
Array
(
    [0] => mydb
    [Database] => mydb
)
Array
(
    [0] => mysql
    [Database] => mysql
)
Array
(
    [0] => performance_schema
    [Database] => performance_schema
)
Array
(
    [0] => sys
    [Database] => sys
)
Array
(
    [0] => testdb
    [Database] => testdb
)
Array
(
    [0] => tutorials
    [Database] => tutorials
)
Array
(
    [0] => usersdb
    [Database] => usersdb
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");

//Creating a Database
sql = "create database testDB1"
con.query(sql);
sql = "create database testDB2"
con.query(sql);
sql = "create database testDB3"
con.query(sql);
sql = "create database testDB4"
con.query(sql);

//Displaying Databases
sql = "show databases;"
con.query(sql, function(err, result){
  if (err) throw err
  console.log(result)
});
});

Connected!
--------------------------
[
  { Database: ''customers'' },
  { Database: ''information_schema'' },
  { Database: ''mysql'' },
  { Database: ''performance_schema'' },
  { Database: ''testdb1'' },
  { Database: ''testdb2'' },
  { Database: ''testdb3'' },
  { Database: ''testdb4'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowDatabase {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st1 = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SHOW DATABASES";
            rs = st1.executeQuery(sql);
            System.out.println("Show query executed successfully...!");
            System.out.println("Databases are: ");
            while(rs.next()) {
            	String db = rs.getNString(1);
            	System.out.println(db);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Show query executed successfully...!
Databases are:
bank
company
information_schema
mydb
mysql
performance_schema
sys
testdb
tutorials
usersdb

import mysql.connector
# Creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# Creating cursor object
cursorObj = connection.cursor()
# Show databases
cursorObj.execute("SHOW DATABASES")
# Fetch all the databases
databases = cursorObj.fetchall()
# Printing the list of databases
print("The list of databases are: ")
for database in databases:
    print(database[0])
# Disconnecting from server
connection.close()

The list of databases are:
information_schema
mysql
mysqlpythondb
performance_schema
sqlserver
stdi
sys
textx
tut
tutorials

MySQL is one of the most popular relational database systems used to store and manage data. It structures data in the form of tables and views so that data handling becomes easier making organizations prefer using MySQL to manage their company”s confidential data. Since their data is highly confidential, it becomes necessary to back up the database and restore it whenever necessary. Hence we perform database export.

Exporting a database in MySQL is commonly used for backup purposes or transferring data between servers. You can export entire database or just a portion of it. The simplest way of exporting a database is by using the mysqldump command-line tool.

Exporting Database using mysqldump

The mysqldump command-line tool is used in MySQL to create backups of databases. It can be used to back up an entire database, specific tables, or even specific rows based of a table.

Following is the syntax of mysqldump command to export a database −

$ mysqldump -u username -p database_name > output_file_path

Where,

• username: It is the MySQL username to use when connecting to the database.

• database_name: It is the name of the database to be exported.

• output_file_path: It is the path of the backup file. This is where the backup data will be stored.

• >: This symbol exports the output of the mysqldump command into a file named output_file_path.

Example

First of all, create a database named TUTORIALS using the following query −

Create database TUTORIALS;

Execute the below query to select the current database as TUTORIALS −

USE TUTORIALS;

Let us also create a table named CUSTOMERS in the above-created database −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Here, we are using the mysqldump command-line statement to export the TUTORIALS database to the ”datadump.sql” file. Once we execute the below statement, we need to enter our MySQL server password.

$ mysqldump -u root -p TUTORIALS > data-dump.sql

The above command will not produce any visual output. Instead, the ”data-dump.sql” file will be saved in the current working directory of the command prompt or terminal where you executed the command.

Exporting only Specific Tables in Database

We can also export only specific tables in a database using the mysqldump command-line tool. To do so, we use the following syntax −

mysqldump -u username -p database_name table1 table2 ... > output_file.sql

Example

Before exporting, let us create two new tables (STUDENTS and EMPLOYEES) in the above-created TUTORIALS database using the following query −

CREATE TABLE STUDENTS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   FEES DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are creating the EMPLOYEES table −

CREATE TABLE EMPLOYEES (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARIES DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Let us fetch the list of tables in the TUTORIALS database using the below query −

Show Tables;

As we can in the output below, we have three tables present in TUTORIALS database −

Now, let us export two tables named EMPLOYEES and STUDENTS into the ”datadump2.sql” as follows −

$ mysqldump -u root -p TUTORIALS employees students > output_file.sql

Once we executed the above query, it wont display any ouptut. Instead, it exports the data of both the tables into the specified file.

Exporting all Databases in a Host

For instance, you have multiple databases in your host and you want to export all of them in a single query. In such scenario, we can use the “–all-databases” option of mysqldump command.

Example

Following is the query to export all the databases in a host using the –all-databases option −

$ mysqldump -u root -p --all-databases > database_dump.sql

The above command won”t show any visible output on the screen. Instead, the ”database_dump.sql” file will be saved in the current working directory of the command prompt or terminal where you ran the command.”

In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.

Copy Database in MySQL

In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.

To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.

The following are three steps that involve in copying a database −

• First of all, we need to create a new database.

• Then, we need to export the original database using mysqldump.

• Finally, importing the exported data into the new database.

Example

First of all, let us create a database in the MySQL server using the following query −

CREATE DATABASE testdb;

We can verify whether the database testdb is created or not using the SHOW DATABASES statement.

SHOW DATABASES;

As we can see the output below, the testdb database has been created successfully.

Once the database is created successfully, we need to change the current database to ”testdb”, using the USE statement so that any operations we perform such as creating a table will be stored in this database.

USE testdb;

Now, let us create a table named CUSTOMERS using the CREATE query as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Using the following query, we can verify whether the table CUSTOMERS is created in ”testdb” database or not −

SHOW TABLES;

The table is successfully created in the testdb database.

Creating Copy database (Manually)

As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.

Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).

CREATE DATABASE testdb_copy;

Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.

Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C Program Files directory so, the following command will take us to the bin folder −

C:> CD C:Program FilesMySQLMySQL Server 8.0bin

Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named “testdb.sql” located at “D:Database_backup”.

Note − The (>) operator is used for exporting the database from one location to another.

mysqldump -u root -p testdb > D:database_backuptestdb.sql

Step 3 − Import the contents of the “testdb.sql” file into the destination database (in our case “testdb_copy”).

Note − The (<) operator is used for importing the database from one location to another.

mysql -u root -p testdb_copy
Verification
To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client −
USE testdb_copy;





If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).




Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query −




SHOW TABLES;





As we can see in the list below, all the database objects and data have been successfully copied.











Tables_in_testdb










customers










Copy Database Without MySQLdump




If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.




Example




Let us create a new database in the MySQL server using the following query −




CREATE DATABASE Tutorials;





We can verify whether the database Tutorials is created or not using the following query −




SHOW DATABASES;





As we can see the output below, the ”Tutorials” database has been created successfully.










Database










information_schema










mysql










performance_schema










tutorials










Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.




USE Tutorials;





Once we have switched, create a table named CUSTOMERS using the following query −




CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);





Here, we are inserting some records into the table using the INSERT INTO statement below −




INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );





Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.




CREATE DATABASE Tutorials_copy;





We can verify whether the database Tutorials_copy is created or not, using the following query −




SHOW DATABASES;





The database has been created.










Database










information_schema










mysql










performance_schema










tutorials










tutorials_copy










Now, switch the current database to ”tutorials_copy” using the following query −




USE Tutorials_copy;





Here, we are creating an empty table named “CUSTOMERS” in the ”Tutorials_copy” database with the same schema as the original ”CUSTOMERS” table in the ”Tutorials” −




CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;





This query inserts all the data from the original ”customers” table in the ”Tutorials” database into the new ”customers” table in the ”tutorials_copy” database.




INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;





We can verify whether the database objects and data from the ”Tutorials” database have been copied to the ”Tutorials_copy” database or not.




SHOW TABLES;





Output




As we can see in the list below, the table has been successfully copied −










Tables_in_tutorials_copy










customers










Let us also retrieve the records of CUSTOMERS table in ”Tutorials_copy” database to verify whether the records have been copied or not −




Select * from CUSTOMERS;





As we can see the CUSTOMERS table in ”Tutorials_copy” database below, the records have been successfully copied −










ID




NAME




AGE




ADDRESS




SALARY










1




Ramesh




32




Ahmedabad




2000.00










2




Khilan




25




Delhi




1500.00










3




Kaushik




23




Kota




2000.00










4




Chaitali




25




Mumbai




6500.00










5




Hardik




27




Bhopal




8500.00










6




Komal




22




Hyderabad




4500.00










7




Muffy




24




Indore




10000.00










		


	


 

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương:   Khóa học lập trình nhận lương tại trung tâm Toidayhoc




			
August 17, 2024
		

MySQL usually stores two types of data in it: actual data stored in the form of tables and views, and information about the structure of a database and its objects. Such information is known as Metadata.

For instance, whenever a user forgets certain information of a database or its objects, MySQL provides specific commands to retrieve the said information. There are actually three types of information, which you can retrieve from a MySQL database. They are as follows −

• Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.

• Information about the tables and databases − This includes information related to the structure of the tables and the databases.

• Information about the MySQL server − This includes the status of the database server, version number, etc.

It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.

Obtaining Database Info from MySQL Prompt

While accessing a MySQL server from MySQL prompt, which is a Command Prompt in Windows and a Terminal in Linux etc., any information regarding a database using following commands.

• SHOW DATABASES: This command is used to retrieve the list of all databases present in MySQL.

• SHOW TABLES: This command is used to display the list of tables present in a database.

• mysql -V: This command is used to provide the current version of MySQL installed in your system.

• DESC or DESCRIBE: This command is used to retrieve the structure or definition of a database table.

mysql -V Command

If you want to check the version of MySQL Server installed in your system, use the following mysql -V on Command Prompt or Terminal.

Note: You must remember that the command prompt must be run as an administrator in Windows.

Example

In here, we are checking the current version of MySQL server in command prompt −

C:WindowsSystem32> mysql -V

Output

As we can see the output below, the current MySQL server version is ”8.0.33” −

mysql  Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

SHOW DATABASES Command

To list or retrieve the names of all the databases in MySQL, you can use the following SHOW DATABASES command after logging into MySQL server −

Note − This command will list both system databases and user-defined databases together. The user must identify their specific user-defined database all the data is stored in.

Example

In the following query, we are fetching list of databases that exists in the current MySQL server −

SHOW DATABASES;

Here, the first three rows are system databases and the last two rows are user-defined databases −

SHOW TABLES Command

To list all the tables in a MySQL database, you can use the SHOW TABLES command after selecting a database with USE command −

Example

In the below query, we are selecting a database named ”Tutorials” −

USE Tutorials;

Now, let us use the SHOW TABLES to fetch all the names of tables present in the above database −

Show Tables;

Output

Following is the list of tables exist in the ”Tutorials” database −

DESC Command

If we want to check the structure of a MySQL table, we need to use the DESC or DESCRIBE query. DESC is a shortcut for DESCRIBE query, but retrieves the same result.

Example

Here, we are fetching the structure of MySQL CUSTOMERS table −

DESC CUSTOMERS;

Following is the structure −

Obtaining the Number of Rows Affected by a Query

Let is now see how to obtain this information.

PERL Example

In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

PHP Example

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affectedn");

Listing Tables and Databases

It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don”t have the sufficient privileges.

Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.

PERL Example

# Get all the tables available in current database.
my @tables = $dbh->tables ( );

foreach $table (@tables ){
   print "Table Name $tablen";
}

PHP Example

Try the following example to get database info −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;

         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Default database is tutorials

Getting Server Metadata

There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.

In MySQL, you can create multiple user accounts to access the database, each with specific authentication detail such as password. These users can be granted specific privileges using SQL statements like CREATE USER for authentication when creating a new user, and GRANT and REVOKE for assigning and removing administrative privileges, respectively.

The MySQL CREATE USERS Statement

We can create a new user account using the CREATE USER Statement in MySQL. To execute this statement, the current account must have the CREATE USER privilege or the INSERT privilege for the MySQL system schema.

Syntax

Following is the syntax of the MySQL CREATE USER statement −

CREATE USER ''user_name''@''host_name'' IDENTIFIED BY ''password

Where,

• user_name is the name of the user you need to create.

• hostname specifies the host from which the user can connect.

• password is the user”s password.

Example

In the following query, we are creating a user named ”sample” who can only connect from the ”localhost” host and sets their password as ”123456”. Make sure that you have logged in with a user with admin privileges (root) −

CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456

Output

The output will be displayed as −

Query OK, 0 rows affected (0.12 sec)

Verification

You can verify the list of users using the following query −

SELECT USER FROM MySQL.USER;

The table will be displayed as shown below −

Granting Privileges in MySQL

You can grant all privileges to the created user using the GRANT ALL statement. This allows you to give specific permissions to users for actions like accessing databases, tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.

Syntax

Following is the syntax to grant all privileges in MySQL −

GRANT ALL PRIVILEGES ON database_name.* TO ''username''@''host

Example

The following query grants the user ”sample” full privileges to perform any action on any database or table when connecting from the ”localhost” host, giving complete control over the MySQL server locally −

GRANT ALL PRIVILEGES ON * . * TO ''sample''@''localhost

Output

The output will be displayed as −

Query OK, 0 rows affected (0.02 sec)

Logging as a Different User

To log in as a different user in MySQL, you should first exit the current MySQL session if you are already logged in and then execute the command -u user_name -p in your system”s command prompt or terminal, not within the MySQL shell itself.

Example

Here, we are executing the -u sample -p command. After running the command, you will be prompted to enter the password for the specified user. Enter the correct password to log in as shown below −

mysql -u sample -p
Enter password: ******

Output

This will log you in as the sample user with the appropriate privileges and permissions as shown below −

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ''help;'' or ''h'' for help. Type ''c'' to clear the current input statement.

The Expire Clause

If you use the expire clause, the old password (current password) will expire immediately and the user need to choose new password at first connection.

Example

Here, we are first removing the existing user ”sample”@”localhost” −

DROP user sample@localhost;

We are now creating a new user ”sample”@”localhost” with the password ”MyPassword” while immediately expiring the password, forcing the user to set a new password upon the first login −

CREATE USER ''sample''@''localhost''
IDENTIFIED BY ''MyPassword'' PASSWORD EXPIRE;

Now, if you log in as a newly created user, an error will be generated. So, to login as newly created user, open command prompt browse through bin folder of the MySQL directory and execute the following command −

C:Program FilesMySQLMySQL Server 8.0bin> mysql -u sample@localhost -p
Enter password: **********

Any MySQL command execution at this point will trigger an error message as shown below −

select now();

The output obtained is as shown below −

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Since the password is expired, the above error message is generated. To make this right we need to change (reset) the password using the following command −

SET PASSWORD=''passwordtest

Following is the output produced −

Query OK, 0 rows affected (0.34 sec)

You can also set an interval for the EXPIRE clause to implement periodic password changes as shown below −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost''
   IDENTIFIED BY ''MyPassword''
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.20 sec)

User Comment

You can add comments to the user while creating a user in MySQL using the COMMENT clause. This provides additional information or context about the user.

Example

In the following example, we are first removing the existing ”sample”@”localhost” user. Then, we are creating a new ”sample”@”localhost” user while adding a comment to describe the user −

drop user sample@localhost;
CREATE USER ''sample''@''localhost'' COMMENT ''Sample information

Output

The result obtained is as shown below −

Query OK, 0 rows affected (0.10 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER=''sample'' AND HOST=''localhost

The result produced is as shown below −

User Attribute

You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a user account. These attributes can store additional information about the user.

Example

In here, we are first removing the existing ”sample@localhost” user. Then, we are creating a new ”sample”@”localhost” user with attributes ”attr1” and ”attr2” set to ”val1” and ”val2,” respectively, associated with the user account −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost''
ATTRIBUTE ''{"attr1": "val1", "attr2": "val2"}

The result obtained is as shown below −

Output

Query OK, 0 rows affected (0.09 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER=''sample'' AND HOST=''localhost

The result obtained is as shown below −

The IF NOT EXISTS Clause

If you try to create a user with an existing name, an error will be generated. To prevent this error and ensure the user is created only if it does not already exist, you can use the “IF NOT EXISTS” clause.

Example

In the example below we are creating a user ”sample@localhost” without the “IF NOT EXISTS” clause −

CREATE USER ''sample@localhost

We can see in the below output that an error is generated −

ERROR 1396 (HY000): Operation CREATE USER failed for ''sample@localhost''@''%''

However, if we use the “IF NOT EXISTS” clause along with the CREATE statement, a new user will be created, and if a user with the given name already exists, the query will be ignored −

CREATE USER IF NOT EXISTS ''sample@localhost

Following is the output obtained −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Creating User Using a Client Program

In addition to creating a user into MySQL Database using the MySQL query, we can also create using a client program.

Syntax

Following are the syntaxes to create a MySQL user in various programming languages −

$sql = "CREATE USER ''user_name''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
$mysqli->query($sql);

sql= " CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ''password
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

sql = "CREATE USER ''USER_NAME''@LOCALHOST INDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY ''PASSWORD''";
statement.execute(sql);

sql = "CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''";
cursorObj.execute(sql)

Example

Following are the client programs to create an user in MySQL −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE USER ''Revathi''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
if($mysqli->query($sql)){
   printf("User created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User created successfully...!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");

   //Creating User
   sql = "CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456"
   con.query(sql);

   //List of users
   sql = "select user from MySQl.user;"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Connected!
--------------------------
[
  { user: ''mysql.infoschema'' },
  { user: ''mysql.session'' },
  { user: ''mysql.sys'' },
  { user: ''root'' },
  { user: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateUsers {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection con = DriverManager.getConnection(url, user, password);
         Statement st = con.createStatement();
         //System.out.println("Database connected successfully...!");
         String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
         st.execute(sql);
         System.out.println("User ''Vivek'' created successfully...!");
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

User ''Vivek'' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''textx''
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''")
print("User ''newUser'' is created successfully.")
cursorObj.close()
connection.close()

User ''newUser'' is created successfully.

Dropping users in MySQL will remove a user”s access and permissions on a specific database. This is performed by database administrators to maintain security and control over who can interact with the database system, ensuring that only authorized users can access and manipulate the data.

The MySQL Drop User Statement

You can drop/delete one or more existing users in MySQL using the DROP USER Statement. Once you delete an account, all privileges of it are deleted. To execute this statement, you need to have CREATE USER privilege.

Syntax

Following is the syntax of the DROP USER statement −

DROP USER [IF EXISTS] ''username''@''hostname

Where, user_name is the name of the MySQL user you need to delete.

Example

Suppose, we have created a MySQL user account named ”TestUser” as shown below −

CREATE USER TestUser@localhost IDENTIFIED BY ''password1

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

You can verify the list of users using the following query −

SELECT user FROM MySQl.user;

The table will be displayed as shown below −

Now, let us delete the ”TestUser” account created above using the DROP USER statement as shown below −

DROP USER TestUser@localhost;

After executing the above code, we can see the output as shown below −

Query OK, 0 rows affected (0.02 sec)

Verification

Once a table is dropped, if you verify the list of the users as shown below using the SELECT statement, you will find that its name is missing from the list −

SELECT user FROM MySQl.user;

The table obtained is as follows −

Removing Multiple Users

You can also delete multiple users at once using the DROP ROLE statement. Roles are used to manage permissions and access control in a database system. By dropping a role, you revoke all privileges associated with that role. −

Example

Let us start by creating two roles ”MyAdmin” and ”MyDeveloper” −

CREATE ROLE ''MyAdmin'', ''MyDeveloper

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Now, let us remove these roles using the DROP ROLE statement −

DROP ROLE ''MyAdmin'', ''MyDeveloper

This query will effectively delete both roles from the database −

Query OK, 0 rows affected (0.01 sec)

The IF EXISTS clause

If you try to drop a MySQL user that doesn”t exist, an error will be generated. To address this issue, MySQL provides the IF EXISTS clause, which can be used with the DROP USER statement.

Hence, the IF EXISTS clause allows you to drop a user if they exist, and it handles situations where the specified user is not found in the database.

Example

In the below query, we are attempting to drop the ”demo” user. However, it results in an error because the user doesn”t exist in the database −

DROP USER demo@localhost;

The output produced is as shown below −

ERROR 1396 (HY000): Operation DROP USER failed for ''demo''@''localhost''

If you use the IF EXISTS clause along with the DROP USER statement as shown below, the specified user will be dropped and if a user with the given name doesn”t exist, the query will be ignored −

DROP USER IF EXISTS demo;

The output obtained is as follows −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Dropping User Using a Client Program

In this section we are going to see various client programs to drop an existing user from MySQL.

Syntax

Following are the syntaxes to drop a MySQL user in various programming languages −

$sql = "DROP USER ''username''@''localhost''";
$mysqli->query($sql);

sql= "DROP USER [IF EXISTS] user_name ...";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "DROP USER "USER_NAME''@''LOCALHOST''";
statement.execute(sql);

sql = "DROP USER ''UserName''@''localhost''";
cursorObj.execute(sql);

Example

Following are the client programs to drop an user in MySQL −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "DROP USER ''Revathi''@''localhost''";
if($mysqli->query($sql)){
   printf("User dropped successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User dropped successfully...!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Deleting Users
  sql = "DROP USER TestUser1@localhost;"
  con.query(sql);
  sql = "DROP USER TestUser2@localhost;"
  con.query(sql);

  //Listing the users after deleting
  sql = "select user from MySQl.user;"
  con.query(sql, function(err, result){
    console.log("**List of Users after deleting:**")
    if (err) throw err
    console.log(result)
  })
});

Connected!
--------------------------
*List of Users after deleting:**
[
  { user: ''TestUser3'' },
  { user: ''TestUser4'' },
  { user: ''mysql.infoschema'' },
  { user: ''mysql.session'' },
  { user: ''mysql.sys'' },
  { user: ''root'' },
  { user: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DropUsers {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "DROP USER ''Vivek''@''localhost''";
            st.execute(sql);
            System.out.println("User ''Vivek'' dropped successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("DROP USER ''UserNew''@''localhost''")
print("User ''UserNew'' is dropped successfully.")
cursorObj.close()
connection.close()

User ''UserNew'' is dropped successfully.

As you might have already known, MySQL is a multi-user database that allows multiple users to work on it simultaneously. But have you ever wondered who these users might be?

MySQL provides an account to each user that is authenticated with a username and a password. And details of these accounts are stored in the “user” table in the database. This table contains details like username, the host this user is connected from, and other privileges the said user has etc.

The MySQL SHOW Users

MySQL does not provide any direct command to show (list out) all the users. However, the details of these user accounts is stored in the “user” table within the database. Hence, we can use the SELECT statement to list out the contents of this table.

There is no limit for how many users can connect to a MySQL database but the default user is always “root”. And it does not have any password, unless it is set manually.

Syntax

Following is the syntax to show users in a MySQL database −

SELECT * FROM mysql.user;

Example

To see the structure of this “user” table, use the following query with the DESC command −

DESC mysql.user;

Now, in this example, we are listing out all the users in the MySQL database local to a system −

SELECT Host, User, User_attributes, account_locked
FROM mysql.user;

Output

The output obtained is as shown below −

The actual user table contains a lot more columns/fields than what is displayed in this chapter. Here, however, only some information is displayed for simplicity.

Note that list of these users are local to a system. Hence, not all systems would give the same output (apart from the default users).

Show Current User

Not only the list of all users, MySQL also has a provision to see the current user. This is done with the help of user() or current_user() functions.

Syntax

Following is the syntax to show the current user −

SELECT user();
or
SELECT current_user();

Example

Using the following query, let us display the username of the currently logged in user in MySQL database using the user() function −

SELECT user();

Output

Following is the output obtained −

Example

In here, we are using the current_user() function to show the current user −

SELECT current_user();

Output

The output obtained is as follows −

Show Currently Logged in Users

The difference between current users and currently logged in users is that, current user is the user that is executing the queries; whereas, currently logged in user list includes all the active users that are connected to the MySQL server at the moment.

This information can be extracted from the “information_schema.processlist” table using the SELECT statement.

Example

In the following query, we are retrieving the information of all the currently logged in users −

DESC information_schema.processlist;

Output

Following is the output of the above code −

Example

In here, we are retrieving information of current users, host, database, and command from the information_schema −

SELECT user, host, db, command
FROM information_schema.processlist;

Output

After executing the above code, we get the following output −

Show Users Using a Client Program

We can also display information about the MySQL users using a client program.

Syntax

Following are the syntaxes to display information regarding MySQL users in various programming languages −

$sql = "SELECT USER FROM MYSQL.user";
$mysqli->query($sql);

sql= "SELECT * FROM mysql.user";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "SELECT USER FROM MYSQL.USER";
statement.executeQuery(sql);

sql = "SELECT user, host FROM mysql.user";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT USER FROM MYSQL.user";
if($result = $mysqli->query($sql)){
   printf("User found successfully...!");
   printf("Users list are: ");
   while($row = mysqli_fetch_array($result)){
       print_r($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User found successfully...!Users list are: Array
(
    [0] => Vivek Verma
    [USER] => Vivek Verma
)
Array
(
    [0] => Revathi
    [USER] => Revathi
)
Array
(
    [0] => Sarika
    [USER] => Sarika
)
Array
(
    [0] => mysql.infoschema
    [USER] => mysql.infoschema
)
Array
(
    [0] => mysql.session
    [USER] => mysql.session
)
Array
(
    [0] => mysql.sys
    [USER] => mysql.sys
)
Array
(
    [0] => root
    [USER] => root
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating Users
  sql = "CREATE USER TestUser1@localhost IDENTIFIED BY ''password1"
  con.query(sql);
  sql = "CREATE USER TestUser2@localhost IDENTIFIED BY ''password2"
  con.query(sql);
  sql = "CREATE USER TestUser3@localhost IDENTIFIED BY ''password3"
  con.query(sql);
  sql = "CREATE USER TestUser4@localhost IDENTIFIED BY ''password4"
  con.query(sql);

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
  });
});

Connected!
--------------------------
**List of Users:**
[
  { USER: ''TestUser1'' },
  { USER: ''TestUser2'' },
  { USER: ''TestUser3'' },
  { USER: ''TestUser4'' },
  { USER: ''mysql.infoschema'' },
  { USER: ''mysql.session'' },
  { USER: ''mysql.sys'' },
  { USER: ''root'' },
  { USER: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowUsers {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
            st.execute(sql);
            System.out.println("User ''Vivek'' created successfully...!");
            String sql1 = "SELECT USER FROM MYSQL.user";
            rs = st.executeQuery(sql1);
            System.out.println("Users: ");
            while(rs.next()) {
            	String users = rs.getNString(1);
            	System.out.println(users);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' created successfully...!
Users:
Sarika
Vivek Verma
Revathi
Sarika
Vivek
mysql.infoschema
mysql.session
mysql.sys
root

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("SELECT user, host FROM mysql.user")
users = cursorObj.fetchall()
print("Existing users are:")
for user, host in users:
    print(f"User: {user}, Host: {host}")
cursorObj.close()
connection.close()

Existing users are:
User: UserNew, Host: localhost
User: mysql.infoschema, Host: localhost
User: mysql.session, Host: localhost
User: mysql.sys, Host: localhost
User: newUser, Host: localhost
User: root, Host: localhost

MySQL provides an account to each user which is authenticated with a username and a password. The default account in MySQL is a root with no password (One can however set a password to the root using a statement). Similarly, other user-defined accounts can have passwords set explicitly using an SQL statement or can have it system generated by MySQL.

MySQL Change User Password

Just like any other authenticated accounts, MySQL has a provision to change the user password. But one must make sure that there is currently no application being used by the user. If the password is reset without disconnecting the application, the application cannot connect to the server through this user again.

We can change the password for a MySQL user account using the following three SQL statements −

• UPDATE statement

• SET PASSWORD statement

• ALTER USER statement

The UPDATE Statement

The most basic way to change a user”s password in MySQL is by using the UPDATE statement. This statement is used to update account details, including the account password, from the ”root” account. But, once the modifications are done using this statement, you must use the FLUSH PRIVILEGES statement to reload privileges from the grant table of the MySQL database.

Syntax

Following is the syntax to change password using the UPDATE statement −

UPDATE mysql.user
SET authentication_string = PASSWORD(password_string)
WHERE User = user_name AND
      Host = host_name
FLUSH PRIVILEGES;

Example

Following example demonstrates how to change the password of a user account using the UPDATE statement. Firstly, we are creating a user account “sample” with a password ”123456” −

CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

Now, you can verify the list of users using the following query −

SELECT User FROM mysql.user;

The table will be displayed as shown below −

If you have the MySQL version 5.7.6 and later, you can directly modify the mysql.user table with the following query −

UPDATE user
SET authentication_string = PASSWORD(''xxxxxx'')
WHERE User = ''sample'' AND Host = ''localhost

After executing the above code, we get the following output −

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After making changes to user accounts, you need to use the FLUSH PRIVILEGES statement to apply these changes immediately −

FLUSH PRIVILEGES;

The output obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

The SET PASSWORD statement

The SET PASSWORD statement is used to set a password for a MySQL account. It contains a “password-verification” clause which lets the system know that the current user password needs to be replaced by another.

Syntax

Following is the syntax for the SET PASSWORD statement −

SET PASSWORD FOR username@localhost = password_string;

You can also change the password using SET PASSWORD without using the FOR clause. To use this syntax however, you must already be logged in on the user account you wish to change the password of −

SET PASSWORD = password_string;

Example

Now, using the SET PASSWORD statement, we are changing the password to ”hello” −

SET PASSWORD = ''hello

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.01 sec)

The ALTER USER Statement

To alter anything regarding a user account in MySQL, including changing passwords, ALTER USER statement is more preferable than SET PASSWORD statement. This statement is not used alone, instead is followed by the IDENTIFIED BY clause to authenticate the new password.

Note that the user must be connected to the MySQL server for this statement to work.

Syntax

Following is the syntax to change the password using the ALTER USER statement −

ALTER USER username IDENTIFIED BY ''password

Example

Here, we are changing the password of the sample@localhost account to ”000000” using the ALTER USER query given below −

ALTER USER sample@localhost IDENTIFIED BY ''000000

Output

Output of the above code is shown below −

Query OK, 0 rows affected (0.01 sec)

The password is now changed. To verify, log in to the sample account again using the new password −

C:WindowsSystem32> mysql -u sample -p
Enter password: ******

mysql>

Changing User password Using a Client Program

Besides using MySQL queries to change the user password in MySQL, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes −

$sql = "ALTER USER ''root''@''localhost'' IDENTIFIED BY ''new_password''";
$mysqli->query($sql);

sql= "ALTER USER username IDENTIFIED BY ''new_password''";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER ''USER_NAME''@''LOCALHOST'' IDENTIFIED BY ''NEW_PASSWORD''";
statement.execute(sql);

sql = f"ALTER USER ''{username_to_change}''@''localhost'' IDENTIFIED BY ''{new_password}''";
cursorObj.execute(sql);

Example

Following are the client programs to change the user password in MySQL −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
   if($mysqli->connect_errno ) {
     printf("Connect failed: %s
", $mysqli->connect_error);
     exit();
}
//printf(''Connected successfully.
'');
$sql = "ALTER USER ''root''@''localhost'' IDENTIFIED BY ''password1''";
if($mysqli->query($sql)){
   printf("User password has been changed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Your password has been changed successfully...!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Nr5a0204@123"});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
    console.log("--------------------------");
  });

  sql = "ALTER USER ''sample''@''localhost'' IDENTIFIED BY ''tutorials";
  con.query(sql, function(err){
    if (err) throw err;
    console.log("Password changed Successfully...");
  });
  sql = "FLUSH PRIVILEGES;"
  con.query(sql);
});

Connected!
--------------------------
**List of Users:**
[
  { USER: ''mysql.infoschema'' },
  { USER: ''mysql.session'' },
  { USER: ''mysql.sys'' },
  { USER: ''root'' },
  { USER: ''sample'' }
]
--------------------------
Password changed Successfully...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class ChangePassword {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "ALTER USER ''root''@''localhost'' IDENTIFIED BY ''password1''";
            st.execute(sql);
            System.out.println("User ''root'' password changed successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''root'' password changed successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_change = ''newUser''
new_password = ''passwordSet!''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER ''{username_to_change}''@''localhost'' IDENTIFIED BY ''{new_password}''")
print(f"Password for user ''{username_to_change}'' changed successfully.")
cursorObj.close()
connection.close()

Password for user ''newUser'' changed successfully.