Category: mysql

MySQL – Wildcards Table of content The MySQL wildcards The MySQL Percent % Wildcard The MySQL Underscore _ Wildcard ”; Previous Next The MySQL wildcards The MySQL wildcards are special characters used in combination with the LIKE operator to search for text patterns in a table column. MySQL provides two wildcards: percent (%) and underscore (_). The following table mentions the use case of the two wildcards in MySQL − S.NO Wildcard & Description 1 The percent (%) Matches zero or one characters”. For example, ”a%” matches strings starting with ”a” like ”android” or ”aeroplane”. 2 The underscore (_) Matches a single character. For instance, ”_un” matches three-character strings ending with ”un” like ”gun” or ”bun” Syntax Following is the syntax of % and _ wildcards in MySQL − SELECT * FROM table_name WHERE column_name LIKE wildcard; The wildcard characters can be used in combination with each other. The following table demonstrates different ways of using ”%” and ”_” with the LIKE operator in a WHERE clause − S.NO Statement & Description 1 WHERE SALARY LIKE ”200%” Finds any values that start with 200. 2 WHERE SALARY LIKE ”%200%” Finds any values that have 200 in any position. 3 WHERE SALARY LIKE ”_00%” Finds any values that have 00 in the second and third positions. 4 WHERE SALARY LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length. 5 WHERE SALARY LIKE ”%2” Finds any values that end with 2. 6 WHERE SALARY LIKE ”2%0” Finds any value that starts with 2 and ends with 0. 7 WHERE SALARY LIKE ”_2%3” Finds any values that have a 2 in the second position and end with a 3. 8 WHERE SALARY LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3. The MySQL Percent % Wildcard The MySQL % wildcard is a symbol used in SQL queries for pattern matching. It represents any sequence of characters (including zero characters) within a string. When used with the LIKE operator in a WHERE clause, % allows you to search for values that match a specified pattern. Example First, 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) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) ); Now, let us insert values into the table created above using the INSERT 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 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 Here, we are retrieving all the records from the CUSTOMERS table where SALARY starts with 2000 − SELECT * FROM CUSTOMERS WHERE SALARY LIKE ”2000%”; Output The output of the above query is as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 Example In the following query, we are fetching all the records where ADDRESS starts with ”D” and ends with ”i” − SELECT * FROM CUSTOMERS WHERE ADDRESS LIKE ”D%i”; Output On executing the given query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 Example Here, we are finding all the records where ADDRESS ends with ”d” − SELECT * FROM CUSTOMERS WHERE ADDRESS LIKE ”%d”; Output When we execute the above query, the output is obtained as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 4500.00 Example In the following query, we are trying to fetch all the records where SALARY has ”1” in any position − SELECT * FROM CUSTOMERS WHERE SALARY LIKE ”%1%”; Output The output produced from the above query is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 7 Muffy 24 Indore 10000.00 The MySQL Underscore _ Wildcard The MySQL underscore Wildcard represents a single character at the position where it is used. When

