Category: mysql

MySQL – Having Clause Table of content MySQL Having Clause HAVING clause with ORDER BY clause HAVING clause with COUNT() function HAVING clause with AVG() function HAVING clause with MAX() function Having Clause Using a Client Program ”; Previous Next MySQL Having Clause The MySQL HAVING Clause is used to filter grouped rows in a table based on conditions. This clause is used with the GROUP BY clause to group the rows based on one or more columns and then filter them based on the conditions specified in the HAVING clause. So, the HAVING clause must always be followed by the GROUP BY clause. The HAVING clause was added to MySQL because the WHERE keyword cannot be used with aggregate functions such as COUNT(), SUM(), AVG(), etc. This clause is similar to the MySQL WHERE clause. The difference between both of them is that the WHERE clause filters individual rows in a table, whereas the HAVING clause filters grouped rows based on conditions. Syntax Following is the basic syntax of the HAVING clause in MySQL − SELECT column1, column2, aggregate_function(column) FROM table_name GROUP BY column1, column2, … HAVING condition ORDER BY column1, column2, …; Example Let us begin with creating a table named 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) ); The following INSERT statement 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 CUSTOMERS table is created or not − SELECT * FROM CUSTOMERS; Following is the 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 HAVING clause with ORDER BY clause In MySQL, the HAVING clause filters the groups, and the ORDER BY clause sorts the results. When we used both of them together, HAVING is executed first, then the result set is sorted according to the ORDER BY criteria. Example In the following query, we are retrieving all the records from the CUSTOMERS table where the sum of their SALARY is less than 4540, ordered by their name in ascending order − SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY) < 4540 ORDER BY NAME; Output The output for the query above is produced as given below − NAME total_salary Kaushik 2000.00 Khilan 1500.00 Komal 4500.00 Ramesh 2000.00 HAVING clause with COUNT() function We can use the MySQL HAVING clause in conjunction with the COUNT() function to filter the groups based on the number of rows they contain. Example In this query, we are fetching a record where the count of similar age is greater than or equal to 2. SELECT AGE FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2; Output There are two records in CUSTOMERS table with age 25, thus the output is 25 − AGE 25 HAVING clause with AVG() function The MySQL HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column. Example In the following query, we are trying to return the names of the customers whose salary is greater than 3000 − SELECT NAME, AVG(salary) as avg_salary FROM customers GROUP BY NAME HAVING AVG(salary) > 3000; Output The output for the query above is produced as given below − NAME avg_salary Chaitali 6500.000000 Hardik 8500.000000 Komal 4500.000000 Muffy 10000.000000 HAVING clause with MAX() function In MySQL, we can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column. Example In this query, we are retrieving the customer names whose maximum SALARY is less than 4000 − SELECT NAME, MAX(salary) as max_salary FROM customers GROUP BY NAME HAVING MAX(salary) < 4000; Output On executing the given query, the output is displayed as follows − NAME max_salary Ramesh 2000.00 Khilan 1500.00 Kaushik 2000.00 Having Clause Using a Client Program Besides using MySQL HAVING clause to filter grouped rows in a table based on conditions, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java

