Category: mysql

MySQL – Full-Text Search Table of content MySQL Full-Text Search Key Points of MySQL Full-Text Search Types of Full-Text Searches Creating MySQL FULLTEXT Index Dropping MySQL FULLTEXT index Full-Text Search Using Client Program ”; Previous Next The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns. The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc. MySQL Full-Text Search To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement. Stop words are words that are commonly used (such as ”on”, ”the”, or, ”it”) in sentences and will be ignored during the searching process. The basic syntax to perform a full-text search on a MySQL is as follows − SELECT column_name(s) FROM table_name WHERE MATCH(col1, col2, …) AGAINST(expression [search_modifier]) Here, MATCH() function contains one or more columns separated by commas to be searched. AGAINST() function contains a search string to use for the full-text search. Key Points of MySQL Full-Text Search Following are some key points about the full-text search in MySQL − Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables. Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns). A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements. Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data. Types of Full-Text Searches There are three types of full-text searches. The same is described below: Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user”s intent. Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >, Query Expansion Searches: This expands the user”s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion. Creating MySQL FULLTEXT Index In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways: Using the FULLTEXT Keyword Using the ALTER TABLE Statement Using the CREATE INDEX Statement Using the FULLTEXT Keyword To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax − CREATE TABLE table_name( column1 data_type, column2 data_type, …, FULLTEXT (column1, column2, …) ); Example Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query − CREATE TABLE FILMS ( ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) ); Now, let us insert values into this table using the following query − INSERT INTO FILMS (NAME, DIRECTOR) VALUES (”RRR”, ”Directed by Rajamouli”), (”Bahubali”, ”Directed by Rajamouli”), (”Avatar”, ”Directed by James cameron”), (”Robot”, ”Directed by Shankar”); The table will be created as − ID NAME DIRECTOR 1 RRR Directed by Rajamouli 2 Bahubali Directed by Rajamouli 3 Avatar Directed by James Cameron 4 Robot Directed by Shankar Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below − SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (”Rajamouli”); Output As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string. ID NAME DIRECTOR 1 RRR Directed by Rajamouli 2 Bahubali Directed by Rajamouli Using the ALTER TABLE Statement In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax − ALTER TABLE table_name ADD FULLTEXT (column1, column2,…) Example In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table − ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR); Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ”Shankar”. SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (”Shankar”); Output Following is the output − ID NAME DIRECTOR 4 Robot Directed by Shankar Using the CREATE INDEX Statement In MySQL, we can also create a full-text index for

