MySQL for beginners

Install mysql-server and mysql-client  using the command for example in ubuntu sudo apt-get install mysql-server mysql-client, this will prompt for admin password, provide the password and remember the password.

logging into the mysql shell for id “root” and password “password"

mysql -u root -ppassword

To list the available databases

> show databases; 

To create database

> create database if not exists django_tut; 

Selecting database

> use django_tut

To delete database

> drop database if exists django_tut;

To list down all the tables in the database

> Show Tables;

Creating a table

> create table if not exists ex_table( p_id INT, P_code varchar 20)

verifying the table

>describe ex_table;

deleting the table “ex_table1"

> drop table if exists ex_table1;

Inserting a records in the table ex_table

> insert into ex_table value (100, “hundred”)

> insert into ex_table value (200, “two hundred”)

Inserting a records in the table ex_table1

> insert into ex_table value (100, “hundred”)

> insert into ex_table value (300, “three hundred”)

Updating a record
>update ex_table set p_code="HUNDRED" where p_id=100

Querying the table

> select * from ex_table;

Querying for a particular column in the table

> select p_id from ex_table;

Querying based on condition

> select p_id from ex_table where p_id =100;

Alter table adding a column in the table

> alter table ex_table add column p_description varchar(50);

>describe table;

Joining a table

> select * from ex_table join ex_table1 on ex_table.p_id = ex_table1.p_id;

taking backup (below command prompt for password)

$mysqldump -u root -p —databases django_tut > ~/django_tut_bkup.sql

restoring backup(below command prompt for password)

$mysql -u root -p django_tut < ~/django_tut_bkup.sql