Get Duplicate Values MySQL
Get Duplicate Values MySQL Simply group on all required columns. SELECT customer_name, customer_email, customer_age, COUNT(*) FROM users GROUP BY customer_name, customer_email, customer_age HAVING COUNT(*) > 1
MySQL
Get list of Procedures and Functions MySQL
Procedures: Get the list of all Procedures in MySQL. SHOW PROCEDURE STATUS; Get the list of all Procedures of a specific Database Schema in MySQL. SHOW PROCEDURE STATUS where db = ‘Schema_name’; Get specific Procedures of a specific Database Schema in MySQL. SHOW PROCEDURE STATUS where db = ‘sqsapp’ and name in (‘procedureName1’, ‘procedureName2’, ‘procedureNameN’); … Read more
Get list of User Accounts MySQL
Use below script to Get list of User Accounts MySQL. SELECT User FROM mysql.user;
Query results in CSV format MySQL
In Older Versions of MySQL: SELECT customer_id,customer_name,customer_age FROM customer WHERE salary > 50000 INTO OUTFILE ‘/var/lib/mysql-files/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’; In Newer Versions of MySQL: SELECT customer_id,customer_name,customer_age INTO OUTFILE ‘/var/lib/mysql-files/orders.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’; FROM customer WHERE salary > 50000
Insert into a table or update if exists MySQL
DUPLICATE KEY can be used to DUPLICATE to Insert into a table or update if exists in MySQL. INSERT INTO tableName (id, name, age) VALUES(10, “Roy”, 34) ON DUPLICATE KEY UPDATE name=”Roy”, age=34 It will insert a new record if no record exists with id = 10, Otherwise update if any record exists with id … Read more
Select First Row In Each Group MySQL
Select the First Row In Each Group of MySQL Here is an example: SELECT MIN(purchases2.id), # change to MAX if you want the highest value purchases1.customer, purchases1.total FROM PURCHASES purchases1 JOIN (SELECT p.customer, MAX(total) AS max_total FROM PURCHASES purchases GROUP BY p.customer) purchases2 ON purchases2.customer = purchases1.customer AND purchases2.max_total = purchases1.total GROUP BY purchases1.customer, purchases1.total
Alter Table if Column Not Exist in MySQL
Alter Table if Column Not Exist in MySQL To alter the table if the column does not exist: IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘tableName’ AND table_schema = ‘database_name’ AND column_name = ‘columnName’) THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default ‘0’; END IF;
Update From Another Table MySQL
Update From Another Table MySQL We can update another table with the help of the inner join. UPDATE TableName1 SET TableName1.col1 = TableName2.col1, TableName1.col2 = TableName2.col2 FROM Some_Table AS TableName1 INNER JOIN Other_Table AS TableName2 ON TableName1.id = TableName2.id WHERE TableName1.column3 = ‘Yes’
Select Random Rows in MySQL
SELECT column_name1, column_name2, column_nameN FROM table_name ORDER BY RAND() LIMIT 20; Another way that is more efficient way of selecting random rows in MySQL SELECT column_name1, column_name2, column_nameN FROM table_name AS t1 JOIN (SELECT id FROM table_name ORDER BY RAND() LIMIT 20) as t2 ON t1.id=t2.id;
Concatenate Multiple Rows Into One Field MySQL
GROUP_CONCAT function is used to concatenate multiple rows into a single field in MySQL. SELECT student_id, GROUP_CONCAT(subject_name SEPARATOR ‘, ‘) FROM student_subject GROUP BY student_id; Distinct Operator can be used to avoid duplicates. SELECT student_id, GROUP_CONCAT(distinct subject_name SEPARATOR ‘, ‘) FROM student_subject GROUP BY student_id; Order By Clause can be used to keep the values … Read more