MySQL Syntax:

	USE events;

	SHOW TABLES;

	DESCRIBE tablename;

	DELETE FROM tablename;

	DUMP TABLE tablename;

	TRUNCATE TABLE (TABLENAME);
	(will reset autoincrement & delete all rows)
		
	ALTER TABLE tbl_name AUTO_INCREMENT = xxx
	(to reset autoincrement)

	ALTER TABLE tblSpeakerSeminar ADD FOREIGN KEY(fkSpeakerID) REFERENCES tblSpeaker(pkSpeakerID);
	
	INSERT INTO tblTable VALUES (21, 01, 'Ottoman', 200.00); 

	UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair'; 



MOVE DB FROM ONE SERVER TO ANOTHER:

- dump database into a .txt file and then import it at new host

OR

- 1. Dump your database with the command mysqldump -u "User" -
p "Pass" "Databasename" > "Outfile".

- 2. Import the dumpfile in another database with the command mysql -
u "User" -p "Pass" "Databasename" < "Outfile from above".



MYSQL CHEAT SHEET:
http://www.nparikh.org/unix/mysql.php


Selecting a database:

mysql> USE database;


Listing databases:

mysql> SHOW DATABASES;


Listing tables in a db:

mysql> SHOW TABLES;


Describing the format of a table:

mysql> DESCRIBE table;


Creating a database:

mysql> CREATE DATABASE db_name;



Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);


Add foreign key:  
ALTER TABLE tblSpeaker ADD CONSTRAINT fk_SemID FOREIGN KEY (fkSeminarID) REFERENCES tblSeminar(pkSeminarID) ON UPDATE CASCADE ON DELETE CASCADE; 
ALTER TABLE tblSeminarSection ADD CONSTRAINT fk_SemSecID FOREIGN KEY (fkSeminarID) REFERENCES tblSeminar(pkSeminarID) ON UPDATE CASCADE ON DELETE CASCADE; 

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)


Inserting one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)


Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";


Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;


Fixing all records with a certain value:

mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";


Selecting specific columns:

mysql> SELECT column_name FROM table;


Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;


Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;


Date calculations:

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.


Pattern Matching:

mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)


Extended Regular Expression Matching:

mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"


Counting Rows:

mysql> SELECT COUNT(*) FROM table;


Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')


Selecting from multiple tables:

(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')


Currently selected database:

mysql> SELECT DATABASE();


Maximum value:

mysql> SELECT MAX(col_name) AS label FROM table;


Auto-incrementing rows:

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");


Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;


Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)



Batch mode (feeding in a script):

# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;



Backing up a database with mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at MySQL's docs.)











Data Types:

Numeric or Number:  Only numbers; should be used for numbers on which calculations will be done.

Text, String, Character, Alphanumeric:  Alphanumeric content

Memo:  Allows for more characters than the text data type.  Usually used for descriptions or lengthy blocks of text.

Date, Date/Time, or Time

Boolean:  Permits the entry of only one of two values, e.g., yes/no, true/false, on/off, etc.