MySQL for Beginners !!!


helo ,

MySql database Quick Revision !!

This for Beginners

First we enter into mysql database , we need to install mysql :

Lamp for ubuntu :

sudo apt-get install lamp-server^

Lamp for fedora:

as root user –

yum install httpd mysql-server php

so ,

for Ubuntu , Debian package :

root@ubuntu:~#  apt-get install mysql-client-5.1 mysql-server-5.1
Reading package lists… Done

During installation , at end it will ask for password for mysql root

after that , Installation Successfully Completed !!!!!!

Now , you can enter to mysql through shell

sathia@ubuntu:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Now u entered into MySql 🙂

Now we can create our own User account from root of MySql

mysql>CREATE USER ‘sathia’@’localhost’ IDENTIFIED BY ‘sathia’

by this command creating account with user name ‘sathia’ with password ‘sathia’

Now u can login to mysql as your account :

sathia@ubuntu:~$ mysql -usathia -psathia

Now how to creating a new Database !!

mysql> CREATE DATABASE sathia;
Query OK, 1 row affected (0.00 sec)

Here I am created new Database with name ‘sathia’

Similarly U may need to know how to delete database “sathia”

mysql> DROP DATABASE sathia;
Query OK, 0 rows affected (0.00 sec)

Now Created new Database , now u will like to see Databasses available :

mysql> SHOW DATABASES;
+—————————+
| Database                     |
+—————————+
| information_schema     |
| mysql                          |
| phpmyadmin                |
|sathia                           |
+—————————+
5 rows in set (0.00 sec)

You need to use particular database , hence you need to select the database ,,

mysql> USE sathia;
Database changed

You should be careful that Every command should be ended with semicolan .. but there is not restriction for selecting Database

mysql> USE sathia

Now database Created and entered into database , Now Creating TABLE , I willl show you Creating a simle table with two row

mysql> CREATE TABLE add_book (name varchar(15) d.o.b varchar(15));

I created the TABLE in the name of “add_book”

mysql> DESCRIBE add_book;
+——-+————-+——+—–+———+——-+
| Field   | Type        | Null   | Key | Default |  Extra |
+——-+————-+——+—–+———+——-+
| name  | varchar(20) | YES  |     | NULL   |         |
| date  | varchar(20)   | YES  |     | NULL   |         |
+——+————-+——+—–+———+——-+
2 rows in set (0.00 sec)

Now you can load data into database from a file or you can insert it direstly ..

mysql> LOAD DATA LOCAL INFILE ‘/home/sathia/Desktop/DOB.txt’ INTO TABLE add_book;
By this you can load file into mysql , you should correcly give the path..

and in file  name and next value should be seperated by tab in each line..

mysql>INSERT INTO add_book value (‘partha’ , ’21/06/1989′);

to see the table values

mysql> SELECT* FROM add_book;
+———+————+
| name    | date       |
+———+————+
| sathia  | 27/04/1989 |
| suresh  | 03/08/1988 |
| moorthi | 30/12/1989 |
| partha  | 21/06/1989 |
——-+————+
5 rows in set (0.00 sec)

You can see the particular value in table ,

mysql>SELECT * FROM  add_book where name = “suresh” ;

same rule for deleting instead of “SELECT” ,”DELETE” is used

This is Overview of MySQL database,

and one more important is that Back up the mysql data.

BACK UP

You can back up MySql database direcly or you can back up only TABLE :

sathia@ubuntu:~$ mysqldump -usathia -psathia sathia add_book  > satbackup.sql

This will back up TABLE “add_book” under database “sathia” as “satbackup.spl” file

sathia@ubuntu:~$ mysqldump -usathia -psathia sathia > satbackup.sql

This will back up whote database named “sathia” as “satbackup.spl” file

sathia@ubuntu:~/Desktop$ mysqldump -usathia -psathia –all-databases > sathia.sql

above command , back up all database as “sathia.sql”

Getting back all the tables in mysql in another system

mysql -usathia -psathia sathia < satbackup.s

Before feeding into db ,  note that you should create same database name in that system

Regards

sathia

Advertisements

About sathia

Web developer at cloudmint
This entry was posted in programs. Bookmark the permalink.

2 Responses to MySQL for Beginners !!!

  1. Arulalan.T says:

    Its a great post to beginners of MySql

    🙂

    Keep Posting .!.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s