Wednesday, September 04, 2013

Getting Started with MySQL

This is a simple beginners guide to use MySQL in linux, from installation to querying the databases.
  1. Installation
  2. Login
  3. Databases and tables

Installation

First let's make sure our package management tools are up-to date. For that run the following commands in command line.

sudo apt-get update
sudo apt-get dist-upgrade
Once it finishes update and upgrading, we can install MySQL with following command.
sudo apt-get install mysql-server mysql-client
This will take a moment to install and now we are ready to go. 

Login

At first start up MySQL server is not set up with a password for root and we can login with,
mysql -u root -p 
If we are setting the password for the first time we can use following to set-up,
mysqladmin -u root -p NEWPASSWORD   
If we want to change a previously set password following command can be used,
mysqladmin -u root -p'oldpassword' password newpassword

Databases and Tables

First we should login to MySQL server with,
mysql -uroot -p<password>
Then it will point us to mysql console as follows where we can run queries,
mysql>
To see the available databases,
show databases;

To see the available tables inside a database,
use <database_name>;
show tables;
To see the field formats of the table,
describe <table_name>; 
To delete database/tables,
drop database if exists <database_name>  (for a table use table name)
Just like that, we can also run SQL queries like "SELECT * FROM user;" which will print the result in console.

If we have an .sql script to set up the databases or tables, we can just run it with,

source <path_to_script_file>;


If we want to import a large database then following is recommanded,
mysql -u root -p database_name < database_dump.sql
Cheers!

No comments :

Post a Comment