MySQL database where query in,like subquery

We have seen the SQL SELECT command to fetch data from the MySQL table. We can use a conditional clause in the WHERE clause to filter out the results. Using the WHERE clause, we can specify a selected criterion to select the desired record from the table.

Grammar:
The following is a generic SQL WHERE clause to get the syntax for the data from the Select command of the MySQL table:

The code is as follows	Copy Code
SELECT field1, Field2,... fieldn table_name1, table_name2 ... [WHERE condition1 [and [OR]] condition2 .....

You can use one or more comma-delimited tables, including various use WHERE clause conditions. But the select command for the WHERE clause is an optional part.

You can specify any condition by using the WHERE clause.

You can specify more than one condition to use the and OR operator.

An SQL command that can use a scatter delete or update in a WHERE clause to specify conditions.

Just like in a programming language, if a condition's where clause. This clause is used to compare the field value of the table in MySQL with the given value. If the external given value is equal to the available field value in the MySQL table, then return the row.

Here is the operator of the manifest that can use the WHERE clause.

Suppose field A has a value of 10 and field B has a value of 20:

Operation Description Example
= If the value of two operands equals or checks, if so, the condition is true. (A = B) is not true.
!= if the value of two operands equals or checks, if the value is not equal, the condition is true. (A!= B) is true.
> Check the value, if the left operand is greater than the value of the right-hand operand, if so, the condition is true. (A > B) is not true.
< check if the value of the left-hand operand is less than the value of the right-hand operand, if so, the condition is true. (A < B) is true.
>= Check if the value of the left operand is greater than or equal to the value of the right-hand operand, if so, the condition is true. (A >= B) is not true.
<= Check if the value of the left operand is less than or equal to the value of the right-hand operand, if so, the condition is true. (A <= B) is true.

The WHERE clause is very useful when you want to get a row in the selection table, especially if you are using a MySQL join. Join is discussed in another chapter.

It is a common practice to use primary keys to find records so that the search is quick.

If a given condition does not conform to a record in any table, the query does not return any rows.

To get data from a command prompt:
This uses the WHERE clause of the SQL SELECT command to get the selected data MySQL table tutorials_tbl

Instance:
The following example returns the Tutorials_tbl table in which the author's name is Sanjay all records:

The code is as follows

Copy Code

root@host# mysql-u root-p password; Enter password:******* mysql> use tutorials; Database changed Mysql> SELECT * from tutorials_tbl WHERE tutorial_author= ' Sanjay '; +-------------+----------------+-----------------+-----------------+ | tutorial_id | Tutorial_title | Tutorial_author | Submission_date | +-------------+----------------+-----------------+-----------------+ | 3 | JAVA Tutorial | Sanjay | 2007-05-21 | +-------------+----------------+-----------------+-----------------+ 1 rows in Set (0.01 sec)

Mysql>
Unless the comparison to perform a like comparison string is case-insensitive. You can search for case-sensitive, using the binary keyword as follows.

The code is as follows	Copy Code
root@host# mysql-u root-p password; Enter password:******* mysql> use tutorials; Database changed Mysql> SELECT * from TUTORIALS_TBL WHERE BINARY tutorial_author= ' Sanjay '; Empty Set (0.02 sec) Mysql>

MySQL like clause

Grammar:
Here is the general SQL SELECT command syntax and LIKE clause to get data from the MySQL table:

SELECT field1, Field2,... fieldn table_name1, table_name2 ...
WHERE field1 like Condition1 [and [OR]] filed2 = ' somevalue '
you can specify any condition by using the WHERE clause.

You can use the LIKE clause with the WHERE clause.

Where you can use the equals sign in the LIKE clause.

When like is used with the% symbol, then it will be similar to a meta character search.

You can specify more than one condition to use the and or the OR operator

A Where ... You can also specify a condition by using the SQL command for the scatter delete or update that you can use in the LIKE clause.

Use the LIKE clause at a command prompt:
This will use the SQL SELECT command where ... The LIKE clause gets the data selected from the MySQL table tutorials_tbl

Example:
The following example returns the Tutorials_tbl table author's name to all records in Jay's end:

The code is as follows

Copy Code

root@host# Mysql-u root-p password; Enter password:******* mysql> use tutorials; Database changed mysql> SELECT * from Tutorials_tbl    -> WHERE tutorial_author like '%jay ' ; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | Tutorial_author | Submission_date | +-------------+----------------+-----------------+-----------------+ |            3 | JAVA tutorial  | sanjay          | 2007-05-21      | +-------------+----------------+-----------------+-----------------+ 1 rows in Set (0.01 sec) MySQL

Use the LIKE clause in a PHP script:
You can use a similar syntax where ... Like clause to PHP function mysql_query (). After this function is used to execute the SQL command, another PHP function mysql_fetch_array () can be used to get all the data, if where ... The Select command for the LIKE clause is used with the.

However, if the where ... The LIKE clause is using the DELETE or UPDATE command, and no further PHP function calls are required.

Example:
The following example returns the Tutorials_tbl table author's name to all records in Jay's end:

The code is as follows

Copy Code

<?php $dbhost = ' localhost:3036 '; $dbuser = ' root '; $dbpass = ' Rootpassword '; $conn = mysql_connect ($dbhost, $dbuser, $dbpass); if (! $conn) { Die (' Could not connect: '. Mysql_error ()); } $sql = ' SELECT tutorial_id, Tutorial_title, Tutorial_author, Submission_date From Tutorials_tbl WHERE tutorial_author like "%jay%"; // mysql_select_db (' tutorials '); $retval = mysql_query ($sql, $conn); if (! $retval) { Die (' could not get data: '. mysql_error ()); } while ($row = Mysql_fetch_array ($retval, MYSQL_ASSOC)) { Echo ' Tutorial ID: {$row [' tutorial_id ']} <br>. "Title: {$row [' Tutorial_title ']} <br>". "Author: {$row [' Tutorial_author ']} <br>". ' Submission Date: {$row [' submission_date ']} <br>. "--------------------------------<br>"; } echo "fetched data Successfullyn"; Mysql_close ($conn); ?>

The Where in usage in MySQL is detailed.

Here are two kinds of situations to introduce

1, in the following is a recordset, such as:

The code is as follows	Copy Code
SELECT * FROM table where uname in (select uname from user);

2, in the following is a string, such as:

The code is as follows	Copy Code
SELECT * FROM table where uname in (' AAA ', BBB ', ' CCC ', ' ddd ', ' eee ', ffff ');

Note: This must be marked with a single quotation mark on the string;

3, in the following is an array, use this method, please refer to:

The code is as follows	Copy Code
$pieces is an array containing data for ($i =0; $i <count ($pieces); $i + +) { $uname = $uname. "'". $pieces [$i]. "', '; } $the _uname = "uname in (". $uname. "')"; SELECT * FROM table where ". $the _uname.";

Note: The principle of this method is actually very simple, the second is to the array programming above the "2nd case" form