Category: mysql

MySQL – JSON Table of content MySQL JSON Retrieving Data From JSON Column The JSON_UNQUOTE() Function The JSON_TYPE() Function The JSON_ARRAY_APPEND() Function The JSON_ARRAY_INSERT() Function JSON Using Client Program ”; Previous Next MySQL provides a native JSON (JavaScript Object Notation) datatype that enables efficient access to the data in JSON documents. This datatype is introduced in MySQL versions 5.7.8 and later. Before it was introduced, the JSON-format strings were stored in the string columns of a table. However, the JSON datatype proves to be more advantageous than strings due to the following reasons − It automatically validates the JSON documents, displaying an error whenever an invalid document is stored. It stores the JSON documents in an internal format allowing easy read access to the document elements. Hence, when the MySQL server later reads the stored JSON values in a binary format, it just enables the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document. The storage requirements for JSON documents are similar to those of LONGBLOB or LONGTEXT data types. MySQL JSON To define a table column with JSON datatype, we use the keyword JSON in the CREATE TABLE statement. We can create two types of JSON values in MySQL: JSON array: It is a list of values separated by commas and enclosed within square brackets ([]). JSON object: An object with a set of key-value pairs separated by commas and enclosed within curly brackets ({}). Syntax Following is the syntax to define a column whose data type is JSON − CREATE TABLE table_name ( … column_name JSON, … ); Example Let us see an example demonstrating the usage of JSON datatype in a MySQL table. Here, we are creating a table named MOBILES using the following query − CREATE TABLE MOBILES( ID INT NOT NULL, NAME VARCHAR(25) NOT NULL, PRICE DECIMAL(18,2), FEATURES JSON, PRIMARY KEY(ID) ); Now, let us insert values into this table using the INSERT statement. In the FEATURES column, we use key-value pairs as a JSON value. INSERT INTO MOBILES VALUES (121, ”iPhone 15”, 90000.00, ”{“OS”: “iOS”, “Storage”: “128GB”, “Display”: “15.54cm”}”), (122, ”Samsung S23”, 79000.00, ”{“OS”: “Android”, “Storage”: “128GB”, “Display”: “15.49cm”}”), (123, ”Google Pixel 7”, 59000.00, ”{“OS”: “Android”, “Storage”: “128GB”, “Display”: “16cm”}”); Output The table will be created as − ID NAME PRICE FEATURES 121 iPhone 15 90000.00 {“OS”: “iOS”, “Storage”: “128GB”, “Display”: “15.54cm”} 122 Samsung S23 79000.00 {“OS”: “Android”, “Storage”: “128GB”, “Display”: “15.49cm”} 123 Google Pixel 7 59000.00 {“OS”: “Android”, “Storage”: “128GB”, “Display”: “16cm”} Retrieving Data From JSON Column As JSON datatype provides an easier read access to all JSON elements, we can also retrieve each element directly from the JSON column. MySQL provides a JSON_EXTRACT() function to do so. Syntax Following is the syntax of the JSON_EXTRACT() function − JSON_EXTRACT(json_doc, path) In a JSON array, we can retrieve a particular element by specifying its index (starting from 0). And in a JSON object, we specify the key from key-value pairs. Example In this example, from the previously created MOBILES table we are retrieving the OS name of each mobile using the following query − SELECT NAME, JSON_EXTRACT(FEATURES,”$.OS”) AS OS FROM MOBILES; Instead of calling the function, we can also use -> as a shortcut for JSON_EXTRACT. Look at the query below − SELECT NAME, FEATURES->”$.OS” AS OS FROM MOBILES; Output Both queries display the same following output − NAME FEATURES iPhone 15 “iOS” Samsung S23 “Android” Google Pixel 7 “Android” The JSON_UNQUOTE() Function The JSON_UNQUOTE() function is used to remove the quotes while retrieving the JSON string. Following is the syntax − JSON_UNQUOTE(JSON_EXTRACT(json_doc, path)) Example In this example, let us display the OS name of each mobile without the quotes − SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,”$.OS”)) AS OS FROM MOBILES; Or, we can use ->> as a shortcut for JSON_UNQUOTE(JSON_EXTRACT(…)). SELECT NAME, FEATURES->>”$.OS” AS OS FROM MOBILES; Output Both queries display the same following output − NAME FEATURES iPhone 15 iOS Samsung S23 Android Google Pixel 7 Android We cannot use chained -> or ->> to extract data from nested JSON object or JSON array. These two can only be used for the top level. The JSON_TYPE() Function As we know, the JSON field can hold values in the form of arrays and objects. To identify which type of values are stored in the field, we use the JSON_TYPE() function. Following is the syntax − JSON_TYPE(json_doc) Example In this example, let us check the type of the FEATURES column of MOBILES table using JSON_TYPE() function. SELECT JSON_TYPE(FEATURES) FROM MOBILES; Output As we can see in the output, the songs column type is OBJECT. JSON_TYPE(FEATURES) OBJECT OBJECT OBJECT The JSON_ARRAY_APPEND() Function If we want to add another element to the JSON field in MySQL, we can use the JSON_ARRAY_APPEND() function. However, the new element will only be appended as an array. Following is the syntax − JSON_ARRAY_APPEND(json_doc, path, new_value); Example Let us see an example

