Mysql

Installation

For Ubuntu:

sudo apt-get install mysql-server

Login as root

mysqladmin -u root -p

Creating Databases

Create a Database:

mysqladmin -u root -p create testdb

Or

create database testdb;

Check Databases:

mysql -u root -p
show databases;

Select a Database and check its tables:

use testdb;
show tables;

Checking Table contents:

show columns from column_name;

Creating Tables

Generic SQL syntax to create a MySQL table

CREATE TABLE table_name (column_name column_type);

 CREATE TABLE book_tbl(
 book_id INT NOT NULL AUTO_INCREMENT,
 book_title VARCHAR(100) NOT NULL,
 book_author VARCHAR(40) NOT NULL,
 submission_date DATE,
 PRIMARY KEY ( book_id )
 );

Inserting Data

 INSERT INTO book_tbl
 (book_title, book_author, submission_date)
 VALUES
 ("Learn MySQL", "Aman", NOW());

Fetching Data

SELECT * from book_tbl;
SELECT * from book_tbl WHERE tutorial_author = 'Aman';
SELECT * from book_tbl WHERE tutorial_author LIKE '%man';

Updating Data

 UPDATE book_tbl 
   SET book_title = 'Learning JAVA' 
   WHERE book_id = 3;

Sorting Data

SELECT * from book_tbl ORDER BY book_author ASC

User Administration

Creating a user:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON testdb.* TO 'guest'@'localhost' IDENTIFIED BY 'guest123';

Connecting to a database directly:

mysql testdb -u guest -pguest123

Access Control

Granting access to the Database:

This section is under construction.

Dumping Databases

Export DB

mysqldump testdb > testdb.sql -p

Import DB

mysql testdb < /var/www/html/test.com/testdb.sql -p

External Connections

The default port of the database server is 3306 If you are unable to connect to the database, try disabling the IPTables:

iptables -F

PHPMySQL

Installation

This section is under construction.