MYSQL Tips

Command line importing

d:cd D:\wamp\bin\mysql\mysql5.1.33\bin

mysql -uDBNAME -pPASSWORD DBNAME < SQLFILENAME
 if there is no password(localhost) you can ommit the word starting with -p

Referential integrity(Foreign key)
http://articles.techrepublic.com.com/5100-10878_11-6035435.html


CREATE TABLE species (id TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) ENGINE=INNODB;# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO species VALUES (1, 'orangutan'), (2, 'elephant'), (3, 'hippopotamus'), (4, 'yak');# 4 row(s) affected.

CREATE TABLE zoo (id INT(4) NOT NULL, name VARCHAR(50) NOT NULL, FK_species TINYINT(4) NOT NULL, INDEX (FK_species), FOREIGN KEY (FK_species) REFERENCES species (id), PRIMARY KEY(id)) ENGINE=INNODB;# MySQL returned an empty result set (i.e. zero rows).


INSERT INTO zoo VALUES (1, 'Harry', 5);

will fail

ON DELETE  or  ONUPDATE

granding remote access to database(use with sql yog)

in cpanel homepage there is an option called 'remote databasese
click it and add % (wildcard to allowed ips)

upload phpmyadmin after editing config.inc.php
set
$cfg['Servers'][$i]['auth_type'] = 'cookie';

RESTRICT | CASCADE | SET NULL | NO ACTION