Filtering, sorting, NULL, changing data

More ways to filter rows in WHERE:

• BETWEEN a AND b: values in a range, including both ends. Salary BETWEEN 20000 AND 50000 includes 20000 and 50000.
• IN (list): values matching any in the list, such as DeptId IN ('D01', 'D02'). NOT IN excludes them.
• LIKE with wildcards matches patterns in text: % stands for any number of characters (even none), and _ for exactly one. So 'K%' means starting with K; '%a' ending with a; '_ANYA' five letters ending in ANYA; '%se%' containing se.

Sorting: ORDER BY column sorts the output in ascending order; add DESC for descending. A second column breaks ties: ORDER BY DeptId, Salary DESC.

NULL means a value is unknown or missing. It is not zero or a blank. Any arithmetic with NULL gives NULL. To test for it, use IS NULL or IS NOT NULL, never = NULL.

Changing data:

• UPDATE table SET column = value WHERE condition; changes values in the matching rows.
• DELETE FROM table WHERE condition; removes the matching rows.

Without a WHERE clause, UPDATE changes every row, and DELETE removes every row. So always check the WHERE clause first.