Fetching Records from Database
In this post I want to talk about fetching records from your database using SQL Syntax. As always, let's start with an example. Imagine we have one table which is called ‘Person‘.
In this post I want to talk about fetching records from your database using SQL Syntax.
As always, let's start with an example. Imagine we have one table which is called ‘Person‘. ‘Person‘ attributes are, ‘pid, name, type‘ and our primary key is ‘pid‘ and it is filled with records. How can we fetch records from this table? Well the answer is easy. There is an SQL Syntax which fetches all records in a table, SELECT.
How do we use SELECT?
When writing your query using SELECT, you only need to say which fields of the table, and which table you want to select from. Imagine we want to select all fields of Person and we want all records. Our query would be:
"SELECT * FROM Person"
The little asterisk symbol after SELECT, shows that we want to select all fields and the name of our table comes after FROM. So this query returns something like the following table:
|
pid |
name |
type |
|
1 |
Hirad |
user |
|
2 |
Mehrdad |
user |
|
3 |
Tom |
user |
|
4 |
Jack |
user |
If you only want to select some columns of your table, instead of * just write the name of your columns and separate them with a comma. As an example the following query returns only 'pid' and 'name':
"SELECT pid, name FROM Person"
So this query returns something like the following table:
|
pid |
name |
|
1 |
Hirad |
|
2 |
Mehrdad |
|
3 |
Tom |
|
4 |
Jack |
How can we limit our query to show us some specific record(s)?
This is easy too. You just need to use WHERE after the name of your table to limit your query. For example records that their type is user would be:
"SELECT * FROM Person WHERE type='user'"
Or a query to show records that their type is user and their name is Jack would be:
"SELECT * FROM Person WHERE type='user' AND name='Jack'"
You can also user OR instead of AND in your condition. For example, records that their name is Jack or Tom would be:
"SELECT * FROM Person WHERE name='Jack' OR name='Tom'"
You can also use LIMIT and OFFSET in your query to limit the number of records you want to fetch. I have talked about LIMIT and OFFSET in another post!