MySQL – UNION Operator Table of content MySQL UNION Operator UNION with WHERE clause UNION with ORDER BY clause UNION with Aliases UNION ALL Operator UNION Operator Using Client Program ”; Previous Next MySQL UNION Operator The UNION operator in MySQL combines the data (without duplicate records) from multiple tables. We can use UNION if we want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria − The same number of columns selected with the same datatype. These columns must also be in the same order. They need not have same number of rows. Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables. UNION is available as of MySQL 4.0. This section illustrates how to use it. Syntax The basic syntax of UNION operator in MySQL is as follows − SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; Example Let us first create the prospective customers table named PROSPECT using the following CREATE TABLE query − CREATE TABLE PROSPECT ( FNAME CHAR(20) NOT NULL, LNAME CHAR(20), ADDRESS VARCHAR(100) NOT NULL ); Now, we insert records into this table using INSERT statement below − INSERT INTO PROSPECT VALUES (”Peter”, ”Jones”, ”482 Rush St., Apt. 402”), (”Bernice”, ”Smith”, ”916 Maple Dr.”); The PROSPECT table is created as − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. ACTIVE Table − We then create an active customers table named ACTIVE using the following CREATE TABLE query − CREATE TABLE ACTIVE ( FNAME CHAR(20) NOT NULL, LNAME CHAR(20), ADDRESS VARCHAR(100) NOT NULL ); Using the following INSERT statement, insert records into the ACTIVE table − INSERT INTO ACTIVE VALUES (”Grace”, ”Peterson”, ”16055 Seminole Ave.”), (”Bernice”, ”Smith”, ”916 Maple Dr.”), (”Walter”, ”Brown”, ”8602 1st St.”); The ACTIVE table is created as − FNAME LNAME ADDRESS Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. Walter Brown 8602 1st St. Now, you want to create a single mailing list by merging names and addresses from all the tables. UNION provides a way to do this. The following query illustrates how to select names and addresses from the tables all at once − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Walter Brown 8602 1st St. As you can see, duplicates are avoided in the result-set. UNION with WHERE clause We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them. Syntax Following is the syntax for using the WHERE clause with UNION operator − SELECT column1, column2, column3 FROM table1 WHERE column1 = ”value1” UNION SELECT column1, column2, column3 FROM table2 WHERE column1 = ”value2”; Example Let us use the same tables from the previous example to retrieve combined records using UNION operator with WHERE clause − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT WHERE LNAME = ”Jones” UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE WHERE LNAME = ”Peterson”; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Grace Peterson 16055 Seminole Ave. UNION with ORDER BY clause When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set. Syntax Following is the basic syntax to use UNION operator with ORDER BY clause − SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2 ORDER BY column_name; Example Let us try to sort the table records in ascending order with respect to values in the ”lname” column of result set, using the following query − SELECT FNAME, LNAME, ADDRESS FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE ORDER BY LNAME; Output Following output is obtained − FNAME LNAME ADDRESS Walter Brown 8602 1st St. Peter Jones 482 Rush St., Apt. 402 Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. UNION with Aliases We can use aliases in a MySQL statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names. When using UNION with aliases, it”s important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set. Syntax Following is the syntax for using Union with Aliases − SELECT column1 AS alias1, column2 AS alias2 FROM table1 UNION SELECT column3 AS alias1, column4 AS alias2 FROM table2; Example In this following example, we are trying to combine two tables using aliases to represent the fields in result-set obtained − SELECT FNAME AS Firstname, LNAME AS Lastname, ADDRESS AS Address FROM PROSPECT UNION SELECT FNAME, LNAME, ADDRESS FROM ACTIVE; Output Following output is obtained − Firstname Lastname Address Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Walter Brown 8602 1st St. UNION ALL Operator If you want to select all records, including duplicates, follow the first UNION keyword with ALL − SELECT fname, lname, ADDRESS FROM prospect UNION ALL SELECT fname, lname, ADDRESS FROM active; Output Following output is obtained − FNAME LNAME ADDRESS Peter Jones 482 Rush St., Apt. 402 Bernice Smith 916 Maple Dr. Grace Peterson 16055 Seminole Ave. Bernice Smith 916 Maple Dr. Walter Brown 8602 1st St. UNION

MySQL – Using Joins Table of content Types of Joins Joins Using a Client Program ”; Previous Next A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause. For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. Types of Joins There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows − Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join. Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial. Example In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) ); Let us then insert the following records in the CUSTOMERS table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”Hyderabad”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); 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 ORDERS Table − We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query − CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into 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); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Joining Tables − Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS. SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID; Output The table is displayed as follows − ID NAME DATE AMOUNT 3 Kaushik 2009-10-08 00:00:00 3000.00 3 Kaushik 2009-10-08 00:00:00 1500.00 2 Khilan 2009-11-20 00:00:00 1560.00 4 Chaitali 2008-05-20 00:00:00 2060.00 Joins Using a Client Program In addition to Join two or more than two tables using the MySQL query we can also perform the Join operation using a client program. Syntax PHP NodeJS Java Python To join two or more than two MySQL tables through a PHP program, we need to perform the JOIN operation using the mysqli function query() as follows − $sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; $mysqli->query($sql); To join two or more than two MySQL tables through a JavaScript program, we need to perform the JOIN operation using the query() function of mysql2 library as follows − sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; con.query(sql); To join two or more than two MySQL tables through a Java program, we need to perform the JOIN operation using the JDBC function executeQuery() as follows − String sql = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”; st.executeQuery(sql); To join two or more than two MySQL tables through a Python program, we need to perform the JOIN operation using the execute() function of the MySQL Connector/Python as follows − using_join_query = ”SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID”

