My SQL

MySQL, the most popular Open Source SQL database, is provided by MySQL AB. MySQL AB is a commercial company that builds its business providing services around the MySQL database. MySQL is a database management system means the structured collection of data. It also known as “Relational Database Management System” (A relational database stores data in separate tables rather than putting all the data in one big storeroom.). This adds speed and flexibility and tables are linked by defined relations making it possible to combine data from several tables on request.

Starting MySQL

1.     Open C drive.

2.     Open ‘MySQL’.

3.     Open ‘Bin’.

4.     Double click on ‘MySQL. EXE’ file.

Starting MySQL using Server

1.         Click on start.

2.         Click on run.

3.         Type ‘C:\mysql\bin’.

4.         Copy the ‘mysql.exe’ file and paste the exe file in your z: drive.

5.         Click on start/run and type ‘cmd’.

6.         Type

‘mysql –h fortunec01 –u root’ and press enter key.

To create database

syntax-

create database <databasename>;

example:-

create database saloni;

To show database

Syntax:-

show databases;

example:

show databases;

To use database

syntax:-

use databasename;

example:-

Use saloni;

To create table:-

syntax:-

create table tablename(fieldname datatype(20),fieldname datatype(20),fieldname datatype(20),fieldname datatype(20));

Example:-

create table office(staff_id varchar(20),Staffname varchar(20),address varchar(20),phone varchar(20),email varchar(20));

  

To insert record into the table:-

syntax:-

insert into tablename values('i001','ram','patan','8232','ram@yahoo.com');

example:-

insert into office values('i001','ram','patan','8232','ram@yahoo.com');

  

To Retrieve all records from table:-

syntax:-

select*from <tablename>;

example:-

select*from office;

  

To retrieve selected field only:-

syntax:-

select field1,field2 from tablename;

example:-

select name, address from office;

  

To  retrieve data using where clause:-

example:-

select*from office where name='raj':

select*from student where phone no=5539522;

  

To retrieve data using AND operator:

example:-

select*from office where name='ram'and address='patan';

To retrieve data using or operator:

example:-

select*from office where name='ram'or address='patan';

Update command:-

syntax:-Update <tablename>  set fieldname='value'  where fieldname='criteria';

example:-

Update office set phoneno='5539522' where name='raj';

 To delete all records:-

syntax:-

delete from tablename;

To delete particular record:-

example:-

delete from office where address='patan';

delete from office where name='raj';

delete from office where name='raj' or address='patan';

 To add column in existing table at last:-

syntax:-

alter table <tablename> add column <columnname> datatype;

example:-

alter table office add column gender varchar(20);

To add column in to required position at first:-

syntax:-

alter table <tablename> add column <columnname> datatype first;

example:-

alter table office add column gender varchar(20) first;

  

To add column in to required position at middle:-

syntax:-

alter table <tablename> add column <columnname> datatype after <column_name>;

example:-

alter table office add column gender varchar(20) after roll;

  

To drop column:-

syntax:-

alter table <tablename> drop column<columnname>;

example:-

alter table office drop column gender;

To rename column:-

Syntax: -

alter table <table_name> change [old field_name] [new field_name] [datatype];

e.g.

alter table staff change roll roll_no varchar(20);

To rename table:-

syntax:-

alter table<old table name>rename<newtablename>;

example:-

alter table office rename officetable;

Deleting table

Syntax: -

Drop table <table_name>;

Example:-

Drop table OFFICETABLE;

Deleting Database

Syntax: -

Drop  database < database _name>;

Example:-

Drop database ABC;