Prerequisites: Install Mysql DB. Opem My SQL command line client and login as root user.
-- Create database for application
CREATE DATABASE app_dev;
-- create user for the application
CREATE USER 'app_user' IDENTIFIED BY 'p@ssw0rd';
-- Grant access
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON app_dev.* TO 'app_user';
-- switch to dev database
USE app_dev;
-- Test login by ruuning the command line
mysql -u app_user -p app_dev###Backup and Restore Database
Back Up
mysqldump -u root -p --databases app_dev > "c:\myProject\backup_app_dev.sql"Restore
source "c:\myProject\backup_app_dev.sql"###Create Table example
CREATE TABLE IF NOT EXISTS products (
productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
productCode CHAR(3) NOT NULL DEFAULT '',
name VARCHAR(30) NOT NULL DEFAULT '',
quantity INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
PRIMARY KEY (productID)
);
-- Insert a row with all the column values
INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
INSERT INTO products VALUES (1002, 'PENCIL', 'Pencil', 5000, 1.23);
INSERT INTO products VALUES (1003, 'PEN1', 'Pen 1', 5000, 1.23);
INSERT INTO products VALUES (1004, 'PEN2', 'Pen 2', 5000, 1.23);
-- ONE TO MANY RELATIONSHIP -- A supplier can supply many products
CREATE TABLE suppliers (
supplierID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL DEFAULT '',
phone CHAR(8) NOT NULL DEFAULT '',
PRIMARY KEY (supplierID)
);
INSERT INTO suppliers VALUE
(501, 'ABC Traders', '88881111'),
(502, 'XYZ Company', '88882222'),
(503, 'QQ Corp', '88883333');
--Instead of deleting and re-creating the products table,
-- we shall use "ALTER TABLE"
-- to add a new column supplierID into the products table.
-- Add column in products table
ALTER TABLE products
ADD COLUMN supplierID INT UNSIGNED NOT NULL;
-- Add a foreign key constraint
ALTER TABLE products
ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
UPDATE products SET supplierID = 501;###Create Table example - Many to Many Relationship
CREATE TABLE products_suppliers (
productID INT UNSIGNED NOT NULL,
supplierID INT UNSIGNED NOT NULL,
-- Same data types as the parent tables
PRIMARY KEY (productID, supplierID),
-- uniqueness
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
);###Create Table example - One to One Relationship
CREATE TABLE product_details (
productID INT UNSIGNED NOT NULL,
-- same data type as the parent table
comment TEXT NULL,
-- up to 64KB
PRIMARY KEY (productID),
FOREIGN KEY (productID) REFERENCES products (productID)
);CREATE [UNIQUE] INDEX indexName ON tableName(columnName, ...);
ALTER TABLE tableName ADD UNIQUE|INDEX|PRIMARY KEY indexName (columnName, ...)
SHOW INDEX FROM tableName;
-- example
CREATE TABLE employees (
emp_no INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender ENUM ('M','F') NOT NULL,
birth_date DATE NOT NULL,
-- The 'Date' type contains a date value in 'yyyy-mm-dd'
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
-- Index built automatically on primary-key column
);
CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
-- Index built automatically on primary-key column
UNIQUE INDEX (dept_name)
-- Build INDEX on this unique-value column
);
DESCRIBE departments;
SHOW INDEX FROM departments \G
-- Many-to-many junction table between employees and departments
CREATE TABLE dept_emp (
emp_no INT UNSIGNED NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
INDEX (emp_no),
-- Build INDEX on this non-unique-value column
INDEX (dept_no),
-- Build INDEX on this non-unique-value column
FOREIGN KEY (emp_no) REFERENCES employees (emp_no)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (emp_no, dept_no)
-- Index built automatically
);SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name`
FROM products_suppliers
JOIN products ON products_suppliers.productID = products.productID
JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
WHERE price < 0.6;
-- Define aliases for tablenames too
SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
FROM products_suppliers AS ps
JOIN products AS p ON ps.productID = p.productID
JOIN suppliers AS s ON ps.supplierID = s.supplierID
WHERE p.name = 'Pencil 3B';
-- Using WHERE clause to join (legacy and not recommended)
SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.productID = ps.productID
AND ps.supplierID = s.supplierID
AND s.name = 'ABC Traders';
-- select with subquery examples
SELECT suppliers.name from suppliers
WHERE suppliers.supplierID
NOT IN (SELECT DISTINCT supplierID from products_suppliers);
-- INSERT|UPDATE|DELETE with Subquery
INSERT INTO products_suppliers VALUES (
(SELECT productID FROM products WHERE name = 'Pencil 6B'),
(SELECT supplierID FROM suppliers WHERE name = 'QQ Corp'));
DELETE FROM products_suppliers
WHERE supplierID = (SELECT supplierID FROM suppliers
WHERE name = 'QQ Corp'); Reference & Credits:
http://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html