MySQL – Aliases Table of content The MySQL Alias Aliasing Column Names Aliasing Table Names Aliasing with Self Join ”; Previous Next The MySQL Alias The MySQL Alias is used to assign a temporary name, called an Alias to a table or a column in SQL. Aliases are created using the AS keyword and are used to refer to a specific table or a column without changing its original name. They are used to make the query easily readable when working tables or columns with similar names. Aliasing Column Names Aliasing column names is used to assign a different name to a column of a table. Syntax The basic syntax of a column alias is as follows − SELECT column_name AS alias_name FROM table_name; Example First, 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 values into the table created above using the INSERT 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 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 Example In the following query, we are creating two aliases, one for the ID column and one for the AGE column − SELECT ID AS CUST_ID, AGE AS CUST_AGE FROM CUSTOMERS; Output The output of the above query is produced as given below − CUST_ID CUST_AGE 1 32 2 25 3 23 4 25 5 27 6 22 7 24 Example If we want the alias name to contain spaces, we can use the double quotation marks as shown in the query below − SELECT ID AS “CUST ID”, AGE AS “CUST AGE” FROM CUSTOMERS; Output On executing the given query, the output is displayed as follows − CUST ID CUST AGE 1 32 2 25 3 23 4 25 5 27 6 22 7 24 Example In the query below, we are creating an alias named ”INFORMATION” that combines two columns (AGE, ADDRESS) − SELECT ID, CONCAT(AGE, ”, ”, ADDRESS) AS INFORMATION FROM CUSTOMERS; Output When we execute the above query, the output is obtained as follows − ID INFORMATION 1 32, Ahmedabad 2 25, Delhi 3 23, Kota 4 25, Mumbai 5 27, Bhopal 6 22, Hyderabad 7 24, Indore Aliasing Table Names Aliasing table names is used to assign a different name to a table. Syntax Following is the syntax of a table alias − SELECT column1, column2…. FROM table_name AS alias_name Example Let us create another table with the name ORDERS using the following query − CREATE TABLE ORDERS ( OID INT NOT NULL, DATES DATETIME NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT INT NOT NULL, PRIMARY KEY (OID) ); Now, let us insert values into the table created above using the INSERT INTO statement as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000), (100, ”2009-10-08 00:00:00”, 3, 1500), (101, ”2009-11-20 00:00:00”, 2, 1560), (103, ”2008-05-20 00:00:00”, 4, 2060); The ORDERS table obtained is as follows − OID DATES CUSTOMER_ID AMOUNT 100 2009-10-08 00:00:00 3 1500 101 2009-11-20 00:00:00 2 1560 102 2009-10-08 00:00:00 3 3000 103 2008-05-20 00:00:00 4 2060 In the following query, the CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” − SELECT C.ID, C.NAME, C.AGE, O.AMOUNT FROM CUSTOMERS AS C, ORDERS AS O WHERE C.ID = O.CUSTOMER_ID; Output This would produce the following result − ID NAME AGE AMOUNT 3 Kaushik 23 1500 2 Khilan 25 1560 3 Kaushik 23 3000 4 Chaitali 25 2060 Aliasing with Self Join The MySQL Self Join is used to join

MySQL – VARCHAR Table of content The MySQL Varchar Data Type Varchar Datatypes Using a Client Program ”; Previous Next The MySQL Varchar Data Type The MySQL VARCHAR data type is used to store variable-length character strings, having a length up to 65,535 bytes. In MySQL, when you store text in a VARCHAR column, it needs a little extra space to keep track of how long the text is. This extra space can be either 1 or 2 bytes, depending on the length of the text. If the text is short (less than 255 characters), it uses 1 byte for length. For longer text, it uses 2 bytes. The total size of data plus the length info cannot exceed 65,535 bytes for a row in a table. Example In the following query, we are creating a new table named test_table that has two columns column1 and column2. As we can see in the below code block, the columns (column1 = 32765 and column2 = 32766) makes 65531 bytes. These columns will take 2 bytes each as a length prefix. Therefore, the columns totally make 32765+2+32766+2 = 65535 bytes − CREATE TABLE test_table ( column1 VARCHAR(32765) NOT NULL, column2 VARCHAR(32766) NOT NULL )CHARACTER SET ”latin1” COLLATE LATIN1_DANISH_CI; Output Following is the output of the above code − Query OK, 0 rows affected (0.03 sec) Example Now, let us create another table test_table2 and provide 32766 and 32766 to both the columns (column1 and column2) − CREATE TABLE test_table2 ( column1 VARCHAR(32766) NOT NULL, –error column2 VARCHAR(32766) NOT NULL )CHARACTER SET ”latin1” COLLATE LATIN1_DANISH_CI; Output As we can see in the output below, an error is generated because the row size (32766 +2 +32766 +2 = 65536) exceeds the maximum limit (65,535) − ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs Example Here, we are creating another table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID int PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(3) ); Following is the output obtained − Query OK, 0 rows affected (0.03 sec) Now, we are inserting a string into NAME column where the length is greater than the length of VARCHAR column − INSERT INTO CUSTOMERS (NAME) VALUES (”Rahul”); Output As a result, MySQL will generate an error given below − ERROR 1406 (22001): Data too long for column ”NAME” at row 1 Example MySQL does not count the trailing spaces when inserting a value. Instead it truncates the trailing spaces. Let us insert a value into the NAME column that has trailing spaces − INSERT INTO CUSTOMERS (NAME) VALUES (”ABC ”); Output As we can see in the output below, MySQL issued a warning − Query OK, 1 row affected, 1 warning (0.02 sec) Example In the following query, we are trying to check the length of the values in NAME column − SELECT ID, NAME, length(NAME) FROM CUSTOMERS; The result produced is as follows − ID NAME length(NAME) 1 ABC 3 Now, let us execute the below query to display the warnings that issued on the above insertion operation − SHOW warnings; The result produced is − Level Code Message Note 1265 Data truncated for column ”NAME” at row 1 Varchar Datatypes Using a Client Program In addition to performing datatypes using mysql query, we can also create column of the Varchar datatypes using the client program. Syntax PHP NodeJS Java Python To create a column of Varchar datatypes through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows − $sql =”CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) “; $mysqli->query($sql); To create a column of Varchar datatypes through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows − sql = “CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))”; con.query(sql); To create a column of Varchar datatypes through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows − String sql = “CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))”; statement.execute(sql); To create a column of Varchar datatypes through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows − sql = ”CREATE TABLE test_table (column1 VARCHAR(32765) NOT NULL, column2 VARCHAR(32766) NOT NULL)CHARACTER SET ”latin1” COLLATE LATIN1_DANISH_CI” cursorObj.execute(sql) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $dbname = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } // printf(”Connected successfully.”); //create a customer table and use varchar data type with differenet size $sql = “CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) “; if ($mysqli->query($sql)) { echo “Table created successfully with varchar data!n”; } if ($mysqli->errno) { printf(“table could not create table: %s”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Table created successfully with varchar data!

