Your cart is currently empty!
Author: alien
-
Khóa học miễn phí MySQL – SubQuery nhận dự án làm có lương
MySQL – SubQuery
Table of content
The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.
Subquery with the SELECT Statement
A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery.
Syntax
Following is the basic syntax of a subquery within a SELECT statement −
SELECT column1, column2, ... FROM table1 WHERE columnN operator (SELECT column_name FROM table2 WHERE condition);
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 above-created table 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 displayed is as shown below −
ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID”s match with the ID”s in the same table −
SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS);
Output
The output for the query above is produced as given below −
SALARY 2000.00 1500.00 2000.00 6500.00 8500.00 4500.00 10000.00 Subquery with the INSERT Statement
We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table.
Syntax
Following is the basic syntax of a subquery within an INSERT statement −
INSERT INTO target_table (column1, column2, ...) SELECT source_column1, source_column2, ... FROM source_table WHERE condition;
Example
Before performing the subqueries with INSERT statement, let us create a table named “CUSTOMERS_BKP” with a similar structure as CUSTOMERS table −
CREATE TABLE CUSTOMERS_BKP( 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 all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query −
INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS);
Output
The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table −
Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0
Verification
Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement −
SELECT * FROM CUSTOMERS_BKP;
As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP 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 Subquery with Comparison Operator
The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators.
Syntax
Following is the basic syntax of a subquery with comparison operators −
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE] .....)
Example
The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs.
SELECT * FROM CUSTOMERS_BKP WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP WHERE AGE > 23);
Output
The output for the query above is produced as given below −
ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 Subquery with IN or NOT-IN Operator
The MySQL subqueries with IN/NOT-IN operators are used to filter data based on whether values from one query match or do not match values from another query −
-
IN matches any value from the list
-
NOT-IN excludes any value from the list.
Example
The following query retrieves all the records from the CUSTOMERS table where the ADDRESS is not “Hyderabad” by comparing it to addresses in the CUSTOMERS_BKP table −
SELECT * FROM CUSTOMERS WHERE ADDRESS NOT IN ( SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");
Output
Following is the output of the above query −
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 7 Muffy 24 Indore 10000.00 Example
Now, the following query retrieves all the rows from the CUSTOMERS table where the ADDRESS is “Hyderabad” by using a subquery to fetch all addresses that match “Hyderabad” from the CUSTOMERS_BKP table −
SELECT * FROM CUSTOMERS WHERE ADDRESS IN ( SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");
Output
On executing the given query, the output is displayed as follows −
ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.00 Subquery Using a Client Program
We can also perform Subquery using the client program.
Syntax
To fetch the data using subqueries through a PHP program, we need to execute the “SELECT” statement using the mysqli function query() as follows −
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; $mysqli->query($sql);
To fetch the data using subqueries through a JavaScript program, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows −
sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; con.query(sql);
To fetch the data using subqueries through a Java program, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows −
String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; statement.executeQuery(sql);
To fetch the data using subqueries through a Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows −
sub_query = "SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS)" cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = ''localhost $dbuser = ''root $dbpass = ''password $db = ''TUTORIALS $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); $sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; printf("Table records: n"); if($result = $mysqli->query($sql)){ while($row = mysqli_fetch_array($result)){ printf("Id: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f", $row[''ID''], $row[''NAME''], $row[''AGE''], $row[''ADDRESS''], $row[''SALARY'']); printf("n"); } } if($mysqli->error){ printf("Error message: ", $mysqli->error); } $mysqli->close();Output
The output obtained is as shown below −
Table records: Id: 4, NAME: Chaitali, AGE: 25, ADDRESS: Mumbai, SALARY: 6500.000000 Id: 5, NAME: Hardik, AGE: 27, ADDRESS: Bhopal, SALARY: 8500.000000 Id: 6, NAME: Komal, AGE: 22, ADDRESS: Hyderabad, SALARY: 4500.000000 Id: 7, NAME: Muffy, AGE: 24, ADDRESS: Indore, SALARY: 10000.000000
NodeJS program var mysql = require(''mysql2''); var con = mysql.createConnection({ host:"localhost", user:"root", password:"password" }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; // console.log("Connected successfully...!"); // console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); //create table sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; con.query(sql, function(err, result){ console.log("Subquery executed successfully...!"); console.log("Table records: ") if (err) throw err; console.log(result); }); });Output
The output obtained is as shown below −
Subquery executed successfully...! Table records: [ { NAME: ''Chaitali'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: ''6500.00'' }, { NAME: ''Hardik'', AGE: 27, ADDRESS: ''Bhopal'', SALARY: ''8500.00'' }, { NAME: ''Komal'', AGE: 22, ADDRESS: ''Hyderabad'', SALARY: ''4500.00'' }, { NAME: ''Muffy'', AGE: 24, ADDRESS: ''Indore'', SALARY: ''10000.00'' } ]import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SubQuery { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); //create table String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)"; rs = st.executeQuery(sql); System.out.println("Table records: "); while(rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); String age = rs.getString("age"); String address = rs.getString("address"); String salary = rs.getString("salary"); System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary); } }catch(Exception e) { e.printStackTrace(); } } }Output
The output obtained is as shown below −
Table records: Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00 Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00 Id: 6, Name: Komal, Age: 22, Address: Hyderabad, Salary: 4500.00 Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) cursorObj = connection.cursor() # Subquery to fetch the salaries of all customers whose ID is present in the same table sub_query = f""" SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS); """ cursorObj.execute(sub_query) # Fetching all the rows that meet the criteria filtered_rows = cursorObj.fetchall() for row in filtered_rows: print(row) cursorObj.close() connection.close()Output
The output obtained is as shown below −
(Decimal(''2000.00''),) (Decimal(''1500.00''),) (Decimal(''2000.00''),) (Decimal(''6500.00''),) (Decimal(''8500.00''),) (Decimal(''4500.00''),) (Decimal(''10000.00''),)
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
-
Khóa học miễn phí MySQL – SQL Injection nhận dự án làm có lương
MySQL – SQL Injection
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.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc