Category: mysql

MySQL – Truncate Table Table of content MySQL TRUNCATE TABLE Statement TRUNCATE vs DELETE TRUNCATE vs DROP Truncating Table Using a Client Program ”; Previous Next MySQL TRUNCATE TABLE Statement The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table. This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process. You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table”s structure from the database. If you want to store some data again, you would need to re-create this table once again. Syntax Following is the basic syntax of the TRUNCATE TABLE statement − TRUNCATE TABLE table_name Where, table_name is the name of the table you need to delete all the records from. Example First of all, let us create a table with name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we are inserting 7 records into the above-created table using the following INSERT statement − 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 are displaying the records of CUSTOMERS table − SELECT * FROM CUSTOMERS; Following are the records of CUSTOMERS table − 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 In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table − TRUNCATE TABLE CUSTOMERS; Output The records have been truncated from the CUSTOMERS table without any error. Query OK, 0 rows affected (0.02 sec) Verification To verify whether the records have been truncated, let us retrieve the records using the following query − SELECT * FROM CUSTOMERS; As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated. Empty set (0.00 sec) TRUNCATE vs DELETE Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically: DELETE TRUNCATE The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. It is a DML(Data Manipulation Language) command. It is a DDL(Data Definition Language) command. There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. When you use the TRUNCATE command, the modifications made to the table are committed automatically. It deletes rows one at a time and applies some criteria to each deletion. It removes all of the information in one go. The WHERE clause serves as the condition in this case. There is no necessity of using a WHERE Clause. All rows are locked after deletion. TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. It makes a record of each and every transaction in the log file. The only activity recorded is the deallocation of the pages on which the data is stored. It consumes a greater amount of transaction space compared to TRUNCATE command. It takes comparatively less amount of transaction space. If there is an identity column, the table identity is not reset to the value it had when the table was created. It returns the table identity to a value it was given as a seed. It requires authorization to delete. It requires table alter permission. When it comes to large databases, it is much slower. It is faster. TRUNCATE vs DROP The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table”s records, whereas DROP command deletes the table entirely from the database. However, there are still some differences between these commands, which are summarized in the following table − DROP TRUNCATE The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. It is a DDL(Data Definition Language) command. It is also a DDL(Data Definition Language) command. The table space is

MySQL – Show Users Table of content The MySQL SHOW Users Show Current User Show Currently Logged in Users Show Users Using a Client Program ”; Previous Next 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 − Host User User_attr account_locked localhost mysql.infoschema NULL Y localhost mysql.session NULL Y localhost mysql.sys NULL Y localhost root NULL N 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 − user() root@localhost 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 − current_user() root@localhost 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 − Field Type Null Key Default Extra ID bigint unsigned NO USER varchar(32) NO HOST varchar(261) NO DB varchar(64) YES COMMAND varchar(16) NO TIME int NO STATE varchar(64) YES INFO varchar(65535) YES 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 − user host db command root localhost:49958 customers Query event_scheduler localhost NULL Daemon 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 − PHP NodeJS Java Python To display info regarding user(s) in a MySQL database using a PHP program, we need to execute the SELECT USER statement using the query() function of the PHP mysqli library as − $sql = “SELECT USER FROM MYSQL.user”; $mysqli->query($sql); To display the user information We need to execute the SELECT * FROM statement using the query() function of mysql2 library using JavaScript (NodeJS) program as follows − sql= “SELECT * FROM mysql.user”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); Similarly in Java we can use the JDBC executeQuery() function to execute the SQL query that displays the user info as follows − String sql =

MySQL – REVOKE Statement Table of content The MySQ REVOKE statement Revoking All Privileges Revoking Proxy Privilege Revoking a Role Revoking Privileges Using a Client Program ”; Previous Next Earlier, we discussed how a root user gets access to a MySQL server with default privileges after installation. These privileges are sufficient for performing basic operations on the data. However, in some special situations, users might need to request the server”s host to take away certain privileges. To do so, we use the MySQL REVOKE statement. The MySQ REVOKE statement The MySQL REVOKE statement is used to remove certain administrative privileges or roles from users. It revokes permissions that were previously granted. Syntax Following is the syntax of the MySQL REVOKE Statement − REVOKE privileges ON database_name.table_name FROM ”user”@”host”; Example Assume we have created a user named ”test_user”@”localhost” in MySQL using the CREATE USER statement as shown below − CREATE USER ”test_user”@”localhost” IDENTIFIED BY ”testpassword”; Following is the output produced − Query OK, 0 rows affected (0.23 sec) Now, let us create a database named ”test_database” − CREATE DATABASE test_database; The output produced is as follows − Query OK, 1 row affected (0.56 sec) Next, we will use the created database − USE test_database; We get the output as shown below − Database changed Now, let us create a table in the database − CREATE TABLE MyTable(data VARCHAR(255)); The output obtained is as follows − Query OK, 0 rows affected (0.67 sec) Following query grants privileges on the table created above to the user ”test_user”@”localhost − GRANT SELECT ON test_database.MyTable TO ”test_user”@”localhost”; After executing the above code, we get the following output − Query OK, 0 rows affected (0.31 sec) You can verify the granted privileges using the SHOW GRANTS statements − SHOW GRANTS FOR ”test_user”@”localhost”; The output we get is as shown below − Grants for test_user@localhost GRANT USAGE ON *.* TO `test_user`@`localhost` GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost` Now, you can revoke the above granted privilege using the REVOKE statement as shown below − REVOKE SELECT ON test_database.MyTable FROM ”test_user”@”localhost”; We get the output as follows − Query OK, 0 rows affected (0.25 sec) Verification We can verify whether the SELECT privilege has been revoked or not using the SHOW GRANTS statements as shown below − SHOW GRANTS FOR ”test_user”@”localhost”; We can see that the output no longer lists the SELECT privilege, indicating that it has been revoked − Grants for test_user@localhost GRANT USAGE ON *.* TO `test_user`@`localhost` Revoking All Privileges If a user has multiple privileges with a user, you can revoke all those privileges at once using the REVOKE ALL statement in MySQL. Syntax Following is the syntax to revoke all privileges in MySQL − REVOKE ALL PRIVILEGES ON *.* FROM ”user”@”host”; Example Assume we have created a user as follows − CREATE USER ”sample_user”@”localhost”; Following is the output produced − Query OK, 0 rows affected (0.18 sec) We also create a procedure as shown below − DELIMITER // CREATE PROCEDURE sample () BEGIN SELECT ”This is a sample procedure”; END// DELIMITER ; The output obtained is as follows − Query OK, 0 rows affected (0.29 sec) Additionally, we create a table named ”sample” in a database − CREATE TABLE sample(data INT); We get the output as shown below − Query OK, 0 rows affected (0.68 sec) Now, the following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named ”sample_user”@”localhost”. GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE test_database.sample TO ”sample_user”@”localhost”; Output of the above code is as shown below − Query OK, 0 rows affected (0.20 sec) Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table ”sample” to the user ”sample_user”@”localhost − GRANT SELECT, INSERT, UPDATE ON test.sample TO ”sample_user”@”localhost”; The result produced is − Query OK, 0 rows affected (0.14 sec) You can verify the list of all privileges granted for the user using the SHOW GRANTS statement − SHOW GRANTS FOR ”sample_user”@”localhost”; The result obtained is as follows − Grants for sample_user@localhost GRANT USAGE ON *.* TO `sample_user`@`localhost` GRANT SELECT, INSERT, UPDATE ON `test`.`sample` TO `sample_user`@`localhost` GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test_database`.`sample` TO `sample_user`@`localhost` Finally, to revoke all the privileges granted to ”sample_user”@”localhost”, you can use the following statement − REVOKE ALL PRIVILEGES, GRANT OPTION FROM ”sample_user”@”localhost”; The result produced is − Query OK, 0 rows affected (0.30 sec) Verification After revoking privileges, you can check the user”s grants again − SHOW GRANTS FOR ”sample_user”@”localhost”; The output below confirms that all privileges have been revoked − Grants for sample_user@localhost GRANT USAGE ON *.* TO `sample_user`@`localhost` Revoking Proxy Privilege You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges. Example Assume we have created users named sample_user, proxy_user in MySQL using the CREATE statement − CREATE USER sample_user, proxy_user IDENTIFIED BY ”testpassword”; Following is the output obtained − Query OK, 0 rows affected (0.52

MySQL – Drop User Table of content The MySQL Drop User Statement Removing Multiple Users The IF EXISTS clause Dropping User Using a Client Program ”; Previous Next 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 − user TestUser mysql.infoschema mysql.session mysql.sys newUser root sample 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 − user mysql.infoschema mysql.session mysql.sys newUser root sample 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 − PHP NodeJS Java Python The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. To drop a user from a MySQL database, we need to execute the DROP USER statement using this function as − $sql = “DROP USER ”username”@”localhost””; $mysqli->query($sql); To drop a user using a NodeJS program, we need to execute the DROP USER statement using the function named query() as − sql= “DROP USER [IF EXISTS] user_name …”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); To drop an user in a MySQL database using Java program, we need to execute the DROP USER statement using the JDBC function named execute() as − String sql = “DROP USER “USER_NAME”@”LOCALHOST””; statement.execute(sql); The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. To drop a user from a MySQL dataBase, we need to execute the DROP USER statement using this function as − sql = “DROP USER ”UserName”@”localhost””; cursorObj.execute(sql); Example Following are the client programs to drop an user in MySQL − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); }

MySQL – Change Password Table of content MySQL Change User Password The UPDATE Statement The SET PASSWORD statement The ALTER USER Statement Changing User password Using a Client Program ”; Previous Next 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 − User mysql.infoschema mysql.session mysql.sys root sample 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 − PHP NodeJS Java Python To change the user”s password MySQL database, we need to execute the ALTER USER statement using this function as − $sql = “ALTER USER ”root”@”localhost” IDENTIFIED BY ”new_password””; $mysqli->query($sql); To change the user”s password MySQL, we need to execute the ALTER USER statement using the function named query() as − sql= “ALTER USER username IDENTIFIED BY ”new_password””; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); To change the user”s password into MySQL database, we need to execute the ALTER USER statement using the JDBC execute() function

MySQL – Lock User Account Table of content MySQL Lock User Account Locking Existing Accounts Locking User Account Using a Client Program ”; Previous Next Account Locking in MySQL is introduced to increase security of the database by preventing unauthorized transactions or suspicious activities. In many cases, the MySQL user accounts require to be locked for various reasons. For instance, to wait while completing the authorization of an account, or if the account has been inactive for a very long time, etc. In such cases, locking accounts will improve the efficiency of the MySQL server. MySQL Lock User Account To check whether an account is locked or not, MySQL provides the ”account_locked” attribute in the ”mysql.user” table that will hold either ”Y” or ”N” values respectively. A value of ”Y” indicates that the account is locked, while ”N” indicates that it is not locked. Locking New Accounts MySQL provides ACCOUNT LOCK clause to lock the accounts. Using this clause with CREATE USER and ALTER USER statements will either create a new already locked user or lock the existing user respectively. Syntax Following is the syntax of CREATE USER… ACCOUNT LOCK statement − CREATE USER username@hostname IDENTIFIED BY ”new_password” ACCOUNT LOCK; Example In the following query, we are creating a new already-locked user account in MySQL using the CREATE USER statement − CREATE USER test@localhost IDENTIFIED BY ”asdfgh” ACCOUNT LOCK; Output Following is the output of the above code − Query OK, 0 rows affected (0.02 sec) Verification We can verify whether the account of the ”test” user is locked or not using the following SELECT statement − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”test”; Output of the above code is as shown below − User Host account_locked test localhost Y Since the account is locked, you cannot access it unless it is unlocked again. Look at the example below − C:WindowsSystem32> mysql -u test -p Enter password: ****** The result produced is as follows − ERROR 3118 (HY000): Access denied for user ”test”@”localhost”. Account is locked. Locking Existing Accounts We can use the ALTER USER… ACCOUNT LOCK statement to lock existing accounts in MySQL. But you must make sure that the user is in the unlock state before executing the query. Syntax Following is the syntax of ALTER USER… ACCOUNT LOCK statement − ALTER USER username@hostname ACCOUNT LOCK; Example In here, we are locking an existing user account in MySQL using the ALTER USER statement − ALTER USER sample@localhost ACCOUNT LOCK; Output Output of the above code is as follows − Query OK, 0 rows affected (0.00 sec) Verification We can verify whether the account of the ”sample” user is locked or not using the following SELECT statement − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”sample”; The result obtained is as shown below − User Host account_locked sample localhost Y To verify that the account is locked, let us access it as shown in the query below − C:WindowsSystem32> mysql -u sample -p Enter password: ****** We get the output as follows − ERROR 3118 (HY000): Access denied for user ”sample”@”localhost”. Account is locked. Locking User Account Using a Client Program Now, in this section let us discuss how to lock a MySQL user using various client programs. Syntax Following are the syntaxes − PHP NodeJS Java Python Following is the syntax to lock the MySQL user account using PHP − $sql = “CREATE USER user_name IDENTIFIED BY ”password” ACCOUNT LOCK”; Or, $sql = “ALTER USER user_name@localhost IDENTIFIED BY ”password” ACCOUNT LOCK”; $mysqli->query($sql); Following is the syntax to lock the MySQL user account using JavaScript − sql= “CREATE USER username@hostname IDENTIFIED BY ”new_password” ACCOUNT LOCK”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); Following is the syntax to lock the MySQL user account using Java − String sql = “ALTER USER USER_NAME@LOCALHOST IDENTIFIED BY ”password” ACCOUNT LOCK”; Or, String sql = “CREATE USER USER_NAME IDENTIFIED BY ”password” ACCOUNT LOCK”; statement.execute(sql); Following is the syntax to lock the MySQL user account using Python − sql = f”ALTER USER ”{username_to_lock}”@”localhost” ACCOUNT LOCK”; cursorObj.execute(sql); Example Following are the programs to lock users in various programming languages − PHP NodeJS Java Python $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 Sarika IDENTIFIED BY ”password” ACCOUNT LOCK;”; if($mysqli->query($sql)){ printf(“User has been locked successfully..!”); } if($mysqli->error){ printf(“Failed..!” , $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − User has been locked 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 = “CREATE USER test@localhost IDENTIFIED BY ”asdfgh” ACCOUNT LOCK;” con.query(sql); sql = “SELECT User, Host, account_locked FROM mysql.user WHERE User = ”test”;”; con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); }); Output The output produced is as follows − Connected! ————————– [ { User: ”test”, Host: ”localhost”, account_locked: ”Y” } ] import java.sql.Connection; import

