Remove duplicates (only takes into account the given columns):
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(column1, ...);
Select first words:
select distinct substr(name, 1, instr(name, ' ') - 4)
DELETE from seeds where timediff(now(), zeit) > time('12:00:00');
MySQL Stored Procedure Characteristics Clause Defaults:
SQL SECURITY DEFINER -- not INVOKER!
Character Set & Collation:
MySQL variables: They exist as session or as global variables. character_set_client (for MySQL clients older than 4.1), character_set_connection, collation_connection, ...
- Schema default: CREATE/ALTER DATABASE db DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' (ci=case insensitive)
- Relation: CREATE TABLE `people` (`street` char(100) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' DEFAULT NULL, ...)
- At query: SELECT ... ORDER BY description COLLATE utf8_general_ci
(Overview, Spanish) • MySQL Manual: Server System Variables
Normal Forms Reminder
- Tables are relations
- No columns that elaborate on only a part of the subject (e. g., depend only on a subset of the key) such as the buyer's address in an order(person, item, ...) table.
- No transitive dependencies such as Location->Zip->Person (refers to non-key-candidate fields only)
- No tables that are prone to listing all n1*n2*... attribute combinations such as relations(person, child, ancestor).
- No listing of attribute combinations unless necessary. That is, stick to NF4 even if certain values reduce the number of combinations that can be made.
BCNF. No mutually replaceable key candidates like in (servername, service, ip)
DKNF. A value in one non-key-column may not reduce the domain of possible values for another column as infringed in (_city_, country, river) where an anomaly like a Nile-China might occur.