Selecting records in MySQL

Returning all records and all column values using Select keyword. Below query shows how to get all rows from table CUSTOMER.
 
SELECT * FROM CUSTOMER

Below query shows how to get values from the specific columns from table CUSTOMER.
 
SELECT AreaName, CustomerId FROM CUSTOMER

Selecting first 2 records.
 
SELECT * FROM CUSTOMER LIMIT 2

Selecting records from the table without repeating a value of a column. For example – If we execute below query, it will display all area names. The same AreaName may be repeated again in the query.
 
SELECT AreaName FROM CUSTOMER

Below query will return only distinct values from AreaName column in CUSTOMER table.
 
SELECT DISTINCT AreaName FROM CUSTOMER

Sorting records Below query shows how to get all rows from table CUSTOMER sorted by FirstName in Ascending order.
 
SELECT * FROM CUSTOMER ORDER BY FirstName
SELECT * FROM CUSTOMER ORDER BY FirstName ASC

Below query shows how to get all rows from table CUSTOMER sorted by FirstName in Descending order.
 
SELECT * FROM CUSTOMER ORDER BY FirstName DESC

We can also sort by multiple columns.
 
SELECT * FROM CUSTOMER ORDER BY FirstName, LastName DESC;

Where Clause – Below query returns the records for male customers only.
 
SELECT * FROM CUSTOMER WHERE Sex=’m’;

We can use other operators like
 
!=, <, <=, >, >= 
        
etc. in where clause. In MySQL, we have 2 logical operators – AND, OR, NOT AND operator allows you to filter the records matching all column conditions.
 
SELECT * FROM CUSTOMER WHERE Sex=’m’ AND LastName=’Obama’;

OR operator allows you to filter the records matching one of the column conditions.
 
SELECT * FROM CUSTOMER WHERE Sex=’m’ OR LastName=’Obama’;

NOT operator allows you to filter the records not matching one of the column conditions. Below query will return all records where customer sex is not male.
 
SELECT * FROM CUSTOMER WHERE NOT Sex=’m’;

You can not use = operator with NULL values. You need to use below syntax to check if value is null.
 
SELECT * FROM CUSTOMER WHERE AddressLine2 IS NULL;
SELECT * FROM CUSTOMER WHERE AddressLine2 IS NOT NULL;

LIKE keyword – Selecting the records where values of certain column match the given pattern. % symbol is used to match zero or more number of characters. For example – in below query, we will get all customer records where last name starts with Ob.
 
SELECT * FROM CUSTOMER WHERE LastName LIKE ‘Ob%’;

Below query will return all records where customer name contains Ob substring.
 
SELECT * FROM CUSTOMER WHERE LastName LIKE ‘%Ob%’;

_ symbol is used to match any one character. For example – in below query, we will get all customer records where last name starts with Obam and ends with any other letter.
 
SELECT * FROM CUSTOMER WHERE LastName LIKE ‘Obam_’;
SELECT * FROM CUSTOMER WHERE LastName LIKE ‘OBam_’;

Note that pattern is case in-sensitive. It depends on database server setting. If you want case sensitive filter, you will have to edit the server settings. Escaping the character using \. Let us say you want to get the records from ADVERTS table where title contains string _Brisbane
 
SELECT * FROM ADVERTS WHERE AdTitle LIKE ‘%_Brisbane%’;

IN operator allows you to filter the records based on multiple matching values. Both of the below queries are same.
 
SELECT * FROM CUSTOMER WHERE AreaName IN (‘TOOWONG’,’TARINGA’)
SELECT * FROM CUSTOMER WHERE AreaName =’TOOWONG’ OR AreaName =’TARINGA’)

BETWEEN keyword is used to filter the records within specific range. For example – Below query will return the records from customer table where customer id is in the range from 2-4.
 
SELECT * FROM CUSTOMER WHERE CustomerId BETWEEN 2 AND 4

UNION keyword is used to combine the output of 2 queries.
 
SELECT * FROM CUSTOMER WHERE AreaName =’TOOWONG’ UNION SELECT * FROM CUSTOMER WHERE CustomerId = 2

INTERSECT – This keyword is not available in MySQL. You need to use joins to perform INTERSECT like operation in MySQL.

**Alias in MySQL**

Alias – An alias is a temporary name given to the table or column. In below example, we have used c as an alias for CUSTOMER table.
 
SELECT * FROM CUSTOMER c where c.CustomerId =2

In below example, we have used c as an alias for CUSTOMER table and Name as an alias for FirstName column.
 
SELECT FirstName Name FROM CUSTOMER c where c.CustomerId =2

Regular expression in MySQL

You can use regular expressions in SQL queries. For example below query will return the records where LastName starts with b.
 
SELECT * FROM CUSTOMER WHERE LastName REGEXP ‘^b’;

Below query will return the records where LastName ends with b.
 
SELECT * FROM CUSTOMER WHERE LastName REGEXP ‘b$’;

Below query will return the records where LastName contains am.
 
SELECT * FROM CUSTOMER WHERE LastName REGEXP ‘am’;

We can get the column value in variable using below syntax.
 
Select FirstName into @firstname from CUSTOMER where CustomerId=1

Joins in SQL Queries

Joins – Joins are used to combine multiple tables and return the results for each record. There are 3 types of joins. – Inner Join – Gets the records matching in both tables
 

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.column1=table2.column2;

SELECT *
FROM CUSTOMER
INNER JOIN ADVERTS
ON customer.CustomerId=adverts.CustomerId
INNER JOIN category
ON adverts.CatId=category.CatId;
– left (outer) Join – Gets all records from left table as well as matching ones from the right table
 
SELECT column1, column2
FROM table1
LEFT OUTER JOIN table2
ON table1.column1=table2.column2;

SELECT *
FROM CUSTOMER
left outer JOIN ADVERTS
ON customer.CustomerId=adverts.CustomerId;

SELECT *
FROM CUSTOMER
left JOIN ADVERTS
ON customer.CustomerId=adverts.CustomerId;
– Right (outer) Join – gets all records from right table as well as matching one from the left table
 
SELECT column1, column2
FROM table1
RIGHT OUTER JOIN table2
ON table1.column1=table2.column2;

SELECT *
FROM CUSTOMER
right outer JOIN ADVERTS
ON customer.CustomerId=adverts.CustomerId;

SELECT *
FROM ADVERTS
right JOIN category
ON category.CatId=adverts.CatId;
– Full Join (outer) – There is no full outer join in MySQL. Full outer join gets all records from both tables (matching as well as non-matching) We can simulate the full outer join by firing below query.
 

SELECT column1, column2
FROM table1
LEFT OUTER JOIN table2
ON table1.column1=table2.column2
union
SELECT column1, column2
FROM table1
RIGHT OUTER JOIN table2
ON table1.column1=table2.column2;
SELECT *
FROM CUSTOMER

left JOIN ADVERTS

ON customer.CustomerId=adverts.CustomerId

union

SELECT *
FROM CUSTOMER
right outer JOIN ADVERTS

ON customer.CustomerId=adverts.CustomerId;
In same way, we can join multiple tables
Joining multiple tables is very easy. Just add another join keyword.
SELECT column1, column2, column3
FROM table1
INNER JOIN table2
ON table1.column1=table2.column2
INNER JOIN table3
ON table2.column3=table3.column4;

Cross Join – cross join allows you to return all the rows from other table for each row in first table.
 
SELECT *
FROM category

Cross JOIN ADVERTS
Subqueries Subqueries means queries used inside other queries. Main difference between subqueries and joins is that subquery may return scalar value. But Joins always return rows.
 
select * from customer where customerId in (select custid from oders where orderdate=now())

Web development and Automation testing

solutions delivered!!