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