| | |
SQL SELECT CommandSelect command is used to collect date from the table. We will discuss more on this by adding more commands to this select command. If you have not created the tables then please go to sql introduction page to use the sql commands to create the tables and fill the table with some records ( data ).
Now your table with some data is ready. We
will apply select command to our table ( name student ) and fetch all the records
SELECT * FROM `student`
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 2 |
Max Ruin |
Three |
85 |
| 3 |
Arnold |
Three |
55 |
| 4 |
Krish Star |
Four |
60 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
That's
all to get all the records from the table student. We have not placed any
restriction here and asked for all the fields with all the records. Now if we
want to restrict our results and get only name field from the table.
SELECT name FROM `student`
This will return only name field from the table. We can ask for more fields and all
field names we have to separate by comma. We can include as many field names we
want from a table.
SELECT name, class FROM `student`
Now we will go for bit more and restrict the
number of records we are getting. We are interested say in only 3 records. Our
command should return 3 records only. We will use SQL limit command. This will take
two parameters. One is the starting point and other is number of records
required. Say we are interested in 3 records starting from beginning. Our
command will be
SELECT * FROM `student` LIMIT 0,3
This
command with LIMIT command will return us 3 records starting from 0 ( or first )
record. This is very important when we use this command with ORDER BY command.
Now let us try to list all the students who have come in first 3 ranks. We are
required to list here 3 records who have mark more than the others. The top 3
students we want to display in order of first, second and third. The order we
can display are in by default in ascending order but we require the listing
should return in descending order so we will get the highest ranked student at
the top. Before that let us start with a simple example of ORDER BY command.
SELECT * FROM `student` ORDER BY mark
This will display the
records of students in the order of lowest mark to highest mark We will change
it to display in reverse order so it will display highest mark at the top and
lowest mark at the bottom.
SELECT * FROM `student` ORDER BY `mark` DESC
With the addition
of command DESC we can change the order of display to keep the highest mark at
the top of the list and lowest mark at the bottom of the list. Now let us add
the LIMIT command to display only the top 3 records. We already have the list in
the order of highest mark to lowest mark so by just limiting the number of
records to 3 will give our required top three student records.
SELECT * FROM `student` ORDER BY `mark` DESC LIMIT 0,3
| id |
name |
class |
|
2 |
Max Ruin |
Three |
85 |
| 1 |
John Deo |
Four |
75 |
| 4 |
Krish Star |
Four |
60 |
This is the SQL query which will display top three students based on the mark
they scored. In the next section we will use sql WHERE clause to restrict or filter
the records.
| |
| | raju | 11-04-2013 |
|---|
| How to get ( select ) data from more than one table ? |
|
|
|
|
|
|
