Category: mysql

MySQL – Literals Table of content Numeric Literals String Literals Boolean Literals Date and Time Literals Null Literals Client Program ”; Previous Next In MySQL, literals are fixed values (constants) that can be used in SQL statements such as SELECT, INSERT, UPDATE, and DELETE. We can use a literal in SQL statements without needing to be represented by a variable or an expression. Following are some common MySQL literals: Numeric Literals String Literals Boolean Literals Date and Time Literals NULL Literals Numeric Literals The MySQL numeric literals are numeric values that can represent positive or negative numbers, including both integers and floating-point values. If we do not specify any sign (i.e. positive (+) or negative (-)) to a numeric value, then a positive value is assumed. Let us see some examples by using various numeric literals in SQL queries. Example Following example displays an integer literal with no sign (by default positive sign will be considered) SELECT 100 AS ”numeric literal”; Output The output is obtained as follows − numeric literal 100 Example Following example displays an integer literal with positive sign (+) − SELECT -100 AS ”numeric literal”; Output The output is obtained as follows − numeric literal -100 Example Following example displays an integer literal with negative sign (-) − SELECT +493 AS ”numeric literal”; Output The output is obtained as follows − numeric literal 493 Example Following example displays a floating point literal − SELECT 109e-06 AS ”numeric literal”; Output The output is obtained as follows − numeric literal 0.000109 Example Following example displays a decimal literal − SELECT 793.200 AS ”numeric literal”; Output The output is obtained as follows − numeric literal 793.200 String Literals The MySQL string literals are character strings that are enclosed within the single quotes (”) or double quotes (“). Let us see some examples where string literals in SQL queries are used in different ways. Example In this example, we are displaying a string literal enclosed in single quotes − SELECT ”tutorialspoint” AS ”string literal”; We can use double quotes to enclose a string literal as follows − SELECT “tutorialspoint” AS ”string literal”; Output Following output is obtained in both cases − string literal tutorialspoint Example In this example, we are displaying a string literal with spaces enclosed in single quotes − SELECT ”tutorials point india” AS ”string literal”; We can also enclose this string literal (spaces included) in double quotes − SELECT “tutorials point india” AS ”string literal”; Output Following output is obtained with both queries − string literal tutorials point india Boolean Literals The MySQL Boolean literals are logical values that evaluate to either 1 or 0. Let us see some example for a better understanding. Example There are various ways a boolean value is evaluated to true in MySQL. Here, we use the integer 1 as a boolean literal − SELECT 1 AS ”boolean literal”; We can also use the keyword TRUE to evaluate the boolean literal to 1. SELECT TRUE AS ”boolean literal”; We can also use the lowercase of the keyword TRUE, as true, to evaluate the boolean literal to 1. SELECT true AS ”boolean literal”; Output Following output is obtained − boolean literal 1 Example Similarly, there are multiple ways a boolean value is evaluated to false in MySQL. Here, we use the integer 0 as a boolean literal − SELECT 0 AS ”boolean literal”; We can also use the keyword FALSE to evaluate the boolean literal to 0. SELECT FALSE AS ”boolean literal”; We can also use the lowercase of the keyword FALSE, as false, to evaluate the boolean literal to 0. SELECT false AS ”boolean literal”; Output Following output is obtained − boolean literal 0 Date and Time Literals The MySQL date and time literals represent date and time values. Let us see examples to understand how date and time values are represented in various ways in MySQL. Example In this example, we will display a date literal formatted as ”YYYY-MM-DD” SELECT ”2023-04-20” AS ”Date literal”; Output Following output is obtained − Date literal 2023-04-20 Example In this example, we will display a date literal formatted as ”YYYYMMDD” SELECT ”20230420” AS ”Date literal”; Output Following output is obtained − Date literal 20230420 Example In this example, we will display a date literal formatted as YYYYMMDD SELECT 20230420 AS ”Date literal”; Output Following output is obtained − Date literal 20230420 Example In this example, we will display a date literal formatted as ”YY-MM-DD” SELECT ”23-04-20” AS ”Date literal”; Output Following output is obtained − Date literal 23-04-20 Example In