MySQL – On Delete Cascade Table of content The MySQL ON DELETE CASCADE Constraint ”; Previous Next The MySQL ON DELETE CASCADE Constraint The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship. If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables. Example Creating the Parent Table − First, let us create the parent table with the name PERSONS using the following query − CREATE TABLE PERSONS( P_ID int primary key, P_NAME varchar(40), P_AGE int ); Now, let us insert some values into the above created table using the INSERT statement as shown below − INSERT INTO PERSONS VALUES (1, “Priya”, 29), (2, “Sarah”, 20), (3, “Varun”, 26), (4, “Dev”, 25), (5, “Ram”, 31), (6, “Aarohi”, 34); The PERSONS table obtained is as shown below − P_ID P_NAME P_AGE 1 Priya 29 2 Sarah 20 3 Varun 26 4 Dev 25 5 Ram 31 6 Aarohi 34 Creating the Child Table − Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table − CREATE TABLE Films_watched ( P_ID INT, F_NO INT, F_NAME varchar(40), PRIMARY KEY(P_ID,F_NO), FOREIGN KEY(P_ID) REFERENCES PERSONS(P_ID) ON DELETE CASCADE ); Now, we are inserting rows into the Films_watched table − INSERT INTO Films_watched VALUES (1, 130, “RRR”), (2, 131, “Bahubali”), (3, 132, “Pushpa”), (3, 133, “KGF”), (3, 134, “Salaar”), (6, 135, “Karthikeya”); The Films_watched table produced is as follows − P_ID F_NO F_NAME 1 130 RRR 2 131 Bahubali 3 132 Pushpa 3 133 KGF 3 134 Salaar 6 135 Karthikeya Deleting a Record from the Parent Table − As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table − DELETE FROM PERSONS WHERE P_ID = 3; Following is the output obtained − Query OK, 1 row affected (0.01 sec) After this deletion, let us check the data in both the Persons and Films_watched tables. Querying Data from the Persons Table − To see the remaining records in the Persons table, use the following SELECT query − SELECT * FROM PERSONS; We can see in the table below, the row with P_ID = 3 is deleted − P_ID P_NAME P_AGE 1 Priya 29 2 Sarah 20 4 Dev 25 5 Ram 31 6 Aarohi 34 Querying Data from the Films_watched Table − Lastly, you can check the data in the Films_watched table − SELECT * FROM Films_watched; Output We can see in the output below that all related records with P_ID = 3 have been automatically deleted − P_ID F_NO F_NAME 1 130 RRR 2 131 Bahubali 6 135 Karthikeya Print Page Previous Next Advertisements ”;

MySQL – Numeric Functions ”; Previous Next MySQL – Numeric Functions MySQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions that are available in the MySQL. Sr.No. Name & Description 1 ABS() Returns the absolute value of numeric expression. 2 ACOS() Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1. 3 ASIN() Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1 4 ATAN() Returns the arctangent of numeric expression. 5 ATAN2() Returns the arctangent of the two variables passed to it. 6 CEIL() Returns the smallest integer value that is not less than passed numeric expression 7 CEILING() Returns the smallest integer value that is not less than passed numeric expression 8 CONV() Converts numeric expression from one base to another. 9 COS() Returns the cosine of passed numeric expression. The numeric expression should be expressed in radians. 10 COT() Returns the cotangent of passed numeric expression. 11 CRC32() Returns the Cyclic Redundancy Check (CRC) for the given value. 12 DEGREES() Returns numeric expression converted from radians to degrees. 13 EXP() Returns the base of the natural logarithm (e) raised to the power of passed numeric expression. 14 FLOOR() Returns the largest integer value that is not greater than passed numeric expression. 15 FORMAT() Returns a numeric expression rounded to a number of decimal places. 16 HEX() returns the hexadecimal representation of the given value. 17 LN() Returns the natural logarithm of the passed numeric expression. 18 LOG() Returns the natural logarithm of the passed numeric expression. 19 LOG10() Returns the base-10 logarithm of the passed numeric expression. 20 LOG2() Returns the base-2 logarithm of the passed numeric expression. 21 MOD() Returns the remainder of one expression by diving by another expression. 22 PI() Returns the value of pi 23 POW() Returns the value of one expression raised to the power of another expression 24 POWER() Returns the value of one expression raised to the power of another expression 25 RADIANS() Returns the value of passed expression converted from degrees to radians. 26 RAND() Returns a random floating-point value with in the range 0 to 1.0. 27 ROUND() Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points 28 SIGN() Returns the sign of the given number. 29 SIN() Returns the sine of numeric expression given in radians. 30 SQRT() Returns the non-negative square root of numeric expression. 31 TAN() Returns the tangent of numeric expression expressed in radians. 32 TRUNCATE() Returns numeric exp1 truncated to exp2 decimal places. If exp2 is 0, then the result will have no decimal point. Print Page Previous Next Advertisements ”;