MySQL – Useful Functions ”; Previous Next Built-in MySQL Functions Here is the list of all important MySQL functions. Each function has been explained along with suitable examples. MySQL DATE and Time Functions − Complete list of MySQL Date and Time related functions. MySQL Numeric Functions − Complete list of MySQL functions required to manipulate numbers in MySQL. MySQL String Functions − Complete list of MySQL functions required to manipulate strings in MySQL. MySQL Aggregate Functions − Complete list of MySQL aggregate functions. Print Page Previous Next Advertisements ”;

MySQL – UUID Table of content The MySQL UUID function UUID Format UUIDs in a Database Table Modifying UUIDs ”; Previous Next The MySQL UUID function The MySQL UUID() function is used to generate “Universal Unique Identifiers” (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number. UUID Format The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens. The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value. Generating a UUID Following is the basic example to generate a UUID using the UUID() function in MySQL − SELECT UUID(); Output It will display a universal unique identifier as shown below − UUID() 55f7685d-e99c-11ed-adfc-88a4c2bbd1f9 Generating Multiple UUIDs You can generate multiple UUIDs in a single query, and each UUID will be different as shown below − SELECT UUID() AS ID1, UUID() AS ID2; Output The output will show two different UUIDs, with differences generally in the first segment − ID1 ID2 78c3fb43-e99c-11ed-adfc-88a4c2bbd1f9 78c3fb4f-e99c-11ed-adfc-88a4c2bbd1f9 UUIDs in a Database Table You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data − Here, we are first creating a table with the name “ORDERS”, with an ORDER_ID column of type VARCHAR using the following query − CREATE TABLE ORDERS( ID int auto_increment primary key, NAME varchar(40), PRODUCT varchar(40), ORDER_ID varchar(100) ); Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column − INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES (“Varun”, “Headphones”, UUID()); INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES (“Priya”, “Mouse”, UUID()); INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES (“Nikhil”, “Monitor”, UUID()); INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES (“Sarah”, “Keyboard”, UUID()); INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES (“Vaidhya”, “Printer”, UUID()); Following is the ORDERS table obtained − ID NAME PRODUCT ORDER_ID 1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9 2 Priya Mouse a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9 3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9 4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9 5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9 Modifying UUIDs You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64(). Example Here, we are updating the UUID value for the record where ID = 1 using the following query − UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1; Output Following is the output of the above code − Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification To verify the modified UUID values, we can use the following SELECT query − SELECT * FROM ORDERS; As we can see in the output below, every time we execute the UUID() function, we get a different UUID value − ID NAME PRODUCT ORDER_ID 1 Varun Headphones 38f4d94a-e99d-11ed-adfc-88a4c2bbd1f9 2 Priya Mouse a45b03a3-e99d-11ed-adfc-88a4c2bbd1f9 3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9 4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9 5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9 Example Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below − UPDATE ORDERS SET ORDER_ID = REPLACE(UUID(), ”-”, ””) WHERE ID = 2; Output Output of the above code is as follows − Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification To verify the modified UUID value, we can use the following SELECT query − SELECT * FROM ORDERS; As we can see in the output below, the UUID of row = 2 is modified without disturbing the “unique” part of it − ID NAME PRODUCT ORDER_ID 1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9 2 Priya Mouse 069b0ca-7e99e11ed-adfc-88a4c2bbd1f9 3 Nikhil Monitor a45b49cc-e99d-11ed-adfc-88a4c2bbd1f9 4 Sarah Keyboard a45b8d3f-e99d-11ed-adfc-88a4c2bbd1f9 5 Vaidhya Printer a4b003d0-e99d-11ed-adfc-88a4c2bbd1f9 Example In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function − UPDATE ORDERS SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),”-”,””))) WHERE ID=4; Output The result produced is as follows − Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification Let us verify the modified UUID value using the following SELECT query − SELECT * FROM ORDERS; The output produced is as given below − ID NAME PRODUCT ORDER_ID 1 Varun Headphones a45a9632-e99d-11ed-adfc-88a4c2bbd1f9 2 Priya Mouse 069b0ca7-e99e11ed-adfc-88a4c2bbd1f9 3

