MySql
To see the databases
1)show databases;
to create database
2)create database cognizantstud;
to goto the database
3)use cognizantstud;
to drop database
4)drop database cognizantstud;
to create a table
5)create table student
( studid int not null auto_increment,
name varchar(30) not null,
address varchar(30) not null,
primary key(studid));
6)to insert record
insert into student values(101,'tarun','chennei');
7)to view the records
select * from student;
8)update the record
update student set address='Bangalore' where name='tarun';
9)to create a view (view will not contain any data.it will contain only the query)
create view stud as select studid,name from student;
we can do insert,update and delete of the view it will replect to the base table.
10)to see all the tables and views
show tables;
11)where condition :-
select * from student where name='tarun';
12)distinct :-(unique data will be displayed)
select distinct address from student;
13)order by:-
select * from student order by name;
select * from student order by name asc;
select * from student order by name desc;
14)group by
select address, count(*) from student group by address;
15)aggregate function
count(),sum(),avg(),min(),max(),first(),last()
i)select count(*) from student;
first record :- select name from student limit 1;
last record :-select name from student order by name desc limit 1;
16)alter table:-
alter table student add salary int not null;
to add data to salary column :-
update student set salary=45000 where studid=101;
update student set salary=35000 where studid in (102,103,104);
17)select sum(salary) from student;
18)select avg(salary) from student;
19)select min(salary) from student;
20)select max(salary) from student;
between and example:-
21)select * from student where salary between 30000 and 46000;
like % (wildcard)
22)select * from student where name like 'p%';(word starting with p will display)
select * from student where name like '%r';(word ending with r will display)
23)truncate table:-
it will delete all the data from the table.
truncate table student;
24)drop table;
it will delete the data as well as the table also.
drop table student;
25)join
create table marks
(studid int(4),
phy int(4),
chem int(4),
maths int(4),
tot int(4),avg int(4));
i)insert into marks(studid,phy,chem,maths) values(101,56,67,78);
ii)update marks set tot=(phy+chem+maths) where studid in (101,102,103);
iii)update marks set avg=tot/3 where studid in (101,102,103);
joining 2 tables:-
select a.studid,a.name,a.address,a.salary,b.phy,b.chem,b.maths,b.tot,b.avg from student a,marks b where a.studid=b.studid;
Top comments (0)