MySQL – RESIGNAL Statement Table of content The MySQL RESIGNAL Statement Handling Warnings with RESIGNAL Resignal Statement Using Client Program ”; Previous Next When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure. To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE … HANDLER statement. The MySQL RESIGNAL Statement The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure. RESIGNAL is specifically used within error handlers and must always include attributes. These attributes specify the SQL state, error code, and error message to be associated with the raised error. Customizing Error Messages The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution. Syntax Following is the syntax of the MySQL RESIGNAL Statement − RESIGNAL condition_value [SET signal_information_item] Where, condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”. signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME. Example In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement − DELIMITER // CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50)) BEGIN DECLARE wrong_choice CONDITION FOR SQLSTATE ”45000”; DECLARE EXIT HANDLER FOR wrong_choice RESIGNAL SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; IF degree=”BBA” THEN SET full_form = ”Bachelor of Business Administration”; ELSEIF degree=”BCA” THEN SET full_form = ”Bachelor of Computer Applications”; ELSEIF degree=”MD” THEN SET full_form = ”Doctor of Medicine”; ELSEIF degree=”ITI” THEN SET full_form = ”Industrial Training Institute”; ELSE SIGNAL wrong_choice; END IF; END // DELIMITER ; You can call the above procedure to retrieve the result as shown below − CALL example(”MD”, @fullform); You can retrieve the value of the variable using the following SELECT statement − SELECT @fullform; Following is the output obtained − @fullform Doctor of Medicine If you pass an invalid value to the procedure, it will generate an error message as follows − CALL example (”IIT”, @fullform); The output obtained is as follows − ERROR 1001 (45000): Given degree is not valid Handling Warnings with RESIGNAL Let us see another example where we do not pass optional attributes to the RESIGNAL statement − DELIMITER // CREATE PROCEDURE testexample (num INT) BEGIN DECLARE testCondition1 CONDITION FOR SQLSTATE ”01000”; DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL; IF num < 0 THEN SIGNAL testCondition1; END IF; END // DELIMITER ; You can call the above procedure by passing two values. But, any SQLSTATE value that starts with ”01” refers to a warning, so the query is executed with a warning as shown below − CALL testexample(-15); The output obtained is as follows − Query OK, 0 rows affected, 1 warning (0.00 sec) Resignal Statement Using Client Program We can also perform resignal Using Client Program. Syntax PHP NodeJS Java Python To perform the resignal statement through a PHP program, we need to execute the “Stored Procedure” using the mysqli function query() as follows − $sql = “CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END”; $mysqli->query($sql); To perform the resignal statement through a JavaScript program, we need to execute the “Stored Procedure” using the query() function of mysql2 library as follows − var createProcedureSql = ` CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” — Raise a warning SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” — Raise an error SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END`; con.query(createProcedureSql); To perform the resignal statement through a Java program, we need to execute the “Stored Procedure” using the JDBC function execute() as follows − String sql = “CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50)) BEGIN IF degree=”B-Tech” THEN SET full_form = ”Bachelor of Technology”; ELSEIF degree=”M-Tech” THEN SET full_form = ”Master of Technology”; ELSEIF degree=”BSC” THEN SET full_form = ”Bachelor of Science”; ELSEIF degree=”MSC” THEN SET full_form = ”Master of Science”; ELSE RESIGNAL SQLSTATE ”01000” SET MESSAGE_TEXT = ”Choose from the existing values”, MYSQL_ERRNO = 12121; RESIGNAL SQLSTATE ”45000” SET MESSAGE_TEXT = ”Given degree is not valid”, MYSQL_ERRNO = 1001; END IF; END”; statement.execute(sql); To perform the resignal statement through a Python program, we need to execute the

MySQL – SQL Injection Table of content How SQL Injection Works Preventing SQL Injection ”; Previous Next The SQL Injection in MySQL is a harmful approach where an attacker inserts or “injects” harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database. How SQL Injection Works Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below − SELECT * FROM users WHERE username = ”user” AND password = ”password”; In a secure application, the ”user” and ”password” would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code. For example, they might enter the following as the username − ” OR ”1” = ”1 Now, the SQL query becomes − SELECT * FROM users WHERE username = ”” OR ”1” = ”1” AND password = ”password”; Because ”1” always equals ”1”, this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password. Preventing SQL Injection To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this − if (get_magic_quotes_gpc()) { $name = stripslashes($name); } // escape input characters $name = mysql_real_escape_string($name); // Perform the MySQL query with the escaped ”name” mysqli_query(“SELECT * FROM CUSTOMERS WHERE name=”{$name}””); The LIKE Quandary Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include ”%” and ”_” characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining “mysql_real_escape_string()” function with “addcslashes()” function, which allows you to specify a character range to escape. Following is an example of how you can do it − // Escape and convert ”%” and ”_” in the user-provided string $sub = addcslashes(mysql_real_escape_string(“%str”), “%_”); // $sub will be equal to %str_ // Use the escaped string in the LIKE query mysqli_query(“SELECT * FROM messages WHERE subject LIKE ”{$sub}%””); In this way, you ensure that the ”%” and ”_” characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations. Print Page Previous Next Advertisements ”;

MySQL – AND Operator Table of content MySQL AND Operator AND Operator with WHERE Multiple AND Operators AND with UPDATE statement AND with DELETE Statement AND Operator Using a Client Program ”; Previous Next MySQL AND Operator In MySQL, there isn”t a built-in Boolean type. Rather, the Boolean values are represented using numeric data types, where zero is considered false and any non-zero value is considered true. The MySQL AND operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL: A AND B Here, A and B are operands. The AND operator will return true (1) only if both A and B are non-zero and not Null. If either A or B is false, the AND operator will return false (0). If either A or B is NULL, the AND operator will return NULL. The following table below demonstrates the possible outcomes of using the AND operator to combine true, false, and null values: 1 0 NULL 1 1 0 NULL 0 0 0 0 NULL NULL 0 NULL Example The logical AND operator returns 1 if both A and B are non-zero and NOT NULL − SELECT 1 AND 1; Output The output for the program above is produced as given below − 1 AND 1 1 Example The logical AND operator returns 0 if either A or B is zero, or if both A and B are zero. SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL; Output When we execute the above query, the output is obtained as follows − 1 AND 0 0 AND 1 0 AND 0 0 AND NULL 0 0 0 0 Example The logical AND operator returns NULL if at least one operand is non-zero or both operands are NULL − SELECT 1 AND NULL, NULL AND NULL; Output On executing the given query, the output is displayed as follows − 1 AND NULL NULL AND NULL NULL NULL AND Operator with WHERE The MySQL AND operator can be used with the WHERE clause to retrieve only the rows that meet all the specified conditions. When the AND operator is used, both conditions must be true for a row to be included in the result set. Else, it returns an empty set. Syntax Following is the syntax of the AND operator with WHERE clause in MySQL − SELECT column1, column2, …, columnN FROM table_name [WHERE condition1 AND condition2 AND condition3 …;] Example Firstly, let us create a MySQL table named CUSTOMERS using the below query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query inserts 7 rows into the above-created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to fetch all the records from the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us select all the columns from the CUSTOMERS table where the ADDRESS is ”Hyderabad” and AGE is 22. SELECT * FROM CUSTOMERS WHERE ADDRESS = “Hyderabad” AND AGE = 22; Output On executing the given query, the output is displayed as follows − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.00 Example The logical AND operator returns the records only if all the conditions separated by AND are true. In the following query, we are providing a false value to one of the AND operands. SELECT * FROM CUSTOMERS WHERE ADDRESS = “Kerala” AND AGE = 27; Output As the ADDRESS column in the CUSTOMERS table doesn”t contain the value ”Kerala”, it returns an empty set as an output. Empty set (0.00 sec) Multiple AND Operators In MySQL, we can use multiple AND operators in a query to combine multiple conditions or expressions together. Conditions combined with these multiple ”AND” operators are evaluated from left to right. If any of the conditions evaluate to false, the entire condition will be false and the record will not be included in the result set. Example In the following query, we are selecting all records from the CUSTOMERS table where the NAME starts

MySQL – NOT REGEXP Operator Table of content MySQL NOT REGEXP Operator NOT REGEXP Operator Using a Client Program ”; Previous Next MySQL NOT REGEXP Operator Technically, 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 character, multiple characters or words, etc. In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching. NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter. Syntax Following is the basic syntax of the MySQL NOT REGEXP operator − expression NOT REGEXP pattern Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator. Examples Let us start by 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) ); The below INSERT statement inserts 7 records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Execute the following query to display all the records present in CUSTOMERS table − Select * from CUSTOMERS; Following is the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Now, let us show the usage of NOT REGEXPM operator using several queries on this table. Query to find all the names not starting with ”ra” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”^ra”; Following is the output − ID NAME AGE ADDRESS SALARY 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 Query to retrieve all the records whose names not ending with ”ik” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”ik$”; Following is the output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Following is the query to find all the names that do not contain ”an” − SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ”an”; Following is the output − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.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 The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL. In the below query, the string value is NULL. Thus it gives output as NULL. SELECT NULL NOT RLIKE ”value”; Following is the output − NULL NOT RLIKE ”value” NULL Here, the specified pattern is NULL. So, the output will be retrieved as NULL. SELECT ”Tutorialspoint” NOT REGEXP NULL; Following is the output − NULL NOT RLIKE ”value” NULL NOT REGEXP Operator Using a Client Program Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result. Syntax Following are the syntaxes of this operation in various programming languages − PHP

MySQL – Export Table into CSV File Table of content Export MySQL Table into CSV File Storage Location of Exported .csv File Exporting MySQL Data in CSV Format Exporting Table Data Along with Column Headings Exporting Table Data Without Specifying Column Names Client Program ”; Previous Next MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for “Comma Separated Values” file. This allows users to extract data from a table in a structured format that can be easily manipulated and analysed using other tools such as Microsoft Excel, Google documents, open office etc. Export MySQL Table into CSV File To export the MySQL table data into a CSV file, we can use the MySQL “SELECT INTO … OUTFILE” statement. Before exporting any table data into CSV files in the database server, we must ensure the following things − The MySQL server”s process must have the read/write privileges to the specified target folder, where CSV file will be created. The specified CSV file should be already present in the system (No duplicate file). The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows. Syntax Following is the syntax of SELECT INTO … OUTFILE statement − SELECT column_name1, column_name2,… INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv” FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”rn”; Where, INTO OUTFILE is the path and name of the CSV file that we want to export the table data to. FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file. LINES TERMINATED is the line terminator character for exported CSV file. Storage Location of Exported .csv File In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the “secure_file_priv” variable. To find out the default path for exported files, you can use the following SQL query − SHOW VARIABLES LIKE “secure_file_priv”; We get the following output − Variable_name Value secure_file_priv C:ProgramDataMySQLMySQL Server 8.0Uploads Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named “CUSTOMERS” using the following SQL query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we are inserting data into the above created table as shown below − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, NULL, 2000.00 ), (4, ”Chaitali”, NULL, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, NULL, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The CUSTOMERS table obtained is as shown below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 NULL 2000.00 4 Chaitali NULL Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 NULL 4500.00 7 Muffy 24 Indore 10000.00 Exporting MySQL Data in CSV Format You can export MySQL data in CSV file using the SELECT INTO … OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named “CUSTOMERS_BACKUP” using the following query − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS ENCLOSED BY ””” TERMINATED BY ”;” ESCAPED BY ””” LINES TERMINATED BY ”rn”; After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query − Query OK, 7 rows affected (0.01 sec) Following is the image of “CUSTOMERS_BACKUP.csv” file when we opened it − Handling File Already Exists Error − If you attempt to export data into a file that already exists, MySQL will generate an error − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS ENCLOSED BY ””” TERMINATED BY ”;” ESCAPED BY ””” LINES TERMINATED BY ”rn”; Following is the error obtained − ERROR 1086 (HY000): File ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” already exists To avoid this error, you can either choose a different filename or delete the existing file before executing the export query. Removing Enclosing Quotes for Numeric Records − By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below − SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ”C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv” FIELDS TERMINATED BY ”,” OPTIONALLY ENCLOSED BY ””” LINES TERMINATED BY ”rn”; Following is the output obtained after executing the above query − Query OK, 7 rows affected (0.00 sec) As we can see the image of CSV file below, the double quotes (“”) are removed