Basic MySQL

To get rid of the annoying Ctrl+c issue use mysql -u root -p --sigint-ignore

Managing Users

SELECT user, host FROM mysql.user;
CREATE USER 'user'@'server' IDENTIFIED BY 'mypassword';

SHOW GRANTS FOR 'user'@'server';
GRANT INSERT, SELECT, UPDATE ON stuff.* TO 'user'@'server';
REVOKE INSERT ON stuff.* from 'user'@'server';

DROP USER 'user'@'server';

Managing Databases

CREATE DATABASE stuff;
SHOW DATABASES;
USE stuff;
DROP DATABASE stuff;

Managing Tables

CREATE TABLE music (
  id INT unsigned NOT NULL AUTO_INCREMENT,
  artist VARCHAR(50) NOT NULL,
  title VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);
SHOW TABLES;
DESCRIBE music;

ALTER TABLE music ADD release_date DATETIME AFTER title;
ALTER TABLE music CHANGE COLUMN release_date rel_d DATE NOT NULL;
ALTER TABLE music DROP COLUMN rel_d, RENAME TO media;

DROP TABLE music;

Managing Records

INSERT INTO music (artist, title) VALUES
  ('Prefab Sprout', 'Steve McQueen'),
  ('Elbow', 'Asleep at the Back');

SELECT COUNT(*) AS total FROM music WHERE artist LIKE 'Elb%';
SELECT * FROM music WHERE release_date IS NULL ORDER BY artist;
SELECT * FROM stuff WHERE datetime > curdate() - INTERVAL 1 DAY;
SELECT * FROM temperatures.readings
  WHERE pressure=(SELECT min(pressure) FROM temperatures.readings);

UPDATE music SET title='Cast of Thousands' WHERE title='Asleep at the Back';
DELETE FROM music WHERE artist='Elbow';