====== MySQL ======

----

===== Cleaner Terminal Output =====
MySQL can have quite messy output when running queries from the ''mysql'' cli. You can terminate a statement with ''\G'' instead of '';'' to format as a list instead of table.
<code mysql>
SELECT * FROM users\G
</code>

Alternatively, you can set the pager to ''less'' which retains the table format and can be scrolled with arrowkeys.
<code mysql>
pager less -SFX
SELECT * FROM users;
nopager; -- to turn off
</code>

----
===== Create User =====
<code mysql>
CREATE USER 'kalenpw'@'localhost' IDENTIFIED BY 'p@ssword';
</code>

----
===== Grant permissions =====
replace ''*.*'' with ''schema.table'' if needed
<code mysql>
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
</code>

----
===== Basic syntax =====
**update**\\
<code mysql>
UPDATE employees SET email = 'user@domain.com' WHERE id = 123;
</code>


----
===== Many to Many Relationship Joins =====
<code mysql>
SELECT 
  a.*
FROM 
  map_ads_categories m
  JOIN ads a ON (m.ad_id = a.id)
WHERE
  m.category_id = 28;
</code>
<code mysql>
SELECT 
  *
FROM 
  map_ads_categories m
  JOIN ads a ON (m.ad_id = a.id)
  JOIN categories c ON (m.category_id = c.id)
WHERE
  c.id = 28;
</code>

----
===== Util =====
<code mysql>
-- list number of rows in each table
SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TABLE_NAME' ORDER BY TABLE_ROWS DESC;
</code>
----