Basic SQL Select statement - 1 (need to change the layout)

Basic SQL Select statement - 1

The blog is for a basic illustration about Select statement , all the examples are tested in MYSQL 5.6.

Basic Select:

Select [field1,field2] from [table-name];

Order clause:

Order clause is to make the  output order by the specified field.

Order by [field] [ASC| DESC]

 By default , it is ‘ASC’ ascend

With the limit clause, we can just see the limited number of output.

 

Filter using where clause.

When we want to filter the output , we can use where clause.

Where can be combined with the below operations.

Some sample.

1.       List the price < 10 $

Eg: where prod_price < 10

2.       List the vendor is not 1003

Eg: where vend_id <> 1003

3.       Check the email is NULL

Eg: where cust_email is NULL

AND, OR, IN operators

When we need multiple where clauses, we can use AND, OR to combine them

For example, we want to list all products produced by 1003 or 1002 , and the price is less than 10.

where (vend_id = 1002 or vend_id = 1003) and prod_price < 10;

Alternatively, we can use

where vend_id in (1002, 1003)and prod_price <10;

Like (% _) wildcards.

We can use ‘like’ predicate and ‘%’ ‘_’ to do wildcards matching in where clause.

• %: matching any characters (repeated)
• _: matching any single character.

For example, we want to find out the product name like ‘jet%’ (jet1, jetpack 1000, jet)

Where prod_name LIKE ‘jet%

Attention : use wildcards inproperly may cause serious performance problem, especially % is used as the beginning searching pattern.