MySQL – Primary Key Table of content Creating MySQL Primary Key Creating Primary Key on Existing Column Dropping MySQL Primary Key Creating Primary Key Using Client Program ”; Previous Next A PRIMARY KEY is a constraint applied on a field of a MySQL table. When this is applied, the values in that particular table column are uniquely identified. It is the most appropriate candidate key to be the main key of any table. A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key. You can either create a primary key while creating a new table or you can apply it on an already existing table in the database. But if it is being applied on an existing table, you must make sure that the table does not already contain a primary key and . Creating MySQL Primary Key To create a primary key on a new MySQL table, you must specify the column as the PRIMARY KEY while creating a new table using the CREATE TABLE statement. Following are some points to remember while creating a Primary Key on a table − The Primary Key column must only contain unique values. It can not hold NULL values. One table can have only one Primary Key. A Primary Key length cannot be more than 900 bytes. Syntax Following is the syntax to define a column of a table as a primary key − CREATE TABLE table_name( column_name NOT NULL PRIMARY KEY(column_name) ); Example In the following example, let us create a table with the name CUSTOMERS in a MySQL database using the CREATE TABLE query. In this query, we will add the PRIMARY KEY constraint on a column named ID. CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2), PRIMARY KEY(ID) ); Output The table structure displayed will contain a UNI index on the ADDRESS column as shown − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Verification To verify further that the PRIMARY KEY constraint is applied on the ID column, let us insert different types of values into the CUSTOMERS table using the following queries − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 23, ”Pune”, 2000.00), (1, ”John”, 25, ”Hyderabad”, 3000.00); Following error is displayed − ERROR 1062 (23000): Duplicate entry ”1” for key ”customers.PRIMARY” As we can see above, you cannot insert duplicate and null values into this primary key column. Creating Primary Key on Existing Column We can also add a primary key on an existing column of a table, if it was not created (for any reason) while creating a new table. However, adding a primary key on an existing table is only possible if the table does not already contain a primary key (as a MySQL table must not contain multiple primary keys), and the column it is being applied on must only contain unique values. You can add the primary key on an existing table using the ALTER TABLE… ADD CONSTRAINT statement. Syntax Following is the syntax to create a unique constraint on existing columns of a table − ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name); Example Using the ALTER TABLE statement, you can add a PRIMARY KEY on an existing column in the CUSTOMERS table created previously. In the following example, we are applying the PRIMARY KEY on the ID column as shown below − ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY (ADDRESS); Output The table structure displayed will contain a UNI index on the ADDRESS column as shown − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL But if the column, on which the PRIMARY KEY is added, contains duplicate or null values, it cannot be set as a primary key. Dropping MySQL Primary Key MySQL provides the ALTER TABLE… DROP statement to drop the primary key from a table. Syntax Following is the syntax to drop the PRIMARY KEY constraint using the ALTER TABLE… DROP statement − ALTER TABLE table_name DROP PRIMARY KEY; Example Let us consider the CUSTOMERS table with the primary key constraint present on a column named ID. You can drop this constraint from the column ID by executing the following statement ALTER TABLE CUSTOMERS DROP PRIMARY KEY; Output The table structure displayed will contain a UNI index on the ADDRESS column as shown − Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO

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 – EXPLAIN Table of content The MySQL EXPLAIN Statement EXPLAIN and ANALYZE The explain_type Option ”; Previous Next The MySQL EXPLAIN Statement The MySQL EXPLAIN statement is used to provide the execution plan of a query. This statement works similar to the DESCRIBE query; while the DESCRIBE query provides the structure plan of a table, the EXPLAIN statement describes how a query is being executed. You can use the EXPLAIN statement in situations where a query is taking too much time in order to be executed. It displays the execution plan of such slower queries, allowing you to apply indexes wherever necessary to speed up the execution process. Note that you cannot use too many indexes on a query either; as it might make the query even slower. This statement works with the SELECT, DELETE, INSERT, REPLACE and UPDATE statements. Syntax Following is the syntax of the EXPLAIN statement − EXPLAIN tbl_name [col_name | wild] Example Assume we have created a table named CUSTOMERS in MySQL database as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, ADDRESS CHAR (25), PRIMARY KEY (ID) ); You can use the EXPLAIN statement to view the execution plan of this table as shown below − EXPLAIN CUSTOMERS; The output will provide information about the table”s structure, including columns and their attributes as follows − Field Type Null Key Default Extra ID int NO PRI NULL NAME varchar(20) NO NULL ADDRESS char(25) YES NULL You can also use the EXPLAIN statement to obtain details about a specific column as shown below − EXPLAIN CUSTOMERS NAME; Output Following is the output obtained − Field Type Null Key Default Extra NAME varchar(20) NO NULL EXPLAIN is most commonly used with SELECT queries to analyze their execution plans. Consider the following query − EXPLAIN SELECT * FROM CUSTOMERS WHERE NAME LIKE ”k%”; The table obtained is as follows − id select_type table partitions type possible_keys 1 SIMPLE CUSTOMERS NULL ALL NULL Note that not all columns in the table have been displayed in the output above; there are additional columns present. EXPLAIN and ANALYZE If we use the EXPLAIN statement with ANALYZE, it gives additional information such as timing of the execution and iterator-based information like − Estimated execution cost. Estimated number of returned rows. Time to return first row. Time to return all rows (actual cost), in milliseconds. Number of rows returned by the iterator. Number of loops. Example Following is an example of the EXPLAIN statement with ANALYZE − EXPLAIN ANALYZE SELECT * FROM CUSTOMERS; It displays the output that includes more timing and cost-related details as shown below − EXPLAIN –> Table scan on CUSTOMERS (cost=0.35 rows=1) (actual time=0.070..0.070 rows=0 loops=1) Example First, let us insert values into the CUSTOMERS table created above using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”Ahmedabad” ), (2, ”Khilan”, ”Delhi” ), (3, ”kaushik”, ”Kota”), (4, ”Chaitali”, ”Mumbai” ), (5, ”Hardik”, ”Bhopal” ), (6, ”Komal”, ”MP” ), (7, ”Muffy”, ”Indore” ); 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, CUST_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Now, we are inserting some data into the ORDERS 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); Following query deletes records from the above created tables − SELECT * FROM CUSTOMERS INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID; We get the following output − ID NAME ADDRESS OID DATE CUST_ID AMOUNT 3 Kaushik Kota 102 2009-10-08 00:00:00 3 3000.00 3 Kaushik Kota 100 2009-10-08 00:00:00 3 1500.00 2 Khilan Delhi 101 2009-11-20 00:00:00 2 1560.00 4 Chaitali Mumbai 103 2008-05-20 00:00:00 4 2060.00 To obtain information about this query”s execution, you can use the EXPLAIN ANALYZE statement as follows− EXPLAIN ANALYZE SELECT * FROM CUSTOMERS INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.IDG; The result produced is as follows − *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=2.05 rows=4) (actual time=0.117..0.145 rows=4 loops=1) -> Table scan on ORDERS (cost=0.65 rows=4) (actual time=0.078..0.095 rows=4 loops=1) -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID) (cost=0.28 rows=1) (actual time=0.010..0.010 rows=1 loops=4) 1 row in set (0.00 sec) The explain_type Option You can also specify the format in which you want to retrieve the information using the explain_type option. It allows you to choose between TRADITIONAL, JSON, and TREE formats. These different formats provide the same information but in a more structured manner for your analysis. Example In here, we are retrieving

