Category: mysql

MySQL – Create Users Table of content The MySQL CREATE USERS Statement Granting Privileges in MySQL Logging as a Different User The Expire Clause User Comment User Attribute The IF NOT EXISTS Clause Creating User Using a Client Program ”; Previous Next 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 − USER mysql.infoschema mysql.session mysql.sys myuser openkm root sample 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

MySQL – Useful Resources ”; Previous Next The following resources contain additional information on MySQL. Please use them to get more in-depth knowledge on this topic. MySQL Databases With Python Course 25 Lectures 2 hours John Elder More Detail PHP for Beginners 2022: The Complete PHP MySQL PDO Course 74 Lectures 7.5 hours Web Coding More Detail Fundamentals of MySQL with 2 Real World Projects 38 Lectures 4.5 hours Stone River ELearning More Detail Print Page Previous Next Advertisements ”;

MySQL – Database Info Table of content Obtaining Database Info from MySQL Prompt Obtaining the Number of Rows Affected by a Query Listing Tables and Databases Getting Server Metadata ”; Previous Next 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 − Database information_schema mysql performance_schema tutorials tutorials_copy 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 − Tables_in_tutorials customers employees students 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 − 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 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

MySQL – Database Export Table of content Exporting Database using mysqldump Exporting only Specific Tables in Database Exporting all Databases in a Host ”; Previous Next 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 − Tables_in_tutorials customers employees students 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.” Print Page Previous Next Advertisements ”;

MySQL – Standard Deviation Table of content Popular standard deviation Sample Standard Deviation ”; Previous Next MySQL Standard Deviation Functions are mathematical functions that are used to calculate the variation or dispertion between values in a dataset. There are two types of standard deviations in MySQL, they are population standard deviation and sample standard deviation. Popular standard deviation The “population standard deviation” is the square root of the variance of a set of data. It calculates the amount of variation or dispersion within a population. Symbolically it is represented by σ (the Greek letter sigma). To calculate population standard deviation, we can use the following functions: STD(expression): It calculates and returns the population standard deviation the fields in a particular column. If the specified row(s) doesn”t exist this function returns NULL. STDDEV(expression): It is same as STD() function, but it also works with oracle database. STDDEV_POP(expression): It is equivalent to STD() function. Following is the mathematical formula to calculate the “population standard deviation”: // Mathematical Formula $sigma = sqrt{frac{sum_{i=1}^n{(x-bar x)^2}}{N-1}}$ Where, σ = population standard deviation N = size of the population Xi = each value from the population meu = the population mean Example First, let us create a table with the name CUSTOMERS using the CREATE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) ); Now, let us insert values into the CUSTOMERS table using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2000), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); The table is created as − 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 The STD() Function The following query calculates the population standard deviation of scores of all players in exhibition match − SELECT STD(AGE) from CUSTOMERS; Output Following is the output − STD(AGE) 3.063944369932459 The STDDEV() Function The STDDEV() function is the same as STD() function, but it will also work with oracle database. In the following query, we are calculating the population standard deviation on “Score_In_Exhibition_Match” column − SELECT STDDEV(AGE) FROM CUSTOMERS; Output The output is produced as follows − STDDEV(AGE) 3.063944369932459 The STDDEV_POP() Function In MySQL, the STDDEV_POP() function is equivalent to the STD() function. Here, we are performing the population standard deviation on AGE column of CUSTOMERS table. SELECT STDDEV_POP(AGE) FROM CUSTOMERS; Output The output is displayed as follows − STDDEV_POP(AGE) 3.063944369932459 Sample Standard Deviation The MySQL standard deviation is the square root of the variance, which calculates how dispersed or spread out the data is. The STDDEV_SAMP() function is used to calculate the sample standard deviation of a set of values in a column. Following is the formula to calculate the “Sample Standard Deviation”: // Mathematical formula s = sqrt(sum((x – mean)^2) / (n – 1)) Example In the following example, let us calculate the sample standard deviation on the AGE column of the previously created CUSTOMERS table − SELECT STDDEV_SAMP(AGE) FROM CUSTOMERS; Output The output is displayed as follows − STDDEV_SAMP(AGE) 3.309438162646486 Print Page Previous Next Advertisements ”;

MySQL – Copy Database Table of content Copy Database in MySQL Creating Copy database (Manually) Copy Database Without MySQLdump ”; Previous Next 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. Database information_schema mysql performance_schema testdb 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. Tables_in_testdb customers 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

MySQL – ROLLUP Table of content The MySQL ROLLUP Clause ROLLUP on Multiple Columns Rollup Using Client Program ”; Previous Next The MySQL ROLLUP Clause The MySQL ROLLUP Clause is an extension of the GROUP BY Clause. It is used with aggregate functions in MySQL to find the grand total or a summary of a column”s values (also known as super-aggregate of a column), in an extra row within a table. Consider a manufacturing factory that tracks monthly production data in a table. To determine the annual product production, you can use the SUM() aggregate function along with ROLLUP. However, if you need to find out the number of months where production falls below a specific threshold, ROLLUP will allow you to count such months as well using the COUNT() function. Syntax Following is the syntax of ROLLUP clause in MySQL − SELECT AggregateFunction(column_name(s)), column_name(s) FROM table_name GROUP BY column_name(s) WITH ROLLUP; Example First, we will create a table named “PRODUCT” containing production information such as product ID, product name, product count, and manufacturing month within an organization − CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) ); Now, let us insert some data into the above-created table − INSERT INTO PRODUCT VALUES (101, ”Comb”, 2345, ”January”), (102, ”Coffee Mugs”, 1242, ”January”), (103, ”Cutlery”, 124, ”January”), (101, ”Comb”, 3263, ”February”), (102, ”Coffee Mugs”, 10982, ”February”), (103, ”Cutlery”, 435, ”February”); The PRODUCT table obtained is as follows − PRODUCT_ID PRODUCT_NAME PRODUCT_COUNT MONTH 101 Comb 2345 January 102 Coffee Mugs 1242 January 103 Cutlery 124 January 101 Comb 3263 February 102 Coffee Mugs 10982 February 103 Cutlery 435 February Now, let us to find the sum of products manufactured each MONTH using ROLLUP as shown below − SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP; Output you can observe in the output below that the individual product counts for both January and February are calculated, and the grand total of total production is displayed in the third row using ROLLUP − SUM(PRODUCT_COUNT) MONTH 14680 February 3711 January 18391 NULL ROLLUP on Multiple Columns You can also use ROLLUP on multiple columns by grouping them together using GROUP BY clause. Example Here, we are applying the GROUP BY clause on columns ”PRODUCT_ID” and ”PRODUCT_NAME” of the PRODUCT table − SELECT PRODUCT_ID, COUNT(PRODUCT_ID) AS PROD_ID_COUNT, PRODUCT_NAME, COUNT(PRODUCT_NAME) AS PROD_ID_NAME FROM PRODUCT GROUP BY PRODUCT_ID, PRODUCT_NAME; We get the following output − PRODUCT_ID PROD_ID_COUNT PRODUCT_NAME PROD_ID_NAME 101 2 Comb 2 102 2 Coffee Mugs 2 103 2 Cutlery 2 Now, calculate the summary of these two rows using ROLLUP as shown in the following query − SELECT PRODUCT_ID, COUNT(PRODUCT_ID) AS PROD_ID_COUNT, PRODUCT_NAME, COUNT(PRODUCT_NAME) AS PROD_ID_NAME FROM PRODUCT GROUP BY PRODUCT_ID, PRODUCT_NAME WITH ROLLUP; You can see in the output below that the summary is calculated not only at the final level but also at two levels. For every product name, a column summary is displayed − PRODUCT_ID PROD_ID_COUNT PRODUCT_NAME PROD_ID_NAME 101 2 Comb 2 101 2 NULL 2 102 2 Coffee Mugs 2 102 2 NULL 2 103 2 Cutlery 2 103 2 NULL 2 NULL 6 NULL 6 Rollup Using Client Program We can also perform rollup Using Client Program. Syntax PHP NodeJS Java Python To calculate grand total of a column through a PHP program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the mysqli function query() as follows − $sql = “SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP”; $mysqli->query($sql); To calculate grand total of a column through a JavaScript program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows − sql = “SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP”; con.query(sql); To calculate grand total of a column through a Java program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows − String sql = “SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP”; statement.executeQuery(sql); To calculate grand total of a column through a Python program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows − rollup_query = “SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP” cursorObj.execute(rollup_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql =

MySQL – Connection Table of content Set Password to MySQL Root MySQL Connection Using MySQL Binary MySQL Connection Using PHP Script ”; Previous Next While working with MySQL database, we use a client program to communicate with the database server. To do so, we must first establish a connection between them. To connect a client program with MySQL server, we must ensure all the connection parameters are properly used. These parameters work just like any other login parameters: consisting of a username and a password. Where, a username is the name of the host where the server is running and a password needs to be set according to the user. Generally, each connection parameter holds a default value, but we can override them either on the command line or in an option file. This tutorial only uses the mysql client program to demonstrate the connection, but these principles also apply to other clients such as mysqldump, mysqladmin, or mysqlshow. Set Password to MySQL Root Usually, during the installation of MySQL server, we will be asked to set an initial password to the root. Other than that, we can also set the initial password using the following command − mysql -u root password “new_password”; Where, new_password is the password set initially. Reset Password We can also change the existing password using the SET PASSWORD statement. However, we can only do so after logging in to the user account using the existing password. Look at the query below − SET PASSWORD FOR ”root”@”localhost” = PASSWORD(”password_name”); FLUSH PRIVILEGES; Every time a connection is needed to be established, this password must be entered. MySQL Connection Using MySQL Binary We can establish the MySQL database using the mysql binary at the command prompt. Example Here is a simple example to connect to the MySQL server from the command prompt − [root@host]# mysql -u root -p Enter password:****** This will give us the ”mysql>” command prompt where we will be able to execute any SQL query. Following is the result of above command − The following code block shows the result of above code − Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2854760 to server version: 5.0.9 Type ”help;” or ”h” for help. Type ”c” to clear the buffer. In the above example, we have used root as a user but we can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user. We can disconnect from the MySQL database any time using the exit command at mysql> prompt. mysql> exit Bye MySQL Connection Using PHP Script We can open/establish connection to MySQL database using the PHP mysqli() constructor or, mysqli_connect() function. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure. Syntax Following is the syntax to open a MySQL connection using the constructor mysqli() − $mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket); Parameters Following are its parameters − Sr.No. Parameter & Description 1 $host Optional − The host name running the database server. If not specified, then the default value will be localhost:3306. 2 $username Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process. 3 $passwd Optional − The password of the user accessing the database. If not specified, then the default will be an empty password. 4 $dbName Optional − database name on which query is to be performed. 5 $port Optional − the port number to attempt to connect to the MySQL server. 6 $socket Optional − socket or named pipe that should be used. Closing the Connection We can disconnect from the MySQL database anytime using another PHP function close(). Following is the syntax − $mysqli->close(); Example Try the following example to connect to a MySQL server. Save the file as mysql_example.php − <html> <head> <title>Connecting MySQL Server</title> </head> <body> <?php $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”root@123”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s<br />”, $mysqli->connect_error); exit(); } printf(”Connected successfully.<br />”); $mysqli->close(); ?> </body> </html> Output Access the mysql_example.php deployed on apache web server and verify the output. Connected successfully. Print Page Previous Next Advertisements ”;

MySQL – SHOW Databases Table of content MySQL SHOW Databases Statement MySQL SHOW SCHEMAS Statement Showing Databases Using a Client Program ”; Previous Next 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. Database information_schema mysql performance_schema tutorials 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 − Database information_schema mysql performance_schema tutorials 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 − PHP NodeJS Java Python To show the list of all databases present in current MySQL through PHP program, we need to execute the SHOW DATABASES statement using the mysqli function query() as follows − $sql = “SHOW Database_name”; $mysqli->query($sql); To show the list of all databases present in current MySQL through Node.js program, we need to execute the SHOW DATABASES statement using the query() function of the mysql2 library as follows − sql= “SHOW {DATABASES | SCHEMAS} LIKE ”pattern” | WHERE expr]”; con.query(sql); To show the list of all databases present in current MySQL through Java program, we need to execute the SHOW DATABASES statement using the JDBC function executeUpdate() as follows − String sql = “SHOW Database_name”; st.executeQuery(sql); To show the list of all databases present in current MySQL through Python program, we need to execute the SHOW DATABASES statement using the execute() function of the MySQL Connector/Python as follows − sql = “SHOW Database_name”; cursorObj.execute(sql) Example Following are the programs − 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.”); 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(); Output The output obtained is as follows − 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) }); }); Output The output produced is as follows − 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(); } } } Output The output obtained is as shown below − 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” ) #

MySQL – Show Processlist Table of content What is MySQL Process List? The MySQL SHOW PROCESSLIST Command Showing Process List Using Client Program ”; Previous Next MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. A process is defined as the operations performed by a user on the MySQL Server. Multiple processes can be run on a MySQL Server concurrently by multiple users. What is MySQL Process List? The MySQL process list is defined as the list of operations currently being performed by the set of user threads executing within the server. If a user has the PROCESS privilege, they can see all threads in a server, including threads of other users. But if a user does not have such privilege, non-anonymous users have access to information about their own threads only; while anonymous users have no access to thread information. To retrieve information about these processes running on a MySQL Server, we can use the SHOW PROCESSLIST command. The MySQL SHOW PROCESSLIST Command The MySQL SHOW PROCESSLIST command is used to display information about the current processes running on a MySQL Server. This statement is especially useful when dealing with a “too many connections” error, as it provides details about these connections and their operations. Additionally, MySQL reserves one extra connection for administrators with CONNECTION_ADMIN privilege (or SUPER privilege in older versions), to ensure they can always access the system. Syntax Following is the syntax of the SHOW PROCESSLIST Command − SHOW [FULL] PROCESSLIST Here, the FULL keyword is optional. But if you omit the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field. Example Let us see an example to show the usage of SHOW PROCESSLIST command. We will use the ”G” delimiter to print the information table vertically − SHOW PROCESSLISTG Output Following is the output obtained − *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 1065209 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 56 User: root Host: localhost:51995 db: customers Command: Query Time: 0 State: init Info: SHOW PROCESSLIST 2 rows in set (0.00 sec) Example Now, let us also use the FULL keyword with the SHOW PROCESSLIST command as shown in the following example − SHOW FULL PROCESSLISTG Output The output obtained is as shown below − *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 1065138 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 56 User: root Host: localhost:51995 db: customers Command: Query Time: 0 State: init Info: SHOW FULL PROCESSLIST 2 rows in set (0.00 sec) Output Explanation The output result-set obtained from the SHOW PROCESSLIST command has the following columns − Id − It is the identity of a connection. User − This holds the name of a MySQL user who issued the statement. Host − The host name of the client issuing the statement (except for system user, as there is no host for it). The host name for TCP/IP connections is represented in “host_name:client_port” format to make it easier to determine the actions of a client. db − This is the default database for the thread, or NULL if none has been selected. Command − Shows the type of command the corresponding thread is executing on behalf of the client, or shows Sleep if the session is idle. Time − The time in seconds that the thread has been in its current state. State − An action, event, or state that indicates what the thread is doing. Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated. Info − The statement the thread is executing. If it is executing no statement, NULL is shown. Showing Process List Using Client Program We can also show the process list using Client Program. Syntax PHP NodeJS Java Python To retrieve information about processes running on a MySQL Server, through a PHP program, we need to execute the “SHOW PROCESSLIST” command using the mysqli function query() as follows − $sql = “SHOW PROCESSLIST”; $mysqli->query($sql); To retrieve information about processes running on a MySQL Server, through a JavaScript program, we need to execute the “SHOW PROCESSLIST” command using the query() function of mysql2 library as follows − sql = “SHOW PROCESSLIST”; con.query(sql) To retrieve information about processes running on a MySQL Server, through a Java program, we need to execute the “SHOW PROCESSLIST” command using the JDBC function executeQuery() as follows − String sql = “SHOW PROCESSLIST”; statement.executeQuery(sql); To retrieve information about processes running on a MySQL Server, through a Python program, we need to execute the “SHOW PROCESSLIST” command using the execute() function of the MySQL Connector/Python as follows − show_processlist_query = “SHOW PROCESSLIST” cursorObj.execute(show_processlist_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql = “SHOW PROCESSLIST”; if($result = $mysqli->query($sql)){ printf(“Show query executed successfully…!n”); printf(“Process list: n”); while($row = mysqli_fetch_array($result)){ print_r($row); } } if($mysqli->error){ printf(“Error message: “, $mysqli->error); } $mysqli->close(); Output The output obtained is as shown below − Show query executed