MySQL – Find Duplicate Records Table of content Finding Duplicate Records With Having Clause The ROW_NUMBER() function with PARTITION BY Find Duplicate Records Using Client Program ”; Previous Next Duplicate records in a table decrease the efficiency of a MySQL database (by increasing the execution time, using unnecessary space, etc.). Thus, locating duplicates becomes necessary to efficiently use the database. We can, however, also prevent users from entering duplicate values into a table, by adding constraints on the desired column(s), such as PRIMARY KEY and UNIQUE constraints. But, due to various reasons like, human error, an application bug or data extracted from external resources, if duplicates are still entered into the database, there are various ways to find the records. Using SQL GROUP BY and HAVING clauses is one of the common ways to filter records containing duplicates. Finding Duplicate Records Before finding the duplicate records in a table we need to define the criteria for which we need the duplicate records for. You can do this in two steps − First of all, we need to group all the rows by the columns on which you want to check the duplicity on, using the GROUPBY clause. Then Using the Having clause and the count function then, we need to verify whether any of the above formed groups have more than 1 entity. 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 CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us insert some duplicate records into the above-created table using the INSERT IGNORE INTO statement as shown below − 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 − 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 On the following query, we are trying to return the count of duplicate records using the MySQL COUNT() function − SELECT SALARY, COUNT(SALARY) AS “COUNT” FROM CUSTOMERS GROUP BY SALARY ORDER BY SALARY; Output The output for the query above is produced as given below − SALARY COUNT 1500.00 1 2000.00 2 4500.00 1 6500.00 1 8500.00 1 10000.00 1 With Having Clause The HAVING clause in MySQL can be used to filter conditions for a group of rows in a table. Here, we are going to use the HAVING clause with the COUNT() function to find the duplicate values in one or more columns of a table. Duplicates values in single column Following are the steps to find the duplicate values in a single column of a table: Step-1: Firstly, we need to use the GROUP BY clause to group all rows in the column that we want to check the duplicates. Step-2: Then , to find duplicate groups, use COUNT() function in the HAVING clause to check if any group has more than one element. Example Using the following query, we can find all rows that have duplicate DOG_NAMES in the PETS table − SELECT SALARY, COUNT(SALARY) FROM CUSTOMERS GROUP BY SALARY HAVING COUNT(SALARY) > 1; Output The output is as follows − SALARY COUNT 2000.00 2 Duplicate Values in Multiple Columns We can use the AND operator in the HAVING clause to find the duplicate rows in multiple columns. The rows are considered duplicate only when the combination of columns are duplicate. Example In the following query, we are finding rows in the PETS table with duplicate records in DOG_NAME, AGE, OWNER_NAME columns − SELECT SALARY, COUNT(SALARY), AGE, COUNT(AGE) FROM CUSTOMERS GROUP BY SALARY, AGE HAVING COUNT(SALARY) > 1 AND COUNT(AGE) > 1; Output The output is as follows − SALARY COUNT AGE COUNT 2000.00 2 23 2 The ROW_NUMBER() function with PARTITION BY In MySQL, the ROW_NUMBER() function and PARTITION BY clause can be used to find duplicate records in a table. The partition clause divides the table based on a specific column or multiple columns, then the ROW_NUMBER() function assigns a unique row number to each row within each partition. Rows with the same partition and row number are considered duplicates rows. Example In the following query, we are assigning a SELECT *, ROW_NUMBER() OVER ( PARTITION BY SALARY, AGE ORDER BY SALARY, AGE ) AS row_numbers FROM CUSTOMERS; Output The output for the query above as follows − ID NAME AGE ADDRESS SALARY row_numbers 2 Khilan 25 Delhi 1500.00