MySQL – MINUS Operator Table of content MySQL MINUS Operator Minus Operator Using Client Program ”; Previous Next MySQL MINUS Operator The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table. Let”s consider the following diagram to understand the MINUS operation: As we can see in the above diagram, the MINUS operator returned the table containing 1 and 3 values as output because they are the distinct values in table1 which do not exist in table2. MySQL does not support the MINUS operator, we can use the LEFT JOIN instead of the MINUS operator. Syntax Following is the basic syntax of the MINUS operator − SELECT column_lists FROM table_name WHERE (condition) MINUS SELECT column_lists FROM table_name WHERE (condition); Unfortunately MySQL does not support the MINUS operator. However we can use the MySQL JOIN clause instead of MINUS operator. Following is the syntax for JOIN clause to perform MINUS operation − SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL; Example Firstly, let us create a table named 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) ); Here, we are inserting some records into the CUSTOMERS table using the INSERT statement − INSERT INTO CUSTOMERS 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); The table will be 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 Now, let us create another table named ORDERS using the below query − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Here, we are inserting some records into the ORDERS table using the INSERT INTO statement − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is created as follows − OID DATE ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 As MySQL does not support the MINUS operator instead we can use the JOIN to perform this operation − The following query selects all the customers who do not have any orders by joining two tables − SELECT ID, NAME, AGE FROM CUSTOMERS LEFT JOIN ORDERS USING (ID) WHERE ORDERS.ID IS NULL; Output The following are the customers who do not have any orders: ID NAME AGE 1 Ramesh 32 5 Hardik 27 6 Komal 22 7 Muffy 24 Minus Operator Using Client Program In addition to executing the Minus Operator in MySQL table using an SQL query, we can also perform the another operation on a table using a client program. Syntax Following are the syntaxes of the Minus Operator in MySQL table in various programming languages − PHP NodeJS Java Python To perform the Minus Operator in MySQL through a PHP program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysqli connector. $sql = “SELECT column_lists FROM table_name WHERE (condition) LEFT JOIN SELECT column_lists FROM table_name WHERE (condition)”; $mysqli->query($sql); To perform the Minus Operator in MySQL through a JavaScript program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysql2 connector. sql = “SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL”; con.query(sql); To perform the Minus Operator in MySQL through a Java program, we need to execute an SQL statement with JOIN clause using the executeQuery() function provided by JDBC type 4 driver. String sql = “SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL”; statement.executeQuery(sql); To perform the Minus Operator in MySQL through a Python program, we need to execute an SQL statement with JOIN clause using the execute() function provided by MySQL Connector/Python. query = “SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL” cursorObj.execute(query); Example Following are the implementations of this operation in various programming languages −

Discuss MySQL ”; Previous Next MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications. This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming. Print Page Previous Next Advertisements ”;

MySQL – Statements Reference Table of content Data Definition Statements Data Manipulation Statements Transactional and Locking Statements Prepared Statements Compound Statements Variables in Stored Programs MySQL Flow Control Statements MySQL Cursors Statements MySQL Condition Handling Statements MySQL Account Management Statements MySQL Resource Group Management Statements MySQL Table Maintenance Statements MySQL Miscellaneous Statement MySQL SHOW Statements MySQL Other Administrative Statements MySQL Utility Statements ”; Previous Next Here is the list of all important MySQL statements. Each statement has been explained along with suitable example. Data Definition Statements MySQL ALTER DATABASE Statement − This statement is used to change the characteristics of an existing database. MySQL ALTER EVENT Statement − This statement is used to change the characteristics of an existing event. MySQL ALTER FUNCTION Statement − This statement is used to change the characteristics of an existing function. MySQL ALTER PROCEDURE Statement − This statement is used to change the characteristics of an existing procedure. MySQL ALTER SERVER Statement − This statement is used to change the characteristics of an existing server. MySQL ALTER TABLE Statement − This statement is used to change the characteristics of an existing table. MySQL ALTER TABLESPACE Statement − This statement is used to change the characteristics of an existing tablespace. MySQL ALTER VIEW Statement − This statement is used to change the characteristics of an existing view. MySQL CREATE DATABASE Statement − This statement is used to create a new database. MySQL CREATE EVENT Statement − This statement is used to create a new event. MySQL CREATE FUNCTION Statement − This statement is used to create a new function. MySQL CREATE INDEX Statement − This statement is used to create a new index. MySQL CREATE PROCEDURE Statement − This statement is used to create a new procedure. MySQL CREATE SERVER Statement − This statement is used to create a new server. MySQL CREATE SPATIAL REFERENCE SYSTEM Statement − This statement is used to create a new spatial reference system. MySQL CREATE TABLE Statement − This statement is used to create a new table. MySQL CREATE TABLESPACE XXX Statement − This statement is used to create a new table space. MySQL CREATE TRIGGER Statement − This statement is used to create a new trigger. MySQL CREATE VIEW Statement − This statement is used to create a new view. MySQL DROP DATABASE Statement − This statement is used to delete an existing database. MySQL DROP EVENT Statement − This statement is used to delete an existing event. MySQL DROP FUNCTION Statement − This statement is used to delete an existing function. MySQL DROP INDEX Statement − This statement is used to delete an existing index. MySQL DROP PROCEDURE Statement − This statement is used to delete an existing procedure. MySQL DROP SERVER Statement − This statement is used to delete an existing server. MySQL DROP SPATIAL REFERENCE SYSTEM Statement − This statement is used to delete an existing spacial reference system. MySQL DROP TABLE Statement − This statement is used to delete an existing table. MySQL DROP TABLESPACE Statement − This statement is used to delete an existing table space. MySQL DROP TRIGGER Statement − This statement is used to delete an existing trigger. MySQL DROP VIEW Statement − This statement is used to delete an existing view. MySQL RENAME TABLE Statement − This statement is used to rename an existing table. MySQL TRUNCATE TABLE Statement − This statement is used to remove all the records from a table. Data Manipulation Statements MySQL CALL Statement − This statement is used to call the stored procedure. MySQL DELETE Statement − This statement is use to remove records from a table. MySQL DO Statement − This statement is used execute MySQL statement. MySQL INSERT Statement − This statement is used to insert records into a table. MySQL LOAD DATA Statement − This statement loads the contents of a file into a table. MySQL LOAD XML Statement − This statement is used to load the contents of an XML file into a table. MySQL REPLACE Statement − This statement inserts a new record into a table. if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record. MySQL SELECT Statement − This statement is used to retrieve rows from MySQL tables. MySQL Subquries Statement − A subquery is a query with in a query. MySQL TABLE Statement − This statement is used to retrieve the rows and columns of a specified table. MySQL UPDATE Statement − This statement is used to update one or more records of an existing table. MySQL VALUES Statement − This statement is used to return a set of rows as a table. MySQL WITH Statement − This statement is used to create common table expressions. Transactional and Locking Statements MySQL START TRANSACTION Statement − This statement is used to start a transaction. MySQL COMMIT Statement − This statement saves the changes in the current transaction. MySQL ROLLBACK Statement − This statement undoes all changes till last commit. MySQL SAVEPOINT Statement − This statement is used to create a MySQL savepoint. MySQL Set Auto-commit Statement − This statement is used to set turn the auto-commit option on or off. MySQL ROLLBACK TO SAVEPOINT Statement − This statement undoes all the changes done by the current transaction to the last named save point. MySQL RELEASE SAVEPOINT Statement − This statement is used to release/delete the specified savepoint. MySQL LOCK INSTANCE FOR BACKUP Statement − This statement is used to create an instance level backup lock. MySQL UNLOCK INSTANCE Statement − This statement is used to release all backup locks in the current instance. MySQL LOCK TABLES Statement − This statement is used to acquire locks on MySQl tables. MySQL UNLOCK TABLES Statement − This statement is used to release locks on MySQl tables. MySQL SET TRANSACTION Statement − This statement is used to set values to the characteristics of the current transaction. Prepared

MySQL – Drop Index Table of content The MySQL DROP INDEX Statement The MySQL ALTER… DROP Statement Dropping PRIMARY KEY or UNIQUE Constraint Dropping an Index Using a Client Program ”; Previous Next The DROP statement in MySQL database is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, like indexes, it will remove them permanently along with their associated data. Therefore, we can drop any index from a database table using two different SQL DROP queries. It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required. The MySQL DROP INDEX Statement The DROP INDEX statement in MySQL is used to delete an index from a table. Syntax Following is the syntax to drop an index using DROP INDEX statement − DROP INDEX index_name ON table_name; Example In this example, we first create a new table CUSTOMERS and adding an index to one of its columns (AGE) using the following CREATE TABLE 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), INDEX(AGE) ); Now, create another index on CUSTOMERS table. We are using CREATE INDEX statement here − CREATE INDEX NAME_INDEX ON CUSTOMERS (Name); DROP INDEX Query − Then, use the following query to drop the index created above. DROP INDEX NAME_INDEX ON CUSTOMERS; Verification To verify if the index has been dropped, display the table definition using DESC query below − DESC CUSTOMERS; As we can see in the following table, the index on NAME column is dropped. Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO MUL NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL The MySQL ALTER… DROP Statement The ALTER DROP statement can also be used to drop an index in a MySQL table. This is just an alternative to the DROP INDEX statement, so it only works with the index that exists on a table. Syntax Following is the syntax of the DROP INDEX IF EXISTS in SQL − ALTER TABLE table_name DROP INDEX index_name; Example Let us see another example to drop the index from the CUSTOMERS table using the ALTER… DROP command as shown below − ALTER TABLE CUSTOMERS DROP INDEX AGE; Verification To verify if the index on AGE column has been dropped, display the table definition using DESC query below − DESC CUSTOMERS; As we can see in the following table, the index on NAME column is dropped. Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Dropping PRIMARY KEY or UNIQUE Constraint The DROP INDEX statement in MySQL does not usually drop indexes like PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP command. Syntax Following is the syntax − ALTER TABLE table_name DROP constraint_name; Example In this example, we are using the following query to drop the PRIMARY KEY constraint present on the ID column of CUSTOMERS table − ALTER TABLE CUSTOMERS DROP PRIMARY KEY; Verification To verify whether the primary key constraint is dropped from the table, describe the ”temp” table using DESC command as follows − DESC CUSTOMERS; The PRIMARY KEY constraint is finally dropped! Look at the table below − Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Dropping an Index Using a Client Program We have seen how to drop an index from a MySQL database using SQL queries. In addition to it, we can also use other client programs to perform the drop index operation in the MySQL database. Syntax Following are the syntaxes to drop an index from a MySQL database using various programming languages − PHP NodeJS Java Python The MySQL PHP connector mysqli provides a function named query() to execute the DROP INDEX query in the MySQL database. $sql = “DROP INDEX index_name ON tbl_name”; $mysqli->query($sql); The MySQL NodeJS connector mysql2 provides a function named query() to execute the DROP INDEX query in the MySQL database. sql= “DROP INDEX index_name ON

