• Create a new User & grant permissions

CREATE USER 'exampleUser'@'localhost' IDENTIFIED BY 'SomeHardToGuessPassword';
GRANT ALL ON databaseName.* TO 'exampleUser'@'localhost';

• Connect to MySql
  • mysql -u exampleUser -p
• Change a Password
  • SELECT host, user FROM mysql.user;
• List Databases
  • $ show databases;
• Create a Database
  • $ create databasedatabaseName;
• Select a Database to Use
  • $ use databaseName;
• List Tables
  • $ show tables;

• Create a Table

  CREATE TABLE tableName(
  id int NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY (id)
  );
  

• Table Description

  • $ describe tableName;

• Select Query

  • $ SELECT column1, column2…FROM TableName;
  • $ SELECT \* FROM TableName;

• Insert

  INSERT INTO TableName
  ( column1, column2, … )
  VALUES
  ('valueForCol1', 'valueForCol1', … );
  

• Update

  • $ UPDATE tableName SET fieldName = 'value for column' WHERE id=primaryKey;

• Delete

  • $ DELETE FROM tableName WHERE id = primaryKey;

• Create a .sql file with the contents of a database

  • $ mysqldump -u databaseUser -p Chirper > chirper.sql;

Stored Procedures

• Create a stored procedure

DELIMITER$$ 
CREATE PROCEDURE YourProcedure()
BEGIN
    SELECT * FROM tableName;
END$$
delimiter; # don't forget to reset your delimiter

• Create a Stored Procedure with parameters

delimiter$$
CREATE PROCEDURE InsertCourse(
  p_name VARCHAR(30),
  p_description VARCHAR(255)
  )
BEGIN
INSERT INTO Courses (
  name,
  description
) VALUES (
  p_name,
  p_description
);
SELECT LAST_INSERT_ID() as id;
END$$

delimiter; # don't forget to reset your delimiter

• Calling a procedure

  • CALL YourProcedure();