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: CREATE USER test IDENTIFIED BY 'test123';

Grant access to a DB to a user created on fly: 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

Allows the user my_user to connect to the database. grant usage on. to my_user@localhost identified by 'my_password';

Allows the user my_user from the localhost full access to my_database grant all privileges on my_database.* to my_user@localhost;

= Access Control =

Granting access to the Database:

= 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