MySQL – Stored Procedure Table of content The MySQL Stored Procedure Creating a Procedure Stored Procedure Parameter Types Deleting a Stored Procedure Advantages of Stored Procedure Drawbacks of Stored Procedure Stored Procedure Using Client Program ”; Previous Next The MySQL Stored Procedure A MySQL stored procedure is a group of pre-compiled SQL statements that can be reused anytime. Stored procedures can be used to perform different database operations such as such as inserting, updating, or deleting data. Syntax The basic syntax to create a stored procedure in MySQL is as follows − DELIMITER // CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name parameter_datatype) BEGIN — SQL statements to be executed END // DELIMITER; Where, The CREATE PROCEDURE statement is used to create the procedure. The SQL statements that need to be executed are placed between the BEGIN and END keywords. Creating a Procedure We can use the following steps to create a stored procedure in MySQL − Choose a name for the procedure. Write the SQL query of the procedure. Execute the procedure with different parameters. Example To understand a stored procedure, let us consider the CUSTOMERS table which contains the personal details of customers including their ID, name, age, address and salary as shown below − 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 values into this table using the INSERT statement as follows − 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 CUSTOMERS table obtained 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 Now, let us create a procedure named ”GetCustomerInfo” without any parameters to retrieve all the records from CUSTOMERS table where age is greater than 25 − DELIMITER // CREATE PROCEDURE GetCustomerInfo() BEGIN SELECT * FROM CUSTOMERS WHERE AGE > 25; END // Verification To verify the changes, we execute the procedure using the CALL command as shown in the query below − CALL GetCustomerInfo(); // The result produced is as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 Stored Procedure Parameter Types Stored procedures can have different types of parameters, which are used to decide the values that will be passed during execution. Following are the different types of stored procedure parameters in SQL − Input parameters − These parameters are used to pass values from the calling program or user to the stored procedure. Output parameters − These parameters are used to return values from the stored procedure to the calling program or user. Input/Output parameters − These parameters allow a stored procedure to accept input values and return output values. Table-valued parameters − These parameters are used to pass a table variable as a parameter to a stored procedure. Default parameters − These parameters are used to specify a default value that will be used if no value is passed for the parameter. Cursor parameters − These parameters are used to pass a cursor to a stored procedure. Output XML parameters − These parameters are used to return XML data from a stored procedure. Now, let us take a look at some of the most common types of stored procedure parameters in SQL − Procedure with IN parameter The IN parameter is the default parameter and is used to receive the input value from the calling program. The value is passed at the time of procedure execution. Example In the following query, we are creating a stored procedure that takes a customer”s ID as an input parameter and returns that customer”s details. DELIMITER // CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT) BEGIN SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge; END // Verification To execute the stored procedure and pass a value for the ”CustomerAge” parameter, we will use the CALL command as shown below − CALL GetCustomerInfo(23); // Following is the output of the above code − ID NAME AGE ADDRESS SALARY 3 Kaushik 23 Kota 2000.00 Procedure with OUT parameter The OUT parameter is used to send the output values to the calling program. It is necessary to specify the OUT keyword to an output parameter when creating the procedure. At the time of calling, a variable, prefixed with ”@” is used to hold the returned value. We can then use the SELECT statement on the variable to display the output of the procedure. Example In the following query, we are creating a stored procedure that takes customer”s ID

MySQL – Composite Key Table of content Creating MySQL Composite Key Dropping MySQL Composite Key Composite Key Using a Client Program ”; Previous Next A MySQL Composite Key is a key that consists of two or more columns in a table, used to uniquely identify a record (combination of values in the same table row). It can also be described as a Primary Key created on multiple columns. With composite key on multiple columns of a table, a combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn”t have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table. Creating MySQL Composite Key To create a composite key in a MySQL table, we create a primary key on two or more columns of a table using the PRIMARY KEY keyword in the CREATE TABLE statement. The composite key must have the following features − A Composite Key may or may not be a part of the Foreign key. A Composite Key can not be NULL. A Composite Key also can be created by combining more than one Candidate Key. It is also known as Compound key. All the attributes in a compound keys are foreign keys. Syntax Following is the syntax to create a Composite Key while creating a table − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype…, CONSTRAINT composite_key_name PRIMARY KEY(column_name1, column_name2,..) ); Example In the following example, we are trying to create a table named CUSTOMERS and add a composite key on ID and NAME columns as shown − 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, NAME) ); PRIMARY KEY is added to both ID and NAME columns in the CUSTOMERS table. The combination of values inserted into these columns must be unique, even if the individual column values has duplicates. Verification To verify if a composite key is created or not, let us display the table definition of a CUSTOMERS table using the DESC query − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO PRI NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Dropping MySQL Composite Key We can drop the MySQL Composite Key by using the ALTER TABLE… DROP statement. Syntax Following is the syntax to drop the Composite key from the column of a table − ALTER TABLE table_name DROP PRIMARY KEY; Example Using the following SQL statement, we can drop the Composite key constraint from the table − ALTER TABLE CUSTOMERS DROP PRIMARY KEY; Verification To verify if the Composite Key has been dropped or not, we display the CUSTOMERS table using the DESC keyword − 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 Composite Key Using a Client Program We can also apply a Composite Key constraint on Fields to uniquely identified using a client program. Syntax PHP NodeJS Java Python To apply a Composite key on fields through a PHP program, we need to execute the “Create/Alter” statement using the mysqli function query() as follows − $sql = ”ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)”; $mysqli->query($sql); To apply a Composite key on fields through a JavaScript program, we need to execute the “Create/Alter” statement using the query() function of mysql2 library as follows − sql = `CREATE TABLE employee(ID Int NOT NULL, emp_Id INT NOT NULL, emp_Name varchar(25), PRIMARY KEY(ID, emp_Id))`; con.query(sql); To apply a Composite key on fields through a Java program, we need to execute the “Create/Alter” statement using the JDBC function execute() as follows − String sql = “Alter TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)”; statement.execute(sql); To apply a Composite key on fields through a python program, we need to execute the “Create/Alter” statement using the execute() function of the MySQL Connector/Python as follows − composite_key_query = ”CREATE TABLE TEST(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, MOBILE BIGINT, CONSTRAINT CK_TEST PRIMARY KEY (ID, MOBILE))” cursorObj.execute(composite_key_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $dbname = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } // printf(”Connected successfully.”); //creating composite key using alter statement. $sql = ”ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)”; if ($mysqli->query($sql)) { echo “composite key column created successfully in customers table n”; } if ($mysqli->errno) { printf(“Table could not be created!.”, $mysqli->error); } $mysqli->close();

MySQL – Today Date Table of content MySQL Today”s Date Inserting Date Values in a Table Today Date Using Client Program ”; Previous Next Generally, the date is represented using three values: date, month, and year. Dates have many possible variations, all of which depend on several inconsistency factors. DD/MM/YYYY, For instance – 04/04/2024 YYYY/MM/DD, For instance – 2024/04/27 DD-MM-YYYY, For instance – 04-04-2024 MySQL Today”s Date We have several built-in functions to retrieve and manipulate the MySQL today”s date. The following are the functions: CURDATE(), CURRENT_DATE(), CURRENT_DATE. CURDATE(): This function returns the current date as ‘YYYY-MM-DD’ (string) or ‘YYYYMMDD’ (numeric). CURRENT_DATE(): This function is s synonym to the CURDATE() function which returns the current date in the same format. CURRENT_DATE: This is also synonym of CURDATE() function. MySQL CURDATE() Function In the following example, we are retrieving the current date value using the CURDATE() function − SELECT CURDATE() AS Today; Output On executing the given query, the output is displayed as follows − Today 2023-04-27 MySQL CURRENT_DATE() Function Similarly, we can also display the current date value using the CURRENT_DATE() function. SELECT CURRENT_DATE() AS Today; Output On executing the given query, the output is displayed as follows − Today 2023-04-27 MySQL CURRENT_DATE Function In this example, we use the CURRENT_DATE function to retrieve the current date local to a system. SELECT CURRENT_DATE AS Today; Output On executing the given query, the output is displayed as follows − Today 2023-04-27 Inserting Date Values in a Table Following are the steps to insert date and time values in a table − First, we must create a table that accepts date and time values. Second, we must insert the data into the newly created table, which accepts date and time data types. Example Now, let us a create a table with the name ORDERS using the following query − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Here, we are inserting values into the above-created table using the INSERT INTO statement as shown below − INSERT INTO ORDERS VALUES (102, CURDATE() + 1, 3, 3000.00), (100, CURDATE() – 5, 3, 1500.00), (101, CURRENT_DATE() – 2, 2, 1560.00), (103, CURRENT_DATE + 3, 4, 2060.00); The table is created as follows − OID DATE CUSTOMER_ID AMOUNT 102 20231012 3 3000.00 100 20231006 3 1500.00 101 20231009 2 1560.00 103 20231014 4 2060.00 Today Date Using Client Program We can also perform Today Date Using Client Program. Syntax PHP NodeJS Java Python To display today date through a PHP program use CURDATE() function, we need to execute the “SELECT” statement using the mysqli function query() as follows − $sql = “SELECT CURRENT_DATE AS TODAYS_DATE”; $mysqli->query($sql); To display today date through a JavaScript Program use CURDATE() function, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows − sql = “SELECT CURDATE() AS TODAY_DATE”; con.query(sql) To display today date through a Java program use CURDATE() function, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows − String curr_date = “SELECT CURDATE() AS TODAYS_DATE”; statement.executeQuery(curr_date); To display today date through a Python program use CURDATE() function, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows − today_date_query = “SELECT CURDATE() AS Today” cursorObj.execute(today_date_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 = “SELECT CURRENT_DATE AS TODAYS_DATE”; If($result = $mysqli->query($sql)){ printf(“Select query executed successfully…!n”); while($row = mysqli_fetch_array($result)){ printf(“Todays date: %s”, $row[“TODAYS_DATE”]); } } if($mysqli->error){ printf(“Error message: “, $mysqli->error); } $mysqli->close(); Output The output obtained is as shown below − Select query executed successfully…! Todays date: 2023-08-04 var mysql = require(”mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “Password” }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log(“Connected!”); console.log(“————————–“); sql = “CREATE DATABASE TUTORIALS;” con.query(sql); sql = “USE TUTORIALS;” con.query(sql); sql = “SELECT CURDATE() AS TODAY_DATE”; con.query(sql, function(err, result){ if (err) throw err console.log(result); }); }); Output The output obtained is as shown below − [ { TODAY_DATE: 2023-08-14T18:30:00.000Z } ] import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TodayDate { public static void main(String[] args) { String url = “jdbc:mysql://localhost:3306/TUTORIALS”; String user = “root”; String password = “password”; ResultSet rs; try { Class.forName(“com.mysql.cj.jdbc.Driver”); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println(“Database connected successfully…!”); //find current date using CURDATE() function String curr_date = “SELECT CURDATE() AS TODAYS_DATE”; rs = st.executeQuery(curr_date); System.out.println(“Today”s date(using CURDATE() function): “); while(rs.next()) { String date = rs.getString(“TODAYS_DATE”); System.out.println(date); } //find today”s date using CURRENT_DATE() function String sql1 = “SELECT CURRENT_DATE() AS TODAY_DATE”; rs = st.executeQuery(sql1); System.out.println(“Today”s date(using CURRENT_DATE() function): “); while(rs.next()) { String dt = rs.getString(“TODAY_DATE”); System.out.println(dt); }

MySQL – Cursors Table of content The MySQL Cursors Declare Cursor Open Cursor Fetch Cursor Close Cursor ”; Previous Next The MySQL Cursors A MySQL cursor is a pointer that is used to iterate through a table”s records. They are used within stored programs such as procedures and functions and have the following features − READ ONLY − Cursors only allow you to read data; you can”t make changes to it. Non-Scrollable − Cursors move through records in one direction, from the top to the bottom. Asensitive − Cursors are sensitive to the changes made in the table. Any modification done in the table will be reflected in the cursor. The following four operations are used to manage cursors in MySQL: Declare Cursor Open Cursor Fetch Cursor Close Cursor Let us now see each of these operations in detail. Declare Cursor The DECLARE statement is used to declare a cursor in a MySQL. Once declared, it is then associated with a SELECT statement to retrieve the records from a table. Following is the syntax to declare a cursor − DECLARE cursor_name CURSOR FOR select_statement; Open Cursor The OPEN statement is used to initialize the cursor to retrieve the data after it has been declared. Following is the syntax to open a cursor − OPEN cursor_name; Fetch Cursor The FETCH statement is then used to retrieve the record pointed by the cursor. Once retrieved, the cursor moves to the next record. Following is the syntax to fetch a cursor − FETCH cursor_name INTO variable_list; Close Cursor The CLOSE statement is used to release the memory associated with the cursor after all the records have been retrieved. Following is the syntax to close a cursor − CLOSE cursor_name; Example In this example, we see how to manage a cursor in a stored procedure. Assume two tables, CUSTOMERS and BACKUP, are created using the CREATE TABLE statement. The CUSTOMERS table holds information like ID, name, age, address, and salary as shown below − 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 creating the BACKUP table, having the same structure as the CUSTOMERS table to store a copy of the records from the CUSTOMERS table − CREATE TABLE BACKUP ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) ); Now let us insert some records into the CUSTOMERS table using the INSERT statement as follows − 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 following SQL query creates a cursor on the CUSTOMERS table − DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS; Now, we are creating a stored procedure named ExampleProc to manage the cursor from declaration to closure − DELIMITER // CREATE PROCEDURE ExampleProc() BEGIN — Variable declarations DECLARE done INT DEFAULT 0; DECLARE cust_id, cust_age INTEGER; DECLARE cust_name VARCHAR(20); DECLARE cust_address CHAR(25); DECLARE cust_salary DECIMAL(18,2); — Cursor declaration DECLARE cur CURSOR FOR SELECT * FROM CUSTOMERS; — Handler for no more records DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; — Open the cursor OPEN cur; — Loop to fetch and insert records label: LOOP FETCH cur INTO cust_id, cust_name, cust_age, cust_address, cust_salary; INSERT INTO backup VALUES(cust_id, cust_name, cust_age, cust_address, cust_salary); IF done = 1 THEN LEAVE label; END IF; END LOOP; — Close the cursor CLOSE cur; END// DELIMITER ; Output Finally, if we call the procedure using CALL ExampleProc(); and check the contents of the BACKUP table, it will contain the same records as CUSTOMERS − SELECT * FROM BACKUP; The BACKUP table obtained 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 7 Muffy 24 Indore 10000.00 Print Page Previous Next Advertisements ”;

