MySQL get start commands

first start the [MySQL Command Line Client] under the MySQL server after install
use root password to login

mysql --user=[USERNAME] --password=[PASSWORD]

or connect to remote host

mysql -h [HOSTNAME or IP] --user=[USERNAME] --password=[PASSWORD]

Find out what DB and tables are on the system

To show what databses on the system use

SHOW DATABASES;

To select a database to use

USE [databasename];

To show all the tables in the database use

SHOW TABLES;

To show the structure of the table

DESC [table name];
OR
SHOW COLUMNS FROM [table name];

To create your own database

CREATE DATABASE [databasename];
then you need to point to use that new DB
USE[databasename];

Create table

To create a table to store data

CREATE TABLE tbl_user(userID int(10), firstName char(20), lastName char(20), userName char(20), password char(50));

Alter table

ALTER TABLE [table name] ADD DateOfBirth date;

Change Column Data Type

ALTER TABLE [table name] ALTER COLUMN DateOfBirth year;

Drop Column

ALTER TABLE [table name] DROP COLUMN DateOfBirth;

Rename table

RENAME TABLE [table name] TO [new table name];

Insert values(data) into the new table

INSERT INTO tbl_user VALUE(1, "Peter", "Bitshop", "Peter.B", "12345678");

or

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Show the values in the table

SELECT * FROM tbl_user;

Delete a set of value from the table

DELETE FROM tbl_user WHERE userID = 1;

or

DELETE FROM tbl_user WHERE firstName = "Peter";

Truncate Table

TRUNCATE [table name];

Update value for a field

UPDATE tbl_user SET password="88888888", firstName = "Walter" WHERE userID=1;

Drop Table

DROP TABLE [table_name];

Drop Database (Schema)

DROP DATABASE [databasename];

Reset User Password

SET PASSWORD FOR '[USERNAME]'@'[HOST]' = PASSWORD('[new password]');

TIMESTAMP Column

CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

ALTER column to Timestamp column

ALTER TABLE `[table name]` 
    CHANGE COLUMN `[column]` `[column]` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

Update timestamp field

UPDATE [table name] SET
	time = now()
	WHERE [id] = [id];