Important MySQL Queries

First create two tables, demo and demoupdate.

CREATE TABLE `demo` (
  `slno` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `phone` VARCHAR(45) NOT NULL,

   'id' INTEGER NOT NULL,
   PRIMARY KEY (`slno`)
);

CREATE TABLE  `demoupdate` (
  `slno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `count` int(10) unsigned NOT NULL,
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`slno`),
  KEY `de_cnt` (`count`)
);

SQL SELECT: Selects all the columns from table demo where slno=1.

select * from demo where slno=1;

SQL INSERT: Inserts new records into a table.

insert into demo values(0,'a','[email protected]','1254856254',1);

SQL UPDATE: Updates existing records in a table. updates name where name='a'.

update demo set name='Sookshmas' where name='a';

SQL DELETE: Deletes existing records in a table.Deletes the row where slno=1.

delete from demo where slno=1;

SQL LIKE:  Searches for a specified pattern in a column.

select name from demo where name like 'a%';

'a%' - searches for name starting with a.

'%a' - searches for name ending with a.

'%a%' - searches for a in any position.

SQL CASE:  It is like an IF-THEN-ELSE statement. It goes through conditions, once a condition is true, it returns the result. 

If no conditions are true, it returns the value in the ELSE clause.

select id,case when count>10 then 10 else count end as cnt from demoupdate;

SQL UNION: It is used to combine the result of two or more select statements. Each select statement must have same no. of columns, same datatype and must be in same order.

select name,' ' as id from demo union select ' ' as name,id from demoupdate;

The above statement selects name from demo and id from demoupdate.In the first select statement id column is not there , so I have taken ' ' . Similarly in the second select statement name column is not there, so I have taken ' ' .

Notice both select statement are having, same columns, same datatype and same order.

SQL JOINS : It is used to combine rows from two or more tables, based on a related column between them.

Consider 2 tables demo and demoupdate. The relationship between two tables is id column.

demo table:

 demoupdate table:

• INNER JOIN : Returns records that have matching values in both tables.

          select d.name,du.count from demo d inner join demoupdate du on d.id=du.id;

output:

• LEFT JOIN: Returns all records from the left table, and the matched records from the right table. If there is no matching record in right table, it returns null.

           select d.name,du.count from demo d left join demoupdate du on d.id=du.id;

output:

• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table. If there is no matching record in left table, it returns null.

            select d.name,du.count from demo d right join demoupdate du on d.id=du.id;

output: