MYSQL useful snippet

Table name = tbl1 or tbl2

Table row   tbl1 = 4 , tbl2=3

————————————————————————————

(A) INNER JOIN :-

1. simple inner join :-

SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.class=tbl2.class

———————————————————————————

2. Table Field According joining : –

SELECT tbl1.id,  tbl1.name , tbl1.class , tbl2.fee  FROM tbl1 INNER JOIN tbl2 ON tbl1.class=tbl2.class

—————————————————————————————————

3. Max fee two person name :-

SELECT tbl1.id,  tbl1.name , tbl1.class , tbl2.fee  FROM tbl1 INNER JOIN tbl2 ON tbl1.class=tbl2.class order by tbl2.fee desc limit 2

——————————————————————————————————

(B) LEFT JOIN :-

1. Simple left join :-

SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.class=tbl2.class

tbl1 =4  tbl2=3

output = 4

——————————————————————————————————————————————————

(C) RIGHT JOIN :-

1. Simple right  join :-

SELECT * FROM tbl1 RIGHT JOIN tbl2 ON tbl1.class=tbl2.class

tbl1 =4  tbl2=3

output = 3

———————————————————————————————————————————————————————–

(D) FULL JOIN :-

1. Simple full join :-

SELECT * FROM tbl1 FULL JOIN tbl2 ON tbl1.class=tbl2.class

tbl1 =4  tbl2=3

output = 4

———————————————————————————————————————————————————————–

(E) CROSS JOIN :-

1. Simple cross join :-

SELECT * FROM tbl1 CROSS JOIN tbl2

tbl1=4  tbl2=3

output = 12

———————————————————————————————————————————————————————–

(F) BETWEEN :-

1. Simple between :-

SELECT * FROM tbl2 WHERE fee BETWEEN 100 AND 200

————————————————————————————————————————————————————————

(G) IN  :-

1. Simple In :-

SELECT * FROM tbl1 WHERE class IN (‘i’,’ii’)

————————————————————————————————————————————————————————

(H) LIKE :-

1. Simple Like :-

SELECT * FROM tbl1 WHERE name LIKE ‘%ab%’

2. SELECT * FROM tbl1 WHERE name NOT LIKE ‘%ab%’

3. SELECT * FROM tbl1 WHERE name LIKE ‘a%’

4. SELECT * FROM tbl1 WHERE name  LIKE ‘%a’

————————————————————————————————————————————————————————

(I) UNION :-

1. Simple UNION :-

SELECT class FROM tbl1 UNION SELECT class FROM tbl2

OUTPUT :- Unique Value return

———————————————————————————————————————————————————————–

(J)  Create new table :-

create table (id int , name varchar(255) , email varchar(255) , password varchar(255))

———————————————————————————————————————————————————————–

(K) Drop table :-

drop table tabl4

———————————————————————————————————————————————————————–

(L) Truncate :-

truncate table tabl3

———————————————————————————————————————————————————————-

(M) Maths Function :-

1. AVG :-

select avg(fee) from tbl2

2. MAX :-

select max(fee) from tbl2

3.COUNT

select COUNT(fee) from tbl2

4.MIN :-

SELECT min(fee) FROM tbl2

5.SUM :-

SELECT sum(fee) AS total fee FROM tbl2

6. GROUP BY :-

SELECT SUM(fee) FROM tbl2 GROUP BY class

SELECT class ,SUM(fee) FROM tbl2 GROUP BY class

7. HAVING :-

SELECT SUM(fee) FROM tbl2 GROUP BY class HAVING SUM(fee)<1000

8.UPPER CASE :-

SELECT upper(name) FROM `tbl1`

9. LOWER :-

SELECT lower(name) FROM `tbl1`

10.LCASE

SELECT lcase(name) FROM `tbl1`

11.MID:- substr in sql

SELECT MID(city,1,4) as SmallCity FROM tabl3

12.ROUND :-next value return like 10.20 output 11

SELECT class, ROUND(fee,0) FROM tbl2

13.LENGTH :- get name String langth

SELECT LEN(name) FROM tbl1

14.NOW :- Today add query display

SELECT name, Now() as todaydatetime from tbl1

15.NOW FORMAT :-Set date format

SELECT name, class, FORMAT(Now(),’YYYY-MM-DD’) as PerDate

FROM tbl1

SQL : Query.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s