MySQL – Unlock User Account Table of content MySQL Unlock User Account Unlocking New Accounts Unlocking Existing Accounts Unlock User Account Using a Client Program ”; Previous Next Account Locking and Unlocking in MySQL is introduced to increase security of the database by preventing unauthorized transactions or suspicious activities. MySQL Unlock User Account To check whether an account is unlocked or not, MySQL provides the ”account_locked” attribute in the ”mysql.user” table that will hold either ”Y” or ”N” values respectively. If the attribute holds the ”N” value, then the account is said to be in the unlock mode. By default, all the new user accounts created in MySQL are unlocked. Unlocking New Accounts You can use the CREATE USER… ACCOUNT UNLOCK statement to unlock new accounts created in MySQL. By default, the newly created accounts are always unlocked unless specified otherwise. However, the ACCOUNT UNLOCK clause is mostly used when an account is in the locked state. Syntax Following is the syntax of CREATE USER… ACCOUNT UNLOCK statement − CREATE USER username@hostname IDENTIFIED BY ”new_password” ACCOUNT UNLOCK; Example In the following query, we are creating a new user account in MySQL using the CREATE USER statement − CREATE USER testuser@localhost IDENTIFIED BY ”qwerty” ACCOUNT UNLOCK; Output Following is the output of the above code − Query OK, 0 rows affected (0.02 sec) Verification We can verify whether the account of the ”testuser” is unlocked or not using the following SELECT statement − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”testuser”; Output of the above code is as shown below − User Host account_locked testuser localhost N Example As we have learned above, the newly created user accounts are unlocked by default. Look at the example below − CREATE USER demo@localhost IDENTIFIED BY ”000000”; Output The result produced is as follows − Query OK, 0 rows affected (0.02 sec) Verification We can verify whether the newly created account is unlocked by default using the following SELECT statement − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”demo”; The output obtained is as follows − User Host account_locked demo localhost N Unlocking Existing Accounts We can use the ALTER USER… ACCOUNT UNLOCK statement unlock existing accounts in MySQL that are locked beforehand. Syntax Following is the syntax of ALTER USER… ACCOUNT UNLOCK statement − ALTER USER username@hostname ACCOUNT UNLOCK; Example We are first retrieving the information of the existing account ”sample”, including its username, host, and the status of its account lock − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”sample”; We can see in the output below that the user account is locked − User Host account_locked test localhost Y Now, we will unlock the existing account ”sample” using the ALTER USER statement − ALTER USER sample@localhost ACCOUNT UNLOCK; Output Following is the output of the above query − Query OK, 0 rows affected (0.00 sec) Verification We can verify whether the account is unlocked or not using the following SELECT query − SELECT User, Host, account_locked FROM mysql.user WHERE User = ”sample”; As we can see in the below output, the ”sample@localhost” account is now unlocked and can be accessed according to its privileges − User Host account_locked sample localhost N Unlock User Account Using a Client Program Now, in this section let us discuss how to unlock a MySQL user using various client programs. Syntax Following are the syntaxes − PHP NodeJS Java Python Following is the syntax to unlock a MySQL user account using PHP − $sql = “ALTER USER user_name ACCOUNT UNLOCK”; $mysqli->query($sql); Following is the syntax to unlock a MySQL user account using JavaScript − sql= “CREATE USER username@hostname IDENTIFIED BY ”new_password” ACCOUNT UNLOCK”; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); Following is the syntax to unlock a MySQL user account using Java − String sql = “ALTER USER USER_NAME@LOCALHOST ACCOUNT UNLOCK”; statement.execute(sql); Following is the syntax to unlock a MySQL user account using Python − sql = f”ALTER USER ”{username_to_unlock}”@”localhost” ACCOUNT UNLOCK”; cursorObj.execute(sql); Example Following are the programs to unlock users in various programming languages − PHP NodeJS Java Python $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 Sarika ACCOUNT UNLOCK”; if($mysqli->query($sql)){ printf(“User has been unlocked successfully..!”); } if($mysqli->error){ printf(“Failed..!” , $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − User has been unlocked 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 = “CREATE USER testuser@localhost IDENTIFIED BY ”qwerty” ACCOUNT UNLOCK;” con.query(sql); sql = “SELECT User, Host, account_locked FROM mysql.user WHERE User = ”testuser”;”; con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); }); Output The output produced is as follows − Connected! ————————–

MySQL – Create Tables Table of content MySQL Create Table Statement Creating Tables from Command Prompt Creating a Table from an Existing Table The IF NOT EXISTS clause Create table into MySQL Database Using a Client Program ”; Previous Next In the MySQL relational database system, SQL is used to store data in the form of structured tables. These tables consist of fields and records. A field represents a column that defines the type of data to be stored in a table, and a record is a row containing the actual data. MySQL provides various queries to interact with the data, allowing you to create tables, update them, delete them, etc. MySQL Create Table Statement To create a table in MySQL RDBMS in prompt, CREATE TABLE statement is used. One can create any number of tables in an SQL Server database. However, a limit exists on the number of objects that can be present in a database. Including tables, views, indexes etc., a database cannot exceed 2,147,483,647 objects. Therefore, a single user-defined table can define a maximum of 1024 columns. A MySQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column”s data type. Note that each table must be uniquely named in a database. To begin with, the table creation command requires the following details − Name of the table. Name of the columns. Definitions for each column. Syntax Following is the basic SQL syntax to create a MySQL table − CREATE TABLE table_name( column1 datatype, column2 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE Statement − 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, a few items need explanation − Field Attribute AUTO_INCREMENT in MySQL automatically increments the value in the ID column by one for each new record you add. It starts from the next available number. Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user tries to create a record with a NULL value in that field, then MySQL will raise an error. Keyword PRIMARY KEY is used to define a column as a primary key. It ensures that every record in that column is unique. You can also use it for multiple columns by separating them with commas. Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.03 sec) Verification Once we have finished creating the table, we can check whether it has been created successfully or not using the following query − DESC CUSTOMERS; The above query displays the structure of the CUSTOMERS table: column names, their datatypes, etc. Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Creating Tables from Command Prompt We can create a MySQL table from the command prompt by defining its structure and columns. Following are the steps to perform to create a MySQL table from Command Prompt: Firstly, open the command prompt and enter the following command: mysql -u root -p to access the MySQL database management system. After entering the command, enter the password to log into the MySQL server. Then, we can start creating a table using the respected SQL CREATE TABLE query. Example In the following example, we are creating a MySQL table named CUSTOMERS from command prompt. CREATE TABLE CUSTOMERS ( NAME VARCHAR(20) NOT NULL, ID INT AUTO_INCREMENT, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command. Output When we execute the above query, the output is obtained as follows − Query OK, 0 rows affected (0.03 sec) Verification We can verify if the table has been created successfully or not using the following query − mysql> DESC CUSTOMERS; The above query will show the structure and description of the CUSTOMERS table − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Creating a Table from an Existing Table We can create a duplicate table of an existing table including its structure and data, by using the SQL CREATE TABLE and SELECT statements. The duplicate table has the same columns and their definitions, and it also gets filled with the same data as the original table. Note − As it is a completely new table, any changes made in it

MySQL – Show Privileges Table of content The MySQL SHOW Privileges Listing Privileges Using a Client Program ”; Previous Next The users in MySQL must have enough privileges to interact with the server. This is possible by assigning authentication details, like passwords to the users. In addition to this, operational or administrative privileges are granted separately if a user wants to interact with and operate on the data. The MySQL SHOW Privileges The MySQL SHOW PRIVILEGES Statement displays the list of privileges that are supported by the MYSQL server. The displayed list includes all static and currently registered dynamic privileges. The information (returned list) contains three columns − Privilege − Name of the privilege Context − Name of the MySQL object for which the privilege is applicable. Comment − A string value describing the purpose of the privilege. Syntax Following is the syntax to list out all privileges in a MySQL Server − SHOW PRIVILEGES; Example Following query lists out all the privileges supported by the MySQL server − SHOW PRIVILEGES Output After executing the above code, we get the following output − Privilege Context Comment Alter Tables To alter the table Alter routine Functions, Procedures To alter or drop stored functions/procedures Create Databases, Tables, Indexes To create new databases and tables Create routine Databases To use CREATE FUNCTION/PROCEDURE Create role Server Admin To create new roles Create temporary tables Databases To use CREATE TEMPORARY TABLE Create view Tables To create new views Create user Server Admin To create new users Delete Tables To delete existing rows Drop Databases, Tables To drop databases, tables, and views Drop role Server Admin To drop roles Event Server Admin To create, alter, drop and execute events Execute Functions, Procedures To execute stored routines File File access on server To read and write files on the server Grant option Databases, Tables, Funcs, Procedures To give to other users those privileges you possess Index Tables To create or drop indexes Insert Tables To insert data into tables Lock tables Databases To use LOCK TABLES (together with SELECT privilege) Process Server Admin To view the plain text of currently executing queries Proxy Server Admin To make proxy user possible References Databases,Tables To have references on tables Reload Server Admin To reload or refresh tables, logs and privileges Replication client Server Admin To ask where the slave or master servers are Replication slave Server Admin To read binary log events from the master Select Tables To retrieve rows from table Show databases Server Admin To see all databases with SHOW DATABASES Show view Tables To see views with SHOW CREATE VIEW Shutdown Server Admin To shut down the server Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. Trigger Tables To use triggers Create tablespace Server Admin To create/alter/drop tablespaces Update Tables To update existing rows Usage Server Admin No privileges – allow connect only BINLOG_ENCRYPTION_ADMIN Server Admin AUDIT_ADMIN Server Admin ENCRYPTION_KEY_ADMIN Server Admin INNODB_REDO_LOG_ARCHIVE Server Admin APPLICATION_PASSWORD_ADMIN Server Admin SHOW_ROUTINE Server Admin BACKUP_ADMIN Server Admin BINLOG_ADMIN Server Admin CLONE_ADMIN Server Admin CONNECTION_ADMIN Server Admin SET_USER_ID Server Admin SERVICE_CONNECTION_ADMIN Server Admin GROUP_REPLICATION_ADMIN Server Admin REPLICATION_APPLIER Server Admin INNODB_REDO_LOG_ENABLE Server Admin PERSIST_RO_VARIABLES_ADMIN Server Admin TABLE_ENCRYPTION_ADMIN Server Admin ROLE_ADMIN Server Admin REPLICATION_SLAVE_ADMIN Server Admin SESSION_VARIABLES_ADMIN Server Admin RESOURCE_GROUP_ADMIN Server Admin RESOURCE_GROUP_USER Server Admin SYSTEM_USER Server Admin SYSTEM_VARIABLES_ADMIN Server Admin XA_RECOVER_ADMIN Server Admin Listing Privileges Using a Client Program Now, let us see how to retrieve/list all the privileges granted to the current MySQL user using a client program in programming languages like Java, PHP, Python, JavaScript, C++ etc. Syntax Following are the syntaxes − PHP NodeJS Java Python To show all the privileges granted to an user, we need to pass the SHOW PRIVILEGES statement as a parameter to the query() function of the PHP mysqli library as − $sql = “SHOW PRIVILEGES”; $mysqli->query($sql); Following is the syntax to show all the privileges granted to the

MySQL – Database Import Table of content Importing Backup Data ”; Previous Next 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”. Tables_in_testdb customers 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. 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 Print Page Previous Next Advertisements ”;