MySQL – Handling Duplicates Table of content Importance of Handling MySQL Duplicates Preventing Duplicate Entries Counting and Identifying Duplicates Eliminating Duplicates from a Query Result Removing Duplicates Using Table Replacement Handling Duplicates Using a Client Program ”; Previous Next Tables or result sets in a database usually contain duplicate records. While duplicates are generally allowed, there are situations where it is necessary to prevent them. In such cases, it becomes essential to identify and remove duplicate records from a database table. Importance of Handling MySQL Duplicates There are various reasons why handling duplicates in a database becomes necessary. One of the main reasons is that the existence of duplicates in an organizational database will lead to logical errors. In addition to it, we need to handle redundant data to prevent the following consequences − Duplicate data occupies storage space, reducing the efficiency of database usage and increasing storage costs. Dealing with duplicate records consumes additional resources, driving up the overall cost of maintaining the database. Duplicates in a database can lead to logical errors in data, affecting the integrity and reliability of the information stored. Preventing Duplicate Entries You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to prevent duplicate record entries into a table. Example The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name. CREATE TABLE CUSTOMERS ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) ); To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it is also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values − CREATE TABLE CUSTOMERS ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) ); Using INSERT IGNORE Query − The existence of a unique index in a table normally causes an error when attempting to insert a record that duplicates an existing record in the indexed column(s). To handle this situation without generating an error, you can use the “INSERT IGNORE” command. When a record is not a duplicate, MySQL inserts it as usual. However, if the record is duplicate, the “IGNORE” keyword instructs MySQL to discard it without producing an error. The provided example does not result in an error, and it also ensures that duplicate records are not inserted − INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES (”Jay”, ”Thomas”), (”Jay”, ”Thomas”); We get the following output − Query OK, 1 row affected, 1 warning (0.01 sec) Records: 2 Duplicates: 1 Warnings: 1 Using REPLACE Query − Instead of using the INSERT command, consider using the REPLACE command. When dealing with a new record, it is inserted just as with INSERT. However, if it is a duplicate, the new record replaces the old one. REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME) VALUES ( ”Ajay”, ”Kumar”), ( ”Ajay”, ”Kumar”); Following is the output of the above code − Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 Your choice between the INSERT IGNORE and REPLACE commands should depend on the specific duplicate-handling behaviour you wish to achieve. The INSERT IGNORE command retains the first set of duplicated records and discards the remaining. On the other hand, the REPLACE command keeps the last set of duplicates and removes any earlier instances. Using UNIQUE Constraint − Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table − CREATE TABLE CUSTOMERS ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name) ); Counting and Identifying Duplicates You can use the COUNT function and GROUP BY clause to count and identify duplicate records based on specific columns. Example Following is the query to count duplicate records with first_name and last_name in a table − SELECT COUNT(*) as repetitions, last_name, first_name FROM CUSTOMERS GROUP BY last_name, first_name HAVING repetitions > 1; This query will return a list of all the duplicate records in the CUSTOMERS table. In general, to identify sets of values that are duplicated, follow the steps given below. Determine which columns may contain duplicated values. Include those columns in the column selection list, along with COUNT(*). List the columns in the GROUP BY clause as well. Apply a HAVING clause to filter unique values by requiring the group counts to be greater than one. Eliminating Duplicates from a Query Result You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table. SELECT DISTINCT last_name, first_name FROM CUSTOMERS ORDER BY last_name; An alternative to the DISTINCT command is to add a GROUP BY clause that specifies the columns you are selecting. This approach eliminates duplicates and retrieves only the unique combinations of values from the specified columns. SELECT last_name, first_name FROM CUSTOMERS GROUP BY (last_name, first_name); Removing Duplicates Using Table Replacement If you have duplicate records in a table and you want to remove all the duplicate records from that table, then follow the procedure given below − CREATE TABLE tmp AS SELECT DISTINCT last_name, first_name, sex FROM CUSTOMERS; DROP TABLE CUSTOMERS; ALTER TABLE tmp RENAME TO CUSTOMERS; Handling Duplicates Using a Client Program We can also handle duplicate using the client program. Syntax PHP NodeJS Java Python To handle duplicates value through a PHP program, we need to execute the “INSERT IGNORE” statement using the mysqli function query() as follows − $sql = “INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ”Jay”, ”Thomas”)”; $mysqli->query($sql); To handle duplicates value through a JavaScript program, we need to execute the “INSERT IGNORE” statement using the query() function of mysql2 library as follows − sql = “INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ”Jay”, ”Thomas”)”; con.query(sql); To handle duplicates value through a Java program, we need to execute the “INSERT IGNORE” statement using the JDBC function execute()

