mysql

mysql -u root -p
show databases;
create database test;
use test

show current user name:

SELECT CURRENT_USER();

set password for current user:
SET PASSWORD = PASSWORD(’123456’);

show all users:
SELECT User FROM mysql.user;

create a user with remote access:
mysql> CREATE USER ‘test’@‘localhost' IDENTIFIED BY ‘123456’;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'
-> WITH GRANT OPTION;
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'
-> WITH GRANT OPTION;

permission issue with the mysql folder: ERROR! MySQL server PID file could not be found!
sudo chmod -R 777 ./mysql/*

mysql
set mysql root password
/usr/local/mysql/bin/mysqladmin/ -u root password ‘yourpassword'
stop mysql
sudo /usr/local/mysql/support-files/mysql.server start

login mysql
mysql --user username --password
mysql -h ip -u user -p ( login remote database)

show database
show databases;

show tables
show tables from database;

select database
use database;

table structure
describe table;

table insert
insert into table (user, password) values ('test', 'test’);

change mysql root password in php admin
encoded password: 6BeC4xZEtDwAv7vF
no encoded password: 123456

creating a database
create database database_name;

creating a table
create table customers (name varchar(20), city varchar(20), country varchar(20));

load text file into table
LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE pet;
or -> LINES TERMINATED BY ‘\r\n’; (OS X LINE TERMINATED BY ‘\r’)

add new records one at a time
mysql> INSERT INTO pet
-> VALUES (‘Puffball’, ‘Diane’, ‘hamster’, ‘f’, ‘1999-03-30’, ‘NULL’);

bypassing foreign key constraint
SET foreign_key_checks = 0; SET foreign_key_checks = 0;