CONNECTING TO A MYSQL SERVER

mysql -u [username] -p
mysql -u [username] -p [database]
mysqldump -u [username] -p [database] > data_backup.sql
quit
exit
help

CREATING AND DISPLAYING DATABASES

CREATE DATABASE zoo;
SHOW DATABASES;
USE zoo;
DROP DATABASE zoo;
SHOW TABLES;
DESCRIBE animal;

CREATING TABLES

CREATE TABLE habitat (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64)
);
CREATE TABLE animal (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64),
  species VARCHAR(64),
  age INT,
  habitat_id INT,
  FOREIGN KEY (habitat_id) REFERENCES habitat(id)
);

MODIFYING TABLES

ALTER TABLE animal RENAME pet;

ALTER TABLE animal
ADD COLUMN name VARCHAR(64);

ALTER TABLE animal
RENAME COLUMN id TO identifier;

ALTER TABLE animal
MODIFY COLUMN name VARCHAR(128);

ALTER TABLE animal
DROP COLUMN name;

DROP TABLE animal;

QUERYING DATA

SELECT species, AVG(age) AS average_age
FROM animal
WHERE id != 3
GROUP BY species
HAVING AVG(age) > 3
ORDER BY AVG(age) DESC;

SELECT city.name, country.name
FROM city
[INNER | LEFT | RIGHT] JOIN country
  ON city.country_id = country.id;

AGGREGATION AND GROUPING

SELECT COUNT(*)
FROM animal;

SELECT COUNT(name)
FROM animal;

SELECT COUNT(DISTINCT name)
FROM animal;

GROUP BY

SELECT species, COUNT(id)
FROM animal
GROUP BY species;

SELECT habitat_id, AVG(age), MIN(age), MAX(age)
FROM animal
GROUP BY habitat;

INSERTING DATA

INSERT INTO habitat VALUES (1, 'River'), (2, 'Forest');

INSERT INTO habitat (name) VALUES ('Savanna');

UPDATING DATA

UPDATE animal
SET
  species = 'Duck',
  name = 'Quack'
WHERE id = 2;

DELETING DATA

DELETE FROM animal
WHERE id = 1;

TRUNCATE TABLE animal;