Creating and Deleting Tables

-- Create a table with a primary and foreign key, 
-- with a constraint to ensure the foreign key 
-- exists in another table
CREATE TABLE TableName (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    other_id INT,
    CONSTRAINT FK_OtherTable_OtherID FOREIGN KEY (other_id)
    REFERENCES OtherTable(other_id)
);

-- Delete a table
DROP TABLE TableName;

-- If table exists, delete it
DROP TABLE IF EXISTS TableName;

Inserting Data

-- Inserting example
INSERT INTO TableName (id, name, other_id)
VALUES (1, 'Name', 1),
(2, 'Name2', 2);

Selecting Data

-- Select all columns from a table
SELECT * FROM TableName;

-- Select specific columns from a table
SELECT column1, column2 FROM TableName;

-- Select all columns from a table with a condition
SELECT * FROM TableName WHERE column1 = 'value';

-- Select all columns from a table with a condition and 
-- order by
SELECT * FROM TableName WHERE column1 = 'value' 
ORDER BY column2;

-- Select all columns from a table with a condition and 
-- order by, with a limit
SELECT * FROM TableName WHERE column1 = 'value' 
ORDER BY column2 LIMIT 10;

-- Order by ascending or descending
SELECT * FROM TableName ORDER BY column1 ASC;
SELECT * FROM TableName ORDER BY column1 DESC;

-- Joining tables
-- This will join Products and InvoiceDetails on the 
-- ProductNumber column
SELECT InvoiceDetails.InvoiceID, Products.ProductName
FROM InvoiceDetails
INNER JOIN Products 
ON InvoiceDetails.ProductNumber = Products.ProductNumber;

-- Joining tables with a condition
SELECT InvoiceDetails.InvoiceID, Products.ProductName
FROM InvoiceDetails
INNER JOIN Products 
ON InvoiceDetails.ProductNumber = Products.ProductNumber
WHERE InvoiceDetails.InvoiceID = 1;

-- Joining with a condition and order by
SELECT InvoiceDetails.InvoiceID, Products.ProductName
FROM InvoiceDetails
INNER JOIN Products 
ON InvoiceDetails.ProductNumber = Products.ProductNumber
WHERE InvoiceDetails.InvoiceID = 1
ORDER BY InvoiceDetails.InvoiceID, Products.ProductName;

-- Compute sum of a column
-- This will create a new column called LineSum that 
-- contains the sum of the LineTotal column
SELECT Invoices.InvoiceID, 
SUM(InvoiceDetails.LineTotal) AS LineSum
FROM InvoiceDetails
INNER JOIN Invoices 
ON InvoiceDetails.InvoiceID = Invoices.InvoiceID
GROUP BY Invoices.InvoiceID;

-- Use CURDATE() to create a new column with the current date
SELECT Invoices.InvoiceID, Customers.CustomerName, 
CURDATE() AS Date, Invoices.TotalDue
FROM Customers
INNER JOIN Invoices 
ON Customers.CustomerID = Invoices.CustomerID
WHERE Invoices.InvoiceID = 1;

Altering Tables

-- Add a column to a table
ALTER TABLE TableName ADD new_column INT;

-- Drop a column from a table
ALTER TABLE TableName DROP COLUMN column_name;

-- Rename a column in a table
ALTER TABLE TableName CHANGE old_column new_column INT;

-- Modify a column in a table
ALTER TABLE TableName MODIFY column_name VARCHAR(100);

-- i.e. Change an ENUM column to display a different value
ALTER TABLE TableName 
MODIFY column_name ENUM('value1', 'value2', 'value3');

-- Change name of a column
ALTER TABLE TableName CHANGE old_column new_column INT;

Dumping and Restoring Data

-- Dump a database to a file
mysqldump -u username -p database_name > file.sql

-- Dump specified tables from a database to a file
mysqldump -u username -p database_name table1 table2 > file.sql

-- Restore a database from a file
mysql -u username -p database_name < file.sql