-
-
1:-Create Database:
—-
CREATE DATABASE `university` ;
2:- Create Table
CREATE TABLE student(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 200 ) NULL,
address VARCHAR( 200 ) NULL,
mobile VARCHAR( 256 ) NULL
)
3:-INSERT values in to single or multple table
—-
INSERT INTO emp(id,name,address,mobile)
VALUES (’1′,’arun’,'lucknow’,’98659865′)
INSERT INTO emp(name,address,mobile)VALUES
(‘arun’, ‘luckmnoe’,64654),
(‘ttt’, ‘d’, 654),
(‘tiwari’, ‘luckmnoe’,64654),
(‘aman’, ‘d’, 654)
INSERT INTO emp VALUES (9,’arun’, ‘luckmnoe’,64654)
4:-select record from multiple table
—-
SELECT * FROM `emp`
SELECT id,name FROM `emp`
SELECT * FROM emp LIMIT 3 –top
SELECT * FROM emp LIMIT 2,3 –range
5:- delete record
DELETE FROM emp WHERE id = 9
6:- Update record in multiple table
UPDATE emp SET name=’Radhe Shyam’ WHERE id=7
7:-change table name
RENAME TABLE emp TO employee;
8:-change field name of table or change structure of table
change table field name:
————————
ALTER TABLE emp CHANGE `add` `address` VARCHAR( 200 ) NOT NULL
9:-Search in all table
——–
SELECT * FROM employee WHERE
id LIKE ‘%radh%’ OR
name LIKE ‘%radh%’OR
address LIKE ‘%radh%’ OR
mobile LIKE ‘%radh%’
SELECT * FROM employee WHERE name LIKE ‘r%’ start with r
SELECT * FROM employee WHERE name LIKE ‘%r%’any where
SELECT * FROM employee WHERE name LIKE ‘%r’ end with r
10:-Replace string in all table or entire database
UPDATE wp_options a,wp_users b,wp_posts c,wp_links d SET
a.option_value = REPLACE (a.option_value,’uni-50/bseionline’,'projectdesk.us/bseionline’),
b.user_url = REPLACE (b.user_url,’uni-50/bseionline’,'projectdesk.us/bseionline’),
c.post_content = REPLACE (c.post_content,’uni-50/bseionline’,'projectdesk.us/bseionline’),
c.guid = REPLACE (c.guid,’uni-50/bseionline’,'projectdesk.us/bseionline’),
d.link_url = REPLACE (d.link_url,’uni-50/bseionline’,'projectdesk.us/bseionline’),
d.link_notes= REPLACE (d.link_notes,’uni-50/bseionline’,'projectdesk.us/bseionline’),
d.link_rss= REPLACE (d.link_rss,’uni-50/bseionline’,'projectdesk.us/bseionline’)
11-CONCAT
concat(md.mem_title,’ ‘,md.mem_first_name,’ ‘,COALESCE(md.mem_middle_name,”),’ ‘,md.mem_last_name)
as name
12-Count
SELECT COUNT(a.id) FROM mlm_member_detail a WHERE a.mem_id NOT IN(SELECT p.mem_id FROM
p_block_member_blocking_time p WHERE blocking_date BETWEEN ’2009-09-06 17:01:04′ AND ’2010-09-06
18:01:04′)
select count(id) from mlm_member_detail where mem_parent_id=$id
13-date difference
SELECT count(a.id) FROM mlm_member_detail a,mlm_member_pincode c where c.used_by=a.id AND
a.mem_sponser_id=$id AND DateDiff(CURDATE(),a.mem_doj)<731 AND DateDiff(CURDATE(),a.mem_doj)>29 AND
a.scheme_id=7 AND c.payment_mode!=4 AND a.active_status!=2
14:-Select day in Date Format
SELECT a.mem_id,b.mem_first_name,if(a.blocking_status=1,’block’,'Unblock’) as Blocking_Status
,DATE_FORMAT(a.blocking_date,’%Y-%m-%d’) as Block_Date, DATE_FORMAT(a.blocking_date,’%H:%i:%s %p’) as
Block_Time FROM p_block_member_blocking_time a,mlm_member_detail b WHERE a.mem_id=b.mem_id order by
a.blocking_date asc
15- Group Order By->Desc Asc
select a.mem_id,a.id,concat(a.mem_title,’ ‘,a.mem_first_name,’ ‘,’ ‘,a.mem_last_name) as
name,count(a.mem_sponser_id) as coun from mlm_member_detail a ,mlm_member_detail b where
b.mem_sponser_id=a.id group by b.mem_sponser_id order by coun desc
16-GROUP <> NOT Equal
select b.id,b.name,count(*) schemes from mlm_member_pincode a ,mlm_reg_scheme b,mlm_member_detail c
where b.id=c.scheme_id and c.id=a.used_by and c.mem_id <>’50001′ and a.used_by is not NULL group by
b.id
17- Like with field concat
SELECT md.id, md.mem_id AS memID,concat(md.mem_title,’ ‘,md.mem_first_name,’
‘,COALESCE(md.mem_middle_name,”),’ ‘,md.mem_last_name) as name,COUNT(md1.id) AS memCount FROM
`mlm_member_detail` md,`mlm_member_detail` md1 WHERE md1.mem_doj BETWEEN ‘”.$prevGenDate.”‘ AND
‘”.$genDate.”‘ AND md1.`mem_parent_link` LIKE CONCAT(md.`mem_parent_link`,’.%’) GROUP BY md.mem_id
ORDER BY memCount DESC
18- Date Comparison
SELECT md.id,md.mem_doj,md.mem_id AS memID,md.scheme_id,md.mem_doj, concat(md.mem_title,’
‘,md.mem_first_name,’ ‘,COALESCE(md.mem_middle_name,”),’ ‘,md.mem_last_name) as name FROM
mlm_member_detail md,mlm_member_pincode mp where mp.used_by=md.id AND md.mem_doj<=’”.$genDate.”‘
order by md.id asc
19-Inter Conection
$pli=$this->model->getAdapter()->fetchOne(“select mem_parent_link from mlm_member_detail where
id=$id”);
return $this->model->getAdapter()->fetchOne(“SELECT count(id) FROM mlm_member_detail where
mem_parent_link like ‘”.$pli.”.2%’”);
20-SUM AND
SELECT SUM(b.point_value) FROM mlm_member_detail a,mlm_reg_scheme b where a.mem_parent_link like
‘”.$pli.”.2%’ AND b.id=a.scheme_id
21- LIMIT clause
The Limit clause is used to specify the number of records to return.
The Limit clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
The syntax is as follows:
SELECT column_name(s)
FROM table_name
LIMIT number
An example in this clause
SELECT *FROM Persons LIMIT 5