I have explained basics In my previous blog of MySQL. This is the next part of the tutorial.

WHERE 

You have learned numerous ways to retrieve particular columns from your tables, but not specific rows. This is when the WHERE clause comes in to play. From the basic SELECT syntax, you see that WHERE is used to specify a particular condition:

SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true]

select * from grocery_inventory where curr_qty = 500; 

Using Operators in WHERE Clauses

You can use many types of operators, with comparison operators and logical operators being the most popular types. 

Operator Meaning

= Equal to

!= Not equal to

<= Less than or equal to

< Less than

>= Greater than or equal to

> Greater than

BETWEEN 

There's also a handy operator called BETWEEN, which is useful with integer or data comparisons because it searches for results between a minimum and maximum value. For example:

select * from grocery_inventory where item_price between 1.50 and 3.00; 

String Comparison Using LIKE

You were introduced to matching strings within a WHERE clause by using = or !=, but there's another useful operator for the WHERE clause comes in to play. From string comparisons: LIKE. 

This operator uses two characters as wildcards in pattern matching.

• % — Matches multiple characters
• _ — Matches exactly one character

String Comparison Using LIKE

If you want to find records in the grocery_inventory table where the first name of the item starts with the letter "A", use

select * from grocery_inventory where item_name like 'A%'; 

Selecting from Multiple Tables 

You're not limited to selecting only one table at a time. That would certainly make application programming a long and tedious task! When you select from more than one table in one SELECT statement, you are said to be joining the tables together.

Suppose you have two tables, fruit and color. You can select all rows from each of the two tables, using two separate SELECT statements:

select * from fruit, color; 

When you select from multiple tables, you must build proper WHERE clauses to ensure you really get what you want. In the case of the fruit and color tables, what you really want is to see the fruitname and colorname records from these two tables where the IDs of each match up. 

So, the query for selecting fruitname and colorname from both tables where the IDs match would be

mysql> select fruitname, colorname from fruit, color where fruit.id = color.id; 

JOIN (Inner) 

Several types of JOINs can be used in MySQL, all of which refer to the order in which the tables are put together and the results are displayed. The type of JOIN used with the fruit and color tables is called an INNER JOIN, although it wasn't written explicitly as such. To rewrite the SQL statement using the proper INNER JOIN syntax, you would use

mysql> select fruitname, colorname from fruit inner join color on fruit.id = color.id; 

JOIN 

The ON clause replaced the WHERE clause, in this instance telling MySQL to join together the rows in the tables where the IDs match each other. When joining tables using ON clauses, you can use any conditions that you would use in a WHERE clause, including all the various logical and arithmetic operators.

JOIN (Left)

Another common type of JOIN is the LEFT JOIN. When joining two tables with LEFT JOIN, all rows from the first table will be returned, no matter if there are matches in the second table or not. Suppose you have two tables in an address book, one called master_name, containing basic records, and one called email, containing email records. Any records in the email table would be tied to a particular id of a record in the master_name table. For example:

mysql> select name_id, firstname, lastname from master_name; 

+---------+-----------+----------+ 

|name_id | firstname | lastname 

| +---------+-----------+----------+ 

| 1 | John | Smith | 

| 2 | Jane | Smith | 

| 3 | Jimbo | Jones | 

| 4 | Andy | Smith | 

| 7 | Chris | Jones | 

| 45 | Anna | Bell | 

| 44 | Jimmy | Carr | 

| 43 | Albert | Smith | 

| 42 | John | Doe | 

+---------+-----------+----------+ 

mysql> select name_id, email from email; 

+---------+------------------+ 

| name_id | email | 

+---------+------------------+ 

| 42 | jdoe@yahoo.com | 

| 45 | annabell@aol.com | 

+---------+------------------+ 

JOIN (Left)

mysql> select firstname, lastname, email fom master_name left join email -> on master_name.name_id = email.name_id; 

+-----------+----------+------------------+ 

| firstname | lastname | email | 

+-----------+----------+------------------+ 

| John | Smith | NULL | 

| Jane | Smith | NULL | 

| Jimbo | Jones | NULL | 

| Andy | Smith | NULL | 

| Chris | Jones | NULL | 

| Anna | Bell | annabell@aol.com | 

| Jimmy | Carr | NULL | 

| Albert | Smith | NULL | 

| John | Doe | jdoe@yahoo.com | 

+-----------+----------+------------------+ 

JOIN (Right)

A RIGHT JOIN works like LEFT JOIN, but with the table order reversed. In other words, when using a RIGHT JOIN, all rows from the second table will be returned, no matter whether there are matches in the first table or not. However, in the case of the master_name and email tables, there are only two rows in the email table, whereas there are nine rows in the master_name table. This means that only two of the nine rows will be returned: 

mysql> select firstname, lastname, email from master_name right join email -> on master_name.name_id = email.name_id; 

+-----------+----------+------------------+ 

| firstname | lastname | email | 

+-----------+----------+------------------+ 

| John | Doe | jdoe@yahoo.com | 

| Anna | Bell | annabell@aol.com | 

+------+-----+----------------------------+ 

UPDATE Command to Modify Records 

UPDATE is the SQL command used to modify the contents of one or more columns in an existing record. The most basic UPDATE syntax looks like this:

UPDATE table_name SET column1='new value', column2='new value2' [WHERE some_condition_is_true] 

update grocery_inventory set curr_qty = curr_qty - 1 where id = 1; 

REPLACE Command 

Another method for modifying records is to use the REPLACE command, which is remarkably similar to the INSERT command.

REPLACE INTO table_name (column list) VALUES (column values); 

This command mimics the action of a DELETE and re-INSERT of a particular record. In other words, you get two commands for the price of one. 

Using the grocery_inventory table, the following command will replace the entry for Apples:

mysql> replace into grocery_inventory values (1, 'Granny Smith Apples', 'Sweet!', '0.50', 1000); 

DELETE Command 

The basic DELETE syntax is:

DELETE FROM table_name [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT rows] 

delete from access_log order by date_accessed desc limit 1;

Exporting Data to an Out File

INTO OUTFILE ‘’ [ []] ::= {FIELDS [TERMINATED BY ‘’] [[OPTIONALLY] ENCLOSED BY ‘’] [ESCAPED BY ‘’]} | {LINES [STARTING BY ‘’] [TERMINATED BY ‘’]}

Exporting Data to an Out File

SELECT CDName, InStock, Category INTO OUTFILE ‘CDsOut.txt’ FROM CDs; SELECT CDName, InStock, Category INTO OUTFILE ‘CDsOut.txt’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘“‘ FROM CDs; LOAD DATA Statement to Import Data LOAD DATA INFILE ‘’ INTO TABLE