MySQL - retrieve data

retrieve a single column

SELECT p_name FROM products

• The order in which the results are returned here may be different, which may or may not be the order in which the data is stored.
• Writing this will return all rows, neither filtered nor sorted.
• Although not required, keyword capitalization is a respected coding style

Retrieve multiple columns

If you want to retrieve multiple columns, just add multiple field names after SELECT and separate them with commas.

 SELECT p_name, p_id, p_price FROM products

retrieve all columns

SELECT * FROM products
Such retrieval will lead to retrieval and application performance, and is not recommended.

retrieve different rows

SELECT DISTINCT p_kind FROM products
The DISTINCT keyword can return data of different rows. If there are 5 types of p_kinds, each with 500 in the database, then the above statement will only return 5 pieces of data with different p_kinds.

Retrieval returns limited rows

SELECT p_name FROM products LIMIT 10,10
LIMIT10,10 represents the 10 rows to be retrieved starting from row 10. This row counts from row 0.

Retrieve fully qualified table name

 SELECT products.p_name FROM walmart.products

Specify both table name and field name

---

Reference: Forta B. MySQL crash course[M]. Pearson Education India, 2006.