Responsive image
Responsive image

Responsive image

To create database
syntax:
create  database  databasename;

example:-
create  database  class4;

To show database
Syntax:
show  databases;

example:
show  databases;

To use database
syntax:
use  databasename;
example:-
Use  class4;

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 show table
Syntax:
          Show   tables;
Example:
          Show  tables;

To insert record into the table:-
syntax:
        insert into tablename values(‘i001','John','Biratnagar','8232','john@gmail.com');
        insert into tablename(staff_id, staffname, address, phone, email) values('i001','John','New York','9841000000','john@gmail.com');

example:
        insert into office values('i001','John','New York','9841000000','john@gmail.com');
        insert into office (staff_id, staffname, address, phone, email) values('i001','John','Biratnagar','8232','john@gmail.com');

To Retrieve all records from table:-
syntax:
select*from   tablename;
example:
select * from   student;

To retrieve selected field only:-
syntax:
select field1,field2 from tablename;

example:
select name, address from student;

To  retrieve data using where clause:-
example:
select*from  Student   where name='John';
select*from student where phone no=01234567;

To retrieve data using AND operator:
example:
select*from   student  where   name='John'  and  address='Biratnagar';

To retrieve data using OR operator:
example:
select*from  Student  where   name='John'  or  address='Biratnagar';

IN Operator
syntax
select * from <table name> where <field name> in ('value1','value2',....);
example
select * from student where address in('Butwal','Bhaktapur','Manang');

Not IN Operator
syntax
select * from <table name> where <field name> not in ('value1','value2',....);
example
select * from student where address not in('Nawalparasi','Chitwan');

MySQL commands are used to create, store, modify and retrieve records from database. It is a structured query language that contains different commands which are required for database processing. Some commands and functions are listed below with their uses, syntax and examples.

Display record in ascending order
syntax
select * from <table name> order by <field name> asc;
example
select * from student order by first_name asc;
select * from student order by Fee asc;

Display record in descending order
syntax
select * from <table name> order by <field name> desc;
example
select * from student order by first_name desc;
select * from student order by fee desc;

Like Operator
strating with R
synatax
select * from student where <field name> like 'string'
example
select * from student where first_name like 'R%'

Ending with T
select * from student where first_name like '%T'

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;

Update command:-
syntax:-Update <tablename>  set fieldname='value'  where fieldname='criteria';
example:-12
Update office set phoneno='023339522' where name='John';
Update student set name='Peter',address='Callifornia',post='CEO',gender='Male' where id=4;

aggregate function
sum()
syntax
select sum(fieldname) as ' column name' from <table name>
example
select sum(fee) as 'Total Fee' from student
Min()
syntax
select min(fieldname) as ' column name' from <table name>
example
select Min(fee) as 'Minimum Fee' from student

Max()
syntax
select max(fieldname) as ' column name' from <table name>
example
select max(fee) as 'Minimum Fee' from student

Average()
syntax
select avg(fieldname) as ' column name' from <table name>
example
select avg(fee) as 'Average Fee' from student

Count()
syntax
select count(fieldname) as ' column name' from <table name>
example
select count(*) as 'Number of Records' from student

Join table
This is the advanced feature of MySQL database software that allows user to connect the values of two tables. The use of Join table feature is explained here with tables, syntax and example.
Create two tables like this:
       1.   news_writer                       2. news                         


News_writer

News_qty

John

2

Peter

Null

Maria

1

Jennifer

4

News_id

News_writer

Gender

1

Maria

Female

2

Jennifer

Female

3

John

Male

4

Peter

Male

Query:
Select a.news_id, a.news_writer, a.gender, b.news_qty from news  a, news_writer  b where a.news_writer=b.news_writer;

To rename column:
Syntax:
alter table <table_name> change [old field_name] [new field_name] [datatype];
e.g.
alter table staff change roll_no Registration_no varchar(20);

To drop column:
syntax:
alter table <tablename> drop column<columnname>;
example:
alter table office drop column gender;

To rename table:
Syntax:
alter table<old table name>rename<newtablename>;
example:
alter table office rename officetable;

To delete particular record:
example:
delete from office where address='Real Madrid';
delete from office where name='Lusifer';
delete from office where name='Lusifer' or address='Real Madrid';

To delete all records:
syntax:
delete from tablename;

Deleting table
Syntax:
Drop table <table_name>;
Example:
Drop table OFFICETABLE;

Deleting Database
Syntax:
Drop  database < database _name>;
Example:

Drop database OFFICETABLE;



Post Your Comment Here


Related Topics/Codes