MySQL – Before Update Trigger Table of content MySQL Before Update Trigger Before Update Trigger Using a Client Program ”; Previous Next Triggers in MySQL are of two types: Before Triggers and After Triggers for various SQL operations like insertion, deletion and update. As we have already learned in previous chapters, the After Update Trigger is executed immediately after a value is updated in a row of a database table. Here, let us learn more about BEFORE UPDATE trigger. MySQL Before Update Trigger The Before Update Trigger is a row-level trigger supported by the MySQL database. It is type of special stored procedure which is executed automatically before a value is updated in a row of a database table. A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically. Whenever an UPDATE statement is executed in the database, the trigger is set to go off first followed by the updated value. Syntax Following is the syntax to create the BEFORE UPDATE trigger in MySQL − CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW BEGIN — trigger body END; Example Let us first create a table named USERS containing the details of users of an application. Use the following CREATE TABLE query to do so − CREATE TABLE USERS( ID INT AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL, AGE INT NOT NULL, birthDATE VARCHAR(100), PRIMARY KEY(ID) ); Insert values into the USERS table using the regular INSERT statement as shown below − INSERT INTO USERS (Name, Age, birthDATE) VALUES (”Sasha”, 23, ”24/06/1999”); (”Alex”, 21, ”12/01/2001”); The USERS table is created as follows − ID Name Age birthDATE 1 Sasha 23 24/06/1999 2 Alex 21 12/01/2001 Creating the trigger: Using the following CREATE TRIGGER statement, create a new trigger ”before_update_trigger” on the USERS table to display a customized error using SQLSTATE as follows − DELIMITER // CREATE TRIGGER before_update_trigger BEFORE UPDATE ON USERS FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END // DELIMITER ; Update values of the USERS table using the regular UPDATE statement − UPDATE USERS SET AGE = -1 WHERE NAME = ”Sasha”; Output An error is displayed as the output for this query − ERROR 1644 (45000): Age Cannot be Negative Before Update Trigger Using a Client Program We can also execute the Before Update Trigger using a client program instead of SQL queries directly. Syntax PHP NodeJS Java Python To execute the Before Update Trigger through a PHP program, we need to execute the CREATE TRIGGER statement using the mysqli function query() as follows − $sql = “CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END”; $mysqli->query($sql); To execute the Before Update Trigger through a JavaScript program, we need to execute the CREATE TRIGGER statement using the query() function of mysql2 library as follows − sql = `CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END`; con.query(sql); To execute the Before Update Trigger through a Java program, we need to execute the CREATE TRIGGER statement using the JDBC function execute() as follows − String sql = “CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END”; statement.execute(sql); To execute the Before Update Trigger through a python program, we need to execute the CREATE TRIGGER statement using the execute() function of the MySQL Connector/Python as follows − beforeUpdate_trigger_query = ”CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {sample} FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative” END IF END” cursorObj.execute(beforeUpdate_trigger_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 = “CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END”; if($mysqli->query($sql)){ printf(“Trigger created successfully…!n”); } $q = “UPDATE SAMPLE SET AGE = -1 WHERE NAME = ”Sasha””; $result = $mysqli->query($q); if ($result == true) { printf(“Record updated successfully…!n”); } if($mysqli->error){ printf(“Error message: ” , $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Trigger created successfully…! PHP Fatal error: Uncaught mysqli_sql_exception: Age Cannot be Negative var mysql = require(”mysql2”); var con = mysql.createConnection({ host:”localhost”, user:”root”, password:”password” }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log(“Connected successfully…!”); //console.log(“————————–“); sql = “USE TUTORIALS”; con.query(sql); sql = `CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Age Cannot be Negative”; END IF; END`; con.query(sql); console.log(“Before Update query executed successfully..!”); sql = “UPDATE SAMPLE SET AGE = -1 WHERE NAME = ”Sasha””; con.query(sql); console.log(“Table records: “) sql = “SELECT

