SQL

Contents
      1. Misc
              1. Remove duplicates (only takes into account the given columns):
              2. Select first words:
              3. Compare time:
              4. MySQL Stored Procedure Characteristics Clause Defaults:
              5. Character Set & Collation:
      2. Normal Forms Reminder
      3. Links

Misc

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)
Compare time:
 DELETE from seeds  where timediff(now(), zeit) > time('12:00:00'); 
MySQL Stored Procedure Characteristics Clause Defaults:
 LANGUAGE SQL
 NOT DETERMINISTIC
 SQL SECURITY DEFINER -- not INVOKER!
 COMMENT ''
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, ...
MySQL encodings (Overview, Spanish) • MySQL Manual: Server System Variables

Normal Forms Reminder

  1. Tables are relations
  2. 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.
  3. No transitive dependencies such as Location->Zip->Person (refers to non-key-candidate fields only)
  4. No tables that are prone to listing all n1*n2*... attribute combinations such as relations(person, child, ancestor).
  5. 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.

Links


EditContents