query..

DATABASE NAME:  testing
TABLE NAMES:  area , category , subcat
VIEW NAME: currentc

FOR STORED PROCEDURE DATABASE NAME: proc
FOR TRIGGER DATABASE NAME: trig

INSERT QUERY:
insert into test1 values (6,’sd’,’cd’,11);

insert into category values (1,52,12,10)

SELECT QUERY:
select * from category

select count(*) as number from table_name where field = ‘value’;
// it will return number of rows..

select sum(value) from subcat;

SELECT DISTINCT QUERY:
select distinct name from subcat

WHERE CLAUSE:
select * from subcat where cost1 > 80
select * from subcat where name = ‘c2’

AND CLAUSE:
select * from subcat where name = ‘c2’ and cost1 = 85

OR CLAUSE:
select * from subcat where name = ‘c2’ or cost1 = 85

ORDER BY:
select * from subcat order by cost1
select * from subcat order by cost1 desc

UPDATE QUERY:
update subcat set cost1 = 52, name = ‘c4’ where id2 = 3

DELETE QUERY:
delete from subcat where cost1 = 52

delete from category; delete all the data

LIMIT QUERY:
select * from area limit 2     ; to display records in limited range   ; always use wildcard with limit query

LIKE QUERY:     like query is an alias for where
select  *from area where lname like ‘ v% ‘;   select all with starting with v
select  *from area where lname like ‘ %v’      select all with ending v

select * from area where fname like ‘_a’  // start with any character followed by a  // _ use for any character

select * from area where fname like ‘[as]%’  // for character set

IN CLAUSE:
select * from area where fname in (‘s’,’sa’);

BETWEEN QUERY:
select  * from area where lname between ‘v’ and ‘vo’   // for character
select * from area where age between 21 and 26   // for number

ALIASES:
select a.lname, c.cost from area as a, category as c where a.id = 2    // use for shortname of tables using as

UNION:
select name from category union select name from subcat // u can use with different coiumn name also
select name from category union all select name from subcat

SELECT INTO:
select * into table ssubcat from subcat; // to create another same copy of table
select * into table ssubcat in ‘newdb.mdb’ from subcat // to create another same copy of table in new db

ALTER QUERY
alter table `testing`.`category` add column’  ‘name’  VARCHAR(45)  after ‘quantity’;

alter table area drop index gender

ALTER TABLE `testing`.`subcat` ADD COLUMN `name` VARCHAR(50)  AFTER `value`;
nsert into subcat values (4,95,55,’cat4′)
rename table test1 to area;

CRREATE QUERY:
create database testing

create table ‘testing’ , ‘category’ (
‘id1’ int auto_increment,
‘cost’  varchar(45),
‘value’ varchar(45),
‘quantity’ varchar(45),
primary key(‘id1’)
)
unique key can be more then 1
primary key can’t  be more then 1
foriegn key use to link b/w to tables // in  one table pk will be foreign key for another table

INDEX:
create index sn on subcat (name)  // u can’t see index just useful for fast searching

DROP QUERY:
drop table area; // u can also use drop for dropping database and also for index

VIEW:  // its one kind of virtual table use to store data
to create:
create view Currentc as select cost1,name from subcat where id2 = 2

to display:
select * from Currentc

to drop:
drop view Currentc

DATE : format is (yy or yyyy- mm – day)
select datediff(‘2008-11-20′,’2008-11-15’) as diffate   // O/P = 5 //

JOIN QUERY: // to use it one column must common b/w to tables and use on keyword instead of where.  // first table will be left and second will be right
select category.cost, subcat.cost1 from category join subcat on category.name=subcat.name;  // join will work same as inner join // most time use with on keyword

select category.cost, subcat.cost1 from category left join subcat on category.name=subcat.name;

select category.cost, subcat.cost1 from category right join subcat on category.name=subcat.name;

AGGREGATE FUNCTIONS:
select avg(value) from subcat // to calculate average
select first(name)  from subcat  // return first
select last(name)  from subcat
select max(cost1)  from subcat
select min(cost1)  from subcat
select sum(cost1)  from subcat

GROUP BY QUERY:
select cost1,sum(cost1) from subcat group by name;

TRIGGERS:
//to use define or then to use it anywhere use @  keyword // @ this keyword is used for accessing variable
create table account (acct_num INT, amount DECIMAL(10,2)); // for creating table

create trigger  ins_sum before insert on account for each row set @sum = @sum + NEW.amount; // mysql doesnt support multiple triggers

after creting trigger for checking it do……………
set @sum = 0;
insert into account values(138,16.98),(148,2937.50),(98,-200.00);
select @sum as ‘Total amount inserted’;

//for dropping:
drop trigger test.ins_sum;

//for getting list of triggers
Show Triggers From databse_name

STORED PROCEDURE:

//to declaer,display,set  any variable…
DECLARE var1 varchar(15);
select count(*) into var1 from test1;
select @var1;

//to declaer,display,set  any variable.
SET @a = 10;
SELECT @a;

//how to pass table name and column name as argument see here
CREATE PROCEDURE varstm(IN test1 CHAR(64), IN age CHAR(64)
BEGIN

SET @s = CONCAT(‘SELECT AVG(‘ , age , ‘) FROM ‘ , test1);
#select @s;
PREPARE stmt FROM @s;
EXECUTE stmt;

//we use cursor for sql query statment with stored procedure and also we can save that value to particular variable

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc`.`curs` $$
CREATE PROCEDURE `proc`.`curs` (in id int , out var varchar(10))  //here id is work as input variable and var work as output variable
BEGIN

DECLARE cur1 CURSOR FOR SELECT stu_name from stu_table where stu_id =id;

OPEN cur1;

Fetch cur1 into var;

close cur1;

//to call cursor and display value we use

call curdemo(2, name);
select  @name;

//to create it

create procedure p1