MySQL – String Functions ”; Previous Next MySQL – String Functions MySQL string functions are used to manipulate the string values. The following table details the string functions that are available in the MySQL. Sr.No. Name & Description 1 ASCII() This function returns numeric value of left-most character. 2 BIN() This function returns a string representation of the argument. 3 BIT_LENGTH() This function returns length of argument in bits. 4 CHAR() This function returns the character for each integer passed. 5 CHAR_LENGTH() This function returns number of characters in argument. 6 CHARACTER_LENGTH() This function is a synonym for CHAR_LENGTH(). 7 CONCAT() This function returns concatenated string. 8 CONCAT_WS() This function returns concatenate with separator. 9 ELT() This function returns string at index number. 10 EXPORT_SET() This function returns a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string. 11 FIELD() This function returns the index (position) of the first argument in the subsequent arguments. 12 FIND_IN_SET() This function returns the index position of the first argument within the second argument. 13 FROM_BASE64() This function decodes a base-64 encoded string as a binary string. 14 INSERT() Inserts a substring at the specified position up to the specified number of characters. 15 INSTR() This function returns the index of the first occurrence of substring. 16 LCASE() Synonym for LOWER(). 17 LEFT() This function returns the leftmost number of characters as specified. 18 LENGTH() This function returns the length of a string in bytes. 19 LOAD_FILE() This function is used to load the specified file. 20 LOCATE() This function returns the position of the first occurrence of substring. 21 LOWER() This function returns the argument in lowercase. 22 LPAD() This function returns the string argument, left-padded with the specified string. 23 LTRIM() This function is used to removes leading spaces from the given string. 24 MAKE_SET() This function returns a set of comma-separated strings that have the corresponding bit in bits set. 25 MID() This function returns a substring starting from the specified position. 26 OCT() This function returns a string representation of the octal argument. 27 OCTET_LENGTH() This function is a synonym for LENGTH(). 28 ORD() If the leftmost character of the argument is a multi-byte character, returns the code for that character. 29 POSITION() This function is a synonym for LOCATE(). 30 QUOTE() This function escapes the argument for use in an SQL statement. 31 REPEAT() This function returns the starting index of the substring matching given regular expression. 32 REPLACE() This function replaces the matched sub string with the replacement string and returns the result. 33 REVERSE() This function is used to reverse the characters in a string 34 RIGHT() This function returns the specified rightmost number of characters. 35 RPAD() This function is used to add padding to the right side of the string until it reaches the desired length. 36 RTRIM() This function is used to remove trailing spaces. 37 SOUNDEX() This function returns a soundex string. 38 SPACE() This function returns a string of the specified number of spaces. 39 STRCMP() This function is used to compare two given strings. 40 SUBSTR() This function returns the substring as specified. 41 SUBSTRING() This function returns the substring as specified. 42 SUBSTRING_INDEX() This function returns a substring from a string before the specified number of occurrences of the delimiter. 43 TO_BASE64() This function encodes a string value to base-64 string. 44 TRIM() This function is used to removes the leading and trailing spaces of the given string. 45 UCASE() This function is a synonym for UPPER(). 46 UNHEX() This function converts each pair of hexadecimal digits to a character. 47 UPPER() This function is used to convert the characters in the given string to uppercase. 48 WEIGHT_STRING() This function returns the weight string value of the given argument. 49 MATCH This operator is used to search for particular string in the specified list of columns. 50 REGEXP This operator is similar to the REGEXP_LIKE() function it is used to match a particular pattern in the given string. 51 SOUNDS LIKE This operator is used to compare the soundex values of two strings. Print Page Previous Next Advertisements ”;

