MySQL: commonly used syntax example

this is a quick dirty list of some commonly used SQL syntax, summarized by WZY:-

The delimited dump of data:

SELECT * INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM mydb1.table1;

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE mydb1.table1 

  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

  LINES STARTING BY 'xxx',

  LINES TERMINATED BY '\n';

Note: 
(1)Backing up your data using the delimited dump is more efficient than mysqldump, and the dump file is smaller on disk.
(2)To dump or restore multiple tables at once, you can use mk-parallel-dump.
 
(3)Here you should make sure the in/out file is readable/writeable by mysql (for example, the file is owned by mysql) . Otherwise, you will have a permission problem.



ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE
UPDATE t SET c1_newtype = c1
ALTER TABLE t DROP COLUMN c1
RENAME COLUMN t.c1_newtype TO c1

----

example: change a column name

ALTER TABLE genes CHANGE oldName newName VARCHAR(9);

----

SHOW GLOBAL VARIABLES;

SHOW GLOBAL STATUS:

SHOW TABLE STATUS;

CHECK TABLE mytable;

OPTIMIZE TABLE mytable;

ANALYZE TABLE mytable;

REPAIR TABLE mytable;

-------------------------

Example: to create a table

CREATE TABLE test.genes(

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
gene VARCHAR(15),
chrom VARCHAR(6),
direction CHAR(1),
start1 INT,
stop1 INT,
start2 INT,
stop2 INT,
exons INT,
exonStarts VARCHAR(60),
exonStops VARCHAR(60),
unknown varchar(8),
remark VARCHAR(15)
) ENGINE=InnoDB; 

---------------------------

Here is the official table from UCSC

CREATE TABLE `knownGene` (
  `name` varchar(255) NOT NULL DEFAULT '',
  `chrom` varchar(255) NOT NULL DEFAULT '',
  `strand` char(1) NOT NULL DEFAULT '',
  `txStart` int(10) unsigned NOT NULL DEFAULT '0',
  `txEnd` int(10) unsigned NOT NULL DEFAULT '0',
  `cdsStart` int(10) unsigned NOT NULL DEFAULT '0',
  `cdsEnd` int(10) unsigned NOT NULL DEFAULT '0',
  `exonCount` int(10) unsigned NOT NULL DEFAULT '0',
  `exonStarts` longblob NOT NULL,
  `exonEnds` longblob NOT NULL,
  `proteinID` varchar(40) NOT NULL DEFAULT '',
  `alignID` varchar(255) NOT NULL DEFAULT '',
  KEY `name` (`name`),
  KEY `chrom` (`chrom`(16),`txStart`),
  KEY `chrom_2` (`chrom`(16),`txEnd`),
  KEY `protein` (`proteinID`(16)),
  KEY `align` (`alignID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


(to make this post easier to find, I leave a few unique/weird labels here: mysql import infile wzy 
alex138671 
)