mySQL simple commands

mysql-commands

# Basic Terminal Commands mySQL

#  IMPORTANT : add your own data or parameters, I make use of double segments [[ your variable ]]. eg. ssh root@[[ 96.172.44.11 ]] should be replaced with ssh root@888.88.88.88 where "888.88.88.88" is your value, variable etc. I have a habit of using ":::" to indicate line ending and end of paragraph, crazy I know but be warned its just how I write ::: All notes are for my own use & should you use any it's at your own risk, it's NOT a Tutorial :::

# Resources
# http://www.pantz.org/software/mysql/mysqlcommands.html

# Login to your terminal

# Show all DB's
  user@username:~$ mysql -u root -p
	show databases;

	# You will be prompted for your mySQL password
	# Below you are show default DB's on a new install

	+--------------------+
	| Database           |
	+--------------------+
	| information_schema |
	| mysql              |
	| performance_schema |
	+--------------------+
	3 rows in set (0.00 sec)

# Create databae
	mysql> create database [[ wordpress ]];

# Confirm that database is created
	mysql> show databases;
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| mysql              |
		| performance_schema |
		| wordpress            |
		+--------------------+
		4 rows in set (0.00 sec)

# Add User To [[ wordpress ]] database
	CREATE USER [[ wordpressuser ]]@localhost;

	# Set Password for new user
	SET PASSWORD FOR [[ wordpressuser ]]@localhost= PASSWORD("[[ password ]]");

	# Grant Priveleges to new User
	GRANT ALL PRIVILEGES ON [[ database ]].* TO [[ wordpressuser ]]@localhost IDENTIFIED BY '[[ password ]]';

	# Then refresh MySQL:
	FLUSH PRIVILEGES;

# Confirm Host, Users ( should look similar to below )
	select Host, User, Password from mysql.user;

	mysql> select Host, User, Password from mysql.user;
		+-----------+------------------+-------------------------------------------+
		| Host      | User             | Password                                  |
		+-----------+------------------+-------------------------------------------+
		| localhost | root             | *4759Axxxxx |
		| localhost | wordpressuser   | *E6FC1Bxxxxx |
		| 127.0.0.1 | root             | *2470Cxxxxx |
		| ::1       | root             | *2470Cxxxxx |
		| localhost | debian-sys-maint | *232B8xxxxx |
		+-----------+------------------+-------------------------------------------+
		5 rows in set (0.00 sec)

# Delete Users
	delete from mysql.user WHERE User='[[ name ]]';
	DROP USER test@localhost;

# Delete a Database
	mysql> drop database [[ database name ]];

# Drop Table
  DROP TABLE [[ yourtable ]]

# Delete Row
  DELETE from user where Name = '[[ yourvalue ]]';

# Add Table

	Now database is created. Use a database with use command:
	mysql> USE [[ database name ]];

	Now create a table called authors with name, email and id:
	mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));

	Display your table name just created:
	mysql> SHOW TABLES;

	Output:

	+-----------------+
	| Tables_in_books |
	+-----------------+
	| authors         |
	+-----------------+
	1 row in set (0.00 sec)
	Now add a data / row to table books using INSERT statement:
	mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");

	Output:

	Query OK, 1 row affected (0.00 sec)
	Add few more rows:
	mysql> INSERT INTO authors (id,name,email) VALUES(2,"Priya","p@gmail.com");
	INSERT INTO authors (id,name,email) VALUES(3,"Tom","tom@yahoo.com");

	Now display all rows:
	mysql> SELECT * FROM authors;

	Output:

	+------+-------+---------------+
	| id   | name  | email         |
	+------+-------+---------------+
	|    1 | Vivek | xuz@abc.com   |
	|    2 | Priya | p@gmail.com   |
	|    3 | Tom   | tom@yahoo.com |
	+------+-------+---------------+
	3 rows in set (0.00 sec)

#################
# Remote Access
#################
# Resource :
# ** http://endpoint.co/technology/enable-remote-access-mysql
# http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html