MySQL – Select Random Records Table of content Selecting Random Records in MySQL The MySQL RAND() Function LIMIT with RAND() Function Random Records Using Client Program ”; Previous Next Have you ever taken online examinations? If yes, then did you ever wonder how is the order, in which these questions are displayed, random? These questions are usually stored in a database of the test application and are randomly displayed one by one. While using a database for an application, there arise situations where the records from a table object need to be selected randomly. MySQL does not have a built-in provision for this. Selecting Random Records in MySQL In order to select random records in MySQL, you can use the ORDER BY RAND() clause. The RAND() function is used with the SELECT query to retrieve the stored data one by one or collectively together. The MySQL RAND() Function The MySQL RAND() Function returns a result-set containing all records of the original table in a completely random order. It is usually used with a SELECT statement in the ORDER BY clause. Syntax Following is the basic syntax of the RAND() function with ORDER BY Clause − SELECT column_name(s) FROM table_name ORDER BY RAND(); Example Following example demonstrates the usage of RAND() function when used with ORDER BY Clause. Here, let us first create a table ”CUSTOMERS” and insert some values into it. CREATE TABLE CUSTOMERS( ID int NOT NULL AUTO_INCREMENT, NAME varchar(20), AGE int, PRIMARY KEY(Id) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”John”,23); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”Larry”,21); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”David”,21); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”Carol”,24); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”Bob”,27); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”Mike”,29); INSERT INTO CUSTOMERS (NAME, AGE) VALUES (”Sam”,26); The CUSTOMERS table obtained is as follows − ID NAME AGE 1 John 23 2 Larry 21 3 David 21 4 Carol 24 5 Bob 27 6 Mike 29 7 Sam 26 Now, let us use the RAND() function with the SELECT statement to retrieve the records of the CUSTOMERS table in a randomized order − SELECT * FROM CUSTOMERS ORDER BY RAND(); Output Following is the output of the above query − ID NAME AGE 6 Mike 29 4 Carol 24 3 David 21 1 John 23 5 Bob 27 7 Sam 26 2 Larry 21 LIMIT with RAND() Function You can also limit the number of randomly retrieved records using the LIMIT clause with the RAND() function Syntax Following is the syntax to use LIMIT with RAND() function − SELECT column_name(s) FROM table_name ORDER BY RAND() LIMIT int_value; Example In this example, we are retrieving a limited number of records at random from the ”CUSTOMERS” table using the following query − SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1; Output of the above code is as shown below − ID NAME AGE 7 Sam 26 Each time you execute this query, you will get a different random record − SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1; The result produced is as follows − ID NAME AGE 6 Mike 29 You can also increase the limit of records to be displayed by modifying the LIMIT value as shown below − SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2; We get the output as shown below − ID NAME AGE 1 John 23 3 David 21 Random Records Using Client Program We can also select random records using client program. Syntax PHP NodeJS Java Python To select random records through a PHP program, we need to execute the RAND() function using the mysqli function query() as follows − $sql = “SELECT * FROM CUSTOMERS ORDER BY RAND()”; $mysqli->query($sql); To select random records through a JavaScript program, we need to execute the RAND() function using the query() function of mysql2 library as follows − sql = “SELECT * FROM CUSTOMERS ORDER BY RAND()”; con.query(sql) To select random records through a Java program, we need to execute the RAND() function using the JDBC function executeQuery() as follows − String sql = “SELECT * FROM CUSTOMERS ORDER BY RAND()”; statement.executeQuery(sql); To select random records through a Python program, we need to execute the RAND() function using the execute() function of the MySQL Connector/Python as follows − random_query = “SELECT * FROM CUSTOMERS ORDER BY RAND()” cursorObj.execute(random_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(); }

MySQL – Import CSV into database Table of content Import MySQL CSV into Database Importing a CSV File Using Client Program ”; Previous Next Import MySQL CSV into Database To import the MySQL data from a CSV file into a database table, we can use the MySQL LOAD DATA INFILE statement. Before importing the CSV file into the database server, we must ensure the following things − Database Table − Ensure you have a database table already set up to receive the incoming data. CSV File − You need a CSV file containing the data to be imported. User Privileges − Ensure your account has the necessary privileges, specifically FILE and INSERT, to perform this operation. Matching Columns − The target table and the CSV file should have matching columns with the same data types. CSV Format − The CSV file should be in a comma-separated format, with each row representing a record. Syntax Following is the syntax of the LOAD DATA INFILE statement in MySQL − LOAD DATA INFILE file_path INTO TABLE table_name FIELDS TERMINATED BY delimiter ENCLOSED BY enclosure LINES TERMINATED BY line_separator IGNORE number_of_lines_to_skip; Where, file_path is the path to the CSV file that contains the data to be imported. table_name is the name of the target table, where the data will be imported. delimiter is a character that separates each record in the CSV file. Enclosure is a character that encloses string records in the CSV file. line_seperator is a character that marks the end of a line in the CSV file. number_of_lines_to_skip is the number of lines to ignore at the beginning of the CSV file. Example First of all, let us create a table with the name EMPLOYEES using the following query − CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Now, let us insert rows into the above created table − INSERT INTO EMPLOYEES 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, ”MP”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The EMPLOYEES table obtained is as shown below − 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 MP 4500.00 7 Muffy 24 Indore 10000.00 Export Data to CSV − Now, we export the data from the EMPLOYEES table into a CSV file named “EMPLOYEES_BACKUP” using the following query − SELECT * FROM EMPLOYEES INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv” FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”rn”; After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query − Query OK, 7 rows affected (0.00 sec) Following is the image of “EMPLOYEES_BACKUP.csv” file when we opened it − Create Another Table − Now, let us create another table named “CUSTOMERS” with the same columns and data types as EMPLOYEES table − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Following is the output of the above code − Query OK, 0 rows affected (0.03 sec) Import Data from CSV − Now, we import all the data from “EMPLOYEES_BACKUP.csv” file into the CUSTOMERS table using the following query − LOAD DATA INFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv” INTO TABLE CUSTOMERS FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”n”; The result obtained is as follows − Query OK, 7 rows affected (0.01 sec) Records: 7 Deleted: 0 Skipped: 0 Warnings: 0 Verify Data Import − To verify that the data has been successfully imported into the CUSTOMERS table, we can use the following SELECT statement − SELECT * FROM CUSTOMERS; As we can see the output below, the CUSTOMERS table contains the same data as the EMPLOYEES table, as the CSV data has been imported successfully − 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 MP 4500.00 7 Muffy 24 Indore 10000.00 Importing a CSV File Using Client Program We can also import CSV file into database using Client Program. Syntax PHP NodeJS Java Python To import CSV file into database through a PHP program, we need to execute the “LOAD DATA INFILE” statement using the mysqli function query() as follows − $sql = “LOAD DATA INFILE ”C:/ProgramData/MySQL/MySQL

MySQL – Left Join Table of content MySQL Left Join Joining Multiple Tables with Left Join Left Join with WHERE Clause Left Join Using a Client Program ”; Previous Next Unlike inner join, which provides the intersection values of two tables, there is another type of join called Outer Join. This outer join provides the collection of matched and unmatched records of two tables in multiple cases. MySQL Left Join Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table. If the records in left table do not have their counterparts in the second table, NULL values are added. But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result. Syntax Following is the basic syntax of Left Join in MySQL − SELECT table1.column1, table2.column2… FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; Example Using the following query, let us create a table named CUSTOMERS, that contains the personal details of customers including their name, age, address and salary. 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 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 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows − 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 displayed as follows − OID DATE CUSTOMER_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 Left Join Query: Using the following left join query, we will retrieve the details of customers who made an order at the specified date. If there is no match found, the query below will return NULL in that record. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The joined result-set is obtained as − ID NAME AMOUNT DATE 1 Ramesh NULL NULL 2 Khilan 1560.00 2009-11-20 00:00:00 3 Kaushik 1500.00 2009-10-08 00:00:00 3 Kaushik 3000.00 2009-10-08 00:00:00 4 Chaitali 2060.00 2008-05-20 00:00:00 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL Joining Multiple Tables with Left Join Left Join also joins multiple tables where the first table is returned as a whole and the next tables are matched with the rows in the first table. If the records are not matched, NULL is returned. Syntax The syntax to join multiple tables using Left Join is given below − SELECT column1, column2, column3… FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name LEFT JOIN table3 ON table2.column_name = table3.column_name . . . Example To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create another table named EMPLOYEE, which consists of the details of employees in an organization and sales made by them, using the following query − CREATE TABLE EMPLOYEE ( EID INT NOT NULL, EMPLOYEE_NAME VARCHAR (30) NOT NULL, SALES_MADE DECIMAL (20) ); Now, we can insert values into this empty tables using the INSERT statement as follows − INSERT INTO EMPLOYEE VALUES (102, ”SARIKA”, 4500), (100, ”ALEKHYA”, 3623), (101, ”REVATHI”, 1291), (103, ”VIVEK”, 3426); The table is created as − EID EMPLOYEE_NAME SALES_MADE 102 SARIKA 4500 100 ALEKHYA 3623 101 REVATHI 1291 103 VIVEK 3426 Left Join Query: Let us join these three tables using the left join query given below −

MySQL – Transactions Table of content The MySQL Transactions Properties of Transactions Transactional Statements in MySQL The COMMIT Command The AUTOCOMMIT Command The ROLLBACK Command The SAVEPOINT Command Transaction-Safe Table Types in MySQL Transactions Using a Client Program ”; Previous Next The MySQL Transactions The MySQL transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail. Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. This will ensure no data losses or failed executions of SQL queries. Properties of Transactions There are four standard properties of transactions, often referred to by the acronym ACID − Atomicity − This ensures that all operations within a transaction are treated as a single unit. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in its original state. Consistency − This ensures that the database properly changes states upon a successfully committed transaction. Isolation − This enables transactions to operate independently and transparent to each other. Durability − This ensures that once a transaction is committed, its effects on the database are permanent and will survive system failures (e.g., power outages, hardware failures). Transactional Statements in MySQL In MySQL, the transactions begin with either START TRANSACTION, BEGIN or BEGIN WORK statements, and end with either a COMMIT or a ROLLBACK statement. The MySQL commands executed between the beginning and ending statements forms the main body of the transaction. To enable or disable the auto-commit option in a transaction, you can use the SET AUTOCOMMIT command. To enable auto-commit, set the command to ”1” or ”ON,” and to disable it, set the command to ”0” or ”OFF.” The COMMIT Command The COMMIT command is a transaction control command in MySQL. When issued, it finalizes the changes made to a database table up to that point in a transaction, making those changes permanent. As a result, these changes become visible to other active sessions in MySQL. Syntax Following is the syntax to execute the COMMIT command in MySQL − COMMIT; Example Let us create a table names 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) ); We are inserting some records into the above-created table − 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 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 Using the following query, start a transaction and delete the records from the CUSTOMERS table whose AGE is 25, then COMMIT the changes in the database − START TRANSACTION; DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT; Verification Two rows from the table would be deleted. To verify, display the modified CUSTOMERS table using the following SELECT statement − SELECT * FROM CUSTOMERS; Following is the output obtained − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 MP 4500.00 7 Muffy 24 Indore 10000.00 The AUTOCOMMIT Command You can control the behaviour of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued. The ROLLBACK Command The ROLLBACK command is a transactional command used to undo changes made in a transaction that have not been saved (committed) to the database. This command can only reverse the effects of transactions made since the last COMMIT or ROLLBACK statement was executed. Syntax Following is the syntax for ROLLBACK command in MySQL − ROLLBACK; Example Using the following query, delete the records from the CUSTOMERS table whose AGE is 25, then ROLLBACK the changes in the database − DELETE FROM CUSTOMERS WHERE AGE = 25; ROLLBACK; Verification The table will not be affected. To verify, display the modified CUSTOMERS table using the following SELECT statement − SELECT * FROM CUSTOMERS; Following is the table obtained − 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 You must remember that ROLLBACK only works inside a transaction. If you try to execute it without starting a transaction, the changes would not be revoked. The SAVEPOINT Command A SAVEPOINT is a logical rollback point within a transaction in MySQL. When you execute the ROLLBACK command, it reverts the changes made in the transaction back to the last COMMIT or the beginning of the transaction if there haven”t been any COMMITs. However, by creating save points within the transaction, you can establish specific points to which you can partially roll back the transaction. You can create multiple save points within a transaction to have multiple rollback options between two commits. Syntax The syntax for

