Useful SQL Queries

gistfile1.sql

--SQL REPLACING
UPDATE mdl_message SET message = REPLACE(message, "target_view.phpcourse", "target_view.php?course") WHERE message LIKE('%target_view.phpcourse%');

--SQL Backup and Restore
--BACKUP
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
--RESTORE
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

--CREATE TABLE
CREATE TABLE mdl_targets (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    name VARCHAR(50) NOT NULL,
    date_created BIGINT(15) unsigned  NOT NULL,
    date_modified BIGINT(15) unsigned NOT NULL
);

--Find most searched for terms on website
SELECT DISTINCT term, COUNT(*) AS count FROM table GROUP BY term ORDER BY count DESC LIMIT 20;

--UPDATE COLUMN NAME
ALTER TABLE colors CHANGE tone hue varchar (10) ;

--PUT ONE COLUMNS DATA INTO ANOTHERS
UPDATE mdl_user SET email = CONCAT(idnumber, '@student.school.ac.uk') WHERE email = 'test@student.school.ac.uk' AND idnumber != '';

--ADD NEW COLUMN INTO TABLE
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

--FIND DUPLICATE VALUES IN A COLUMN
SELECT courseid FROM mdl_grade_items WHERE itemtype = 'course' GROUP BY courseid HAVING COUNT(*) > 1;

--Retrieving UNIXTIME in ORACLE:
SELECT (DATE_OF_BIRTH - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt, DATE_OF_BIRTH FROM FES.MOODLE_PEOPLE WHERE ROWNUM <10;

--Drop an INDEX
ALTER TABLE `table` DROP INDEX `indexname`;

--MySQL DATETIME FROM DATE - [PHP]
$date_now_mysql = date('Y-m-d H:i:s'); -- "2009-06-21 14:34:04": MySQL timestamp format

-- Create a MySQL dump and restore
backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql