MySQL From Command line

If you have set up or you are going to use MySQL with SSH for example not via PHPMyadmin open your terminal and type

mysql -u username -p

Which means go to mysql, after -u put your username and -p is password, but do not enter your password after that it is going to ask it in the next prompt.

Enter your password (if you are using Putty press SHIFT + INSERT to paste)

Create new database:

CREATE DATABASE Niki_Aanitteet;

To select database you want to use insert

use databasename;

to see what tables you have type

show tables;

To see what you have in the tables type

select * from tablename;

As you can see it works with same commands as phpmyadmin. If you want to add a table here is my example code:

CREATE TABLE Niki_Aanitteet(
id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
artisti VARCHAR(255) NOT NULL,
levyn_nimi VARCHAR(255) NOT NULL
) ENGINE=Innodb DEFAULT CHARSET=latin1;

And to insert some data into the tables:

INSERT INTO Niki_Aanitteet(artisti, levyn_nimi) VALUES('PSY', 'Gangnam Style');
INSERT INTO Niki_Aanitteet(artisti, levyn_nimi) VALUES('Train', 'Drive By');
INSERT INTO Niki_Aanitteet(artisti, levyn_nimi) VALUES('Rihanna', 'Where Have You Been');

To see field, type, null, key, default, extra information type:
describe Niki_Aanitteet;

Create new user for your database and grant all privileges:

CREATE USER 'shnigi'@'localhost' IDENTIFIED BY 'passwordhere';

GRANT ALL PRIVILEGES ON * . * TO 'shnigi'@'localhost';

Explained bit better here: https://www.digitalocean.com/community/articles/how-to-install-wordpress-with-nginx-on-ubuntu-12-04

create database testdatabase;

create user 'folio'@'localhost' identified by 'password123';

grant all privileges on testdatabase.* to 'folio'@'localhost' with grant option;