MySQL – SIGNAL Statement Table of content The MySQL SIGNAL Statement ”; Previous Next When working with MySQL stored procedures, managing exceptions is important to prevent abrupt termination of procedures and to provide meaningful error information. This is achieved using the MySQL SIGNAL statement and the DECLARE … HANDLER statement. The MySQL SIGNAL Statement The MySQL SIGNAL statement is used to convey error information in stored procedures. It ensures that exceptions are properly handled, preventing sudden procedure termination. Exception Handling with DECLARE … HANDLER You can use the DECLARE … HANDLER statement to effectively manage exceptions in MySQL. It allows you to specify how different types of exceptions should be handled within a stored procedure. By using this statement in conjunction with SIGNAL, you can enable precise control over error handling. Customizing Error Messages The SIGNAL statement allows for the customization of error messages using the SET MESSAGE_TEXT command. This is helpful for modifying error messages to provide more meaningful feedback to handlers, applications, or clients. Syntax Following is the syntax of the MySQL SIGNAL Statement − SIGNAL condition_value [SET signal_information_item] Where, condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”. signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME. Example In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than B-Tech, M-Tech, BSC and MSC, an error message is generated using the SIGNAL statement − DELIMITER // CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE SIGNAL SQLSTATE ”01000” SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END // DELIMITER ; You can call the above procedure to retrieve the result as shown below − CALL example(”BSC”, @fullform); You can retrieve the value of the variable using the following SELECT statement − SELECT @fullform; Following is the output obtained − @fullform Bachelor of Science If you pass an invalid value to the procedure, it will generate an error message as shown below − CALL example (”BBC”, @fullform); The output obtained is as follows − ERROR 1001 (45000): Given degree is not valid Example Following is another example demonstrating exception handling with the SIGNAL statement. Here, we declare a condition and use SIGNAL to trigger exceptions based on certain conditions − DELIMITER // CREATE PROCEDURE example (num INT) BEGIN DECLARE testCondition CONDITION FOR SQLSTATE ”45000”; IF num < 0 THEN SIGNAL SQLSTATE ”01000”; ELSEIF num > 150 THEN SIGNAL SQLSTATE ”45000”; END IF; END // DELIMITER ; You can call the above procedure by passing two values as shown below − CALL example(15); Following is the output obtained − Query OK, 0 rows affected (0.00 sec) Calling the procedure by passing the second value − CALL example(160); The result produced is as shown below − ERROR 1644 (45000): Unhandled user-defined exception condition Example You can customize error messages using SET MESSAGE_TEXT with the SIGNAL statement as shown in this modified example − DELIMITER // CREATE PROCEDURE example (num INT) BEGIN DECLARE testCondition CONDITION FOR SQLSTATE ”45000”; IF num < 0 THEN SIGNAL SQLSTATE ”01000”; ELSEIF num > 150 THEN SIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Number higher than the limit set”; END IF; END // DELIMITER ; We get the following output − Query OK, 0 rows affected (0.01 sec) You can call the above procedure by passing two values as shown below − CALL example(20); Following is the output obtained − Query OK, 0 rows affected (0.00 sec) Calling the procedure by passing the second value − CALL example(160); You can observe in the output below, the error message displayed is customized according to the user − ERROR 1644 (45000): Number higher than the limit set Print Page Previous Next Advertisements ”;

