Category: mysql

MySQL – Quick Guide ”; Previous Next MySQL – Introduction What is a Database? A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds. Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems. Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys. A Relational DataBase Management System (RDBMS) is a software that − Enables you to implement a database with tables, columns and indexes. Guarantees the Referential Integrity between rows of various tables. Updates the indexes automatically. Interprets an SQL query and combines information from various tables. RDBMS Terminology Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database. Database − A database is a collection of tables, with related data. Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet. Column − One column (data element) contains data of one and the same kind, for example the column postcode. Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription. Redundancy − Storing data twice, redundantly to make the system faster. Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row. Foreign Key − A foreign key is the linking pin between two tables. Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique. Index − An index in a database resembles an index at the back of a book. Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row. MySQL Database MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons − MySQL is released under an open-source license. So you have nothing to pay to use it. MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages. MySQL uses a standard form of the well-known SQL data language. MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc. MySQL works very quickly and works well even with large data sets. MySQL is very friendly to PHP, the most appreciated language for web development. MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB). MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments. Before You Begin Before you begin this tutorial, you should have a basic knowledge of the information covered in our PHP and HTML tutorials. This tutorial focuses heavily on using MySQL in a PHP environment. Many examples given in this tutorial will be useful for PHP Programmers. We recommend you check our PHP Tutorial for your reference. MySQL – Installation All downloads for MySQL are located at MySQL Downloads. Pick the version number of MySQL Community Server which is required along with the platform you will be running it on. Installing MySQL on Linux/UNIX The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its website − MySQL − The MySQL database server manages the databases and tables, controls user access and processes the SQL queries. MySQL-client − MySQL client programs, which make it possible to connect to and interact with the server. MySQL-devel − Libraries and header files that come in handy when compiling other programs that use MySQL. MySQL-shared − Shared libraries for the MySQL client. MySQL-bench − Benchmark and performance testing tools for the MySQL database server. The MySQL RPMs listed here are all built on a SuSE Linux system, but they will usually work on other Linux variants with no difficulty. Now, you will need to adhere to the steps given below, to proceed with the installation − Login to the system using the root user. Switch to the directory containing the RPMs. Install the MySQL database server by executing the following command. Remember to replace the filename in italics with the file name of your RPM. [root@host]# rpm -i MySQL-5.0.9-0.i386.rpm The above command takes care of installing the MySQL server, creating a user of MySQL, creating necessary configuration and starting the MySQL server automatically. You can find all the MySQL related binaries in /usr/bin and /usr/sbin. All the tables and databases will be created in the /var/lib/mysql directory. The following code box has an optional but recommended step to install the remaining RPMs in the same manner − [root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm Installing MySQL on Windows The default installation on any version of Windows is now much easier than it used to be, as MySQL now comes neatly packaged with an installer. Simply download the installer package, unzip it anywhere and run the setup.exe file. The default installer setup.exe will walk you through the trivial process and by default will install