MySQL – Unique Index Table of content MySQL Unique Index Creating Unique Index on Multiple Columns Creating Unique Index Using a Client Program ”; Previous Next MySQL Indexes are used to return the data from the database real quick. The users cannot see the indexes performing, instead they are just used to speed up the queries. However a unique index, in addition to speeding up data retrieval queries, is also used to maintain data integrity in a table. When a unique index is defined on a table column, we cannot add any duplicate values into that column. MySQL Unique Index A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in MySQL. If we are creating unique index on only a single column, all the rows in that column must be unique. We cannot create a unique index where NULL values are present in multiple rows in a single column. If we are creating unique index on multiple columns, the combination of rows in those columns must be unique. We cannot create a unique index on multiple columns if the combination of columns contains NULL values in more than one row. Syntax Following is the syntax for creating a unique index in MySQL − CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …, columnN); Example Let us first create a table named CUSTOMERS using the following query − 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) ); In the following query, we are inserting some values in to the above created table using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”MP”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); The table will be created as follows − 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 Now, create a unique index for the column named SALARY in the CUSTOMERS table using the following query − CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY); Inserting Duplicate Values Now, let us try to update the value in the SALARY column with a duplicate (already existing data) value using the following query − UPDATE CUSTOMERS SET SALARY = 2000 WHERE ID = 2; Error The above query results in an error because a column that has unique index cannot contain duplicate values in it. ERROR 1062 (23000): Duplicate entry ”2000.00” for key ”customers.unique_ind” Creating Unique Index on Multiple Columns In MySQL, we can also create a unique index on multiple columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query. Example Assume the previously created CUSTOMERS table and create a unique index on the columns named NAME and AGE using the following query − CREATE UNIQUE INDEX mul_unique_index ON CUSTOMERS(NAME, AGE); Verification Using the following query, we can list all the indexes that are created on the CUSTOMERS table − SHOW INDEX FROM CUSTOMERSG The table of index information is displayed as − *************************** 1. row *********************** Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *********************** Table: customers Non_unique: 0 Key_name: mul_unique_index Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 3. row *********************** Table: customers Non_unique: 0 Key_name: mul_unique_index Seq_in_index: 2 Column_name: AGE Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 4. row *********************** Table: customers Non_unique: 0 Key_name: unique_ind Seq_in_index: 1 Column_name: SALARY Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL Creating Unique Index Using a Client Program In addition to creating an index using a MySQL query, we can also create the unique index using a client program. Syntax PHP NodeJS Java Python To create an unique index into MySQL table through a PHP program, we need to execute the CREATE UNIQUE INDEX statement using the query() function of mysqli as follows − $sql = “CREATE UNIQUE INDEX uidx_tid ON tutorials_table (tutorial_id)”; $mysqli->query($sql); To create an unique index into MySQL table through a JavaScript program, we need to execute the CREATE UNIQUE INDEX statement using the query() function of mysql2 library as follows − sql = “CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY)”; con.query(sql); To create an unique index into MySQL table through a Java program, we need to execute the CREATE UNIQUE INDEX statement using the executeUpdate() function of JDBC as follows − String sql = “CREATE UNIQUE INDEX UIID ON tutorials_tbl (tutorial_id)”;

MySQL – GROUP BY Clause Table of content MySQL GROUP BY Clause MySQL GROUP BY on Single Column MySQL GROUP BY on Multiple Columns MySQL GROUP BY with ORDER BY Clause MySQL GROUP BY with HAVING Clause GROUP BY Clause Using Client Program ”; Previous Next MySQL GROUP BY Clause The GROUP BY clause in MySQL is used to arrange identical data in a table into groups. For example, let us suppose you have a table of sales data of an organization consisting of date, product, and sales amount. To calculate the total sales in a particular year, the GROUP BY clause can be used to group the sales of products made in that year. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day. This GROUP BY clause follows the WHERE clause in an SQL statement and precedes the ORDER BY or HAVING clause (if they exist). You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG, etc., functions on the grouped column. Syntax Following is the basic syntax to use GROUP BY with SELECT statement − SELECT column_name(s) FROM table_name GROUP BY [condition | column_name(s)]; Example This example demonstrates how to use aggregate functions with GROUP BY clause. First of all, create a table named CUSTOMERS, using the following CREATE TABLE 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, insert the following records into the CUSTOMERS table using the following INSERT statement − INSERT INTO CUSTOMERS 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); The table is created as follows − 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 Now, use the following GROUP BY query to group the customers based on their age − SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE; Output Following is the result produced − AGE COUNT(Name) 32 1 25 2 23 1 27 1 22 1 24 1 MySQL GROUP BY on Single Column When we use the GROUP BY clause on a single column, all common values in that column will be added together making it a single record. Example In this example, let us group the customers by their age and calculate the average salary for each age using the following query − SELECT AGE, AVG(SALARY) AS AVG_SALARY FROM CUSTOMERS GROUP BY AGE; Output This would produce the following result − AGE AVG_SALARY 32 2000.000000 25 4000.000000 23 2000.000000 27 8500.000000 22 4500.000000 24 10000.000000 MySQL GROUP BY on Multiple Columns When we use the GROUP BY clause with multiple columns, the common record obtained by combining values from these columns will be grouped together into a single record. Example In this example, if you want to know the total amount of salary for each customer age wise, then the GROUP BY query would be as follows − SELECT CONCAT(AGE, ” – ”, SALARY) AS SALARY_AGEWISE FROM CUSTOMERS GROUP BY AGE, SALARY; Output This would produce the following result − SALARY_AGEWISE 32 – 2000.00 25 – 1500.00 23 – 2000.00 25 – 6500.00 27 – 8500.00 22 – 4500.00 24 – 10000.00 MySQL GROUP BY with ORDER BY Clause We can use the ORDER BY clause with GROUP BY in MySQL to sort the result set by one or more columns. Syntax Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL − SELECT column1, column2, …, aggregate_function(columnX) AS alias FROM table GROUP BY column1, column2, … ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], …; Example In here, we are trying to find the highest salary for each age, sorted by high to low − SELECT AGE, MAX(salary) AS MAX_SALARY FROM CUSTOMERS GROUP BY AGE ORDER BY MAX(salary) DESC; Output This would produce the following result − AGE AVG_SALARY 24 10000.00 27 8500.00 25 6500.00 22 4500.00 32 2000.00 23 2000.00 MySQL GROUP BY with HAVING Clause We can also use the GROUP BY clause with the HAVING clause to filter the results of a query based on conditions applied to groups of data. The condition can be applied to an aggregate function that is used in the SELECT statement or to a column in the GROUP BY clause. Syntax Following is the syntax for using ORDER BY clause with HAVING clause in SQL − SELECT column1, column2, aggregate_function(column) FROM table_name GROUP BY column1, column2 HAVING condition; Example In the following query, we are grouping the customers by their age and calculating the average salary for each group. The HAVING clause is used to filter the results to show only those groups where the average salary is greater than 8000 − SELECT AGE, AVG(SALARY) AS AVG_SALARY FROM CUSTOMERS GROUP BY AGE HAVING AVG(salary) > 8000; Output This would produce the following result − AGE AVG_SALARY 27 8500.000000 24 10000.000000 GROUP BY Clause Using Client Program In addition to using GROUP BY Clause in MySQL server with an SQL query, we can also execute the GROUP BY clause using a client program. Syntax Following are the syntaxes of the Group by Clause in select statement in various programming languages − PHP NodeJS Java Python To use GROUP BY Clause in MySQL table through PHP program, we need to execute the SQL statement using the function named query() provided by mysqli connector − $sql = “SELECT

MySQL – INDEXES ”; Previous Next An index is a data structure that improves the speed of operations on a database table. They are a special type of lookup tables pointing to the data. Indexes can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records. Indexes, however, reduce the efficiency of INSERT and UPDATE operations on a table. This is because when we insert or update a data value in a table, indexes also need to be modified accordingly. So, they are not always appropriate to use. Users cannot see the indexes, they are just used to speed up queries and will be used by the Database Search Engine to locate records very fast. Types of MySQL Indexes Indexes can be defined on single or multiple columns of a MySQL table. The decision to add indexes on a table column(s) depends on the type of data that needs to be searched. MySQL provides the following types of indexes − Simple Index Unique Index Primary Key Index Fulltext Index Descending Index Simple Index A simple index is a basic type of index where the values inserted into the column, containing this index, are searched easily. In such case, the column can contain duplicate values or NULL. Unique Index A Unique index does not allow any duplicate values to be inserted into a table column (where the index is defined on).It can be added to single or multiple columns of a table. If it is added to a single column, the values of that column must be unique. But if it is added to multiple columns, the combination of values in these columns must be unique. Primary Key Index Primary Key Index is an extension of unique index, as the primary key column must always contain unique values and these values must not be NULL. Primary key can be set to a single column of a database table, or multiple columns as well (which is not recommended). Fulltext Index In a database, sometimes you would have to search for a blob of text instead of a record. You can use fulltext index for it. As its name suggests, it is used to make the text searches in a table easier. Descending Index The descending index is only available in MySQL versions after 8.0. It is simple index used to store data in a reverse order. Using this index, it is easy to search for the latest values inserted into the database table. Print Page Previous Next Advertisements ”;

MySQL – Rename View Table of content Renaming Views in MySQL Rules to be followed while Renaming Views Renaming a View Using a Client Program ”; Previous Next Renaming Views in MySQL The MySQL RENAME TABLE statement in MySQL is generally used to rename the name of a table. But this statement can also be used to rename views because views are typically virtual tables created by a query. Before renaming a view, we need to ensure that no active transactions are being performed on the view using its old name. It is, however, recommended to delete the existing view and re-create it with a new name instead of renaming it. Syntax Following is the basic syntax of the RENAME TABLE query to rename a view in MySQL − RENAME TABLE original_view_name TO new_view_name; Example First of all, let us create a table with the name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) ); Here, we are inserting some records into the above-created table using the query below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”MP”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Creating a view − Now, let us create a view based on the above created table using the following query − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS; The view will be created as follows − 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 Renaming the view − Now, we know that we are having an existing view in our database named “CUSTOMERS_VIEW”. So, we are going to rename this view to VIEW_CUSTOMERS using the below query − RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS; Verification Using the following SELECT statement, we can verify whether the view is renamed or not − SELECT * FROM VIEW_CUSTOMERS; The “VIEW_CUSTOMERS” view displayed is as follows − 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 Rules to be followed while Renaming Views There are some rules and practices to ensure that the renaming process goes smoothly and one should follow them while renaming a view in MySQL. They are listed below: Avoid renaming system views: In MySQL, the system views are views that contain all the information about the database management system. It is recommended not to rename these views because it can cause issues with the functioning of the database. Update all references to the view: After renaming a view in MySQL, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. If we failed to update these references results in errors or issues with the functioning of the database system. Test thoroughly: It is important to test the renaming process thoroughly in the development or testing environment to make sure that all references to the view have been updated correctly. Use a consistent naming convention: While working with views in MySQL, it”s recommended to use a consistent naming convention. If you need to rename a view, follow the same naming convention you”ve used for other views in the database. Backup the database: Before renaming a view, it is recommended to have a backup of the database to make sure that you have a restore point. Renaming a View Using a Client Program Until now, we used an SQL statement to rename a view directly in the MySQL database. But, we can also perform the same rename operation on a view using another client program. Syntax PHP NodeJS Java Python To Rename a view into MySQL Database through a PHP program, we need to execute the RENAME statement using the mysqli function named query() as follows − $sql = “RENAME TABLE first_view To tutorial_view”; $mysqli->query($sql); To Rename a view into MySQL Database through a JavaScript program, we need to execute the RENAME statement using the query() function of mysql2 library as follows − sql = “RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS”; con.query(sql); To Rename a view into MySQL Database through a Java program, we need to execute the RENAME statement using the JDBC function named executeQuery() as follows − String sql = “RENAME TABLE first_view TO tutorial_view”; st.executeQuery(sql); To Rename a view into MySQL Database through a

