MYSQL TIPS

We have seen SQL SELECT command along with WHERE clause to fetch data from MySQL table. But when we try to give a condition which compare field or column value to NULL it does not work properly.

To handle such situation MySQL provides three operators

IS NULL: operator returns true of column value is NULL.

IS NOT NULL: operator returns true of column value is not NULL.

<=> operator compare values, which (unlike the = operator) is true even for two NULL values

tbl

....................................

id name

................................

1 NULL

2 ABC

3 XYZ

4 NULL

////////////////////////////////////////////Wrong

mysql> SELECT * FROM tbl WHERE name= NULL;

Empty set (0.00 sec)

mysql> SELECT * FROM tbl WHERE name != NULL;

Empty set (0.01 sec)



///////////////////////////Correct

mysql> SELECT * FROM tbl WHERE name IS NULL;

Output :

Id Name

1  NULL

4  NULL



mysql> SELECT * from tbl WHERE name IS NOT NULL;

Output :

Id Name

2  ABC

3  XYZ



///////////////////////////////////

What is the use of i-am-a-dummy-flag in mysql?

It makes mysql engine to refuse UPDATE,DELETE command where WHERE clause is not present.

4. Delete multiple Records from a table leaving the table to contain only unique records
   
   eg:
   
   id name
   ===============
   1 Ragesh
   2 Ragesh
   3 Ragesh
   4 subeesh
   5 Rishad
   6 subeesh
   7 Reshma
   8 Reshma
   9 shalini
   
   The o/p should be :
   
   Ragesh
   subeesh
   Rishad
   Reshma
   Shalini

Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by]; Here: 

CREATE TABLE new_tbl AS SELECT *

FROM old_tbl WHERE 1 GROUP BY 'name'

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!

		DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

		RENAME TABLE new_table TO old_table;

Step4: Select name from old_tbl