MySQL Query tricks

MySQL one the very famous Database. Today i going to share with you very simple tricks of MySQL. MySQL DB always come with WAMP, LAMP, XAMP and MySQL is easy because there are many supporters and old and newbie start there first work with db and that db is MySQL.

Let Start

First of all I am taking SELECT , INSERT, UPDATE, DELETE Command and then I shall show you how you can do some trick with your MySQL query by which your work will easy.

SELECT

SELECT * FROM "your table name"

INSERT

INSERT INTO table_name (column1,column2,column3) VALUES (value1,value2,value3,...);

UPDATE

UPDATE table_name SET column1=value1,column2=value2 WHERE some_column=some_value;

DELETE

DELETE FROM table_name WHERE some_column=some_value;

Those shown queries are basic queries which we need in our each and every task in mysql, but by the help of these queries you can do main things or by the help of combinaton of those queries you can perform CRUD operation. Let me show how.

DELETE & SELECT

DELETE FROM TABLE_A WHERE TABLE_A IN(SELECT ID FROM TABLE_B)

Get Auto increment id of your table using query

SHOW TABLE STATUS FROM `DatabaseName` LIKE 'TableName' ;

Get column defination in sql

SHOW COLUMNS FROM `actors` LIKE 'id'

Case creation in sql query

<span class="kwd">SELECT</span> <span class="kwd">CASE</span> <span class="kwd">WHEN</span><span class="pln"> column name IS NULL</span> <span class="kwd">THEN</span> <span class="lit">1</span> <span class="kwd">ELSE</span> <span class="lit">0</span> <span class="kwd">END</span> <span class="kwd">as</span><span class="pln"> abc </span><span class="kwd">FROM</span><span class="pln"> table name
</span>