---
format: md
toc: no
...

# SQL

### MySQL-specific Tricks

To output in tab-delimited format, do something like:

    mysql -u root -p -B < some_command.sql > output.tab

### Setup User Permissions

On contemporary Debian, to login as root do:

    sudo mysql -u root

Then create a new password-less local-only user:

    CREATE USER 'bnewbold'@'localhost' IDENTIFIED BY '';
    UPDATE mysql.user SET plugin='unix_socket' WHERE user='bnewbold';
    GRANT ALL PRIVILEGES ON * . * TO 'bnewbold'@'localhost';
    FLUSH PRIVILEGES;

### UPDATE with JOIN

To do an update based on a join::

    UPDATE tableOne t1 INNER JOIN tableTwo t2 ON t1.fk_two = t2.id SET t1.has_two = 1 WHERE t2.is_good = 1;

### JOINs

Useful visual summary: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins