MySQL cheat-sheet

This page lists down quick reference to mysql commands.

Open My SQL command line client and login as root user.

Admin Commands


back to top

Login as root via Mysql client

-- Local connection
mysql -u root -p

-- Remote connection
mysql -h hostname -u root -p

Table commands

-- Show all the tables
show tables;

-- To see table field format
describe table_name

Create new user

mysql -u root -p
use dbname
INSERT INTO user (Host,User,Password) VALUES('%','user_name',PASSWORD('password'));
flush privileges;

Manage users

-- Update password
mysqladmin -u username -h hostname.blah.org -p password 'new-password'

-- Change a users password from MySQL prompt
mysql -u root -p
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('replacepasswordhere');
flush privileges;

-- Set a root password if there is no root password
mysqladmin -u root password newpassword

-- Update a root password
mysqladmin -u root -p oldpassword newpassword

-- Allow the user to connect to the server from localhost
mysql -u root -p
use dbname;
grant usage on *.* to bob@localhost identified by 'passwd';
flush privileges;

-- Grant user privileges for a db
mysql -u root -p
use dbname;
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
flush privileges; 

-- Update priveleges
UPDATE [table name] SET Select_priv = 'N',Insert_priv = 'Y',Update_priv = 'Y' where [field_name] = 'user';

Manage database

-- Dump all databases for backup.
mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

-- Dump one database for backup.
mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

-- Dump a table from a database
mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

-- Restore database (or database table) from backup
mysql -u username -ppassword databasename < /tmp/databasename.sql

Create new database

-- 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 databasename

Developer Commands


back to top

DDL examples

-- Add a new column
ALTER TABLE [table name] ADD column [new columnName] varchar (20);
	
-- Change column name
ALTER TABLE [table name] CHANGE [old columnName] [new columnName] varchar (50);

-- Delete column
ALTER TABLE [table name] DROP column [columnName];
	
-- Make column unique
ALTER TABLE [table name] ADD UNIQUE ([columnName]);

-- Make column bigger
ALTER TABLE [table name] MODIFY [columnName] VARCHAR(3);

-- Delete unique from the table
ALTER TABLE [table name] DROP index [colmn name];

-- Add/drop foreign key
ALTER TABLE tableName ADD FOREIGN KEY (columnName) REFERENCES tableName (columnName)
ALTER TABLE tableName DROP FOREIGN KEY constraintName

Misc examples


-- How to run a sql script
source c:/amazon/load_products.sql

-- Running sql script via batch mode
 mysql -u root -p databasename < c:\amazon\load_products.sql

-- Load a CSV file into a table
LOAD DATA INFILE '/tmp/products.csv' replace INTO TABLE [table name] 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' (field1,field2,field3);

-- OR, you can use mysqlimport utility

-- Export (windows)
SELECT * FROM products INTO OUTFILE 'c:/amazon/products.csv' 
         COLUMNS TERMINATED BY ','
         LINES TERMINATED BY '\r\n';

-- Export (mac)
SELECT * FROM products INTO OUTFILE '~/amazon/products.csv'
         COLUMNS TERMINATED BY ',';
         

Create Table example

CREATE TABLE IF NOT EXISTS products (
         productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         Code  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 11111.99,
         PRIMARY KEY  (productID)
);

-- Insert a row with all the column values
INSERT INTO products VALUES (1001, 'Shoes', 'Nike', 5000, 100.01);
INSERT INTO products VALUES (1002, 'Shoes', 'Reebok', 1000, 500.99);

-- 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
          (1, 'Reebok partners', '15045678'), 
          (2, 'Nike Ventures', '15045678');


-- 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 = 1;

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)
       );

Indexing Example

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
);

Inner Join Example

SELECT products.name AS `ProductName`, price, suppliers.name AS `SupplierName`
      FROM products_suppliers 
        JOIN products  ON products_suppliers.productID = products.productID
        JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
      WHERE price < 2000;

-- Define aliases for tablenames too      
SELECT p.name AS `ProductName`, s.name AS `SupplierName`
       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 = 'Reebok';
       
-- Using WHERE clause to join (legacy and not recommended)
SELECT p.name AS `ProductName`, s.name AS `SupplierName`
       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');          

Query example

SELECT name, price FROM products WHERE name LIKE 'myproduct%';

-- Count example
SELECT COUNT(*) AS `Count` FROM products;
SELECT productCode, COUNT(*) FROM products GROUP BY productCode;

-- Aggregate functions
SELECT MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity)
       FROM products;

-- Cast example
SELECT productCode, MAX(price), MIN(price),
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
              CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
              SUM(quantity)
       FROM products
       GROUP BY productCode;
  
-- Group by example
SELECT productCode, COUNT(*) AS count 
       FROM products 
       GROUP BY productCode
       ORDER BY count DESC;

-- Having example
SELECT
          productCode AS `ProductCode`,
          COUNT(*) AS `Count`,
          CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`
       FROM products 
       GROUP BY productCode
       HAVING Count >=3;
          -- CANNOT use WHERE count >= 3

Update examples


 UPDATE products SET quantity = quantity - 100 WHERE name = 'Reebok';
 UPDATE products SET quantity = quantity + 50, price = 111.50 WHERE name = 'Nike';
 

View example


-- create view from multiple tables

DROP VIEW IF EXISTS supplier_view;

CREATE VIEW supplier_view
       AS
       SELECT suppliers.name as `Supplier Name`, products.name as `Product Name`
       FROM products 
          JOIN suppliers ON products.productID = products_suppliers.productID
          JOIN products_suppliers ON suppliers.supplierID = products_suppliers.supplierID;
                

References


# Reference
1 http://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html
2 https://www.pantz.org/software/mysql/mysqlcommands.html
3 https://gist.github.com/sunieldalal/388c4c9473c80103ec29

Version History


Date Description
2015-07-01    Initial Version
2016-03-04    updated sql for view