MySQL – COALESCE() Function Table of content The MySQL COALESCE() Function Coalesce() Function Using Client Program ”; Previous Next Sometimes a record in a table might have missing data that the user cannot fill with zeroes. In such cases, MySQL allows the user to fill that record with a NULL value. A NULL value is nothing but a placeholder in database tables to represent missing values or when the data is not available to insert. The MySQL COALESCE() Function The MySQL COALESCE() function returns the first non-NULL value in a list of expressions. It takes multiple expressions as arguments and returns the value of the first expression that is not NULL. If all expressions are NULL, it returns NULL. When all its arguments are NOT NULL, the COALESCE() function evaluates the values based on the priority of their datatypes. For example, an integer is always prioritized over a character expression in the COALESCE() function, resulting in an integer as the output. When the COALESCE() function is used on MySQL tables with arguments representing field names that require comparison, the function compares the corresponding values in these columns, and retrieves the first occurrence that is NOT NULL. Syntax Following is the basic syntax for the COALESCE() function − SELECT COALESCE (expression_1, expression_2, …, expression_n) FROM table_name; Example In the following query, we are retrieving the first occurrence of nnon-NULL value from the list of arguments passed to the COALESCE() function − SELECT COALESCE(NULL, NULL, ”Hello”, ”Tutorialspoint”) AS RESULT; Output Following is the output obtained − RESULT Hello Example Now, let us create a table named “CUSTOMERS” to store personal details of customers, including their name, age, address, and salary using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20), AGE INT, 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, NULL, NULL ), (2, ”Khilan”, 25, ”Delhi”, NULL ), (3, ”kaushik”, 23, ”Kota”, NULL ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”MP”, NULL ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Following is the CUSTOMERS table obtained − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 NULL NULL 2 Khilan 25 Delhi NULL 3 Kaushik 23 Kota NULL 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 MP NULL 7 Muffy 24 Indore 10000.00 Now, let us use the SELECT statement to retrieve a result-set that contains values from the “NAME,” “AGE,” and “SALARY” columns. We will also pass “AGE” and “SALARY” as arguments to the COALESCE() function, and the return values will be displayed in another column named “RESULT.” − SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMERS; Output The result obtained is as shown below − NAME AGE SALARY RESULT Ramesh 32 NULL 32.00 Khilan 25 NULL 25.00 Kaushik 23 NULL 23.00 Chaitali 25 6500.00 6500.00 Hardik 27 8500.00 8500.00 Komal 22 NULL 22.00 Muffy 24 10000.00 10000.00 In the result-set, you will notice that the “NAME,” “AGE,” and “SALARY” values are displayed normally. However, the “RESULT” column contains the first non-NULL value from the “AGE” and “SALARY” columns. For example, in the first record, the “SALARY” column has a NULL value, but “AGE” holds a non-NULL value, so the “RESULT” column displays the age value. In cases where both columns contain non-NULL values, the COALESCE() function returns the highest value. Coalesce() Function Using Client Program We can also perform coalesce() function using client program. Syntax PHP NodeJS Java Python To perform COALESCE() function through a PHP program, we need to execute the “SELECT” statement using the mysqli function query() as follows − $sql = “SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER”; $mysqli->query($sql); To perform COALESCE() function through a JavaScript program, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows − sql = “SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER”; con.query(sql) To perform COALESCE() function through a Java program, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows − String sql = “SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER”; statement.executeQuery(sql); To perform COALESCE() function through a Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows − coalesce_query = “SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER”; cursorObj.execute(coalesce_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(); } // Create table Customer $sql = ”CREATE

MySQL – Self Join Table of content MySQL Self Join Self Join with ORDER BY Clause Self Join Using Client Program ”; Previous Next MySQL Self Join The MySQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, at least one table is temporarily renamed in the MySQL statement. Self Join is a type of inner join, which performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it. However, unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause. Syntax Following is the basic syntax of Self Join in MySQL − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field; Here, the WHERE clause could be any given expression based on your requirement. Example Self Join only requires one table to join itself; so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn. 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 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 join this table using the following Self Join query. Our aim is to establish a relationship among the said customers on the basis of their earnings. We are doing this with the help of WHERE clause. SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY; Output The resultant table displayed will list out all the customers that earn lesser than other customers − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 6 Chaitali Komal 4500.00 3 Chaitali Kaushik 2000.00 2 Chaitali Khilan 1500.00 1 Chaitali Ramesh 2000.00 6 Hardik Komal 4500.00 4 Hardik Chaitali 6500.00 3 Hardik Kaushik 2000.00 2 Hardik Khilan 1500.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00 2 Komal Khilan 1500.00 1 Komal Ramesh 2000.00 6 Muffy Komal 4500.00 5 Muffy Hardik 8500.00 4 Muffy Chaitali 6500.00 3 Muffy Kaushik 2000.00 2 Muffy Khilan 1500.00 1 Muffy Ramesh 2000.00 Self Join with ORDER BY Clause Furthermore, after joining a table with itself using self join, the records in the combined table can also be sorted in an ascending order using the ORDER BY clause. Following is the syntax for it − SELECT column_name(s) FROM table1 a, table1 b WHERE a.common_field = b.common_field ORDER BY column_name; Example In this example, executing the query below will join the CUSTOMERS table with itself using self join on a WHERE clause. Then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column. Here, we are arranging the records based on the salary column SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY ORDER BY a.SALARY; Output The resultant table is displayed as follows − ID EARNS_HIGHER EARNS_LESS LOWER_SALARY 2 Ramesh Khilan 1500.00 2 Kaushik Khilan 1500.00 2 Chaitali Khilan 1500.00 2 Hardik Khilan 1500.00 2 Komal Khilan 1500.00 2 Muffy Khilan 1500.00 3 Chaitali Kaushik 2000.00 1 Chaitali Ramesh 2000.00 3 Hardik Kaushik 2000.00 1 Hardik Ramesh 2000.00 3 Komal Kaushik 2000.00

MySQL – Comments Table of content The MySQL Comments The MySQL Single Line Comments The MySQL Multi-line Comments Where to Place Comments Comments Using a Client Program ”; Previous Next The MySQL Comments The MySQL Comment is a textual explanation added to a piece of code to provide additional information about the code. Comments are not meant to be executed as part of the code. It serve as notes for readers, including developers, to understand the purpose of the code, functionality, or any other relevant details. There are two types of comments in MySQL: Single-line comments and Multi-line comments The MySQL Single Line Comments Single-line comments are used for brief explanations on a single line. To create a single-line comment in MySQL, use two hyphens (–) followed by your comment text. Example In the following query, we are using a single line comment to write a text. SELECT * FROM customers; — This is a comment The MySQL Multi-line Comments Multi-line comments in MySQL are used for longer explanations or to comment out multiple lines of code. These comments start with /* and end with */. Everything between them is considered a comment. Example The following example uses multi-line comment as an explanation of the query − /* This is a multi-line comment. You can use it to explain complex queries or comment out multiple lines of code. SELECT * FROM products WHERE price > 50; */ Where to Place Comments You can place comments almost anywhere in your SQL code. Common places include − Before or after a SQL statement. Within a SQL statement to explain a specific part of it. At the beginning of a script or stored procedure to describe its purpose. — This is a comment before a query SELECT * FROM orders; SELECT /* This is an inline comment */ customer_name FROM customers; /* This is a comment block at the beginning of a script */ DELIMITER // CREATE PROCEDURE CalculateDiscount(IN product_id INT) BEGIN — Calculate discount logic here END // DELIMITER ; Comments Using a Client Program We can also comment any value using the client program. Syntax PHP NodeJS Java Python To comment any value or query through a PHP program, we need to execute the following comment methods using the mysqli function query() as follows − single line comment – multiline comment /**/ (using Query) $sql = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; $mysqli->query($sql); To comment any value or query through a JavaScript program, we need to execute the following comment methods using the query() function of mysql2 library as follows − single line comment – multiline comment /**/ (using Query) sql = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; con.query(sql); To comment any value or query through a Java program, we need to execute the following comment methods using the JDBC function executeQuery() as follows − single line comment – multiline comment /**/ (using Query) String sql = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; statement.executeQuery(sql); To comment any value or query through a Python program, we need to execute the following comment methods using the execute() function of the MySQL Connector/Python as follows − single line comment – multiline comment /**/ (using Query) comments_query = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai”” cursorObj.execute(comments_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 ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; if($mysqli->query($sql)){ printf(“Select query executed successfully…!n”); } printf(“Table records: n”); if($result = $mysqli->query($sql)){ while($row = mysqli_fetch_array($result)){ printf(“Id: %d”, $row[”ID”]); printf(“n”); } } if($mysqli->error){ printf(“Error message: “, $mysqli->error); } $mysqli->close(); Output The output obtained is as shown below − Select query executed successfully…! Table records: Id: 4 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); //create table sql = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; con.query(sql, function(err, result){ console.log(“Select query executed successfully(where we commented the name and address column)…!”); console.log(“Table records: “) if (err) throw err; console.log(result); }); }); Output The output obtained is as shown below − Select query executed successfully(where we commented the name and address column)…! Table records: [ { ID: 4 } ] import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Comments { 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…!”); //create table String sql = “SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ”Mumbai””; rs = st.executeQuery(sql); System.out.println(“Table records: “); while(rs.next()) { String id = rs.getString(“id”); System.out.println(“Id: ” + id); } }catch(Exception e) { e.printStackTrace(); } } } Output The output obtained is as shown below − Table records: Id: 4 import mysql.connector # Establishing the connection connection = mysql.connector.connect( host=”localhost”, user=”root”, password=”password”, database=”tut” ) cursorObj = connection.cursor() # Query with comments comments_query = “””SELECT ID /*NAME,

MySQL – Sequences Table of content Sequences in MySQL Retrieving AUTO_INCREMENT Values Renumbering an Existing Sequence Sequence Using a Client Program ”; Previous Next A sequence is a series of integers, starting from 1 and incrementing by 1 with each successive value. These sequences are usually used in databases, as many applications require each row in a table to have a unique identifier, and sequences provide an easy way to generate such values. Sequences in MySQL MySQL does not have a built-in sequence feature but provides an alternative in the form of the AUTO_INCREMENT column, which serves a similar purpose. In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique integer values (sequences) for a column. By default, this sequence begins with an initial value of 1 and increments by 1 for each new row that is added. Syntax Following is the syntax of AUTO_INCREMENT attribute in MySQL − CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 datatype, column3 datatype, … columnN datatype ); Example In the following example, we are creating a table named “CUSTOMERS” and, in addition, defining the AUTO_INCREMENT attribute for the “ID” column of the table − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us insert some records into the above-created table − INSERT INTO CUSTOMERS VALUES (NULL, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (NULL, ”Khilan”, 25, ”Delhi”, 1500.00), (NULL, ”Kaushik”, 23, ”Kota”, 2000.00), (NULL, ”Chaitali”, 25, ”Mumbai”, 6500.00), (NULL, ”Hardik”, 27, ”Bhopal”, 8500.00), (NULL, ”Komal”, 22, ”Hyderabad”, 4500.00), (NULL, ”Muffy”, 24, ”Indore”, 10000.00); Output We can see in the table displayed below that the values in the “ID” column are automatically incremented − 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 Retrieving AUTO_INCREMENT Values To obtain AUTO_INCREMENT values in MySQL, you can use the LAST_INSERT_ID() SQL function. This function can be used in any client that can issue SQL statements. Alternatively, in PERL and PHP scripts, specific functions are available to retrieve the auto-incremented value of the last record. PERL Example You can access the AUTO_INCREMENT value generated by a query using the mysql_insertid attribute. This attribute can be accessed either through a database handle or a statement handle, depending on how you execute the query. The following example references it through the database handle − $dbh->do (“INSERT INTO insect (name,date,origin) VALUES(”moth”,”2001-09-14”,”windowsill”)”); my $seq = $dbh->{mysql_insertid}; PHP Example After executing a query that generates an AUTO_INCREMENT value, you can retrieve the value using the mysql_insert_id( ) command − mysql_query (“INSERT INTO insect (name,date,origin) VALUES(”moth”,”2001-09-14”,”windowsill”)”, $conn_id); $seq = mysql_insert_id ($conn_id); Renumbering an Existing Sequence In some cases, you may need to re-sequence records in a table, especially if you have deleted many records. Be careful when resequencing if your table is related to other tables through joins. If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the AUTO_INCREMENT column from the table, then add it again. Example The following example shows how to renumber the id values in the table using this technique. ALTER TABLE CUSTOMERS DROP id; ALTER TABLE CUSTOMERS ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id); Output Following is the output obtained − Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 Starting a Sequence at a Specific Value By default, MySQL starts sequences from 1, but you can specify a different initial value when creating the table. Example The following example demonstrates how to start the sequence from 100 during table creation − CREATE TABLE CUSTOMERS ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID), NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2) )AUTO_INCREMENT = 100; Output Output of the above code is as shown below − Query OK, 0 rows affected (0.04 sec) Alternatively, you can create the table first and then set the initial sequence value using the ALTER TABLE command as shown below − ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100; Sequence Using a Client Program We can also create a sequence using the client program. Syntax PHP NodeJS Java Python To create a sequence on a column of a table through a PHP program, we need to specify auto_increment for a specific column while creating the table using the mysqli function query() as follows − $sql = “CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)”; $mysqli->query($sql); To create a sequence on a column of a table through a JavaScript program, we need to specify auto_increment for a specific column while creating the table using the query() function of mysql2 library as follows − sql = “CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)”; con.query(sql); To create a sequence on a column of a table through a Java program, we need to specify auto_increment for a specific column while creating the table using the JDBC function execute() as follows − String sql = “CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)”; statement.execute(sql); To create a sequence on a column of a table through a Python program, we need to specify auto_increment for a specific column while creating the table using the execute() function of the MySQL Connector/Python as follows − create_table_query = “CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)” cursorObj.execute(create_table_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli