Here’s a cheat-sheet for basic database management and manipulation in MySQL.

find where mysql is installed

type -a mysql

restart

location

/usr/local/var/mysql

access mysql shell as root

mysql -u root -p

show/list databses

SHOW DATABASES;

create database

CREATE DATABASE database_name;

drop database

DROP DATABASE IF EXISTS database_name;

open existing database

USE database_name;

list all tables

SHOW tables;

what’s our table look like?

DESCRIBE potluck;

create table

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

create user

USE databaseName;
CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD';
grant CREATE,INSERT,DELETE,UPDATE,SELECT on databaseName.* to user@localhost;
grant ALL PRIVELAGES on databaseName.* to user@localhost;
set password for user = password('mysecretpassword');
set password for user@localhost = password('mysecretpassword');
grant ALL on database.* to databaseName@'%' identified by 'mysecretpassword';
exit;

add data to table

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');

alter table

ALTER TABLE potluck ADD email VARCHAR(40);
ALTER TABLE potluck ADD email VARCHAR(40) AFTER name;
ALTER TABLE potluck DROP email;

delete row

DELETE from potluck where name='Sandy';
DELETE FROM tableName.row WHERE id = 1

select from table

SELECT * FROM tablename.row
SELECT  id, name FROM tableName.row WHERE type = "Data" AND name < "X";
SELECT  COUNT(*) FROM tableName.row WHERE type = "Data";

Query from linked different tables

SELECT * FROM tableName.row INNER JOIN tableName2  ON tableName.rowName = rowName

Example:

SELECT restaurants.id, restaurants.name, addresses.*, types.name AS type_name FROM restaurant_finder.restaurants
 INNER JOIN addresses  ON restaurants.address_id = addresses.id
 INNER JOIN types ON restaurants.type_id = types.id
 WHERE addresses.city = "Munich" AND types.name  = "American"

update data

UPDATE tableName.row SET name = "NewName" WHERE id = 1

list of users

SELECT User FROM mysql.user;

show user granted permissions

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Create User

CREATE USER 'user' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'user';

Check MySQL Status

/etc/init.d/mysql status

CHeck if MySQL is Running

ps aux | grep mysql
ps wwaux | grep -i sql

Restart MySQL

sudo /etc/init.d/mysql restart

Check Open Ports

netstat -tulpen