MySQL – DROP TRIGGER Table of content Dropping Trigger in MySQL With IF EXISTS clause Dropping Trigger Using a Client Program ”; Previous Next Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked. Triggers are, in fact, written to be executed in response to any of the following events − A database manipulation (DML) statement (DELETE, INSERT, or UPDATE) A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). You can delete a trigger using the DROP TRIGGER Statement. Dropping Trigger in MySQL The DROP TRIGGER statement in MySQL will drop a trigger from a database, and all its information. Syntax Following is the syntax of the MySQL DELETE TRIGGER Statement. DROP TRIGGER [IF EXISTS] trigger_name Where, trigger_name is the name of the trigger you need to delete. Example Assume we have created a table with name student as shown below − CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT ); Following query creates a trigger named sample_trigger on this table. This trigger will set the score value 0 if you enter a value that is less than 0 as score. DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END // DELIMITER ; Now, let us use the following query to drop the trigger we created in the previous step − DROP TRIGGER sample_trigger; Verification To verify if the trigger has been dropped, let us display the trigger information using the following query − SHOW TRIGGERSG Since have deleted the trigger created, we get an empty set − Empty set (0.11 sec) With IF EXISTS clause If you try to drop a trigger that doesn”t exist an error will be generated as shown below − DROP TRIGGER demo; Following is the output − ERROR 1360 (HY000): Trigger does not exist If you use the IF EXISTS clause along with the DROP TRIGEGR statement as shown below, the specified trigger will be dropped and if a trigger with the given name, doesn”t exist the query will be ignored. DROP TRIGGER IF EXISTS demo; Dropping Trigger Using a Client Program In addition to create or show a trigger, we can also drop a trigger using a client program. Syntax PHP NodeJS Java Python To drop a trigger through a PHP program, we need to execute the DROP TRIGGER statement using the mysqli function query() as follows − $sql = “Drop TRIGGER testTrigger”; $mysqli->query($sql); To drop a trigger through a JavaScript program, we need to execute the DROP TRIGGER statement using the query() function of mysql2 library as follows − sql = “DROP TRIGGER testTrigger”; con.query(sql); To drop a trigger through a Java program, we need to execute the DROP TRIGGER statement using the JDBC function execute() as follows − String sql = “DROP TRIGGER sample_trigger”; statement.execute(sql); To drop a trigger through a python program, we need to execute the DROP TRIGGER statement using the execute() function of the MySQL Connector/Python as follows − drop_trigger_query = “DROP TRIGGER sample_trigger” cursorObj.execute(drop_trigger_query) Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”password”; $db = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if($mysqli->connect_errno ) { printf(“Connect failed: %s”, $mysqli->connect_error); exit(); } //printf(”Connected successfully.”); $sql = “DROP TRIGGER testTrigger”; if($mysqli->query($sql)){ printf(“Trigger dropped successfully…!”); } if($mysqli->error){ printf(“Failed..!” , $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − Trigger dropped successfully…! var mysql = require(”mysql2”); var con = mysql.createConnection({ host:”localhost”, user:”root”, password:”password” }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log(“Connected successfully…!”); //console.log(“————————–“); sql = “USE TUTORIALS”; con.query(sql); sql = “DROP TRIGGER testTrigger”; con.query(sql); console.log(“Drop trigger query executed successfully..!”); console.log(“Triggers: “); sql = “SHOW TRIGGERS”; con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); }); Output The output produced is as follows − Drop trigger query executed successfully..! Triggers: [] import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DropTrigger { 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…!”); String sql = “DROP TRIGGER sample_trigger”; st.execute(sql); System.out.print(“Triggerd dropped successfully…!”); }catch(Exception e) { e.printStackTrace(); } } } Output The output obtained is as shown below − Triggerd dropped successfully…! import mysql.connector # Establishing the connection connection = mysql.connector.connect( host=”localhost”, user=”root”, password=”password”, database=”tut” ) table_name = ”Student” trigger_name = ”sample_trigger” # Creating a cursor object cursorObj = connection.cursor() # drop trigger drop_trigger_query = “DROP TRIGGER sample_trigger” cursorObj.execute(drop_trigger_query) print(“Trigger is dropped successfully”) connection.commit() # close the cursor and connection cursorObj.close() connection.close() Output Following is the output of the above code − Trigger is dropped successfully Print Page Previous Next Advertisements ”;