MySql query used in projects

Mysql Query Helps you in Project

1. Copy data of one table to another 
CREATE TABLE student2 SELECT * FROM student;
    or

CREATE TABLE student2 LIKE student;
INSERT student2 SELECT * FROM student;

2. Copy structure of one table onto another but not data 
CREATE TABLE student2 SELECT * FROM student WHERE 1=0


3. Insert data into table form another table on id basis  

INSERT INTO student2 SELECT * FROM student WHERE id = 2;

4. Update the record in table

Update students set first_name=’Sudhir’ where id=2;

5. Create table query

CREATE TABLE IF NOT EXISTS `cities` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`country_id` int(10) unsigned DEFAULT NULL,
`state_id` int(10) unsigned DEFAULT NULL,
`name` varchar(100) NOT NULL DEFAULT '',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `country_id` (`country_id`)
) ENGINE=InnoDB

6. Delete Command
DELETE FROM contacts WHERE last_name = 'Pandey';
delete the first 2 records 
DELETE FROM contacts WHERE last_name = 'Pandey' ORDER BY contact_id DESC LIMIT 2;

7. Alter Command add column
ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;
Add column enum type
ALTER TABLE `users` ADD `level` ENUM('Student','Graduate','Entry Level','Middle Management','Executive') NULL DEFAULT NULL AFTER `photo`;
or by GUI
Choose name 'level' type 'ENUM' in length " 'Student','Graduate','Entry Level','Middle Management','Executive' " Default 'NULL'

Modify the column called last_name to be a data type of varchar(50) 
ALTER TABLE contacts MODIFY last_name varchar(50) NULL;
Drop the column called contact_type
ALTER TABLE contacts DROP COLUMN contact_type;
Rename the column called contact_type to ctype. 
ALTER TABLE contacts CHANGE COLUMN contact_type ctype varchar(20) NOT NULL;
Rename table 
ALTER TABLE contacts RENAME TO people;

8. Some select query

Last 10 days records: 
SELECT * FROM dt_table WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 10 DAY ) AND CURDATE( )
Last one month records: 
select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
Fetch record after 2 days of created:
SELECT * FROM users AS User WHERE DATE_FORMAT(DATE_ADD(`created`, INTERVAL 2 DAY),'%Y-%m-%d')= DATE_FORMAT(NOW(),'%Y-%m-%d') AND email_activation_token!=''
Fetch record after interval:
SELECT * FROM accountplans where recurring_flag='0' and plan_id ='2' and DATE_FORMAT(plan_end,'%Y-%m-%d') >= CURDATE() and DATE_FORMAT(plan_start,'%Y-%m-%d') = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 DAY), '%Y-%m-%d')
Fetch record before 2 days of plan end:
SELECT * FROM accountplans where recurring_flag='0' and plan_id ='2' and DATE_FORMAT(plan_end,'%Y-%m-%d') >= CURDATE() and DATE_FORMAT(DATE_SUB(`plan_end`, INTERVAL 2 DAY),'%Y-%m-%d')= DATE_FORMAT(NOW(),'%Y-%m-%d')

9. insert query current date
INSERT INTO `layouts` (`user_id`, `defaultname`, `name`, `active`, `sort`,`created`,`modified`) VALUES ('".$id."', 'Objective', 'Objective', '1', '1',NOW(),NOW())

10. Like query
selects all with a name starting with the letter "s":
SELECT * FROM employees WHERE name LIKE 's%';
selects all with a name ending with the letter "s":
SELECT * FROM employees WHERE name LIKE '%s';
selects all with a name pattern with the letter "s":
SELECT * FROM employees WHERE name LIKE '%s%';
selects all with a name followed with the letter "s":
SELECT * FROM employees WHERE name LIKE '_s';

The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":

SELECT * FROM Customers WHERE City LIKE 'L_n_on';

The following SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[bsp]%';
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
SELECT * FROM Customers WHERE City LIKE '[a-c]%';
The field table.name contains 'Sudhir 2100' and with the following query
SELECT `name` FROM `table` WHERE `name` REGEXP 'Sudhir.+2100'
Also you can use:
SELECT `name` FROM `table` WHERE `name` LIKE '%Sudhir%' AND `name` LIKE '%2100%'

Find cities containing characters A, B or R at any position:
SELECT name FROM cities WHERE name RLIKE 'A|B|R';
-- or
SELECT name FROM cities WHERE name REGEXP 'A|B|R';
-- or using LIKE
SELECT name FROM cities WHERE name LIKE '%A%' OR name LIKE '%B%' OR name LIKE '%R%'

    -- Find cities containing characters A, B or R at any position
SELECT name FROM cities WHERE REGEXP_LIKE(name, 'A|B|R', 'i');

   -- Find cities that starts with characters A, B or R
SELECT name FROM cities WHERE REGEXP_LIKE(name, '^A|^B|^R', 'i');

SELECT DISTINCT SALARY FROM TABLENAME ORDER BY DESC SALARY LIMIT 3,1;

How will retrieve nth level categories from one query in mysql ?
SELECT * FROM (SELECT * FROM CATEGORY LIMIT n) AS TEMP LIMIT n-1,1;

To get offices that does not locate in USA and France                   
SELECT officeCode, city, phone FROM offices WHERE country NOT IN ('USA','France')

GROUP By
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount,SUM(priceeach) AS total FROM orderdetails GROUP BY ordernumber

if you want to find order whose total amount is greater than $60K
SELECT orderNumber, customerNumber, status, shippedDate
FROM orders WHERE orderNumber IN (
SELECT orderNumber FROM   orderDetails GROUP BY orderNumber
HAVING SUM(quantityOrdered * priceEach) > 60000)  

we can find which order has total sales greater than $1000
SELECT ordernumber,SUM(quantityOrdered) AS itemsCount,SUM(priceeach) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 1000

Suppose we want to find all orders that has shipped and has total sales greater than $1500
SELECT a.ordernumber, SUM(priceeach) total, status FROM orderdetails a INNER JOIN orders b ON b.ordernumber = a.ordernumber GROUP BY ordernumber HAVING b.status = 'Shipped' AND total > 1500;

How to get 2nd highest salary of employee, if two employee may have same salary?
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1 
select salary from employee group by salary order by salary limit 1,1

Alternate way without limit

SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

How to find duplicate email records in users table?
SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
INNER JOIN (
SELECT email FROM users GROUP BY email HAVING count(id) > 1
) u2 ON u1.email = u2.email;
Delete duplicate records from a table
Delete FROM knownlanguages a inner join knownlanguages b
    on a.userID=b.userID and a.LanguageId < b.LanguageId

1) If you want to keep the row with the lowest id value:
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
2) If you want to keep the row with the highest id value:
DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

The Question was how would you calculate most in?

Student:     
=====================
|student_id | student_Name  
|-------------------------------   
| 1           | abc                       
|-------------------------------   
| 2           | xyz                       
|-------------------------------   
| 3           | etc                        
|------------------------------  
Student in

===================
| subject_id | student_id 
|-------------------------   
| 1        | 1                       
|-------------------------    
| 2        | 1                       
|-------------------------    
| 3        | 2                      
|-------------------------    

SELECT   student.* FROM     student JOIN student_in USING (student_id)
GROUP BY student_id HAVING   COUNT(*) = ( SELECT   COUNT(*) FROM student_in GROUP BY student_id ORDER BY votes DESC LIMIT    1 )
OR
SELECT count(*) mostIn, c.student_Name FROM student c INNER JOIN student_in cv ON c.student_id = cv.student_id GROUP BY cv.student_id ORDER BY mostIn desc LIMIT 1

Import mysql query result in CSV
SELECT id,firstname,lastname,gender, address FROM excelimportentries INTO OUTFILE 'C:/excelimportentries.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ',' 

Find 2nd highest salary when salary has duplicate value
SELECT Salary FROM users WHERE Salary = (SELECT DISTINCT(Salary) FROM users ORDER BY Salary DESC LIMIT 1,1) 

Multiple Count condition in single query
SELECT patron_id, count(id) as played, sum(case when media_type = 'img' then 1 else 0 end) ImageCount, sum(case when media_type = 'vid' then 1 else 0 end) VideoCount, sum(case when is_paid = 1 then 1 else 0 end) paidCount, sum(case when payment_status = 1 then 1 else 0 end) paymentCount, created FROM parkmedia GROUP BY patron_id ORDER BY id desc

Update query using multiple condition
UPDATE employees   SET gender =
(CASE  WHEN gender='Male'
THEN 'Female'
WHEN gender='Female'
THEN 'Male'
END) WHERE id = 5

Mysql between clause

If you are trying to locate records between two specific dates, say, March 15th, 2012 and April 15th, 2012
SELECT * FROM table name WHERE date BETWEEN '2012-03-15' AND '2012-04-15'

To get the orders whose required dates are from 01/01/2003 to 01/31/2003
SELECT * FROM orders WHERE requireddate BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2003-01-31' AS DATE);

Select all employee who has not attend coaching between 2014-12-26 AND 2015-01-30
SELECT e.id AS employee_id FROM employee e
LEFT JOIN coaching c ON c.employee_id = e.id
AND c.end_date >= '2014-12-26' AND c.start_date <= '2015-01-30'
WHERE c.employee_id IS NULL;

Select records between two columns
select * from mytable where 25 between col1 and col2;

Group Concat
select d.*, group_concat(t.tags_title order by t.tags_title asc separator ',') as topics
from wp_wpyog_document_topics dt join
    wp_wpyog_documents d
    on dt.document_id = d.id join
    wp_wpyog_topics t
    on dt.topic_id = t.id

group by d.id

How to assign rank basic of sum result

select * from(
select 
    IF(@total_like = total_like,
        @rank:=@rank,
        @rank:=@rank + 1) rank,
@rnk:=@rnk + 1 as rank_offset,
    @total_like:=total_like as total_likes,
    user_id AS `teacher_id`,
    school_id,
    user_name,
    imagename
from
    (SELECT 
        sum(`like_count`) as total_like,
            user_id,
            school_id,
            user_name,
            imagename
    from
        like_counts 
    group by user_id
    order by total_like desc ) r) t ,
    (SELECT @total_like:=0, @rank:=0) s where user_name like '%mona%'