MySQL – Reset Auto-Increment Table of content AUTO-INCREMENT in MySQL The MySQL RESET Auto-Increment RESET using ALTER TABLE Statement RESET using TRUNCATE TABLE Statement Resetting Auto-Increment Using Client Program ”; Previous Next Most of the tables in MySQL use sequential values to represent records, like serial numbers. Instead of manually inserting each value one by one, MySQL uses the “AUTO_INCREMENT” to handle this automatically. AUTO-INCREMENT in MySQL AUTO_INCREMENT in MySQL is used to generate unique numbers in ascending order automatically as you add new records to a table. It is very useful for applications that require each row to have a distinct value. When you define a column as an AUTO_INCREMENT column, MySQL takes care of the rest. It starts with the value 1 and increments it by 1 for each new record you insert, creating a sequence of unique numbers for your table. Example The following example demonstrates the usage of AUTO_INCREMENT on a column in database table. Here, we are creating a table named ”insect” with AUTO_INCREMENT applied to the ”id” column. 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 ); Now, you don”t need to manually specify values for the ”id” column when inserting records. Instead, MySQL handles it for you, starting with 1 and incrementing by 1 for each new record. To insert values in other columns of the table, use the following query − INSERT INTO insect (name,date,origin) VALUES (”housefly”,”2001-09-10”,”kitchen”), (”millipede”,”2001-09-10”,”driveway”), (”grasshopper”,”2001-09-10”,”front yard”); The insect table displayed is as follows. Here, we can see that the ”id” column values are automatically generated by MySQL − id name date origin 1 housefly 2001-09-10 kitchen 2 millipede 2001-09-10 driveway 3 grasshopper 2001-09-10 front yard The MySQL RESET Auto-Increment The default AUTO_INCREMENT values on a table start from 1, i.e., the values being inserted usually start from 1. However, MySQL also has a provision to reset these AUTO-INCREMENT values to another number, enabling the sequence to start inserting from the specified reset value. You can reset the AUTO_INCREMENT value in three ways: using ALTER TABLE, TRUNCATE TABLE, or dropping and recreating the table. RESET using ALTER TABLE Statement The ALTER TABLE statement in MySQL is used to update a table or make any alterations in it. Hence, using this statement to reset an AUTO_INCREMENT value is perfectly valid choice. Syntax Following is the syntax to reset autoincrement using ALTER TABLE − ALTER TABLE table_name AUTO_INCREMENT = new_value; Example In this example, we are using the ALTER TABLE statement to reset the AUTO_INCREMENT value to 5. Note that the new AUTO_INCREMENT value be greater than the number of records already present in the table − ALTER TABLE insect AUTO_INCREMENT = 5; Following is the output obtained − Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Now, let us insert another value into the table ”insect” created above and check the new result-set, using the following queries − INSERT INTO insect (name,date,origin) VALUES (”spider”, ”2000-12-12”, ”bathroom”), (”larva”, ”2012-01-10”, ”garden”); We get the result as shown below − Query OK, 2 row affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 To verify whether the new records you inserted will start with the AUTO_INCREMENT value set to 5, use the following SELECT query − SELECT * FROM insect; The table obtained is as shown below − id name date origin 1 housefly 2001-09-10 kitchen 2 millipede 2001-09-10 driveway 3 grasshopper 2001-09-10 front yard 5 spider 2000-12-12 bathroom 6 larva 2012-01-10 garden RESET using TRUNCATE TABLE Statement Another way to reset auto-incrementing column to the default value is by using the TRUNCATE TABLE command. This will delete the existing data of a table, and when you insert new records, the AUTO_INCREMENT column starts from the beginning (usually 1). Example Following is an example to reset the AUTO_INCREMENT value to default, i.e. ”0”. For that, firstly truncate the ”insect” table created above using the TRUNCATE TABLE Command as follows − TRUNCATE TABLE insect; The output obtained is as follows − Query OK, 0 rows affected (0.04 sec) To verify whether the records of the table is deleted, use the following SELECT query − SELECT * FROM insect; The result produced is as follows − Empty set (0.00 sec) Now, insert values again using the following INSERT statement. INSERT INTO insect (name,date,origin) VALUES (”housefly”,”2001-09-10”,”kitchen”), (”millipede”,”2001-09-10”,”driveway”), (”grasshopper”,”2001-09-10”,”front yard”), (”spider”, ”2000-12-12”, ”bathroom”); After executing the above code, we get the following output − Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 You can verify whether the records in the table have been reset using the following SELECT query − SELECT * FROM insect; The table displayed is as follows − id name date origin 1 housefly 2001-09-10 kitchen 2 millipede 2001-09-10 driveway 3 grasshopper 2001-09-10 front yard 4 spider 2000-12-12 bathroom Resetting Auto-Increment Using

MySQL – BIT Table of content The MySQL BIT Data Type BIT Datatype Using a Client Program ”; Previous Next A bit represents the basic unit of data in programming languages. It can store only two values, represented as 0 or 1. The MySQL BIT Data Type The MySQL BIT data type is used to store binary values within a specified range. The range is determined by the number of bits you allocate to the BIT column. If we try to insert an integer value instead of BIT values, MySQL automatically converts them into BIT values. We have to ensure that the integer value we are adding must be within the range for conversion to BIT values. For instance, if you have a BIT(3) column, it can store values from 000 to 111 in binary, which corresponds to 0 to 7 in integer format. If you try to insert the integer 8 into this BIT(3) column, you”ll get an error because 8 in binary is 1000, which is outside the valid range of the column. Syntax Following is the syntax of the MySQL BIT datatype − BIT(n) Here, the range of n value is from 1 to 64. If you don”t provide the “n” value, the default is 1, resulting in a single-bit BIT column. Hence, the following queries will give the same output − Column_name BIT(1); and Column_name BIT; Bit Value Literal To specify bit value literals, you can use the b”val or 0bval notations, where val is a binary value containing only 0s and 1s. The leading ”b” is case-insensitive. b01 B11 Note that the 0b notation is case-sensitive, so 0B”1000” is an invalid bit literal value. 0B”1000” Example Let us create a table named STUDENTS and use the BIT data type for the AGE column as shown below − CREATE TABLE STUDENTS( ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) ); Following is the output obtained − Query OK, 0 rows affected (0.01 sec) Now, we are inserting the values “5” and “3” into the AGE column of the STUDENTS table − INSERT INTO STUDENTS (NAME, AGE) VALUES (”Varun”, 5), (”Akash”, 3); Output of the above query is as shown below − Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 We can use the following query to display the inserted values in the AGE column of the STUDENTS table − SELECT * from STUDENTS; We can see in the output below that the values “5” and “3” are stored in binary format − ID NAME AGE 1 Varun 0x05 2 Akash 0x03 Now, let us insert another value “10”. In binary format, “10” is represented as “1010”. However, we defined the AGE column to have a range of only three bits. Therefore, the following query will generate an error because the value 10 is greater than 7 − INSERT INTO STUDENTS (NAME, AGE) VALUES (”Priya”, 10); The output indicates that the data is too long for the AGE column. ERROR 1406 (22001): Data too long for column ”AGE” at row 1 To insert bit value literals into the “AGE” column, you can use the B”val notation. Here, we are inserting “110” which is equivalent to the integer value “6” as shown below − INSERT INTO STUDENTS (NAME, AGE) VALUES(”Priya”, B”110”); The result obtained is as follows − Query OK, 1 row affected (0.01 sec) Let us display all the records in the “STUDENTS” table using the following query − SELECT * from STUDENTS; We can see in the output below that the value “6” has been inserted in binary format as “0x06” − ID NAME AGE 1 Varun 0x05 2 Akash 0x03 3 Priya 0x06 Verification To verify and display the inserted values in the “AGE” column in binary/bit format, you can use the MySQL BIN() function − SELECT ID, NAME, BIN(AGE) FROM STUDENTS; The output shows the values in binary format − ID NAME BIN(AGE) 1 Varun 101 2 Akash 11 3 NULL 110 In the above output, we can see that the leading zeros are removed. If we want to display them, we can use the LPAD function as shown below − SELECT ID, NAME, LPAD(BIN(AGE), 5, “0”) FROM STUDENTS; Following is the output obtained − ID NAME LPAD(BIN(AGE), 5, “0”) 1 Varun 00101 2 Akash 00011 3 NULL 00110 BIT Datatype Using a Client Program We can also create column of the BIT datatype using the client program. Syntax PHP NodeJS Java Python To create a column of BIT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows − $sql = ”CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )”; $mysqli->query($sql); To create a column of BIT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

MySQL – REGEXP_INSTR() Function Table of content MySQL REGEXP_INSTR() Function Client Program ”; Previous Next MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search. A regular expression is technically defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc. These regular expressions in MySQL provide various functions and operators to easily perform the search operations. One such function is regexp_instr() function. MySQL REGEXP_INSTR() Function The MySQL regexp_instr() function is used to match specified patterns with either a string or the data in database tables. This function returns the starting index of the substring of a string that matches the specified pattern, returns 0 if there is no match, or NULL if the string or the pattern is NULL. Character indices of this string starts at 1. Syntax Following is the syntax of the MySQL regexp_instr() function − REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]]) Where expr is the string in which the search is to be performed and pat is the pattern/regular expression that is to be searched. In addition to the expression and string values this method accepts the following optional parameters. Parameters The regexp_instr() function takes following parameter values − expr: The string in which search is performed pattern: The pattern that is searched in the string Following are the optional arguments that can be passed to this function − pos: The position in expr at which to start the search. If omitted, the default is 1. occurrence: Which occurrence of a match to search for. If omitted, the default is 1. return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring”s first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0. match_type:This is a string which consists of various characters representing the desired features of the match this may contain one or all of these characters. Following are various characters using which you can specify the match type. c This character indicates the case-sensitive matching. i This character indicates the case-insensitive matching. m This character indicates the i.e., multiple lines (separated by line terminators) with in a single string are recognized. n If this character is specified, the dot (.) character matches line terminators. u If this character is specified, only new line character is recognized as line ending by ., ^, and $. Example In this example, we are performing a search operation on a simple string using the MySQL REGEXP_INSTR() function − SELECT REGEXP_INSTR(”Welcome To Tutorialspoint!”, ”To”) AS RESULT; The pattern ”To” is found at 9th index − Result 9 If there is no match found in the string, the return value will be ”0” − SELECT REGEXP_INSTR(”Welcome To Tutorialspoint!”, ”xx”) AS RESULT; Following is the output − Result 0 Example Let us also pass optional arguments to this function and observe the result. Here, the search search position starts at 5 to find the 2nd occurrence of ”T” after that position. As the return option is set to 1, the position following the match is returned. − SELECT REGEXP_INSTR(”Welcome To Tutorialspoint!”, ”T”, 5, 2, 1) AS RESULT; Output Following is the output − Result 13 Example The following query searches for the position for any alphabetic character in the provided string ”9848032919”. If found, it returns 1. Else, 0. SELECT REGEXP_INSTR(”9848032919”, ”[[:alpha:]]”); Output Executing the query above will produce the following output − REGEXP_INSTR(”9848032919”, ”[[:alpha:]]”) 0 Example The below query searches for the position of either ”town” or ”city” in the provided string − SELECT REGEXP_INSTR(”Vishakapatnam is city of destiny ”, ”town|city”) As Result; Output The output for the above query is produced as given below − Result 0 Example If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing ”NULL” as search pattern. SELECT REGEXP_INSTR(”Tutorialspoint”, NULL) As Result; If we compile and run the query, the result is produced as follows − Result NULL In the following query, we are passing ”NULL” to the string parameter. SELECT REGEXP_INSTR(NULL, ”to”) As Result; When we execute the query above, the output is obtained as follows − Result NULL Example In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_INSTR() function. Firstly, let us create the table using the following query − 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) ); Insert some records into the above created table using the following INSERT query − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”,

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 – RLIKE Operator Table of content MySQL RLIKE Operator Patterns used with RLIKE RLIKE On Strings RLIKE Operator Using a Client Program ”; Previous Next MySQL RLIKE Operator The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise. A regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single/multiple characters or words, etc. The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria. Syntax Following is the basic syntax of the RLIKE operator in MySQL − expression RLIKE pattern Patterns used with RLIKE RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator. Pattern What the pattern matches ^ Beginning of string $ End of string * Zero or more instances of preceding element + One or more instances of preceding element {n} n instances of preceding element {m,n} m through n instances of preceding element . Any single character […] Any character listed between the square brackets [^…] Any character not listed between the square brackets [A-Z] Any uppercase letter [a-z] Any lowercase letter [0-9] Any digit (from 0 to 9) [[:<:]] Beginning of words [[:>:]] Ending of words [:class:] A character class, i.e. use [:alpha:] to match letters from the alphabet p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3 Example The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named CUSTOMERS using the following query − 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, insert some values into the above created table using the INSERT statements given below − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to display all the records present in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following are the records present in CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 RLIKE with Patterns − In the following query, we are finding all the records from CUSTOMERS table whose name starts with ”ch” − SELECT * FROM CUSTOMERS WHERE NAME RLIKE ”^ch”; Executing the query above will produce the following output − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 The following query displays all the records whose names ends with ”sh” − SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ”sh$”; Following are records whose name ends with ”sh” − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 Here, we are retrieving the records that have names containing ”an” − SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ”an”; Following are the records − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 This following query retrieves all the records whose names are ending with an vowel − SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ”[aeiou]$”; Following are the records − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 The below query finds all the names starting with a consonant and ending with ”ya” − SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ”^[^aeiou].*ya$”; As we observe the output, there are no records that starts with consonant and ends with ”ya”. Empty set (0.00 sec) RLIKE On Strings The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string,

