Queries

Insert Query :

To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. You can insert data into the MySQL table by using the mysql> prompt or by using any script like PHP.

Syntax :

Here is a generic SQL syntax of INSERT INTO command to insert data into the MySQL table −

INSERT INTO table_name ( field1, field2,...fieldN )
   VALUES
   ( value1, value2,...valueN );

To insert string data types, it is required to keep all the values into double or single quotes. For example “value”.

Inserting Data from the Command Prompt

To insert data from the command prompt, we will use SQL INSERT INTO command to insert data into MySQL table tutorials_tbl.

Example :

The following example will create 3 records into tutorials_tbl table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed

mysql> INSERT INTO tutorials_tbl 
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

 

Select Query :

The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
  • You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
  • You can fetch one or more fields in a single SELECT command.
  • You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
  • You can specify any condition using the WHERE clause.
  • You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
  • You can limit the number of returns using the LIMIT attribute. 

     

    Fetching Data from a Command Prompt

This will use SQL SELECT command to fetch data from the MySQL table tutorials_tbl.

Example :

The following example will return all the records from the tutorials_tbl table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

Where Clause :

We have seen the SQL SELECT command to fetch data from a MySQL table. We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.

Syntax

The following code block has a generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
  • You can specify any condition using the WHERE clause.
  • You can specify more than one condition using the AND or the OR operators.
  • A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.

Here is the list of operators, which can be used with the WHERE clause.

Assume field A holds 10 and field B holds 20, then −

Operator Description Example
= Checks if the values of the two operands are equal or not, if yes, then the condition becomes true. (A = B) is not true.
!= Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true. (A != B) is true.
> Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true. (A > B) is not true.
< Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true. (A < B) is true.
>= Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true. (A >= B) is not true.
<= Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true. (A <= B) is true.

The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. Joins are discussed in another chapter.

It is a common practice to search for records using the Primary Key to make the search faster.

If the given condition does not match any record in the table, then the query would not return any row.

Fetching Data from the Command Prompt:

This will use the SQL SELECT command with the WHERE clause to fetch the selected data from the MySQL table – tutorials_tbl.

Example :

The following example will return all the records from the tutorials_tbl table for which the author name is Sanjay.

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 performing a LIKE comparison on a string, the comparison is not case sensitive. You can make your search case sensitive by using the BINARY keyword as follows −

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>




 

 

 

Update Query :

There may be a requirement where the existing data in a MySQL table needs to be modified. You can do so by using the SQL UPDATE command. This will modify any field value of any MySQL table.

Syntax :

The following code block has a generic SQL syntax of the UPDATE command to modify the data in the MySQL table −

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]
  • You can update one or more field altogether.
  • You can specify any condition using the WHERE clause.
  • You can update the values in a single table at a time.

The WHERE clause is very useful when you want to update the selected rows in a table.

Updating Data from the Command Prompt :

This will use the SQL UPDATE command with the WHERE clause to update the selected data in the MySQL table tutorials_tbl.

Example :

The following example will update the tutorial_title field for a record having the tutorial_id as 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> UPDATE tutorials_tbl 
   -> SET tutorial_title = 'Learning JAVA' 
   -> WHERE tutorial_id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>


 

Delect Query :

If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.

Syntax

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]
  • If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
  • You can specify any condition using the WHERE clause.
  • You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from the Command Prompt :

This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.

Example :

The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>