MySQL – DECIMAL Table of content The MySQL Decimal Data Type MySQL DECIMAL Storage Decimal Datatype Using a Client Program ”; Previous Next The MySQL Decimal Data Type The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point. We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc. Internally, MySQL stores DECIMAL values using a binary format that allocates storage for the integer and fractional parts of the number separately. This binary format efficiently packs 9 digits into 4 bytes of storage. Syntax Following is the syntax to define a column whose data type is DECIMAL − column_name DECIMAL(P,D); Where, P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65. D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (<=) P. For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point. In MySQL, instead of the DECIMAL keyword, we can also use the “DEC”, “FIXED” and “NUMERIC” keywords because they are synonyms for DECIMAL. Attributes The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL. UNSIGNED − When used, it indicates that the column does not accept negative values. ZEROFILL − If used, it pads the number with zeros to the specified width. Precision and Scale In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 − SALARY decimal(5,3) This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999. No Decimal Places In here, the SALARY column contains no fractional part or decimal point. The following two queries are same − SALARY DECIMAL(5); SALARY DECIMAL(5,0); Both declarations indicate that the SALARY column can store values as whole numbers without decimal places. Default Precision If you omit the precision value, the default precision P will be 10 − SALARY DECIMAL; MySQL DECIMAL Storage MySQL stores values of the “DECIMAL” data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage. The storage required for remaining (leftover) digits is demonstrated in the following table: Leftover Digits Bytes 0 0 1-2 1 3-4 2 5-6 3 7-9 4 Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 – 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes. Example To further understand this, let us create a table named EMPLOYEES using the following query − CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) ); Using the following query, we are inserting some records into the above created table − INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (“Krishna”, 150050.34), (“Kalyan”, 100000.65); The EMPLOYEES table obtained is as follows − ID NAME SALARY 1 Krishna 150050.3400 2 Kalyan 100000.6500 Using the following query, we are including the ZEROFILL attribute in the “SALARY” column − ALTER TABLE EMPLOYEES MODIFY SALARY decimal(14, 4) zerofill; Following is the output of the above query − Query OK, 2 rows affected, 1 warning (0.03 sec) Records: 2 Duplicates: 0 Warnings: 1 Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column − SELECT * FROM EMPLOYEES; The records will display zeros padded based on the range specified in the “SALARY” column − ID NAME SALARY 1 Krishna 0000150050.3400 2 Kalyan 0000100000.6500 Decimal Datatype Using a Client Program We can also create column of the decimal datatype using the client program. Syntax PHP NodeJS Java Python To create a column of decimal datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows − $sql = ”CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )”; $mysqli->query($sql); To create a column of decimal datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using

MySQL – Java Syntax Table of content JDBC Installation Java Functions to Access MySQL Basic Example ”; Previous Next To communicate with databases Java provides a library known as JDBC (Java Database Connectivity). JDBC provides a set of classes and methods specifically designed for database connectivity, enabling Java developers to perform tasks such as establishing connections, executing queries, and managing data in MySQL databases. JDBC Installation To use MySQL with Java, you need to use a JDBC (Java Database Connectivity) driver to connect your Java application to a MySQL database. Below are the general steps for installing and using the MySQL Connector/J, which is the official MySQL JDBC driver for Java − Step 1: Download MySQL Connector/J Visit the official MySQL Connector/J download page: MySQL Connector/J Downloads. Step 2: Select the Appropriate Version Choose the appropriate version based on your MySQL server version and Java version. Download the ZIP or TAR archive containing the JDBC driver. Step 3: Extract the Archive Extract the contents of the downloaded archive to a location on your computer. Step 4: Add Connector/J to Your Java Project In your Java project, add the Connector/J JAR file to your classpath. You can do this in your IDE or by manually copying the JAR file into your project. Step 5: Connect to MySQL Database in Java In your Java code, use the JDBC API to connect to the MySQL database. Java Functions to Access MySQL Following are the major functions involved in accessing MySQL from Java − S.No Function & Description 1 DriverManager.getConnection(String url, String user, String password) Establishes a connection to the database using the specified URL, username, and password. 2 createStatement() Creates a Statement object for executing SQL queries. 3 executeQuery(String sql) Executes a SQL SELECT query and returns a ResultSet object containing the result set. 4 executeUpdate(String sql) Executes a SQL INSERT, UPDATE, DELETE, or other non-query statement. 5 next() Moves the cursor to the next row in the result set. Returns true if there is a next row, false otherwise. 6 getInt(String columnLabel) Retrieves the value of the specified column in the current row of the result set. 7 prepareStatement(String sql) Creates a PreparedStatement object for executing parameterized SQL queries. 8 setXXX(int parameterIndex, XXX value) Sets the value of a specified parameter in the prepared statement. 9 executeQuery(), executeUpdate() Execute the prepared statement as a query or update. 10 setAutoCommit(boolean autoCommit) Enables or disables auto-commit mode. 11 commit() Commits the current transaction. 12 rollback() Rolls back the current transaction. Basic Example To connect and communicate with a MySQL database using Java, you can follow these steps − Load the JDBC driver specific to your database. Create a connection to the database using “DriverManager.getConnection()”. Create a “Statement” or “PreparedStatement” for executing SQL queries. Use “executeQuery()” for SELECT queries, or “executeUpdate()” for other statements. Iterate through the “ResultSet” to process the retrieved data. Close “ResultSet”, “Statement”, and “Connection” to release resources. Wrap database code in try-catch blocks to handle exceptions. Use transactions if performing multiple operations as a single unit. The following example shows a generic syntax of a Java program to call any MySQL query − import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DatabaseInteractionExample { public static void main(String[] args) { try { // Load JDBC Driver Class.forName(“com.mysql.cj.jdbc.Driver”); // Connect to Database Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/your_database”, “your_username”, “your_password”); // Execute Query Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(“Your SQL Query”); // Process Results while (resultSet.next()) { // Process data } // Close Resources resultSet.close(); statement.close(); connection.close(); // Handle Exceptions } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } } Print Page Previous Next Advertisements ”;