SOQL

Use: to read information stored in your org’s database

Implementation: Can write and execute a SOQL query in Apex code or in the Developer Console’s Query Editor.

syntax:(similar to SQL (Structured Query Language))

SELECT Id, *Name, BillingCity* FROM Account
SELECT *count()* FROM Contact (Give count)
SELECT *FIELDS(STANDARD)* FROM Contact (Give all std field)

• * elements are fieldlist and it defines the order of field in result.Fieldlist can also be an aggregate function,such as COUNT() and COUNT(fieldName)

Child to Parent

SELECT <b>Contact.Firstname, Contact.Account.Name</b> FROM Contact

Parent to child

SELECT Account.Name,(SELECT Contact.LastName FROM Account.Contacts)FROM Account

Complete syntex
SOQL query syntax consists of a required SELECT statement followed by one or more optional clauses, such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.
The SOQL SELECT statement uses the following syntax:

SELECT fieldList [subquery][...]
[TYPEOF typeOfField whenExpression[...] elseExpression END][...]
FROM objectType[,...] 
    [USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} 
    [HAVING havingConditionExpression] ] 
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[FOR {VIEW  | REFERENCE}[,...] ]
      [ UPDATE {TRACKING|VIEWSTAT}[,...] ]

//You don’t need to specify the Id field in the query as it is always returned in Apex queries

• LIKE: WHERE Name LIKE 'SFDC%'. The % wildcard character matches any or no character

for loop in SOQL

for (variable : [soql_query]) {
    code_block
}

or

for (variable_list : [soql_query]) {
    code_block
}

Difference between ':', "=" and "IN" operators
":" to bind a variable
"=" to check value
"IN" to check value in a collection

use ":" bind variable for access variable in query like
String MyName = 'vasu';
List<Contact> ListOfName = [SELECT FirstName, LastName FROM Contact WHERE FirstName = : MyName];
use "=" operator is simple Equal operator in query like-:
List<Contact> ListOfName = [SELECT FirstName, LastName FROM Contact WHERE FirstName = 'vasu'];
[give contact list where firstName = (equal) vasu]
use IN operator in soql for refer LIST or SET direct in query like
List<String> lstOfStr = new List<String>{'testName','testName2'};
List<Contact> ListOfName = [SELECT FirstName, LastName FROM Contact WHERE FirstName IN : lstOfStr];
[here also use bind ":" operator for access varible value in query ]

Link