MySQL – Delete Duplicate Records Table of content The MySQL Delete Duplicate Records Find Duplicate Values Delete Duplicate Records Delete Duplicate Records Using Client Program ”; Previous Next The MySQL Delete Duplicate Records Duplicate records in a database, including MySQL, is a very common occurrence. A MySQL database stores data in the form of tables consisting of rows and columns. Now, a record is said to be duplicated when two or more rows in a database table have same values. This redundancy might occur due to various reasons − The row might be inserted twice. When raw data is imported from external sources. There might be a bug in the database application. Whatever might be reason, deleting such redundancy becomes important to increase the data accuracy with less errors, or to increase the efficiency of database performance. Find Duplicate Values Before removing duplicate records, we must find whether they exist in a table or not. This is possible using the following ways − GROUP BY Clause COUNT() Method Example Let us first create table named “CUSTOMERS” containing duplicate values − CREATE TABLE CUSTOMERS( ID int, NAME varchar(100) ); Using the following INSERT query, insert few records into the “CUSTOMERS” table. Here, we have added “John” as duplicate record 3 times − INSERT INTO CUSTOMERS VALUES (1,”John”), (2,”Johnson”), (3,”John”), (4,”John”); The CUSTOMERS table obtained is as follows − id name 1 John 2 Johnson 3 John 4 John Now, we are retrieving the record that is duplicated in the table using the COUNT() method and GROUP BY clause as shown in the following query − SELECT NAME, COUNT(NAME) FROM CUSTOMERS GROUP BY NAME HAVING COUNT(NAME) > 1; Output Following is the output obtained − NAME COUNT(NAME) John 3 Delete Duplicate Records To delete duplicate records from a database table, we can use the DELETE command. However, this DELETE command can be used in two ways to remove duplicates from a table − Using DELETE… JOIN Using ROW_NUMBER() Function Using DELETE… JOIN To use DELETE… JOIN command in order to remove duplicate records from a table, we perform inner join on itself. This is applicable for cases that are not completely identical. For instance, suppose there is a repetition of customer details in customer records, but the serial number keeps incrementing. Here, the record is duplicated even if the ID is not same. Example In the following query, we are using the CUSTOMERS table created previously to remove duplicate records using DELETE… JOIN command − DELETE t1 FROM CUSTOMERS t1 INNER JOIN CUSTOMERS t2 WHERE t1.id < t2.id AND t1.name = t2.name; Output Following is the output obtained − Query OK, 2 rows affected (0.01 sec) Verification We can verify whether the duplicate records have been removed or not using the following SELECT statement − SELECT * FROM CUSTOMERS; We can see in the table obtained that the query removed duplicates and leave distinct records in the table − ID NAME 2 Johnson 4 John Using ROW_NUMBER() Function The ROW_NUMBER() Function in MySQL is used to assign a sequential number, starting from 1, to each row in a result-set obtained from a query. Using this function, MySQL allows you to detect the duplicate rows, which can be removed with the DELETE statement. Example Here, we are applying the ROW_NUMBER() function to the CUSTOMERS table having duplicate values in the ”NAME” column. We will assign row numbers within a partition based on the ”NAME” column using the following query − SELECT id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM CUSTOMERS; Following is the output obtained − id row_num 1 1 3 2 4 3 2 1 Now, with the following statement, delete the duplicate rows (rows with a row number greater than 1) − DELETE FROM CUSTOMERS WHERE id IN( SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM CUSTOMERS) AS temp_table WHERE row_num>1 ); We get the output as shown below − Query OK, 2 rows affected (0.00 sec) To verify whether the duplicate records have been removed or not, use the following SELECT query − SELECT * FROM CUSTOMERS; The result produced is as follows − ID NAME 1 John 2 Johnson Delete Duplicate Records Using Client Program We can also delete duplicate records using client program. Syntax PHP NodeJS Java Python To delete duplicate records through a PHP program, we need to perform inner join with “DELETE” command using the mysqli function query() as follows − $sql = “DELETE t1 FROM DuplicateDeleteDemo t1 INNER JOIN DuplicateDeleteDemo t2 WHERE t1.id < t2.id AND t1.name = t2.name”; $mysqli->query($sql); To delete duplicate records through a JavaScript program, we need to perform inner join with “DELETE” command using the query() function of mysql2 library as follows − sql = “DELETE t1 FROM DuplicateDeleteDemo t1 INNER JOIN DuplicateDeleteDemo t2 WHERE t1.id < t2.id AND t1.name =

