MySQL Cheatsheet – Useful MySQL Queries

			Page rank	5

/*** select records from the previous day ***/ SELECT * FROM users WHERE TO_DAYS(last_login) = ( TO_DAYS(NOW()) - 1 ) /*** select records from last 90 minutes ***/ SELECT* FROM users DATE_SUB(NOW(),INTERVAL 90 MINUTE); /*** select records from last 1hr 5 mins ***/ SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE); /*** select records from last hour ***/ select DATE_SUB(NOW(), INTERVAL 1 HOUR); /*** using the SIGN function to mark a number as positive, negative or null ***/ SELECT backlist, SIGN(backlist) AS user_to_backlist FROM users WHERE user_banned IS NOT NULL; /*** select records from last week ***/ select DATE_SUB(NOW(), INTERVAL 1 WEEK); /*** get the last day of next month ***/ SELECT LAST_DAY('2006-03-06' + INTERVAL 1 MONTH) AS last_day; /*** select unique records only ***/ SELECT user_name FROM users GROUP BY users HAVING ( COUNT(user_name) = 1 ); /*** select records from one table that are in another table i.e. all the customers that have placed an order ***/ SELECT DISTINCT cust.customer_id, cust.customer_name FROM cust INNER JOIN orders ON cust.customer_id = orders.customer_id; /*** insert data from one table into another ***/ INSERT INTO customers(customer_id, customer_name) SELECT cus_key, cus_name FROM customers_2 WHERE customer_name LIKE 'W%'; /*** update information based upon a seperate table ***/ UPDATE cust SET status = '1' FROM orders WHERE orderdate > '2009-01-01' and orders.customer_id = cus.customer_id; /*** classic self join example - who is an emoployees manager ***/ SELECT emp.empID, emp.Name, emp.Salary, managers.Name AS manager_name FROM emp LEFT JOIN emp AS manager_name ON emp.ManagerID = Manager.EmployeeID WHERE (emp.empID= '123456'); /*** using UNION to combine results from multiple queries into a single table ***/ SELECT users.name FROM users WHERE (users.name BETWEEN 'A%' AND 'M%') UNION SELECT banned_users.name FROM banned_users WHERE (banned_users.name BETWEEN 'A%' AND 'M%'); /*** concatenate column data into a single column ***/ SELECT CONCAT(emp.firstname, '-', emp.lastname) AS emp_full_name FROM emp; /*** select count of records for each hour ***/ SELECT HOUR(last_login) AS last_login_hour, COUNT(*) AS the_count FROM users GROUP BY HOUR(last_login); /*** import a csv file ***/ LOAD DATA INFILE '/path/xxx.csv' INTO users_table csv_test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" (user_name, access_level , user_email); /*** display current mysql user ***/ SELECT USER();