MySQL – Non-Clustered Index Table of content MySQL Non-Clustered Indexes Creating a Non-Clustered Index Using NodeJS ”; Previous Next Indexes in MySQL are used to retrieve the data much faster from the database tables or views. Users cannot see the indexes on the application level, but they work behind to speed up searches and queries. There are two types of Indexes in MySQL − Clustered Index Non-Clustered Index A clustered index in MySQL can sort the data in a table manually by ordering all the rows in the table based on the key columns used to create it. On the other hand, a non-clustered index stores data in one location and indexes containing pointers to this data in another location. MySQL Non-Clustered Indexes Non-Clustered indexes store data in one location and its indexes in another location. These indexes contain pointers to the actual data. However, MySQL does not provide ways to explicitly create clustered and non-clustered indexes. A PRIMARY KEY is treated as a clustered index. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is a clustered index. All the other indexes on a table are non-clustered indexes. Syntax Following is the basic syntax to create a non-clustered index on a MySQL table − CREATE INDEX index_name ON table_name(column_name(s)); Example Let us see an example to create a non-clustered index on a table named ”Students”. This table contains details of students like their Roll Number, Name, Age, and Department. Here, we are trying to apply the non-clustered index on columns Roll Number and Department, using the following query − Let us first create the table Students using CREATE TABLE statement shown below − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (20, 2), PRIMARY KEY(ID) ); Using the following query, create a non-clustered index on the NAME column − CREATE INDEX nc_index ON CUSTOMERS(NAME); Note − As MySQL does not have specific provision for Non-Clustered Index, we are using the usual CREATE INDEX statement. Verification To verify whether the INDEX is created on the table CUSTOMERS or not, display the table definition using DESC command − DESC CUSTOMERS; As we can see below, there are two indexes created on the CUSTOMERS table. The PRIMARY KEY index is a clustered index and the multi-index is a non-clustered index − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO MUL NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Creating a Non-Clustered Index Using NodeJS In addition to using SQL queries to create non-clustered indexes, we can also create them on a MySQL database using a client program. The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database. Syntax Following is the syntax to create a non-clustered index in MySQL database using NodeJS − sql = “CREATE INDEX index_name ON table_name(column_name(s))”; con.query(sql); Example Following are the implementation of this operation using NodeJS − 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 TUTORIALS” con.query(sql); //Select database sql = “USE TUTORIALS” con.query(sql); //Creating table sql = “CREATE TABLE STUDENTS(RNO INT NOT NULL,NAME VARCHAR(50),AGE INT,DEPT VARCHAR(50));” con.query(sql); //Creating Index sql = “CREATE INDEX nc_index ON STUDENTS(RNO, DEPT);” con.query(sql); //Describing the Table sql = “DESC STUDENTS;” con.query(sql, function(err, result){ if (err) throw err console.log(result) }); }); Output The output produced is as follows − Connected! ————————– [ {Field: ”RNO”,Type: ”int”,Null: ”NO”,Key: ”MUL”,Default: null,Extra: ””}, {Field: ”NAME”,Type: ”varchar(50)”,Null: ”YES”,Key: ””,Default: null,Extra: ””}, {Field: ”AGE”,Type: ”int”,Null: ”YES”,Key: ””,Default: null,Extra: ””}, {Field: ”DEPT”,Type: ”varchar(50)”,Null: ”YES”,Key: ””,Default: null,Extra: ””} ] Print Page Previous Next Advertisements ”;