Databases

Create New Database

CREATE DATABASE database;

Set default database

USE DATABASE database;

Show Databases

SHOW DATABASES;

Tables

Create

CREATE TABLE [IF NOT EXISTS] tablename(
  col1 AS STRING,
  col2 AS INT,
  col3 AS VARCHAR(255)
)

Example:

CREATE TABLE [IF NOT EXISTS] users(
  id INT NOT NULL AUTO_INCREMENT,
  name STRING,
  age INT,
  color VARCHAR(25),
  PRIMARY KEY(id)
)

INSERT

Example

INSERT INTO users (name, age, color) VALUES 
  ('Mike', 25, 'green'),
  ('Nancy', 43, 'pink'),
  ('Jacob', 33, 'red'),
  ('Larry', 54, 'purple')
;

Queries

Standard Query

SELECT col1, col2 FROM tablename WHERE col1 = ‘some string’ and col2 <= n

Users

Creating Users and Adding Permissions

Create Local User

GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Create a remote user – Unknown source IP:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Create a remote user – Known source IP:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'IP.ADD.RE.SS' IDENTIFIED BY 'password' WITH GRANT OPTION;

Plesk MySQL Login

mysql -uadmin -p`cat /etc/psa/.psa.shadow`