Load tab delimited data into a table:
LOAD DATA LOCAL INFILE 'C:/director/importdata.txt' INTO TABLE dbName.tableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

select distinct(lang), (sum(textlength)) FROM dbName.tableName group by lang;


Count number of words in a field:
SELECT LENGTH(langtext) - LENGTH(REPLACE(langtext, ' ', ''))+1, id FROM dbName.tableName INTO OUTFILE "D:\\outdirector\\length.txt" limit 10;


Find Language wise segments:
SELECT langColumn, count(langColumn) FROM dbName.tableName group by lang;

FIND LANGUAGE WISE SUM OF DURATION:
select distinct(lang), sec_to_time(sum(time_to_sec(`duration`)) + sum(microsecond(`duration`))/1000000) FROM dbName.tableName group by lang;


Clone a table:
CREATE TABLE IF NOT EXISTS offices_bk SELECT * FROM offices;

Sort by character length of a column:
select *, char_length(columnFieldCharLenghth) len FROM database.table  where freq > 1 order by len DESC;

Sort time/duration in Ascending or Descending Order:
SELECT * FROM dbName.tableName ORDER BY duration ASC/DESC;

Calculate SUM of total time/duration (with milliseconds):
select distinct(lang), sec_to_time(sum(time_to_sec(`duration`)) + sum(microsecond(`duration`))/1000000) FROM dbName.tableName where remarks <> 'XXX' group by lang;

Calculate SUM of total time/duration (without milliseconds):
SELECT  SEC_TO_TIME(SUM(TIME_TO_SEC(`duration`))) AS totaltime FROM dbName.tableName where lang = 'XX';


Calculate Average of Time/Duration:
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(`duration`))) FROM dbName.tableName where texttype = 'Sentence-S';

Delete/Remove rows:
DELETE FROM dbname.tablename where column= 'xx';


Drop/Delete/Remove a column in a table:
ALTER TABLE dbname.tablename DROP COLUMN `ColumnName`;

Copy content of one column1 to column2:
    UPDATE table SET column2 = column1;

Find fields that begin with 'XX':
SELECT ... WHERE stuff LIKE 'XX%';

Find multiple values:
select  * from iceberg.swords where id in (40, 145);

Find substring case sensitive:
SELECT * FROM iceberg.swords where transcription like BINARY '%e N . v%';


create table WorkTypes (id int(50) not null auto_increment primary key,HospitalName varchar(255),DocsAvailable varchar(255),DB_Worktype varchar(255),WTName_L1 varchar(255),WTName_L2 varchar(255),PT_Type varchar(255),comments varchar(255),WTID_ETS varchar(255),WT_Name_ETS varchar(255),TemplateFName varchar(255));


LOAD DATA LOCAL INFILE 'C:/director/importData.txt' INTO TABLE dbName.tableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE 'E:/directory/Load.txt' INTO TABLE dbName.tableName CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';

E:/directory/keywords.txt"
create table vclasses (id int(50) not null auto_increment primary key,word varchar(255), morph varchar(255), vclass varchar(255));

Select column fields containing a full word:
select * from table name where ColumnName REGEXP '[[:<:]]FullWord[[:>:]]';


SELECT a.* FROM ppost.ppostlatest a,ppost.icdwords b where `a`.`word` =BINARY  `b`.`word` limit 20000;

SELECT a.* FROM ppost.totalunqaed a,ppost.wordstocompare b where `a`.`word` =BINARY  `b`.`word` limit 20000;


Find and replace in a MySQL Column:
UPDATE `ppost`.`totaltagged` SET date = replace(date,'20130111 (Viva 5K)','20130111');


Query to update a field:
UPDATE `ppost`.`totaltagged` SET assigned_tag = 'JJ/NN'  where id = '57064';

Query to append some new data in the same format as the current table
Use the same as the load file

Query to find out the case sensitive duplicates inside a table column:
select word, count(word) cnt FROM `ppost`.`totaltagged` group by binary word having cnt > 1 order by cnt limit 200000;

Query to find out distinct entries in a column where the column is "FileName":
SELECT distinct(FileName) FROM `DirStruct`.`nonemrcnotes` limit 1000000;

//Query to duplicate a table with another name:
//Here "DirStruct.nonemrcnotesedited" is the name of the new table while the table being copied is "DirStruct.nonemrcnotes"

CREATE TABLE DirStruct.nonemrcnotesedited SELECT * FROM DirStruct.nonemrcnotes;

//Count frequency of fields in a column. Here the column name is "dir1"
SELECT dir1, count(dir1) FROM `DirStruct`.`nonemrcnotesedited` group by dir1;

//count occurrence of a some unique group of columns against another column
SELECT DocName,HospitalName, Specialty1, DocSpecialty, WorkType, WTName1, WTName2, count(FileLocation)from `DirStruct`.`corpsamp` group by DocName,HospitalName, Specialty1, DocSpecialty, WorkType, WTName1, WTName2 limit 5000;

//Select Unique Rows in a table
SELECT DISTINCT DocName,Specialty1,DocSpecialty,HospitalName,WorkType,WTName1,WTName2  FROM `DirStruct`.`corpsamp` limit 50000;


SELECT HospitalName, Specialty1, WorkType, WTName1, count(FileLocation)from `DirStruct`.`corpsamp`
group by HospitalName, Specialty1, WorkType, WTName1 limit 5000;

SELECT DISTINCT Specialty1,HospitalName,WorkType,WTName1  FROM `DirStruct`.`corpsamp` limit 50000;


SELECT count(DISTINCT HospitalName),Specialty1 FROM `DirStruct`.`corpsamp` where Specialty1 in ('IM_Cardiology','IM_Pulmonology','Emergency Medicine','Oncology','IM_Nephrology','IM_Internal Medicine General','Surgery','Otorhinolaryngology ','Radiology','IM_Gastroenterology','Orthopedics','Obstetrics & Gynecology','Pathology','Obstetrics','Urology','IM_Neurology','IM_Hematology','IM_Pediatrics','Anesthesiology','Psychiatry','Podiatry','Opthalmology','IM_Endocrinology','Family Medicine','Vascular and Thoracic Surgery','IM_Infectious Diseases','IM_Physical Medicnie and Rehabilitation','IM_Geriatrics','IM_After Hours Care','IM_General Medicine','IM_Oncology','Neurosurgery','IM_Pain Management','IM_Rheumatology','Hospitalist','IM_Hospice Care and Palliative Medicine','IM_Physician Assistant','Nurse Practitioner','IM_Occupational Medicine','Ignored') group by Specialty1;

Merge TwoTables:
insert into 50k1_new2 (fileRefs,freqs,id,parses,sents,sentsCleaned) select a.fileRefs,a.freqs,a.id,a.parses,a.sents,b.sentsCleaned from 50k1 a,50k1_cleaned b where a.id=b.id;

Backup a table from command line:
mysqldump  -u root -p posann whole_docs > whole_docs.sql

Backup all MySQL Databases into one file:
mysqldump -u root -p --all-databases > all_dbs.sql

Backup one MySQL Databse:
mysqldump database_name > database_name.sql

Restore a table from command line:
mysql -u root -p DatabaseName < path\TableName.sql

Insert column fields with another table in the database:
Update tempstatus.20140429_TemporalEval T, tempstatus.gold_data_doc_properties as G set T.AdmitDate = G.AdmitDate, T.DictnDate = G.DictnDate, T.DocType = G.DocType, T.DocTypeId = G.DocTypeId, T.ServiceDate = G.ServiceDate where G.fileName = T.fileName;

Find and Replace a String inside a field in a MYSQL table:
update dictionary.englishhindi set hindimeaning = replace(hindimeaning,'अंगूठालगाकरपलटनागन्दा','अंगूठा लगा कर पलटना गन्दा');

Number of words in a field in each row of a column where column name = "langtext":
SELECT id, LENGTH(langtext) - LENGTH(REPLACE(langtext, ' ', ''))+1 FROM dbName.tableName where lang = 'Bengali';

Does not equal to operator in MYSQL is: <>
SELECT count(*) FROM umls_14.rxnorm_fromumls_may2014 where tty <> 'OCD' ;
will return all rows where tty is not "OCD".

Export a query output to a file:
SELECT * FROM moviesite.movie INTO OUTFILE "C:\\TEMP\\test3.txt" ;

Select Rows where one column does not contain the value of another column:
SELECT * FROM tableName WHERE column1 NOT LIKE CONCAT('%', column2, '%');

Deleting a row:
DELETE FROM tableName (WHERE clause);

Combine multiple columns into one column:
update tableName set combined = CONCAT(column1, 'ANY_SEPARATOR', column2,);

Find fields ending in spaces:
SELECT * FROM tableName WHERE columnName REGEXP '[[.space.]]+$';



update ktaxon.contexts_h_sorted
set combined =
CONCAT(cat, '@', subcat, '@', context_h_sorted, '@', keyword, '@', comment);

Move a table to another database:
ALTER TABLE old_DB_Name.tableName RENAME new_DB_Name.tableName