SQL 💾

Code snippets with useful stuff about SQL

Created Jan 1, 2019 - Last updated: Dec 15, 2021

Growing 🌿

sql

Code snippets with useful stuff about SQL

Users

Create a user in MySQL ¹

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON <database> . <tables> TO 'user'@'localhost';
FLUSH PRIVILEGES

Remove a user and it’s privileges in MySQL ²

SHOW GRANTS FOR 'user'@'localhost'; -- To see the user privileges
REVOKE ALL PRIVILEGES ON <database> . <tables> TO 'user'@'localhost';
DROP USER 'user'@'localhost';

Import/export databases

# Import database
mysql -u <user> -p <database> < database.sql

# Export database
mysql -u <user> -p <database> > database.sql

Alter tables ³

-- Add the AUTO_INCREMENT option
ALTER TABLE <table> MODIFY <value> INT NOT NULL AUTO_INCREMENT;

Times

If you want to add or substract two times, simply use ADDTIME and SUBTIME.

SELECT ADDTIME('14:00:00', '00:15:00');                

+---------------------------------+
| ADDTIME('14:00:00', '00:15:00') |
+---------------------------------+
| 14:15:00                        |
+---------------------------------+
1 row in set (0.00 sec)

SELECT SUBTIME ('14:00:00', '00:15:00');

+----------------------------------+
| SUBTIME ('14:00:00', '00:15:00') |
+----------------------------------+
| 13:45:00                         |
+----------------------------------+
1 row in set (0.00 sec)

It also works with dates, and you can substract hours, minutes, seconds and fractional seconds.