MySQL – Select Database (USE Statement) Table of content MySQL USE Statement Selecting MySQL Database Selecting a Non Existing MySQL Database Selecting Database Using a Client Program ”; Previous Next Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available with the MySQL Server. MySQL USE Statement To select a database in MySQL, we use the SQL USE statement. Once a specific database is selected, we can perform different operations such as creating tables, adding data, updating, and deleting information. Every operation we perform after selecting a database will be stored in that particular database. Syntax Following is the syntax of the USE statement in SQL − USE DatabaseName; Here, the “DatabaseName” is a placeholder representing the name of the database that we want to use. The database name must always be unique within the MySQL or any other RDBMS. Example Let us start by creating a database named TUTORIALS using the following CREATE query − create database TUTORIALS; Now, we will fetch all the databases present in MySQL server using the below query − Show databases; Following are the list of databases − Field information_schema mysql performance_schema tutorials The following will select/switch the current database to TUTORIALS − USE TUTORIALS; Output The database has been selected/switched successfully without any error. Database changed Once we finish switching to the database TUTORIALS, we can perform operations such as creating a table, and inserting data in that table as shown below − 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) ); Once the table is created, execute the following query to retrieve the data present in the current database (TUTORIALS) − SHOW TABLES; As we can see in the output below, the CUSTOMERS table we have created after selecting the TUTORIALS database is stored in it. Tables_in_tutorials customers Selecting a Non Existing MySQL Database If we try to select/switch a non-existent database in a MySQL server, it will result in an error stating that “Unkwown Database”. Example Here, we are trying to select/swith to the database which doesn”t exist − USE NonExistingDatabase; The output for the above query is produced as given below − ERROR 1049 (42000): Unknown database ”nonexistingdatabase” Selecting Database Using a Client Program Besides selecting/switching a database in a MySQL server using a MySQL query, we can also use a client program to perform the USE operation. Syntax Following are the syntaxes of this operation in various programming languages − PHP NodeJS Java Python To select/switch a database in a MySQL server through PHP program, we need to execute the USE statement using the mysqli function query() as follows − $sql = “USE Database_name”; $mysqli->query($sql); To select/switch a database in a MySQL server through Node.js program, we need to execute the USE statement using the query() function of the mysql2 library as follows − sql = “USE Database_name”; con.query(sql); To select/switch a database in a MySQL server through Java program, we need to execute the USE statement using the JDBC function executeUpdate() as follows − String sql = “USE Database_name”; st.execute(sql); To select/switch a database in a MySQL server through Python program, we need to execute the USE statement using the execute() function of the MySQL Connector/Python as follows − sql = “USE Database_name”; cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”root@123”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass); if($mysqli->connect_errno ) { printf(“Connect failed: %s<br />”, $mysqli->connect_error); exit(); } printf(”Connected successfully.<br />”); if ($mysqli->query(“USE TUTORIALS”)) { printf(“Database selected successfully…!<br />”); } if ($mysqli->errno) { printf(“Database could not connect: %s<br />”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Connected successfully. Database selected successfully…! var mysql = require(”mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “Nr5a0204@123″ }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log(“Connected!”); console.log(“————————–“); //Selecting a Database sql = “Use TUTORIALS;” con.query(sql, function(err, result){ if (err) throw err console.log(“Database selected successfully…”) }); }); Output The output produced is as follows − Connected! ————————– Database selected successfully…! import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SelectDatabase { public static void main(String[] args) { String url = “jdbc:mysql://localhost:3306/”; String user = “root”; String password = “password”; System.out.println(“Connecting to select database…..!”); try { Class.forName(“com.mysql.cj.jdbc.Driver”); Connection con = DriverManager.getConnection(url, user, password); Statement st1 = con.createStatement(); String sql = “USE TUTORIALS”; st1.execute(sql); System.out.println(“Database selected successfully…!”); }catch(Exception e) { e.printStackTrace(); } } } Output The output obtained is as shown below − Connecting to select database…..! Database selected successfully…! import mysql.connector # creating the connection object connection = mysql.connector.connect( host =”localhost”, user =”root”, password =”password”) # creating cursor object cursorObj = connection.cursor() # selecting the database cursorObj.execute(“USE TUTORIALS”) # Fetching a single row print(“Database selected Successfully…!”) # disconnecting from server connection.close() Output Following is the output of the above code − Database selected Successfully…!