1 MySQL prepared

2 Retrieving Data

  • To use SELECT to retrieve table data, you must give at least two pieces of information — what do you want to SELECT, and from where

2.1 Retrieve a single column

  1. SELECT prod_name FROM products

2.2 Retrieve multiple columns

  1. SELECT prod_id, prod_name, prod_price FROM products

2.3 Retrieve all columns

  1. SELECT * FROM products

2.4 Retrieve different rows

  1. SELECT vent_id FROM products
  2. SELECT DISTINCT vent_id FROM products
  3. DISTINCT applies to all columns, not just the column that precedes it

2.5 Restricted Results

  1. SELECT prod_name FROM products LIMIT 5
  2. SELECT prod_name FROM products LIMIT 5, 5
  3. The first argument is the starting position and the second argument is the number of rows to retrieve

2.6 Use fully qualified table names

3 Sort and retrieve data

3.1 Sorting Data

  1. Relational database design theory states that if the sorting order is not clearly specified, the order of retrieved data should not be assumed to be meaningful
  2. SELECT prod_name FROM products
  3. SELECT prod_name FROM products ORDER BY prod_name

3.2 Sort by multiple columns

  1. SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name

3.3 Specifying the sorting direction

  1. SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC
  2. SELECT prod_id, prod_price, prod_name FROM products ORDER BY product_price DESC, prod_name
  3. The DESC keyword applies only to column names directly preceding it
  4. SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1

4 Filtering Data

4.1 Use the WHERE clause

  1. SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50

4.2 WHERE clause operator

4.2.1 Checking a Single value

  1. SELECT prod_name, prod_price FROM products WHERE prod_name = ‘fuses’
  2. SELECT prod_name, prod_price FROM products WHERE prod_price < 10
  3. SELECT prod_name, prod_price FROM products WHERE prod_price <= 10

2.2.2 Checking Mismatches