Select Command

Select all column

select is the DDL command. The SELECT Command is used to retrieve information from a database. There are various ways in which the SELECT command can be used. Syntax of SELECT Command is as follows:

Syntax :-

SELECT * FROM table_name;

Example

mysql> SELECT * FROM student;
+----+---------+--------+--------+
| id | fname   | lname  | gender |
+----+---------+--------+--------+
|  1 | Ram     | Singh  | NULL   |
|  2 | Krishan | Kumar  | NULL   |
|  3 | Mohit   | Kumar  | NULL   |
|  4 | Savita  | Sharma | F      |
+----+---------+--------+--------+
4 rows in set (0.01 sec)

mysql>

Select specific column

The basic structure of a SELECT command comprises of the keyword SELECT followed by the attribute list (separated by comma (,)) you want to select, followed by FROM clause followed by the table name and lastly an optional WHERE clause which is followed by a condition that can be a Boolean expression or another SELECT command that identifies the tuples to be selected.

Syntax

SELECT field_name,field_name, ... FROM table_name;

Example

mysql> SELECT fname,lname FROM student;
+---------+--------+
| fname   | lname  |
+---------+--------+
| Ram     | Singh  |
| Krishan | Kumar  |
| Mohit   | Kumar  |
| Savita  | Sharma |
+---------+--------+
4 rows in set (0.00 sec)

mysql>

ALIAS

alias is as key work that is used for set other name of the column feild and table also. Alias also used in view

Syntax :-

SELECT column AS set_column_name FROM table_name;

Example

mysql> SELECT price AS rate FROM car;
+----------+
| rate     |
+----------+
| 20000.00 |
| 15000.00 |
| 40000.00 |
| 40000.00 |
| 40000.00 |
+----------+
5 rows in set (0.00 sec)

mysql>

WHERE Clause

where is a clause that is option part of query. It is used for set condition in query.

Syntax

select_query [ WHERE condition ];

Example

mysql> SELECT fname FROM student WHERE id > 2;
+--------+
| fname  |
+--------+
| Mohit  |
| Savita |
+--------+
2 rows in set (0.03 sec)

mysql>

---

ORDER Clause

ORDER clause is help to set data in format with the respect selected column. You can also set limit or data that you can select. Even you can set asc anddesc order.

Syntax

select_query [ ORDER BY column_name ];

Example

mysql> SELECT * FROM mytable ORDER BY id;
+------+---------+--------+
| id   | fname   | lname  |
+------+---------+--------+
|    1 | gutam   | budha  |
|    2 | octavia | NULL   |
|    5 | hattori | kanjo  |
|    7 | peter   | parker |
+------+---------+--------+
4 rows in set (0.01 sec)

mysql>

---

Set LIMIT

with the used to limit you can set quantity of data. Simple you can set number of data select in query if they present. As well as you can set how many number that you can select limit that called offset

Syntax

select_query [ ORDER BY column_name LIMIT number ];

Example

mysql> SELECT * FROM mytable LIMIT 1;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|    1 | gutam | budha |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> /* offset use before limit */
mysql> SELECT * FROM mytable ORDER BY FNAME LIMIT 2,2;
+------+---------+--------+
| id   | fname   | lname  |
+------+---------+--------+
|    2 | octavia | NULL   |
|    7 | peter   | parker |
+------+---------+--------+
2 rows in set (0.00 sec)

mysql>

ASC ORDER

asc used to format data in asccending order it is default.

Syntax

select_query [ ORDER BY column_name ASC ];

Example

mysql> SELECT * FROM mytable ASC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
mysql> SELECT * FROM mytable ORDER BY id ASC;
+------+---------+--------+
| id   | fname   | lname  |
+------+---------+--------+
|    1 | gutam   | budha  |
|    2 | octavia | NULL   |
|    5 | hattori | kanjo  |
|    7 | peter   | parker |
+------+---------+--------+
4 rows in set (0.00 sec)

mysql>

DESC ORDER

desc used to format data in desccending order it you can set.

Syntax

select_query [ ORDER BY column_name DESC ];

Example

mysql> SELECT * FROM mytable DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1
mysql> SELECT * FROM mytable ORDER BY id DESC;
+------+---------+--------+
| id   | fname   | lname  |
+------+---------+--------+
|    7 | peter   | parker |
|    5 | hattori | kanjo  |
|    2 | octavia | NULL   |
|    1 | gutam   | budha  |
+------+---------+--------+
4 rows in set (0.00 sec)

mysql>

---

GROUP Clause

The group clause is used to get concatenated values of expressions with more than one result per column. Meaning, there are many rows to be selected back for one column such as Name(1):Score(*). You can also use inside function

Syntax

select_query [ GROUP BY column_name ];

Example

mysql> SELECT * FROM address ORDER BY id;
+------+------------+
| id   | city       |
+------+------------+
|    1 | chandigarh |
|    2 | agra       |
|    3 | delhi      |
|    4 | delhi      |
|    5 | agra       |
|    6 | agra       |
|    7 | delhi      |
|    9 | delhi      |
|   10 | agra       |
|   11 | chanigarh  |
|   12 | delhi      |
+------+------------+
11 rows in set (0.00 sec)

mysql> SELECT city FROM address GROUP BY city;
+------------+
| CITY       |
+------------+
| agra       |
| chandigarh |
| chanigarh  |
| delhi      |
+------------+
4 rows in set (0.00 sec)

mysql> SELECT city,count(id) as 'Number_Of_employee' FROM address GROUP BY city;
+------------+--------------------+
| city       | Number_Of_employee |
+------------+--------------------+
| agra       |                  4 |
| chandigarh |                  1 |
| chanigarh  |                  1 |
| delhi      |                  5 |
+------------+--------------------+
4 rows in set (0.00 sec)

mysql>

---

HAVING Clause

where is a clause that is option part of query. It is used for set condition in query.

Syntax

SELECT fields FROM table_name [ GROUP BY column_name HAVING condition ];

Example

---

DISTINCT Select unique column

The distinct is used to select unique data between mutliple data. It is help to remove duplicate data from you selection query.

Syntax :-

SELECT DISTINCT FROM table_name;

Example

mysql> SELECT DISTINCT price FROM CAR;
+----------+
| price    |
+----------+
| 20000.00 |
| 15000.00 |
| 40000.00 |
+----------+
3 rows in set (0.00 sec)

mysql> SELECT price FROM CAR;
+----------+
| price    |
+----------+
| 20000.00 |
| 15000.00 |
| 40000.00 |
| 40000.00 |
| 40000.00 |
+----------+
5 rows in set (0.00 sec)

mysql>

---

IN

in function is use to compare mutliple values of columns it is very use full function then we need to compare multiple value by same field.

Syntax :-

select_query [ WHERE column_name in( data1,data2,... ) ];

Example

mysql> SELECT fname FROM mytable WHERE id = 1 or id = 2 or id = 9 or id = 5;
+---------+
| fname   |
+---------+
| gutam   |
| hattori |
| octavia |
| jan     |
+---------+
4 rows in set (0.00 sec)

mysql> SELECT fname FROM mytable WHERE id in(1,2,9,5);
+---------+
| fname   |
+---------+
| gutam   |
| hattori |
| octavia |
| jan     |
+---------+
4 rows in set (0.00 sec)

---

IS

May be select or deal with null value?. NULL is are made to work each other, Mean you can deal with null values use is.

Syntax :-

select_query [ WHERE column_name IS NULL ];

Example

mysql> SELECT fname FROM mytable WHERE lname = NULL;
Empty set (0.00 sec)

mysql> SELECT fname FROM mytable WHERE lname IS NULL;
+---------+
| fname   |
+---------+
| octavia |
| pooja   |
| aman    |
+---------+
3 rows in set (0.00 sec)

---

NOT

NOT is use to conver result in negetive mean if true so false and false so true.

Syntax

select_query [ WHERE NOT contition ];

Example

mysql> SELECT fname FROM mytable WHERE lname IS NOT NULL;
+---------+
| fname   |
+---------+
| gutam   |
| hattori |
| peter   |
| NULL    |
| monika  |
| jan     |
+---------+
6 rows in set (0.00 sec)

mysql>

---

LIKE

The like operator is used for deal with string it is very good for selecting to target string in query.

• like 'aman' is require to search full string
• like '%man' if you don't know before charaters
• like '_man' is you don't know before single character
• like '%man' if you don't know after charaters
• like '_man' is you don't know after single character
• like 'w%n' if you forget between characters
• like 'wo_an' if you forget between single character

Syntax

select_query [ WHERE column LIKE 'value' ];

Example

mysql> select * from mytable;
+------+---------+-------------+
| id   | fname   | lname       |
+------+---------+-------------+
|    1 | gutam   | budha       |
|    5 | hattori | kanjo       |
|    2 | octavia | NULL        |
|    7 | peter   | parker      |
|    3 | pooja   | NULL        |
|    4 | aman    | NULL        |
|    6 | NULL    | port        |
|    8 | monika  | gustamridey |
|    9 | jan     | mhod        |
+------+---------+-------------+
9 rows in set (0.00 sec)

mysql> SELECT id FROM mytable WHERE fname like 'aman';
+------+
| id   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql> SELECT id FROM mytable WHERE fname like 'p%';
+------+
| id   |
+------+
|    7 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT id FROM mytable WHERE fname like '%a';
+------+
| id   |
+------+
|    2 |
|    3 |
|    8 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT id FROM mytable WHERE fname like '_an' or  'm__d';
+------+
| id   |
+------+
|    9 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT fname FROM mytable WHERE fname like '_c%';
+---------+
| fname   |
+---------+
| octavia |
+---------+
1 row in set (0.00 sec)

mysql> SELECT fname FROM mytable WHERE fname like '%a%';
+---------+
| fname   |
+---------+
| gutam   |
| hattori |
| octavia |
| pooja   |
| aman    |
| monika  |
| jan     |
+---------+
7 rows in set (0.00 sec)

mysql>