MySQL Learning Note 004 -- Querying

Select rows based on column value

SELECT * FROM table_name WHERE col_name = value; // select all rows that satisfies
 
SELECT col_1, col_2 FROM table_name WHERE col_name = value; // only specified columns from rows that satisfies

Example matching conditions (after which)

• Exact equality: 

  country_id = 15;

• Inequality:

city_id < 5 -- (>, >=, <=, !=/<>)

• Check if col value is NULL:

col IS NULL;
col IS NOT NULL;

Match string suffix: 

title LIKE '%family'; // string ending with family

• Match string prefix: 

title LIKE 'family%'; // string starting with family

• String containing keyword: 

title LIKE '%family%'; // string containing family

• Match exactly 1 wildcard character:

first_name like 'NATALI_' // will match natalie, won't match nataliey

• Combine conditions
  • AND: (category LIKE 'Sci-Fi') AND (rating LIKE 'PG');   // match rows that meet both conditions
  • OR: (category LIKE 'Children') OR (category LIKE 'Family');  // match rows that meet at least one condition
  • NOT: FID < 7 AND NOT (FID = 4 OR FID = 6);

*Note: % wildcard may cause performance issue

Sort query result

SELECT name FROM customer_list
    -> ORDER BY name
    -> LIMIT 10;

• Order by sort result in ascending order, based on the field specified
• Limit shows the top k sorted items

ORDER BY district ASC, address DESC

• Use ASC or DESC to change sorting order (sort by increasing district first, if same district, order by desc address)

 LIMIT 10 offset 5;

• Show the result start at 6th row and the next 10 rows (offset 5)

Get max value

SELECT MAX(col_name) FROM table_name;

which is equivalent to

SELECT col_name FROM table_name ORDER BY col_name DESC LIMIT 1

Inner Join